在第4行'ORDER BY date DESC LIMIT 1'附近使用的正确语法[关闭]

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.

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!

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

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

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

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)