Using filters in search engine PHP - MYSQL

Hello,

I have a search engine for documents (business studies), and I need to include the filters for sectorRegion. Both of them has a number value in the database.

Each study has at least 1 category and 1 region.

Ex.
study title: Construction in Germany

Sector: construction (71)   // 71 is the code for construction sector in the database
Region: Europe(4) , Western Europe (23), Germany (249) //where  71, 23, 249 are the code for this regions in the database.

- The table that has the info about the sectors is : sector_index_reports

sectors table
- The table that has the info about the regions is: country_index_reports
regions table
In this 2 tables, there is a column called: "id_obiekt", that has the  "id"  of the studies, so this is what makes the relation between the tables.


The titles of the studies are in the table: reports and swots, and are connected to
sector_index_reports and country_index_reports by the "id" number of the studies.

This is how looks the tables "reports" and "swots"
I want to get the ID of the sector and region selected by the user and include in my search to create a more specific query to the database. Hope you can give me a hand with this. Thanks.

HTML (The value I gave to the items in the filters are the same value that the databae use to recognize the sectors and regions)

<html>
   <link rel="stylesheet" type="text/css" href="style.css">
   <title>Test Search Engine</title>
   <body>

      <div id="leftcol">
	<p>My menu</p>
      </div>

      <div id="content">
        <p>Enter your search terms:</p>
        <form method="GET" action="<?php echo $_SERVER['PHP_SELF'];?>" name="searchForm">
          <p> Search For: <input type="text" name="keyword" value="" />
           <input type="submit" name="submit" value="Search!" />
	       <label for="categories">Select category: </label>
	       <select name="categories" id="categories">
	         <option value="0">All sectors</option>
	         <option value="1">Aerospace &amp; Transportation</option>
	         <option value="2">Automotive</option>
	         <option value="3">Banking, Finance &amp; Real Estate</option>
	         <option value="4">Basic Materials</option>
	         <option value="5">Business &amp; Government</option>
	         <option value="7">Construction &amp; Heavy Industry</option>
	         <option value="6">Consumer Goods &amp; Retail</option>
	         <option value="8">Energy &amp; Utilities</option>
	         <option value="9">Food &amp; Drink</option>
	         <option value="10">Information Technology</option>
	         <option value="11">Media &amp; Leisure</option>
	         <option value="12">Pharma &amp; Healthcare</option>
	         <option value="13">Telecomunication</option>
           </select>
           <label for="regions">Select Region:</label>
           <select name="regions" id="regions">
             <option value="0">All Regions</option>
             <option value="1">Africa</option>
             <option value="3">Asia</option>
             <option value="24">Australia and New Zealand</option>
             <option value="12">Central America</option>
             <option value="20"> Central and Eastern Europe</option>
             <option value="15">Central Asia</option>
             <option value="16">East Asia</option>
             <option value="4">Europe</option>
             <option value="19">Middle East</option>
             <option value="8">North Africa</option>
             <option value="13">North America</option>
             <option value="14">South America</option>
             <option value="17">South Asia</option>
             <option value="18">South East Asia</option>
             <option value="23">Western Europe</option>
           </select>
          </p>
          <p>&nbsp;</p>
          <p>
            <input type="radio" name="radio" id="boton1" value="1" checked="checked" />
            <label for="boton1">market reports</label>
            <input type="radio" name="radio" id="boton2" value="2" />
            <label for="boton2">swots</label>
            <input type="radio" name="radio" id="boton3" value="3" />
            <label for="boton3">all content</label>
          </p>
        </form>

Open in new window


PHP

<?php

	$MySQLPassword = "***";	
	$HostName = "***";	
	$UserName = "***";
	$Database = "***";

	mysql_connect($HostName,$UserName,$MySQLPassword)
	or die("ERROR: Could not connect to database!");
 	mysql_select_db($Database) or die("cannot select db");

	$default_sort = 'ID';
	$allowed_order = array ('name','publication_date', 'price');
	
	if (!isset ($_GET['order']) || 
	    !in_array ($_GET['order'], $allowed_order)) {
	    $order = $default_sort;
	} else {
	    $order = $_GET['order'];
	}

