How do I paginate my entries the most efficiently?
What can I do to optimize my script's output so it doesn't take so long to execute?
Any help would be much appreciated.
Code: Select all
<?php
// EE AUTOMATICALLY PULLS DB INFO --
global $PREFS;
$db = mysql_connect( // Handle the connection to the database.
$PREFS->core_ini['db_hostname'], // hostname,
$PREFS->core_ini['db_username'], // username and
$PREFS->core_ini['db_password']); // password are automatically pulled
mysql_select_db($PREFS->core_ini['db_name']); // from EE's config.php
// SET POST DATA TO VARIABLES --
$get_offset = $_GET['offset'];
$get_page = $_GET['page'];
$post_zip = $_GET['zipcode']; // Example Data
$post_city = $_GET['city'];
$post_cat = '';
// QUERIES SET TO VARIABLES --
$search_all = "
SELECT
t.entry_id AS entry_id_master,
d.field_id_50 AS zips_master
FROM
exp_weblog_data AS d,
exp_weblog_titles AS t
WHERE
t.weblog_id = '14'
AND t.entry_id = d.entry_id
";
$search_narrow = "
SELECT
t.title AS title,
t.url_title AS permalink,
t.entry_id AS entry_id,
d.field_id_32 AS phone_foodbank,
d.field_id_46 AS crossstreets_foodbank,
d.field_id_33 AS hours_foodbank,
d.field_id_47 AS languages_foodbank,
d.field_id_36 AS eligibility_foodbank,
d.field_id_31 AS zipcode_foodbank
FROM
exp_weblog_data AS d,
exp_weblog_titles AS t,
exp_category_posts AS c
WHERE t.weblog_id = '14'
AND t.entry_id = d.entry_id
AND t.entry_id = c.entry_id
";
// CHECK TO SEE IF POST DATA IS ENTERED --
// EXECUTE SEARCH ALL QUERY --
$search_all_row_count = mysql_numrows($search_all_result = mysql_query($search_all,$db));
// For each entry found in search_all, do this
for ($i = 0; $i <= $search_all_row_count - 1; $i++) {
// Assign variables from DB
$entry_id_master = mysql_result($search_all_result, $i, "entry_id_master");
$zips = mysql_result($search_all_result, $i, "zips_master");
// Remove spacing, explode, then implode zipcode string for each entry
if ($zips != "") {
$zips_formatted = str_replace (" ", "", $zips);
$zips_exploded = explode( "," , $zips_formatted);
foreach ($zips_exploded as $key => $value) {
if ($value == "") {
unset($zips_exploded[$key]);
}
}
$zips_imploded = "'" . implode("','", $zips_exploded) . "'";
}
// Apply search_all DB information to search_narrow
$search_narrow_param_entry_id = "AND t.entry_id = '" . $entry_id_master . "'";
if ($post_cat != '') { $search_narrow_param_category = "AND c.cat_id IN (" . $post_cat . ")"; }
if ($post_city != '') { $search_narrow_param_city = "AND d.field_id_40 ='" . $post_city . "'"; }
if ($post_zip != '') { $search_narrow_param_zip = "AND '" . $post_zip . "' IN(" . $zips_imploded . ")"; }
$search_narrow_param_group = "GROUP BY t.title ORDER BY t.title ASC";
// Combine search_narrow with its paramaters
$search_narrow_all =
$search_narrow .
$search_narrow_param_entry_id .
$search_narrow_param_category .
$search_narrow_param_city .
$search_narrow_param_zip .
$search_narrow_param_group;
// Nested search_narrow query row count & results definition
$search_narrow_count = mysql_numrows($search_narrow_result = mysql_query($search_narrow_all, $db));
// Display search_narrow query if results are produced
for ($x = 0; $x <= $search_narrow_count - 1; $x++) {
// Assign variables from DB
$title = mysql_result($search_narrow_result, $x, "title");
$entry_id = mysql_result($search_narrow_result, $x, "entry_id");
$permalink = mysql_result($search_narrow_result, $x, "permalink");
$phone = mysql_result($search_narrow_result, $x, "phone_foodbank");
$cross_streets = mysql_result($search_narrow_result, $x, "crossstreets_foodbank");
$hours = mysql_result($search_narrow_result, $x, "hours_foodbank");
$languages = mysql_result($search_narrow_result, $x, "languages_foodbank");
$eligibility = mysql_result($search_narrow_result, $x, "eligibility_foodbank");
$zipcode = mysql_result($search_narrow_result, $x, "zipcode_foodbank");
// search_narrow display
$output = "
<div style='padding: 20px;'>
<div class='foodbank'>
<span class='color large'><a href='/index.php/foodbank/article/" . $permalink . "'>" . $title ."</a></span><br /><br />
<table width='100%' cellspacing='0' cellpadding='0' border='0'><tr>
<td width='40%'>
<span class='color bold'>Phone:</span><br /><br />" . $phone . "<br /><br />
<span class='color bold'>Cross Streets:</span><br /><br />" . $cross_streets . "<br /><br />
<span class='color bold'>Hours of Operation:</span><br /><br />" . $hours . "<br />
</td>
<td width='40%'>
<span class='color bold'>Language(s):</span><br /><br /> " . $languages . "<br /><br />
<span class='color bold'>Eligibility:</span><br /><br /> " . $eligibility . "<br /><br />
<span class='color bold'>Programs:</span> <br /><br />
</td>
<td width='20%' valign='top'>
<a class='btn' href='/index.php/foodbank/article/" . $permalink . "'>View Map & Details</a><br /><br />
</td>
</tr></table>
</div>
</div>
";
echo $output;
}
}
// IF NO RESULTS ARE FOUND --
if ($search_narrow_count == 0) {
echo "No results found. Please try broadening your search parameters.";
exit();
}
?>