Refining an Existing Search

Hello everyone,

I am new to PHP and here's a project I need to finish within a give time frame. I have tried to explain everything in detail. I would appreciate help from some members in the forum.

I have one mySQL database and one table in that database with a few fields.

Given below is my script. What this script basically does is when a search keyword is entered in the form below, it pulls up the rows containing that word from over 100,000 entries in the database. Each row has 4 fields that are denoted as Field1, Field2, Field3, and Field4 in the script below. The placeholders (if you will) to display those fields as output are $title, $title2, $title3,and $title4 in the script below.

The script below has been tested and works fine.

However, here is what I would like to do. Instead of just the one keyword as the search criteria, I would like to be able to search based on other criteria as well. For starters I would like to be able to search based on the length of the keyword and also the extension of the keyword.

Let me explain:

Lets say I have 5 rows in the database each containing different filenames with different extensions with each file name having a certain length (# of characters in name)

dinosaurs.htm (extension = htm , # of characters excluding extension 9)
services.html (extension = html, # of characters excluding extension 8)
layout.css (extension = css, # of characters excluding extension 6)
index.php (extension = php, # of characters excluding extension 5)
coolprogram.cgi (extension = cgi, # of characters excluding extension 11)
greenhouse.asp (extension = asp, # of characters excluding extension 10)

so on ... about 100K such entries.

The script given below just searches for a term in the filename and spits out all rows containing that keyword,

BUT

Here' how I would like to be able to refine the search. I would like to word my query (or queries as) Give me all the filenames that contain the keyword with the result containing filenames only of a given length and a given extension.

So I'm thinking of obviously keeping the text field for the keyword to be searched, and a drop down box to specify the length I want. Then use about 4 checkboxes each representing file extensions from which I could check one in order to refine my search.

I have a certain target date to finish this project, so can someone please help me to refind my search as I explained above? I would really really appreciate it.

I would ike the code that I can integrate in my preexisting code unless someone has a different and better approach altogether.

======== existing code follows below ===========
field names are designated Field1, Field2 etc.

<?php

// Get the search variable from URL
$var = @$_GET['q'] ;
$trimmed = trim($var); //trim whitespace from the stored variable

// rows to return
$limit=100;

// check for an empty string and display a message.
if ($trimmed == "")
{
?>
<p><center>Click on a result for details</center></p>
<?
exit;
}

// check for a search parameter
if (!isset($var))
{
?>
echo "<p>We dont seem to have a search parameter!</p>";

<?
exit;
}

mysql_connect("localhost","MYUSERNAME","MYPASSWORD"); //(host, username, password)

mysql_select_db("MYDATABASE") or die("Unable to select database"); //select which database we're using

// Build SQL Query
$query = "select * from MYTABLENAME where FIELD1 like \"%$trimmed%\" order by FIELD1";

$numresults=mysql_query($query);
$numrows=mysql_num_rows($numresults);

// If we have no results, offer a google search as an alternative

if ($numrows == 0)
{
echo "<h4>Results</h4>";
echo "<p>Sorry, your search: "" . $trimmed . "" returned zero results</p>";

// google
echo "<p><a href=\"http://www.google.com/search?q="
. $trimmed . "\" target=\"_blank\" title=\"Look up
" . $trimmed . " on Google\">Click here</a> to try the
search on google</p>";
}

// next determine if s has been passed to script, if not use 0
if (empty($s)) {
$s=0;
}

<tr>
<td bgcolor=FFCC33 width="10"><font face=Arial, Helvetica, sans-serif size=2><b><center>FIELD1</b></center></font></td>
<td bgcolor=FFCC33 width="300"><font face=Arial, Helvetica, sans-serif size=2><b><center>DISPLAY FIELD 1 VALUE</b></center></font></td>
<td bgcolor=FFCC33 width="10"><font face=Arial, Helvetica, sans-serif size=2><b><center>DISPLAY FIELD 2 VALUE</b></center></font></td>
<td bgcolor=FFCC33 width="100"><font face=Arial, Helvetica, sans-serif size=2><b><center>DISPLAY FIELD 3 VALUE</b></center></font></td>
<td bgcolor=FFCC33 width="100"><font face=Arial, Helvetica, sans-serif size=2><b><center>DISPLAYA FIELD 4 VALUE</b></center></font></td>

<td bgcolor=FFCC33 width="50"><font face=Arial, Helvetica, sans-serif size=2><b><center>Google Links</b></center></font></td>

</tr>

// get results
$query .= " limit $s,$limit";
$result = mysql_query($query) or die("Couldn't execute query");

// display what the person searched for

$a = $s + ($limit) ;
if ($a > $numrows) { $a = $numrows ; }
$b = $s + 1 ;

// begin to show results set

$count = 1 + $s ;
?>

// now you can display the results returned
while ($row= mysql_fetch_array($result)) {
$title = $row["FIELD1"];
$title2 = $row["FIELD2"];
$title3 = $row["FIELD3"];
$title4 = $row["FIELD4"];
?>

<?
$count++ ;
}

$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=\"$PHP_SELF?s=$prevs&q=$var\"><<
Prev 100</a>&nbsp ";
}

// 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++;
}
?>
</table>
<?

// check to see if last page
if (!((($s+$limit)/$limit)==$pages) && $pages!=1) {

// not last page so give NEXT link
$news=$s+$limit;

echo "<br><br><a href=\"$PHP_SELF?s=$news&q=$var\">Next 100 >></a>";

}

$a = $s + ($limit) ;
if ($a > $numrows) { $a = $numrows ; }
$b = $s + 1 ;
echo "<p>Showing results $b to $a of $numrows</p>";

?>
</td></tr>

Refining an Existing Search

Urgent Help Needed

rts5678

mcibor

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week