// The following line stops the undefined index error on initial page load
	if (isset($_GET['keyword'])) {

	        if(!$_GET['keyword']) {
	          die('<p>Please enter a search term.</p>');
	  	}     

// Database Query Settings //
/* Change these to match your database table, the fields you want displayed and the fields to run the query against. */
	$opcion_busqueda = $_GET['radio'];
	
	if ($opcion_busqueda == 2){
		
		$tables = 'swots';
	}
	
	else {
		$tables = 'reports';
		}
	
	$return_fields = 'id name organizer_id no_pages publication_date price currency';
	
	if  ($opcion_busqueda == 3){
		
		$check_fields = 'table_content description';
	}
	
	else {
	$check_fields = 'name';
	}

// Get the keyword from the search form.
 	$query_text = $_GET['keyword'];
    

// Sanitize Data Input
	$clean_query_text =cleanQuery($query_text);


// Call the bq_simple function and store the //
// resulting SQL Query in $newquery variable //
	$newquery=bq_simple ($return_fields, $tables, $check_fields, $clean_query_text);
	$newquery = $newquery . " ORDER BY $order;";

// sql data query construction //
	$result = mysql_query($newquery) or die(mysql_error());

/* make sure data was retrieved */
	$numrows = mysql_num_rows($result);
	if ($numrows == 0) {
	    echo "<H4>No data to display!</H4>";
	    exit;
	}
	echo 	"<p>Your search: '$query_text' within </p>\n";	
	
	if ($opcion_busqueda == 1){
		echo " market reports titles";}
		else if ($opcion_busqueda == 3) {
		echo " All content";}
	 else {echo " SWOTS";} 	
	 
	 echo "<p>returned ".$numrows. " results.</p>\n";
	echo 	"<p>Click on the headings to sort.</p>\n";


/* now grab the first row and start the table */
	$row = mysql_fetch_assoc ($result);
	echo "<TABLE cellspacing=\"3\" cellpadding=\"8\">\n";
	
	echo "</TR>\n";
	 foreach ($row as $heading=>$column) {
      if ($heading != 'id') { //don't create a column for ID!
         echo "<TD><b>";
         if (in_array ($heading, $allowed_order)) {
            echo "<a href=\"{$_SERVER['PHP_SELF']}?order=$heading&keyword=$query_text\">$heading</a>";
         } else {
            echo $heading;
         }                
         echo "</b></TD>\n";
      }
   }
echo "</TR>\n";

/* reset the $result set back to the first row and 
 * display the data */
	
	mysql_data_seek ($result, 0);
	while ($row = mysql_fetch_assoc ($result)) {
   //get the publisher name
  $organizerSql = mysql_query("SELECT nazwa FROM baza_obiektow_inne WHERE id=" . $row['organizer_id'] . " LIMIT 1");
   $organizer = mysql_fetch_assoc($organizerSql);

   echo "<tr>\n";
   printf("<td><a href='http://embs-group.com/%s,%s'>%s</a></td>", $row['id'], str_replace(" ", "_", $row['name']), $row['name']);
   printf("<td>%s</td>", $organizer['nazwa']);
   printf("<td>%s</td>", $row['no_pages']);
   printf("<td>%s</td>", $row['publication_date']);
   printf("<td>%s</td>", $row['price']);
   printf("<td>%s</td>", $row['currency']);
  
   echo "</tr>\n";

}

}
/* * * * * * * * * * * * * *  F U N C T I O N S * * * * * * * * * * * */

	function cleanQuery($string)
	{
	  $string = trim($string);
	  $string = strip_tags($string); // remove any html/javascript.

	  if(get_magic_quotes_gpc())  // prevents duplicate backslashes
	  {
	    $string = stripslashes($string);
	  }
	  if (phpversion() >= '4.3.0')
	  {
	    $string = mysql_real_escape_string($string);
	  }
	  else
	  {
	    $string = mysql_escape_string($string);
	  }
	  return $string;
	}

function bq_handle_shorthand($text) {
	$text = preg_replace("/ \+/", " and ", $text);
	$text = preg_replace("/ -/", " not ", $text);
	return $text;
}

function bq_explode_respect_quotes($line) {
        $quote_level = 0;	#keep track if we are in or out of quote-space
        $buffer = "";

        for ($a = 0; $a < strlen($line); $a++) {
                if ($line[$a] == "\"") {
                        $quote_level++;
                        if ($quote_level == 2) { $quote_level = 0; }
                }
                else {
                        if ($line[$a] == " " and $quote_level == 0) {
                                $buffer = $buffer . "~~~~";   #Hackish magic key
                        }
                        else {
                                $buffer = $buffer . $line[$a];
                        }
                }

        }

	$buffer = str_replace("\\", "", $buffer);

        $array = explode("~~~~", $buffer);
        return $array;
}

function bq_make_subquery($fields, $word, $mode) {

	if ($mode == "not") {
		$back = " LIKE '%$word%'))";
	}
	else {
		$back = " LIKE '%$word%')";
	}

	if ($mode == "not") {
		$front = "(NOT (";
		$glue = " LIKE '%$word%' AND ";
	}
	else {
		$front = "(";
		$glue = " LIKE '%$word%' AND ";
	}

	$text = str_replace(" ", $glue, $fields);
	$text = $front . $text . $back;

	return $text;
}

