Page 1 of 1

Pagination & Optimization of PHP/MySQL Search

Posted: Tue Sep 21, 2010 11:18 am
by vincentkriegvogel
I've created a script that searches a mysql database for either a matching zipcode, city, or category, and then displays the proper output.

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();
}

?>

Re: Pagination & Optimization of PHP/MySQL Search

Posted: Tue Sep 21, 2010 11:53 am
by alex.barylski
Search the forums, this is a topic that has been beaten to death dozens of times. :p

viewtopic.php?f=50&t=100934&start=0&hilit=pager

Cheers,
Alex

Re: Pagination & Optimization of PHP/MySQL Search

Posted: Tue Sep 21, 2010 12:23 pm
by vincentkriegvogel
Being new to php, that solution seems pretty complex? Isn't there a simpler way? I was thinking of doing something with LIMIT and OFFset and GET data. Also, any thoughts on optimizing this script? It seems to take way too long and it's as simple as I can seem to get it.

Re: Pagination & Optimization of PHP/MySQL Search

Posted: Tue Sep 21, 2010 12:34 pm
by alex.barylski
Being new to php, that solution seems pretty complex?
Ahh...I gotcha. Yes you could bundle all that code into a single script but maintanability, extensibility, etc all suffer, so most professional developers will implement a more complex module.

Google my friend there are 1000's of articles out there that step you through it.

Cheers,
Alex

Re: Pagination & Optimization of PHP/MySQL Search

Posted: Tue Sep 21, 2010 12:47 pm
by ell0bo
Yes, google would be your friend, as is PEAR.

http://pear.php.net/package/Pager/redirected

Re: Pagination & Optimization of PHP/MySQL Search

Posted: Tue Sep 21, 2010 2:11 pm
by vincentkriegvogel
Ill look into those solutions, thanks.

Now is there any way to get this thing running faster?

Executing a query inside of a query seems so process intensive when all I really need is to sort a query by a comma delimited field (in this case the zipcode field).