Trying to reduce the number of queries

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hello all,

My script works fine as it is, but I am trying to reduce the number of
queries to the database.  I am running PHP 4.3.4 and MySQL 4.0.3.

Here is my table structure:

mysql> desc application;
+-----------------------------+--------------+------+-----+---------+-------
---------+
| Field                       | Type         | Null | Key | Default | Extra
|
+-----------------------------+--------------+------+-----+---------+-------
---------+
| ID                          | int(10)      |      | PRI | NULL    |
auto_increment |
| media_ID                    | int(5)       |      |     | 0       |
|
+-----------------------------+--------------+------+-----+---------+-------
---------+

mysql> desc mailbox;
+------------------+-------------+------+-----+---------+----------------+
| Field            | Type        | Null | Key | Default | Extra          |
+------------------+-------------+------+-----+---------+----------------+
| ID               | int(10)     |      | PRI | NULL    | auto_increment |
| app_ID           | int(5)      |      |     | 0       |                |
| company_ID       | int(5)      |      |     | 0       |                |
+------------------+-------------+------+-----+---------+----------------+

mysql> desc media;
+---------------+---------------------+------+-----+---------+--------------
--+
| Field         | Type                | Null | Key | Default | Extra
|
+---------------+---------------------+------+-----+---------+--------------
--+
| ID            | int(5) unsigned     |      | PRI | NULL    |
auto_increment |
| company_ID    | int(5) unsigned     |      |     | 0       |
|
| media_type_ID | int(5) unsigned     |      |     | 0       |
|
| name          | varchar(100)        |      |     | 0       |
|
| state_ID      | tinyint(1) unsigned |      |     | 0       |
|
| city          | varchar(50)         |      |     | 0       |
|
+---------------+---------------------+------+-----+---------+--------------
--+

mysql> desc media_type;
+-------+-----------------+------+-----+---------+----------------+
| Field | Type            | Null | Key | Default | Extra          |
+-------+-----------------+------+-----+---------+----------------+
| ID    | int(5) unsigned |      | PRI | NULL    | auto_increment |
| type  | varchar(25)     | YES  |     | 0       |                |
+-------+-----------------+------+-----+---------+----------------+

mysql> desc state;
+------------------+-------------+------+-----+---------+----------------+
| Field            | Type        | Null | Key | Default | Extra          |
+------------------+-------------+------+-----+---------+----------------+
| ID               | int(5)      |      | PRI | NULL    | auto_increment |
| full_name        | varchar(25) | YES  |     | NULL    |                |
| abbreviated_name | char(2)     | YES  |     | NULL    |                |
| region           | varchar(25) | YES  |     | NULL    |                |
+------------------+-------------+------+-----+---------+----------------+


Here is a part of my code.  I have a function query_database() that I use to
run mysql_connect() and mysql_query with all of the necessary error
handling.  I also use db_select_val() that returns the first field from the
first record of the results.

// Get a list of all of the types of media this company has listings
$query_type = "select media_type.ID, type from media_type, media where
media_type_ID = media_type.ID and company_ID = \"$company_ID\" and
deleted_date = \"0\" group by media_type_ID";
$result_type = query_database($query_type);
while($row_type = mysql_fetch_assoc($result_type)){
	$type_ID = $row_type["ID"];
	$type = $row_type["type"];

	// Get a count of the number of applications in this company's mailbox that
are the same type as 	// the $type_ID found above
	$type_count = db_select_val("select count(*) from application, media,
mailbox where application.ID 	= mailbox.app_ID and media.company_ID =
mailbox.company_ID and application.media_ID = media.ID and
media.media_type_ID = \"$type_ID\" and media.company_ID = \"$company_ID\"");
	echo "$type $type_count\n";

	// Get a list of all of the media locatons this company has listings for
this media type
	$query_state = "select state.abbreviated_name, city, name, media.ID from
media, state where 	state_ID = state.ID and company_ID = \"$company_ID\" and
deleted_date = \"0\" and media_type_ID = 	\"$type_ID\" order by
state.full_name, city, name";
	$result_state = query_database($query_state);
	while($row_state = mysql_fetch_assoc($result_state)){
		$state = $row_state["abbreviated_name"];
		$city = $row_state["city"];
		$name = $row_state["name"];
		$ID = $row_state["ID"];

		// Get a count of the number of applications in this company's mailbox
that are associated
		// with this media location
		$state_count = db_select_val("select count(*) from application, mailbox
where mailbox.app_ID 		= application.ID and application.media_ID = \"$ID\"
and mailbox.company_ID = 					\"$company_ID\"");

		echo "$state - $city - $name - $state_count\n";
	}
}

I would like to incorportate each of the queries that uses the count(*)
function into the associated query outside the while() loop.  Can this be
done?  I assume that the fewer times I need to query the database, the
faster my script will run.  Is this true as well?

TIA,
Ryan Marks

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux