mysql query based on php variable

Question

This is my code

  $query = "select ((recipients.maennlichDeutsch+recipients.maennlichAuslaender+recipients.weiblichDeutsch+recipients.weiblichAuslaender)/inhab.Einwohner) as Sozialhilfeempfaenger,jahr from recipients left join education on recipients.Bundesland = education.FK_Land and recipients.Jahr = education.FK_Jahr left join inhab on recipients.Bundesland = inhab.FK_land and recipients.Jahr = inhab.FK_Jahr where education.Abschluss in ('Hauptschulabschluss') and recipients.Bundesland = '.$_REQUEST['land'].'";
$result=mysqli_query($db, $query) or die('Error querying database.');

$q = "select ((education.weiblich+education.maennlich)/inhab.Einwohner) as 'niedriger Bildungsstand',Jahr from recipients left join education on recipients.Bundesland = education.FK_Land and recipients.Jahr = education.FK_Jahr left join inhab on recipients.Bundesland = inhab.FK_land and recipients.Jahr = inhab.FK_Jahr WHERE education.Abschluss in ('Ohne Haupschulabschluss','Hauptschulabschluss') and recipients.Bundesland = '.$_REQUEST['land'].'";
$r=mysqli_query($db, $q) or die('Error querying database.');

With $_REQUEST['land']. I'm trying to fetch the selected value of a dropdown menu. The variable land is working, I can echo it without problems. The querys also work fine without $_REQUEST['land'].

But now I'm getting 500 error. Do you know what I'm doing wrong here ?

score 1 · Accepted Answer · answered May 25 '17 at 16:08

You should escape your variable to concatenate with double quotes (as you're starting your string with double quotes), change this:

$q = "... and recipients.Bundesland = '.$_REQUEST['land'].'";

To this:

$q = "... and recipients.Bundesland = '".$_REQUEST['land']."'";

Same for the first query. Hope it helps.

score 0 · Answer 2 · answered May 25 '17 at 16:10

0

I think that maybe the problem is on your query line , you open with double quotes and you end it with single one

 $query = "select.... '.$_REQUEST['land'].'";

try to use simply this :

 $query = "select.... ".$_REQUEST['land'];

answered May 25 '17 at 16:10

Omis Brown

199
2
16

score 0 · Answer 3 · answered May 25 '17 at 16:10

try it:

$query = "select ((recipients.maennlichDeutsch+recipients.maennlichAuslaender+recipients.weiblichDeutsch+recipients.weiblichAuslaender)/inhab.Einwohner) as Sozialhilfeempfaenger,jahr from recipients left join education on recipients.Bundesland = education.FK_Land and recipients.Jahr = education.FK_Jahr left join inhab on recipients.Bundesland = inhab.FK_land and recipients.Jahr = inhab.FK_Jahr where education.Abschluss in ('Hauptschulabschluss') and recipients.Bundesland = '".$_REQUEST['land']."'";
$result=mysqli_query($db, $query) or die('Error querying database.');

$q = "select ((education.weiblich+education.maennlich)/inhab.Einwohner) as 'niedriger Bildungsstand',Jahr from recipients left join education on recipients.Bundesland = education.FK_Land and recipients.Jahr = education.FK_Jahr left join inhab on recipients.Bundesland = inhab.FK_land and recipients.Jahr = inhab.FK_Jahr WHERE education.Abschluss in ('Ohne Haupschulabschluss','Hauptschulabschluss') and recipients.Bundesland = '".$_REQUEST['land']."'";
$r=mysqli_query($db, $q) or die('Error querying database.');

i think it works. :) , i just replace ' for " in query

score 0 · Answer 4 · answered May 25 '17 at 16:11

Concatenation Issues, always prefer use Curly Brackets to avoid those problems:

Curly Brackets {} Curly brackets are used to mark class, function (methods in OOP terminology), loop and control structure bodies.

They can also be used within strings to separate variables from surrounding text.

1. $verb = 'add'; 2. echo "Present tense of this verb is $verb"; Think that you want to display the past tense of the verb without redefining it (just by adding ‘ed’).

1. echo "Past tense of this verb is $verbed"; If you tried above way then PHP would search for variable $verbed and throw an error (since it’s not defined). To separate the verb from suffix ‘ed’, you can use curly brackets as below.

1. echo "Past tense of this verb is {$verb}ed"; If $verb is an array, an element of it can be used like below.

1. echo "Past tense of this verb is {$verb['past_tense']}"; If $verb is an object and has a method called getPastTense() that returns past tense of the verb, it can be used like below.

1. echo "Past tense of this verb is {$verb->getPastTense()}";

 $query = "select ((recipients.maennlichDeutsch+recipients.maennlichAuslaender+recipients.weiblichDeutsch+recipients.weiblichAuslaender)/inhab.Einwohner) as Sozialhilfeempfaenger,jahr from recipients left join education on recipients.Bundesland = education.FK_Land and recipients.Jahr = education.FK_Jahr left join inhab on recipients.Bundesland = inhab.FK_land and recipients.Jahr = inhab.FK_Jahr where education.Abschluss in ('Hauptschulabschluss') and recipients.Bundesland = '{$_REQUEST['land']}'";
$result=mysqli_query($db, $query) or die('Error querying database.');

$q = "select ((education.weiblich+education.maennlich)/inhab.Einwohner) as 'niedriger Bildungsstand',Jahr from recipients left join education on recipients.Bundesland = education.FK_Land and recipients.Jahr = education.FK_Jahr left join inhab on recipients.Bundesland = inhab.FK_land and recipients.Jahr = inhab.FK_Jahr WHERE education.Abschluss in ('Ohne Haupschulabschluss','Hauptschulabschluss') and recipients.Bundesland = '{$_REQUEST['land']}'";
$r=mysqli_query($db, $q) or die('Error querying database.');

Curly Braces Notation in PHP

score 0 · Answer 5 · answered May 25 '17 at 16:16

You could double quote your strings and use {$var} within the double quotes. Makes it much more readable.

$query = "... and recipients.Bundesland = '{$_REQUEST['land']}'";

Further more I'd suggest you try PDO or a NotORM. At least pull your GET/POST/REQUEST through something that sanitize it.

mysql query based on php variable

5 Answers5