Help with pagination in search engine - PHP MYSQL

Hello,


I'm trying to include a simple pagination system to the results from a search engine, but when I add the pagination code to the script I get a syntax error.

The code of the search engine ( I call the pagination.php in the end)

<?php


// DB Connection //
/* Change the following 3 values to reflect your MySQL Server */
	$MySQLPassword = "***";	
	$HostName = "***";	
	$UserName = "****";
	$Database = "****";

	mysql_connect($HostName,$UserName,$MySQLPassword)
	or die("ERROR: Could not connect to database!");
 	mysql_select_db($Database) or die("cannot select db");

// Data Sorting Variables //
/* The following are used to set the columns for data sorting                */
/* Change these to the table field names in your MySQL database              */
	$default_sort = 'ID';
	$allowed_order = array ('name','publication_date', 'price');
	

/* if order is not set, or it is not in the allowed
 * list, then set it to a default value. Otherwise, 
 * set it to what was passed in. */
	if (!isset ($_GET['order']) || 
	    !in_array ($_GET['order'], $allowed_order)) {
	    $order = $default_sort;
	} else {
	    $order = $_GET['order'];
	}

// The following line stops the undefined index error on initial page load
	if (isset($_GET['keyword'])) {

	        if(!$_GET['keyword']) {
	          die('<p>Please enter a search term.</p>');
	  	}     

// Database Query Settings //
/* Change these to match your database table, the fields you want displayed and the fields to run the query against. */
	$opcion_busqueda = $_GET['radio'];
	
	if ($opcion_busqueda == 2){
		
		$tables = 'swots';
	}
	
	else {
		$tables = 'reports';
		}
	
	
	
	$return_fields = 'id name organizer_id no_pages publication_date price currency';
	if  ($opcion_busqueda == 3){
		
		$check_fields = 'table_content description';

	}
	
	else {
	$check_fields = 'name';
	}

// Get the keyword from the search form.
 	$query_text = $_GET['keyword'];
    

// Sanitize Data Input
	$clean_query_text =cleanQuery($query_text);


// Call the bq_simple function and store the //
// resulting SQL Query in $newquery variable //
	$newquery=bq_simple ($return_fields, $tables, $check_fields, $clean_query_text);
	$newquery = $newquery . " ORDER BY $order;";




// sql data query construction //
	$result = mysql_query($newquery) or die(mysql_error());

/* make sure data was retrieved */
	$numrows = mysql_num_rows($result);
	if ($numrows == 0) {
	    echo "<H4>No data to display!</H4>";
	    exit;
	}
	echo 	"<p>Your search: '$query_text' within </p>\n";	
	
	if ($opcion_busqueda == 1){
		echo " market reports titles";}
		else if ($opcion_busqueda == 3) {
		echo " All content";}
	 else {echo " SWOTS";} 	
	 
	 echo "<p>returned ".$numrows. " results.</p>\n";
	echo 	"<p>Click on the headings to sort.</p>\n";


/* now grab the first row and start the table */
	$row = mysql_fetch_assoc ($result);
	echo "<TABLE cellspacing=\"3\" cellpadding=\"8\">\n";
	
	echo "</TR>\n";
	 foreach ($row as $heading=>$column) {
      if ($heading != 'id') { //don't create a column for ID!
         echo "<TD><b>";
         if (in_array ($heading, $allowed_order)) {
            echo "<a href=\"{$_SERVER['PHP_SELF']}?order=$heading&keyword=$query_text\">$heading</a>";
         } else {
            echo $heading;
         }                
         echo "</b></TD>\n";
      }
   }
echo "</TR>\n";

/* reset the $result set back to the first row and 
 * display the data */
	
	mysql_data_seek ($result, 0);
	while ($row = mysql_fetch_assoc ($result)) {
   //get the publisher name
  $organizerSql = mysql_query("SELECT nazwa FROM baza_obiektow_inne WHERE id=" . $row['organizer_id'] . " LIMIT 1");
   $organizer = mysql_fetch_assoc($organizerSql);
   $pretty_date = date('F Y', strtotime($row['publication_date']));
   echo "<tr>\n";
   $simbolos_reemplazar = array (" ","%","/");
   printf("<td><a href='http://embs-group.com/%s,%s'>%s</a></td>", $row['id'], str_replace( $simbolos_reemplazar, "_", $row['name']), $row['name']);
   printf("<td>%s</td>", $organizer['nazwa']);
   printf("<td>%s</td>", $row['no_pages']);
 printf("<td>%s</td>", $pretty_date);
   printf("<td>%s</td>", $row['price']);
   printf("<td>%s</td>", $row['currency']);
  
   echo "</tr>\n";

}

}
/* * * * * * * * * * * * * *  F U N C T I O N S * * * * * * * * * * * */

	function cleanQuery($string)
	{
	  $string = trim($string);
	  $string = strip_tags($string); // remove any html/javascript.

	  if(get_magic_quotes_gpc())  // prevents duplicate backslashes
	  {
	    $string = stripslashes($string);
	  }
	  if (phpversion() >= '4.3.0')
	  {
	    $string = mysql_real_escape_string($string);
	  }
	  else
	  {
	    $string = mysql_escape_string($string);
	  }
	  return $string;
	}


