problems incrementing inbetween a rows

[anilvaghela]

hello all!

 

ok i am having a problem in inserting a tmp table data into the main table where it increments the order_id from where ever its been pointed to... to go more further here below i explain of the process.....

 

1 - Main Table has same columns as Temp Table

 

In the main table there is index_id which is auto_increment and order_id which is not but has to be in order from 1 - 2000 for example.....

 

2 - from the temp table i could have 1 record or 50 records either way the results in the temp table are in order and need to be in that set order through out the process....

 

3 - I am able to in php start of with selecting the order_id where the new data will need to sit for example  the data may need to sit under order_id - 60  so there fore there will be rows after 60 and this is where the new rows from temp table will sit......

 

but what problem i am having is when i insert the data under 60 which i use the a @myorder +1 all the data from the temp table inserts there but it does increment by 1 on all the records it shows 61 on all the records.

 

so this is where i need to know how to actually make all the rows increment and if there is only 25 records then after order_id 85 the other records also get updated!

 

i hope you guys n girls understand what i am trying to do here....

 

here is some code of process....

 

 

step 1 :

 

this sets the order_id where the new data will sit....

$sql = sprintf("SELECT @myorder1 := order_id FROM ref_db WHERE name_id = '%s'",mysql_real_escape_string($name_id_add));
mysql_query($sql,$conn) or die(mysql_error($conn));

$sql = sprintf("UPDATE ref_db set order_id = order_id + 1 Where order_id > @myorder1");
mysql_query($sql,$conn) or die(mysql_error($conn));

$sql = sprintf("UPDATE ref_db set order_id = order_id where order_id > @myorder1");
mysql_query($sql,$conn) or die(mysql_error($conn));

step 2:

 

this here inserts the data from the temp table into the main table

mysql_select_db($database_mysql, $mysql);
$query_tmptbl = "SELECT * FROM tmpmove ";
$tmptbl = mysql_query($query_tmptbl, $mysql) or die(mysql_error());
$row_tmptbl = mysql_fetch_assoc($tmptbl);
$all_tmptbl = mysql_query($query_tmptbl);
$totalRows_tmptbl = mysql_num_rows($all_tmptbl);


do {	
$sql = sprintf("INSERT INTO ref_db(order_id, name_id, name_ch, parent_id, level, agg, pseudo, ps_id, replevel, class, audit, tdate, user_id, checked_id, assign_id) VALUES
(@myorder1+1, '%s', '%s', '%s', '%s', '%s','%s', '%s', '%s', '%s', '%s', NOW(),'%s', '%s', '%s')",
mysql_real_escape_string($row_tmptbl['name_id']),
mysql_real_escape_string($row_tmptbl['name_ch']),
mysql_real_escape_string($row_tmptbl['parent_id']),
mysql_real_escape_string($row_tmptbl['level']),
mysql_real_escape_string($row_tmptbl['agg']),
mysql_real_escape_string($row_tmptbl['pseudo']),
mysql_real_escape_string($row_tmptbl['ps_id']),
mysql_real_escape_string($row_tmptbl['replevel']),
mysql_real_escape_string($row_tmptbl['class']),
mysql_real_escape_string($row_tmptbl['audit']),
mysql_real_escape_string($row_tmptbl['user_id']),
mysql_real_escape_string($row_tmptbl['checked_id']),
mysql_real_escape_string($row_tmptbl['assign_id']));
mysql_query($sql) or die(mysql_error());

} while ($row_tmptbl = mysql_fetch_assoc($tmptbl));

BUT it does not increment +1 on all and only does the same number on all

 

 

 

 

PLEASE HELP!!! THANKS!!

 

 

Anil Vaghela

 

 

 

 

 

 

 

[fenway]

Sorry, I don't follow.