CODEWITHSUNDEEP
phpmysqlcaching
Mike
Mike

Reputation: 31

PHP MySql Query is showing old value after update

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 )

Upvotes: 3

Views: 3016

Answers (5)

KoolKabin
KoolKabin

Reputation: 17663

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());

Upvotes: 0

Luke Stevenson
Luke Stevenson

Reputation: 10341

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.

Upvotes: 1

adamors
adamors

Reputation: 2656

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

Upvotes: 0

Bojangles
Bojangles

Reputation: 101483

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.

Upvotes: 0

Aaria Carter-Weir
Aaria Carter-Weir

Reputation: 1679

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.

Upvotes: 0

Related Questions