?
Solved

Advanced Search Form PHP MYSQL - Multiple Selections

Posted on 2005-04-26
13
Medium Priority
?
879 Views
Last Modified: 2013-12-12
I've never done a search function and I realise this is a bit of an advanced level of entry but I hope someone can help out.

The Scenario:
---------------
The Database: Listings of individuals with specialist qualifications.
The Search Page: An HTML form dynamically populated from the database.

The Database:
----------------
table resources
|id|name|email|phone|specialities|languages|regions|detail_description|

table region
|id|region|

table language
|id|language|

table speciality
|id|speciality|

The Form:
------------

List of Regions (option box)
List of Languages (option box)
List of Specialist Fields (option box)
Other Search Terms (text input) - this item should full text search $detail_description

The Questions:
-----------------
1 - Once I've created the dynamically populated menus what will the search query look like?
2 - What special measures should be taken to be able to make multiple selections from the drop down menu, and have those   selections included in the search?
3 - What database structure would work best in this scenario

Last Comment:
----------------
Does anyone know of a complete functioning script somewhere that I can adapt?

0
Comment
Question by:MariaCM
  • 6
  • 6
12 Comments
 

Author Comment

by:MariaCM
ID: 13868446
Thanks
0
 
LVL 32

Expert Comment

by:ldbkutty
ID: 13869540
I assume all the "id" fields are PK with AUTO_INCREMENT.

Table: resources
Fields: id | name | email | phone | specialitiy_id | language_id | region_id | detail_description |

where:

specialitiy_id => Foreign Key to "speciality" table.
language_id => Foreign Key to "language" table.
region_id => Foreign Key to "region" table.

// Rest of the tables are fine.

PHP Code:
=======

<?php
$hostname = "HOSTNAME";
$host_user = "USERNAME";
$host_pass = "PASSWORD";
$database_name = "DATABASE_NAME";

// Connect to MySql
$link = mysql_connect($hostname, $host_user, $host_pass) or die('Could not connect: ' . mysql_error());
// Select the database
mysql_select_db($database_name, $link) or die('Could not select db: ' . mysql_error());
?>

<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="POST">

<?php
$result = mysql_query("SELECT id, region FROM region") or die("Sql1 error: " . mysql_error());
echo '<select name="region" multiple="multiple">';
while($row = mysql_fetch_array($result))
{
      $selected = (isset($_POST['region']) && $_POST['region'] == $row['id']) ? 'selected="selected"' : '';
      echo "<option value='{$row['id']}> {$row['region']} </option>";
}
echo "</select>";

$result = mysql_query("SELECT id, language FROM language") or die("Sql2 error: " . mysql_error());
echo '<select name="language" multiple="multiple">';
while($row = mysql_fetch_array($result))
{
      $selected = (isset($_POST['language']) && $_POST['language'] == $row['id']) ? 'selected="selected"' : '';
      echo "<option value='{$row['id']}> {$row['language']} </option>";
}
echo "</select>";

$result = mysql_query("SELECT id, speciality FROM speciality") or die("Sql3 error: " . mysql_error());
echo '<select name="speciality" multiple="multiple">';
while($row = mysql_fetch_array($result))
{
      $selected = (isset($_POST['speciality']) && $_POST['speciality'] == $row['id']) ? 'selected="selected"' : '';
      echo "<option value='{$row['id']}> {$row['speciality']} </option>";
}
echo "</select>";
?>

<input type="text" name="detail_description" size="50" maxlength="255" value=""/>
<input type="submit" name="search_submit" value="Search"/>  
</form>

<?php
// Process if the form wa submitted.
if(isset($_POST['search_submit']) && !empty($_POST['search_submit']))
{
      $where_clause = '';
      if(array_key_exists('region', $_POST) && is_array($_POST['region']))
      {
            $region_ids_arr = array_values($_POST['region']);
            if(!empty($region_ids_arr))
            {
                  $region_ids = implode(',', $region_ids_arr);
                  $where_clause .= (!empty($where_clause)) ? ' AND' : '';
                  $where_clause .= " region_id IN ($region_ids)";
            }
      }      

      if(array_key_exists('language', $_POST) && is_array($_POST['language']))
      {
            $language_ids_arr = array_values($_POST['region']);
            if(!empty($region_ids_arr))
            {
                  $language_ids = implode(',', $language_ids_arr);
                  $where_clause .= (!empty($where_clause)) ? ' AND' : '';
                  $where_clause .= " language_id IN ($language_ids)";
            }
      }      

      if(array_key_exists('speciality', $_POST) && is_array($_POST['speciality']))
      {
            $speciality_ids_arr = array_values($_POST['region']);
            if(!empty($region_ids_arr))
            {
                  $speciality_ids = implode(',', $speciality_ids_arr);
                  $where_clause .= (!empty($where_clause)) ? ' AND' : '';
                  $where_clause .= " speciality_id IN ($speciality_ids)";
            }
      }      
      
      if(isset($_POST['detail_description']) && !empty($_POST['detail_description']))
      {
            $where_clause .= (!empty($where_clause)) ? ' AND' : '';
            $where_clause .= " MATCH(details_description) AGAINST('" . mysql_real_escape_string($_POST['detail_description']) . "')";
      }
      
      $where_clause = (!empty($where_clause)) ? " WHERE $where_clause" : "";
      $query = "SELECT * FROM resources $where_clause";
      
      $result = mysql_query($query) or die("Sql error: " . mysql_error());
      while($row = mysql_fetch_array($result))
      {
            // Display the results here
            print_r($row);
      }
}

