[RESOLVED] Problem with Search MySQL Database...

[TDK]

Okay, everything's working. Connecting to the database fine and what not.. but it says it can't find any results when I know the search text is in the table.

Anything wrong with this code?

<?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("sql1.phpnet.us","User","Password"); //(host, username, password)

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

// Build SQL Query  

$query = "select * from address_book where first_name like \"%$trimmed%\"  

  order by 1st_field"; // 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";
$count = 1 + $s ;

// now you can display the results returned
  while ($row= mysql_fetch_array($result)) {
  $title = $row["1st_field"];

  echo "$count.)&nbsp;$title" ;
  $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>";

?>

I have taken out the real username and password, so that is not the problem. Everything seems to connect fine. I know for a fact the tables and columns are proper. It returns 'No results' even when I know I'm putting in correct text.

[TDK]

So sorry! Figured it out!

If anyone was wondering it was a NEWBIE mistake and I forgot to change "1st_field" in all spots to the field in my actual table. The code works pretty good!

Now I just need to get it to make a link in the search results so that when clicked it displays all fields. Anyone got a clue how to do this?

[NogDog]

[EDIT: I was typing this while the OP responded with the above reply.]

The first thing I'd do is make sure the query is actually executing OK:

// For debugging, make sure all errors are displayed:
ini_set('display_errors', 1);
error_reporting(E_ALL);
// output some debug info if query fails:
$numresults=mysql_query($query) or die("Query failed ($query): " . mysql_error());

[TDK]

Sorry, NogDog.. your efforts are not in vain. I have copied that piece of code for future use! Thank you for the reply.

Quick question, if you wouldn't mind...

I am doing what I said in my last post where they will be able to click on the search results and have it display only that item. The below code displays ALL columns in the table. Is there a way to have it display only the item clicked?

<?

$var = @$_GET['q'] ;

mysql_connect("sql1.phpnet.us","username","pass"); 


mysql_select_db("database_guide"); 


$result = mysql_query("select * from strain_guide");

//grab all the content
while($r=mysql_fetch_array($result))
{	
   //the format is $variable = $r["nameofmysqlcolumn"];
   //modify these to match your mysql table columns

   $field1=$r["field1"];
   $field2=$r["field2"];
   $field3=$r["field3"];
   $field4=$r["field4"];
   $field5=$r["field5"];
   $id=$r["id"];


?>

I just need it to display the selected ROW instead of all ROWS of the table. This is most likely very simple but I can't seem to figure it out.

You'll notice I have the GET code there because it will be sent over from the search results. Should I use the ID itself or just the first column to tell it which item to display?

Thanks so much and sorry for the messy thread!

[NogDog]

Make each item in the list a link, with the ID as a value in the URL's query string, e.g.:

echo "<a href='scriptname.php?id=".urlencode($r['id'])."'>link text</a>";

Then in scriptname.php, use the value from $GET['id'] to focus the query:

// if "id" is an integer value:
$query = "SELECT * FROM table_name WHERE id=" . (int) $_GET['id'];

// if "id" is a string value:
$query = "SELECT * FROM table_name WHERE id='" . mysql_real_escape_string($_GET['id']) . "'";

[TDK]

Thank you very much, got it figured out.

🙂