Dynamic dynamic SQL

edewolf

Hi,

I have a mySql DBtable containing info on how to create drop down lists.

Table: myDropDownLists

ID SQL

1 SELECT ID, name FROM sysUsers
2 SELECT ID, name FROM sysUsers WHERE status = 3
3 SELECT ID, name FROM sysUsers WHERE userGroup = $_POST["p_userGroupID"]

This works great for the first 2 entries in this example, but for the third SQL statement I do not get any records returned. If I traced what SQL statement was processed, I noticed that the variable was not substitited by its value.
I tried this in a zillion different ways, but I do not get it to work.

I get it to work when I assemble the SQL statement using PHP, but when I get it directly out of the DB, then the variableis not substituted

Is there someone who did this before or who knows a way to get this done?

Thx

Edward Dewolf

devinemke

your problem is that you are storing a PHP variable as a literal inside of string. as a result you are passing the literal name of the variable to your query instead of the the variable's value. you can use the [man]eval[/man] function like so:

$_POST['p_userGroupID'] = '123';

$sql = 'SELECT ID, name FROM sysUsers WHERE userGroup = $_POST[p_userGroupID]';
eval("\$sql = \"$sql\";");

echo $sql;

this can get quite messy. perhaps an easier solution is not store the literal PHP var in your string at all but just a place holder, then use [man]str_replace[/man] to replace before running the query:

$_POST['p_userGroupID'] = '123';

$sql = 'SELECT ID, name FROM sysUsers WHERE userGroup = [var]';
$sql = str_replace('[var]', $_POST['p_userGroupID'], $sql);

echo $sql;

edewolf

Hi,

You are right, the second option would be better, but since I do not know in advance what variabels will be used, I cannot use the second option.

The first option is working great

Thx