sql minimisation

billiondevil

hi i am making a mafia styled RPG game in php and mysql, i am about half finished and it has come to my attension that i am running way to many sql quieries and i haven't got much idea how to squish them into one or if it will cut down on the resources need to complete it.
sooo, i would like to know if crushing sql querys into smaller ones will make my gameuse less resources, and if it does how would i make this into 1 query??

<?php
function update() {
include('include.php');
$sql="UPDATE members SET hp=hp+1 WHERE hp<maxhp AND jail<1 AND hospital<1";
$result=mysql_query($sql);
$sql="UPDATE members SET brave=brave+1 WHERE brave<maxbrave AND jail<1 AND hospital<1";
$result=mysql_query($sql);
$sql="UPDATE members SET energy=energy+1 WHERE energy<maxenergy AND jail<1 AND hospital<1";
$result=mysql_query($sql);
$sql="UPDATE time SET lasttime='".time()."' WHERE id='1'";
$result=mysql_query($sql);
}
?>

any help would be apreciated because i am running 15 querys per page before i even start ciounting the querys for the page specific funstions. anyway i will stop blabing now and just say, thanks in advance!😃

laserlight

Yes, reducing the number of queries executed is usually a good idea, but in this case it seems to me that you do need all them separately.

billiondevil

what about

<?php

$sql="SELECT * FROM time WHERE id='1'";
$result=mysql_query($sql);
$info=mysql_fetch_assoc($result);

$timedifferance=time()-$info['lasttime'];
if($timedifferance>"29"){include('statupdate.php');update();}

$sql="SELECT * FROM time WHERE id='2'";
$result=mysql_query($sql);
$info=mysql_fetch_assoc($result);

$timedifferance=time()-$info['lasttime'];
if($timedifferance>"86400"){include('messageupdate.php');messageupdate();}

?>

can that be put together?

laserlight

That could be turned into:

$sql = "SELECT lasttime FROM time WHERE id='1' OR id='2'";
// ...

But maybe depending on what update() and messageupdate() do there could be better options.

billiondevil

i dont get how i could use that code, btw those vars are the only ones in the table and are purly made timestamp keeping. and how could update() and messageupdate() be made better?

laserlight

billiondevil wrote:
i dont get how i could use that code

Well, it should probably be:

$sql = "SELECT lasttime FROM time WHERE id='1' OR id='2' ORDER BY id";
// ...

Now, you just retrieve the two rows from the result set in turn and use them as you did the two single row result sets in your original version.

billiondevil wrote:
how could update() and messageupdate() be made better?

That is especially difficult to say when we do not know what they do.

billiondevil

update () is suppose to update my players brave and energy and stuff at a set interval of 29 seconds and updatemessage() goes off every day so i can only keep my user messages for 14 days, any way to improve it?

and btw how many sql queries do you think i should be getting per a page in an rpgn game?

laserlight

billiondevil wrote:
update () is suppose to update my players brave and energy and stuff at a set interval of 29 seconds and updatemessage() goes off every day so i can only keep my user messages for 14 days, any way to improve it?

Perhaps you could run them as separate scripts via cron or some other task scheduler.

billiondevil wrote:
and btw how many sql queries do you think i should be getting per a page in an rpgn game?

There are no hard and fast rules as it depends on the situation. For example, you could have no queries at all, but that is not necessarily a good thing. Maybe your 15 queries per page load is reasonable, or maybe you actually only need two.

billiondevil

i am designing my game to only rely on php and mysql (so no cron jobs) so i wont be able to use them, but is there a better/more efficiant way to do it? any code u need i can give

Roger_Ramjet

Two things that will improve your overall structure and efficiency are to

use Stored Procedures

Stored procedures are pre-compiled which saves the overhead of parsing your sql every time you want to query the database.
Stored procs can contain multiple sql statements which saves on the overhead of multiple calls to the db server.
Stored procs can be re-used which saves on the coding and maintaining of the same sql in multiple parts of your code. All you have to do is to type 'CALL sp($var)' when you want to invoke one.