function bq_make_query($fields, $text) {
	
	$text = strtolower($text);

	
	$text = bq_handle_shorthand($text);

	#
	# Split, but respect quotation
	#
	$wordarray = bq_explode_respect_quotes($text);

	$buffer = "";
	$output = "";

	
	for ($i = 0; $i<count($wordarray); $i++) {
		$word = $wordarray[$i];

		if ($word == "and" or $word == "not" and $i > 0) {
			if ($word == "not") {
				#
				# $i++ kicks us to the actual keyword that the 'not' is working against, etc
				#
				$i++;
				if ($i == 1) {   #invalid sql syntax to prefix the first check with and/or/not
					$buffer = bq_make_subquery($fields, $wordarray[$i], "not");
				}
				else {
					$buffer = " AND " . bq_make_subquery($fields, $wordarray[$i], "not");
				}
			}
			else {
				if ($word == "and") {
					$i++;
					if ($i == 1) {
						$buffer = bq_make_subquery($fields, $wordarray[$i], "");
					}
					else {

						$buffer = " AND " . bq_make_subquery($fields, $wordarray[$i], "");
					}
				}
				else {
					if ($word == "and") {
						$i++;
						if ($i == 1) {
							$buffer = bq_make_subquery($fields, $wordarray[$i], "");
						}
						else {

							$buffer = " AND " . bq_make_subquery($fields, $wordarray[$i], "");
						}
					}
				}
			}
		}
		else {
			if ($i == 0) {  # 0 instead of 1 here because there was no conditional word to skip and no $i++;
				$buffer = bq_make_subquery($fields, $wordarray[$i], "");
			}
			else {
				$buffer = " AND " . bq_make_subquery($fields, $wordarray[$i], "");
			}
		}
		$output = $output . $buffer;
	}
	return $output;
}


function bq_simple ($return_fields, $tables, $check_fields, $query_text) {

	
	$return_fields = str_replace(" ", ", ", $return_fields);
	$tables = str_replace(" ", ", ", $tables);

	
	$query = "SELECT $return_fields FROM $tables WHERE ";
	$query = $query . bq_make_query($check_fields, $query_text);

	#
	# Uncomment to debug
	#

	return $query;
}
		
?>

Open in new window

Klaus AndersenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
For starters, you will need to get off MySQL.  PHP is removing support for the MySQL extension.  This article will explain why and what you must do about that part of things.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

I will try to envision a query in a little while; I don't think it will be too hard since it appears that the tables are related in a normalized fashion.
0
Klaus AndersenAuthor Commented:
Thanks for the reply Ray. My plan is to make the search engine work in the way I need with the MYSQL syntax, and then to update to Mysqli. I know it will be a longer process, but actually my target is to learn, and for me will be easier if I go through Mysqli having a base of Mysql.

Cheers!
0
Ray PaseurCommented:
This is obviously incomplete, but I don't have any test data nor the time to try to test all of the functions (you might want to learn about unit testing and mock objects).  Here are a few notes to show you where I was trying to go with the information I had.

Lines 7-12: You would want to apply some sanity checks to these values from $_GET and run them through the appropriate real_escape() functions before using them in the query.

Line 12: I did not know how to use this value in the query - what table.column might be searched?

Line 14-21: This is probably wrong.  The filtering process is OK, but I don't have any consolidation of thought about multi-table searches, etc.

Line 23-50: Shows how to use HEREDOC variables substitution to create a query string

Lines 55-59: Shows the inputs we receive from the client request and how these inputs are put into the query string.

Lines 66-end: Create the HTML document using HEREDOC notation.  Check the comments in the HTML for some changes I needed to make.

<?php // RAY_temp_pj_.php
error_reporting(E_ALL);

// RUN THE ACTION SCRIPT IF THE REQUEST HAS BEEN SUBMITTED
if (!empty($_GET))
{
    // SECTOR / REGION INDEX REPORT ELEMENTS
    $id_cat_sector_index_reports  = $_GET['categories'];
    $id_cat_country_index_reports = $_GET['regions'];

    // SEARCH KEYWORD
    $keyword                      = $_GET['keyword'];

    // TABLES TO BE SEARCHED (THIS IS INCOMPLETE - DO NOT UNDERSTAND TABLE ORGANIZATION)
    switch ($_GET['radio'])
    {
        case 1: $tbl = 'reports';     break;
        case 2: $tbl = 'swots';       break;
        case 3: $tbl = 'all_content'; break;
        default: $tbl = 'reports';    break;
    }

    // CONSTRUCT A QUERY STRING IN HEREDOC NOTATION
    $sql = <<<EOD
SELECT
  country_index_reports.id_cat    AS id_cs
, country_index_reports.id_obiekt AS id_co
, sector_index_reports.id_cat     AS id_ss
, sector_index_reports.id_okiekt  AS id_so
, $tbl.id                         AS id_t
, $tbl.name                       AS nm_t
FROM
  country_index_reports
, sector_index_reports
, $tbl
WHERE
  country_index_reports.id_cat = $id_cat_country_index_reports
AND
  sector_index_reports.id_cat  = $id_cat_sector_index_reports
AND
(
  country_index_reports.id_obiekt = $tbl.id
OR
  sector_index_reports.id_obiekt  = $tbl.id
)
GROUP BY
  $tbl.id
ORDER BY
  $tbl.name
EOD;

    // THIS IS WHERE YOU WOULD RUN THE QUERY


    // DEBUGGING INFORMATION TO SHOW THE REQUEST DATA AND THE RESULTING QUERY
    echo '<pre>';
    var_dump($_GET);
    var_dump($sql);
    echo '</pre>';

// END OF ACTION SCRIPT
}



