I hope I am on topic here (It does have to do with web design, after all). I have noticed that in a MySQL database, the 'id' field just keeps its numbers for every record, with the backdraw that, whenever you remove (a) record(s) from the table, you get your list numbered as eg. 1,5,6,7,12,25 etc. This makes it a bit difficult of keeping track of the number of records in your table. Is there a way of achieving (either in PHPMyAdmin or through the use of PHP) that the id field in your table gets sort of 'refreshed and updated' so you have a clearer view on the number of records in your database? What I mean is, imagine the highest number in your 'id' field is 221, that does not necessarily mean you have 221 records in your table, as some records may have been removed. So, if anyone can help, it would be very much appreciated. Thanks in advance.

Reply

There are other ways to tell how many rows there are in a database than changing all the IDs. The purpose of a unique ID is that it is unique (obviously) and permanent. Changing it can cause all sorts of problems with scripts.

If you go into phpMyAdmin and click on the database you want information for, then look at the list of tables in the middle, you will notice a column labeled Records. That is a count of how many rows are in each table.

If that doesn't take your fancy then try the following SQL:

That returns an integer, which is the number of rows in the table.

Changing all the IDs is never a good idea.

Reply

Changing the ID field of a Table element messes with the links between an element and those above/below it in the schema as Rvalkass suggests. Particularly if the ID is an index, which is often the case.
to re-new the ID numbers, you would need to read the table one row at a time and find all the rows 'connected' to it inside all the other tables above and below it with an eye toward changing the indexed values as you change the element's id field. Rather a complicated mess could result if you arbitrarily alter ID values.

Use the Count function to find out the number of rows as per rvalkass' suggestion.

Reply

Thanks for your comments, gentlemen.
One more thing: is it possible to display that 'Record' field?
Ihave seen the SQL Query

rvalkass posted, but not 100% sure how to display that (probably using PHP).
You see, I have put a petition on my site, everything works perfectly and I display the result of the petition (bar the people's email addresses) on my site by using coding that was made with PHPRunner.(A program that generates PHP code to do things with a MySQL database, I used the 'printer friendly' page to display the list).

However, I would like to put something at the top such as "xxx people have already signed the petition" or to put a number (1,2,3,..... all nicely numerically ordered) next to every name, although the first solution might make more sense, ie.saying how many people signed, and have this automatically updated every time someone new signs.

As you can see, my knowledge of PHP is not yet strong enough, which is why I have to rely on programs like PHPRunner and the likes.

Anyway, thanks for your explanations you have sent in so far, and apologies for my ignorance.

 

 

 

Reply