Forum Moderators: coopster
The call for the variable ($var = @$_GET['q'];) gets the term searched for from the input box with name "q"....How would i set it up so that it will accept multiple variables and return search results based on both criteria....THANKS!
<body>
<form name="form" action="search.php" method="get">
<input type="text" name="q" />
<input type="text" name="z" />
<input type="submit" name="Submit" value="Search" />
</form>
<table width="630" border="0" cellspacing="0" cellpadding="0" align="center">
<tr>
<td valign="top"><?php
//MySQL options
$user = "";
$pass = "";
$host = "";
//Database options
$database = ""; // the database to search
$table = ""; // the table to search
$col = "wf_FirstName1"; //column to search.
$col2 = "wf_LastName3"; //Second column to search
$col3 = "wf_Address1"; //column to search.
$col4 = "wf_City"; //column to search.
$col5 = "wf_State1"; //column to search.
$col6 = "wf_ZipCode"; //column to search.
$col7 = "cp1"; //column to search.
$col8 = "cp2"; //column to search.
$col9 = "cp3"; //column to search.
$cola = "wf_EmailAddress"; //column to search.
$coli = "wf_9"; //column to search.
$colj = "wf_6"; //column to search.
$colk = "wf_7"; //column to search.
$coll = "wf_11"; //column to search.
$colm = "wf_32"; //column to search.
$coln = "wf_34"; //column to search.
$colo = "wf_MaritalStatus1"; //column to search.
$colp = "referral"; //column to search.
$colq = "dt"; //column to search.
$colr = "tm"; //column to search.
$col_two = ""; //second column to display, it displays the cell in this colunm on the same row
$sorted= "wf_FirstName1"; //what to sort by
$limit=25; // how many at max to display on one page
?>
<?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 $table
where $col like \"%$trimmed%\"
or $col2 like \"%$trimmed%\"
or $col3 like \"%$trimmed%\"
or $col4 like \"%$trimmed%\"
or $col5 like \"%$trimmed%\"
or $col6 like \"%$trimmed%\"
or $col7 like \"%$trimmed%\"
or $col8 like \"%$trimmed%\"
or $col9 like \"%$trimmed%\"
or $cola like \"%$trimmed%\"
or $colb like \"%$trimmed%\"
or $colc like \"%$trimmed%\"
or $cold like \"%$trimmed%\"
or $cole like \"%$trimmed%\"
or $colf like \"%$trimmed%\"
or $colg like \"%$trimmed%\"
or $colh like \"%$trimmed%\"
or $coli like \"%$trimmed%\"
or $colj like \"%$trimmed%\"
or $colk like \"%$trimmed%\"
or $coll like \"%$trimmed%\"
or $colm like \"%$trimmed%\"
or $coln like \"%$trimmed%\"
or $colo like \"%$trimmed%\"
or $colp like \"%$trimmed%\"
or $colq like \"%$trimmed%\"
or $colr like \"%$trimmed%\"
order by $sorted";
$numresults=mysql_query($query);
$numrows=mysql_num_rows($numresults);
// If we have no results
echo "<title>search results for \"".$trimmed."\" in ".$col."</title>";
if ($numrows == 0)
{
//echo "<p span class=searchboxtype3>Sorry, your search: \"" . $trimmed . "\" returned 0 results.</span>";
}
// 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>";
$count = 1 + $s ;
/* This its the header, be sure you update this to match your columns in your MySQL table */
echo "<table border=\"0\" width=\"500\" align=center>";
echo "
<tr>
<td colspan=3 class=qtypes>There is $numrows result(s) that match \"$trimmed\". Results are Ordered by First Name.
<br><br>
</td>
</tr><tr>
<td colspan=3></td>
</tr>\n" ;
echo "</table>";
echo "<table border=\"1\" width=\"2500\" align=center>";
while ($row= mysql_fetch_array($result)) {
$col_1 = $row['wf_FirstName1'];
$col_2 = $row['wf_LastName3'];
$col_3 = $row['wf_Address1'];
$col_4 = $row['wf_City'];
$col_5 = $row['wf_State1'];
$col_6 = $row['wf_ZipCode'];
$col_7 = $row['cp1'];
$col_8 = $row['cp2'];
$col_9 = $row['cp3'];
$col_a = $row['wf_EmailAddress'];
$col_i = $row['wf_9'];
$col_j = $row['wf_6'];
$col_k = $row['wf_7'];
$col_l = $row['wf_11'];
$col_m = $row['wf_32'];
$col_n = $row['wf_34'];
$col_q = $row['dt'];
$col_r = $row['time'];
echo "<tr>";
echo "<td>$col_1</td><td>$col_2</td><td>$col_3</td><td>$col_4</td><td>$col_5</td><td>$col_6</td><td>$col_7 $col_8 $col_9</td>
<td>$col_a</td><td>$col_b</td><td>$col_c</td><td>$col_d</td><td>$col_e</td><td>$col_f</td><td>$col_g</td><td>$col_h</td><td>$col_i-$col_j-$col_k</td>
<td>$col_l $col_m $col_n</td><td>$col_p</td><td>$col_q</td>";
/* End edit */
echo "</tr><tr><td colspan=3></td></tr>\n";
$count++ ;
}
echo "</table>";
$currPage = (($s/$limit) + 1);
//break before paging
// echo "<br>";
?><table align="left"><tr><td align="left"><?php
// next we need to do the links to other results
if ($s>=1) { // bypass PREV link if s is 0
$prevs=($s-$limit);
print " <span class=searchboxtype3><a href=\"".$_SERVER["PHP_SELF"]."?s=".$prevs. "&q=" .$var. "&col=" .$col."&sort=" .$sorted."\"><< Prev ".$limit." </a></span> ";
}
// 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 "<span class=searchboxtype3><a href='" .$_SERVER["PHP_SELF"]. "?s=" .$news. "&q=" .$var. "&col=" .$col."&sort=" .$sorted."'> Next ".$limit." >></a></span>";
}
$a = $s + ($limit) ;
if ($a > $numrows) { $a = $numrows ; }
$b = $s + 1 ;
echo "<span class=searchboxtype4><br><br>Showing results $b to $a of $numrows</span><br><br><br>";
?>
[dev.mysql.com...]
$sql = "SELECT * FROM table WHERE MATCH ($col, $col2) AGAINST ('$trimmed')";
Hope this helps.
On the other side I doubled the relevant data:
I have columns: name, surname, tel and search with data with all of them separated by space.
Merry Xmas
Michal
$query = "SELECT * FROM $table WHERE MATCH ($col, $col2, $col3, $col4, $col5, $col6, $col7, $col8,
$col9, $cola, $colb, $colc, $cold, $cole, $colf, $colg) AGAINST ('$trimmed')";
My questions is how do you search based on more than 1 variable...if i want to search all records for a name of Michael Smith...Currently, if i search for Michael, i get all the michales in the table...I want to narrow the search to just the one Michael Smith....Any Ideas?
$query = "SELECT * FROM $table WHERE MATCH ($col, $col2, $col3, $col4, $col5, $col6, $col7, $col8,
$col9, $cola, $colb, $colc, $cold, $cole, $colf, $colg) AGAINST ('$trimmed') AND $col = 'Michael' AND $col2 = 'Smith'";
I believe that should work.
...And welcome to WebmasterWorld! :)
We have a search form
<form name="form" action="search.php" method="get">
<input type="text" name="q" />
<input type="text" name="z" />
<input type="submit" name="Submit" value="Search" />
</form>
name="q" is the First Name Query
name="z" is the Last Name Query
If someone types in Michael for the "q" and Smith for the "z" for the results to only show the row for Michael Smith.
something like that though the colnames may not be right
if this is your actual form
<input type="text" name="q" />
<input type="text" name="z" />
it won't work with 2 textboxes with the same name
Name: Michael Jones, City: Los Angeles, Age: 36, Kids: No
Name: John Smith, City: New York, Age: 22, Kids: Yes
Name: Dave Miller, City: Los Angeles, Age: 36, Kids: Yes
Name: Amanda Jones, City: Los Angeles, Age: 64, Kids: No
From reading around, everybody seems to recommend Full-Text searching in mysql. I went ahead and indexed our table and have built a Full-Text Index. I have incorporated the proper syntax in the below code so that it will use the Full-Text index to search (in boolean mode as well).
My problem is that the below code is genericly written to extract data based on 1 criteria. After searching and reading, there were a few suggestions but im missing something to tie them all together. My SQL query is as follows:
$query = "SELECT * FROM $table WHERE MATCH ($col, $col2, $col3, $col4, $col5, $col6, $col7, $col8,
$col9, $cola, $colb, $colc, $cold, $cole, $colf, $colg, $colh) AGAINST ('$trimmed' IN BOOLEAN MODE)";
This basically, to my understanding, selects all the data from the "table variable" and searches the specified columns ($col-$colh) for the search term "$trimmed".
$Trimmed is actually - $trimmed = trim($var);
$var is actually - $var = @$_GET['q'];
I would assume that "q" is coming from the input name
<input type="text" name="q">
So, sorry to stray, to my question: Based upon what we have now, what would be the logical way to set it up so that it will search based upon a number of criteria as opposed to just 1
I have included the entire code below in case i missed something. I am somewhat of a newbie to php but i typically pick up on things pretty quickly when explained so thanks for your patience...
<body>
<form name="form" action="search.php" method="get">
<input type="text" name="q" />
<input type="submit" name="Submit" value="Search" />
</form>
<table width="630" border="0" cellspacing="0" cellpadding="0" align="center">
<tr>
<td valign="top">
<?php
//Database options
$database = ""; // the database to search
$table = ""; // the table to search
$col = "Status"; //column to search.
$col2 = "wa"; //column to search.
$col3 = "wf_FirstName1"; //column to search.
$col4 = "wf_LastName3"; //Second column to search
$col5 = "wf_Address1"; //column to search.
$col6 = "wf_City"; //column to search.
$col7 = "wf_State1"; //column to search.
$col8 = "wf_ZipCode"; //column to search.
$col9 = "wf_EmailAddress"; //column to search.
$cola = "wf_P"; //column to search.
$colb = "wf_Pr"; //column to search.
$colc = "wf_Use"; //column to search.
$cold = "wf_Type"; //column to search.
$cole = "wf_34"; //column to search.
$colf = "referral"; //column to search.
$colg = "dt"; //column to search.
$colh = "completion";
$col_two = ""; //second column to display, it displays the cell in this colunm on the same row
$sorted= "wf_FirstName1"; //what to sort by
$limit=25; // how many at max to display on one page
?>
<?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 $table WHERE MATCH ($col, $col2, $col3, $col4, $col5, $col6, $col7, $col8,
$col9, $cola, $colb, $colc, $cold, $cole, $colf, $colg, $colh) AGAINST ('$trimmed' IN BOOLEAN MODE)";
$numresults=mysql_query($query);
$numrows=mysql_num_rows($numresults);
// If we have no results
echo "<title>search results for \"".$trimmed."\" in ".$col."</title>";
if ($numrows == 0)
{
//echo "<p span class=searchboxtype3>Sorry, your search: \"" . $trimmed . "\" returned 0 results.</span>";
}
// 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>";
$count = 1 + $s ;
/* This its the header, be sure you update this to match your columns in your MySQL table */
echo "<table border=\"0\" width=\"500\" align=center>";
echo "
<tr>
<td colspan=3 class=qtypes>There is $numrows result(s) that match \"$trimmed\". Results are Ordered by First Name.
<br><br>
</td>
</tr><tr>
<td colspan=3></td>
</tr>\n" ;
echo "</table>";
echo "<table border=\"1\" width=\"2500\" align=center>";
echo "<tr><td><strong>First Name</strong></td><td><strong>Last Name</strong></td><td><strong>Address</strong></td><td><strong>City</strong></td>
<td><strong>State</strong></td><td><strong>Zip Code</strong></td><td><strong>Phone Number</strong></td><td><strong>Email Address</strong></td>
<td><strong>Value</strong></td><td><strong>Location</strong></td><td><strong>Use</strong></td><td><strong>Type</strong></td>
<td><strong>Type</strong></td><td><strong>I</strong></td><td><strong>Referral</strong></td><td><strong>Date</strong></td>
</tr>";
while ($row= mysql_fetch_array($result)) {
$col_1 = $row['wf_FirstName1'];
$col_2 = $row['wf_LastName3'];
$col_3 = $row['wf_Address1'];
$col_4 = $row['wf_City'];
$col_5 = $row['wf_State1'];
$col_6 = $row['wf_ZipCode'];
$col_7 = $row['cp1'];
$col_8 = $row['cp2'];
$col_9 = $row['cp3'];
$col_a = $row['wf_EmailAddress'];
$col_b = $row['wf_P'];
$col_c = $row['wf_Pr'];
$col_d = $row['wf_Use'];
$col_e = $row['wf_Type'];
$col_i = $row['wf_9'];
$col_j = $row['wf_6'];
$col_k = $row['wf_7'];
$col_l = $row['wf_11'];
$col_m = $row['wf_32'];
$col_n = $row['wf_34'];
$col_o = $row['wf_M'];
$col_p = $row['referral'];
$col_q = $row['dt'];
$col_r = $row['time'];
echo "<tr>";
echo "<td>$col_1</td><td>$col_2</td><td>$col_3</td><td>$col_4</td><td>$col_5</td><td>$col_6</td><td>$col_7 $col_8 $col_9</td>
<td>$col_a</td><td>$col_b</td><td>$col_c</td><td>$col_d</td><td>$col_e</td><td>$col_f</td><td>$col_g</td><td>$col_h</td><td>$col_i-$col_j-$col_k</td>
<td>$col_l $col_m $col_n</td><td>$col_p</td><td>$col_q</td>";
/* End edit */
echo "</tr><tr><td colspan=3></td></tr>\n";
$count++ ;
}
echo "</table>";
$currPage = (($s/$limit) + 1);
//break before paging
// echo "<br>";
?><table align="left"><tr><td align="left"><?php
// next we need to do the links to other results
if ($s>=1) { // bypass PREV link if s is 0
$prevs=($s-$limit);
print " <span class=searchboxtype3><a href=\"".$_SERVER["PHP_SELF"]."?s=".$prevs. "&q=" .$var. "&col=" .$col."&sort=" .$sorted."\"><< Prev ".$limit." </a></span> ";
}
// 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 "<span class=searchboxtype3><a href='" .$_SERVER["PHP_SELF"]. "?s=" .$news. "&q=" .$var. "&col=" .$col."&sort=" .$sorted."'> Next ".$limit." >></a></span>";
}
$a = $s + ($limit) ;
if ($a > $numrows) { $a = $numrows ; }
$b = $s + 1 ;
echo "<span class=searchboxtype4><br><br>Showing results $b to $a of $numrows</span><br><br><br>";
?>
</body>
to return 1/1/3 answers, then I see only one east way to do that and one very hard.
Hard way requires long parsing of the q. Easy way requires you have an additional fields:
search with doubled data, which looks like:
'Name: Michael Jones, City: Los Angeles, Age: 36, Kids: No'
'Name: John Smith, City: New York, Age: 22, Kids: Yes'
'Name: Dave Miller, City: Los Angeles, Age: 36, Kids: Yes'
'Name: Amanda Jones, City: Los Angeles, Age: 64, Kids: No'
The the query is:
$q = htmlspecialchars(trim($_GET['q']),ENT_QUOTES);
$q = str_replace(" ", "%", $q);
$sql = "SELECT * FROM $table WHERE `search` LIKE '%$q%' LIMIT $s,$limit";
Hope this helps you to solve all your problems.
PS. Don't post irrelevant script here. Displaying the results and paging them is the irrelevant code.
Regards
Michal Cibor
<input type="text" name="q">
or
<input type="text" name="q">
<input type="text" name="r">
<input type="text" name="s">
Dont i have to include the Input names when there are multiple input boxes? (EG, ($_GET['q,r,s'])
each box to columns:
<input type="text" name="col1">
<input type="text" name="col2">
<?php
//parse input
$trim1 = mysql_real_escape_string(trim($_POST['col1']));
$trim2 = mysql_real_escape_string(trim($_POST['col2']));
$sql = "SELECT * FROM $table WHERE $col1 = '$trim1' AND $col2 = '$trim2' LIMIT 0,2";
Hope this helps
Michal
PS. You can use WHERE $col1 LIKE '%trim1%'... if you wish
Happy New Year
