The Idea
The idea is that my PHP page will execute specific SQL statements on a database depending on which form elements are filled in. Instead of having to write continuously lengthy IF statements to determine which statement to use, I would like to have the necessary statement "looked up".

The Database
I've got a MySQL database set up with a table for storing the SQL statements. Each record has a unique ID number and the statement itself.

The PHP Page
My PHP page has a simple (enough) form on it that contains several controls of varying type. When the form is submitted, a javascript function calculates an SQL ID number which is used for the "look up" of the required SQL statement.

THE PROBLEM
From the outset it works. The SQL ID is generated correctly and the statement is successfully looked up. However, the returned statement cannot be used on another database if the statement contains $_REQUEST s or other variables. This seems like it is simply due to the returned statement being a pure string.

Here is what I have so far (I apologise if it looks primitive):

$name = $_REQUEST['cst_name'];
$acc = $_REQUEST['cst_accno'];
$sql = getSQL($_REQUEST['sql_code']);

$result = mysql_query($sql);
if(!$result){
   die("Error: ".mysql_error());
}

while($nt=mysql_fetch_array($result)){
   // Do Stuff Here...
}

function getSQL($code){
   $sql = 'SELECT QUERY FROM QUERIES WHERE (((ID)="'.$code.'"));';
   $result = mysql_query($sql);
   $nt = mysql_fetch_array($result);
   return $nt[QUERY];
}

Example, sql_code is 10 and the SQL Statement returned against that ID no is: SELECT * FROM CUSTOMER_DETAILS WHERE (((ACCNO)='.$cst_acc.')); Subsequently that string is not parsed as an SQL statement for use with another mysql_query($sql) command.

Down to brass tacks; is there a way I can parse the returned string into a proper SQL statement with appropriate form $_REQUEST s apply? Otherwise is there another way of achieving the goal I'm working toward?


Thanks!

Although I do not recommend it, eval is an option.

A better option would be to use mysqli with prepared statements. The trouble is however, knowing which query uses which parameters. Of course stored procedures are another option, but poses the same parameter issue.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.