Update row using LIMIT 1, but then go to top after last row

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
troutmissile
Forum Newbie
Posts: 7
Joined: Mon Feb 07, 2011 5:16 pm

Update row using LIMIT 1, but then go to top after last row

Post by troutmissile »

My table is:

id | next
_________
1 | 1
2 | 0
3 | 0
4 | 0

I update it with:

Code: Select all

if($_POST['doApprove'] == 'Done') {

mysql_query("update users set next='1' where id > '$id' LIMIT 1");

mysql_query("update users set next='0' where id='$id'");
So if user with id 1 is logged in and submits the form, it changes his "next" to 0, and user id 2's "next" to 1, like so:

id | next
_________
1 | 0
2 | 1
3 | 0
4 | 0

It works. But given a total of 4 users, I want id 1's "next" to be updated to 1 when id 4 submits the form (as well as id 4's row being updated to 0). In other words, it goes back to the beginning and changes the first user's row when the last user submits.

Many thanks for any clues!
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Re: Update row using LIMIT 1, but then go to top after last

Post by Mordred »

That's a strange thing to want to do, but who am I to stop you from shooting your own foot ;)

1. Query for the last id in the table
2. If $id is the same:
2.1. Query for the first if in the table
2.2. Use another UPDATE query for the next=1 operation
3. Else, use your current solution

1: SELECT id FROM users ORDER BY id DESC LIMIT 1
2.1: SELECT id FROM users ORDER BY id ASC LIMIT 1
2.2. UPDATE users SET next=1 WHERE id = $first_id LIMIT 1
troutmissile
Forum Newbie
Posts: 7
Joined: Mon Feb 07, 2011 5:16 pm

Re: Update row using LIMIT 1, but then go to top after last

Post by troutmissile »

If you knew my foot you would understand why I want to shoot it.

Well I ended up using

Code: Select all

if($_POST['doApprove'] == 'Done') {

mysql_query("update users set next='1' where id > '$id' LIMIT 1");

mysql_query("update users set next='0' where id='$id'");
		
$to_email = "select user_email from users where id > '$id' LIMIT 1";	

list($highest) = mysql_fetch_row(mysql_query("select id from users where id >'$id'"));	

if(empty($highest)) {
	mysql_query("update users set next='1' where id > 0 LIMIT 1");
	mysql_query("update users set next='0' where id='$id'");
	mysql_query("select user_email from users where id > 0 LIMIT 1");
and it works.

Now I am just trying to restrict the updates to rows that share a common var in another field called "group". So if I have:

id | next | group
______________
1 | 0 | alpha
2 | 0 | alpha
3 | 0 | alpha
4 | 1 | alpha
5 | 0 | beta
6 | 0 | beta

I want to make sure that when user id 4 submits the form, it will look like this:

id | next | group
______________
1 | 1 | alpha
2 | 0 | alpha
3 | 0 | alpha
4 | 0 | alpha
5 | 0 | beta
6 | 0 | beta

and NOT like this:

id | next | group
______________
1 | 0 | alpha
2 | 0 | alpha
3 | 0 | alpha
4 | 0 | alpha
5 | 1 | beta
6 | 0 | beta

...because the logged-in user id 4 is in the alpha group. (He's wicked smaht).

It seems so simple... I guess I'm too tired. There's a hidden get part of the form too, like this:

Code: Select all

if ($get['doSearch'] == 'List') {
	  if($get['qoption'] == 'incoming') {
	  $cond = "where `id`='$_SESSION[user_id]'";
	  }
	 
	  
	  if($get['qoption'] == 'incoming') { 
	  $sql = "select * from users $cond"; 
	  } 
Should I specify the "group" restriction there, or in the POST part above?
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Update row using LIMIT 1, but then go to top after last

Post by requinix »

I do recall mentioning using mysql_affected_rows to check if there were any updates made, and that if there weren't it probably meant you were at the end of the list and would need to resume at the beginning.
troutmissile
Forum Newbie
Posts: 7
Joined: Mon Feb 07, 2011 5:16 pm

Re: Update row using LIMIT 1, but then go to top after last

Post by troutmissile »

My code already sends it back to the beginning. The problem is not making it go back to the top when it reaches the last row in the table, but making it go back to the lowest id in group alpha when it reaches the highest id in group alpha.
Post Reply