PHP - When Could Database Queries Be Reduced?

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?

Code: [Select]
<?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;
}

?>

Hello,

I've created a function whereby I want to return the school_id associated with a particular user.  Each user will be associated with exactly one school.  My query below works, but the thing that it returns is an array; I need to make use of it as a variable (say $instructor_school_id).  Any idea how to make the conversion from a single element array into a non-array variable?

Thank you....

function getInstructorSchool($read, $user_id) {

$sql = "SELECT school_id

FROM users

WHERE user_id = $user_id";

return $read->fetchRow($sql);
}
   

I have been reading in Larry Ullmans book "Visual QuickPro Guide PHP 6 and MySQL 5" and I find it well-written. Since it is from 2008, it does not contain anything about MySQL PDO, but rather does it in the mysqli_* way. Larry suggest placing the secret database password and more along with a database connection script in a connect.php file, placed above the webroot if possible. Then later, when he is creating queries and executing them in other php files, he includes the connect.php file before making the queries.   Now I know it is very important to be careful with the Error handling, so the script won't output errors, which could reveal something about the database making it less secure. Therefore I am wondering how to structure things when using PDO. I need to write error-handling scripts for the following situations:   a) Connection to the database doesn't succeed b) The execution of the queries doesn't succeed c) User input in HTML forms are not appropriate   and probably more. The recommended way of handling errors when using PDO seems to be writing some try-catch code. But then I don't see how I can keep the connection to the database completely inside the connect.php file. Either I will need to use a die() or exit() inside this file or I will need to give up my idea to keep everything which concerns the connection to the database in the file mentioned AND write nested try-catch sentences - first make sure the connection works, then make sure the query will execute properly.   I don't like either of those approaches. Firstly I have been told that using exit() is bad programming and secondly it seems to get more complicated using nested try-catch code and to let database connection take part in diverse php-files.   Maybe somebody have a smart, convenient and secure way to do it?   Erik  

As we know the total number of hits to database is proportional to the number of page views if page views * queries per page is more than the capacity of your database, then mysql queries may show  problem.  is there any way to calculate the database capacity ? I googled someone says 128Mb cache is there for mysql.

I am just new to relational algebra probably a pre-step before learning SQL queries. Can you help me make the expressions of relational algebra expression for each of the following queries. This is the table contained inside a bus driver database.

driver ( driver_id, driver_name, age, rating );
bus ( bus_id, bus_name, color);
reserves ( driver_id, bus_id, date);

a. Find the names of drivers who have reserved at least three busses. 
b. Find the names of drivers who have reserved all busses. 
c. Find the names of drivers who have reserved all busses called Shuttle. 
d. Find the IDs of drivers whose rating is better than some driver called Paul.   I would be grateful if somebody can help me here.