FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


Update 2 rows, restrict by group field





thighmister
Worthless noob here. I cannot seem to make this one change that seems so simple.

Table like so:

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

When user 4 submits form, it updates the "next" field in user 4's row to 0, and the "next" field in user 5's row to 1, like so:

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

I would like it to restrict updates by "group" field, so that user 4's form submit results in:

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

Here is the script:

Code:
if($_POST['doWork'] == 'Done') {

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

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

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'");
     


It goes to the lowest id row when the highest id row has been reached, which is almost what I want: I would like it to stop at the highest id IN GROUP ALPHA (or whichever group the logged-in user belongs to) and go back to the lowest id in the same group.

I'm thinking:

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


But how/where to tell it what "$group" means?

There's a hidden GET too:

Code:
<form name="form1" method="get" action="admin.php" id="form1">
     <input type="hidden" name="qoption" value="guys">
     <input name="doSearch" type="hidden" id="doSearch2" value="List">
   </form>   </td>
      </tr></table>
     <?php
    

      if ($get['doSearch'] == 'List') {
     if($get['qoption'] == 'guys') {
     $cond = "where `id`='$_SESSION[user_id]'";
     }
   
    
     if($get['qoption'] == 'guys') {
     $sql = "select * from users $cond";
     }
     else {
     $sql = "select * from users where `id` = '$_SESSION[user_id]'";
     }


Should I specify the "group" requirement there instead? But how?

One of the main reasons I am doing this project is to learn, and so far it has been very instructive. Many thanks for any suggestions.
jmraker
I think you'd need to have to make 2 queries
1. gets the next user
2. gets the first user, that's run when there are no more users

1. would be like (where user 3 edits the record)
Code:
SELECT * FROM users WHERE id>4 AND group="alpha" ORDER BY id LIMIT 1

if return nothing it should be the last user and to get the id of the first user in alpha
2. would be like
Code:
SELECT * FROM users WHERE group="alpha" ORDER BY id LIMIT 1


Then change the current user's next value to 0 and use the "id" that the query returned to update the next record so next=1.
Code:
UPDATE users SET next=0 WHERE id=$currentUserID;
UPDATE users SET next=1 WHERE id=$nextUserID;

I would expect next would always be 1 if there's only one user in the group, where if $currentUserID = $nextUserID you might as well do nothing.
Fire Boar
Given that you don't know the group of the current ID, I'd suggest the following approach:

- Get the IDs of all users in the current user's group.
- Find the one that comes directly after the current (wrapping around if necessary).

The query for the first step can be written as follows:

Code:
SELECT u1.id FROM users u1 INNER JOIN users u2 ON u1.group=u2.group WHERE u2.id=$id ORDER BY u1.id


I'll use your example above to illustrate what this is doing. The INNER JOIN alone connects the table "users" to itself, establishing a relation between every entry (6*6 = 36 rows). The ON clause adds the condition that the group field must be the same on either side (4*4 + 2*2 = 20 rows). Finally, the WHERE clause makes sure that the ID on the right is the one we expect (leaving 4*1 + 2*0 = 4 rows matched). The end result is

Code:
+-------+---------+----------+-------+---------+----------+
| u1.id | u1.next | u1.group | u2.id | u2.next | u2.group |
+-------+---------+----------+-------+---------+----------+
|   1   |    0    |  alpha   |   4   |    1    |  alpha   |
|   2   |    0    |  alpha   |   4   |    1    |  alpha   |
|   3   |    0    |  alpha   |   4   |    1    |  alpha   |
|   4   |    1    |  alpha   |   4   |    1    |  alpha   |
+-------+---------+----------+-------+---------+----------+


from which only u1.id is selected.
Related topics
Build an online dictionary by PHP/MySQL
751 Useful Windows XP Files
SQL Basics
SQL Tutorial
An Example to mySQL class
Opera 9.0 Technology Preview 2
phpBB 2.0.20 RELEASED
X360: PGR3 update=new cars
MS Word 2002: How to update fields on save? VB Macro?
mail system......an mysql....
Jesus Camp
My Weird Phobia - prepare to laugh!
How to update multiple rows in mysql with one query?
I'm still a noobie at home networks, please help...
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.