use PDO

The manual will tell you why.

Also, if all you want are timestamps then use the Timestamp data type

billiondevil

kk, ony my website i have a friends page and it is horrible when it comes to its sql because it can do up to 151 querys per page!!!! the code is below,how can i make it more server friendly

$sql="SELECT * FROM friends WHERE userid='".$userinfo['id']."'";
    $friend=mysql_query($sql);
    $count=mysql_num_rows($friend);

    if ($count=="0"){
    echo("<tr><td colspan=3><center>You have no friends!</center></td><tr>");
    }else{
            while($row = mysql_fetch_array($friend, MYSQL_ASSOC))
            {

            $sql="SELECT timestamp FROM members WHERE id='".$row['friendid']."'";
            $result=mysql_query($sql);
            $friendtimestamp=mysql_fetch_assoc($result);

            $timestamp2=time()-300;
            if ($friendtimestamp['timestamp']>$timestamp2) {
            $online="<font color=green>[Online]</font>";
            }else{
            $online="<font color=red>[Offline]</font>";
            }


            echo "<tr><td><a href=\"game.php?do=userinfo&id=".$row['friendid']."\">".$row['friendname']."</a></td><td>".$online."</td><td><a href='game.php?do=writemail&to=".$row['friendname']."'>Send Mail</a></td></tr>";

            }

    }

thx in advance

laserlight

I would suggest something like this:

