php and sql query code error

Question

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

Here is the code for recorddisplay.php

<?php
require_once 'db.php'
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title></title>
<style type="text/css">
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;
}
</style>

<script src="SpryAssets/SpryValidationTextField.js" type="text/javascript"></script>
<link href="SpryAssets/SpryValidationTextField.css" rel="stylesheet" type="text/css" />
</head>
<body text="#FFFFFF">

 <div class="container">
  <div class="header"><a href="#"><img src="images/logocdts.jpg" alt="" name="Insert_logo" width="348" height="151" id="Insert_logo" style="background: #C6D580; display:block;" /></a>
  </div>

<?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 "<script>alert('Please choose subject to Proceed....')</script>";
               echo "<script language=javascript>history.back(1);</script>";
               echo "go back"; 
        }
            $subject      = $table;
            $id           = $_POST['id'];
            $staffname    = $_POST['staffname'];
            $date         = $_POST['date'];


    if ((($id) && ($staffname) || ($date)) || ((!$id) && ($staffname) || ($date))   ) {

        $field_array = array("staffname"=>"$staffname", "date"=>"$date"); 

        foreach ($field_array as $row => $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."%') && ".$where." )";
        //       echo "<br>".$query."<br>"; die();

        $search = mysql_query($query) or die(mysql_error()); 
    }   else if (($id) && (!$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 "<br>".$query."<br>"; die();
        $search = mysql_query($query) or die(mysql_error());
    }


        $num_rows = mysql_num_rows($search);
        if ($num_rows > 0) {
                echo '<h1>'.$title.'</h1>';
                print "<BR>";
                print "<table width=800 border=1>";
                print "<tr bgcolor=#FF9900>";
                print  "<td colspan=14>SUBJECT RECORDS INFO</td>";
                print "</tr>";
                print "<tr bgcolor=black>";
                print "<td width=108>ID</td>";
                print "<td width=108>Staff Name</td>";
                print "<td width=108>Date</td>";

                print "</tr>";

                while ($row= mysql_fetch_array($search)) {
                print "<tr>";

                print "<td>";
                print $row[0];
                print "</td>";
                print "<td>";
                print $row[1];
                print "</td>";
                print "<td>";
                print $row[2];
                print "</td>";

                print "</tr>";
            }
                print "</table>";
        } else {
                echo "<script>alert('Please go back to continue or to the Instructions menu for more info....')</script>";
                echo "<script language=javascript>history.back(1);</script>";
                echo "go back";
        }?>



<div class="footer">Copyright 2013 - CDTS </div>
</div>



</body>



</html>

here is the code for recordsearch.php

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>


<script type="text/javascript" src="jquery-1.9.1.js"></script>
<script language="JavaScript" type="text/javascript">
function swapContent(cv) {
    $("#mydiv").html('<img src="gmap2.jpg"/>').show();
    var url= "ProfilePs_script.php"

        $.post(url, {contentVar: cv} ,function(data){
        $("#mydiv").html(data).show();
        });

}

function swapVar(cv) {
    $("#mydiv").html('<img src="gmap2.jpg"/>').show();
    var url= "ProfileSp_script.php"

        $.post(url, {swapVar: cv} ,function(data){
        $("#mydiv").html(data).show();
        });
}
</script>

<title>ProfilePC_PS</title>

<style type="text/css">
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;
}
</style>

<script src="SpryAssets/SpryValidationTextField.js" type="text/javascript"></script>
<link href="SpryAssets/SpryValidationTextField.css" rel="stylesheet" type="text/css" />


</head>



<body text="#FFFFFF">
  <div class="content">
    <p>Search records here</p>
    <div class="overlay">
      <table width="514" border="0">
      <tr>
      <td width="508" height="204"><form action="recorddisplay.php" method="post" enctype="multipart/form-data" name="form1" id="form1">
      <table width="400" border="0">
      <tr>
      <td width="178" align="center"><div align="left">Choose your Subject</div></td>
      <td width="212"><label for="id"></label>
              <label for="Subject"></label>
              <select name="Subject" id="Subject">
              <option value="">--please select--</option>
              <option value="1">Criminalrecord</option>
              <option value="2">Offenderrecord</option>
              </select></td>
      </tr>
      <tr>
      <td align="center" valign="middle"><div align="left">Subject ID</div></td>
      <td><input type="text" name="id" id="id" />
      </td>
      </tr>
      <tr>
      <td height="21"><div align="left">Staff Name</div></td>
      <td><input type="text" name="staffname" id="staffname" /></td>
      </tr>
      <tr>
      <td height="21"><div align="left">Date</div></td>
      <td><input type="date" name="date" id="date" /></td>
      </tr>
       <tr>
      <td height="26">&nbsp;</td>
      <td><input name="Clear" type="submit" class="button" id="Clear" value="Clear" />
          <input name="Search" type="submit" class="button" id="Search" value="Search" /></td>
     </tr>
      </table>
        </form></td>
      </tr>
      </table>

</div>
<div class="overlay"></div>
</div>





</body>



</html>

Here is my database structure:

staff have staffid and unitid(fk)

unit have unitid

location have locid unitid(fk)

criminal have criminalid and locid(fk)

criminalrecords have orecid, date and criminalid(fk)

AS requested:

when i echo query i got this when i choose criminalrecord:

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 '%%'

and when i choose offenderrecord i got this:

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 '%%'

Sarajog · Answer 1 · 2013-08-12T12:28:04.097

Can you please add a description of the offenderrecord table? And why do you have 2 different Select stattements? First is with crecid and the second with orecid, is this correct? So if I'm not wrong your code is running the same Select-Statement for both Tables but should run the first one for the criminalrecord and the second one for the offenderrecord.

$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."%') && ".$where." )";
$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."%' ";

If I'm correct the Problem is not the Select-Statement but, that the Wrong statement is run.

Sarajog

i have run this query and it works: SELECT a.crecid, b.criminalid, e.staffname, a.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 — Nitin J Mutkawoa, Jul 24 '13 at 06:29

score 0 · Answer 2 · answered Jul 24 '13 at 06:20

0

When you have given the alias to table name FROM ".$table." a then use the alias to refer its columns not the table name

`".$table."id`  change this to a.`id`

answered Jul 24 '13 at 06:20

M Khalid Junaid

63,861
10
90
118

still i get this error when i choose criminalrecord Unknown column 'a.offenderid' in 'where clause' and when i choose offenderrecord it gives me Unknown column 'a.id' in 'where clause' – Nitin J Mutkawoa Jul 24 '13 at 06:31
Please `echo $query ;` and add in your question – M Khalid Junaid Jul 24 '13 at 06:33
ok done i have already pu the echo result for both criminalrecord and offenderrecord – Nitin J Mutkawoa Jul 24 '13 at 06:48

score 0 · Answer 3 · answered Jul 24 '13 at 06:27

0

I notice in code you have a.crecid but you gave the error message reporting a.orecid - typo or source of problem ?

answered Jul 24 '13 at 06:27

Tin Bum

1,397
1
8
16

look for orecid in your query then - error says it does not exist so if its a typo change it to crecid ?? – Tin Bum Jul 24 '13 at 06:39
it is crecid though im choosing criminalrecord just like i have posted above – Nitin J Mutkawoa Jul 24 '13 at 06:46
Something is incomplete in the code you provided. I've searched it for orecid and it's nowhere significant enough to produce the error. orecid exists only in a commented out line of code ( //$fields = "`orecid`,`staffname`,`date`"; ) – Tin Bum Jul 24 '13 at 06:53
Another possible source of problem may be that you are using 1,2 etc for flagging choices. 1 can indicate true (versus false) as well as the integer you intend. Haven't looked in detail at this but might help to use A,B,C instead to eliminate the possibility. Or maybe use if ($_POST['Subject'] === '1') three equals signs not 2 (see http://stackoverflow.com/questions/589549/php-vs-operator) – Tin Bum Jul 24 '13 at 07:00
same error..i think its in the query or the query does not match the php – Nitin J Mutkawoa Jul 24 '13 at 07:14
I suspect the code your are running/testing IS NOT the code you have posted here. The code you have posted here does not contain the orecid string in runnable code so it cannot produce the queries you are showing. – Tin Bum Jul 24 '13 at 08:16

php and sql query code error

3 Answers3