PHP MySQL changing select query based on variables

Question

I've got 4 variables (year, month, projectcode and type).

So if the person submits the year and leave the other 3 variables blank then the select query must be select * from table where year(trandate) = $var
But if the user supplies the year & month then the query must be select * from table where year(trandate) = $var and month(trandate) = $var1

If the user selects year, month & projectcode and leave type blank then query must be select * from table where year(trandate) = $var and month(trandate) = $var1 and projcode = $var3

And so on. How do I go about programming this, otherwhise I will have an awful lot of combinations?

Hope the question is clear.

For example this is what I have so far but I can see that there is too much combinations:

if (empty($ej1year) && empty($ej1month) && empty($ej1proj) && empty($ej1type)) {
$rows = mysql_query("SELECT a.employee
,a.trandate
,CONCAT(a.workdone, '-', wc.BriefDescription) as WorkCodeActivity
,CONCAT(a.custname, '-', cl.ShortName) as clientdet
,a.qty
,a.rate
,a.amount
,a.ref
,a.projcode
,a.type
,a.qty*a.rate as costrate
FROM transaction as a
LEFT JOIN workcodes as wc ON a.workdone=wc.WorkCodeNo
LEFT JOIN clients as cl On a.custname=cl.EntityNo");
} elseif (empty($ej1year) && !empty($ej1month)) {
$rows = mysql_query("SELECT a.employee
,a.trandate
,CONCAT(a.workdone, '-', wc.BriefDescription) as WorkCodeActivity
,CONCAT(a.custname, '-', cl.ShortName) as clientdet
,a.qty
,a.rate
,a.amount
,a.ref
,a.projcode
,a.type
,a.qty*a.rate as costrate
FROM transaction as a
LEFT JOIN workcodes as wc ON a.workdone=wc.WorkCodeNo
LEFT JOIN clients as cl On a.custname=cl.EntityNo
where month(trandate) = '$ej1month'");

} elseif

score 2 · Answer 1 · edited May 23 '17 at 11:43

Something like this should work:

<?php
$where = array();
$binds = array();

if ($_POST['month'] !== '') {
    $where[] = 'month = ?';
    $binds[] = $_POST['month'];
}
if ($_POST['year'] !== '') {
    $where[] = 'year = ?';
    $binds[] = $_POST['year'];
}
...

$query = 'select * from table where ' . implode(' AND ', $where);
$db->execute($query, $binds);

You'd want to add a check to see if any variables are set. If you don't mind if all are empty, you can change

$where = array();

to

$where = array(1);

Which will end up as "where 1" in the query, effectively selecting everything.

EDIT: I see you are using mysql_ functions, that's not ideal as they are deprecated. You should update to PDO or mysqli ASAP. Here's a version that will work with mysql_

<?php
$where = array();

if ($_POST['month'] !== '') {
    $where[] = "month = '" . mysql_real_escape_string($_POST['month'])  . "'";
}
if ($_POST['year'] !== '') {
    $where[] = "year = '" . mysql_real_escape_string($_POST['year'])  . "'";
}
...

$query = 'select * from table where ' . implode(' AND ', $where);
$result = mysql_query($query);

Thanks, will give it a go and report back on output – Wilest Jun 14 '15 at 15:51 — Wilest, Jun 14 '15 at 15:51

score 1 · Answer 2 · answered Jun 14 '15 at 15:43

Try this.

if (!empty($year) && empty($month) && empty($projectcode) && empty($type)){
  $query = 'select * from table where year(trandate) = $var';
}elseif (!empty($year) && !empty($month) && empty($projectcode) && empty($type)){
  $query = 'select * from table where year(trandate) = $var and month(trandate) = $var1';
}elseif (!empty($year) && !empty($month) && !empty($projectcode) && empty($type)){
  $query = 'select * from table where year(trandate) = $var and month(trandate) = $var1 and projcode = $var3'
}

PHP MySQL changing select query based on variables

2 Answers2