Using filters in search engine PHP - MYSQL

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.
https://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.

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!

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']);

Select allOpen 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'}

Select allOpen 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!

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:
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_11769-And-by-the-way-I-am-new-to-PHP.html

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;

Select allOpen in new window

Best regards, ~Ray

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

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" ;

Select allOpen in new window

$catergories is the input that gives the user for sectors and $regions for the same.

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

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

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%'

Select allOpen 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>

Select allOpen 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;
}
 

        
?>

Select allOpen in new window