I hope you guys can help me. I'm not very knowledgeable about HTML or PHP but I'm trying to learn at least enough to get my personal site up and running. I have been banging my head against the wall trying to figure this out and can't. I have searched and searched but no luck finding what I need
I have a mysql database table. It's only 1 table with 7 columns. In this table is a list of race car drivers and all of the diecast cars that were made for each driver. Driver, Make, Scale, Year, Scheme, Quanty Made. I found some CODE online that I tweaked to search one search term at a time. What I'm looking for is 4 separate drop down boxes to search my table. There are 5 on my page but the one is just for sorting and the sorting works fine. I would like the people that visit my sit to be able to use the drop downs to search any way they like. If they only want to search by using 2 of the drop downs or if they want to search by using only one, or if they want to use them all they can get the results filtered any way they like with any combination of drop downs. I guess I would need to know how to do the "all" statement as well.
This is the code I have so far. Any and all help would be greatly appreciated.
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css">
.auto-style1 {
text-align: center;
}
</style>
</head>
<body style="color: #FFFFFF; background-color: #000000">
<form name="form" action="new2.php" method="get">
<div class="auto-style1">
</select>
Select Driver:
<select name="q" size="5">
<option value="Jeff Gordon">Jeff Gordon</option>
<option value="_Evernham Motorsports">_Evernham Motorsports</option>
<option value="_Multiple Drivers">_Multiple Drivers</option>
<option value="_No Driver">_No Driver</option>
<option value="_Ramchargers">_Ramchargers</option>
<option value="_Richard Childress Racing">_Richard Childress Racing</option>
</select>
Select Make:
<select name="q" size="5">
<option value="Elite">Elite</option>
<option value="ARC">ARC</option>
<option value="Revell">Revell</option>
</select>
Select Scale:
<select name="q" size="5">
<option value="1:64">1:64</option>
<option value="1:32">1:32</option>
<option value="1:24">1:24</option>
</select>
Select Year:
<select name="q" size="5">
<option value="2006">2006</option>
<option value="2005">2005</option>
<option value="2004">2004</option>
</select>
Sort By:
<select name="sort" size="5">
<option value="Driver"<?php if(trim(@$_GET['sort'])=="Driver"){echo " selected";}?>>Driver</option>
<option value="Make"<?php if(trim(@$_GET['sort'])=="Make"){echo " selected";}?>>Make</option>
<option value="Scale"<?php if(trim(@$_GET['sort'])=="Scale"){echo " selected";}?>>Scale</option>
<option value="Year"<?php if(trim(@$_GET['sort'])=="Year"){echo " selected";}?>>Year</option>
<option value="Scheme"<?php if(trim(@$_GET['sort'])=="Scheme"){echo " selected";}?>>Scheme</option>
<input name="Submit" type="submit" value="Update" /></form><br>
<?php
//MySQL options
$user = ""; //your MySql username
$pass = ""; //
$host = "..com"; //your host
//Database options
$database = ""; // the database to search
$table = ""; // the table to search
$col = ""; //column to search. leave blank if you want your users to defint it themselves.
$col_two = "";//second column to display, it displays the cell in this colunm on the same row
$sorted= "";//what to sort by. leave blank for user selection.
$limit=10; // how many at max to display on one page
/*
ALSO!!!
there is one more spot you need to update down further, you need to match it to your MySQL table
*/
?>
<?php
if($col == ""){
$col = trim(@$_GET['col']); //trim whitespace from the stored
}else{}
if($sorted == ""){
$sorted = trim(@$_GET['sort']); //trim whitespace from the stored
}else{}
// Get the search variable from URL
$var = @$_GET['q'];
$trimmed = trim($var); //trim whitespace from the stored variable
$s = trim(@$_GET['s']);
// rows to return
// 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;
}
mysql_connect($host,$user,$pass);
mysql_select_db($database) or die("Unable to select database"); //select which database we're using
$query = "SELECT * FROM diecastlist WHERE $col LIKE '%$trimmed%' order by $sorted";
$numresults=mysql_query($query);
$numrows=mysql_num_rows($numresults);
// If we have no results, offer a google search as an alternative
echo "<title>search results for \"".$trimmed."\" in ".$col."</title>";
if ($numrows == 0)
{
echo "<h4>Results</h4>";
echo "<p>Sorry, your search: \"" . $trimmed . "\" returned no results</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: \"" . $var . "\"</p>";
// begin to show results set
echo "Results:<br>";
/* This its the header, be sure you update this to match your columns in your MySQL table */
echo "<table border=\"1\"align=center>";
echo "<tr>
<td>Driver</td>
<td>Make</td>
<td>Scale</td>
<td>Year</td>
<td>Scheme</td>
<td>Qty</td>
</tr>\n" ;
/* Be sure you update this to match your columns in your MySQL table */
while ($row= mysql_fetch_array($result)) {
$col_1 = $row['Driver'];
$col_2 = $row['Make'];
$col_3 = $row['Scale'];
$col_4 = $row['Year'];
$col_5 = $row['Scheme'];
$col_6 = $row['Qty'];
echo "<tr>";
echo "<td>   $col_1   </td>";
echo "<td>   $col_2   </td>";
echo "<td>   $col_3   </td>";
echo "<td>   $col_4   </td>";
echo "<td>   $col_5   </td>";
echo "<td>   $col_6   </td>";
/* End edit */
echo "</tr>\n";
$count++ ;
}
echo "</table>";
$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 " <a href=\"".$_SERVER["PHP_SELF"]."?s=".$prevs. "&q=" .$var. "&col=" .$col."&sort=" .$sorted."\"><< Prev ".$limit."</a> ";
}
// 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 "<a href='" .$_SERVER["PHP_SELF"]. "?s=" .$news. "&q=" .$var. "&col=" .$col."&sort=" .$sorted."'>Next ".$limit." >></a>";
}
$a = $s + ($limit) ;
if ($a > $numrows) { $a = $numrows ; }
$b = $s + 1 ;
echo "<p>Showing results $b to $a of $numrows</p>";
?>
</div>
</body>
</html>
