#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2012
    Posts
    15
    Rep Power
    0

    Avoiding excessive queries


    I currently have a list of games as a single query and it shows a table of all the games currently added to the database. At the end of each row there is a button which says "own", when I click the button the game is added to a different table. That portion I have done and there's no problems at all, the part which I can also do but know is very costly is having the button hidden and having something like "owned" in its place. The tables and queries are as follows:
    Code:
    ------------------------
    |   site_games_list    |
    ------------------------
    | id   | int (6)       |
    | game | varchar (100) |
    ------------------------
    
    --------------------
    | site_games_owned |
    --------------------
    | user | int (10)  |
    | game | int (6)   |
    --------------------
    
    --------------------
    |     vb4_user     |
    --------------------
    | userid | int(10) |
    --------------------
    PHP Code:
        // Select games
    try {
        
    $sql    "SELECT * FROM site_games_list ORDER BY name";
        
    $result $pdo->query($sql);
        
    $count    $result->rowCount();
    }
    catch (
    PDOException $e) {
        
    $error "Cannot retrieve the games list.<br>Error: " $e -> getMessage();
        echo 
    $error;
        exit();
    }
    while (
    $row $result->fetch()) {
        
    $game[] = array('id'        => $row['id'],
                    
    'name'    => $row['name'],
                    
    'year'        => $row['year'],
                    
    'box_art'    => $row['box_art'],
                    
    'comp'    => $row['completable']
            );
        } 
    PHP Code:
    <?php foreach ($game as $games):                
    // Select owned games
    try {
        
    $sql    "SELECT * FROM site_games_owned WHERE user = " $vbulletin->userinfo["userid"] . " AND game = " $games["id"];
        
    $result $pdo->query($sql);
        
    $cntOwn    $result->rowCount();
    }
    catch (
    PDOException $e) {
        
    $error "Cannot retrieve the games owned.<br>Error: " $e -> getMessage();
        echo 
    $error;
        exit();
    }
    while (
    $row $result->fetch()) {
        
    $own[] = array("user" => $row['user'],
                
    "game" => $row['game']
        );
    }                
    ?>

    <tr>
        <td><a href="games_info.php?gameID=<?php echo $games["id"]; ?>"><?php echo htmlspecialchars($games["name"], ENT_QUOTES"UTF-8"); ?></a></td>
        <?php if ($vbulletin->userinfo["userid"]) { ?>
        <td>
            <?php
            
    foreach ($own as $owned):
                if (
    $games["id"] == $owned["game"]) {
            
    ?>
                    <div class="ownCheck">Owned</div>
                <?php } elseif (($games["id"] != $owned["game"]) && ($cntOwn == 0)) { ?>
                    <div class="ownButton" style="float:left;">
                        <form method="post" action="?own">
                            <input type="hidden" name="game_id" value="<?php echo $games["id"]; ?>">
                            <input type="submit" value="Own" class="own">
                        </form>
                    </div>
            <?php
                
    }
            endforeach;
            
    ?>
            <div class="playedButton" style="float:right;">
                <form method="post" action="?played">
                    <input type="hidden" name="game_id" value="<?php echo $games["id"]; ?>">
                    <input type="submit" value="Played" class="played">
                </form>
            </div>
            <div class="playedCheck">Played</div>
        </td>
        <?php ?>
    </tr>
    <?php endforeach; ?>
    As you can see it's extremely expensive when there's thousands of games and each user is checking to see if they own it or not. You might be able to notice that there's also another option for "played" as well so it will be a similar situation, that would mean having an extra 2 queries per row which is something that I want to be prevented if possible. Any ideas?

    Edit: What I would want to achieve is something like this:
    Code:
    ----------------------------------------
    | game_id | game name | owned | played |
    ----------------------------------------
    |    1    | Anno      |   1   |   1    |
    |   432   | Quake     |   1   |   0    |
    |   91    | Unreal    |   0   |   1    |
    |  2425   | XCOM      |   0   |   0    |
    ----------------------------------------
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Sep 2006
    Posts
    1,806
    Rep Power
    529
    Try something like the following. Then iterate over the rows in your view and do as needed when sgo.user exists.

    Code:
    SELECT sgl.id,sgl.game,sgo.user AS owned,sgp.user AS played
    FROM site_games_list AS sgl
    LEFT OUTER JOIN site_games_owned AS sgo ON sgo.game=sgl.id AND sgo.user=123
    LEFT OUTER JOIN site_games_played AS sgp ON sgp.game=sgl.id AND sgo.user=123
    Also, scrap your while loop and retrieve them all at once. Instead of using SELECT *, always list the names and use aliases when needed.
    PHP Code:
    $games=$result->fetchAll(PDO::FETCH_ASSOC); 
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2012
    Posts
    15
    Rep Power
    0
    Originally Posted by NotionCommotion
    Try something like the following. Then iterate over the rows in your view and do as needed when sgo.user exists.

    Code:
    SELECT sgl.id,sgl.game,sgo.user AS owned,sgp.user AS played
    FROM site_games_list AS sgl
    LEFT OUTER JOIN site_games_owned AS sgo ON sgo.game=sgl.id AND sgo.user=123
    LEFT OUTER JOIN site_games_played AS sgp ON sgp.game=sgl.id AND sgo.user=123
    Also, scrap your while loop and retrieve them all at once. Instead of using SELECT *, always list the names and use aliases when needed.
    PHP Code:
    $games=$result->fetchAll(PDO::FETCH_ASSOC); 
    lol I was just testing it out with some customisations and about to reply when I saw you edited your post.

    Without the "played" part I managed to do the same as your edits and all that's needed is setting a condition that if it's not NULL then hide the button and show the text (or later an image) instead.

    I'll get back to you to see how it goes though you guys are better than any book I've ever read and I'm learning much faster because of you too.

    I'm going to look into the last part you mentioned once I get the SQL running as needed. I take it using "foreach" isn't a good idea then? I'm pretty new to PDO and this book I have is pretty good though I haven't quite got all the way through it yet regarding PDO.

    Edit: Aha I think I get it now regarding PDO::FETCH_ASSOC, sorry I went completely blank for a second there.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Sep 2006
    Posts
    1,806
    Rep Power
    529
    I am glad I am seeing you use PDO. I only relatively recently started using it, and think it is great.

    Nothing wrong with the foreach loop, just think it is faster to use fetchAll, and the code is definitely more readable. The key is not to use SELECT *. Again, it works, but is prone to making silly mistakes.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2012
    Posts
    15
    Rep Power
    0
    Thanks for the advice.

    In the summer I only heard about PDO for the first time after hearing about it from a friend, I looked into it and found out that it was much more secure than just using plain old MySQL, took me a while to figure out the basics but like you I'm loving it too. My previous sites though were very basic so only having standard queries was all I needed, now I'm starting to make my own custom CMS (the vBulletin 4 one was horrible) and finding out that I really have to know a lot of Advanced SQL!

    I'll definitely keep away from the "SELECT *" as much as possible from now on and also hopefully you'll see less of me... or more of me while I make the CMS.

    Can't thank everyone enough for helping me out here.

    Oh and forgot to mention that with a few alterations here and there it is now working perfectly. ^_^
  10. #6
  11. Put a potato on it!
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2008
    Location
    Maryland
    Posts
    304
    Rep Power
    970
    Using select * in this case also hurts your performance because the database has to give you ever field in that table or tables. As a rule of thumb, if you are fetching a handful of columns, it's better to list them out as NotionCommotion said, in that way you only fetch what you need and don't waste time and resources retrieving the columns you don't.

    And yes, PDO is a great interface and will ultimately make your life easier in the long run.
    "Those who can make you believe absurdities can make you commit atrocities."

IMN logo majestic logo threadwatch logo seochat tools logo