// CREATE THE HTML DOCUMENT USING HEREDOC NOTATION
$htm = <<<EOD
<html>

   <!-- THE DOCUMENT WILL NOT BE STYLED BECAUSE WE DO NOT HAVE THE FULL URL -->
   <link rel="stylesheet" type="text/css" href="style.css">
   <title>Test Search Engine</title>
   <body>

      <div id="leftcol">
	<p>My menu</p>
      </div>

      <div id="content">
        <p>Enter your search terms:</p>

        <!-- OMITTING THE ACTION ATTRIBUTE CAUSES THE REQUEST TO GO TO THE CURRENT URL -->
        <form method="GET" name="searchForm">
          <p> Search For: <input type="text" name="keyword" value="" />
           <input type="submit" name="submit" value="Search!" />
	       <label for="categories">Select category: </label>
	       <select name="categories" id="categories">

	         <!-- ADDED A DEFAULT SELECTION -->
	         <option value="0" selected>All sectors</option>
	         <option value="1">Aerospace &amp; Transportation</option>
	         <option value="2">Automotive</option>
	         <option value="3">Banking, Finance &amp; Real Estate</option>
	         <option value="4">Basic Materials</option>
	         <option value="5">Business &amp; Government</option>
	         <option value="7">Construction &amp; Heavy Industry</option>
	         <option value="6">Consumer Goods &amp; Retail</option>
	         <option value="8">Energy &amp; Utilities</option>
	         <option value="9">Food &amp; Drink</option>
	         <option value="10">Information Technology</option>
	         <option value="11">Media &amp; Leisure</option>
	         <option value="12">Pharma &amp; Healthcare</option>
	         <option value="13">Telecomunication</option>
           </select>
           <label for="regions">Select Region:</label>
           <select name="regions" id="regions">

             <!-- ADDED A DEFAULT SELECTION -->
             <option value="0" selected>All Regions</option>
             <option value="1">Africa</option>
             <option value="3">Asia</option>
             <option value="24">Australia and New Zealand</option>
             <option value="12">Central America</option>
             <option value="20"> Central and Eastern Europe</option>
             <option value="15">Central Asia</option>
             <option value="16">East Asia</option>
             <option value="4">Europe</option>
             <option value="19">Middle East</option>
             <option value="8">North Africa</option>
             <option value="13">North America</option>
             <option value="14">South America</option>
             <option value="17">South Asia</option>
             <option value="18">South East Asia</option>
             <option value="23">Western Europe</option>
           </select>
          </p>
          <p>&nbsp;</p>
          <p>
            <input type="radio" name="radio" id="boton1" value="1" checked="checked" />
            <label for="boton1">market reports</label>
            <input type="radio" name="radio" id="boton2" value="2" />
            <label for="boton2">swots</label>
            <input type="radio" name="radio" id="boton3" value="3" />
            <label for="boton3">all content</label>
          </p>

          <!-- ADDED A SUBMIT CONTROL -->
          <p>
            <input type="submit" />
          </p>

        </form>
EOD;
echo $htm;

Open in new window

HTH, ~Ray
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Klaus AndersenAuthor Commented:
Thanks a lot Ray. To be honest with you, I´m a little lost in your code, because I´m new in PHP/Mysql and there are methods you use I don´t know yet.  

Anyway, I decided to work parallel in my previous option and in yours, and take  the one will give me best results.

I will make you a couple of comments under the ones you wrote. Mine will be in italic.

Lines 7-12: You would want to apply some sanity checks to these values from $_GET and run them through the appropriate real_escape() functions before using them in the query.

I agree. should it be in that way?

$id_cat_sector_index_reports  = mysqli_real_escape_string($_GET['categories']);
$id_cat_country_index_reports =mysqli_real_escape_string( $_GET['regions']);

    // SEARCH KEYWORD
    $keyword                      = mysqli_real_escape_string($_GET['keyword']);

Open in new window



Line 12: I did not know how to use this value in the query - what table.column might be searched?

That´s the word -or words- that the user is searching in the database.
The table.column might be searched depends of the first filter (radio)
So,
reports titles (radio value 1) = Should search in reports.name // this should be the default search
swots (radio value 2) = Should search in swots.name
all content (radio value 3) = Should fin the $keyword in name.reports OR name.swots OR description.reports OR table_content.reports


In code will be something like this:

$radio = ($_GET['radio']); // I get the value from the radio options (market reports, swots or all content)

if ($radio =='1') 


{ $table = 'reports.name'}

else if  ($radio =='2')

{ $table = 'swots.name'}

else 

{ $table = 'name.reports name.swots description.reports table_content.reports'}

Open in new window


Line 14-21: This is probably wrong.  The filtering process is OK, but I don't have any consolidation of thought about multi-table searches, etc.

Previous answer may help with this information?

Line 23-50: Shows how to use HEREDOC variables substitution to create a query string


unfortunately I don´t know the variable HEREDOC

Lines 55-59: Shows the inputs we receive from the client request and how these inputs are put into the query string.

Understood

Thanks for review!
0
Ray PaseurCommented:
Lines 7-12: Yes, that would be a minimum before you use the values in a query.  You might also check to see if the values were in the allowable range, etc.

Line 23-50: HEREDOC notation is a kind of string notation.  Don't be put off by the warning message in the online man page; HEREDOC is a very useful way to do templating with variable substitution.  Description here:
http://www.php.net/manual/en/language.types.string.php#language.types.string.syntax.heredoc