function bq_handle_shorthand($text) {
	$text = preg_replace("/ \+/", " and ", $text);
	$text = preg_replace("/ -/", " not ", $text);
	return $text;
}



function bq_explode_respect_quotes($line) {
        $quote_level = 0;	#keep track if we are in or out of quote-space
        $buffer = "";

        for ($a = 0; $a < strlen($line); $a++) {
                if ($line[$a] == "\"") {
                        $quote_level++;
                        if ($quote_level == 2) { $quote_level = 0; }
                }
                else {
                        if ($line[$a] == " " and $quote_level == 0) {
                                $buffer = $buffer . "~~~~";   #Hackish magic key
                        }
                        else {
                                $buffer = $buffer . $line[$a];
                        }
                }

        }

	$buffer = str_replace("\\", "", $buffer);

        $array = explode("~~~~", $buffer);
        return $array;
}


function bq_make_subquery($fields, $word, $mode) {

	if ($mode == "not") {
		$back = " LIKE '%$word%'))";
	}
	else {
		$back = " LIKE '%$word%')";
	}



	if ($mode == "not") {
		$front = "(NOT (";
		$glue = " LIKE '%$word%' AND ";
	}
	else {
		$front = "(";
		$glue = " LIKE '%$word%' AND ";
	}
	

	$text = str_replace(" ", $glue, $fields);
	$text = $front . $text . $back;

	return $text;
}



function bq_make_query($fields, $text) {
	
	$text = strtolower($text);

	
	$text = bq_handle_shorthand($text);

	#
	# Split, but respect quotation
	#
	$wordarray = bq_explode_respect_quotes($text);

	$buffer = "";
	$output = "";

	
	for ($i = 0; $i<count($wordarray); $i++) {
		$word = $wordarray[$i];

		if ($word == "and" or $word == "not" and $i > 0) {
			if ($word == "not") {
				#
				# $i++ kicks us to the actual keyword that the 'not' is working against, etc
				#
				$i++;
				if ($i == 1) {   #invalid sql syntax to prefix the first check with and/or/not
					$buffer = bq_make_subquery($fields, $wordarray[$i], "not");
				}
				else {
					$buffer = " AND " . bq_make_subquery($fields, $wordarray[$i], "not");
				}
			}
			else {
				if ($word == "and") {
					$i++;
					if ($i == 1) {
						$buffer = bq_make_subquery($fields, $wordarray[$i], "");
					}
					else {

						$buffer = " AND " . bq_make_subquery($fields, $wordarray[$i], "");
					}
				}
				else {
					if ($word == "and") {
						$i++;
						if ($i == 1) {
							$buffer = bq_make_subquery($fields, $wordarray[$i], "");
						}
						else {

							$buffer = " AND " . bq_make_subquery($fields, $wordarray[$i], "");
						}
					}
				}
			}
		}
		else {
			if ($i == 0) {  # 0 instead of 1 here because there was no conditional word to skip and no $i++;
				$buffer = bq_make_subquery($fields, $wordarray[$i], "");
			}
			else {
				$buffer = " AND " . bq_make_subquery($fields, $wordarray[$i], "");
			}
		}
		$output = $output . $buffer;
	}
	return $output;
}



#

function bq_simple ($return_fields, $tables, $check_fields, $query_text) {

	#
	# Convert from space deliniated to comma deliniated for the query
	#
	$return_fields = str_replace(" ", ", ", $return_fields);
	$tables = str_replace(" ", ", ", $tables);

	#
	# build the query itself
	$query = "SELECT $return_fields FROM $tables WHERE ";
	$query = $query . bq_make_query($check_fields, $query_text);

	#
	# Uncomment to debug
	#

	return $query;
}

