I am updating a value in a sql table and then printing the value, it is still showing old value.
<?
print_r(mysql_fetch_array(mysql_query("select visits from Orders")));
mysql_query("update Orders set visits=visits+1");
print_r(mysql_fetch_array(mysql_query("select visits from Orders")));
?>
It outputs 1 and then again 1. The second value should be 2. When I check in PhpMyAdmin it is 2 then why is it showing Old Value?
Please help! Thanks in advance.
Regards, Mike
Edit:
This is the code the OP tried:
mysql_connect("localhost","mayankx_tt","111111");
mysql_select_db("mayankx_tt") or die(mysql_error());
print_r(mysql_fetch_array(mysql_query("select visits from Orders")));
mysql_query("update Orders set visits=visits+1");
print_r(mysql_fetch_array(mysql_query("select visits from Orders")));
And his output:
Array ( [0] => 4 [visits] => 4 ) Array ( [0] => 4 [visits] => 4 )
Check you have write privileges to the database. Also check your query cache? Load up your db in either CLI or something like phpmyadmin / sequel pro and run the query as root. Check the results. The problem will most likely be down to permissions / caching.
I could be on the wrong track here, but it might be because you're updating the entire Orders table. Try adding a WHERE clause to your UPDATE and SELECT queries so that they only affect/fetch one row.
I have encountered a similar problem, when there was no WHERE clause in the update/delete query. Newer MySQL versions won't execute the script if WHERE is missing, regardless of how many rows you have.
Phpmyadmin usually adds this WHERE clause to everything however, so it may work there, and not in PHP. For instance, if you browse a table, you'll see in Phpmyadmin the query
SELECT * FROM TABLE_NAME WHERE 1
Check whether the UPDATE is actually executing:
mysql_connect("localhost","mayankx_tt","111111");
mysql_select_db("mayankx_tt") or die(mysql_error());
# Checking PRE Value
print_r(mysql_fetch_array(mysql_query("select visits from Orders")));
# Attempting to Modify
if( !mysql_query("update Orders set visits=visits+1") ){
echo 'Error Occurred: #'.mysql_errno().' '.mysql_error();
}else{
echo 'UPDATE Processed OK';
}
# Check POST Value
print_r(mysql_fetch_array(mysql_query("select visits from Orders")));
Run that and let us (me) know what output it produced.
You may be getting error due to using orders as tablename. "Orders" Is reserved keyword so in order to use it as tablename for select and update enclose it with backtick (`) symbol
"Update `Orders` set visit = visit + 1"
For executing Mysql Query better track the error too. generally we log sql statement and either log or show the error message as per setting. For simple debugging use following stmt:
mysql_query( "SQL STMT" ) or die("MYSQL Error :: ".mysql_errno()."<br>Message: ".mysql_error());