Databases
StackOverflow2013
Postsdbo
POphp and sql query code error
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POphp and sql query code error
    primarykey
    data
    text
    <p>hello friends// i have this 2 codes for a simple search and its not working// can u help me please... the SQL query is working fine in PHPMYADMIN. but when i run the php code it gives me this error Unknown column 'a.orecid' in 'field list</p> <p>Here is the code for recorddisplay.php</p> <pre><code>&lt;?php require_once 'db.php' ?&gt; &lt;!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"&gt; &lt;html xmlns="http://www.w3.org/1999/xhtml"&gt; &lt;head&gt; &lt;title&gt;&lt;/title&gt; &lt;style type="text/css"&gt; body { background-image:url(images/back.jpg); background-repeat:!important; text-align: justify; } .button { background-color: #000; color: #FFF; font-weight: bold; text-transform: uppercase; } .title { letter-spacing: normal; word-spacing: normal; } .subtitle { background-image: url(images/transparent.png); background-repeat:inherit; font-size: x-large; } .container .content table tr td #form1 table tr td { text-align: center; font-weight: bold; } .container .content p { text-align: center; font-weight: bold; font-family: "Trebuchet MS", Arial, Helvetica, sans-serif; } #Myrules { margin-top: 100px; margin-right: auto; margin-bottom: auto; margin-left: 50px; } .container .content p { font-size: large; color: #FFF; } .container .footer { text-align: center; } &lt;/style&gt; &lt;script src="SpryAssets/SpryValidationTextField.js" type="text/javascript"&gt;&lt;/script&gt; &lt;link href="SpryAssets/SpryValidationTextField.css" rel="stylesheet" type="text/css" /&gt; &lt;/head&gt; &lt;body text="#FFFFFF"&gt; &lt;div class="container"&gt; &lt;div class="header"&gt;&lt;a href="#"&gt;&lt;img src="images/logocdts.jpg" alt="" name="Insert_logo" width="348" height="151" id="Insert_logo" style="background: #C6D580; display:block;" /&gt;&lt;/a&gt; &lt;/div&gt; &lt;?php //session_start(); //$username = $_SESSION['username']; if ($_POST['Subject'] === '1') { $table = "criminalrecord"; //$fields = "`crecid`,`staffname`,`date`"; $title = "Criminalrecord"; } else if ($_POST['Subject'] === '2') { $table = "offenderrecord"; //$fields = "`orecid`,`staffname`,`date`"; $title = "Offenderrecord"; } else if ($_POST['Subject'] != '1' and $_POST['Subject'] != '2') { echo "&lt;script&gt;alert('Please choose subject to Proceed....')&lt;/script&gt;"; echo "&lt;script language=javascript&gt;history.back(1);&lt;/script&gt;"; echo "go back"; } $subject = $table; $id = $_POST['id']; $staffname = $_POST['staffname']; $date = $_POST['date']; if ((($id) &amp;&amp; ($staffname) || ($date)) || ((!$id) &amp;&amp; ($staffname) || ($date)) ) { $field_array = array("staffname"=&gt;"$staffname", "date"=&gt;"$date"); foreach ($field_array as $row =&gt; $value){ if ($value != ""){ $query_string[] = "(`".$row."` LIKE '%".$value."%')"; } } $where = implode(" || " , $query_string); //break apart the array into a string //SELECT a.crecid, e.staffname, date FROM criminalrecord a, criminal b, location c, unit d, staff e WHERE a.criminalid = b.criminalid AND b.locid = c.locid AND c.unitid = e.unitid $query = "SELECT a.crecid, b.criminalid, e.staffname, a.date FROM `".$table."` a, criminal b, location c, unit d,staff e WHERE a.criminalid = b.criminalid AND b.locid = c.locid AND c.unitid = e.unitid AND ((`".$table."id` LIKE '%".$id."%') &amp;&amp; ".$where." )"; // echo "&lt;br&gt;".$query."&lt;br&gt;"; die(); $search = mysql_query($query) or die(mysql_error()); } else if (($id) &amp;&amp; (!$staffname) || (!$date)) { $query = "SELECT a.orecid, b.offenderid, e.staffname, a.date FROM `".$table."` a, offender b ,location c, unit d,staff e WHERE a.offenderid = b.offenderid AND b.locid = c.locid and c.unitid = e.unitid and `".$table."id` LIKE '%".$id."%' "; // echo "&lt;br&gt;".$query."&lt;br&gt;"; die(); $search = mysql_query($query) or die(mysql_error()); } $num_rows = mysql_num_rows($search); if ($num_rows &gt; 0) { echo '&lt;h1&gt;'.$title.'&lt;/h1&gt;'; print "&lt;BR&gt;"; print "&lt;table width=800 border=1&gt;"; print "&lt;tr bgcolor=#FF9900&gt;"; print "&lt;td colspan=14&gt;SUBJECT RECORDS INFO&lt;/td&gt;"; print "&lt;/tr&gt;"; print "&lt;tr bgcolor=black&gt;"; print "&lt;td width=108&gt;ID&lt;/td&gt;"; print "&lt;td width=108&gt;Staff Name&lt;/td&gt;"; print "&lt;td width=108&gt;Date&lt;/td&gt;"; print "&lt;/tr&gt;"; while ($row= mysql_fetch_array($search)) { print "&lt;tr&gt;"; print "&lt;td&gt;"; print $row[0]; print "&lt;/td&gt;"; print "&lt;td&gt;"; print $row[1]; print "&lt;/td&gt;"; print "&lt;td&gt;"; print $row[2]; print "&lt;/td&gt;"; print "&lt;/tr&gt;"; } print "&lt;/table&gt;"; } else { echo "&lt;script&gt;alert('Please go back to continue or to the Instructions menu for more info....')&lt;/script&gt;"; echo "&lt;script language=javascript&gt;history.back(1);&lt;/script&gt;"; echo "go back"; }?&gt; &lt;div class="footer"&gt;Copyright 2013 - CDTS &lt;/div&gt; &lt;/div&gt; &lt;/body&gt; &lt;/html&gt; </code></pre> <p>here is the code for recordsearch.php</p> <pre><code>&lt;!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"&gt; &lt;html xmlns="http://www.w3.org/1999/xhtml"&gt; &lt;head&gt; &lt;script type="text/javascript" src="jquery-1.9.1.js"&gt;&lt;/script&gt; &lt;script language="JavaScript" type="text/javascript"&gt; function swapContent(cv) { $("#mydiv").html('&lt;img src="gmap2.jpg"/&gt;').show(); var url= "ProfilePs_script.php" $.post(url, {contentVar: cv} ,function(data){ $("#mydiv").html(data).show(); }); } function swapVar(cv) { $("#mydiv").html('&lt;img src="gmap2.jpg"/&gt;').show(); var url= "ProfileSp_script.php" $.post(url, {swapVar: cv} ,function(data){ $("#mydiv").html(data).show(); }); } &lt;/script&gt; &lt;title&gt;ProfilePC_PS&lt;/title&gt; &lt;style type="text/css"&gt; body { background-image: url(images/back.jpg); background-repeat:!important; text-align: center; } .button { background-color: #000; color: #FFF; font-weight: bold; text-transform: uppercase; } .title { letter-spacing: normal; word-spacing: normal; } .subtitle { background-image: url(images/transparent.png); background-repeat:inherit; font-size: x-large; } .container .content table tr td #form1 table tr td { text-align: left; font-weight: bold; } .container .content p { text-align: center; font-size: large; font-family: "Trebuchet MS", Arial, Helvetica, sans-serif; font-weight: bold; } #Myrules { margin-top: 100px; margin-right: auto; margin-bottom: auto; margin-left: 50px; } .container .content .overlay table tr td #form2 table tr td { text-align: left; font-weight: bold; } &lt;/style&gt; &lt;script src="SpryAssets/SpryValidationTextField.js" type="text/javascript"&gt;&lt;/script&gt; &lt;link href="SpryAssets/SpryValidationTextField.css" rel="stylesheet" type="text/css" /&gt; &lt;/head&gt; &lt;body text="#FFFFFF"&gt; &lt;div class="content"&gt; &lt;p&gt;Search records here&lt;/p&gt; &lt;div class="overlay"&gt; &lt;table width="514" border="0"&gt; &lt;tr&gt; &lt;td width="508" height="204"&gt;&lt;form action="recorddisplay.php" method="post" enctype="multipart/form-data" name="form1" id="form1"&gt; &lt;table width="400" border="0"&gt; &lt;tr&gt; &lt;td width="178" align="center"&gt;&lt;div align="left"&gt;Choose your Subject&lt;/div&gt;&lt;/td&gt; &lt;td width="212"&gt;&lt;label for="id"&gt;&lt;/label&gt; &lt;label for="Subject"&gt;&lt;/label&gt; &lt;select name="Subject" id="Subject"&gt; &lt;option value=""&gt;--please select--&lt;/option&gt; &lt;option value="1"&gt;Criminalrecord&lt;/option&gt; &lt;option value="2"&gt;Offenderrecord&lt;/option&gt; &lt;/select&gt;&lt;/td&gt; &lt;/tr&gt; &lt;tr&gt; &lt;td align="center" valign="middle"&gt;&lt;div align="left"&gt;Subject ID&lt;/div&gt;&lt;/td&gt; &lt;td&gt;&lt;input type="text" name="id" id="id" /&gt; &lt;/td&gt; &lt;/tr&gt; &lt;tr&gt; &lt;td height="21"&gt;&lt;div align="left"&gt;Staff Name&lt;/div&gt;&lt;/td&gt; &lt;td&gt;&lt;input type="text" name="staffname" id="staffname" /&gt;&lt;/td&gt; &lt;/tr&gt; &lt;tr&gt; &lt;td height="21"&gt;&lt;div align="left"&gt;Date&lt;/div&gt;&lt;/td&gt; &lt;td&gt;&lt;input type="date" name="date" id="date" /&gt;&lt;/td&gt; &lt;/tr&gt; &lt;tr&gt; &lt;td height="26"&gt;&amp;nbsp;&lt;/td&gt; &lt;td&gt;&lt;input name="Clear" type="submit" class="button" id="Clear" value="Clear" /&gt; &lt;input name="Search" type="submit" class="button" id="Search" value="Search" /&gt;&lt;/td&gt; &lt;/tr&gt; &lt;/table&gt; &lt;/form&gt;&lt;/td&gt; &lt;/tr&gt; &lt;/table&gt; &lt;/div&gt; &lt;div class="overlay"&gt;&lt;/div&gt; &lt;/div&gt; &lt;/body&gt; &lt;/html&gt; </code></pre> <p><strong>Here is my database structure:</strong></p> <p>staff have staffid and unitid(fk)</p> <p>unit have unitid</p> <p>location have locid unitid(fk)</p> <p>criminal have criminalid and locid(fk)</p> <p>criminalrecords have orecid, date and criminalid(fk)</p> <p>AS requested:</p> <p>when i echo query i got this when i choose criminalrecord:</p> <pre><code>SELECT a.orecid, b.offenderid, e.staffname, a.date FROM `criminalrecord` a, offender b ,location c, unit d,staff e WHERE a.offenderid = b.offenderid AND b.locid = c.locid and c.unitid = e.unitid and `a.id` LIKE '%%' </code></pre> <p>and when i choose offenderrecord i got this:</p> <pre><code>SELECT a.orecid, b.offenderid, e.staffname, a.date FROM `offenderrecord` a, offender b ,location c, unit d,staff e WHERE a.offenderid = b.offenderid AND b.locid = c.locid and c.unitid = e.unitid and `a.id` LIKE '%%' </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USNitin J Mutkawoa
    1. USNitin J Mutkawoa
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload