PHP - Search Multiple Mysql Tables For Search Function

Hello,

Does anyone know a tutorial I can follow to create my own search engine over the items I have in my SQL database?
I find a lot of tutorials but they are all 'one word searches' which means if you type two words you will get all results that contain either word (too many hits).

If the search engine tutorial displays result with AJAX even better.

Thanks for help.
df

 The result pages is supposed to have  pagination like google  help me please

 

I have a search set up to search a table for the text entered in a textbox, I have two columns in the table, one with the first name of people, and the second with their last names, I am wondering how I can search both, so for instance:

I type in the search field: Roger Smith

in the database it would look like:

First_name-----|-----Last_name
-------------------|-------------------
Roger------------|-------Smith

my current query is:

Code: [Select]
$query = mysql_query("SELECT * FROM users WHERE fname LIKE '%$find%' OR lname LIKE '%$find%'");
But if I type both parts of the name it doesn't return anything. works fine if I just search for "Roger" OR "Smith".

I have a multiple choice search page which asks visitors to select one or more choices before rendering the search results. It only works in items 1+2 are selected, or 1+2+3, or 1+2+3+4, or 1+2+3+4, etc...

I want to make it so that any combination of choices work. Example: Items 1+6 or 2+3+5 or 1+4+6 etc...

I have four hours to fix it before my client shows up and need help BADLY.

Anyone out there who can help? Whoever can help me... you are the best and I will have a special prayer for you tonight.

Here is the coding:

