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

Question

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){
    if($row[clientid].contains($clientid){
              $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).

Edit 2:

Hold up! I wanted to do it php side because the associated username is generated from the clientid (all in php). So im going to follow the links from liridyn and see if i can do something.

Would there be a safe way to query the database again as soon as a new client has been registered in order to get their allocated clientid, so i can then update their row with the generated username?

Thanks

if you made **clientid** `auto_increment` in your database, it would alleviate all the stress of having to try and code this. It would automatically increment your **clientid** — Zak, Jul 20 '16 at 18:02
First order of business: Please stop using `mysql_` functions _immediately_. Those functions were [deprecated in PHP 5.5](https://secure.php.net/manual/en/migration55.deprecated.php) and have been removed in PHP 7. Instead, use `mysqli_` functions or `PDO`. And _don't_ mix these like you are above. See https://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php Next, why are you doing this manually? Let the database assign IDs itself using [`AUTO_INCREMENT`](https://dev.mysql.com/doc/refman/5.7/en/example-auto-increment.html). — ChrisGPT was on strike, Jul 20 '16 at 18:02
The two above 100% but if you absolutely need to do this manually, a far easier approach would be to limit your query to the highest clientid and then just add one to that. The scaling of your current solution is going to drastically decrease performance as that table fills up. — Jason Bell, Jul 20 '16 at 18:05
Your logic breaks completely if records are deleted. You'd be better off using something like `SELECT MAX(id)` — mister martin, Jul 20 '16 at 18:05
@mistermartin That's not even enough. You can have conflicts due to race conditions. — tadman, Jul 20 '16 at 18:10
ok, i am using mysqli thanks. Something upset me about the leaving gaps in the client id's. I tested A_I and it works great for now. When i can remember why i avoided using it, ill return. — Jube, Jul 20 '16 at 18:10
yeah, I tried going through the max($row[client]) and got it working but as soon as i delete a row i'm unable to recreate because of doubles — Jube, Jul 20 '16 at 18:13
@Jube Don't worry about gaps. They're inevitable and trying to plug them is a fool's errand, you'll never be able to get it perfect. They're just numbers that are, for all intents, arbitrary. — tadman, Jul 20 '16 at 18:13
Thanks guys, ill send this pseudocode to the head of mysql and tell him to update his auto increment (less gaps). — Jube, Jul 20 '16 at 18:16
Hold up! I wanted to do it php side because the associated username is generated from the clientid (all in php). So im going to follow the links from liridyn and see if i can do something. — Jube, Jul 20 '16 at 18:25

score 1 · Accepted Answer · edited May 23 '17 at 12:14

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.

score 0 · Answer 2 · answered Jul 20 '16 at 18:19

0

I've ended up using Auto_increment on the db side. Thanks

answered Jul 20 '16 at 18:19

Jube

184
2
15

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

2 Answers2