require "pagination.php"; 
 
		
?>

Open in new window


HERE IS THE PAGINATION.PHP

<?php
    
    // WE NEED TO KNOW HOW MUCH RECORDS THERE ARE
    $query_pag = "select count(*) as recordcount from $query";
    $result_pag = mysql_query($query_pag) or die(mysql_error());
    $row_pag = mysql_fetch_array($result_pag) or die(mysql_error());
    
    // NUMBER OF ALL ROWS
    $numrows_pag = $row_pag['recordcount'];
    
    // HERE WE SET THE NUMER OF ITEMS PER ONE PAGE
    $ipp = 50;
    
    // NUMBER OF ALL PAGES
    // WE USE CEIL SO IF THE RECORD COUNT
    // IS - FOR EXAMPLE - 11 WE GET 3 PAGES
    $pagecount = ceil($numrows_pag / $ipp);
    echo"We have ".$pagecount." pages<br />";    
    
    // THE CURRENT PAGE NUMBER FROM GET VARIABLE
    // IF NOT SET - IT'S FIRST 
    $page = 0;
	if(empty($_GET[page]) || !isset($_GET[page])) $page = 1;
	else $page = $_GET[page];	
	
	// WHERE ARE WE ?
    echo"<br /><br />current page: $page<br /><br />";	
	
	// STARTRECORD IS USED FOR THE MYSQL QUERY
	// SO WE KNOW WHERE WE CAN START FROM
	// LIMIT WITH IPP (ITEMS PER PAGE)
	if(!empty($page)) $startrecord = ($page-1)*$ipp;
	else $startrecord = 0; 		
	
	$query_pag = "select * from $query limit $startrecord,$ipp";
	$result_pag = mysql_query($query_pag) or die(mysql_error());
	while($row_pag = mysql_fetch_array($result_pag)){
        echo"<div>$query</div>";
	}
	
	// THIS IS THE VALUE FOR THE NUMBER OF
	// NEXT AND PREVIOUS PAGES DISPLAYED IN
	// THE DYNAMIC PANEL
 	$margin = 6;
	
	
	// THE DYNAMIC PANEL
    echo"<br /><div>";
    echo"Page ";
            
			if($page == 1);
			else
			{
				$prev = $page - 1;
				echo" <a href=\"index.php?page=1\">first</a> ";
				echo" <a href=\"index.php?page=$prev\">prev</a> ";
			}

			if($page-$margin <= 0) $start = 1; else $start = $page - $margin;
			if($page+$margin > $pagecount) $last = $pagecount; else $last = $page + $margin;

			for($i=$start;$i<=$last;$i++){
                // SHOW THE CURRENT PAGE WITH DIFFERENT FORMATTING
                if($i == $page)
                    echo" <a href=\"index.php?page=$i\" style=\"font-weight: bold;\">$i</a> ";
                else
                    echo" <a href=\"index.php?page=$i\">$i</a> ";
			}


			if($page == $pagecount);
			else
			{
				$next = $page + 1;
				echo" <a href=\"index.php?page=$next\">next</a> ";
				echo" <a href=\"index.php?page=$pagecount\">last</a> ";
			}
    
    echo"</div>";  		
    
?>

Open in new window

Klaus AndersenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
I don't have time to work it right now, but I can point you to the canonical article on PHP pagination.  It's old, but it stands the test of time fairly well.
http://www.sitepoint.com/perfect-php-pagination/
0
Klaus AndersenAuthor Commented:
Thanks Ray, much appreciated!
0
Klaus AndersenAuthor Commented:
Ray, I had a read to the document you gave me and I tested it, but didn´t work.

I just need something very simple. The current pagination I´m trying to use I took from here
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_4742-Simple-PHP-pagination.html
0
Ray PaseurCommented:
That article looks pretty good.  The general concept is that your pagination is based on the LIMIT clause.  It's computed by knowing the number of rows in the results set and the number of rows that would be included in each page (subset) of the results set.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Joe_PritchardCommented:
I haven't read your code so this is just a thought, but have you thought about doing this clientside? It would depend on the size of the record set, but I find datatables ( Link ) to be very good at this sort of thing and extremely flexible.

Might save you a bit of time and it's nice not to have to reload the page each time the user clicks 'next', plus you get search, sort and filtering more or less for free...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.