1

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.

Akshay N Shaju
  • 355
  • 4
  • 17
  • for this, did this: `"UPDATE table SET count = count + 1 WHERE id = _id_"`, simply increment the count by 1. – Murad Hasan May 18 '16 at 17:23
  • you realize that without a WHERE clause that you'll be updating your entire db if you have more than one record – Funk Forty Niner May 18 '16 at 17:24
  • oh nice stealth edit on your part!!! where an answer was given. Lovely. – Funk Forty Niner May 18 '16 at 17:26
  • Nope... actually i wanna select and display a particular data from a SQL table, also wanna update the count of the same data '+1' then update the table with the value. – Akshay N Shaju May 18 '16 at 17:32
  • 1
    `table` is a reserved word in MySQL. Use backticks to wrap your table and column name, like this: `$sqla="UPDATE \`table\` SET \`count\`='$new_count' WHERE id='$id'";`. – Rajdeep Paul May 18 '16 at 17:33
  • 1
    it's really quite simple. You read tutorials (the good ones), you learn, you build, and then you also learn how to debug code. It gets better too. – Funk Forty Niner May 18 '16 at 17:34
  • @RajdeepPaul hehe, true. I haven't seen a question for the longest time where an OP was actually using that reserved word; if that is really the case. They obviously don't know the "tricks of the trade" as in "error checking" etc. ;-) – Funk Forty Niner May 18 '16 at 17:45
  • @Fred-ii- I almost missed this, but then OP's [comment](http://stackoverflow.com/questions/37306331/php-mysql-select-data-from-table-then-update-the-same-table#comment62134254_37306393) made me realize this. :-) – Rajdeep Paul May 18 '16 at 17:50
  • 1
    @RajdeepPaul Real hard to say. Oh well, what can we do except "watch". – Funk Forty Niner May 18 '16 at 17:51
  • and I for one, have been watching long enough. bye bye – Funk Forty Niner May 18 '16 at 17:58
  • [Little Bobby](http://bobby-tables.com/) says [your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! – Jay Blanchard May 18 '16 at 18:11
  • Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). – Jay Blanchard May 18 '16 at 18:11

1 Answers1

3

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:

Funk Forty Niner
  • 74,450
  • 15
  • 68
  • 141
pes502
  • 1,597
  • 3
  • 17
  • 32
  • same problem again . Only the update query is not working. – Akshay N Shaju May 18 '16 at 17:26
  • yes ! condition (started at second line) ended with } correctly – Akshay N Shaju May 18 '16 at 17:35
  • @AkshayNShaju and in your table, column `count` really exist? – pes502 May 18 '16 at 17:36
  • yes , I can able to get the value from the table , i tried adding an `echo $count;` in my code , it returns the original count value , – Akshay N Shaju May 18 '16 at 17:40
  • @AkshayNShaju try to add `\`` to your SQL query, I mean. `UPDATE \`table\` SET \`count\`='$new_count' WHERE id='$id'` – pes502 May 18 '16 at 17:41
  • still it's not working ...... actually i wanna select and display a particular data from a SQL table, also wanna update the count of the same data '+1' then update the table with the value.. give me a solution – Akshay N Shaju May 18 '16 at 17:45
  • 1
    @pes502 I hope you don't mind the edit I made. It's unsure whether or not the OP is actually using `table` as their table name. You can always do a rollback if you wish. – Funk Forty Niner May 18 '16 at 17:50
  • 1
    @AkshayNShaju Few things to debug this issue: 1) Do `echo $sqla;` before executing the query and check whether it's the correct query or not. 2) Run the raw query on your MySQL console and see whether the update is actually happening or not. 3) Turn on error reporting, [http://php.net/manual/en/function.mysql-error.php](http://php.net/manual/en/function.mysql-error.php) – Rajdeep Paul May 18 '16 at 17:57
  • 1
    @AkshayNShaju Oh so now you tell us *lol* Btw,`LEVEL` is not a reserved word, it's a keyword; 2 different animals here. Next time, why don't you post your REAL code?! wink. and leave the guesswork out of it perhaps and would have been solved a LOT sooner! – Funk Forty Niner May 18 '16 at 18:07