PDO SELECT and UPDATE loop

Question

I'm really bad with php loops like foreach and while, so I seek help.

I need to loop this code:

$loser = $pdo->prepare("SELECT `user` FROM `contest` WHERE `winner`= '0'");
$loser->execute();

$balance = $pdo->prepare("SELECT `balance` FROM `users` WHERE `login` = '$loser'");
$balance->execute();

$new_balance = $balance + 100;

$back = $pdo->prepare("UPDATE `users` SET `balance`= '$new_balance' WHERE `login`= '$loser'");
$back->execute();

$returned = $pdo->prepare("UPDATE `contest` SET `returned`= '1' WHERE `login`= '$loser'");
$returned->execute();

I tried a couple of things and looked for similar questions around here but I couldn't come up with the solution unfortunately. Can somebody help, please?

Answer 1

Thank you everyone. I found a solution:

$stmt = $pdo->prepare('SELECT * from contest  WHERE winner=0');
$stmt -> execute();
while($row = $stmt->fetch()) {

$result_b = $pdo->prepare("SELECT `balance` FROM `users` WHERE `login` = '$row[user]'");

$result_b->execute();

$balance = $result_b->fetchColumn();

$new_balance = $balance + 100;

$back = $pdo->prepare("UPDATE `users` SET `balance` = '$new_balance' WHERE `login` = '$row[user]'");
$back->execute();

$returned = $pdo->prepare("UPDATE `contest` SET `returned` = '1'");
$returned->execute();
}

Answer 2

I realise the code below is probably way more than is required but it does make good use of some of the powerful features of a database and saves any messy loops and stray variables within the php code.

The following is partially tested using ficticious database tables and data. Using a stored procedure within the database which itself uses a cursor to iterate over the initial recordset and then perform the update calculations.

mysql> describe balance;
+---------+------------------+------+-----+---------+----------------+
| Field   | Type             | Null | Key | Default | Extra          |
+---------+------------------+------+-----+---------+----------------+
| id      | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| balance | float unsigned   | NO   |     | 0       |                |
| login   | varchar(50)      | NO   | MUL | 0       |                |
+---------+------------------+------+-----+---------+----------------+

mysql> describe contest;
+----------+---------------------+------+-----+---------+----------------+
| Field    | Type                | Null | Key | Default | Extra          |
+----------+---------------------+------+-----+---------+----------------+
| id       | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| returned | tinyint(3) unsigned | NO   | MUL | 0       |                |
| login    | varchar(50)         | NO   | MUL | 0       |                |
| winner   | int(10) unsigned    | NO   | MUL | 0       |                |
| user     | varchar(50)         | YES  |     | NULL    |                |
+----------+---------------------+------+-----+---------+----------------+

mysql> describe users;
+---------+------------------+------+-----+---------+----------------+
| Field   | Type             | Null | Key | Default | Extra          |
+---------+------------------+------+-----+---------+----------------+
| id      | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| balance | float            | YES  |     | NULL    |                |
| login   | varchar(50)      | YES  |     | NULL    |                |
+---------+------------------+------+-----+---------+----------------+






mysql> select * from balance;
+----+---------+-------+
| id | balance | login |
+----+---------+-------+
|  1 |       0 | rita  |
|  2 |       0 | sue   |
|  3 |       0 | bob   |
+----+---------+-------+

mysql> select * from contest;
+----+----------+-------+--------+------+
| id | returned | login | winner | user |
+----+----------+-------+--------+------+
|  1 |        0 | rita  |      0 | rita |
|  2 |        0 | sue   |      0 | sue  |
|  3 |        0 | bob   |      1 | bob  |
+----+----------+-------+--------+------+

mysql> select * from users;
+----+---------+-------+
| id | balance | login |
+----+---------+-------+
|  1 |       0 | bob   |
|  2 |       0 | rita  |
|  3 |       0 | sue   |
+----+---------+-------+


/* calling the stored procedure ~ this will be done in PHP */
mysql> call `spUpdateContest`(100);
+----------+
| result   |
+----------+
| finished |
+----------+
1 row in set (0.01 sec)




mysql> select * from users;
+----+---------+-------+
| id | balance | login |
+----+---------+-------+
|  1 |       0 | bob   |
|  2 |     100 | rita  |
|  3 |     100 | sue   |
+----+---------+-------+
3 rows in set (0.00 sec)




create definer=`root`@`localhost` procedure `spUpdateContest`(
    in `p_addition` int
)
language sql
not deterministic
contains sql
sql security definer
comment ''

proc:begin

    declare done int default false;
    declare _addition int default 0;
    declare _balance integer default 0;
    declare _loser varchar(50);
    declare _cursor cursor for select `user` from `contest` where `winner`= '0';

    declare continue handler for not found set done=true;

    set @_addition=cast( p_addition as unsigned );


    open _cursor;
    repeat
    fetch _cursor into _loser;


    if not done then

        select `balance` into @_balance from `users` where `login` = _loser;
        update `users` set `balance`= @_balance + @_addition where `login`= _loser;
        update `contest` set `returned`= '1' where `login` = _loser;

    end if;

    until done end repeat;
    close _cursor;

    set _balance=null;
    set _loser=null;
    set _addition=null;
    set done=null;

    select 'finished' as 'result';
end

In PHP this can be called like this:

$sql='call `spUpdateContest`( 100 )';