Problem with Database search.

[Hangwire]

Hi all. This is the code I've been trying to execute.

<?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>No search parameter</p>";
  exit;
  }

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

//specify database ** EDIT REQUIRED HERE **
mysql_select_db("my_db") or die("Can't select database//Does not exist."); //select which database we're using

// Build SQL Query  
$query = "select * from people \"%$trimmed%\"  
  order by FirstName"; // 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: "" . $trimmed . "" 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: "" . $var . ""</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["FirstName"];

  echo "$count.) $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 " <a href=\"$PHP_SELF?s=$prevs&q=$var\"><< 
  Prev 10</a>&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 " <a href=\"$PHP_SELF?s=$news&q=$var\">Next 10 >></a>";
  }

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

 

As you can see, it's pretty simple and it's got very nice explanations of what everything does. But when I try it, I get this.

 

Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in D:\Program Files\xampp\xampp\htdocs\search.php on line 36

Results

 

Sorry, your search: "Ella" returned zero results

 

Click here to try the search on Google

Couldn't execute query

 

I'm pretty much stuck. Any ideas what that error means? And yes, there is a entry with the name of Ella in the people table of the database.

 

Help?  :-\

[mikosiko]

what do you believe this is doing? 

 

$query = "select * from people \"%$trimmed%\"  
  order by FirstName";

 

also is good to include this lines at the beginning of your script... its will help you to find the errors that your code produce

 

error_reporting(E_ALL);
ini_set("display_errors", "1");

 

that is for a start...  I didn't read the rest of your code, therefore I don't know if you have more errors in there

[Hangwire]

As far as I know, it selects the results from the table called people and orders them by the first block, in the case FirstName.

EDIT: Thanks! I've made an edit to that and now the script works... kind of.

 

$query = ("SELECT * FROM people ORDER BY FirstName");

 

Now the problem is that it gives me all the contents of the table and not what I searched for:

You searched for: "Ella"

Results1.) boom2.) Borko3.) Dick4.) Ella5.) Lev6.) Nick7.) Nick8.) Todor

 

Showing results 1 to 8 of 8

 

[PFMaBiSmAd]

WHERE clauses are used to specify what to search for.

 

This is the SELECT query syntax definition (commonly used parts are highlighted) -

SELECT

    [ALL | DISTINCT | DISTINCTROW ]

      [HIGH_PRIORITY]

      [sTRAIGHT_JOIN]

      [sql_SMALL_RESULT] [sql_BIG_RESULT] [sql_BUFFER_RESULT]

      [sql_CACHE | SQL_NO_CACHE] [sql_CALC_FOUND_ROWS]

    select_expr [, select_expr ...]

    [FROM table_references

    [WHERE where_condition]

    [GROUP BY {col_name | expr | position}

      [ASC | DESC], ... [WITH ROLLUP]]

    [HAVING where_condition]

    [ORDER BY {col_name | expr | position}

      [ASC | DESC], ...]

    [LIMIT {[offset,] row_count | row_count OFFSET offset}]

    [PROCEDURE procedure_name(argument_list)]

    [iNTO OUTFILE 'file_name'

        [CHARACTER SET charset_name]

        export_options

      | INTO DUMPFILE 'file_name'

      | INTO var_name [, var_name]]

    [FOR UPDATE | LOCK IN SHARE MODE]]

 

[systemick]

Your query should be something like this

 

 

$query = "select * from people where [column] = \"%$trimmed%\" order by firstName"  

 

Replace column with the name of the column you are searching on.

[mikosiko]

Your query should be something like this

 

$query = "select * from people where [column] = \"%$trimmed%\" order by firstName"  

Replace column with the name of the column you are searching on.

 

and %'s are no screaming LIKE ? 

[Hangwire]

Your query should be something like this

 

$query = "select * from people where [column] = \"%$trimmed%\" order by firstName"  

Replace column with the name of the column you are searching on.

 

I did this:

$query = "SELECT * FROM people WHERE [FirstName] = \"%$trimmed%\" ORDER BY FirstName"

 

This is the error it gives...

 

 

Parse error: syntax error, unexpected T_NS_SEPARATOR in D:\Program Files\xampp\xampp\htdocs\search.php on line 32

 

I really can't figure this thing out!  I tried several edits and still nothing..

[mikosiko]

:'(  :'(  :'(  ..... just kidding...

try this

$query = "SELECT * FROM people WHERE FirstName LIKE \"%$trimmed%\" ORDER BY FirstName"

 

[Hangwire]

:'(  :'(  :'(  ..... just kidding...

try this

$query = "SELECT * FROM people WHERE FirstName LIKE \"%$trimmed%\" ORDER BY FirstName"

 

Parse error: syntax error, unexpected T_VARIABLE in D:\Program Files\xampp\xampp\htdocs\search.php on line 34

 

I'm starting to lose hope about this thing...

[mikosiko]

you know... at least you should try to understand and do some basic... basic... really basic intent to make it work and no just cut & paste....

 

$query = "SELECT * FROM people WHERE FirstName LIKE \"%$trimmed%\" ORDER BY FirstName";

 

was a missing ; at the end... was not THAT simple? 

[Hangwire]

you know... at least you should try to understand and do some basic... basic... really basic intent to make it work and no just cut & paste....

 

$query = "SELECT * FROM people WHERE FirstName LIKE \"%$trimmed%\" ORDER BY FirstName";

 

was a missing ; at the end... was not THAT simple? 

 

Problem is i've got my mind at seperate places, you could say im not that concentrated.

Stupid mistake on my behalf, oh well. Thank you.