$sql = sprintf("SELECT friends.friendid, friends.friendname, members.`timestamp`
    FROM friends, members
    WHERE friends.friendid=members.id AND friends.userid='&#37;d'", $userinfo['id']);
$result = mysql_query($sql);
if (mysql_num_rows($result) == 0) {
    echo '<tr><td colspan="3" style="text-align: center;">You have no friends!</td><tr>';
} else {
    while ($row = mysql_fetch_assoc($result)) {
        $online_text = ($row['timestamp'] > (time() - 300))
            ? '<span style="color: green;">Online</span>'
            : '<span style="color: red;">Offline</span>';

    printf('<tr><td><a href="game.php?do=userinfo&amp;id=%1$d">%2$s</a></td><td>%3$s</td>'
        . '<td><a href="game.php?do=writemail&amp;to=%2$s">Send Mail</a></td></tr>',
        $row['friendid']
        htmlentities($row['friendname'])
        htmlentities($online_text));
}
}

Notice how I escape values to be used in the query and how I escape variables to be output as text in the webpage.

billiondevil

this is waht it came up with

Parse error: syntax error, unexpected T_STRING in E:\xampp\xampp\htdocs\friends.php on line 31

and this is all the code for the page

<?php
function friends(){

$sql="SELECT id FROM members WHERE username='".$_SESSION['myusername']."'";
$result=mysql_query($sql);
$userinfo=mysql_fetch_assoc($result);

echo "<table class='box4' width='644'><tr><td>Friends</td></tr></table>";

echo "<table class='box3' width='644'><tr><td>";

echo "<center><table class='bank'><tr><td width=250>Friends</td><td width=100>Online?</td><td width=100>Mail?</td><tr>";



$sql = sprintf("SELECT friends.friendid, friends.friendname, members.`timestamp`
FROM friends, members
WHERE friends.friendid=members.id AND friends.userid='%d'", $userinfo['id']);
    $result = mysql_query($sql);
    if (mysql_num_rows($result) == 0) {
       echo '<tr><td colspan="3" style="text-align: center;">You have no friends!</td><tr>';
    } else {
     while ($row = mysql_fetch_assoc($result)) {
            $online_text = ($row['timestamp'] > (time() - 300))
            ? '<span style="color: green;">Online</span>'
            : '<span style="color: red;">Offline</span>';

    printf('<tr><td><a href="game.php?do=userinfo&amp;id=%1$d">%2$s</a></td><td>%3$s</td>'
        . '<td><a href="game.php?do=writemail&amp;to=%2$s">Send Mail</a></td></tr>',
        $row['friendid']
        htmlentities($row['friendname'])
        htmlentities($online_text));
}
}



echo "</td></tr></table></center>";
echo "</td></tr></table>";
}
php?>

laserlight

Ah yes, I see that I made at least two typographical errors. Fix them.

billiondevil

this porbably makes me seem like a compleate idiot, but I am not used to all of these %2$ thingys and html entities, so i am compleatly lost.

laserlight

billiondevil wrote:
this porbably makes me seem like a compleate idiot, but I am not used to all of these %2$ thingys and html entities, so i am compleatly lost.

Read the PHP manual on [man]sprintf/man (it applies to printf as well) and [man]htmlentities/man. My typo errors have little to do with sprintf() though: they are just missing commas in a function call argument list.

billiondevil

kk, I have tryed for ages now and i still carn't get it!!!

johanafm

%N$ refers to the Nth parameter in the call to sprintf (if you consider the actual format string to be the 0th parameter).

billiondevil

i am still lost. i have never used printf or sprintf before so i really have no idea what to do. could somobody show m,e how to fix and tell me what was wrong with it?

laserlight

billiondevil wrote:
i have never used printf or sprintf before so i really have no idea what to do.

Take a look at this:

<?php
function foo($a, $b, $c) {
    echo $a . $b . $c;
}

foo('Test' 'this' 'out');

What is wrong with the call of foo()? That is the same mistake that I made with printf. As you can see, it has nothing to do with printf itself, other than printf taking more than one argument.

You should learn how to use sprintf and related functions, but even if you choose not to use them, the part to get right is the use of htmlentities() and the appropriate escaping of input/output in general.

billiondevil wrote:
could somobody show m,e how to fix and tell me what was wrong with it?

This is a fixed version:

<?php
function friends() {
    $sql="SELECT id FROM members WHERE username='".$_SESSION['myusername']."'";
    $result=mysql_query($sql);
    $userinfo=mysql_fetch_assoc($result);

echo "<table class='box4' width='644'><tr><td>Friends</td></tr></table>";

echo "<table class='box3' width='644'><tr><td>";

echo "<center><table class='bank'><tr><td width=250>Friends</td><td width=100>Online?</td><td width=100>Mail?</td><tr>";

$sql = sprintf("SELECT friends.friendid, friends.friendname, members.`timestamp`
    FROM friends, members
    WHERE friends.friendid=members.id AND friends.userid='&#37;d'", $userinfo['id']);
$result = mysql_query($sql);
if (mysql_num_rows($result) == 0) {
   echo '<tr><td colspan="3" style="text-align: center;">You have no friends!</td><tr>';
} else {
    while ($row = mysql_fetch_assoc($result)) {
        $online_text = ($row['timestamp'] > (time() - 300))
            ? '<span style="color: green;">Online</span>'
            : '<span style="color: red;">Offline</span>';

        printf('<tr><td><a href="game.php?do=userinfo&amp;id=%1$d">%2$s</a></td><td>%3$s</td>'
            . '<td><a href="game.php?do=writemail&amp;to=%2$s">Send Mail</a></td></tr>',
            $row['friendid'],
            htmlentities($row['friendname']),
            htmlentities($online_text));
    }
}

echo "</td></tr></table></center>";
echo "</td></tr></table>";
}
php?>

Spot the difference :p

But I only corrected the mistake propagated from my example. I did not correct this:

$sql="SELECT id FROM members WHERE username='".$_SESSION['myusername']."'";
$result=mysql_query($sql);

To understand what is wrong with the above, read about the exploits of a mom. To fix this, you can use:

$sql="SELECT id FROM members WHERE username='".mysql_real_escape_string($_SESSION['myusername'])."'";
$result=mysql_query($sql);

or:

$sql = sprintf("SELECT id FROM members WHERE username='%s'", mysql_real_escape_string($_SESSION['myusername']));
$result=mysql_query($sql);

I prefer the latter because I find it easier to read, and it is arguably more directly convertible to a prepared statement's SQL statement, should you choose to use the PDO extension, or MySQLi extension, etc, later.