$query = "SELECT COUNT(*) as row_count FROM profiles";
$result = mysql_query($query) or die(mysql_error());
$total = mysql_fetch_object($result);
$counter = $total->row_count;

The code above will tell me how many rows there are that ain't been deleted

but how do i find out how many rows there are altogether?

in admin php it says next auto increment number is 18..
but the above script it returning 6 because 12 rows have been deleted

so how do i get the script to say 18 ?

thanks

sharon

What do you want to get?

Currently you are selecting the number of rows in the table. If you need the next autoincrement value (the value which will be assigned to the autoincrement field of record during next insert statement) you should select max(autoincrement_field)+1 instead.

Originally Posted by mtishetsky

What do you want to get?

Currently you are selecting the number of rows in the table. If you need the next autoincrement value (the value which will be assigned to the autoincrement field of record during next insert statement) you should select max(autoincrement_field)+1 instead.

Thanks

that worked perfect

$query = "SELECT MAX(ID) as row_count FROM albums";
$result = mysql_query($query) or die(mysql_error());
$total = mysql_fetch_object($result);
$counter = $total->row_count;

Shaz x

But, as you say, if there are deleted records, the number you expect would not be right.

SHOW TABLE STATUS LIKE 'table_name'

Would return:

+----------------+
| Auto_increment |
+----------------+
|       99       |
+----------------+

Dammit, I missed this case.

Sharon, why do you need the next value for auto_increment field? Isn't it easier to get the id of last inserted record with mysql_insert_id() ?

Hiya

i needed it because i am writing a site and needed to show the last 6 entries for something going backwards from the last entry

but now i just found out there is

$result = mysql_query("SELECT * FROM this WHERE that = 1 ORDER BY the other DESC") or die(mysql_error());
while($riw = mysql_fetch_array( $result )) {do thisandthat}

lol much quicker and less messy lol

before i wrote a load of lines of code when i could just as easy done the above..

i am a php newbie so learning still