PHP MYSQL Search Script With multiple tables and multiple keywords

[harshm027]

Hi,

I am designing a system wherein a student can search projects based on technology, project type, location etc etc.
The search form goes like this :-

Project Type - HTML Select Option - Example:- Automobile, Manufacturing, Marketing, Communication
Technology - Text Input Type - Example:- HTML, CSS, Java
Starting Year - HTML Select Option - Example:- 2002, 2003, 2010
Financing_type - HTML Select Option - Example:- Self Financing, Government Grants
Location - Text Input Type - Example:- New York, Tokyo, Bangalore

Technology and Location search fields are text fields and the user can give multiple technologies and multiple location seperated by comma.

The database has three tables:-

1. Project
2. Technology
3. Financing

Project Table has the following columns:-
project_id,
project_name,
starting_year,
location

Technology Table
project_id
technologies

Financing
project_id
financing_type
financing_amount

I am not sure how to make the search script as there are multiple tables involved + technologies and location can be more than one (i.e seperated by comma) - so the search becomes a bit more complex.

[broj1]

The following is a simple example of how to build a SELECT query based on the values typed in a search form. Please note security stuff has ben omitted for clarity. Do not forget to apply security checks. See comments in the code.

// check if the form was submitted
if(isset($_POST['Submit'])) {

    // Please note: validating, sanitizing and escaping of input was
    // omitted in this example for clarity; please do not forget
    // to make the production version of the script secure

    // start the select query that will read from all three tables
    // the types of JOIN are just an example, use the join types
    // that suit your requrements
    $qry  = 'SELECT * FROM project_table AS prj ';
    $qry .= 'JOIN technology_table AS thn ON prj.project_id=thn.project_id ';
    $qry .= 'JOIN financing_table AS fin ON prj.project_id=fin.project_id ';

    // conditional operator in a SELECT statement
    // (it will be WHERE for first condition and will change to AND
    // for every next condition)
    $operator = 'WHERE';

    // check if project type was submited to be searched for
    if(isset($_POST['project_type']) and $_POST['project_type'] != '') {

        // add the search string to the SQL statement
        $qry .= "$operator project_type=$_POST['project_type'] ";

        // change the operator from WHERE to AND
        $operator = 'AND';
    }

    // check if technologies were submited to be searched for
    if(isset($_POST['technologies']) and $_POST['technologies'] != '') {

        // remove spaces that might follow commas
        $tecnologies = str_replace(', ', ',', $_POST['technologies']);

        // explode the string to an array (where commas are)
        // and add the elements of the array to the SQL statement
        foreach($tecnologies as $technology) {

            $qry .= "$operator technology=$technology ";

            // change the operator from WHERE to AND
            $operator = 'AND';
        }
    }

    // check if starting year was submited to be searched for
    if(isset($_POST['starting_year']) and $_POST['starting_year'] != '') {

        // add the string to the conditions array
         $qry .= "$operator starting_year=$_POST['starting_year'] ";

        // change the operator from WHERE to AND
        $operator = 'AND';
    }

    // check if locations were submited to be searched for
    if(isset($_POST['locations']) and $_POST['locations'] != '') {

        // remove spaces that might follow commas
        $locations = str_replace(', ', ',', $_POST['locations']);

        // explode the string to an array (where commas are)
        // and add the elements of the array to the SQL statement
        foreach($locations as $location) {

            $qry .= "$operator location=$location ";

            // change the operator from WHERE to AND
            $operator = 'AND';
        }
    }

    // run the query and display the search results
    // ...    

    } 

// if the form was not submited just display the page with the form
// ...

You can also use LIKE operator and % instead of = if you like to enhance the search functionality. For starting year you can also use >=.

[harshm027]

Hi,

Thanks for your reply.

I encountered an error while trying this and am not able to figure why it is coming :-

Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING

The line is 24

[harshm027]

Hi,

I figured it out

I wrote this $qry .= "$operator project_type=$_POST['project_type'] ";

as $qry .= "$operator project_type= '$_POST[project_type] '";

[harshm027]

Hi,

Could you just write a sample query to run

[harshm027]

Hi
Now I am able to display the results.
But one problem still persists i.e results are being displayed twice. How do I solve it?

[broj1]

First I have to correct myself, sorry. The $_POST array elements should be enclosed in curly brackets whenever they are in a double quoted string. So this is the correct code (changes only in lines 24 and 51):

