1

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

outis
  • 75,655
  • 22
  • 151
  • 221
Conrad
  • 933
  • 4
  • 19
  • 34
  • 1
    Note that `or die(mysql_error())` should never appear in production code, as [`die`](http://www.phpfreaks.com/blog/or-die-must-die) breaks HTML output and database error messages should never be revealed to non-admin users as it [discloses too much information](http://msdn.microsoft.com/en-us/library/ms995351.aspx#securityerrormessages_topic2). A better approach would be to properly implement error handling (and use PDO instead of the outdated mysql extension, which is being deprecated). – outis Jul 13 '12 at 09:41
  • Aren't you supposed to use a join here ? And I think you forgot to add spaces at your "ORDER BY" strings. – Bob Jul 13 '12 at 09:41
  • [Implicit joins](http://stackoverflow.com/questions/44917/) shouldn't be used in favor of [explicit joins](http://stackoverflow.com/questions/2241991/). – outis Jul 13 '12 at 09:42
  • Note that whenever you're generating an SQL statement and are getting a syntax error, you should get the actual statement generated (and post it), and not just the code that generates the statement. – outis Jul 13 '12 at 09:44
  • SO uses a Q&A, rather than a forum, format. Questions shouldn't contain an answer, such as corrected code. Leave that to the answers. – outis Jul 13 '12 at 11:04
  • For coding issues other than correctness issues, you should check out [codereview.SE](http://codereview.stackexchange.com/). – outis Jul 13 '12 at 11:05

5 Answers5

4

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

Gerald Versluis
  • 30,492
  • 6
  • 73
  • 100
  • oh I see, because I just follow the way like writing C++, so making such kind of careless error, sorry. – Conrad Jul 13 '12 at 09:49
  • Always be sure to check the (minor) differences while switching languages ;) – Gerald Versluis Jul 13 '12 at 09:52
  • may I ask why there still have error in the order by sentence after I switch it to desc? here 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 'T.TechName ASC, L.Time DESC' at line 3" – Conrad Jul 13 '12 at 09:57
  • Ah sorry, you cant use ASC and DESC together, I believe. Just use DESC *OR* ASC at the end or your query – Gerald Versluis Jul 13 '12 at 09:59
  • I think it is okay to do so, as when I change it like the code I given above, it gives out no problem now(at least it works now) – Conrad Jul 13 '12 at 10:12
3

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.

Aleks G
  • 56,435
  • 29
  • 168
  • 265
3

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";
jagm
  • 576
  • 4
  • 6
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
}
Community
  • 1
  • 1
outis
  • 75,655
  • 22
  • 151
  • 221
0

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
James Thomas
  • 552
  • 1
  • 4
  • 12