how to make a search sql page?

Question

sdgundamuse 0 Newbie Poster

14 Years Ago

i am trying to make a page that able to search the data that store in the sql database, i would like to have a field search which enable the user to choose the field they wan to search.

the problem i facing is i dunno how to submit the data in the dropdown box to the action page to search the sql database for the term.

here is my search page:

<html>
   <title>My Simple Search Form</title>
    <body>
           <form method="GET" action="<?php echo $_SERVER['PHP_SELF'];?>" name="searchForm">
         Search For: <input type="text" name="search" value="<?php echo isset($searchTerms)?$searchTerms:''; ?>" /><br />
         Search In:<br />
         
         <Select name="field"  >
			<Option value="body">Body</option>
			<Option value="title" >Title</option>
			<Option value="desc" >Description</option>
		</Select>
		 
		 <input type="submit" name="submit" value="Search!" />
      </form>
      <?php echo (count($results) > 0)?"Your search term: {$searchTerms} returned:<br /><br />" . implode("", $results):""; ?>
   </body>
</html>

i want to submit the drop down value to the php script and to search for the related field data on my sql.

here is my php script:

$error = array();
$results = array();

if (isset($_GET['search'])) {
   $searchTerms = trim($_GET['search']);
   $searchTerms = strip_tags($searchTerms); // remove any html/javascript.
   
   if (strlen($searchTerms) < 3) {
      $error[] = "Search terms must be longer than 3 characters.";
   }else {
      $searchTermDB = mysql_real_escape_string($searchTerms); // prevent sql injection.
   }
   
 
   if (count($error) < 1) {
      $searchSQL = "SELECT sid, sbody, stitle, sdescription FROM simple_search WHERE ";
      
  
      $types = array();
    // here i dunno how to get the selected option in the page to use in the search.
           
      
         
      $searchResult = mysql_query($searchSQL) or die("There was an error.<br/>" . mysql_error() . "<br />SQL Was: {$searchSQL}");
      
      if (mysql_num_rows($searchResult) < 1) {
         $error[] = "The search term provided {$searchTerms} yielded no results.";
      }else {
         $results = array(); 
         $i = 1;
         while ($row = mysql_fetch_assoc($searchResult)) {
            $results[] = "{$i}: {$row['stitle']}<br />{$row['sdescription']}<br />{$row['sbody']}<br /><br />";
            $i++;

actually i am very new to php, above code is modified version of Simple SQL Search Tutorial by premiso, thx for helping.

php

2 Contributors
10 Replies
143 Views
6 Days Discussion Span
Latest Post 14 Years Ago Latest Post by sdgundamuse

Graphix 68 ---

14 Years Ago

Try this:

$error = array();
$results = array();

if (isset($_GET['search']) && isset($_GET['field'])) {
   $searchTerms = trim($_GET['search']);
   $searchTerms = strip_tags($searchTerms); // remove any html/javascript.
  $searchField = trim($_GET['field']);
  $searchField = strip_tags($searchField);
  $searchField = addslashes($searchField);
   
   if (strlen($searchTerms) < 3) {
      $error[] = "Search terms must be longer than 3 characters.";
   }else {
      $searchTermDB = mysql_real_escape_string($searchTerms); // prevent sql injection.
   }
   
 
   if (count($error) < 1) {
      $searchSQL = "SELECT sid, sbody, stitle, sdescription FROM simple_search WHERE field='$searchField' ";         
         
      $searchResult = mysql_query($searchSQL) or die("There was an error.<br/>" . mysql_error() . "<br />SQL Was: {$searchSQL}");
      
      if (mysql_num_rows($searchResult) < 1) {
         $error[] = "The search term provided {$searchTerms} yielded no results.";
      }else {
         $results = array(); 
         $i = 1;
         while ($row = mysql_fetch_assoc($searchResult)) {
            $results[] = "{$i}: {$row['stitle']}<br />{$row['sdescription']}<br />{$row['sbody']}<br /><br />";
            $i++;
          } // End of while
       } // End of else
    } // End of error check
} // End of isset()

Edited 14 Years Ago by Graphix because: n/a

Graphix 68 ---

14 Years Ago

i have no idea what you table/database structure is, neither do i know what columns. So the query:

$searchSQL = "SELECT sid, sbody, stitle, sdescription FROM simple_search WHERE [B]field[/B]='$searchField' ";

is just an example. You will need to replace the field with the corresponding column.

~G

Graphix 68 ---

14 Years Ago

$error = array();
$results = array();

if (isset($_GET['search']) && isset($_GET['field'])) {
   $searchTerms = trim($_GET['search']);
   $searchTerms = strip_tags($searchTerms); // remove any html/javascript.
  $searchField = trim($_GET['field']);
  $searchField = strip_tags($searchField);
  $searchField = addslashes($searchField);
   
   if (strlen($searchTerms) < 3) {
      $error[] = "Search terms must be longer than 3 characters.";
   }else {
      $searchTermDB = mysql_real_escape_string($searchTerms); // prevent sql injection.
   }
   
 
   if (count($error) < 1) {
      $searchSQL = "SELECT sid, sbody, stitle, sdescription FROM simple_search WHERE $searchField LIKE '%$searchTerms%' ";         
         
      @$searchResult = mysql_query($searchSQL);
     
      if (mysql_num_rows($searchResult) < 1 || !$searchResult) {
         $error[] = "The search term provided {$searchTerms} yielded no results.";
      }else {
         $results = array(); 
         $i = 1;
         while ($row = mysql_fetch_assoc($searchResult)) {
            $results[] = "{$i}: {$row['stitle']}<br />{$row['sdescription']}<br />{$row['sbody']}<br /><br />";
            $i++;
          } // End of while
       } // End of else
    } // End of error check
} // End of isset()

Edited 14 Years Ago by Graphix because: n/a

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

sdgundamuse 0 Newbie Poster · Answer 1 · 2009-11-15T09:41:35+00:00

There was an error.
Unknown column 'field' in 'where clause'
SQL Was: SELECT sid, sbody, stitle, sdescription FROM simple_search WHERE field='body'

is there anything wrong with my html code? because i dunno how to set the value into the field.

from the example i get which is for check box
it appear as

<input type="checkbox" name="body" value="on" <?php echo isset($_GET['body'])?"checked":''; ?> />

but when it come to drop down box, i totally no idea on how it would look like, i try replace the body with field but it wont work.

sdgundamuse 0 Newbie Poster · Answer 2 · 2009-11-17T08:33:20+00:00

my table is look like this

sid stitle sdescription sbody
1 title description text
2 title description text

sdgundamuse 0 Newbie Poster · Answer 3 · 2009-11-18T13:06:12+00:00

the error become
Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\search\search3.php on line 39
Call Stack
# Time Memory Function Location
1 0.0003 326392 {main}( ) ..\search3.php:0
2 0.6560 328168 mysql_num_rows ( ) ..\search3.php:39

my full code:

<?php
$dbHost = 'localhost'; // localhost will be used in most cases
// set these to your mysql database username and password.
$dbUser = 'username'; 
$dbPass = 'password';
$dbDatabase = 'test'; // the database you put the table into.
$con = mysql_connect($dbHost, $dbUser, $dbPass) or die("Failed to connect to MySQL Server. Error: " . mysql_error());

mysql_select_db($dbDatabase) or die("Failed to connect to database {$dbDatabase}. Error: " . mysql_error());

// Set up our error check and result check array
$error = array();
$results = array();
 
if (isset($_GET['search']) && isset($_GET['field'])) {
   $searchTerms = trim($_GET['search']);
   $searchTerms = strip_tags($searchTerms); // remove any html/javascript.
  $searchField = trim($_GET['field']);
  $searchField = strip_tags($searchField);
  $searchField = addslashes($searchField);
 
   if (strlen($searchTerms) < 3) {
      $error[] = "Search terms must be longer than 3 characters.";
   }else {
      $searchTermDB = mysql_real_escape_string($searchTerms); // prevent sql injection.
   }
 
 
   if (count($error) < 1) {
      $searchSQL = "SELECT sid, sbody, stitle, sdescription FROM simple_search WHERE $searchField LIKE '%$searchTerms%' ";         
 
      @$searchResult = mysql_query($searchSQL);
 
      if (mysql_num_rows($searchResult) < 1 || !$searchResult) {
         $error[] = "The search term provided {$searchTerms} yielded no results.";
      }else {
         $results = array(); 
         $i = 1;
         while ($row = mysql_fetch_assoc($searchResult)) {
            $results[] = "{$i}: {$row['stitle']}<br />{$row['sdescription']}<br />{$row['sbody']}<br /><br />";
            $i++;
          } // End of while
       } // End of else
    } // End of error check
} // End of isset()

function removeEmpty($var) {
   return (!empty($var)); 
}
?>
<html>
   <title>My Simple Search Form</title>
   <style type="text/css">
      #error {
         color: red;
      }
   </style>
   <body>
      <?php echo (count($error) > 0)?"The following had errors:<br /><span id=\"error\">" . implode("<br />", $error) . "</span><br /><br />":""; ?>
      <form method="GET" action="<?php echo $_SERVER['PHP_SELF'];?>" name="searchForm">
         Search For: <input type="text" name="search" value="<?php echo isset($searchTerms)?$searchTerms:''; ?>" /><br />
         Search In:<br />
 
         <Select name="field"  >
			<Option value="body">Body</option>
			<Option value="title" >Title</option>
			<Option value="desc" >Description</option>
		</Select>
 
		 <input type="submit" name="submit" value="Search!" />
      </form>
      <?php echo (count($results) > 0)?"Your search term: {$searchTerms} returned:<br /><br />" . implode("", $results):""; ?>
   </body>
</html>

thanks for helping me.

Graphix 68 --- · Answer 4 · 2009-11-18T21:26:10+00:00

Ok, first of all: i thought you had made correct html, with the appropiate values in the options/select. But no. You had no "s" infront of the values. I made a new table that corresponded to yours. And checked the entire code. The following code works fine, if it still doesnt work: update your PHP version.

<?php
$dbHost = 'localhost'; // localhost will be used in most cases
// set these to your mysql database username and password.
$dbUser = 'root'; 
$dbPass = '';
$dbDatabase = 'test'; // the database you put the table into.
$con = mysql_connect($dbHost, $dbUser, $dbPass) or die("Failed to connect to MySQL Server. Error: " . mysql_error());

mysql_select_db($dbDatabase) or die("Failed to connect to database {$dbDatabase}. Error: " . mysql_error());

// Set up our error check and result check array
$error = array();
$results = array();
 
if (isset($_GET['search']) && isset($_GET['field'])) {
   $searchTerms = trim($_GET['search']);
   $searchTerms = strip_tags($searchTerms); // remove any html/javascript.
  $searchField = trim($_GET['field']);
  $searchField = strip_tags($searchField);
  $searchField = addslashes($searchField);
 
   if (strlen($searchTerms) < 3) {
      $error[] = "Search terms must be longer than 3 characters.";
   }else {
      $searchTermDB = mysql_real_escape_string($searchTerms); // prevent sql injection.
   }
 
 
   if (count($error) < 1) {
      $searchSQL = "SELECT sid, sbody, stitle, sdescription FROM simple_search WHERE $searchField LIKE '%$searchTerms%' ";         
 echo $searchSQL;
      @$searchResult = mysql_query($searchSQL);
 
      if (mysql_num_rows($searchResult) < 1 || !$searchResult) {
         $error[] = "The search term provided {$searchTerms} yielded no results.";
      }else {
         $results = array(); 
         $i = 1;
         while ($row = mysql_fetch_assoc($searchResult)) {
            $results[] = "{$i}: {$row['stitle']}<br />{$row['sdescription']}<br />{$row['sbody']}<br /><br />";
            $i++;
          } // End of while
       } // End of else
    } // End of error check
} // End of isset()

function removeEmpty($var) {
   return (!empty($var)); 
}
?>
<html>
   <title>My Simple Search Form</title>
   <style type="text/css">
      #error {
         color: red;
      }
   </style>
   <body>
      <?php echo (count($error) > 0)?"The following had errors:<br /><span id=\"error\">" . implode("<br />", $error) . "</span><br /><br />":""; ?>
      <form method="GET" action="<?php echo $_SERVER['PHP_SELF'];?>" name="searchForm">
         Search For: <input type="text" name="search" value="<?php echo isset($searchTerms)?$searchTerms:''; ?>" /><br />
         Search In:<br />
 
         <select name="field">
			<Option value="sbody">Body</option>
			<Option value="stitle" >Title</option>
			<Option value="sdescription" >Description</option>
		</Select>
 
		 <input type="submit" name="submit" value="Search!" />
      </form>
      <?php echo (count($results) > 0)?"Your search term: {$searchTerms} returned:<br /><br />" . implode("", $results):""; ?>
   </body>
</html>

sdgundamuse 0 Newbie Poster · Answer 5 · 2009-11-19T19:37:43+00:00

thanks a lot, finally it is working:)

just an additional question, if i would like to add another selection to include in the search page other part i think is still the same but this part

$searchSQL = "SELECT sid, sbody, stitle, sdescription FROM simple_search WHERE $searchField LIKE '%$searchTerms%' ";

let my first search field be the $searchField and second search field be $searchfield2
what would de code will become?

Graphix 68 --- · Answer 6 · 2009-11-19T21:40:53+00:00

Then the search-query would become:

SELECT * FROM simple_search WHERE $searchField LIKE '%$searchTerms%' OR $searchField2 LIKE '%$searchTerms2%'

You can add unlimited OR's and AND's

I hope this solves your problem :)

~G

sdgundamuse 0 Newbie Poster · Answer 7 · 2009-11-20T11:24:48+00:00

thanks, i totally clear now
thanks for helping me.:)