How to write the SQL query for a search having to consider several values using php?

PHP
1

On my page, I have text boxes and drop down lists. All the values are stored in arrays. I want to fetch values of yeat from the database vehicles. Below shows the html code.

<form name="frm_vehicles" action="processinput.php" method ="post">                                 
          Enter year of vehicle
           <input type="text" name="year[]" >

            <select name="choice[]">
                  <option value="cars" selected="selected">Cars</option>
                  <option value="lorry">Lorry</option>
            </select>

            <select name="query[]">
                   <option value="and" selected="selected">AND</option>
                   <option value="or">OR</option>
                   <option value="not">NOT</option>
            </select>

             <br> 

             <input type="text" name="year[]">

             <br>
             <select name="query[]">
                      <option value="cars" selected="selected">Cars</option>
                      <option value="lorry">Lorry</option>
             </select>
            <div id="disp"></div>
              <input type="button" name="addRow" onclick= "addRow()">
             <input type="button" type="submit" name="search" value="Search">
</form>

On my page, I have text boxes and drop down lists. All the values are stored in arrays. I want to fetch values of v from the tables vehicles. Below shows the html code.

On clicking the button addRow, a javasccript function is called and another row is added. The elemets below are added.

        <input type="text" name="year[]" >

        <select name="choice[]">
              <option value="cars" selected="selected">Cars</option>
              <option value="lorry">Lorry</option>
         </select>

Thus, we can see that the fields are dynamically added.

When all fields have been selected, the input are sent to the processinput.php file. In this file, SQL statements are written. What should happen is a statement should be executed based on the user’s input. Cars and Lorry are two different tables

The and/or/not are used as joins. If ‘and’ is selected, both value of the previous year entered and the value after should match. If ‘or’ is selected, only one value may match. If ‘not’ is selected, the values after the ‘not’ is not selected. So what need to be done is to write a SQL statement to take the values and perform the search.

For example I add a third row. i entered 2006 in first text box and selected table ‘cars’ and ‘and’ in the 2nd row, i entered year selected table ‘lorry’ and ‘not’ in the third row i entered table ‘year’ and ‘cars’

$year=$_POST['year'], $choice=$_POST['choice'], $query=$_POST['query']

Can someone guide me how to write a single sql statement.

How will i mention the $year, $choice and $query in my sql statements?

2

What query did you try by yourself? What did you learn from public tutorials like the ones on Sitepoint? Read about Prepared Statements.

3

I tried this

$additional = '';

if (count($year) > 1) {

    foreach($year as $key=>$y) { 
        if($key > 0) {
        $additional .= $query[0] . "(`year` = $year[$key] and `choice` = $choice[$key])";
        }
    }
}

$baseQuery = "SELECT * 
FROM <table>
WHERE (`year` = $year[0] and `choice` = $choice[0]) " . $additional;
4

What query does this produce? Whats wrong with it?

5

I found this code on a forum.
In fact what i want to do is to use value of the $table in the query.
eg. SELECT * FROM $table
Is that correct?

6

If you want to use a variable table name, that’s correct. But you could easily try this by yourself. Also, for security reasons, you can use a whitelist that contains your table names.

7

Building up complex queries by hand is no fun at all. Especially if you want them to be reasonably secure. There is where query builders can help.

8

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.