Akshay N Shaju Akshay N Shaju - 6 months ago 55
SQL Question

Php - MySQL select data from table, then update the same table

What is wrong with this code ?

$sql=mysql_query("SELECT * FROM table WHERE id='$id'");
if($data=mysql_fetch_array($sql))
{
$count=$data['count'];
$new_count=$count+1;
$sqla="UPDATE table SET count='$new_count' WHERE id='$id'";
if(mysql_query($sqla))
{
echo "success";
}


Everything is working fine except UPDATE query.

when I add an

echo $new_count;


it returns the correct value.

Answer

As first:

In your second SQL query:

$sqla="UPDATE table SET count='$new_count'";

you need to specify, which row/rows you want to update. For this you must use WHERE clause.

For example:

$sqla="UPDATE table SET count='$new_count' WHERE id='$id'";

As second:

You have missing } in your condition, which can be the problem too. If I will space your code, it will looks like:

$sql=mysql_query("SELECT * FROM table WHERE id='$id'");
if($data=mysql_fetch_array($sql))
{
  $count=$data['count'];
  $new_count=$count+1;

  $sqla="UPDATE table SET count='$new_count' WHERE id='$id'";
  if(mysql_query($sqla))
  {
    echo "success";
  }

Is your condition (started at second line) ended with } correctly?

As third:

Save output of mysql_fetch_array and mysql_query to a variable and then use this variable in your conditions:

$data = mysql_fetch_array($sql);
if($data) { ...

And

$result = mysql_query($sqla);
if($result) { ...

Footnotes:

It is unknown whether or not the table name you are using is indeed called table.

If it is, then that is a MySQL reserved word and it requires special attention, as in wrapping it in ticks or naming it to something other than a reserved word.

I.e.:

SELECT * FROM `table`

and

UPDATE `table`

Reference: