Function Runs 100 Queries - Need This Reduced!

[unemployment]

I have this function and for some reason it is running over 100 queries on page load.  I'm not sure what I am doing wrong.  Can I please get some guidance on this?

 

<?php

function fetch_users($page = 1, $per_page = 20, $type = null, $country = null, $state = null)
{
$start		= (int)(($page - 1) * $per_page);
$per_page	= (int)$per_page;
$type 		= (int)$type;
$country 	= (int)$country;
$state 		= (int)$state;

$sql = "SELECT
                `users`.`id`,
                `users`.`firstname`,
                `users`.`lastname`,
                `users`.`username`,
                `users`.`email`,
                `users`.`gender`,
                `users`.`accounttype`,
                `users`.`personalweb`,
                `users`.`guestviews`,
                `users`.`iviews`,
                `users`.`eviews`,
                `users`.`credentials`,
                `users`.`specialties`,
                `users`.`country`,
                `users`.`city`,
                `users`.`state`,
                `users`.`phonenumber`,
                `users`.`dateofbirth` AS `dob`,
			`users`.`mail_status`,
			`users`.`status`,
                `investor_info`.`investor_type`,
                DATE_FORMAT(`users`.`dateofbirth`,'%D') AS `dayofbirth`,
                DATE_FORMAT(`users`.`dateofbirth`,'%c') AS `monthofbirth`,
                DATE_FORMAT(`users`.`dateofbirth`,'%Y') AS `yearofbirth`,
                DATE_FORMAT(`users`.`dateofbirth`,'%D \of %M %Y') AS `dateofbirth`,
                DATE_FORMAT(`users`.`signupdate`,'%h:%i %p %M %e, %Y') AS `signupdate`,
                SUM(`investor_info`.`capital_available`) AS `totalavailable`,
			`companytype`,
			`capital`
            FROM `users`
            LEFT JOIN `investor_info`
            ON `users`.`id` = `investor_info`.`uid`
            LEFT OUTER JOIN employees
            ON users.id = employees.userid
            LEFT OUTER JOIN (
			SELECT 
				companies.companyid, 
				companies.companytype AS `companytype`,
				SUM(companies.capital) AS `capital`
			FROM  `companies`
			GROUP BY companies.companytype) SumCompanies
            ON employees.companyid = SumCompanies.companyid
		WHERE `users`.`status` > 2 ";

if($type != null)
{
	$acctype = $type - 1;

	$sql.= "AND`users`.`accounttype` = {$acctype} ";
}

if($country != null)
{
	$sql.= "AND`users`.`country` = {$country} ";
}

if($state != null)
{
	$sql.= "AND`users`.`state` = {$state} ";
}

$sql.= "GROUP BY `users`.`id` 
		ORDER BY `users`.`id` DESC 
		LIMIT {$start}, {$per_page}";

$result = mysql_query($sql) or die(mysql_error());

$users = array();

$i = 0;

while($row = mysql_fetch_assoc($result))
{
	$users[$i] = array(
		'id' 				=> $row['id'],
		'firstname' 		=> $row['firstname'],
		'lastname' 			=> $row['lastname'],
		'username' 			=> $row['username'],
		'email' 			=> $row['email'],
		'gender' 			=> $row['gender'],
		'accounttype' 		=> $row['accounttype'],
		'guestviews'		=> $row['guestviews'],
		'iviews' 			=> $row['iviews'],
		'eviews' 			=> $row['eviews'],
		'credentials' 		=> $row['credentials'],
		'specialties' 		=> $row['specialties'],
		'country' 			=> $row['country'],
		'state' 			=> $row['state'],
		'city' 				=> $row['city'],
		'phonenumber' 		=> $row['phonenumber'],
		'dob' 				=> $row['dob'],
		'mail_status' 		=> $row['mail_status'],
		'status' 			=> $row['status'],
		'investor_type' 	=> $row['investor_type'],
		'dayofbirth' 		=> $row['dayofbirth'],
		'monthofbirth' 		=> $row['monthofbirth'],
		'yearofbirth' 		=> $row['yearofbirth'],
		'dateofbirth' 		=> $row['dateofbirth'],
		'signupdate' 		=> $row['signupdate'],
		'totalavailable' 	=> $row['totalavailable'],
		'companytype' 		=> $row['companytype'],
		'capital' 			=> $row['capital'],
	);

	$i++;
}

return $users;
}

?>

[kicken]

That function is only going to run one query.  Check the rest of the code to see how many times your calling that function, and reduce the number of calls.

[unemployment]

That's odd because when I just print the array I get 130 queries, but then when I remove it I only run 13 queries.

 

Here is what I have which forms 130 queries:

 

<?php 

include("assets/init.inc.php");
include("assets/header.php");

