1

i want to store 2 related record in 2 different table in mysql
i have these information
name, option1, option2

name and its id are store in users table and options are store in options table
the uid in this table is auto increment

users table

uid --- name
1   --- Jack
2   --- Sara

options table

user_uid --- option
1        --- aaa
1        --- bbb
2        --- ccc
2        --- ddd

the question is how can i find next uid in users table to save my options with that foreign key, there are several ways to get next generated id like

LAST_INSERT_ID()

or

mysql_insert_id()

or

$result = mysql_query("SHOW TABLE STATUS LIKE 'table_name'");
$row = mysql_fetch_array($result);
$nextId = $row['Auto_increment'];

but if several request coming to server in same time are these ways work ?
what if after i get last id, another record saved to db and change last id
is there any way to reserve next auto increment id and get that ?

Ali Parsa
  • 134
  • 10
  • 1
    Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). – Jay Blanchard Feb 09 '16 at 16:54
  • 1
    Yes they work. The `mysql_insert_id()` will give you the `id` of the record YOU just created, regardless of whether 1000 other people also created a row in the same table at roughly the same time. I say roughly as of course the point of a **real database** is that it looks after concurrent access for you, unlike lesser databases like Access etc – RiggsFolly Feb 09 '16 at 16:56

1 Answers1

2

Apart from the fact that mysql functions are deprecated and removed in PHP 7, the mysql_insert_id will work just fine, since that the function returns the last generated id using your connection. In case another record is created by someone else, then you will get the last generated id using your connection and not the last inserted id into that table.

Try using PDO or MySQLi instead.

Alfasatwi
  • 62
  • 11