MySQL & PHP > inserting rows and incrementing cols

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Tecktron
Forum Newbie
Posts: 3
Joined: Wed Dec 24, 2003 9:29 am
Location: San Jose, CA. USA

MySQL & PHP > inserting rows and incrementing cols

Post by Tecktron »

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
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

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"];
Tecktron
Forum Newbie
Posts: 3
Joined: Wed Dec 24, 2003 9:29 am
Location: San Jose, CA. USA

Post by Tecktron »

Cool!

Thank you much! :D

--Tecktron
Post Reply