Explore our sites

Stack Exchange
tour help careers 2.0
Take the tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite
1

I cannot see an answer to my question after hours of searching online.
Trying to obtain id of last updated row. This is my php script- 

$con = mysqli_connect("localhost","DB_NAME","PWD");  
if (mysqli_connect_errno())  
  {  
  echo "Failed to connect to MySQL: " . mysqli_connect_error();  
  }  
mysqli_select_db($con,"DB") or die ("no database");   


$result=mysqli_query($con,"SET @update_ID := 0;  
UPDATE TABLE_234 SET PLAYERNAME = PETER, ID = (SELECT @update_ID := ID)  
WHERE PLAYERNAME IS NULL  
LIMIT 1;  
SELECT @update_ID ");

if (!mysqli_query($con,$result))  
  {  
  die('Error: ' . mysqli_error($con));  
  }  

echo $ID;  
mysqli_close($con);

The error-
Warning: mysqli_query() [function.mysqli-query]: Empty query in… on line… 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…

Thank you for your help.

share|improve this question
add comment

3 Answers

up vote 1 down vote accepted

EDITED: you try to show $ID within last lines; but where did you set it in php code?

try this:

$con = mysqli_connect("localhost","DB_NAME","PWD");  
if (mysqli_connect_errno())  
{  
    echo "Failed to connect to MySQL: " . mysqli_connect_error();  
}  
mysqli_select_db($con,"DB") or die ("no database");   
$sql="SET @update_ID := 0;  
    UPDATE TABLE_234 SET PLAYERNAME = PETER, ID = (SELECT @update_ID := ID)  
    WHERE PLAYERNAME IS NULL  
    LIMIT 1;  
    SELECT @update_ID as ID";

if (mysqli_multi_query($con,$sql))
{
  do
    {
    // Store first result set
    if ($result=mysqli_store_result($con))
      {
        while ($row=mysqli_fetch_row($result))
        {

        $ID=$row[0];
        }
      }
    }
  while (mysqli_next_result($con));
}

echo "ID=" . $ID;  
mysqli_close($con);
share|improve this answer
 
Thank you for answering. Using your code, I get- 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 'UPDATE TABLE_234 SET PLAYERNAME = PETER, ID = (SELECT @update_ID :=' at line 2. Don’t know what is wrong… –  user3054183 Dec 1 '13 at 11:38
 
we have to use mysqli_multi_query, so answer edited, try again –  Mehdi Dec 1 '13 at 11:56
 
Thanks a lot. You have solved the problem. –  user3054183 Dec 1 '13 at 12:25
add comment
up vote 0 down vote

You're calling mysqli_query twice in your code. The first way is the correct way, the second way is wrong. In the first call you are passing SQL string, however in the second call you are passing result.

Based on your code, I believe you want to check $result in the if statement as follows:

if (!$result)  
{  
  die('Error: ' . mysqli_error($con));  
}
share|improve this answer
 
Thank you for your help. My problem appears to occur above that statement; here- 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 'UPDATE TABLE_234 SET PLAYERNAME = PETER, ID = (SELECT @update_ID :=' at line 2. Don’t know what is wrong… –  user3054183 Dec 1 '13 at 11:39
 
That problem is because you are trying to execute multiple sql statements with mysqli_query. You need mysqli_multi_query. Have a look at documentation here: php.net/manual/en/mysqli.multi-query.php. –  vee Dec 1 '13 at 11:42
add comment
up vote 0 down vote

try something like this

$con = mysqli_connect("localhost","DB_NAME","PWD");  
if (mysqli_connect_errno())  
{  
    echo "Failed to connect to MySQL: " . mysqli_connect_error();  
}  
mysqli_select_db($con,"DB") or die ("no database");   
$result=mysqli_query($con,"SET @update_ID := 0;  
    UPDATE TABLE_234 SET PLAYERNAME = PETER, ID = (SELECT @update_ID := ID)  
    WHERE PLAYERNAME IS NULL  
    LIMIT 1;  
    SELECT @update_ID ");

    if (!$result)  
    {  
       die('Error: ' . mysqli_error($con));  
    }  

    echo $ID;  
    mysqli_close($con);
share|improve this answer
 
Thanks a lot. 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 'UPDATE TABLE_234 SET PLAYERNAME = PETER, ID = (SELECT @update_ID :=' at line 2. Any ideas? –  user3054183 Dec 1 '13 at 11:41
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.