Page 1 of 1

Getting next number in line before insert?!?

Posted: Wed Jan 21, 2009 11:47 am
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.

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

Posted: Wed Jan 21, 2009 12:04 pm
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.