Page 1 of 1

Possible to combine these queries?

Posted: Sat May 19, 2007 2:25 am
by s.dot
I have a foreach() loop that does a calculation inside of the loop and updates a database row with the calculation.

Here is the code:

Code: Select all

foreach($ult AS $un => $at)
{
	//determine the amount of credits to reward upline users
	$amount_creds = constant(strtoupper($at).'_DL_PC_'.implode('', array_keys($ul, $un))) / 100;
				
	//update credits statistic for users
	mysql_query("UPDATE `users` SET `credits`=`credits`+$amount_creds, `credits_today`=`credits_today`+$amount_creds, `credits_month`=`credits_month`+$amount_creds, `credits_alltime`=`credits_alltime`+$amount_creds WHERE `username` = '$un' LIMIT 1") or die(mysql_error());
}
This works good. However, this will be the most viewed page of thie web site, and there could be 10 possible iterations through the loop on each pageload, thus up to 10 queries (just for this loop) per page load, per user.

I don't think it is possible, at least with my knowledge, to combine this into one query?

Posted: Sat May 19, 2007 4:02 am
by Ollie Saunders
Well these

Code: Select all

$stuff = implode(',', array_keys($un));

Code: Select all

WHERE `username` IN ($stuff)
will work provided $amount_creds is the same for each of them. But I don't think it is so then you are probably looking at mysqli_multi_query(). The use of stored procedures would also help.

Posted: Sat May 19, 2007 5:07 am
by s.dot
Hello,

Thanks for the reply. Indeed, the $amount_creds is likely to change. However there may be certain groups that have the same $amount_creds. I could group them like that, and then do the query WHERE username IN('$stuff'). That would be maybe.. 4 queries instead of 10. Still a lot, but better.

What is a stored proceedure?

Posted: Sat May 19, 2007 5:21 am
by Ollie Saunders
I could group them like that, and then do the query WHERE username IN('$stuff'). That would be maybe.. 4 queries instead of 10. Still a lot, but better.
You are probably right. But you would really need to do some profiling to know for sure.
What is a stored proceedure?
http://en.wikipedia.org/wiki/Stored_procedure

Also I would refactor that constant() stuff. Here's why.

Posted: Sat May 19, 2007 9:16 pm
by s.dot
I don't know if I can do that. I'm not used to using constants myself, but the client asked me to provide one page with variables he can edit to update variables of the web site. Maybe I should've used arrays for the variables instead of constants.

I'm so far into the coding though, that changing all of those would be.. hard.

Code: Select all

define('FREE_DL_PC_1', '10');
define('FREE_DL_PC_2', '5');
define('FREE_DL_PC_3', '3');
define('BRONZE_DL_PC_1', '10');
define('BRONZE_DL_PC_2', '8');
define('BRONZE_DL_PC_3', '6');
define('BRONZE_DL_PC_4', '4');
define('BRONZE_DL_PC_5', '3');
define('GOLD_DL_PC_1', '10');
define('GOLD_DL_PC_2', '10');
define('GOLD_DL_PC_3', '10');
define('GOLD_DL_PC_4', '8');
define('GOLD_DL_PC_5', '8');
define('GOLD_DL_PC_6', '5');
define('GOLD_DL_PC_7', '5');
define('GOLD_DL_PC_8', '5');
define('GOLD_DL_PC_9', '3');
define('GOLD_DL_PC_10', '3');
This makes for very easy administration, but gets very complicated when performing queries such as the above post.

Posted: Sun May 20, 2007 5:16 am
by Ollie Saunders
Err yes and also is he going to know what all those abbreviations mean?
I would hasten to say that even if this was written as

Code: Select all

$GLOBAL['config']['DL']['PC'][implode('', array_keys($ul, $un))]
it would still smell because I can't tell what data it is accessing. Dynamic access is something that should be handled with care and nearly always needs documentation.

I tried to write you a regex that would perform a conversion for you but it evolved into a class with multiple regexes and then I needed to start tokenizing for strings and what if \' was used or variables inside double quotes and uhghh it just became too much work so I gave up.