PHP MySQL query problem

$dblocation = "localhost";
$dbname = "xx";
$dbuser = "xx";
$dbpasswd = "xx";
$dbcnx = @mysql_connect($dblocation,$dbuser,$dbpasswd);
mysql_select_db($dbname, $dbcnx);
mysql_query("SET NAMES utf8");
mysql_query("SET COLLATION_CONNECTION=utf8_bin"); 
$num = mysql_query("select no from votes WHERE id='".$id."';");
$num2 = $num + 1;
$num3 = mysql_real_escape_string($num2);
mysql_query("UPDATE votes SET no='".$num3."' WHERE id=".$id.";");

It's a php code. I want it to:

1) Get a "no" v开发者_Go百科alue (INT) from "votes" table from the row where "id" (INT) equals $id

2) Increment this value by one

3) Write the new value to "votes" table to the row with "id" (INT) equals $id

But somehow my code is not working properly. Any ideas?

---

There are several things that can go wrong here -

• You are suppressing errors from mysql_connect() - it might not be connecting properly to the database
• mysql_query() returns a resource which then has to be used by one of the row fetching functions (such as mysql_fetch_assoc() ). You can't use its result like you do.

Basically, you should be running your code with the highest error_reporting level during development. You will know immediately what the problem is (and drop the error suppression operator in front of mysql_connect() - there is almost never a good reason to use it).

As an aside, there is no reason to set collation and character set for retrieving numbers. This entire operation can be replaced by a single update query:

$query = "UPDATE votes SET no=no+1 WHERE id=" . (int) $id;

---

You should, for starters, get rid of the @ symbols in front of function calls. This will suppress errors caused by the given statement - and leave you wondering what is going wrong. While you are developing, you want all the information about your code you can get, especially errors. So, removing the @ (error suppression operator) is the first step.

Second, whenever you query your database, you have two steps. First you call mysql_query. Then, after ensuring that mysql_query returned anything, you call a fetch-type statement on the query object. Check out the docs for mysql_query: http://php.net/manual/en/function.mysql-query.php There is a great, simple example there - see if you can spot where your code goes wrong.

All of that aside, don't need to jump through all those hoops to increment a database field...

mysql_query('UPDATE votes SET no = no+1 WHERE id='.$id);

For reference, a quick re-write of your sample code would look like this (using your approach rather than the one I mention above):

$dblocation = "localhost";
$dbname = "xx";
$dbuser = "xx";
$dbpasswd = "xx";
$dbcnx = mysql_connect($dblocation,$dbuser,$dbpasswd);
// handle a problem connecting to the database.  You probably want to do something nicer than just "die" an error message
if (!$dbcnx)
    die('Error connecting to database');

mysql_select_db($dbname, $dbcnx);

// not strictly neccesary to do this
mysql_query("SET NAMES utf8");
mysql_query("SET COLLATION_CONNECTION=utf8_bin");


$query = mysql_query('SELECT `no` FROM `votes` WHERE `id`='.$id);
// again, you'll probably want to do something nicer than blat out a text error
if (!$query)
    die('Record not found');

$num = mysql_fetch_assoc($query);
// type your number
$num = (int) $num['no'];

// increment it
$num++;

mysql_query('UPDATE `votes` SET `no`='.$num.' WHERE `id`='.$id);

---

You missed to get the row from the result:

$result= mysql_query("select no from votes WHERE id='".$id."';");
// you have to fetch a row from the result
$row = mysql_fetch_array($result);
// your first item in the array will be the value of 'no'
$num2 = $row[0] + 1;

---

There are any number of things that can co wrong without you knowing, put a few ifs and die()s (check if $dbcnx is not null).

Next, $num is actually a resource, not a result. To get the result, do the following:

$result = mysql_query("select no from votes WHERE id='".$id."';");
$row = mysql_fetch_assoc($result);
$num = (int) $row['no'];

Lastly, the "no" column looks like it is an integer, so you do not need the single quotes around it in the query statement, nor do you need to escape it like you do with text, just cast it as an int and that will eliminate all risk.