<?php } else{
if(!empty($_GET['latt']) && !empty($_GET['long']))
{

$condition="SELECT ( 3959 * acos( cos( radians(  '".$_GET['latt']."' ) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(  '".$_GET['long']."' ) ) + sin( radians( '".$_GET['latt']."' ) ) * sin( radians( latitude ) ) ) ) as distance,id,membership FROM notary_members ";

   if(!empty($_GET['s']))
   {
      $a=" where and";
   }
   else
   {
      $a=" where";
   }
   if($_GET['edoc']!='')
   {
      $condition.=$a." edoc= 'yes'";
   }
   if($_GET['esign']!='')
   {
      if($_GET['edoc']!='')
         $condition.=" and esign= 'yes'";
      else
         $condition.=$a." esign= 'yes'";
   }
   if($_GET['laserprinter']!='')
   {
      if($_GET['esign']!='' || $_GET['edoc']!='')
         $condition.=" and laserprinter= 'yes'";
      else
         $condition.=" laserprinter= 'yes'";
   }
   if($_GET['dualtray']!='')
   {
      if( $_GET['laserprinter']!='' || $_GET['esign']!='' || $_GET['edoc']!='')
         $condition.=" and dualtray= 'yes'";
      else
         $condition.=" dualtray= 'yes'";
   }
   if($_GET['error_in']!='')
   {
      if($_GET['dualtray']!='' || $_GET['laserprinter']!='' || $_GET['esign']!='' || $_GET['edoc']!='')
         $condition.=" and error_in= 'yes'";
      else
         $condition.=" error_in= 'yes'";
   }
   if($_GET['bg_checked']!='')
   {
      if($_GET['error_in']!='' || $_GET['dualtray']!='' || $_GET['laserprinter']!='' || $_GET['esign']!='' || $_GET['edoc']!='')
         $condition.=" and bg_checked= 'yes'";
      else
         $condition.=" bg_checked= 'yes'";
   }
   if($_GET['home_insp']!='')
   {
      if($_GET['bg_checked']!='' || $_GET['error_in']!='' || $_GET['dualtray']!='' || $_GET['laserprinter']!='' || $_GET['esign']!='' || $_GET['edoc']!='')
         $condition.=" and home_insp= 'yes'";
      else
         $condition.=" home_insp= 'yes'";
   }
   if($_GET['fingerprint']!='')
   {
      if($_GET['home_insp']!='' || $_GET['bg_checked']!='' || $_GET['error_in']!='' || $_GET['dualtray']!='' || $_GET['laserprinter']!='' || $_GET['esign']!='' || $_GET['edoc']!='')
         $condition.=" and fingerprint= 'yes'";
      else
         $condition.=" fingerprint= 'yes'";
   }
   if($_GET['hosp_signing']!='')
   {
      if($_GET['fingerprint']!='' || $_GET['home_insp']!='' || $_GET['bg_checked']!='' || $_GET['error_in']!='' || $_GET['dualtray']!='' || $_GET['laserprinter']!='' || $_GET['esign']!='' || $_GET['edoc']!='')
         $condition.=" and hosp_signing= 'yes'";
      else
         $condition.=" hosp_signing= 'yes'";
   }
   if($_GET['jail_signing']!='')
   {
      if($_GET['hosp_signing']!='' || $_GET['fingerprint']!='' || $_GET['home_insp']!='' || $_GET['bg_checked']!='' || $_GET['error_in']!='' || $_GET['dualtray']!='' || $_GET['laserprinter']!='' || $_GET['esign']!='' || $_GET['edoc']!='')
         $condition.=" and jail_signing= 'yes'";
      else
         $condition.=" jail_signing= 'yes'";
   }
   if($_GET['im_docs']!='')
   {
      if($_GET['jail_signing']!='' || $_GET['hosp_signing']!='' || $_GET['fingerprint']!='' || $_GET['home_insp']!='' || $_GET['bg_checked']!='' || $_GET['error_in']!='' || $_GET['dualtray']!='' || $_GET['laserprinter']!='' || $_GET['esign']!='' || $_GET['edoc']!='')
         $condition.=" and im_docs= 'yes'";
      else
         $condition.=" im_docs= 'yes'";
   }
   if($_GET['jail_signing']!='' || $_GET['hosp_signing']!='' || $_GET['fingerprint']!='' || $_GET['home_insp']!='' || $_GET['bg_checked']!='' || $_GET['error_in']!='' || $_GET['dualtray']!='' || $_GET['laserprinter']!='' || $_GET['esign']!='' || $_GET['edoc']!='' || $_GET['im_docs']!='')
   {
    $condition.=" and status=1 order by distance asc";
    }
    else
    {
    $condition.=$a." status=1 order by distance asc";
    }

   $sql=mysql_query($condition);
   $num=mysql_num_rows($sql);
   $getmile=explode(' ',$_GET['miles']);
   
        $c=0;
        while($getidres=mysql_fetch_array($sql))
      {
          if($getidres['distance']<=$getmile[0])
        {
            if($getidres['membership']=='premium')
            {
            $prefirst[$getidres['id']]=$getidres['distance'];
            $c++;
            }
            else
            {
            $basicfirst[$getidres['id']]=$getidres['distance'];
             $c++;
            }
        }
        }
      
      if(!empty($sql))
     {
      
          if($c!=0)
         {
            if(!empty($prefirst) || !empty($basicfirst))
            { ?>

So I have a basic search form that goes through and does a mySQL like '%$sanitizedInput%' to column in the table.

The problem I have is that if I have two rows, one with a value "green" and the second "blue", and I do a search for "green", it shows the green result, if I do a search for "blue", it shows the blue result, but if i do a search for "green blue" it returns no results.

Any ideas on how to fix this?

I want to have a search product feature, but I would like members to be able to search multiple fields in one go i.e.  product_code, Product_name in one MySQL query.

The thing is, members have to be logged on, so the query must also only show results relating to that specific member, via the session[member_ID], my current query for listing products for that specific member is :

Code: [Select]
$sql = "SELECT productId, productCode, image, name, price, stock_level FROM product_inventory WHERE memberr_ID = '" . $_SESSION['SESS_mem_ID'] . ";
How would I change the above into a search query to search for productcode, productname and still only show results beloging to this member using the session data ? 

all help appreciated

I have a simple (1 table, 14 fields) database. I'm using 9 separate search boxes from my search page :
http://www.cinemaposter.com/database.html
There are currently 162 entries. Hitting "submit" throws them all out, same with valid input into any box, with the exception of "country" - example  : entering "F" gives 20results; "PL" gives 34 results.
Here's my code (to simplify, I only went down to the 4th box ('country' - the last and only one giving correct results).  I'm one sorry newbie.... please help !
Here's the code :
------------------

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"
"http://www.w3.org/TR/1998/REC-html40-19980424/loose.dtd">
<html>
<head>
   <title>Search Results</title>
   <meta name="generator" content="BBEdit 6.5">
   <style type="text/css">
td
{
   color: #000000;
   font-family: arial;
   font-size: 10px;
}
</style>
</head>
<body>
<?php
$link = mysql_connect("xxxx", "xxxx", "xxxx") or die("Could not connect");
///print "Connected successfully<BR>";
mysql_select_db("xxxx") or die("Could not select database");


{
$Author = $_POST['Author'];
$sql = "SELECT * FROM posters WHERE Author LIKE '%$Author%'";
}

{
$Title = $_POST['Title'];
$sql = "SELECT * FROM posters WHERE Title LIKE '%$Title%'";
}

{
$Originaltitle =$_POST['Originalitle'];
$sql="SELECT * FROM posters WHERE Originaltitle LIKE '%$Originaltitle%'";
}

{
$Country =$_POST['Country'];
$sql="SELECT * FROM posters WHERE Country LIKE '%$Country%'";
}


$result = mysql_query ($sql) or die("Query failed : " . mysql_error());
$numrs = mysql_num_rows($result);

if ($numrs > 0){

if ($numrs==1){
   print ("<p><font face=\"Arial\"><b>There is $numrs record in our database.</B> <BR><BR>");
   }elseif($numrs>1){
   print ("<p><font face=\"Arial\"><b>There are $numrs records in our database. </B><BR><BR>");//Apparently, he wan't to be able to search again...</b><form method \"POST\" action=\"$PHP_SELF\">Search Again:");
   }
   
   
   
   
  echo("<a href='javascript:history.go(-1)'>Go Back</a></p>");
  print("<P align=center>Click on the column heading links to sort the results.<br><br />");   
   
print ("<TABLE CELLPADDING=\"1\" CELLSPACING=\"2\" BORDER=\"0\">\n");
PRINT ("<TR BGCOLOR=\"#FFFF77\">
<TD COLSPAN=2><P align=center>Author</td>
<TD><P align=center>Title</TD>
<TD><P align=center>Original Title</TD>
<TD><P align=center>Country</TD>
<TD><P align=center>Director</TD>
<TD><P align=center>Cast</TD>
<TD><P align=center>Year</TD>
<TD><P align=center>Original Year</TD>
<TD><P align=center>Photo</TD>
<TD><P align=center>Producer</TD>
<TD><P align=center>Circulation</TD>
<TD><P align=center>Size</TD>
<TD><P align=center>Print type</TD>
<TD><P align=center>Remarks</TD></TR>");

$z=1;
      while ($line = mysql_fetch_array($result)) {
      
         $num=$z++;
         //Note here that I'm assigning the found objects new names (indicated by lowercase) to that php wont get confused.....
         $auth=$line['Author'];
         $title=$line['Title'];
         $originaltitle=$line['Originaltitle'];
         $country=$line['Country'];
         $director=$line['Director'];
         $cast=$line['Cast'];
         $year=$line['Year'];
         $originalyear=$line['Originalyear'];
         $producer=$line['Producer'];
         $circ=$line['Circulation'];
         $size=$line['Size'];
         $print=$line['Print'];
         $remarks=$line['Remarks'];
         $pict=$line['Picture'];


         //Set the formatting
         $bgcolor = ($i++ & 1) ? '#d3d3d3' : '#c0c0c0';
         
                           
      PRINT("<TR BGCOLOR=$bgcolor>
      <TD>$num.</TD>
      <TD>$auth</td>
      <TD>$title</TD>
      <TD>$originaltitle</TD>
      <TD><P align=center>$country</TD>
      <TD>$director</TD>
      <TD>$cast</TD>
      <TD><P align=center>$year</TD>
      <TD><P align=center>$originalyear</TD><TD>");
// This is the test to find do we have a picture
if ($pict) {
// If there is a picture, display it:
print ("<P align=center><a href='zdjecie.php?zdj= $pict&autor=$auth&opis=$title&orig=$originaltitle&rok=$year' target='_blank'>
<img src=$pict height=\"150\"></a>");
} else {

// We don't have a picture, so we print some text instead:
print ("<P align=center>no picture available");
}

print ("</TD><TD>$producer</TD>
<TD><P align=center>$circ</TD>
<TD>$size</TD>
<TD>$print</TD>
<TD>$remarks</TD></TR>");
            
         }
      PRINT("</TABLE><BR><font face=\"Arial\"><a href=\"javascript:history.go(-1);\">Search Again</a></font>");
   }else {
   print ("<font face=\"Arial\">Sorry, there were no results for your search. <a href=\"javascript:history.go(-1);\">Please try again</a></font>");
   }
//mysql_close($link);
?>                                                                     
</body>
</html>

Looking for help with a small task for my weather station website.
Has already created a MySQL database where values are entered every hour.

Want to see on my website a table with current temperature or other types of measurements for each hour.
The idea is that users select a "Year", "Month" and Which info they want displayed, which is then displayed in an HTML table.
Info that can be chosen from is e.g. temperature, mm rain, air pressure, mm.

A small fee is usually given.

the website:
Se the page

Examples of desired solutions:
http:Sample 1
Sample 2

 

Need some help I have 2 tables in a database and I need to search the first table and use the results from that search, to search another table, can this be done? and if it can how would you recommend that I go about it?

Thanks For Your Help Guys! 

I have now got a search engine to work, but it only show in normal text, could someone help me get into a table, I have a CSS file but cannot get the search page to print in my table.

 

<div id="main">
<table>
    <thead>
        <tr>
            <th>ID</td>
            <th>GAME</td>
            <th>PLATFORM</td>

        </tr>
    <thead>        

<?php
$output = NULL;
if(isset($_POST['submit'])) {
    //Connect to database
    $mysqli = NEW MySQLi ("localhost","root","","***.**");

    $search = $mysqli->real_escape_string($_POST{'search'});

    //Query the database
    $resultSet = $mysqli->query("SELECT * FROM games WHERE GAME LIKE '$search%'");

    if($resultSet->num_rows >0){
            while($rows = $resultSet->fetch_assoc())
            {
               $id = $rows['ID'];
               $gn = $rows['GAME'];
               $pf = $rows['PLATFORM'];
               $od = $rows['Owned'];?>
            }   
               
                <tr>
                    <td><?php $output .= $id; ?></tr>;
               
               <!-- Game: $gn<br />
               Platform: $pf<br />
               Owned: $od<br /><br />";-->
               

<form method="POST">
<input type="TEXT" name="search" />
<input type="SUBMIT" name="submit" value="Search" />
</form>
</table>
</body>
</html>

Also how can i use my connect file instead of having the connect to database in this file?

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=330205.0

I present videos on my site. The videos (their titles, descriptions, etc.) are stored in a MySQL database.

I want visitors to be able to search my database for videos.

My MySQL version is 5.5.40-36.1.

The storage engine of my tables is INNODB.

The two columns I want searched a title (varchar; unique index) description (text; no index) According to my research, the best way to allow users to search my database would be to use FULLTEXT, but my INNODB tables are incompatible. FULLTEXT is available for INNODB in MySQL 5.6.4 and above.

I would rather not use LIKE '%...%', and I would rather not use a third party solution such as Sphinx because my database will never grow particularly large (I doubt it'll ever exceed 6,000–8,000 videos).

What do you guys recommend? Is it okay to use LIKE with a database containing 6,000–8,000 items?

does anyone have a code todo this? a snippet or soemthing ive tried a few diffrent ones ive seen but nothing that works.. it takes whats submited in search, then takes the words, breaks them up and creates a query string with all the diffrent feilds... here is what phpadmin produces when i search

 SELECT *  FROM `breedclads`.`activeamahorse` WHERE (`id_entered` LIKE '%green%' OR `uuid` LIKE '%green%' OR `ama_hr_coat` LIKE '%green%' OR `breedable` LIKE '%green%' OR `ama_hr_sex` LIKE '%green%' OR `ama_hr_age` LIKE '%green%' OR `date_entered` LIKE '%green%' OR `ama_hr_eyes` LIKE '%green%' OR `ama_hr_eyes_type` LIKE '%green%' OR `ama_hr_mane` LIKE '%green%' OR `ama_hr_tail` LIKE '%green%' OR `ama_hr_luster_coat` LIKE '%green%' OR `ama_hr_luster_hair` LIKE '%green%' OR `ama_hr_gleam_coat` LIKE '%green%' OR `ama_hr_gleam_hair` LIKE '%green%' OR `ama_hr_parents` LIKE '%green%' OR `ama_hr_sale_type` LIKE '%green%' OR `ama_hr_sale_price` LIKE '%green%' OR `ama_hr_location` LIKE '%green%' OR `auction_date` LIKE '%green%' OR `picture_type` LIKE '%green%' OR `picture_name` LIKE '%green%' OR `picture_size` LIKE '%green%' OR `picture_content` LIKE '%green%' OR `ama_hr_coat_type` LIKE '%green%' OR `ama_hr_coat_gloom` LIKE '%green%' OR `ama_hr_hair_gloom` LIKE '%green%' OR `ama_hr_slurl` LIKE '%green%' OR `class_link_key` LIKE '%green%')  AND  (`id_entered` LIKE '%albino%' OR `uuid` LIKE '%albino%' OR `ama_hr_coat` LIKE '%albino%' OR `breedable` LIKE '%albino%' OR `ama_hr_sex` LIKE '%albino%' OR `ama_hr_age` LIKE '%albino%' OR `date_entered` LIKE '%albino%' OR `ama_hr_eyes` LIKE '%albino%' OR `ama_hr_eyes_type` LIKE '%albino%' OR `ama_hr_mane` LIKE '%albino%' OR `ama_hr_tail` LIKE '%albino%' OR `ama_hr_luster_coat` LIKE '%albino%' OR `ama_hr_luster_hair` LIKE '%albino%' OR `ama_hr_gleam_coat` LIKE '%albino%' OR `ama_hr_gleam_hair` LIKE '%albino%' OR `ama_hr_parents` LIKE '%albino%' OR `ama_hr_sale_type` LIKE '%albino%' OR `ama_hr_sale_price` LIKE '%albino%' OR `ama_hr_location` LIKE '%albino%' OR `auction_date` LIKE '%albino%' OR `picture_type` LIKE '%albino%' OR `picture_name` LIKE '%albino%' OR `picture_size` LIKE '%albino%' OR `picture_content` LIKE '%albino%' OR `ama_hr_coat_type` LIKE '%albino%' OR `ama_hr_coat_gloom` LIKE '%albino%' OR `ama_hr_hair_gloom` LIKE '%albino%' OR `ama_hr_slurl` LIKE '%albino%' OR `class_link_key` LIKE '%albino%')  AND  (`id_entered` LIKE '%69%' OR `uuid` LIKE '%69%' OR `ama_hr_coat` LIKE '%69%' OR `breedable` LIKE '%69%' OR `ama_hr_sex` LIKE '%69%' OR `ama_hr_age` LIKE '%69%' OR `date_entered` LIKE '%69%' OR `ama_hr_eyes` LIKE '%69%' OR `ama_hr_eyes_type` LIKE '%69%' OR `ama_hr_mane` LIKE '%69%' OR `ama_hr_tail` LIKE '%69%' OR `ama_hr_luster_coat` LIKE '%69%' OR `ama_hr_luster_hair` LIKE '%69%' OR `ama_hr_gleam_coat` LIKE '%69%' OR `ama_hr_gleam_hair` LIKE '%69%' OR `ama_hr_parents` LIKE '%69%' OR `ama_hr_sale_type` LIKE '%69%' OR `ama_hr_sale_price` LIKE '%69%' OR `ama_hr_location` LIKE '%69%' OR `auction_date` LIKE '%69%' OR `picture_type` LIKE '%69%' OR `picture_name` LIKE '%69%' OR `picture_size` LIKE '%69%' OR `picture_content` LIKE '%69%' OR `ama_hr_coat_type` LIKE '%69%' OR `ama_hr_coat_gloom` LIKE '%69%' OR `ama_hr_hair_gloom` LIKE '%69%' OR `ama_hr_slurl` LIKE '%69%' OR `class_link_key` LIKE '%69%')




i need this to be made when people search with mutliple words  as you can see it searchs each feilds for the green then each feild for albino, so on.. but i wont ever know what there searching for

Please help me, thanks