loop within a loop for updating db record

Question

I have a deals table with the following data:

dealID   |   date    |   followUP  |  user
1        |2012-10-15 |    Yes      |
2        |2012-12-24 |    Yes      |
3        |2013-01-05 |             |
4        |2013-02-02 |    Yes      |
5        |2013-02-02 |    Yes      |

And a users table with my users list

userID    | name
1         | john
2         | eric
3         | anne

What I would like to do is to query the first table that has followUP set as 'YES' then from the result assign a user to them in sequence from the users table so my final deals table will look like this

dealID   |   date    |   followUP  | userID
1        |2012-10-15 |    Yes      | 1
2        |2012-12-24 |    Yes      | 2
3        |2013-01-05 |             |
4        |2013-02-02 |    Yes      | 3
5        |2013-02-02 |    Yes      | 1

I know its a loop but for some reason i cant figure out how to setup the second loop to assign the value of the users. Any help would be appreciated.

How will you decide which user will be assigned to which deal? You do not have any userids in deals table ? — cartina
– cartina, Commented Jan 16, 2013 at 4:38
You want to assign a user to the deals that have followUP = 'Yes' in sequential order based on the userID? — user985189
– user985189, Commented Jan 16, 2013 at 4:50
for now just assign the userID in sequence to the users table. So far the only thing ive been able to do is update the deals table but only for one user, I cant figure out how to loop the userID while the deals update query is looping as well. — Renee Cribe
– Renee Cribe, Commented Jan 16, 2013 at 4:51
So, your userID in the "user" table is not auto incremental?? — user985189
– user985189, Commented Jan 16, 2013 at 4:52
my IDs are all AI. Yes, I would like to update the deals table that has followUP = 'Yes' in sequential order based on the userID — Renee Cribe
– Renee Cribe, Commented Jan 16, 2013 at 4:56

Majid Fouladpour · Accepted Answer · 2013-01-16 05:07:48Z

2

First get your user IDs into an array so it looks:

$uids = array(1, 2, 3);

Then, when reading the records from deals table compose update queries like so:

$follow_up_user_idx = 0;
$update_queries = array();
while($row=mysqli_fetch_assoc($result)) {
    // ... do whatever you need to
    if($row['followUP'] != 'Yes') continue;
    $update_queries[] = "UPDATE `deals` SET `user` = '" . $uids[$follow_up_user_idx] . "'
                         WHERE `dealID` = '" . $row['dealID'] . "' LIMIT 1";
    $follow_up_user_idx++;
    if($follow_up_user_idx > count($uids) - 1) $follow_up_user_idx = 0;
}

Now you have all update queries. Just execute them:

foreach($update_queries as $uq) {
    mysqli_query($link, $uq);
}

edited Jan 16, 2013 at 5:07

answered Jan 16, 2013 at 5:01

Majid Fouladpour

30.4k21 gold badges80 silver badges129 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bhavik Shah · Accepted Answer · 2013-01-16 05:04:28Z

You will have to follow a series of steps to achieve what you have mentioned.

1) Fetch all the records from database where followUP is 'Yes'.
Lets say that first table is deals. So, fetch all records where followUP is 'Yes'.
Lets say, you have result in $deals_details.

2) Fetch all the users(name) from database.
Lets say that second table is users. So, fetch all users.
Lets say, you have all users' names in $users_details array.
Get a count of total users in system in seperate variable say $users_count.

3) Loop through $deals_details and assign each user one-by-one sequentially from $users_details.

$i = 0;
foreach($deals_details as $keyDD => $valueDD){
    $user = $users_details[$i];
    $i++;
    if($i == $users_count)
        $i = 0;
    $query = "update `deals` set user = '".$user."' where dealID = '".$valueDD['dealID']."'";

    //fire query
    //$link is connection variable.
    mysqli_query($link, $query);
}

suseelan · Accepted Answer · 2013-02-07 06:07:49Z

Table user +---------------+---------------+------------+ | user_id | username | user_level | +---------------+---------------+------------+ | 1 | superadmin | admin | | 2 | subadmin | admin | | 3 | team1 | team | | 4 | team2 | team | | 5 | team3 | team | | 6 | customer1 | customer | | 7 | customer2 | customer | | 8 | customer3 | customer | | 9 | customer4 | customer | +---------------+---------------+------------+ Table complaint: +---------------+---------------+------------+ | complaint_id | complaint | user_id | +---------------+---------------+------------+ | 1 | os issue | 7 | | 2 | USB issue | 8 | | 3 | OS currepted | 7 | | 4 | HD issue | 9 | | 5 | DVD issue | 6 | | 6 | SW problem | 9 | | 7 | Network issue| 9 | | 8 | system issue | 6 | +---------------+---------------+------------+ Table assign_work +---------------+------------+ | complaint_id | user_id | +---------------+------------+ | 1 | 3 | | 2 | 4 | | 3 | 5 | | 4 | 3 | | 5 | 4 | | 6 | 5 | | 7 | 3 | | 8 | 4 | +---------------+------------+

When customer raise the complaint data should save in complaint table also that last complaint_id should save in assign_work table at the same time
user_id also save sequencially, fetch from user table who are the team that person id only. I m new in ph please any one help me.

eggyal · Accepted Answer · 2013-01-16 05:29:17Z

This may be a poor design decision. Without knowing more about your application, I'd be tempted to wait for a user to become available/request a deal, then assign to them (from a queue) the next deal requiring follow-up. Even if you have good reasons to assign users before they are "available", you might consider doing so upon deal creation/modification based on some appropriate business logic (e.g. maintaining a queue of users to be assigned to the next deal).

Indeed, there's no particularly nice way of performing this operation with a simple UPDATE statement. One way to accomplish it purely within the database would be to use a stored procedure (it's not especially concurrency-safe though, as changes to the Users table between closing and reopening the _curUser cursor may cause undesirable effects; one might instead copy the Users to a temporary table for the purposes of this procedure, depending on your desired logic):

DELIMITER //

CREATE PROCEDURE assignDeals() BEGIN
  DECLARE _userID, _dealID BIGINT UNSIGNED;
  DECLARE _done BOOLEAN DEFAULT FALSE;

  DECLARE _curUser CURSOR FOR
    SELECT   userID
    FROM     users
    ORDER BY userID;

  DECLARE _curDeal CURSOR FOR
    SELECT   dealID
    FROM     deals
    WHERE    followUP = 'Yes'
    ORDER BY dealID
    FOR UPDATE;

  DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET _done := TRUE;

  PREPARE stmt FROM 'UPDATE deals SET userID = ? WHERE dealID = ?';

  OPEN _curUser;
  OPEN _curDeal;

  readDeal: LOOP
    FETCH _curDeal INTO _dealID;
    IF _done THEN
      LEAVE readDeal;
    END IF;

    FETCH _curUser INTO _userID;
    IF _done THEN
      SET _done := FALSE;
      CLOSE _curUser;
      OPEN  _curUser;
      FETCH _curUser INTO _userID;
      IF _done THEN
        SIGNAL SQLSTATE VALUE '45000' SET
          MESSAGE_TEXT = 'No users';
        LEAVE readDeal;
      END IF;
    END IF;

    SET @userID := _userID, @dealID := _dealID;
    EXECUTE stmt USING @userID, @dealID;
  END LOOP readDeal;

  CLOSE _curUser;
  CLOSE _curDeal;

  DEALLOCATE PREPARE stmt;
END//

DELIMITER ;

See it on sqlfiddle.

Collectives™ on Stack Overflow

loop within a loop for updating db record

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related