// check if the form was submitted
if(isset($_POST['Submit'])) {

    // Please note: validating, sanitizing and escaping of input was
    // omitted in this example for clarity; please do not forget
    // to make the production version of the script secure

    // start the select query that will read from all three tables
    // the types of JOIN are just an example, use the join types
    // that suit your requrements
    $qry  = 'SELECT * FROM project_table AS prj ';
    $qry .= 'JOIN technology_table AS thn ON prj.project_id=thn.project_id ';
    $qry .= 'JOIN financing_table AS fin ON prj.project_id=fin.project_id ';

    // conditional operator in a SELECT statement
    // (it will be WHERE for first condition and will change to AND
    // for every next condition)
    $operator = 'WHERE';

    // check if project type was submited to be searched for
    if(isset($_POST['project_type']) and $_POST['project_type'] != '') {

        // add the search string to the SQL statement
        $qry .= "$operator project_type={$_POST['project_type']} ";

        // change the operator from WHERE to AND
        $operator = 'AND';
    }

    // check if technologies were submited to be searched for
    if(isset($_POST['technologies']) and $_POST['technologies'] != '') {

        // remove spaces that might follow commas
        $tecnologies = str_replace(', ', ',', $_POST['technologies']);

        // explode the string to an array (where commas are)
        // and add the elements of the array to the SQL statement
        foreach($tecnologies as $technology) {

            $qry .= "$operator technology=$technology ";

            // change the operator from WHERE to AND
            $operator = 'AND';
        }
    }

    // check if starting year was submited to be searched for
    if(isset($_POST['starting_year']) and $_POST['starting_year'] != '') {

        // add the string to the conditions array
         $qry .= "$operator starting_year={$_POST['starting_year']} ";

        // change the operator from WHERE to AND
        $operator = 'AND';
    }

    // check if locations were submited to be searched for
    if(isset($_POST['locations']) and $_POST['locations'] != '') {

        // remove spaces that might follow commas
        $locations = str_replace(', ', ',', $_POST['locations']);

        // explode the string to an array (where commas are)
        // and add the elements of the array to the SQL statement
        foreach($locations as $location) {

            $qry .= "$operator location=$location ";

            // change the operator from WHERE to AND
            $operator = 'AND';
        }
    }

    // run the query and display the search results
    // ...    

    } 

// if the form was not submited just display the page with the form
// ...

[broj1]

The problem I guess is probably in the query which is not exactly correct (the join types maybe) and returns duplicate rows. You can try the following query without WHERE conditions:

SELECT * FROM project_table AS prj  
JOIN technology_table AS thn ON prj.project_id=thn.project_id 
JOIN financing_table AS fin ON prj.project_id=fin.project_id

and if returns duplicate rows the query has to be tuned (maybe using GROUP BY or DISTINCT).

[broj1]

Just spotted another quirk: you want to search by a project type but project type is not defined in your porject table. I included it in my sample code above but since it is not in the table it should either go out of the code or define it as a field in the project table.

[broj1]

I found another error in my code, sorry. All text fields in the query have to be quoted (oh, silly me). So put the quotes arround them like this:

// check if project type was submited to be searched for
if(isset($_POST['project_type']) and $_POST['project_type'] != '') {

    // add the search string to the SQL statement
    $qry .= "$operator project_type='{$_POST['project_type']}' ";

    // change the operator from WHERE to AND
    $operator = 'AND';
}

and so on (on lines 24, 40 and 67 of my first post).

[broj1]

OK, looks like I like to talk to myself :-). Well I had too much time and set a database, tables and put some records in. I also had few little errors in my posted code. The following is now tested code that works just fine, no duplicate rows.

<?php

// initialize the variable that will show the records found
$result = '';

