Database search

mortimers_r 2011-03-09 10:15:14 UTC #1

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

r937 2011-03-09 12:53:39 UTC #2

which database?

mortimers_r 2011-03-09 13:26:17 UTC #3

er SQL ?

r937 2011-03-09 13:59:55 UTC #4

er, you mean microsoft sql server?

mortimers_r 2011-03-09 15:55:04 UTC #5

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

r937 2011-03-09 17:49:02 UTC #6

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

mortimers_r 2011-03-14 08:17:13 UTC #7

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>

SpikeZ 2011-03-14 08:56:37 UTC #8

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

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

r937 2011-03-14 08:47:09 UTC #9

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

SHOW CREATE TABLE the_table

mortimers_r 2011-03-15 08:57:57 UTC #10

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.

SpikeZ 2011-03-15 09:29:42 UTC #11

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

mortimers_r 2011-03-15 16:45:50 UTC #12

doh silly me

mortimers_r 2011-03-15 17:31:14 UTC #13

im not getting any search results back.

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

SpikeZ 2011-03-15 20:13:42 UTC #14

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

mortimers_r 2011-03-15 21:20:45 UTC #15

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

r937 2011-03-15 21:23:36 UTC #16

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

SpikeZ 2011-03-15 21:26:44 UTC #17

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

mortimers_r 2011-03-15 21:45:52 UTC #18

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>

SpikeZ 2011-03-15 21:56:40 UTC #19

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

mortimers_r 2011-03-15 22:07:46 UTC #20

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

ThemesPodcastArticlesPremium