$type 		= isset($_GET['type'])? $_GET['type'] : null;
$country 	= isset($_GET['country'])? $_GET['country'] : null;
$state 		= isset($_GET['state'])? $_GET['state'] : null;
$page_count = (isset($_GET['page'])) ? (int)$_GET['page'] : 1;
$total 		= fetch_total_users($type, $country, $state);
$users 		= fetch_users($page_count, 20, $type, $country, $state);

$hasLeftColumn = true;

if($hasLeftColumn === true)
{
include("assets/menu.php");	
}

print_array($users);

?>

[kicken]

How are you determining how many queries are being run? 

[unemployment]

I have this in my footer

 

<!--

Page Generation Time:   <?php echo microtime(true) - $timer_start, "\n"; ?>
Max RAM Used:           <?php echo (memory_get_peak_usage() / 1024), " KB\n"; ?>
Total MySQL Queries:    <?php echo mysql_result(mysql_query("SHOW SESSION STATUS LIKE 'Queries'"), 0, 'Value') - $total_queries_start - 2, "\n"; ?>

-->

[scootstah]

I'm pretty sure that shows the total queries ever executed on the server.

 

But I may be wrong.

[mikesta707]

as scootstah said, that shows the amount of queries ever executed by your server. If you want the amount executed by that page, you want to grab the number at the beginning of the page, and subtract it from the number at the end of the page. (it appears you are doing this though, with your $total_queries_start variable)

 

Also, can you post the code for that print_array function and the fetch_total_users function

[scootstah]

as scootstah said, that shows the amount of queries ever executed by your server. If you want the amount executed by that page, you want to grab the number at the beginning of the page, and subtract it from the number at the end of the page.

 

It still might not be accurate though, unless your app is the only thing connecting to the server.

 

The only way to reliably count queries is to...well, count queries. Make a wrapper for mysql_query or something that automatically increments a SESSION containing the total queries or something similar.

[mikesta707]

as scootstah said, that shows the amount of queries ever executed by your server. If you want the amount executed by that page, you want to grab the number at the beginning of the page, and subtract it from the number at the end of the page.

 

It still might not be accurate though, unless your app is the only thing connecting to the server.

 

The only way to reliably count queries is to...well, count queries. Make a wrapper for mysql_query or something that automatically increments a SESSION containing the total queries or something similar.

 

yes this is true of course, but I was simply telling OP how he would use the "SHOW SESSION STATUS...." query correctly to get an estimate of how many queries are run on that page.

 

Of course, I would suggest abstracting some sort of query logger like you suggested.

[unemployment]

I've found the problem.  In another spot in my code I found that I am looping through each users privacy setting which runs a query to check the privacy settings for that specific user.  Any way to aggregate privacy settings queries?

 

foreach($users as $k => $user)
{	
$privacy 			= fetch_user_privacy_settings($users[$k]['id']);
}

[PFMaBiSmAd]

The first question would be, why do you need the privacy settings for all the users?

 

If you do need all the privacy settings for all the users, you would not use a function that gets the settings one at a time, but a function that gets all the privacy settings at once. If you ever find yourself performing a SELECT query inside of a loop, there's probably something wrong with what you are doing.

 

P.S. The SESSION keyword in the SHOW ... STATUS query should get the query count for the current connection.

[unemployment]

The first question would be, why do you need the privacy settings for all the users?

 

If you do need all the privacy settings for all the users, you would not use a function that gets the settings one at a time, but a function that gets all the privacy settings at once. If you ever find yourself performing a SELECT query inside of a loop, there's probably something wrong with what you are doing.

 

P.S. The SESSION keyword in the SHOW ... STATUS query should get the query count for the current connection.

 

I need to grab everyones privacy settings because I am outputting a list of user data publicly.  The users can choose to opt out of the data.  I guess I need to change the function to grab multiple privacy settings, but the logic just doesn't make any sense to me.  Please explain

[kicken]

Sounds to me like rather than grab a list of users, then loop that and get privcy settings you should work that into your initial query for the list of users.  Not sure how your tables are setup, but something like this is what i mean:

SELECT
   * --the fields you need instead 
FROM users u
INNER JOIN privcy_settings ps ON ps.UserId=u.UserId
WHERE
   ps.VisibleToPublic=1

 

A query that returns your list of users already filtered by whether they are visible or not.

 

[unemployment]

Sounds to me like rather than grab a list of users, then loop that and get privcy settings you should work that into your initial query for the list of users.  Not sure how your tables are setup, but something like this is what i mean:

SELECT
   * --the fields you need instead 
FROM users u
INNER JOIN privcy_settings ps ON ps.UserId=u.UserId
WHERE
   ps.VisibleToPublic=1

 

A query that returns your list of users already filtered by whether they are visible or not.

 

Yeah, that makes the most sense.  Thank you