If I get a spare moment, I'll try to incorporate the table selections into the design.

A good place to start getting a foundation in PHP and MYSQL is here:
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_11769-And-by-the-way-I-am-new-to-PHP.html
0
Ray PaseurCommented:
This is probably a little closer, at least worth testing the query to see what it gets back.  The ability to make the query search multiple tables may be complicated a bit by having the same column names in different tables, and needing to search multiple tables with identical column names.  It may not be as simple as what I'm suggesting with the example code.  You might find a factory method would be useful to build the query.

<?php // RAY_temp_pj_.php
error_reporting(E_ALL);

// RUN THE ACTION SCRIPT IF THE REQUEST HAS BEEN SUBMITTED
if (!empty($_GET))
{
    // SECTOR / REGION INDEX REPORT ELEMENTS
    $id_cat_sector_index_reports  = $_GET['categories'];
    $id_cat_country_index_reports = $_GET['regions'];

    // SEARCH KEYWORD
    $keyword                      = $_GET['keyword'];

    // TABLES TO BE SEARCHED (THIS IS INCOMPLETE - DO NOT UNDERSTAND TABLE ORGANIZATION)
    switch ($_GET['radio'])
    {
        case 1: $tbl = 'reports';     break;
        case 2: $tbl = 'swots';       break;
        case 3: $tbl = 'all_content'; break;
        default: $tbl = 'reports';    break;
    }

    // CONSTRUCT A QUERY STRING IN HEREDOC NOTATION
    $sql = <<<EOD
SELECT
  country_index_reports.id_cat    AS id_cs
, country_index_reports.id_obiekt AS id_co
, sector_index_reports.id_cat     AS id_ss
, sector_index_reports.id_okiekt  AS id_so
, $tbl.id                         AS id_t
, name                            AS nm_t
FROM
  country_index_reports
, sector_index_reports
, $tbl
WHERE
  country_index_reports.id_cat = $id_cat_country_index_reports
AND
  sector_index_reports.id_cat  = $id_cat_sector_index_reports
AND
(
  country_index_reports.id_obiekt = $tbl.id
OR
  sector_index_reports.id_obiekt  = $tbl.id
)
AND name LIKE '%$keyword%'
GROUP BY
  $tbl.id
ORDER BY
  name
EOD;

    // THIS IS WHERE YOU WOULD RUN THE QUERY


    // DEBUGGING INFORMATION TO SHOW THE REQUEST DATA AND THE RESULTING QUERY
    echo '<pre>';
    var_dump($_GET);
    var_dump($sql);
    echo '</pre>';

// END OF ACTION SCRIPT
}



// CREATE THE HTML DOCUMENT USING HEREDOC NOTATION
$htm = <<<EOD
<html>

   <!-- THE DOCUMENT WILL NOT BE STYLED BECAUSE WE DO NOT HAVE THE FULL URL -->
   <link rel="stylesheet" type="text/css" href="style.css">
   <title>Test Search Engine</title>
   <body>

      <div id="leftcol">
	<p>My menu</p>
      </div>

      <div id="content">
        <p>Enter your search terms:</p>

        <!-- OMITTING THE ACTION ATTRIBUTE CAUSES THE REQUEST TO GO TO THE CURRENT URL -->
        <form method="GET" name="searchForm">
          <p> Search For: <input type="text" name="keyword" value="" />
           <input type="submit" name="submit" value="Search!" />
	       <label for="categories">Select category: </label>
	       <select name="categories" id="categories">

	         <!-- ADDED A DEFAULT SELECTION -->
	         <option value="0" selected>All sectors</option>
	         <option value="1">Aerospace &amp; Transportation</option>
	         <option value="2">Automotive</option>
	         <option value="3">Banking, Finance &amp; Real Estate</option>
	         <option value="4">Basic Materials</option>
	         <option value="5">Business &amp; Government</option>
	         <option value="7">Construction &amp; Heavy Industry</option>
	         <option value="6">Consumer Goods &amp; Retail</option>
	         <option value="8">Energy &amp; Utilities</option>
	         <option value="9">Food &amp; Drink</option>
	         <option value="10">Information Technology</option>
	         <option value="11">Media &amp; Leisure</option>
	         <option value="12">Pharma &amp; Healthcare</option>
	         <option value="13">Telecomunication</option>
           </select>
           <label for="regions">Select Region:</label>
           <select name="regions" id="regions">

             <!-- ADDED A DEFAULT SELECTION -->
             <option value="0" selected>All Regions</option>
             <option value="1">Africa</option>
             <option value="3">Asia</option>
             <option value="24">Australia and New Zealand</option>
             <option value="12">Central America</option>
             <option value="20"> Central and Eastern Europe</option>
             <option value="15">Central Asia</option>
             <option value="16">East Asia</option>
             <option value="4">Europe</option>
             <option value="19">Middle East</option>
             <option value="8">North Africa</option>
             <option value="13">North America</option>
             <option value="14">South America</option>
             <option value="17">South Asia</option>
             <option value="18">South East Asia</option>
             <option value="23">Western Europe</option>
           </select>
          </p>
          <p>&nbsp;</p>
          <p>
            <input type="radio" name="radio" id="boton1" value="1" checked="checked" />
            <label for="boton1">market reports</label>
            <input type="radio" name="radio" id="boton2" value="2" />
            <label for="boton2">swots</label>
            <input type="radio" name="radio" id="boton3" value="3" />
            <label for="boton3">all content</label>
          </p>

          <!-- ADDED A SUBMIT CONTROL -->
          <p>
            <input type="submit" />
          </p>

        </form>
