PHP Optimizing a check

Question

I'm trying to optimize this check I have. I need to check table called lines and see if any row has matching Earned and Maxearned values (only rows with Position 1,2,3,4). If they do, I need to grab Earned from that row, write it in a different table called bank and remove that row from table called lines. This is what I have:

$sql3 = "SELECT * FROM `lines` WHERE Position <= 4 AND Linenum = '$linenum' AND Earned = Maxearned";
$result3 = mysql_query($sql3);
if (mysql_num_rows($result3) != 0)
{   
while ($row3 = mysql_fetch_array($result3)) 
{
$users[] = $row3['User'];
    }
    foreach ($users as $user)
    {
    $sql6 = "SELECT * FROM `lines` WHERE Position <= 4 AND Linenum = '$linenum' AND Earned = Maxearned AND User = '$user'";
    $result4 = mysql_query($sql6);
    while ($row4 = mysql_fetch_array($result4)) 
    {
        $earned = $row4['Earned'];
    }
    $today = date("Y-m-d");
    $method = "Queue money";
    $type = "Earned";
    $status = "Completed";
    $sql4 = "INSERT INTO bank (User,Amount,Method,Transdate,Type,Status) VALUES ('$user','$earned','$method','$today','$type','$status')";
    $sql5 = "DELETE FROM `lines` WHERE Position <= 4 AND Linenum = '$linenum' AND Earned = Maxearned AND User = '$user'";
    }
    $sql7 = "UPDATE `lines` SET Position = Position - 1 WHERE Linenum = '$linenum'";
    }

I'm trying to avoid having to run a different query ($sql6 and the while after that) to grab the value of Earned column. Is there a way to do this? I've tried everything and this is pretty much the best I came up with.

I know I'm not running `mysql_query($sql4,5,6,7)` this is just a dev version — Predrag Beocanin, Oct 24 '13 at 00:40
@user2864740 actually, without this, they would gain infinite money instead of what they should ^^ — Predrag Beocanin, Oct 24 '13 at 00:41
Like @user2864740 said, post your schema. Then read this: http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php — Cfreak, Oct 24 '13 at 00:45
@user2864740 okay, there are 4 lines (different prices for each). When you "buy" a place in line, you enter the table called lines with Linenum (1-4) and position. If there's 50 people there, your position is 51. You can earn max of Price of entry x 2. Whenever someone enters, I need to run a check after that how many people already earned what they are allowed and remove them from the queue plus bump everyone else up a position (only first 4 for each line can earn cash). But before I remove them, I have to log their transaction in table called bank. Sound about right? — Predrag Beocanin, Oct 24 '13 at 00:46
@user2864740 this is 100% working code, I'm just trying to see if there's a better way to do it. @Cfreak isn't my code, I was hired simply to fix few things that were broken, hence the `mysql_*` — Predrag Beocanin, Oct 24 '13 at 00:50
codereview.stackexchange.com is the preferred site for suggesting improvements to working code. — Barmar, Oct 24 '13 at 01:10
thank you @Barmar I didn't know that one. Since there's an answer to this already I'll l;eave it here in case someone finds it useful, but I will make sure to use codereview in the future =) — Predrag Beocanin, Oct 24 '13 at 04:41

Deepak · Accepted Answer · 2013-10-24T05:21:37.937

You can do something like this:

    mysql_query("SET AUTOCOMMIT=0");
    mysql_query("START TRANSACTION");

    $sql3 = "SELECT * FROM `lines` WHERE Position <= 4 AND Linenum = '$linenum' AND Earned = Maxearned";
    $result3 = mysql_query($sql3);
    if (mysql_num_rows($result3) != 0)
    {
        while ($row3 = mysql_fetch_array($result3))
        {
            $users[] = $row3['User'];
        }
        $users_to_compare = "(" . rtrim(implode(",", $users),',') . ")";
        $sql4 = "SELECT * FROM `lines` WHERE Position <= 4 AND Linenum = '$linenum' AND Earned = Maxearned AND User IN $users_to_compare";

        $result4 = mysql_query($sql4);
        while ($row4 = mysql_fetch_array($result4))
        {
            $earned = $row4['Earned'];
            $today = date("Y-m-d");
            $method = "Queue money";
            $type = "Earned";
            $status = "Completed";
            $sql5 = "INSERT INTO bank (User,Amount,Method,Transdate,Type,Status) VALUES ('{$row4['User']}','$earned','$method','$today','$type','$status')"; 
            $result5 = mysql_query($sql5);               
        }
        $sql6 = "DELETE FROM `lines` WHERE Position <= 4 AND Linenum = '$linenum' AND Earned = Maxearned AND User IN $users_to_compare";
        $result6 = mysql_query($sql6); 
        $sql7 = "UPDATE `lines` SET Position = Position - 1 WHERE Linenum = '$linenum'";
        $result7 = mysql_query($sql7); 

        if ($result5 && $result5 && $result7) {
            mysql_query("COMMIT");
        } else {        
            mysql_query("ROLLBACK");
        }
    }

Going one step further you can also use Batch INSERT for you insert queries

Note: Dont forget that mysql_* versions are depreceated you should use mysqli_*

@ta.speot.is Quickly edited it but naming need to be improved.. I just followed the OP's order — Deepak, Oct 24 '13 at 01:22
Cheers @Deepak , I know they are, I just got hired to do some fixing around, not to upgrade it to, hence the mysql =) — Predrag Beocanin, Oct 24 '13 at 04:41

PHP Optimizing a check

1 Answers1