Sql query problem

Question

I have the below sql query that will update the the values from a form to the database

$sql=
    "update leads set
       category='$Category',
       type='$stype',
       contactName='$ContactName',
       email='$Email',
       phone='$Phone',
       altphone='$PhoneAlt', mobile='$Mobile',
       fax='$Fax',
       address='$Address',
       city='$City',
       country='$Country',
       DateEdited='$today',
       printed='$Printed',
       remarks='$Remarks' 
     where id='$id'";

    $result=mysql_query($sql) or die(mysql_error());
echo '<h1>Successfully Updated!!.</h1>';

when i submit I dont get any errors and the success message is displayed but the database isnt updated . When i echo the $sql, all the values are set properly. and when i ech the $result i get the value 1.

can someone please tell me what am i doing wrong here??

Answer 1

Have you tried running the echo of $sql directly using some DB tool? It may provide a more informative error. Alternatively, if that works you may have an issue where the transaction isn't being committed. Often a connection is set to automatically commit transactions, but that may not be the case here. Try adding a commit.

And have you ever heard of SQL injection attacks?

Answer 2

try to echo $sql and run it directly in any database console, may be there is no record with id = $id

Answer 3

If you have a query that is not giving the expected result or receiving an error, and the problem isn't obvious, you should generally take a look at the final query just before it's run. Try using this right before running the query:

echo $sql;
exit;

Viewing the actual query often makes it obvious what the problem is, especially when the query includes variables. If the problem still isn't obvious, you can paste the query as is into a query browser to get feedback directly from the database engine.

Interestingly, using parametrized queries, you won't get to see the parameter values, as the parameters get replaced by MySQL, not PHP, however, you'll still get to see the entire prepared query.

Also, you can see the number of affected rows from your UPDATE statement with the mysql_affected_rows() function. You could put this immediately after the query is run:

echo ("Updated records:", mysql_affected_rows());

Spaces are often forgotten when concatenating queries.

$sql = "SELECT * FROM ducks";
$sql .= "WHERE duck = 'goose'";

When echoing the above query, we see:

SELECT * FROM ducksWHERE duck <> 'goose'

I'm guessing that the WHERE clause in your UPDATE statement isn't matching an "id = '$id'".

Also, is the id column really a string? You've put single quotes around the value. MySQL will cast the string to an integer if needed, but if it's an integer, save the database some work and remove the single quotes.

Answer 4

SQL Injection can be the answer. Not an intentional attack (at this moment), but if your parameters have some unexpected information like quotes or other reserved characters you can have strange results. So, try to run this SQL directly in your database administration utility.