MySQL IF Statements

[bluemicrobyte]

Here's my current SQL query:

$q = "UPDATE phpbb_ina_games SET jackpot = jackpot + ". $add;

The query is supposed to update all values in the "jackpot" column to be $add more than the current value. I want to change the query so that

IF the current value of jackpot is less than 25000 THEN it adds $add to it, ELSE it will SET the value of jackpot to be 25000.

The purpose of this new code is to make sure that the value of "jackpot" in any entry never exceeds 25000. This query is called quite frequently so I'm trying to do this in one query instead of doing a loop in PHP to go through each jackpot value and run a different query based on its current value.

I read a bit about MySQL IF statements but I couldn't figure out how to make it work.

[Markpyro]

I've never used IF statements with MySQL before, but I know you could do it with PHP. Something like:


$query = mysql_query("SELECT jackpot FROM phpbb_ina_games")
$jackpot = mysql_fetch_array($query,MYSQL_ASSOC); // Too lazy to look up another function
if ($jackpot['jackpot'] < 25000)
{

// update add blah blah blah

}
else
{

// set to 25000
}






http://pyrom.net/sigs/manual/hr.png
http://pyrom.net/sigs/manual/sigimage.png
*http://pyrom.net/test/blamecountimage.php*http://pyrom.net/test/lastblameimage.php*
http://pyrom.net/test/blameclick.png (http://pyrom.net/test/blame.php)

[Jeff]

Use two queries, perhaps? Maybe it can be done in one, but this is the easiest way I can think of doing it in a fixed number of queries.

$q = "UPDATE phpbb_ina_games SET jackpot = jackpot + ".$add;

...

$q = "UPDATE phpbb_ina_games SET jackpot = 25000 WHERE jackpot > 25000";

[bluemicrobyte]

ah, smart idea Jeff. I think I'll go with that one. Maybe I'll set the second query to run only if it hasnt run in over 5 minutes or something (this query set gets called almost each time someone loads a page).

I've never used IF statements with MySQL before, but I know you could do it with PHP. Something like:


$query = mysql_query("SELECT jackpot FROM phpbb_ina_games")
$jackpot = mysql_fetch_array($query,MYSQL_ASSOC); // Too lazy to look up another function
if ($jackpot['jackpot'] < 25000)
{

// update add blah blah blah

}
else
{

// set to 25000
}






Yeah, that's kind of what I was thinking of, but that would involve using some kind of loop to go through each entry in the table, doing 2 queries per entry. There are about a hundred entries and the script runs every pageload (or so). So thats something like 200 queries per pageload which seems like a lot to me (I have no sense of how many queries is a good number of queries).

As far as traffic on my site goes, peak moments will get 5-10 pageloads per minute.

[Jeff]

You're right, 200 queries per page load is not efficient :)

A decent message board (without tons of add-ons) will use roughly 10, which isn't many considering how much data is loaded.

When I am coding new sites I will do $querycount++; after every mysql_query(), then echo $querycount at the end to make sure I'm not using more than intended.