conditional where clause based on value passed through

Question

In php I have an mysql query which should only include the where clause if the value passed through is not empty.

Unfortunately the mysql doesn't seem to work. Could you please tell me what I am doing wrong?

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

                }
else
                $q ="";



$query = mysql_query("Select q1.Umsatz, kundendatenbank.Kundenname from

(SELECT sum(rechnung.Betrag) as Umsatz, kundendatenbank.Hauptname 
fROM auftrag
JOIN rechnung ON rechnung.Auftragsnummer = auftrag.Auftragsnummer
JOIN kundendatenbank ON auftrag.Kundennummer=kundendatenbank.Kundennummer

JOIN auftrag_ma on auftrag.Auftragsnummer=auftrag_ma.Auftragsnummer";

if ($q!="")
        $query =  $query." where Quarter(auftrag_ma.Datum)='$q'";


$query = $query."group by kundendatenbank.Hauptname)q1
join kundendatenbank on q1.Hauptname=kundendatenbank.Kundennummer");

Try adding a space, between the two query parts, like this: `$query." group by...` — Amal Murali, Jul 28 '13 at 15:11
Watch out for SQL injection. http://stackoverflow.com/questions/60174/how-to-prevent-sql-injection-in-php — James P., Jul 28 '13 at 15:49
possible duplicate of [Json code doesn't produce result](http://stackoverflow.com/questions/17910220/json-code-doesnt-produce-result) — Sean, Jul 28 '13 at 17:06

score 1 · Answer 1 · edited Jun 20 '20 at 09:12

Run the query after you fully define it. You can't have an if statement inside the function call.
Escape $q param to protect yourself from SQL injection. I did it the if statement.
mysql_* functions are deprecated, consider switching to PDO or mysqli.

code

$query = "Select q1.Umsatz, kundendatenbank.Kundenname from

(SELECT sum(rechnung.Betrag) as Umsatz, kundendatenbank.Hauptname 
FROM auftrag
JOIN rechnung ON rechnung.Auftragsnummer = auftrag.Auftragsnummer
JOIN kundendatenbank ON auftrag.Kundennummer=kundendatenbank.Kundennummer
JOIN auftrag_ma ON auftrag.Auftragsnummer=auftrag_ma.Auftragsnummer";

if ($q != "")
{
   $q = mysql_real_escape_string($q);
   $query =  $query." where Quarter(auftrag_ma.Datum)='$q'";
}


$query = $query." GROUP BY kundendatenbank.Hauptname) q1
join kundendatenbank on q1.Hauptname=kundendatenbank.Kundennummer";

$result = mysql_query($query);

Unfortunately this didn't help. I still don't get any values from my table. When running it in mysql directly and inputting a value for q it works. — Sandra Grassl, Jul 28 '13 at 15:26
@SandraGrassl Try the new version: there was no space before group by. I added it now. — user4035, Jul 28 '13 at 15:47

score 0 · Answer 2 · answered Jul 28 '13 at 15:44

actually all you have to do is this it should work. the where will stay empty if there is nothing selected without messing with your query.

    if (isset($_GET['q'])) 
            {
            $q =  $_GET['q'];
            $whereclause = "WHERE <insert your table.columnname> = '".$q ."'";

            }
    else {
            $whereclause = "";
    }


    $query = mysql_query("Select q1.Umsatz, kundendatenbank.Kundenname from

    (SELECT sum(rechnung.Betrag) as Umsatz, kundendatenbank.Hauptname 
    FROM auftrag
    JOIN rechnung ON rechnung.Auftragsnummer = auftrag.Auftragsnummer
    JOIN kundendatenbank ON auftrag.Kundennummer=kundendatenbank.Kundennummer

    JOIN auftrag_ma on auftrag.Auftragsnummer=auftrag_ma.Auftragsnummer $whereclause";       


    $query = $query."group by kundendatenbank.Hauptname)q1
    join kundendatenbank on q1.Hauptname=kundendatenbank.Kundennummer");

Both answers seem to work. What is strange is that now the json string doesn't work anymore — Sandra Grassl, Jul 28 '13 at 16:05
I'm not sure I see the json string? Am I missing something? sorry my German's a little rusty. I may have put the whereclause variable in the wrong location you can join the two queries you have together and add the whereclause after the last join statement, that is where I have mine. — puddleJumper, Jul 28 '13 at 17:05

conditional where clause based on value passed through

2 Answers2

code