Database search

ive created a database and with your help ive created a page that lets me add data to this database.

however im stuck on creating a page to search this database.

ive searched google and tryed various php scripts ive found but none seem to work.

my database simply contains customers details such as name, address, phone number etc,

can someone recomend a script that does this that works

which database?

er SQL ?

er, you mean microsoft sql server?

no didnt think it was microsoft, its a linux server and in cpanel i use phpmyadmin and mysqldatabase wizard

ah :slight_smile:

your database system is called mysql (see mysql.com)

go into phpmyadmin, go to the SQL tab, and run this query –

SHOW CREATE TABLE [I]yourtablename[/I]

meanwhile, we’ll get this thread moved to the mysql forum

im getting the following error

Parse error: syntax error, unexpected T_VARIABLE in /home/public_html/search7.php on line 22


<html>
<head>
<title>designplace.org search script</title>
<meta name="author" content="Steve R, http://www.designplace.org/">

</head>
<body>

<form name="form" action="search.php" method="get">
  <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","david","dave"); //(host, username, password)

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

// Build SQL Query  
$query = "select * from the_table where 1st_field 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\\"><< 
  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 >></a>";
  }

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

</body>
</html>

Parse error is because you are missing a ; of the end of this line:


$trimmed = trim($var) //trim whitespace from the stored variable

please go into phpmyadmin, go to the SQL tab, and run this query –

SHOW CREATE TABLE the_table

ahh ty

now i get a search box, however when i enter something in the search box and press submit i get a “Oops! This link appears to be broken.” error.

thats because your form action is set to go to search.php and your page is called search7.php.
:slight_smile:

doh silly me

im not getting any search results back.

do i need to tell it the name of the table on the database ?

Assuming that you have edited the line:


// Build SQL Query  
$query = "select * from the_table where 1st_field like \\"%$trimmed%\\"  
  order by 1st_field"; // EDIT HERE and specify your table and field names for the SQL query

the_table and 1st_field need changing

ok the table is called user_info and the first column/field is user_id so i have

// Build SQL Query  
$query = "select * from user_info where user_id like \\"%$trimmed%\\"  
  order by user_id"; // EDIT HERE and specify your table and field names for the SQL query

the rest of the fields/columns are user_first_name user_surname user_email user_mobile etc etc

do i need to add all the fields/columns to this line, if so would it be

// Build SQL Query  
$query = "select * from user_info where user_id like \\"%$trimmed%\\, user_first_name like \\"%$trimmed%\\, user_surname like \\"%$trimmed%\\, user_email like \\"%$trimmed%\\, user_mobile like \\"%$trimmed%\\"  
  order by user_id"; // EDIT HERE and specify your table and field names for the SQL query

anyway im still not getting any search results

which is why i asked what i asked in post #8

:cool:

OK, try the following code:


// Build SQL Query  
$query = "
    select 
          user_first_name 
        , user_surname 
        , user_email 
        , user_mobile 
    from 
        user_info
    where 
        user_id 
    like 
        '%" . $trimmed ."%'  
    order by 
        user_id"; // EDIT HERE and specify your table and field names for the SQL query

 $numresults=mysql_query($query) or die(mysql_error());


see what happens

r937 i did do as you asked but it didnt seem to help me much at the time, i now see what you was getting at.

spikeZ ive added the code above and i still dont get any search results

my code now looks like this

<html>
<head>
<title>designplace.org search script</title>
<meta name="author" content="Steve R, http://www.designplace.org/">
</head>
<!-- © http://www.designplace.org/ -->
<body>

<form name="form" action="search7.php" method="get">
  <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","david","dave"); //(host, username, password)

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

// Build SQL Query  
$query = "
    select 
          user_first_name 
        , user_surname 
        , user_email 
        , user_mobile 
    from 
        user_info
    where 
        user_id 
    like 
        '%" . $trimmed ."%'  
    order by 
        user_id"; // EDIT HERE and specify your table and field names for the SQL query

 
 $numresults=mysql_query($query) or die(mysql_error());
 $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\\"><< 
  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 >></a>";
  }

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

<!-- © http://www.designplace.org/ -->

</body>
</html>

ok just want to check - is there a record or records in the databse that would match with a user_id?!

yes there is, i enter the customers name, surname, email, phone etc on a form and that gets put into the database. user_id is set to AUTO_INCREMENT