// check if the form was submitted
if(isset($_POST['submit'])) {

    // Please note: validating, sanitizing and escaping of input was
    // omitted in this example for clarity; please do not forget
    // to make the production version of the script secure

    // start the select query that will read from all three tables
    // the types of JOIN are just an example, use the join types
    // that suit your requrements
    $qry  = 'SELECT * FROM project AS prj ';
    $qry .= 'JOIN technology AS thn ON prj.project_id=thn.project_id ';
    $qry .= 'JOIN financing AS fin ON prj.project_id=fin.project_id ';

    // conditional operator in a SELECT statement
    // (it will be WHERE for first condition and will change to AND
    // for every next condition)
    $operator = 'WHERE';

    // check if project type was submited to be searched for
    if(isset($_POST['project_type']) and $_POST['project_type'] != '0') {

        // add the search string to the SQL statement
        $qry .= "$operator project_type='{$_POST['project_type']}' ";

        // change the operator from WHERE to AND
        $operator = 'AND';
    }

    // check if technologies were submited to be searched for
    if(isset($_POST['technology']) and $_POST['technology'] != '') {

        // remove spaces that might follow commas
        $tecnology = str_replace(', ', ',', $_POST['technology']);

        $technologies_array = explode(',', $tecnology);

        // explode the string to an array (where commas are)
        // and add the elements of the array to the SQL statement
        foreach($technologies_array as $one_technology) {

            $qry .= "$operator technologies='$one_technology' ";

            // change the operator from WHERE to AND
            $operator = 'AND';
        }
    }

    // check if starting year was submited to be searched for
    if(isset($_POST['starting_year']) and $_POST['starting_year'] != '') {

        // add the string to the conditions array
         $qry .= "$operator starting_year={$_POST['starting_year']} ";

        // change the operator from WHERE to AND
        $operator = 'AND';
    }

    // check if locations were submited to be searched for
    if(isset($_POST['locations']) and $_POST['locations'] != '') {

        // remove spaces that might follow commas
        $locations = str_replace(', ', ',', $_POST['locations']);

        // explode the string to an array (where commas are)
        // and add the elements of the array to the SQL statement
        foreach($locations as $location) {

            $qry .= "$operator location='$location' ";

            // change the operator from WHERE to AND
            $operator = 'AND';
        }
    }

    // run the query and display the search results

    // dont forget to enter your db credentials here :-)
    $link = mysqli_connect('localhost', 'dbuser', '********', 'test');    
    $res = mysqli_query($link, $qry);

    // this will show the records found
    $result  = '<p>Found the following records:<br />';

    // result table head
    $result .= "<samp>TYPE | NAME | YEAR | LOCATION | TECNOLOGIES | FINANCING<br />";
    $result .= "-------------------------------------------------------<br />";

    // result table rows
    while($row = mysqli_fetch_assoc($res)) {

        $result .= "{$row['project_type']}| {$row['project_name']}| ";
        $result .= "{$row['starting_year']}| {$row['location']}| ";
        $result .= "{$row['technologies']}| ";
        $result .= "{$row['financing_type']} in amount of {$row['financing_amount']}";
        $result .= '<br />';
    }

    $result .= '</samp>';

    // display the query
    $result .= "<p>The query was:<br />$qry</p>";
} 

// if the form was not submited just display the page with the form
$frm  = '<form action="#" method="post">'; 

$frm .= 'Project type<br /><select name="project_type">';
$frm .= '<option value="0">-- Please select ---</option>';
$frm .= '<option value="automobile">Automobile</option>';
$frm .= '<option value="manufacturing">Manufacturing</option>';
$frm .= '<option value="marketing">Marketing</option>';
$frm .= '<option value="communication">Communication</option>';
$frm .= '</select><br />';

$frm .= 'Technology<br /><input type="text" name="technology" /><br />';

$frm .= 'Starting year<br /><input type="text" name="starting_year" /><br />';

$frm .= '<input type="submit" name="submit" value="submit" /><br />';
$frm .= '</form>';

// echo the form
echo $frm;

// echo the results (if any)
echo $result;

And this is the data I made up:

The project table:

project_id  project_type    project_name    starting_year   location
1   automobile  Project 1   2010    London
2   automobile  Project 2   2006    Paris
3   marketing   Project 3   2010    New York
4   automobile  Project 4   2009    Rome
5   communication   Project 5   2005    Rome
6   manufacturing   Project 6   2003    Rome
7   communication   Project 7   2011    Rome
8   communication   Project 8   2005    Rome
9   marketing   Project 9   2009    Rome

The technology table:

project_id  technologies
1   Java
2   C
3   C
4   C++
5   PHP
6   Oracle
7   PHP, mysql
8   HTML
9   HTML, CSS

The financing table:

project_id  financing_type  financing_amount
1   loan    15000
2   loan    15000
3   leasing     25000
4   loan    45000
5   loan    18000
6   leasing     20000
7   leasing     15000
8   loan    35000
9   loan    45000

[harshm027]

Hi,

Thanks a lot, now everything is working fine. I had a few problems with my database as there were multiple entries. But now everything is working smoothly.

Thanks again.