How to make dropdown base MySQL query in PHP


Problem :

I have 6 <select> boxes. I am making a report base on <select> boxes. What I have done is make a form and and set method to GET and then getting these values through $_GET method on next page. Like

<form method="get" action="report.php">

<select name="select1">
<option value="none">Select value</option>
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
</select>


<select name="select2">
<option value="none">Select value</option>
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
</select>

<select name="select3">
<option value="none">Select value</option>
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
</select>
.....................
</form>

Here is PHP code. (These are all sample data :P) Just trying to tell you people my problem. I am getting all values an check if it's value equal to none or not and do this, etc.

if(
$cby == "none" 
&& $crequirment == "none" 
&& $cdepartment == "none" 
&& $period == "none" 
&& $ccontact == "none" 
&& $cattend == "none"
)
{
    $query = mysqli_query($db, "SELECT * FROM customer");
}



else if (
$cby != "none" 
&& $crequirment == "none" 
&& $cdepartment == "none" 
&& $period == "none" 
&& $ccontact == "none" 
&& $cattend == "none"
)
{
    $query = mysqli_query($db, "SELECT * FROM customer WHERE cby = $cby");
}
else if (
$cby == "none" 
&& $crequirment == "none" 
&& $cdepartment != "none" 
&& $period == "none" 
&& $ccontact == "none" 
&& $cattend == "none")
{
    $query = mysqli_query($db, "SELECT * FROM customer WHERE cdepartment = $cdepartment");
}
else if..........

What I want is to make as simple as possible. What method should I have to adopt for it?



Solution :

Optimize your code.

Create selects with names like data[cby], data[crequirment].
Instead of none use empty string.

In PHP:

if (isset($_GET['data']) {
    $where = [];

    foreach ($_GET['data'] as $name => $value) {
        if ($value != '') {
            $where[] = $name."=".$value; // sanitize `$name` and `$value` first
        }
    }

    if (!empty($where)) {
        $whereString = ' WHERE '.implode(' AND ', $where);
    } else {
        $whereString = '';
    }

    $query = mysqli_query($db, "SELECT * FROM customer".$whereString);
}

Mysql Tutorials

Mysql Howto..

how to adjust the rendering sequence of table rows in mysql after an update was made

How to place sum values (from MySQL using PHP) above presentation table?

How to use MySQL index columns?

How many times a event fired in mysql

How do I calculate an average date in SQL? [duplicate]

MySQL How to use LIKE with ending charaters

Creating a leaderboards, how would I go about displaying rank/position?

How to convert Mysql query for date range into Django query

How to add to an array in a database and get back each array and display it

MySQL how to log thread connections (I need user and ip)

How to perform two COUNT(*) fetches in one query?

how to optimise ajax queries to mysql

How to grant access to root on mysql?

How do delete related data in MySQL tables in a database?

How to join two tables having one will be limit in php mysql [closed]

How to change password and/or connect to mysql database MAMP?

how to get columns in a table in mysql with php?

Mysql how to get new date from a column date?

How do I get distinct rows by a column?

How to get the count of different categories from mysql database with php [closed]

Perl + DBI + MySQL: How To Run Multiple Queries/Statements [duplicate]

How to Check for record existence and do Insert in MYSQL?

how to extract data from excel (apache poi) to put it in mysql table using jsp?

Mysql: how to reuse a calculation within a query

How to be able to show marker's place name instead of longitude,latitude that retrieve from mysql database?

How to perform a case sensitive search on a case insensitive MySQL database?

How do I create a variable cursor inside a store procedure?

how to select a period of time in mysql?

How to put a local database onto a server?

How to get COUNT and INNER JOIN related with concated value field in mysql