The right syntax to use near 'ORDER BY date DESC LIMIT 1' at line 4

Question

I keep getting the following 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 'ORDER BY date DESC LIMIT 1' at line 4

function printSoldiersOfRank($rank, $branch)
{
  $soldier = new soldierClass;
  $sql = "SELECT * FROM soldier WHERE rank = $rank AND branch = '$branch'";
  $result = mysql_query($sql);
  while($row = mysql_fetch_array($result)):
    $soldier->getInfo($row['sID']);
    $soldier->printInfo(); 
  endwhile;

function printInfo()
  {
    $sql = "SELECT promoter, name, date
    FROM soldier s, log l
    WHERE s.sID = l.promoter AND l.promotee = $this->sid
    ORDER BY date DESC LIMIT 1";
    $result = mysql_query($sql) or die(mysql_error());
    $row = mysql_fetch_array($result);
    echo "<div class='soldierInfo'>";
    echo "<a href='index.php?page=soldier&sid=$this->sid'>";
    echo $this->name;
    echo "</a><br />";
    echo "Last Promoted: ";
    echo date("d M Y", $row['date']);
    echo "<br />By: <a href='index.php?page=soldier&sid=$row[0]'>";
    echo $row['name'];
    echo "</a></div>";
  }

I'm assuming the error is in my printInfo() function. All help is appreciated, thanks in advance.

Please, don't use `mysql_*` functions to write new code. They are no longer maintained and the community has begun [deprecation process](http://goo.gl/KJveJ). See the *[red box](http://goo.gl/GPmFd)*? Instead you should learn about [prepared statements](http://goo.gl/vn8zQ) and use either [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli). If you can't decide which, [this article](http://goo.gl/3gqF9) will help you. If you pick PDO, [here is good tutorial](http://goo.gl/vFWnC). — Waleed Khan, Dec 27 '12 at 22:16
possible duplicate of [How to prevent SQL injection in PHP?](http://stackoverflow.com/questions/60174/how-to-prevent-sql-injection-in-php) — hakre, Dec 27 '12 at 22:21
`ORDER BY name` but in your function you have `ORDER BY date` . are sure you are in correct sql ? — echo_Me, Dec 27 '12 at 22:27
This isn't a dupe. Well, I mean, the dupe *helps*, but this isn't a dupe. This is just a vanilla bad question. I mean seriously, the user couldn't even be bothered to put the correct erroring code in there. Seriously now. I mean come on. **How are we supposed to give you a concrete, helpful answer if you don't give us the real, broken code!?** — Charles, Dec 27 '12 at 22:27
@JaredFarrish, I'm out of patience and I have to survive [tag:php] for another four days to win a meaningless bet. No, I'm not alright. \*twitch\* — Charles, Dec 27 '12 at 22:35
Sorry, I didn't realise I posted the wrong error. log 1 is where date is. — user1933198, Dec 27 '12 at 22:36
Did you even bother to check what your query looks like after variable substitution? What the bleep does `$this->sid` return? — Salman A, Dec 27 '12 at 22:42
To everyone: give the guy a break. @user1933198: $this->sid is either null or can't be parsed, try replacing it with '".$this->sid."' — periklis, Dec 27 '12 at 22:45
Break granted. Do an `echo $sql = "..."` and check the printed query for syntax errors. You might be able to fix the error yourself. — Salman A, Dec 27 '12 at 22:48

score 2 · Answer 1 · answered Dec 27 '12 at 22:35

2

Don't assume that the error is anywhere. Instead create an error message that gives you all the information you need to debug. Here is an example.

$sql = 
"
SELECT promoter, name, date
FROM soldier s, log l
WHERE ( s.sID = l.promoter AND l.promotee = $this->sid )
ORDER BY date DESC 
LIMIT 1
"
;

$result = mysql_query($sql);
if (!$result)
{
    echo PHP_EOL;
    echo "<br/>";
    echo "FAILED QUERY $sql";
    echo "<br/> ON ";
    echo __LINE__;
    echo " IN ";
    echo __FILE__;
    echo "<br/>";
    echo " BECAUSE ";
    die(mysql_error());
}

If you use something like that, you will be able to see the query that failed and the location of the failure with certainty. As it stands now, you have a query that uses "ORDER BY date DESC" and an error message that says "ORDER BY name ASC" so it leaves me to wonder if we are looking at the right script!

answered Dec 27 '12 at 22:35

Ray Paseur

2,106
2
13
18

1

Ahh, I see that the edit changed the error message. So I'll offer another suggestion. In MySQL "date" is a reserved word. You should probably try to avoid using reserved words for column names. But if you're stuck with it, try enclosing it in backticks (on the keyboard to the left of the number 1) like this... (but using backticks instead of apostrophes) ORDER BY `'date'` DESC – Ray Paseur Dec 27 '12 at 22:37
`date` is **not** a reserved word in MySQL. – ypercubeᵀᴹ Dec 28 '12 at 15:43
My mistake. Must have been some older data base that I remembered that from! – Ray Paseur Dec 28 '12 at 16:42

score 1 · Answer 2 · answered Dec 27 '12 at 22:46

1

Try this:

$sql = "SELECT promoter, name, date
    FROM soldier s, log l
    WHERE s.sID = l.promoter AND l.promotee = '".$this->sid."'
    ORDER BY date DESC LIMIT 1";

answered Dec 27 '12 at 22:46

periklis

10,102
6
60
68

2

+1 "right syntax to use near 'xxx'" -> you have to look right before that 'xxx' and you will know, what is wrong - and i guess `$this->sid` contains nothing and you get `... AND l.promotee = ORDER BY ...`:o) – Sir Rufo Dec 27 '12 at 23:09
When I do that, it gives me the following error: Table '1202341_us.log' doesn't exist – user1933198 Dec 28 '12 at 00:00
well this means that this error appears in another part of your system, so what you posted is actually resolved... – periklis Dec 28 '12 at 08:03

score 1 · Answer 3 · answered Dec 27 '12 at 22:48

1

Date is a variable type. Try enclosing the field named 'date' with ``

Example.

set @soldierId = 1 ;

SELECT promoter, name, `date`
    FROM soldier s, log l
    WHERE s.sID = l.promoter AND l.promotee = @soldierId
    ORDER BY `date` DESC LIMIT 1

answered Dec 27 '12 at 22:48

Keith John Hutchison

4,955
11
46
64

1

Surprise... `date` isn't a reserved word and can be used without backticks :) http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html – Salman A Dec 27 '12 at 22:50
Date is however a variable type. It is more important to write clear code than being clever. Putting the field named date inside back ticks makes the code easier to read. – Keith John Hutchison Dec 27 '12 at 22:55
I still get an 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 ') ORDER BY `date` DESC LIMIT 1' at line 3 – user1933198 Dec 27 '12 at 22:56
It's strange that the error message did not change. Have you tried the solution suggested by @Ray Paseur. His solution will display the exact SQL that failed, which you can then try directly with a MySQL client/gui. – Keith John Hutchison Dec 27 '12 at 23:02

score 1 · Answer 4 · answered Dec 27 '12 at 23:33

When you get an error message on a SQL statement from MySQL like

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 'xxxx' at line n

you have to keep an eye on your statement before 'xxxx'.

The SQL Parser stopped parsing the statement because of an error and tells you at what line this (stop) happens and what's the rest of the unparsed statement.

In this case the statement (received by MySQL) will look like this ($this->sid contains nothing)

SELECT promoter, name, date
FROM soldier s, log l
WHERE s.sID = l.promoter AND l.promotee = 
ORDER BY date DESC LIMIT 1

Give it a try and you will get the same error message.

You should check if $this->sid contains something useful before the query.

BTW: Using a parameterized statement will get you out of this special pitfall too (and several others)

echo_Me · Answer 5 · 2012-12-27T23:24:19.410

since u didnt say anything about your tables then

assign your columns by an aliace

    $sql = "SELECT s.`promoter`, s.`name`, s.`date`
                   ^-----------^--------------------------------if they are log table then replace by l
          FROM soldier s, log l
         WHERE s.sID = l.promoter AND l.promotee = '".$this->sid."'

         ORDER BY s.`date` DESC LIMIT 1";
                  ^--------------------------------look what table the date is.

i just guessed and made soldier if its log table then change where there is s to l

and make date between backtiks , because date is reserved word for mysql

The right syntax to use near 'ORDER BY date DESC LIMIT 1' at line 4

5 Answers5