How to make the "+1" in "'$id'+1" go back to lowest id when $id is highest one?

[thighmister]

Stupid individual here.  Hello! 

 

I respect the power and depth of php, and I want to learn, but to be honest I also want a quick fix for this one project so I will look cool to my roommates.  And THEN I plan to start at the beginning and learn syntax.  Just let me rub a little on my teeth, baby, come on.

 

I have a mysql db with table 'users,' columns 'id' (int, auto-increment) and 'next' (int).

 

The idea is that there will be four users.  When the user whose 'next' column has a '1' in it hits the "Done" button, the '1' in his 'next' column changes to '0' and the '0' in the 'next' column of the user with the next-highest id number changes to '1', like so:

 

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

 

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

 

mysql_query("update users set next='0' where id='$id'");       

 

...and then

<input name="Done" type="submit" id="Done" value="Done">

 

 

It works!  And I am very proud of myself.  But when the user with id 4 hits "Done", and his 'next' still changes to '0', nobody's 'next' changes to '1', because, alas, there are only four users, and so there is no '$id'+1, is there.

 

And I was like testing it out for each user, really excited that it worked each time, and was really surprised when the user 4 dead end hit.  That's the level of comprehension you are dealing with here.

 

I would like it to go back to the user with id number '1' when number 4 hits "Done", and start from the top again. 

 

Is there any chance that by typing

 

SELECT MAX(id) FROM users

 

or

 

$sql = "SELECT count(*) FROM users";

         $result = mysql_query($sql, $conn) or die(mysql_error());

         $r = mysql_fetch_row($result);

         $max = $r[0];

 

I will make you think I am trying hard enough for you to help me?  I do not quite know what either of those things I just typed (well, copied/pasted/edited) means, but I almost know.  The way a dog almost knows it is looking in a mirror, and not at another dog.  I'm turning my head to the side really hard, and I almost get it.  But I am still a dog.  Throw me a bone?

 

I love you?

 

Spank me? 

 

I'm not... I don't really... have thought... shiny.

[requinix]

First, a simple though unrelated change:

update users set next='1' where id = (select id from users where id > $id order by id limit 1)

And as I said in another thread, you should be determining the "next" user by looking at something that isn't the ID number. auto_increment does not guarantee that two consecutive rows have two consecutive ID numbers, and in some circumstances the next ID will not be the previous + 1.

 

Now, take a guess at what you can do with mysql_affected_rows.

Protip: If the above query did not change any rows then you must* be at the end of the list...

 

* Actually that's not always the case, but I don't think you have to worry about that yet.

[thighmister]

Thank you!

 

I checked out mysql-affected-rows, and I see that it can return the number of affected rows, which I guess in this case would be two, but I can't see how to exploit that.  I would be grateful for any more hints, but I understand if I am just too noobish to be dealing with this stuff yet.

 

Btw-- It's weird, the code snippet you posted didn't change any user's 'next' to 1.  I must have left something out?

 

 

[fenway]

Sorry, I don't follow.