Hi,
I've got a bit of a stumper out there for anyone who'd like to tackle it. I have a table with say 4 rows. Each row has an index that autoincrements as set in mysql (rowid). I have another col. in each row that has another ID that is not the rowid, but something set by the user (userid). Now this is where things gets a bit tricky. So far the table looks like this:
rowid - userid
1 - 1
2 - 2
3 - 3
4 - 4
Now if I want to insert a row, but I want the userid to be 3 and the userid that was 3 to auto increase (as far as the user is concerned) to 4 and 4 to 5. So the table will now look something like this:
rowid - userid
1 - 1
2 - 2
3 - 4
4 - 5
5 - 3
This seems easy enough, but lets say that I wanted to insert into userid 35 in a 148 row table, so 35 becomes 36, 36 to 37 ... 147 to 148, 148 to 149. Now it seems a bit difficult. I could just do it in a loop sending a new query each time, but I'm trying to do this in as few queries as I can.
Any help, please?
Thanks,
--Tecktron
MySQL & PHP > inserting rows and incrementing cols
Moderator: General Moderators
It can't be implemented in a less then 2 queries, I guess:
Code: Select all
//first
update sometable set userid=userid+1 where userid>$_POSTї"userid"];
//then
insert into sometable set userid=$_POSTї"userid"];