Possible to combine these queries?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Possible to combine these queries?

Post 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?
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post 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.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post 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?
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post 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.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post 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.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post 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.
Post Reply