最受欢迎

php和sql查询代码错误

php and sql query code error

本文关键字：代码 错误 查询 sql php | 更新日期: 2023-09-27

你好，朋友们//我有这两个代码用于简单搜索，但它不起作用//你能帮我吗。。。SQL查询在PHPMYADMIN中运行良好。但是当我运行php代码时，它会给我这个错误"字段列表中的未知列"a.orecid"

这是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>

这是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>

这是我的数据库结构：

工作人员已经配备了

机组已安装

位置具有位置单元（fk）

罪犯具有刑事身份和本地身份（fk）

犯罪记录有orecid、日期和犯罪id（fk）

按要求：

当我回显查询时，当我选择犯罪记录时，我得到了这个：

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

当我选择冒犯记录时，我得到了这个：

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

当您将别名赋予表名FROM ".$table." a时，请使用别名引用其列，而不是表名

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

我注意到在代码中您有a.recid，但您给出了报告a.orecid的错误消息-打字错误还是问题来源？

你能添加一个offenderrecord表的描述吗？为什么你有两个不同的Select语句？第一种是用crecid，第二种是用orecid，这是正确的吗？因此，如果我没有错的话，您的代码正在为两个表运行相同的Select语句，但应该为刑事记录运行第一个语句，为犯罪记录运行第二个语句。

  1. $query="选择a.recid，b.criminid，e.staffname，a.date FROM ".$table." a，罪犯b，地点c，单位d，工作人员e在哪里a.criminald=b.criminidAND b.locid=c.locid AND c.unitid=e.unitid AND（（".$table."id LIKE'%".$id"%'）&&"$哪里"）"；

  2. $query="选择a.orecid，b.offenderid，e.staffname，a.date FROM ".$table." a，罪犯b，地点c，单位d，工作人员e WHERE a.offenderid=b.offendered AND b.locid=c.locid AND c.unitid=e.unitid AND ".$table."id LIKE'%"$id。"%'"；

如果我是正确的，问题不是Select语句，而是运行了Wrong语句。

Sarajog

相关文章：
最新更新
www.56WenDa.com 2012-2023 | php问答网 | php学习