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
