Using PHP inside a SQL query to determine how the next part of the query should be

Question

first post at StackOverflow. I hope my question is good enough and well formulated.

I have a list of orders in my project with 3 dropdown menus, the dropdowns are for year, month and type of payment.

I'll just ask for one of them as the solution will be universal for them all.

For the month drop down, I have a list of all months (Jan-Dec) with values 01-12. I also have one option with the value 00, this option I want to represent all of the months, so if the user chooses this one the SQL query should ignore the AND MONTH() = value;

My SQL query I use is the following. This one is working as it should if all the strings are set to a value.

$sql = "SELECT      oc_order.order_id AS orderID,               
                oc_order.firstname AS firstname,
                oc_order.lastname AS lastname,
                oc_order.payment_code AS paycode,
                oc_order.order_status_id AS statusID,
                oc_order.total AS totalsumma,
                oc_order.date_added AS date

    FROM        oc_order

    WHERE       (oc_order.order_status_id = 5 OR oc_order.order_status_id = 31)

    AND         MONTH(date_added) = '$chosenmonth'
    AND         YEAR(date_added) = '$chosenyear'
    AND         oc_order.payment_code = '$chosenpayway'

    ORDER BY orderID ASC";

What i want to do is to replace the AND MONTH() with something like:

". $chosenmonth != '00' ? 'AND MONTH(date_added) = \'$chosenmonth\'' : '' ."

Thanks in advance!

UPDATE: I have now fixed the problem! Thank you Barmar for the assistance. I used echo to debug and finaly found a solution.. there is probably a nicer way to do this but this worked fine for me.

$chosenmonth = $_GET["month"];
$chosenyear = $_GET["year"];
$chosenpayment = $_GET["pay"];

$ifmonth = ($chosenmonth != NULL ? "AND MONTH(date_added) = '$chosenmonth' " : "");
$ifyear = ($chosenyear != NULL ? "AND YEAR(date_added) = '$chosenyear' " : "");
$ifpay = ($chosenpayment != NULL ? "AND oc_order.payment_code = '$chosenpayment' " : "");

$sql = "SELECT  oc_order.order_id AS orderID,               
                oc_order.firstname AS firstname,
                oc_order.lastname AS lastname,
                oc_order.payment_code AS paycode,
                oc_order.order_status_id AS statusID,
                oc_order.total AS totalsumma,
                oc_order.date_added AS date

    FROM        oc_order

    WHERE       (oc_order.order_status_id = 5 OR oc_order.order_status_id = 31)

    ". $ifmonth . $ifyear . $ifpay ."       
    ORDER BY orderID ASC";

What you've written will work if you put `AND MONTH(date_added = '$chosenmonth'` in double quotes. Variables aren't expanded inside single quotes. — Barmar, Apr 14 '16 at 20:13
You may also want to look at this question for a more general technique for building the WHERE clause dynamically. http://stackoverflow.com/a/28909923/1491895 — Barmar, Apr 14 '16 at 20:15
@Barmar Thank you for your answer. I tried replacing the whole row with the first AND to the following: ". $chosenmonth != '00' ? "AND MONTH(date_added) = \'$chosenmonth\'" : '' ." But when I do that it don't work at all.. Did I missunderstand something? PS: How is this a exact duplicate of another question? It's the first time I post on StackOverflow.. — TryingToLearn, Apr 15 '16 at 10:14
It's not an exact duplicate, but the solution to your problem is there, since the only problem you have is the use of the wrong type of quotes. If that didn't solve your problem, edit the question and show the new code as an `UPDATE` at the end, and I'll reopen it. — Barmar, Apr 15 '16 at 14:07
The way to debug a problem like this is to put `echo $sql;` into the script and then try running the sql by hand. — Barmar, Apr 15 '16 at 14:08
@Barmar Thanks again for the answer. I did edit the question and added the new code where I changed the row and the quotes. I don't know how to make a functional UPDATE as you asked for so I just wrote UPDATE. (Even tried googling Stackoverflow Update post but that showed alot of other stuff.. if you don't mind explaining this also that would be great!) Thanks again! — TryingToLearn, Apr 15 '16 at 18:29
@Barmar Sorry if this is considered spamming.. I am trying to debug the code now with echo and I see that most of the code is left out for some reason.. You can view a shortened code in action at: www.avetian.se/test/test.php?month=00 — TryingToLearn, Apr 15 '16 at 19:03

score 0 · Accepted Answer · answered Apr 15 '16 at 19:10

you need parentheses around the ternary expression, because the default precedence is combining things differently than you need.

$sql = "SELECT  oc_order.order_id AS orderID,               
                oc_order.firstname AS firstname,
                oc_order.lastname AS lastname,
                oc_order.payment_code AS paycode,
                oc_order.order_status_id AS statusID,
                oc_order.total AS totalsumma,
                oc_order.date_added AS date

    FROM        oc_order

    WHERE       (oc_order.order_status_id = 5 OR oc_order.order_status_id = 31)

    ". ($chosenmonth != '00' ? "AND MONTH(date_added) = '$chosenmonth'" : "") ."
    AND         YEAR(date_added) = '$chosenyear'
    AND         oc_order.payment_code = '$chosenpayway'

    ORDER BY orderID ASC";

It would be easier to understand, and less errorprone, if you did the conditional in a separate statement.

$monthcheck = $chosenmonth != '00' ? "AND MONTH(date_added) = '$chosenmonth'" : "";

$sql = "SELECT  oc_order.order_id AS orderID,               
                oc_order.firstname AS firstname,
                oc_order.lastname AS lastname,
                oc_order.payment_code AS paycode,
                oc_order.order_status_id AS statusID,
                oc_order.total AS totalsumma,
                oc_order.date_added AS date

    FROM        oc_order

    WHERE       (oc_order.order_status_id = 5 OR oc_order.order_status_id = 31)
    $monthcheck
    AND         YEAR(date_added) = '$chosenyear'
    AND         oc_order.payment_code = '$chosenpayway'

    ORDER BY orderID ASC";

Thank you.. didn't see this answer but I have it fixed now.. I updated the original code with the fix if anyone else wants to see.. Thanks again! — TryingToLearn, Apr 15 '16 at 19:30

Using PHP inside a SQL query to determine how the next part of the query should be

1 Answers1