problems with num_rows

Anon

I have a problem with num_rows (well I think)

I have a player table with about 1700 ID's
there aren't 1700 players/rows though, more like 1600 rows (some players have been deleted, say for example PlayerID=1580).

The thing is, when I try to do a query (as described below), He counts the rows and the player gets ID 1601 instead of 1701 (the previous player has ID=1700, so the new one should get 1701, and not 1601, which the player gets due to MySQL counting the rows.

How can I overcome this problem?

----if needed --- here's a further description---------
This is a bit complicated for me to describe, but I will try my best.

I have 2 tables (Player & Stats)

I have a form that adds a player to the MySQL database and after the INSERT player query is done, I can directly choose (on the following php-page) to add the player to a team and insert his stats. This is all done by INNER JOIN etc..

However, this is not the problem.
The problem is that when I add a player he gets say PlayerID=1710, so on the following page when I insert player to the table stats, I use his PlayerID in the query.

The stats table has the following essential columns (PlayerID, TeamID)

thanks a bunch for any assistance you can offer me!

Anon

Can you use auto_increment for the playerID assignment and use mysql_insert_id() to get the last ID inserted to use in your next page?

scoppc

Well, u should change the way you get the last player's ID.
Instead of counting the number of rows (which is obviously gives you 1600 or something less than the actual ID), why don't you get the ID of the last person.

U could probably use this SQL statement.

$command="SELECT id FROM player ORDER BY id DESC LIMIT 1";

hope it helps.

Anon

I think this is not really good idea.
Imagine the situation when the last ID in the players table is 1700

you insert new row and it gives ID = 1701

for example you use it 2 generate link

see "SOME"player on

href="yoursite.com/yourscript.php?ID=$ID"

user sees this link and copy it for future use

some day further you delete row with id=1701

now the last is 1700 again

you insert new row it gives id=1701 again

What we have? the same link with..wrong content.
I've wasted the hell lotta time with bugzzz when wroting forums with posts IDed this manner

I use where it is possible AUTO_INCREMENT.
Where it is not I use file last.id contains the last id inserted. Everytime I insert new row, I make ++ for content of this file.