PHP MySQL max() function producing 5

I am working on a survey created by PHP and MySQL. So the issue is, I am trying to create a ResponseID, which is an ID specific for every person submitting the survey. So the code was created to add 1 to the existing max value from the ResponseID column. Here's the code:

//Response ID creation 
$query = "SELECT max(ResponseID) FROM survey";
$res = mysql_query($query);
$RID = $res+1; 

I know I can condense it, but here's the problem: I already entered one item on the table with the ResponseID of 1. When I tested the survey with different answers, the next ResponseID was 5. It should have been 2. So I tested again to see if it would produce 6 next time.

Unfo开发者_如何学运维rtunately, it produced 5 again. I had my PHP guru looked it over and he said the coding was correct and it should be something from the database. I didn't set anything in the ResponseID except for it being an int. So why is it producing a 5? If anyone could please tell me how to go about fixing it, that would be super cool of you.

I am somewhat new to PHP.

$res will be a mysql statement handle, NOT the result of the query. you still have to actually FETCH a row from this $res result to access the value of the max() function in the query.

This handle probably internally has identifier #5, which is why you're getting that "odd" result.

The code should be:

$sql = "SELECT MAX(responseID) AS max ...";
$result = mysql_query($sql) or die(mysql_error());
$row = mysql_fetch_assoc($result);

$RID = $row['max'] + 1;

$res is a resource, not the value of the query, please read the manual: http://nz.php.net/manual/en/function.mysql-query.php

Why don't you use an AUTO_INCREMENT column and retrieve the new value using mysql_insert_id?

You need to use an AUTO_INCREMENT column for this. The problem is that if two instances of the PHP script are running at the same time (extremely likely assuming you're using a multithreaded server like Apache), then you could have a case like this:

|PHP #1         |  PHP #2       |
=================================
|Accept client  |(waiting)      |
|SELECT MAX...  |(waiting)      |
|Send to MySQL  | Accept client |
|(waiting)      | SELECT MAX... |
|(waiting)      | Send to MySQL |
|Get response 4 |Get response 4 | //Nothing inserted yet, max is still 4
|Try to insert 5| (waiting)     |
|Succeeded      | (waiting)     |
|(waiting)      |Try to insert 5|
| ...           | Failed!       |

(This in addition to what Dagon said)

Use $row = mysql_fetch_assoc($res) to get the resulting row from your query.

See mysql_fetch_assoc().

The reason why you are not getting the correct value is because mysql_query doesn't return a value, it returns a resource.

Try this instead:

//Response ID creation 
$query = "SELECT max(ResponseID) FROM survey";
$res = mysql_query($query);
if($res !== FALSE) $res = mysql_fetch_array($res);   // If $res === FALSE, something went wrong
$RID = $res[0] + 1;

Also I suggest you to use AUTO_INCREMENT on the ResponseID field, this makes your life a lot easier :)

i'm not really sure if i understood ur problem but if u wanna generate a response ID specific to every user or every survey posted, then what u can do is use auto_increment for the response ID. the response id will be incremented every time a new survey is posted. also get the last id posted using mysql_insert_id to get the last id posted.

so u can get the last id using

$last_id = mysql_insert_id ();

u need to put that statement right after ur query. that way u can get the last id.