Utlizing the ID field

[Shalazar]

I was playing around with a table where the ID field is listed as the PRIMARY KEY.[nbsp][nbsp]I understand that PRIMARY KEY is used so that no two ID fields contain the same number.

I was experimenting with two options to print out the number of rows.

1 --

$ID = mysql_insert_id ($result)

where $result is taken from the insertion of the variable into the new row.

Now, assuming the table remains unmodified, you can do something like:

print ("Your chances of winning are 1 in $ID");

Outputting something like "Your chances of winning are 1 in 19."

However, if rows are deleted in the interim, the next person would receive "Your chances of winning are 1 in 20." even though sevreal rows have been deleted.

Is there a way to alter each row's ID if deletion occurs? How about resetting the ID for the entire table without starting from scratch again?

Or is the better way to do it as follows?

2 --

$SelectAll = "SELECT * FROM table_name";
$Results =[nbsp][nbsp]MySQL($db,$SelectStr);
$RowCount = MySQL_NUMROWS($Results);[nbsp][nbsp][nbsp][nbsp][nbsp]
print ("Your chances of winning are 1 in $RowCount");

This works like a charm, even when deleting rows.[nbsp][nbsp]

I understand the majority of this code -- but could somebody better explain what values get placed into the $SelectAll?[nbsp][nbsp]When I run SELECT * FROM table_name from the mysql command line, I figure the data being send is the number of rows.

I don't understand the middle line, so that's what is most important for me to have explained.[nbsp][nbsp]What does the MYSQL() command do with those variables?

Thanks again.

[urban]

What language are you using?

Since you have a primary key on the ID field and you know that the chances are 1 out of the TOTAL_NUMBER_OF_ROWS, you would be better off using a query like:


select count(*) from table_name;


This will return one record containing the total number of rows in the table.[nbsp][nbsp]Thus, the chances of winning are 1 out of the returned result set.[nbsp][nbsp]This should be much faster and more efficient than selecting all of the rows (and columns) from the table.

I'm guessing that your primary key is an AUTO_INCREMENT field.[nbsp][nbsp]If so, the deleted values will not be reused by MySQL and if you really needed to reuse them it would require you handle the primary key IDs in your program.[nbsp][nbsp]For example, you could specify the primary key as an integer and then examine each ID value and use the highest value as your next ID.[nbsp][nbsp]This is not that uncommon, but it is far less efficient and requires quite a bit more code...

[Justin]

Select * should pretty much never be used for any reason, unless you actually need every column from every row in the table...

But aside from that, here is the easiest way in PHP (my personal comments in <font color=#FF0000>red</font>:


$result = mysql_query (&quot;select count(ID) from table&quot;); <font color=#FF0000># Only need to count one field</font>
if ($result) $total = mysql_numrows ($result); <font color=#FF0000># Never assume the query was successful - always check $result</font>
if ($total) { <font color=#FF0000># again, checking that the count was &quot;found&quot;</font>
[nbsp][nbsp] $MyCount = mysql_result ($result, 0);[nbsp][nbsp]<font color=#FF0000># We want the first (and only) result - which is the count</font>
}


Hope this helps.

------------------
Justin Nelson
FutureQuest (http://www.FutureQuest.net/index.php) Support
[This message has been edited by Justin (edited 02-22-00@11:19 am)]

[urban]

Select * should pretty much never be used for any reason, unless you actually need every column from every row in the table...

Good advice and I agree...

However, the SELECT COUNT(*) does NOT return, every row.[nbsp][nbsp]In fact, a count(*) with no WHERE clause (assuming a single table and not a join) is optimized by MySQL to return the number of records in the table very quickly.

(select *) != (select count(*))

In the case (as stated) using count(*) or count(id) will likely make absolutely no difference since ID is the primary key and MySQL will optimize the count(*) to use the primary key anyway.[nbsp][nbsp]