Avatar of shinnmill
shinnmill
 asked on

PHP to search a MySQL database and return paged results

hi,

in wordpress, i'm trying to use a php form to access a mysql database and return paged results based on search results. it's working fine except for paged results - prev and next.

seems that the $PHP_SELF variable is returning index.php

form and paged results work outside of wordpress, but not in wordpress.

here's code i'm using:

<?php
/**
 * Template Name: Search Nutrition Page
 *
 * @package Mysitemyway
 * @subpackage Template
 */
global $mysite;
get_header(); ?>

<?php if( isset( $mysite->is_blog ) ) : ?>
	
	<?php get_template_part( 'loop', 'index' ); ?>
	
<?php else : ?>
	
	<?php if ( have_posts() ) while ( have_posts() ) : the_post(); ?>

		<div id="post-<?php the_ID(); ?>" <?php post_class(); ?>>
	
			<?php mysite_before_entry(); ?>
			
			<div class="entry">
				<?php the_content(); ?>
				
				<div class="clearboth"></div>
				
<form name="form" method=post">
  <input type="text" name="q" />
  <input type="submit" name="Submit" value="Search" />
</form>

<?php

  // Get the search variable from URL

  $var = @$_GET['q'] ;
  $trimmed = trim($var); //trim whitespace from the stored variable

// rows to return
$limit=10; 

// check for an empty string and display a message.
if ($trimmed == "")
  {
  echo "<p>Please enter a search...</p>";
  exit;
  }

// check for a search parameter
if (!isset($var))
  {
  echo "<p>We dont seem to have a search parameter!</p>";
  exit;
  }

//connect to your database ** EDIT REQUIRED HERE **
mysql_connect("localhost","bunbelle_nutadm","FoodFacts"); //(host, username, password)

//specify database ** EDIT REQUIRED HERE **
mysql_select_db("bunbelle_nutrition") or die("Unable to select database"); //select which database we're using

// Build SQL Query  
$query = "select `Shrt_Desc`, `Protein_(g)` from `Abbrev` where `Shrt_Desc` like \"%$trimmed%\"  
  order by `Shrt_Desc`"; // EDIT HERE and specify your table and field names for the SQL query

 $numresults=mysql_query($query);
 $numrows=mysql_num_rows($numresults);

// If we have no results, offer a google search as an alternative

if ($numrows == 0)
  {
  echo "<h4>Results</h4>";
  echo "<p>Sorry, your search: &quot;" . $trimmed . "&quot; returned zero results</p>";

// google
 echo "<p><a href=\"http://www.google.com/search?q=" 
  . $trimmed . "\" target=\"_blank\" title=\"Look up 
  " . $trimmed . " on Google\">Click here</a> to try the 
  search on google</p>";
  }

// next determine if s has been passed to script, if not use 0
  if (empty($s)) {
  $s=0;
  }

// get results
  $query .= " limit $s,$limit";
  $result = mysql_query($query) or die("Couldn't execute query");

// display what the person searched for
echo "<p>You searched for: &quot;" . $var . "&quot;</p>";

// begin to show results set
echo "Results<br/>";
$count = 1 + $s ;

// now you can display the results returned
  while ($row= mysql_fetch_array($result)) {
  $title = $row["Shrt_Desc"] . $row['Protein_(g)'] ;

  echo "$count.)&nbsp;$title<br />" ;
  $count++ ;
  }

$currPage = (($s/$limit) + 1);

//break before paging
  echo "<br />";

  // next we need to do the links to other results
  if ($s>=1) { // bypass PREV link if s is 0
  $prevs=($s-$limit);
  print "&nbsp;<a href=\"$PHP_SELF?s=$prevs&q=$var\">&lt;&lt; 
  Prev 10</a>&nbsp&nbsp;";
  }

// calculate number of pages needing links
  $pages=intval($numrows/$limit);

// $pages now contains int of pages needed unless there is a remainder from division

  if ($numrows%$limit) {
  // has remainder so add one page
  $pages++;
  }

// check to see if last page
  if (!((($s+$limit)/$limit)==$pages) && $pages!=1) {

  // not last page so give NEXT link
  $news=$s+$limit;

  echo "&nbsp;<a href=\"$PHP_SELF?s=$news&q=$var\">
  Next 10 &gt;&gt;</a>";
  
  }

$a = $s + ($limit) ;
  if ($a > $numrows) { $a = $numrows ; }
  $b = $s + 1 ;
  echo "<p>Showing results $b to $a of $numrows</p>";
  
?>

				
				</div><!-- .entry -->
							
			<?php mysite_after_entry(); ?>

		</div><!-- #post-## -->
		
		<?php if( !mysite_get_setting( 'disable_page_comments' ) ) comments_template( '', true ); ?>
		
	<?php endwhile; ?>
	
<?php endif; ?>

	<?php mysite_after_page_content(); ?>
	
		<div class="clearboth"></div>
	</div><!-- #main_inner -->
</div><!-- #main -->

<?php get_footer(); ?>

Open in new window

PHPWordPressMySQL Server

Avatar of undefined
Last Comment
Ray Paseur

8/22/2022 - Mon
SOLUTION
Ess Kay

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Ray Paseur

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
shinnmill

ASKER
esskayb2d,

your suggestion results in a syntax error

Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING
SOLUTION
skullnobrains

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Ray Paseur

To correct my comment at ID: 38455499, the function is called phpinfo()
http://php.net/manual/en/function.phpinfo.php

Sorry about the typo! ~Ray
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck