Is there a way using MySQL to select the last row that was inserted into? Basically I need the number from a primary key column to know how many rows there are. So alternatively, I could also use a way to count the rows in a column. But I'm think that counting the rows might be a somewhat big query for a large table.

? :)

http://php.net/mysql_insert_id

Try using this

$q = mysql_query(SELECT * FROM table);
$r = mysql_num_rows($q);

$r will be the number of rows in "table"

Even faster than mysql_num_rows:


$dbquery=mysql_query("SELECT COUNT(id) AS somename FROM table");
while($query=mysql_fetch_array($dbquery)) {
$somename=$query['somename'];
}


Then $somename will contain the amount of lines in the specified table. Of course you have to replace "id" and "table" to suit your database structure.

SilnZ: Your SELECT * FROM table will kill the script if you run it on a large table.

A count(id) or a mysql_num_rows isn't good enough and you should use

mysql_insert_id()

Why?

Because if your table is autoincrement and you delete an entry part way down a count(id) will give you the wrong id number.

However saying that, you can use SQL to get the correct answer with

SELECT MAX(id) FROM tablename

Of course that is a assuming no one adds another row in the millisecond between your last SQL statement and this one

sorry, just re-read your post. if all you want to do is count the number of records in a mysql table, use

select count(*) from table;

the above i don't think is correct. count will count the number of records period, it doesn't matter what the auto_increment number if (or if it has gaps due to deletes, etc...)

also, as a general rule of thumb, don't base any sort of calculation on the auto_increment, it's not meant to be a nice pretty flowing pattern, it will have gaps and etc.. and that's fine, you don't have to fill it. the point of it is to give each record a unique identifier, not to have it go from 1-1000.

mysql_insert_id($mysql_link);

is good for getting the last insert ID. example use is if you INSERT a record, then need that unique_id to use as a foreign key in a separate table.

Originally posted by i am a
the above i don't think is correct. count will count the number of records period, it doesn't matter what the auto_increment number if (or if it has gaps due to deletes, etc...)

Yes that is true, but the original question had a flaw. Getting the last insert id wouldn't have got you the total of number of rows in the table as you can remove entries beneath. However it is very useful to get the last inserted id for certain things

Wait, a Primary Key/Auto Increment doesn't update itself to be flowing when you delete something in a table? I was under the impression that it did.

I need to pick a random row from this table, so I was going to use the primary key column to get to a row after using mt_rand(1,$numOfRows) to get a number. That's why I needed the number of rows, but now I'm thinking this won't work anyway. So is there another way I can go about this script?

Originally posted by Falco1199
Wait, a Primary Key/Auto Increment doesn't update itself to be flowing when you delete something in a table? I was under the impression that it did.

I need to pick a random row from this table, so I was going to use the primary key column to get to a row after using mt_rand(1,$numOfRows) to get a number. That's why I needed the number of rows, but now I'm thinking this won't work anyway. So is there another way I can go about this script?

Do something simple like:

select * from table order by rand() limit 1

Ensure that you have a sufficiently new MySQL and that should be okay...

yes build it rand() function is the way to go if you just wanted a random row