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

Community · Accepted Answer · 2017-05-23 11:43:42Z

2

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

edited May 23, 2017 at 11:43

CommunityBot

11 silver badge

answered Jun 14, 2015 at 15:42

rjdown

9,2073 gold badges35 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Wilest Over a year ago

Thanks, will give it a go and report back on output

user4465396 · Accepted Answer · 2015-06-14 15:43:53Z

1

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

answered Jun 14, 2015 at 15:43

user4465396

Collectives™ on Stack Overflow

PHP MySQL changing select query based on variables

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related