错误的SQL语法，检查正确语法在第1行接近“0”。

I have meet the error mentioned in the title, here is the code in my PHP file, I have tried to reform my query, but fail to correct the error, can anyone tell me why there is such kind of error, is there the way I form the error is incorrect? I have seen some similar posts, but what I know from those posts is the problem should be my query, but I have no idea how to change it. So may I ask for your help to tell me where is the problem of my query, this can definitely help me to make clear my concept.

我有满足题目中提到的错误,这是我的PHP文件中的代码,我试图改革查询,但不能纠正这个错误,谁能告诉我为什么有这样的错误,有我的错误是不正确的?我看到过一些类似的帖子，但是我从这些帖子中知道问题应该是我的问题，但是我不知道如何去改变它。所以我可以请求您的帮助告诉我，我的查询的问题在哪里，这肯定能帮助我明确我的概念。

$query = "SELECT * 
    FROM Tech AS T, Client AS C, Site AS S, Log AS L 
    WHERE T.TechID=L.TechID, C.ClientID=L.ClientID, S.SiteID=L.SiteID";
if($sort=="Tech")
   $query += "ORDER BY T.TechName ASC, L.Time DSC";
else if($sort=="Client")
   $query += "ORDER BY C.ClientName ASC, L.Time DSC";
$result = mysql_query($query) or die('Error! ' . mysql_error());; 
print "Real-Time check in/check out<br>";
print "<table><th><td>Tech</td><td>Client</td><td>Site</td>";
print "<td>Date and Time</td><td>Type</td></th>";
while($row = mysql_fetch_array($result)){
print "<tr><td>".$row['T.TechName']."</td>";
print "<td>".$row['C.ClientName']."</td>";
print "<td>".$row['S.SiteName']."</td>";
print "<td>".$row['L.Time']."</td>";
print "<td>".$row['L.Type']."</td></tr>";
}
print "</table>";

Below is the error message:

下面是错误信息:

Error! You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '0' at line 1

错误!在SQL语法中有一个错误;请检查与您的MySQL服务器版本对应的手册，以便在第1行使用接近“0”的语法。

5 个解决方案

#1

Can't see where a zero could be coming from..

我看不出0可能来自哪里。

But at the lines where you are appending the ORDER BY to your query you're missing a space.

但是，在你将订单追加到查询的行中，你丢失了一个空格。

Either append a space at the end of you original $query, or append a space to the beginning of your $query +=

要么在原始$query的末尾追加一个空格，要么追加一个空格到$query +=的开头。

Also for descending it should be DESC not DSC

也应该是DESC而不是DSC。

Also the += should be a .= for PHP

另外，+=应该是。= PHP。

#2

AND instead of commas,

逗号,

space before ORDER

之前空间秩序

.= instead of +=

代替+ =。=

$query = "SELECT * 
    FROM Tech AS T, Client AS C, Site AS S, Log AS L 
    WHERE T.TechID=L.TechID AND C.ClientID=L.ClientID AND S.SiteID=L.SiteID";
if($sort=="Tech")
   $query .= " ORDER BY T.TechName ASC, L.Time DESC";
else if($sort=="Client")
   $query .= " ORDER BY C.ClientName ASC, L.Time DESC";

#3

Your problem is in the WHERE clause. You need to use AND construct instead of commas:

你的问题在WHERE子句中。您需要使用和构造而不是逗号:

SELECT * 
FROM Tech AS T, Client AS C, Site AS S, Log AS L 
WHERE T.TechID=L.TechID AND C.ClientID=L.ClientID AND S.SiteID=L.SiteID

Yet, even a better solution would be to use a join:

然而，一个更好的解决方案是使用一个连接:

SELECT * 
FROM Tech AS T
JOIN Client AS C on T.TechID=L.TechID
JOIN Site AS S on C.ClientID=L.ClientID
JOIN Log AS L on S.SiteID=L.SiteID

EDIT: You get near 0 at line 1 because you use + trying concatenate strings instead of . operator. When you use +, php converts both parts to numbers and adds them. As your strings don't contact numbers, both are converted to 0 and thus the result is 0. Therefore your final SQL is simply 0. You could check it with print_r($query);

编辑:您在第1行接近0，因为您使用+尝试连接字符串而不是。操作符。当您使用+时，php将这两个部分转换为数字并添加它们。由于字符串不接触数字，所以它们都被转换为0，因此结果为0。因此，最终的SQL是0。您可以使用print_r($query)检查它;