EOD;
echo $htm;

Open in new window

Best regards, ~Ray
0
Klaus AndersenAuthor Commented:
Great Ray, thanks for that. I´m gonna have a read just now about the factory method and see how I can apply it to the query. Then I will let you know the results.

Cheers!

Pj
0
Klaus AndersenAuthor Commented:
I´m also continuing with my previous system, and taking advance of our contact I would like to ask you if can I construct the query like this?

(This is for the 3th case, searching within all content, and using the filter for category and region.)

$query2 = "SELECT name, table_content, description FROM reports WHERE name LIKE %$query_text% OR 
table_content LIKE %$query_text% OR description  LIKE %$query_text% AND id_obiekt.sector_index_reports LIKE $catergories   AND id_obiekt.country_index_reports LIKE  $regions" ;

Open in new window


$catergories is the input that gives the user for sectors and $regions for the same.
0
Ray PaseurCommented:
I don't think that's quite the right way to go.  Couple of issues...

In the SQL language, your cascade of information works like this:

databaseName.tableName.columnName

That means id_obiekt.country_index_reports is backwards and should be country_index_reports.id_obiekt

The other issue goes to the performance of the query.  The more LIKE clauses you have the less likely the query will complete in a reasonable amount of time.  LIKE, especially with % at the front and back of the arguments, requires the SQL engine to do a huge amount of repeated character-by-character matching.

The FROM clause needs to name all of the tables you want to SELECT from.

I think this book would be a good investment of your money and time.  It will teach these things and many more in a structured way, and it will be much more effective than trying to learn by trial and error, or by copying any of the thousands of incompetent PHP scripts that litter the internet today.
http://www.amazon.com/PHP-MySQL-Web-Development-Edition/dp/0321833899
0
Klaus AndersenAuthor Commented:
Ray, I was taking a look to the factory method, and at the moment seems not a valid option for my skills in PHP/Mysql.

Is there someone that can help me with this question using a standard query?

Thanks
0
Klaus AndersenAuthor Commented:
UPDATED CODE

The query I have on mind to get the results with sector and region will be like this:

SELECT name,organizer_id,no_pages,publication_date,price,currency

FROM reports AS T1, sector_index_reports AS T2, country_index_reports AS T3

WHERE (id_cat=$category AND T1.id=T2.id_obiekt) AND  (id_cat=$regions AND T1.id=T3.id_obiekt) AND name LIKE'%$query_text%'

Open in new window


HTML

<html>
   <link rel="stylesheet" type="text/css" href="style.css">
   <title> Search Engine</title>
   <body>

      <div id="leftcol">
	<p>My menu</p>
      </div>

      <div id="content">
        <p>Enter your search terms:</p>
        <form method="GET" action="<?php echo $_SERVER['PHP_SELF'];?>" name="searchForm">
          <p> Search For: <input type="text" name="keyword" value="" />
           <input type="submit" name="submit" value="Search!" />
	       <label for="categories">Select category: </label>
	       <select name="categories" id="categories">
	         <option value="0">All sectors</option>
	         <option value="1">Aerospace &amp; Transportation</option>
	         <option value="2">Automotive</option>
	         <option value="3">Banking, Finance &amp; Real Estate</option>
	         <option value="4">Basic Materials</option>
	         <option value="5">Business &amp; Government</option>
	         <option value="6">Construction &amp; Heavy Industry</option>
	         <option value="7">Consumer Goods &amp; Retail</option>
	         <option value="8">Energy &amp; Utilities</option>
	         <option value="9">Food &amp; Drink</option>
	         <option value="10">Information Technology</option>
	         <option value="11">Media &amp; Leisure</option>
	         <option value="12">Pharma &amp; Healthcare</option>
	         <option value="13">Telecomunication</option>
           </select>
           <label for="regions">Select Region:</label>
           <select name="regions" id="regions">
             <option value="0">All Regions</option>
             <option value="1">Africa</option>
             <option value="2">Asia</option>
             <option value="3">Australia and New Zealand</option>
             <option value="4">Central America</option>
             <option value="5"> Central and Eastern Europe</option>
             <option value="6">Central Asia</option>
             <option value="7">East Asia</option>
             <option value="8">Europe</option>
             <option value="9">Middle East</option>
             <option value="10">North Africa</option>
             <option value="11">North America</option>
             <option value="12">South America</option>
             <option value="13">South Asia</option>
             <option value="14">South East Asia</option>
             <option value="15">Western Europe</option>
           </select>
          </p>
          <p>&nbsp;</p>
          <p>
            <input type="radio" name="radio" id="boton1" value="1" checked="checked" />
            <label for="boton1">market reports</label>
            <input type="radio" name="radio" id="boton2" value="2" />
            <label for="boton2">swots</label>
            <input type="radio" name="radio" id="boton3" value="3" />
            <label for="boton3">all content</label>
          </p>
        </form>

