UPDATING table doesn't work

[Rouven]

Sorry, it's me again.
I've just noticed, that the ID isn't deleted when I delete someone from my list.

For example when I delete the member with ID 5, the next member I add to my database has ID 6.

[markl999]

When you delete an row with an autoincrement value then the value isn't 're-used'.
I.e if you have :
1 fred
2 barney
3 bill
4 john

then delete bill, you'll end up with :
1 fred
2 barney
4 john
and the next one inserted will be, for example :
5 harry

That's expected behaviour and the autoincrement value shouldn't really be important to your code.

[Rouven]

When you delete an row with an autoincrement value then the value isn't 're-used'.
I.e if you have :
1 fred
2 barney
3 bill
4 john

then delete bill, you'll end up with :
1 fred
2 barney
4 john
and the next one inserted will be, for example :
5 harry

That's expected behaviour and the autoincrement value shouldn't really be important to your code.

okay. But the one big disadvantage I see is, that you can't see how many entries there are in the table by the last given id.
So is there a way to count the rows in a table and give out the value?

[r937]

select count(*) from thetable

[Rouven]

select count(*) from thetable

<?
$allmembers = mysql_query("select count(*) from mitgliederdatenbank");
?>
Derzeit sind <? print $allmembers ?> Mitglieder in der Datenbank eingetragen.

What's wrong? It gives me "Resource id #4"

[r937]

sorry, i don't know php

perhaps you need to refer to the column, in which case you should use an alias name:

select count(*) as Mitglieder 
  from mitgliederdatenbank

[Rouven]

mhm. doesn't work either.

EDIT:

Got it. the correct code is:

<?php 
$allmembers = mysql_query("select count(*) from mitgliederdatenbank"); 
$allmembers = mysql_fetch_array($allmembers); 
$allmembers = $allmembers[0]; 
?>

[bdl]

mhm. doesn't work either.

EDIT:

Got it. the correct code is:

<?php 
$allmembers = mysql_query("select count(*) from mitgliederdatenbank"); 
$allmembers = mysql_fetch_array($allmembers); 
$allmembers = $allmembers[0]; 
?>

Right. You can't simply echo out the result of mysql_query(), that's the resource ID for the resultset between PHP & MySQL. In your case you can also use mysql_result() to grab the single record, e.g.

$count_result = mysql_query("select count(*) AS count from mitgliederdatenbank"); 
$allmembers = mysql_result($count_result, 0, 'count'); 

You'll note I prefer to not reuse the variable over; to me anyway, makes the script alot more clear as to whats going on if you assign a seperate variable to the result than to your actual count value. I'm also using a MySQL alias 'count' to refer to the resulting data. Otherwise you'd have to refer to it as COUNT(*) and that's just messy.