Help PHP Search database by selecting checkbox

[greensleeves]

Hi, Im a newbie to PHP and I'm wondering if someone could help me out with this
Tryng to search out products from a database by clicking a checkbox option

for instance if description contains the word "bluetooth" and "MP3"

<input type="checkbox" value="bt" />Bluetooth <br /> 
<input type="checkbox" value="cs" />Card slot <br /> 
<input type="checkbox" value="mp3" />MP3 player <br /> 
 

here is my database

$query = "SELECT id, manufacturer, item_number, description FROM $table_name 
$result = @mysql_query ($query) 
 

Here is my table layout

echo '<table> 
<tr> 
<td class="layout"><b><a href="' . $link1 . '">Manufacturer</a></b></td> 
<td class="layout"><b><a href="' . $link2 . '">Item Number</a></b></td> 
<td class="layout"><b><a href="' . $link3 . '">Description</a></b></td> 
</tr> 
'; 
 
// Fetch and print all the records. 
$bg = '#eeeeee'; // Set the background color. 
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) { 
$bg = ($bg=='#eeeeee'? '#ffffff' : '#eeeeee'); // Switch the background color. 
echo '<tr bgcolor="' . $bg . '"> 
<td class="layout_nowwrap">' . $row['manufacturer'] . '</td> 
<td class="layout">' . $row['item_number'] . '</td> 
<td class="layout">' . $row['description'] . '</td> 
</tr> 
'; } 
 
echo '</table>'; 
 

just in case your wondering I also set cases for sorting through colums

$link1 = "{$_SERVER['PHP_SELF']}?sort=mfa"; // manufacturer 
$link2 = "{$_SERVER['PHP_SELF']}?sort=ina"; // item_number 
#and so on.... 
 
// manufacturer case 
case 'mfa': 
$order_by = 'manufacturer DESC, item_number DESC'; 
$link1 = "{$_SERVER['PHP_SELF']}?sort=mfd"; 
break; 
case 'mfd': 
$order_by = 'manufacturer ASC, item_number ASC'; 
$link1 = "{$_SERVER['PHP_SELF']}?sort=mfa"; 
break; 
 

hope someone can help me with this... thanks

[Lumpy]

Well, by far there are many people on this site that are way better php programmers than myself, but I will see what I can do to help you out. I have gotten lots of help here and I want to try to give some back, so here it goes.

Your form, the input boxes, don't have names and I am unsure if you are using POST or GET with the form. In my example I am going to use POST. The other thing I am unsure of is if the same page is going to handle the forms action, but I am going to assume so. With that being said, you need to add a little to your checkboxes on the form, mainly the form tags telling the form what to do when submitted, and the submit, or in this case search button. So it should look something like this...

  <form action="page.php" method="post">
    <input type="checkbox" value="bt" name="bt"/>Bluetooth <br />
    <input type="checkbox" value="cs" name="cs"/>Card slot <br />
    <input type="checkbox" value="mp3" name="mp3"/>MP3 player <br />
    <input type="submit" value="search"/>
  </form>
 

The forms action is set to whatever the page is the will process the search, I just put "page.php" in there and the method is set to post. I added in the name attribute of the checkbox inputs and put a submit button on the bottom that says "search".

Than on the page that will handle this request, you will need to do something like the following with your php to build the rest of your SQL statement to get the right data from your query. Try to follow me here, there might be a better way, but this is how I saw the solution...

The first thing I did was move the search terms from the POST array to a new array called searchterm going something like this...

foreach ($_POST as $var) {
 $searchterm[] = $var;
}
 
 

Then you want to count the number of variables in the searchterm array and begin building the rest of your SQL statement. I also added in another variable in the if statment to see if we need to add the AND(it could be OR in your case as well, I don't know) into the SQL statement for searching for results. The if statement will add the AND for each time through the while statement after the first time through. This code would go after where you already stated the first part of your $query variable, because we want to add on to it.

 
$c = count($searchterm);
///check to see that there is a search term and add the WHERE into the SQL
if ($c > 0) {
  $query .= " WHERE";
}
 
$i = 1;
 
while ($c-- > 0) {
///an if statement to check and see if we should add the "AND" into the query 
 if ($i > 1) {
   $query .= " AND";
 }
///add in the search term or value into the query
$query .= " fieldname = " . $searchterm[$c];
$i++;
}
 
 

Something like that should work, although the SQL syntax may need a little tweaking, I am not sure with the SQL part. it might need to be something like...

 $query .= " fieldname = \' " . $searchterm . " \' ";
 

anyways, I hope this helps.

[greensleeves]

Thank you for the reply. This page is handling it SELF i'm not sure if this is good practice but yeah thats what im tryn to do. Could u pls explain how I would call the tags and have the query search out and match these values. Would i need to set and call a function? Also how would i set these search filters on various fieldnames

<?
include ('./includes/header.html');

require_once ('./mysql_connect.php');

foreach ($_POST as $var) {
$searchterm[] = $var;
}

// query.
$query = "SELECT id, manufacturer, item_number, description FROM $table_name ";
$c = count($searchterm);
///check to see that there is a search term and add the WHERE into the SQL
if ($c > 0) {
$query .= " WHERE";
}

$i = 1;

while ($c-- > 0) {
///an if statement to check and see if we should add the "AND" into the query
if ($i > 1) {
$query .= " AND";
}
///add in the search term or value into the query
$query .= " description = " . $searchterm[$c];
$i++;
}

$query .= " description = \' " . $searchterm . " \' ";
$query .= "ORDER BY manufacturer ASC ";

$result = @mysql_query ($query); // Run the query.

// checkbox
echo '<form action="page.php" method="post">
<input type="checkbox" value="bluetooth" name="bluetooth"/>Bluetooth <br />
<input type="checkbox" value="mp3" name="mp3"/>MP3 <br />
<input type="checkbox" value="video" name="video"/>Video <br />

<input type="submit" value="search"/>
</form> ';

// Table header.
echo '<table> ';

$bg = '#eeeeee'; // Set the background color.
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
$bg = ($bg=='#eeeeee' ? '#ffffff' : '#eeeeee'); // Switch the background color.
echo '<tr bgcolor="' . $bg . '">
<td>' . $row['manufacturer'] . '</td>
<td>' . $row['item_number'] . '</td>
<td>' . $row['description'] . '</td>
</tr>
';
}
echo '</table>';

mysql_free_result ($result);
mysql_close(); // Close the database connection.

include ('./includes/footer.html');
?>

thanks again for your help

[Lumpy]

I am not sure if I am understanding what your asking, but let me take a shot at answering it. You want to know how to call the tags or items and then have the query search for it. This is dependent on what the user clicks as far as what they are going to search for. So if they click mp3, you want the query to search for and return rows of products in the mp3 category. Right now the code is going to build that query for you. One thing I did notice is that the query is going to check the field named description. I am not sure off the top of my head how to get a query to search through the whole description of a product to search for 'mp3', but I would check the mysql manual available on the website. Another option would be to add a field to your table possibly named category and use that field to match the search with, or create another table and join them together.

The query right now when someone clicks an option to search, such as mp3 is going to take that information and search for and return rows "WHERE description = mp3", that is why we put the WHERE in the query. Now if the description field is a product description, it will probably return 0 rows because each row will most likely have more than just "mp3" as a description, this is why I suggested adding in the category field and search that field.

If no search is entered by the user, the query should go through and pull all the rows from the table, because it will skip adding in the "WHERE" clause of the query. You will also, most likely want to change the "AND" to an "OR", so when a search is done with bt and mp3, your query will return both types of products. The "OR" will have the query pull a row if the description is mp3 OR bt.

For the second question, if you want to set the search to different fieldnames, then I would look into creating a data abstraction layer. This is a class that will handle data retrieval and you could just pass the field name to search into the function within the class object and search that fieldname with your query. There is an excellent article written by Motoma on this exact topic... http://www.thescripts.com/forum/thread632487.html. That should give you a good idea how to accomplish this.

One other thing, I removed a line from your code. The line I removed was the last line I had in my suggestion, and I put it not because it needed to be there but that might be what is needed to get the SQL syntax correct in place of this line...$query .= " description = " . $searchterm[$c];

<?php
include ('./includes/header.html');
 
require_once ('./mysql_connect.php');
 
foreach ($_POST as $var) {
 $searchterm[] = $var;
}
 
// query.
$query = "SELECT id, manufacturer, item_number, description FROM $table_name ";
$c = count($searchterm);
///check to see that there is a search term and add the WHERE into the SQL
if ($c > 0) {
  $query .= " WHERE";
}
 
$i = 1;
 
while ($c-- > 0) {
///an if statement to check and see if we should add the "AND" into the query 
 if ($i > 1) {
   $query .= " OR";
 }
///add in the search term or value into the query
$query .= " description = " . $searchterm[$c];
$i++;
}
 
$query .= "ORDER BY manufacturer ASC ";        
 
$result = @mysql_query ($query); /// Run the query.
 
/// checkbox
echo '<form action="page.php" method="post">
<input type="checkbox" value="bluetooth" name="bluetooth"/>Bluetooth<br />
<input type="checkbox" value="mp3" name="mp3"/>MP3<br />
<input type="checkbox" value="video" name="video"/>Video<br />
<input type="submit" value="search"/>
</form> ';
 
/// Table header.
echo '<table> ';
 
$bg = '#eeeeee'; /// Set the background color.
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
    $bg = ($bg=='#eeeeee' ? '#ffffff' : '#eeeeee'); /// Switch the background color.
    echo '<tr bgcolor="' . $bg . '">
        <td>' . $row['manufacturer'] . '</td>
        <td>' . $row['item_number'] . '</td>
        <td>' . $row['description'] . '</td>
    </tr>
    ';
}
echo '</table>';
 
