Error in SQL syntax, check for right syntax to use near '0' at line 1

Question

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.

$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

Answer 1

+ 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'].

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.

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

<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.

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

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

elseif is the preferred form.

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.

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
}

Answer 2

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

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);

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

Answer 3

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.

So it would look like this:

L.SiteIDORDERBY

Instead of like this:

L.SiteID ORDER BY

Answer 4

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";

Answer 5

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

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 +=

Also for descending it should be DESC not DSC

Also the += should be a .= for PHP