Jube Jube - 1 year ago 47
MySQL Question

How to check each id in a column then allocate the first available number in PHP for mysql?

Using PHP i'd like to check the next available number to use as an id after comparing it to the query that lists all my id's
In theory I can do this;

$clientid = '0';
$getid_query = "SELECT clientid FROM clients";

$response = mysqli_query($conny, $getid_query);

while($data = mysql_fetch_assoc($response)){
$row[] = $data;

$freeid = False;

while($freeid == false){
$clientid = $clientid + 1;
else {
$freeid = true;

This leaves $clientid as an unique id ready to be used for the next created client

there might be a few syntax errors but in general I've tried most combinations and seem to get it right, I've been testing the different outputs such as

echo "Error:" . $row[clientid];

and sometimes (more often than not, nothing displays).


Answer Source

So, SELECT MAX(clientId) + 1 AS clientId FROM clients would replace your mixed PHP/SQL with a single query - but I would recommend against that, as adding AUTO_INCREMENT to clientId would instruct the SQL client to manage that for you automatically; if you then need that id, instead of generating it from PHP or calling SELECT MAX(clientID) FROM clients (which still has a race condition), you can follow the advice in this answer or call mysqli_insert_id.