SELECT query fail when using a variable and a LIMIT clause

[Weaver15]

Hi all,

 

I'm new to PHP/MySQL so it's the usual newbie-type question. I have played around with it and searched various forums for answers but haven't turned up anything useful so the time to ask for some help has come.

 

I've been using a phpacademy search engine and pagination tutorial and am now trying to combine the two to manage search terms that return lots of results.

 

Taking in the search term(s) is fine however, I get to the point where I want to pull all the records that match the search terms and limit them using the LIMIT clause.

 

The query is something like this mysql_query(SELECT * FROM tablename WHERE $variable LIMIT $start_record, $number_of_records).

 

The result is that it finds all the relevant records but will only display as many as defined by the $number_of_records. If this value is greater than the records being returned then they are all shown on one page without breaking it down into the pages. If the value is less than the number of records then only that number of records will be shown.

 

When taking the LIMIT clause out, the number of pages are all worked out ok but when the LIMIT clause is there it breaks.

 

Echoing out the variables such as $recordsfound, $start_record, $number_of_records and $number_of_pages_needed etc all checks out ok. Is there something in the use of a variable and a LIMIT clause that is tricky. For example, the variable not being a number but a 'Resource id'. Either way I'm stumped.

 

I don't have the code to hand to show but I will post it later if that helps.

 

Many thanks,

 

Weaver

[Muddy_Funster]

yeah, gonna need the code - and the exact values that are contained within $variable, $start_record and $number_of_records

 

The exact error message wouldn't go a miss either.

[Weaver15]

Ok, thanks for the response, I'll show you what I have. I also think perhaps this question belongs in the PHP Help forum - I wasn't 100% sure.

[Muddy_Funster]

It happens a lot, The mod's will move it if it's better suited to another board - to ensure you get the best chance of recieving the help you're looking for.

[Maq]

Is there something in the use of a variable and a LIMIT clause that is tricky. For example, the variable not being a number but a 'Resource id'.

The resource id is correct b/c that's what mysql_query() returns, which is good because that means your query is valid (no necessarily correct though).  Like mentioned before, we need some more code.

[Weaver15]

Hi again,

 

I get the feeling that this is a problem encountered by people of my experience again and again. There's something very similar here: http://www.dreamincode.net/forums/topic/189196-big-request-search-engine-pagination-help/.

 

I may not have explained the problem very well and it might be an issue that will make you groan from repetition. From what I gather, trying to query the database for results from a search and for pagination in a oner just isn't possible in one go.

 

Anyway, here's the code I'm using:

 

       <?php
//MySQL Database Connect
 include 'datalogin.php'; 


        //get data
        $button = $_GET['submit'];
        $search = $_GET['search'];
	$page = (isset($_GET['page'])) ? (int)$_GET['page'] : 1;
	echo "Page - $page<br />";

	//if page is requested without using the Submit button
        if (!$button)
                echo "You have reached this page in error.";
        else
        {
			//ensure search query is at least a certain length
                if(strlen($search) < 1 )
                        echo "Search term too short.";
                else{

                                //explode our search term
                                $search_exploded = explode(" ",$search);
                                
                                foreach($search_exploded as $search_each)
                                {
                                        
                                        //construct query
                                        $x++;
                                        if ($x==1)
                                                $construct .= "keywords LIKE '%$search_each%'";
                                        else
                                                $construct .= " OR keywords LIKE '%$search_each%'";
                                        
                                }
             
					//Set up pagination
					//number of images to be displayed per page
					$per_page = 6; 
					echo "Per page: $per_page<br />";
					//Define which record will be displayed at the start of earch page
					$start = ($page - 1) * $per_page;
					echo "Start record = $start<br />";   
                                
                        //echo out construct
                       
                        $construct = "SELECT * FROM images WHERE $construct LIMIT $start, $per_page";

                        $run = mysql_query("$construct");

                        $foundnum = mysql_num_rows($run);
					echo "Number of results found $foundnum<br />";

					//number of pages needed
					$pages = ceil ($foundnum / $per_page);
					echo "Number of pages needed to display data: $pages<br />";



    					echo "<p class='content-text' align='left'>Search term: $search<p>";

   					
                        
                        if ($foundnum==0)
                                {echo "No results found.";
						}
                        else
                        {
                  
                                while ($runrows = mysql_fetch_assoc($run))
                                {
                                        //get extra data from table for display if needed
                                        $image = $runrows['fileName'];
                                        $category = $runrows['category'];
                                        $idnumber = $runrows['id'];
									$filename = rtrim($image, ".jpg");

									echo '<p>', $image ,'</p>';

                                }
						//create pagination links	
						if ($pages >= 1 && $page <= $pages) {
						for ($x=1; $x <= $pages; $x++) {
						if ($x == $page)
						echo '<strong><a href="?search='.$search.'&page='.$x.'&submit=FIND%21">'.$x.'</a></strong> ';

						else { 
						echo '<a href="?search='.$search.'&page='.$x.'&submit=FIND%21">'.$x.'</a> ';
							}

					}
                                       
                        }

	}
}
}
?>

 

 

