0

When a successful register happens there is an Insert into members table. It has an id filed auto increment.

//Create INSERT query
$qry = "INSERT INTO members(email, passwd) VALUES('$email', '$password')";
$result = mysql_query($qry);

Because I need to save some of the input in another table named companies, I find the total records ( that means the last ID)

//find lastid from members
$lastid = mysql_fetch_array(mysql_query("select count(id) as lastid from members"));
$lastid = $lastid[lastid];

and save it into the table companies.

//Insert lastid to Companies
$insertit = mysql_query("INSERT INTO companies( id, name) VALUES('$lastid', '$cname' )");

This is working successfully but is there a better way of doing this ? I have removed some of the fields to give a better more to the point example.

andrewsi
  • 10,807
  • 132
  • 35
  • 51
asotshia
  • 121
  • 1
  • 9
  • I can't provide a full answer, but you may want to look into using `LAST_INSERT_ID()`. More info here: http://stackoverflow.com/questions/560783/the-equivalent-of-sqlserver-function-scope-identity-in-mysql – WT_W Oct 27 '11 at 01:43

3 Answers3

3

Use mysql_insert_id to get the last inserted id.

"select count(id) as lastid from members" does not mean the last id for many cases.

xdazz
  • 158,678
  • 38
  • 247
  • 274
1

Assuming members.id is an AUTO_INCREMENT field you can cut a bit out using mysql_insert_id():

//Create INSERT query
$qry = "INSERT INTO members(email, passwd) VALUES('$email', '$password')";
$result = mysql_query($qry);

//find lastid from members
$lastid = mysql_insert_id();

//Insert lastid to Companies
$insertit = mysql_query("INSERT INTO companies( id, name) VALUES('$lastid', '$cname' )");
Clive
  • 36,918
  • 8
  • 87
  • 113
0

Counting the total records in the table does not guarantee that it is the last id. A better option is get the max value of the id column:

SELECT MAX(id) AS lastid FROM member

But the best option I think is to use the mysql_insert_id function:

$qry = "INSERT INTO members(email, passwd) VALUES('$email', '$password')";
$result = mysql_query($qry);
$lastid = mysql_insert_id();
Luistar15
  • 1,699
  • 3
  • 14
  • 16
  • Getting `MAX(id)` is never a better option. They both are terrible and should never be used at all. – zerkms Oct 27 '11 at 01:55