mysql_free_result ($result);     
mysql_close(); /// Close the database connection;
 
include ('./includes/footer.html');
?>
 

Hope this helps.

[greensleeves]

Nice... Thanks for pointed me in the right direction. With a lil tweaking I was able to search through the description field and display the results just as I wanted... Thank you so much for your help :)

[Lumpy]

Nice... Thanks for pointed me in the right direction. With a lil tweaking I was able to search through the description field and display the results just as I wanted... Thank you so much for your help :)

No Problem! I'm glad you were able to get it working and I was able to be of some help to you. :D

[greensleeves]

I would like to take this a little further now hope you can help… So this is the thing I want to sort through the results query’d on checking. I set up cases to handle the table sorting but when I click the results are query’d back to the original all not just the filtered results

How can I keep and sort through the filtered results

$link2 = "{$_SERVER['PHP_SELF']}?sort=mfa"; // manufacturer
$link3 = "{$_SERVER['PHP_SELF']}?sort=ina"; // item_number
$link7 = "{$_SERVER['PHP_SELF']}?sort=dna"; // description

// Determine the sorting order.
if (isset($_GET['sort'])) {

switch ($_GET['sort']) {
case 'mfa':
$order_by = 'manufacturer DESC, item_number DESC, color DESC';
$link2 = "{$_SERVER['PHP_SELF']}?sort=mfd";
break;

case 'mfd':
$order_by = 'manufacturer ASC, item_number ASC, color ASC';
$link2 = "{$_SERVER['PHP_SELF']}?sort=mfa";
break;
// repeats so cut out for space

default:
$order_by = 'manufacturer ASC, item_number ASC';
break;
}

$sort = $_GET['sort'];
} else { // default sorting order.
$order_by = 'manufacturer ASC, item_number ASC';
$sort = 'mfd';
}