EDIT2: Thanks to Gerald Vesluis for noticing a missing space in the beginning of the ORDER BY clause.

EDIT2:感谢Gerald Vesluis在ORDER BY子句开始时注意到一个丢失的空格。

#4

+ and += add numbers, . and .= append strings.

+ +=添加数字。和。=附加字符串。

Field names in the result array don't include the table name. You should reference (e.g.) $row['TechName'], not $row['T.TechName'].

结果数组中的字段名不包括表名。你应该参考(例如)$row['TechName']，而不是$row['T.TechName']。

Off Topic

Mixing DB access and output creates too high a coupling. Ideally, each would be handled in separate layers. You can start this process by using PDO or (in PHP 5.4 and greater) mysqli, and iterate with foreach over results instead of explicitly calling any methods that are a part of the DB results class interface. This magic is possible because PDOStatement and (as of PHP 5.4) mysqli_result support the Traversable interface.

混合数据库访问和输出创建了过高的耦合。理想情况下，每一层都要分别处理。您可以使用PDO或(在PHP 5.4和更大的)mysqli中启动这个过程，并对结果进行迭代，而不是显式地调用任何作为DB结果类接口部分的方法。之所以有这种魔力是因为PDOStatement和(PHP 5.4) mysqli_result支持可遍历的接口。

<br/> is rarely semantic; use something more appropriate, such as a heading element, or apply styling to existing elements.

< br / >是很少的语义;使用更合适的东西，例如标题元素，或者将样式应用于现有元素。

<th> isn't a valid parent of <td>; <tr> should be the parent and <th> should be used in place of <td>.

不是的有效父母;应该是父母，而应该代替。

While brackets are optional when a branch consists of a single statement, it's usually considered bad style to leave them out, as it occasionally leads to bugs.

当一个分支包含一个单独的语句时，括号是可选的，但是将它们排除在外通常被认为是不好的，因为它偶尔会导致bug。

While else if is functionally equivalent to elseif, it's handled by the runtime as an else with an if as the body:

而elseif在功能上等同于elseif，它由运行时作为另一个if作为主体来处理:

if (...) {
} else {
    if (...) {
    }
}

elseif is the preferred form.

elseif是首选表单。

If there's any chance that $sort could hold a non-string, == could evaluate to TRUE even (0 == 'Tech'). Use strict comparison (===) to ensure the values are compared as strings.

如果$sort可以持有一个非字符串，则==可以计算为TRUE，即使(0 == 'Tech')。使用严格的比较(===)，以确保将值与字符串进行比较。

Apply the various suggested alterations and you'll wind up with something like:

运用各种建议的修改，你就会得到这样的结果:

# Future improvement: abstract each section into methods that are part of separate layers

# Database access
$statement = 'SELECT T.TechName, C.ClientName, S.SiteName, L.Time, L.Type 
    FROM Log AS L
      JOIN Tech AS T ON T.TechID=L.TechID
      JOIN Client AS C ON C.ClientID=L.ClientID
      JOIN Site AS S ON S.SiteID=L.SiteID';

if ($sort === 'Tech') {
   $query .= ' ORDER BY T.TechName ASC, L.Time DESC';
} elseif ($sort === 'Client') {
   $query .= ' ORDER BY C.ClientName ASC, L.Time DESC';
}

try {
    $data = $db->query($statement);
} catch (PDOException $exc) {
    $data = array();
    # handle error appropriately: inform user that there was an internal error. Log the actual error and possibly notify the dev team.
    ...
}

# Output/User interface
$fields = array('TechName' => 'Tech', 'ClientName' => 'Client', 'SiteName' => 'Site', 'Time' => 'Date and Time', 'Type' => 'Type');
if ($data) {
    ?>
    <h3>Real-Time check in/check out</h3>
    <table>
      <thead>
        <tr><th><?php echo implode('</th><th>', $fields) ?></th></tr>
      </thead>
      <tbody>
        <?php foreach ($data as $row) { ?>
          <tr>
            <?php foreach ($fields as $field => $label) { ?>
              <td><?php echo $row[$field] ?></td>
            <?php } ?>
          </tr>
        <?php } ?>
      </tbody>
    </table>
    <?php
}

#5

I think you need to put a space at the end of $query because you are appending the order by statements onto it without any space between.

我认为您需要在$query的末尾添加一个空格，因为您是在没有任何空格的情况下将命令添加到它的。

So it would look like this:

它是这样的

L.SiteIDORDERBY

Instead of like this:

而不是像这样:

L.SiteID ORDER BY