Thanks,

 

Weaver

[Weaver15]

Hi again,

 

A bit more searching has me at the stage where I've avoided using another sql query to get the number of records returned by the search. Instead I've just COUNTed the rows in the array storing the information.

 

So now I have a variable $count with the total number of results I want to display. Is there a way to perform what an sql LIMIT clause does when it comes to displaying these rows? Something like the following is what I'm trying to achieve.

 

<?php
while ($row = mysql_fetch_assoc($result) LIMIT $start, $perpage) {
				$fileName = $row['fileName'];
				$gallery = $row['gallery'];

				echo "<p>Filename: $fileName. Gallery: $gallery</p>";
			}
?>

 

The full code is here:

 

<?php
//MySQL Database Connect
 include 'datalogin.php'; 


        //get data
        $button = $_GET['submit'];
        $search = $_GET['search'];

	//if page is requested without using the Submit button
        if (!$button)
                echo "You have reached this page in error.";
        else
        {
			//ensure search query is at least a certain length
                if(strlen($search) < 1 )
                        echo "Search term too short.";
                else{



                        
                                //explode our search term
                                $search_exploded = explode(" ",$search);
                                
                                foreach($search_exploded as $search_each)
                                {
                                        
                                        //construct query
                                        $x++;
                                        if ($x==1)
                                                $construct .= "keywords LIKE '%$search_each%'";
                                        else
                                                $construct .= " OR keywords LIKE '%$search_each%'";
                                        
                                }

			$query = "SELECT * FROM images WHERE $construct";

			// Perform Query
			$result = mysql_query($query);


						/********* PAGINATION SETUP *********/
						//get the page number so we know where to start printing results from. if not user defined then = 1
						$page = (isset($_GET['page'])) ? (int)$_GET['page'] : 1;
								echo "Page - $page<br />";

						//count the number of rows in the area - this will be the basis of the pagination
						$count = mysql_num_rows($result);
						echo "Records in array: $count<br />";


						//define how many records per page
						$per_page = 4;

						$pages = ceil ($count / $per_page);
						echo "Number of pages needed to display $pages<br />";

						//define which record will start each page
						$start = ($page - 1) * $per_page;
						echo "Start:  $start";



			// Use result
			// Attempting to print $result won't allow access to information in the resource
			// One of the mysql result functions must be used
			// See also mysql_result(), mysql_fetch_array(), mysql_fetch_row(), etc.
			while ($row = mysql_fetch_assoc($result)) {
				$fileName = $row['fileName'];
				$gallery = $row['gallery'];

				echo "<p>Filename: $fileName. Gallery: $gallery</p>";
			}

			if ($pages >= 1 && $page <= $pages) {
				for ($x=1; $x <= $pages; $x++) {
					if ($x == $page)
					echo '<strong><a href="?search='.$search.'&page='.$x.'&submit=FIND%21">'.$x.'</a></strong> ';

										else { 
										echo '<a href="?search='.$search.'&page='.$x.'&submit=FIND%21">'.$x.'</a> ';
						}

					}
}


// Free the resources associated with the result set
// This is done automatically at the end of the script
mysql_free_result($result);
			}
	}
?>

 

Thanks,

 

Weaver

[Maq]

You shouldn't be selecting all the data for every request.  You only need to figure out how many total records there are 1 time, then after the user clicks 'next', 'previous' or a specific page you send back what value to start on.

 

Here is a good tutorial on pagination - http://www.phpfreaks.com/tutorial/basic-paginatio

[Weaver15]

I know that selecting all the data is frowned upon (and that can be edited before implementation) but isn't the only want to know the total number or records to count how many match the search terms?

 

Using the "SELECT * FROM images WHERE $construct LIMIT $start, $per_page" doesn't seem to work. I think this is to do with the different types of information being selected, it need to be be held in an array in order to view it. What I'm struggling on is how to replicate the LIMIT clause when outputting the data.

 

Cheers,

 

Weaver

[fenway]

No, you can use COUNT(*).