?>
0
 

Author Comment

by:MariaCM
ID: 13869807
Thanks for the super fast reponse, i'm exhausted...will test tomorrow.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 32

Expert Comment

by:ldbkutty
ID: 13869834
Ok, but its not a tested code. There might be need to make some changes as well. :=)
0
 

Author Comment

by:MariaCM
ID: 13878014
hmmm it lists only two items in each menu?

any suggestions?
0
 

Author Comment

by:MariaCM
ID: 13878114
also: Can't find FULLTEXT index matching the column list

is there something wrong with my db fields?
0
 
LVL 32

Expert Comment

by:ldbkutty
ID: 13878920
can you post the table structure with some datas so I can test in my localhost ?
0
 
LVL 32

Expert Comment

by:ldbkutty
ID: 13884865
?
0
 

Author Comment

by:MariaCM
ID: 13884960
Here you go:

CREATE TABLE `resources` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(100) NOT NULL default '',
  `email` varchar(100) NOT NULL default '',
  `phone` varchar(100) NOT NULL default '',
  `specialty_id` varchar(100) NOT NULL default '',
  `language_id` varchar(100) NOT NULL default '',
  `region_id` varchar(100) NOT NULL default '',
  `detail_description` longtext NOT NULL,
  PRIMARY KEY  (`id`)
) TYPE=MyISAM AUTO_INCREMENT=4 ;

#
# Dumping data for table `resources`
#

INSERT INTO `resources` VALUES (1, 'John Smith', 'john@smith.com', '08002365896', '1,2', '1,3', '1,2', 'I live and work in New York.');
INSERT INTO `resources` VALUES (3, 'Sarah Bonbon', 'sarah@bonbon.com', '08005326986', '1', '1', '1', 'My name i Sarah, I like cats.');
   

