please be gentle with me 1

[Sarky78]

I recently started to use MySQL and PHP and am having a few problems.

what i want to do is to get the largest value of a primary key value and add one to to it so that i have a unique record. at the moment the table that i am using is empty (this is a possibility) and this is the code that i have:

$strMax = "SELECT MAX(SubscriberID) as maxID FROM tblSubscribers";
$objCheck = mysql_query($strMax);

$recCount = mysql_num_rows($objCheck);
if ($recCount > 0) {
$dbMaxVal = mysql_result($objCheck, "maxID"
if (($dbMaxVal != 0) || ($dbMaxVal != "&quot) {
$maxIDVal = ($dbMaxVal + 1);
} else {
$maxIDVal = 1;
}
} else {
$maxIDVal = 1;
}

i have put in the mysql_num_rows line in as i get an error on the mysql_result line when the table is empty.

can anyone see anything wrong with this?
is there an easier way of achieving this?

cheers

Tony

 

[sleipnir214]

Using an auto-increment primary key is much preferrable. You could run into concurrency problems with your method.

Set your primary key to be auto_increment. Then let the database take care of increments.

Instead of:

INSERT INTO foo VALUES (45, "my", "data"

perform:

INSERT INTO foo (secondcolumn, thirdcolumn) VALUES ("my", "data"


That will get the data into the database with a new id for each line. Then you can use the PHP function mysql_insert_id () to find out what the last id number was, if you need it for inserting into another table.



About the concurrency problem I mentioned:
If two users hit the page within a small enough window of time, the second will get the max id before the first has had time to insert. Then the second user will then insert data into the table with the same id as the first. Depending on your table design, the second user's insert could generate an error. ______________________________________________________________________
My God! It's full of stars!
______________________________________________________________________

 

[KarveR]

I agree sleipnir214. ______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.