// Here's the table
echo '<table>
<tr>
<td><b><a href="' . $link2 . '">Manufacturer</a></b></td>
<td><b><a href="' . $link3 . '">Item Number</a></b></td>
<td><b><a href="' . $link7 . '">Description</a></b></td>
</tr>
';

// Fetch and print all the records.
$bg = '#eeeeee'; // Set the background color.
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
$bg = ($bg=='#eeeeee'? '#ffffff' : '#eeeeee'); // Switch the background color.

echo '<tr bgcolor="' . $bg . '">
<td>' . $row['manufacturer'] . '</td>
<td>' . $row['item_number'] . '</td>
<td>' . $row['description'] . '</td>
</tr> ';
}
echo '</table>';

also how would I keep the checkboxes checked on submiting right now they reset to off on _POST
any help??

[Lumpy]

If I am getting your question, by keeping the box checked after the user checks it, that would solve your problem of the query returning to the original query. Correct?

If that is the case, you will need to add in a little php script when drawing your form's check boxes, to see if they were checked before, and if they were then you would add in 'checked="checked" to the html checkbox tag.

 
if (isset($_POST['mp3'])) {
echo '<input type="checkbox" value="mp3" name="mp3" checked="checked"/>';
} else {
echo '<input type="checkbox" value="mp3" name="mp3"/>';
}
 

I don't know that there would be a better way to do it, but if I think of it I'll let you know.

PS putting the "CODE" tags around your code will make it post a little better and easier to read. Before your first line of code you would want to enclose CODE=php within [ ] and after the last line of code, you want to enclose /CODE within [ ].

[greensleeves]

Thanks again for your reply. Well although this did help keep the checkboxes checked on POST it didn't solve the problem of the query resetting back when the sort link is selected. When the sort link is selected the checkboxes go back to being unchecked :( Any further solutions for this??

[Lumpy]

I get what your saying now. For some reason it didn't click what you were asking before. I can see how that isn't going to help.

To get it to do this, can you change your forms method to "GET"? and make a few changes in your code, something like...


The first change is the foreach statement that moves your GET variables to the $searchterm array...

foreach ($_GET as $var) {
 if (!$_GET['sort']) {
 $searchterm[] = $var;
 }
}
 

The next change is going to be how the query gets the order by instructions. This will have to come after your switch statement but before the query is ran.

$query .= "ORDER BY" . $order_by . "';";
 

And then you will have to have php build you links, something like this so they will have all the right info in them...

$link = "{$_SERVER['PHP_SELF']}";
foreach ($_GET as $key => $var) {
    $link .= "?" . $key . "=" . $var;
}
 

it will take a little tweaking to work the way you want it, but by using a GET method with your form, you can just keep all the variables right in the url and keep reusing them as you continue to change the appearance. Give this a shot and I think you can get it how you want it...