PHP Search Problem

[APZteam]

I am one of the database editors at www.airplanephotozone.com.  We are new to PHP, and have been writing our site's script for the past year.  Currently we are in a bit of a problem and cannot seem to troubleshoot it ourselves.

 

Our site is getting larger by the day, and with that said more photos are available to search.  We had to define the search to only return the top 3,000 newest photos on the site (when we have 21,000 +).  We are unable to do a search for all photos without an error popping up saying that we have either reached our maximum amount of memory available, or the search results just displays a white screen.  Any ideas on how we can change this so that we can do a search on ALL photos in the database (21,000 +), and not just the top 3000?

 

Here is a hands on example of what the problem is  (we have a link that shows the error, and a link the shows how we got it working having to define 3000 search results max):

 

Notice when we try to search on all 21K + photos viewed in the past year it goes to a white screen:

 

http://www.airplanephotozone.com/search.php?top=year (ERROR)

 

For the link below, it should technically still show all 21K + photos viewed over all time, but we had to define it to 3,000 so that it would work to some extent, but it only displays 3,000 out of the total photos. "Search returned 3,000 Photos."

 

http://www.airplanephotozone.com/search.php?dosearch=true&sort=views&order=desc  (TEMP SOLUTION)

 

Sorry if this is confusing, but in short..we can't search on all 21,000 + photos, only a defined 3,000 for a memory error.  We think i think it has to do with our queries, but are unable to solve the problem.

 

Any ideas?

 

Ben

 

[darkfreaks]

can you post the code ???

[APZteam]

Sorry about that...here's a piece of our function coding that deals with the problem area.  You can see that we removed the 3k limit on the "year" query so that you guys could see it sit on the blank screen.  Please let us know if you want to see something else, or something isn't clear.  Thanks in advance for any help.

 

			if($top=="month"){
			$query = "SELECT photoid, SUM(views) AS dayviews FROM apz_dateviews WHERE date BETWEEN '$topmonth' AND '$top24' GROUP BY photoid ORDER BY dayviews DESC LIMIT 0,3000;";
		}
		if($top=="year"){
			$query = "SELECT photoid, SUM(views) AS dayviews FROM apz_dateviews WHERE date BETWEEN '$year' AND '$today' GROUP BY photoid ORDER BY dayviews DESC;";
		}
		$result = mysql_query($query);
		while($row = mysql_fetch_array($result)){
			$photo=$row["photoid"];
			$queryph = "SELECT * FROM apz_photos WHERE status='1' AND photoid='$photo';";
			$resultph = mysql_query($queryph);
			while($rowph = mysql_fetch_array($resultph)){
				$rowph['views']=$row["dayviews"];
				$qcom="SELECT COUNT(status) as cantidad FROM apz_comments WHERE status='1' AND photoid='".$rowph['photoid']."';";
				$resultcom = mysql_query($qcom);
				if($rowcom = mysql_fetch_array($resultcom)){
					$rowph['comments'] = $rowcom['cantidad'];
				}
				$results[] = $rowph;
			}

[darkfreaks]

try limit 0,1000

[soycharliente]

Are you using pagination? Google link

 

That may help with the memory problem as you will be able to search all photos and have them returned little by little so that it doesn't run out of memory.

[APZteam]

The problem is we don't want a limit. Putting 0,1000 would only display 1000 photos....

[darkfreaks]

they are using pagnation i checked.

 

 

then remove LIMIT 0,3000??? then you wont have a limit

[APZteam]

We know by removing it we won't have a limit.  When we remove it, this is what happens:

 

http://www.airplanephotozone.com/search.php?top=year

[benphp]

What about changing the limit as each page is clicked. So if your page shows the first 100, set

 

$lastPage = 100;

LIMIT $lastPage,100+$lastPage

 

above is the concept - not the code I'd use.

[soycharliente]

Change how many results are returned per page.

[benphp]

Yes - if your pagination is set up right, it should limit results per page. So you can have 40000 results, but display only 20 at a time using pagination and LIMIT.

 

Here's some code that does it:

 

<?php

$queryselect = "SELECT mtime FROM messages WHERE messages.mid = '$mid' LIMIT 0,6 ";

$result = mysql_query($queryselect) or trigger_error("SQL", E_USER_ERROR);
  	$numrows = mysql_num_rows($result);

if ($numrows != 0) {
	$mtime = mysql_result($result, 0, 0);
	$rows_per_page = 6;
	$lastpage      = ceil($numrows/$rows_per_page);

	$pageno = (int)$pageno;
	if ($pageno < 1) {
		$pageno = 1;
	} elseif ($pageno > $lastpage) {
		$pageno = $lastpage;
	} // if $pageno

	$limit = 'LIMIT ' .($pageno - 1) * $rows_per_page .',' .$rows_per_page;
	?>

	<?php
	///page hyperlinks

	print "Found: $numrows   ";

	if ($pageno == 1) {
		echo " FIRST PREV ";
	} else {
		echo " <a href='{$_SERVER['PHP_SELF']}?pageno=1&new=new'>FIRST</a> ";
		$prevpage = $pageno-1;
		echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$prevpage&new=new'>PREV</a> ";
	} // if

	echo " ( Page $pageno of $lastpage ) ";

	if ($pageno == $lastpage) {
		echo " NEXT LAST ";
	} else {
		$nextpage = $pageno+1;
		echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$nextpage&new=new'>NEXT</a> ";
		echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$lastpage&new=new'>LAST</a> ";
	} // if


} //end if numrows != 0
?>