CREATE TABLE `region` (
  `id` int(11) NOT NULL auto_increment,
  `region` varchar(100) NOT NULL default '',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM AUTO_INCREMENT=5 ;

#
# Dumping data for table `region`
#

INSERT INTO `region` VALUES (1, 'africa');
INSERT INTO `region` VALUES (2, 'america');
INSERT INTO `region` VALUES (3, 'spain');
INSERT INTO `region` VALUES (4, 'china');

CREATE TABLE `speciality` (
  `id` int(11) NOT NULL auto_increment,
  `speciality` varchar(100) NOT NULL default '',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM AUTO_INCREMENT=5 ;

#
# Dumping data for table `speciality`
#

INSERT INTO `speciality` VALUES (1, 'doctor');
INSERT INTO `speciality` VALUES (2, 'nurse');
INSERT INTO `speciality` VALUES (3, 'detists');
INSERT INTO `speciality` VALUES (4, 'psychologist');
   

CREATE TABLE `language` (
  `id` int(11) NOT NULL auto_increment,
  `language` varchar(100) NOT NULL default '',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM AUTO_INCREMENT=5 ;

#
# Dumping data for table `language`
#

INSERT INTO `language` VALUES (1, 'afrikaans');
INSERT INTO `language` VALUES (2, 'english');
INSERT INTO `language` VALUES (3, 'french');
INSERT INTO `language` VALUES (4, 'portuguese');
   
0
 
LVL 32

Accepted Solution

by:
ldbkutty earned 2000 total points
ID: 13887097
First rename the DB column so it is spelled correct :=)

ALTER TABLE `resources` CHANGE `specialty_id` `speciality_id` VARCHAR( 100 ) NOT NULL

Here's the tested PHP Code:
==================

<?php
$hostname = "HOSTNAME";
$host_user = "USERNAME";
$host_pass = "PASSWORD";
$database_name = "DATABASE_NAME";

// Connect to MySql
$link = mysql_connect($hostname, $host_user, $host_pass) or die('Could not connect: ' . mysql_error());

// Select the database
mysql_select_db($database_name, $link) or die('Could not select db: ' . mysql_error());

// Process if the form was submitted.
if(isset($_POST['search_submit']) && !empty($_POST['search_submit']))
{
       $form_submitted = true;
     $where_clause = '';
     if(array_key_exists('region', $_POST) && is_array($_POST['region']))
     {
          $region_ids_arr = array_values($_POST['region']);
          if(!empty($region_ids_arr))
          {
               $region_ids = implode(',', $region_ids_arr);
               $where_clause .= (!empty($where_clause)) ? ' AND' : '';
               $where_clause .= " region_id IN ($region_ids)";
          }
     }    

     if(array_key_exists('language', $_POST) && is_array($_POST['language']))
     {
          $language_ids_arr = array_values($_POST['language']);
          if(!empty($region_ids_arr))
          {
               $language_ids = implode(',', $language_ids_arr);
               $where_clause .= (!empty($where_clause)) ? ' AND' : '';
               $where_clause .= " language_id IN ($language_ids)";
          }
     }    

     if(array_key_exists('speciality', $_POST) && is_array($_POST['speciality']))
     {
          $speciality_ids_arr = array_values($_POST['speciality']);
          if(!empty($region_ids_arr))
          {
               $speciality_ids = implode(',', $speciality_ids_arr);
               $where_clause .= (!empty($where_clause)) ? ' AND' : '';
               $where_clause .= " speciality_id IN ($speciality_ids)";
          }
     }    
     
     if(isset($_POST['detail_description']) && !empty($_POST['detail_description']))
     {
          $where_clause .= (!empty($where_clause)) ? ' AND' : '';
          $where_clause .= " detail_description LIKE '%" . mysql_real_escape_string($_POST['detail_description']) . "%'";
     }
     
     $where_clause = (!empty($where_clause)) ? " WHERE $where_clause" : "";
     $search_query = "SELECT * FROM resources $where_clause";
}

?>

<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="POST">

<?php
$result = mysql_query("SELECT id, region FROM region") or die("Sql1 error: " . mysql_error());
echo '<select name="region[]" multiple="multiple">';
while($row = mysql_fetch_array($result))
{
     $selected = (isset($region_ids_arr) && in_array($row['id'], $region_ids_arr)) ? 'selected="selected"' : '';
     echo "<option value='{$row['id']}' $selected> {$row['region']} </option>";
}
echo "</select>";

$result = mysql_query("SELECT id, language FROM language") or die("Sql2 error: " . mysql_error());
echo '<select name="language[]" multiple="multiple">';
while($row = mysql_fetch_array($result))
{
     $selected = (isset($language_ids_arr) && in_array($row['id'], $language_ids_arr)) ? 'selected="selected"' : '';
     echo "<option value='{$row['id']}' $selected> {$row['language']} </option>";
}
echo "</select>";

$result = mysql_query("SELECT id, speciality FROM speciality") or die("Sql3 error: " . mysql_error());
echo '<select name="speciality[]" multiple="multiple">';
while($row = mysql_fetch_array($result))
{
     $selected = (isset($speciality_ids_arr) && in_array($row['id'], $speciality_ids_arr)) ? 'selected="selected"' : '';
     echo "<option value='{$row['id']}' $selected> {$row['speciality']} </option>";
}
echo "</select>";
?>

<input type="text" name="detail_description" size="50" maxlength="255" value="<?php echo isset($_POST['detail_description']) ? htmlentities($_POST['detail_description']) : ''; ?>"/>
<input type="submit" name="search_submit" value="Search"/>  
</form>

<?php
     
if(isset($form_submitted) && !empty($form_submitted))
{
     $result = mysql_query($search_query) or die("Sql error: " . mysql_error());
     if(mysql_num_rows($result) > 0)
     {
            echo mysql_num_rows($result) . ' record(s) found <br><br>';
           while($row = mysql_fetch_array($result))
           {
                // Display the results here
                print_r($row);
           }           
     }
     else
     {
           echo 'No records to display';       
     }
}

?>
0
 

Author Comment

by:MariaCM
ID: 13887236
Thanks this deserves a 10/10!

Now...I'm going to play with my new search toy....

Maritza
0
 
LVL 32

Expert Comment

by:ldbkutty
ID: 13887525
Thanks and all the best for your project. :=)

 - Balakrishnan.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this. Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it i…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses
Course of the Month13 days, 19 hours left to enroll

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question