Getting next number in line before insert?!?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
jmansa
Forum Commoner
Posts: 81
Joined: Wed Aug 23, 2006 4:00 am

Getting next number in line before insert?!?

Post by jmansa »

I'm trying to figure out how to insert into a db table but first figure out what number the last record have depinding on a userid?!

Lets say that userid '1' have 5 records in this table with the order_number 1 to 5. Now I want to make the next record the user inserts to be number 6. The table have other users also with numbers starting from 1 and so on.... How do I look into the DB before inserting and exactly add the next number in line for that user?!?

As it is now I inserts the records like this:

Code: Select all

$result = $db->sql_query("INSERT INTO ".$prefix."_cat_user (uid,catid,background,color,admin,order_no) "
                                                       ."  VALUES('$userid','$my_random_id','$color','$textcolor','1','')");
As you can see I don't have any number for the field "order_no"

Thanks for any help.
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Re: Getting next number in line before insert?!?

Post by Burrito »

you could do it with two queries pretty easily or get 'fancy' and do it with one.

Code: Select all

 
$query = "INSERT INTO `myTable` (`firstField`,`numberField`) SELECT '".$_POST['firstfield']."',(MAX(numberField) +1) AS `theMax` WHERE `firstField` = '".$_POST['firstField']."'";
 
untested, but something like that should work.
Post Reply