[SOLVED] issues with UPDATE statement.....maybe there are rules that i dont know

[jeger003]

hello everyone,

im having issues with an update statement that i am using to process a form

 

here is my code....help will be appreciated.

 

here is my 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 '' at line 1

 

line 1 is <?php

 

 

also is there a way to have it display the specific rows affected? like have it tell me which row was affected by id or any other field?

 

 

<?php


include 'myconfig.php';

if(isset($_POST['Ad_Number']) && isset($_POST['Reset_By_Ad_Live']))
{
      $Ad_Number = mysql_real_escape_string($_POST['Ad_Number']);
  $Reset_By_Live = mysql_real_escape_string($_POST['Reset_By_Live']);
  
          //i also replaced AND with a comma and got the same error
  mysql_query("UPDATE listings SET expiration = 0 , expiration_sent = 0 WHERE id = $Ad_Number AND live = $Reset_By_Live ") or die(mysql_error());
      
      $affectedRows = mysql_affected_rows();
               
  echo "<br><br>Ad # Reset: $affectedRows<br><br>";
}
else
{

echo "Ad Reset Process Failed";
}


?>

[teng84]

try adding single quote with the fields value specially if the ecpected value is string something like live = '$Reset_By_Live'

 

also is there a way to have it display the specific rows affected? like have it tell me which row was affected by id or any other field?

affected rows

http://www.php.net/manual/en/function.msql-affected-rows.php

 

or since you have the categories or specification which row should be updated u can you those variable

to query from ur database once update is successful

 

edited..

always echo your sql statement when debugging to see whats going on

[jeger003]

try adding single quote with the fields value specially if the ecpected value is string something like live = '$Reset_By_Live'

 

also is there a way to have it display the specific rows affected? like have it tell me which row was affected by id or any other field?

affected rows

http://www.php.net/manual/en/function.msql-affected-rows.php

 

or since you have the categories or specification which row should be updated u can you those variable

to query from ur database once update is successful

 

edited..

always echo your sql statement when debugging to see whats going on

 

I made the changes but i continue to get the same error. Is AND allowed in an UPDATE statement?

 

as for the rows affected.....your suggestion is actually a really good idea to just use the values i already have..thanks for that

[trq]

Post your current code.

[Philip]

So, does your query look like the following?

UPDATE `listings` SET `expiration` = 0 , `expiration_sent` = 0 WHERE `id` = '$Ad_Number' AND `live` = '$Reset_By_Live'

[jeger003]

this is the edit i made

 

<?php
include 'myconfig.php';

if(isset($_POST['Ad_Number']) && isset($_POST['Reset_By_Ad_Live']))
{
      $Ad_Number = mysql_real_escape_string($_POST['Ad_Number']);
  $Reset_By_Live = mysql_real_escape_string($_POST['Reset_By_Live']);
  
          //i also replaced AND with a comma and got the same error

  mysql_query("UPDATE listings SET expiration = 0 , expiration_sent = 0 WHERE id = '$Ad_Number' AND live = '$Reset_By_Live' ") or die(mysql_error());
      
      $affectedRows = mysql_affected_rows();
               
  echo "<br><br>Ad # Reset: $affectedRows<br><br>";
}
else
{
echo "Ad Reset Process Failed";
}


?>

 

i put single quote around $Ad_Number and $Reset_By_Live

 

this is a different code that is similar and works correctly but this one does not have AND

 

if(isset($_POST['private_email']) && isset($_POST['username']))
{
       $email = mysql_real_escape_string($_POST['private_email']);
       $username = mysql_real_escape_string($_POST['username']);
      
      mysql_query("UPDATE users SET private_email = '$email' WHERE username = '$username'") or die(mysql_error());
      
      $affectedRows = mysql_affected_rows();
               
  echo "<br><br>Rows Affected: $affectedRows<br><br>";
}
else
{

echo "Process Failed";
}

 

@KingPhilip

I made it look like that as well and get the same error

 

 

 

 

 

[premiso]

You can try and trim the $_POST and see if they are getting white spaces from somewhere.

 

But try echoing out that query instead of running it, then opening phpMyAdmin and see if it runs there, if not it is probably the id and live are not in the DB.

[jeger003]

You can try and trim the $_POST and see if they are getting white spaces from somewhere.

 

But try echoing out that query instead of running it, then opening phpMyAdmin and see if it runs there, if not it is probably the id and live are not in the DB.

 

your right about it not finding the values....if you look at my ($_POST['Reset_By_Ad_Live']).....its value is different from the

 

$Reset_By_Live = mysql_real_escape_string($_POST['Reset_By_Live']);

 

the Reset_By_Live is supposed to be Reset_By_Ad_Live...lol

 

i caught it after looking over the form again and again...in conclusion.....it works greatt

 

 

thank you guys soo much for the help.........i heard from somewhere that AND cant be used in an UPDATE statement........i guess they were wrong