limiting amount of database queries

Question

Before reading my question please keep in mind the following is work im doing on a personal project to learn / broaden my php / mysql skills, thus im still very much a rookie as I am sure is evident in my coding which follows. However any tips / helps appreciated

What my app does

My site allows users to guess what will the outcome be of a sporting match, after the match has finished results are uploaded and winning members gets rewarded "credits"

Psuedo Code for verifying winners

Upload a result of an event / sports match
Move old event from activeEvents table to expiredEvents table
Now delete event data from activeEvents table
Check who guessed the correct match result in picks table with uploaded data in step 1
Insert winning members who guessed correctly into winners table
Move old pick data to expiredPicks table
Delete data moved to expiredPicks table from activePicks table

My Problem

For each step described above Im doing a new query to database, this not only leads to slow response times but just seems down right inefficient, I am looking for some suggestions on how I can keep same functionality but improve my code

if(isset($_POST['resultBtn'])){
    //STEP 1:----GET GAME INFO
    foreach($_POST['winner'] as $id =>$winner){
        $winScore = $_POST['score'][$id];
        $teamsel[] = $winner ;
        $team1 = $_POST['team1'][$id];
        $team2 = $_POST['team2'][$id];
        $venue = $_POST['ven'][$id];
        $matchId = $_POST['gameId'][$id]; //match Id
        $score = $_POST['score'][$id]; //score
        $tour = $_POST['tournament'][$id];
        $round = $_POST['round'][$id];
        $event_date = $_POST['event_date'][$id];
        $gameNR = $_POST['gameNr'][$id];
        $sport=$_POST['sport'][$id];

        //STEP 2:----INSERT event info into expiredEvents
        $sql="INSERT INTO expiredEvents (event_id, sport_type, tournament, 
                        round, team1, team2, venue, event_date)
                        VALUES
                        ('$matchId', '$sport', '$tour', '$round', 
                        '$team1', '$team2','$venue','$event_date')";                                        
        mysql_query($sql) or trigger_error(mysql_error()." in ".$sql);      

        //STEP 3:----DELETE event/match from events table
        $sql ="DELETE FROM events WHERE event_id ='$matchId'";
        $result = mysql_query($sql) or trigger_error(mysql_error()." in ".$sql);

        //Step4: ----check for winner(s) in picks table with given data
        $sql="select * from picks where event_id = '$matchId' and
                 abs(score-$winScore) = (select min(abs(score-$winScore)) from picks 
                 where pick = '$winner');";      
    $result = mysql_query($sql) or trigger_error(mysql_error()." in ".$sql);

    while($row = mysql_fetch_array($result)){
        $winingMember = $row['member_nr'];
        $event = $row['event_id'];
        $pick = $row['pick'];
        $score = $row['score'];

        echo'<br />';
        echo $winingMember;
        echo'<br />';
        echo $event;
        echo'<br />';
        echo $pick;
        echo'<br />';
        echo $score;
        echo'<br />';
        echo $event_date;
        echo'<br />';

        //step5 insert winners into winners table
        $sql="INSERT INTO winners
        (member_nr,event_id, pickedTeam,pickedScore,event_date)
        VALUES
        ('$winingMember','$event','$pick','$score','$event_date')";
        mysql_query($sql) or trigger_error(mysql_error()." in ".$sql);

        }//while

        //Step 6 move old pick data to expired picks table  
        $sql="INSERT INTO expiredPicks (select * from picks WHERE event_id = '$matchId'";
        $result = mysql_query($sql) or trigger_error(mysql_error()." in ".$sql);

        //step7: --Delete expired picks
        $sql="DELETE FROM picks where event_id='$matchId'";
        $result = mysql_query($sql) or trigger_error(mysql_error()." in ".$sql);

    }//for each
}//isset

http://stackoverflow.com/questions/1612267/move-sql-data-from-one-table-to-another — Andrew Larsen, Jun 21 '15 at 11:41
side note, especially since you are in the process of improving your skills, its a **really good idea to stop using `mysql_` extensions.** They are **deprecated** (and for good reason, as they are not secure). Look into `mysqli_` prepared statements or `PDO`. They will also run a LOT faster for large queries.. — nomistic, Jun 21 '15 at 11:54
The action queries does not seem to be bottlenecks (but the form loops through multiple records which iterates these steps for however many the webpage takes). Yet, the Step 4 query could use optimization as it uses a subquery in a where clause. And consider using status columns (with 'active' vs 'expired' values) for both events and picks which cuts down on inserts and delete actions into one-column updates. — Parfait, Jun 21 '15 at 14:29

score 2 · Accepted Answer · answered Jun 21 '15 at 11:42

Just a thought, redesign your tables and use join to be more effective with queries.

Table1 Sports Id - Event - Result - Date From - Date To
Table2 Users Id - Name
Table3 Picks Id - Pick - Event (FK to Sports.Id) - Name (FK to Users.Id)
Table4 Winners Id - Winner (FK to Users.ID) - Credit(?)

Now for Table 1 if you want to check which event is done, you just check where Result is not null and when event gets done, you can update table with a result.

This is just Initial thought, and even from now I see it would need more work, but that should be enough to get you started.

Thank you its pretty clear the db design needs some major tweaking yes — Timothy Coetzee, Jun 21 '15 at 11:50

limiting amount of database queries

1 Answers1