CODEWITHSUNDEEP
phpmysqlsqlfetch
Who Cares
Who Cares

Reputation: 205

Getting last value of a field in mysql

I am trying to get the last value of a field during a new registration. before insert data into the table, I want to create a user id number according to the last registered user's id number. to do that I use this:

//to reach the last value of userID field;
$sql = "SELECT userID FROM loto_users ORDER BY userID DESC LIMIT 1";        
$result = mysql_query($sql);
while($row = mysql_fetch_array($result))
{
   $value = $row['userID'];
   echo "$value";            //not resulting here
}
$userID = $value+1;

so, the userID becomes 1.

The weird thing is, I could capable to use exact same code in another php file and works fine.

I would like to say that, rest of the code works fine. No problem with db connections or any other things you can tell me.

Note that: When I run the same query line in the mysql interface, I can get the value I want. I mean $sql line.

Upvotes: 2

Views: 1391

Answers (3)

Ozgur Dogan
Ozgur Dogan

Reputation: 51

If you mark the userID field as autoincrement in you mysql table.

You won't need to set the userID and db increase the userID for you. You can get the assigned userID using the mysql_insert_id() function. Here is an example from php.net

mysql_query("INSERT INTO mytable (product) values ('kossu')");
printf("Last inserted record has id %d\n", mysql_insert_id());

Here is another example for your case

mysql_query("INSERT INTO 'loto_users'('username',...) values('usernameValue',...)");
echo "New User id is ".mysql_insert_id();

Upvotes: 0

Who Cares
Who Cares

Reputation: 205

I solved the problem. Here;

$sql = "SELECT userID FROM loto_users ORDER BY userID DESC LIMIT 1";    
$result = mysql_query($sql);
$user_info = $result->fetch_assoc();
$value = intval($user_info["userID"]);
$userID = $value+1;

Thanks everyone.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270583

Your problem is in this code:

{
   $svalue = $row['userID'];
----^
   echo "$value";            //not resulting here
}
$userID = $value+1;

Change to $value.

But the right answer is to define userID to be auto-incrementing. That way, the database does the work for you. After inserting the row, you can do:

SELECT LAST_INSERT_ID()

To get the last value.

Upvotes: 4

Related Questions