Page 1 of 1

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

Posted: Mon Feb 14, 2011 7:01 am
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!

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

Posted: Mon Feb 14, 2011 12:32 pm
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

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

Posted: Mon Feb 14, 2011 5:11 pm
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?

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

Posted: Tue Feb 15, 2011 12:54 am
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.

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

Posted: Tue Feb 15, 2011 6:48 am
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.