Avoiding excessive queries

Dev Shed Forums Sponsor:

January 7th, 2013, 10:12 AM

Varsh

Registered User

Join Date: Jan 2012

Posts: 15

Time spent in forums: 6 h 59 m 40 sec
Reputation 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    |
----------------------------------------

January 7th, 2013, 10:57 AM

NotionCommotion

Contributing User

Join Date: Sep 2006

Posts: 1,464

Time spent in forums: 2 Weeks 1 Day 6 h 21 m 36 sec
Reputation Power: 526

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);

January 7th, 2013, 11:10 AM

Varsh

Registered User

Join Date: Jan 2012

Posts: 15

Time spent in forums: 6 h 59 m 40 sec
Reputation Power: 0

Quote:

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.

January 7th, 2013, 11:23 AM

NotionCommotion

Contributing User

Join Date: Sep 2006

Posts: 1,464

Time spent in forums: 2 Weeks 1 Day 6 h 21 m 36 sec
Reputation Power: 526

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.

January 7th, 2013, 11:45 AM

Varsh

Registered User

Join Date: Jan 2012

Posts: 15

Time spent in forums: 6 h 59 m 40 sec
Reputation 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. ^_^

January 8th, 2013, 09:51 AM

Jyncka

We're trapped inside a game!

Join Date: Jul 2008

Location: Maryland

Posts: 297

Folding Points: 37258 Folding Title: Starter Folder

Time spent in forums: 5 Days 8 h 35 m 57 sec
Reputation Power: 902

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."

Avoiding excessive queries

Developer Shed Advertisers and Affiliates