Open in new window



PHP


<?php


// DB Connection //
/* Change the following 3 values to reflect your MySQL Server */
  	$MySQLPassword = "e220b3a47e1";	
	$HostName = "127.0.0.1:23306";	
	$UserName = "embs_user";
	$Database = "embsgrou1backup";
	
    mysql_connect($HostName,$UserName,$MySQLPassword)
    or die("ERROR: Could not connect to database!");
     mysql_select_db($Database) or die("cannot select db");

    $default_sort = 'ID';
    $allowed_order = array ('name','publication_date', 'price');
    
    if (!isset ($_GET['order']) || 
        !in_array ($_GET['order'], $allowed_order)) {
        $order = $default_sort;
    } else {
        $order = $_GET['order'];
    }

// The following line stops the undefined index error on initial page load
    if (isset($_GET['keyword'])) {

            if(!$_GET['keyword']) {
              die('<p>Please enter a search term.</p>');
          }     

    $opcion_busqueda = $_GET['radio'];
	$category = $_GET['categories'];
	$region= $_GET['regions'];
    
    switch ($opcion_busqueda) {
        case 1 : {
            $tables = 'reports';
            $check_fields = 'name';
            break;
        }
        case 2 : {
            $tables = 'swots';
            $check_fields = 'name';
            break;
        }
        case 3 : {
            $tables = 'reports'; // SWOTS table can be added and will increase the results from all content, but the search will be slower
            $check_fields = 'name table_content description';
            break;
        }
    }
    $return_fields = 'id name organizer_id no_pages publication_date price currency';
    
    /*if ($opcion_busqueda == 2){
        
        $tables = 'swots';
    }
    
    else {
        $tables = 'reports';
        }
    
    if  ($opcion_busqueda == 3){
        
        $check_fields = 'table_content description';
    }
    
    else {
    $check_fields = 'name';
    }*/

// Get the keyword from the search form.
     $query_text = $_GET['keyword'];
    

// Sanitize Data Input
    $clean_query_text =cleanQuery($query_text);


    $newquery = bq_simple($return_fields, $tables, $check_fields, $clean_query_text);
    $newquery = $newquery . " ORDER BY $order;";

   // echo $newquery;

    $result = mysql_query($newquery) or die(mysql_error());

/* make sure data was retrieved */
    $numrows = mysql_num_rows($result);
    if ($numrows == 0) {
        echo "<H4>No data to display!</H4>";
        exit;
    }
    echo     "<p>Your search: '$query_text' within </p>\n";    
    
    if ($opcion_busqueda == 1){
        echo " market reports titles";}
        else if ($opcion_busqueda == 3) {
        echo " All content";}
     else {echo " SWOTS";}     
     
     echo "<p>returned ".$numrows. " results.</p>\n";
    echo     "<p>Click on the headings to sort.</p>\n";


/* now grab the first row and start the table */
$bSourceFieldVisible = 0;

    $row = mysql_fetch_assoc ($result);
    echo "<TABLE cellspacing=\"3\" cellpadding=\"8\">\n";
    
    echo "</TR>\n";
     foreach ($row as $heading=>$column) {
      if ($heading != 'id') { //don't create a column for ID!
         echo "<TD><b>";
         
         
         if($heading=="name"){$new_heading="Market Report Title";}
    elseif($heading=="organizer_id"){$new_heading="Publisher";}
    elseif($heading=="no_pages"){$new_heading="Number of Pages";}
    elseif($heading=="publication_date"){$new_heading="Date Published";}
    elseif($heading=="price"){$new_heading="Price";}
    elseif($heading=="currency"){$new_heading="";}
    elseif($heading=="source"){$new_heading="Source";$bSourceFieldVisible = 1;}
    
         if (in_array ($heading, $allowed_order)) {
           echo "<a href=\"{$_SERVER['PHP_SELF']}?order=$heading&keyword=$query_text\">$new_heading</a>"; // Use new var here
         } else {
            echo $new_heading;
         }                
         echo "</b></TD>\n";
      }
   }
echo "</TR>\n";

/* reset the $result set back to the first row and 
 * display the data */
   
    mysql_data_seek ($result, 0);
    while ($row = mysql_fetch_assoc ($result)) {
   //get the publisher name
  $organizerSql = mysql_query("SELECT nazwa FROM baza_obiektow_inne WHERE id=" . $row['organizer_id'] . " LIMIT 1");
   $organizer = mysql_fetch_assoc($organizerSql);
   $pretty_date = date('F Y', strtotime($row['publication_date']));
   echo "<tr>\n";
   $simbolos_reemplazar = array (" ","%","/");
   printf("<td><a href='http://embs-group.com/%s,%s'>%s</a></td>", $row['id'], str_replace( $simbolos_reemplazar, "_", $row['name']), $row['name']);
   printf("<td>%s</td>", $organizer['nazwa']);
   printf("<td>%s</td>", $row['no_pages']);
   printf("<td>%s</td>", $pretty_date);
   printf("<td>%s</td>", $row['price']);
   printf("<td>%s</td>", $row['currency']);
   if ($bSourceFieldVisible == 1) {
     printf("<td>%s</td>", $row['source']);
   }
  
   echo "</tr>\n";
    
}    

}
/* * * * * * * * * * * * * *  F U N C T I O N S * * * * * * * * * * * */

    function cleanQuery($string)
    {
      $string = trim($string);
      $string = strip_tags($string); // remove any html/javascript.

      if(get_magic_quotes_gpc())  // prevents duplicate backslashes
      {
        $string = stripslashes($string);
      }
      if (phpversion() >= '4.3.0')
      {
        $string = mysql_real_escape_string($string);
      }
      else
      {
        $string = mysql_escape_string($string);
      }
      return $string;
    }


