database search function on an HTML page possible?

Question

Not sure if this is against stackoverflow rules as it's not a specific code question but I really need a little help.

I want to know if it is possible to create a search feature (search box) on an HTML webpage that will query a database and return the results?

Basically I have a database of products and their related categories. A user would come to the website, enter the category in the search field...somehow query the database and return the results on a new page.

Note: the results page doesn't have to be HTML (could be PHP etc).

If you could also include a little guidance on how (please nothing detailed, just need a direction). Thank you!

Answer 1

Yeah, sure, that's pretty easy.

Here's what I would do:

On your first page:

<form action="mypage.php?searching=true" method="POST">
<input type="text" name="searchcategory" value=""/>
<input type="submit" value="Search"/>
</form>

On mypage.php:

<table>
<?php
    $sql=$db->prepare('SELECT * FROM mytable WHERE category=:category');
    $sql->execute(array(':category'=>$_REQUEST[searchcategory]));

    while ($row=$sql->fetch())
    {
       //Here is where you will loop through all the results for your search. If 
       //you had for example for each product name, price, and category, 
       //you might do the following
       echo "<tr><td>$row[name]</td><td>$row[price]</td><td>$row[category]</td></tr>";
    }
?>
</table>

NOTES:

• I use mySQL...I'm not sure how you would do it with something else, but I would guess you could adapt the code for another sort of database.

• I generally use the PDO (accessing through prepare and execute)...my teacher said it was more secure. You can change that part of the code to however you connect to your DB. The code I use to connect looks something like (goes before the code above on mypage.php):

  try {
  $db = new PDO("mysql:host=localhost;dbname=mydatabasename","username","password");      
  } catch (Exception $e) {
  echo "PDO connection error: " . $e->getMessage();
  exit(1);
  }
  

• They don't actually have to be separate pages. If you had both pages php (the first one not straight HTML), you could just change the content based on whether a variable (maybe called searching) was set to true or false.

Let me know if any of that is unclear, and I'd be happy to help you out. Good luck! :)

Answer 2

This is pretty easy. First the HTML

<form action='search.php' method='POST'>
<input type='text' name='search'/>
<input type='submit' value='submit'/>
</form>

And now create a connect.php file with this code

<?php 
 // Connects to Our Database 
$link = new mysqli('localhost', 'username', 'password', 'db_name');

/* check connection */
if (mysqli_connect_errno()) {
    echo "Could not connect to database, please check your connection details";
    exit();
}

 ?> 

Allright, now create the search.php file with the following code

<?php
include 'connect.php';

$search = $_POST['search']."*";

$search_query = $link->prepare("SELECT name FROM products WHERE MATCH(name) AGAINST (? IN BOOLEAN MODE)")
$search_query->bind_param('s', $search);
$search_query->execute();
$search_query->store_result();
$search_rows = $search_query->num_rows;
$search_query->bind_result($product_name);

if($search_rows > 0){
while($search_query->fetch()){
echo "Your search returned $search_rows results";
echo "$product_name <br>";
}
} else { echo "Your search returned no results, sorry :("; }   

What this will do is, it will search the database for what the user searched or part of it and return the results. Lets say you have these products in your database

1)Micorosft mouse
2)iBall mouse
3)Logitech mouse

And lets say the user searched for 'mouse' It will return the info from the database with the string 'mouse' in it :-)

Hope this helped ;)

You will also need to setup a database via phpmyadmin or something if you dont have it already.