function bq_handle_shorthand($text) {
    $text = preg_replace("/ \+/", " and ", $text);
    $text = preg_replace("/ -/", " not ", $text);
    return $text;
}



function bq_explode_respect_quotes($line) {
        $quote_level = 0;    #keep track if we are in or out of quote-space
        $buffer = "";

        for ($a = 0; $a < strlen($line); $a++) {
                if ($line[$a] == "\"") {
                        $quote_level++;
                        if ($quote_level == 2) { $quote_level = 0; }
                }
                else {
                        if ($line[$a] == " " and $quote_level == 0) {
                                $buffer = $buffer . "~~~~";   #Hackish magic key
                        }
                        else {
                                $buffer = $buffer . $line[$a];
                        }
                }

        }

    $buffer = str_replace("\\", "", $buffer);

        $array = explode("~~~~", $buffer);
        return $array;
}


function bq_make_subquery($fields, $word, $mode) {

    if ($mode == "not") {
        $back = " LIKE '%$word%'))";
    }
    else {
        $back = " LIKE '%$word%')";
    }



    if ($mode == "not") {
        $front = "(NOT (";
        //$glue = " LIKE '%$word%' AND ";
        $glue = " LIKE '%$word%' OR ";
    }
    else {
        $front = "(";
        //$glue = " LIKE '%$word%' AND ";
        $glue = " LIKE '%$word%' OR ";
    }
    

    $text = str_replace(" ", $glue, $fields);
    $text = $front . $text . $back;

    return $text;
}



function bq_make_query($fields, $text) {
    
    $text = strtolower($text);

    
    $text = bq_handle_shorthand($text);

    #
    # Split, but respect quotation
    #
    $wordarray = bq_explode_respect_quotes($text);

    $buffer = "";
    $output = "";

    
    for ($i = 0; $i<count($wordarray); $i++) {
        $word = $wordarray[$i];

        if ($word == "and" or $word == "not" and $i > 0) {
            if ($word == "not") {
                #
                # $i++ kicks us to the actual keyword that the 'not' is working against, etc
                #
                $i++;
                if ($i == 1) {   #invalid sql syntax to prefix the first check with and/or/not
                    $buffer = bq_make_subquery($fields, $wordarray[$i], "not");
                }
                else {
                    $buffer = " AND " . bq_make_subquery($fields, $wordarray[$i], "not");
                }
            }
            else {
                if ($word == "and") {
                    $i++;
                    if ($i == 1) {
                        $buffer = bq_make_subquery($fields, $wordarray[$i], "");
                    }
                    else {

                        $buffer = " AND " . bq_make_subquery($fields, $wordarray[$i], "");
                    }
                }
                else {
                    if ($word == "and") {
                        $i++;
                        if ($i == 1) {
                            $buffer = bq_make_subquery($fields, $wordarray[$i], "");
                        }
                        else {

                            $buffer = " AND " . bq_make_subquery($fields, $wordarray[$i], "");
                        }
                    }
                }
            }
        }
        else {
            if ($i == 0) {  # 0 instead of 1 here because there was no conditional word to skip and no $i++;
                $buffer = bq_make_subquery($fields, $wordarray[$i], "");
            }
            else {
                $buffer = " AND " . bq_make_subquery($fields, $wordarray[$i], "");
            }
        }
        $output = $output . $buffer;
    }
    return $output;
}


#

function bq_simple ($return_fields, $tables, $check_fields, $query_text) {

    #
    # Convert from space deliniated to comma deliniated for the query
    #
    $return_fields = str_replace(" ", ", ", $return_fields);
    //$tables = str_replace(" ", ", ", $tables);
    $table_list = explode(',', $tables);

    $query = '';
    for ($i = 0; $i < sizeof($table_list); $i++) {
        #
        # build the query itself
        //$query = "SELECT $return_fields FROM $tables WHERE ";
        $indicator_field = sizeof($table_list) > 1 ? ", '$table_list[$i]' as source" : '';
        $query = $query . ($query==''? '' : ' UNION ALL ') . "SELECT $return_fields $indicator_field FROM $table_list[$i] WHERE ";
        $query = $query . bq_make_query($check_fields, $query_text);
    }

    #
    # Uncomment to debug
    #

    return $query;
}
 

        
?>

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.