Storing SQL Selects in a table and running them in PHP

[bazianm]

Hi,

 

I would like to store sql query strings in a database. A query might look this like:

 

SELECT * from ListTable where id = '$id'

 

The idea would be to retrieve the string and run it as a SQL string. $id would be defined in the procedure so the idea is that when I send the SQL String through, $id would be replaced with its value and everything would work.

 

Under normal circumstances, the code would look like this:

$id = "somevalue";
$lcSQL = "SELECT * from ListTable where id = '$id'";

I would pass the SQL select off to mySQL and all is right with the world. I just want to store the string in a database, retreieve it and use it.

 

Thanks for your help in advance...

 

 

[ginerjm]

So - just do it? 

 

What is your problem with doing this, albeit it doesn't make sense to me.

[CroNiX]

So, you want to query the db in order to retrieve another query, which then you'll presumably execute? That's 2 queries when it should just be one. Doesn't sound very efficient. Why not just execute the query you want to run in the first place?

[davidannis]

$id = "somevalue";

$lcSQL = "SELECT * from ListTable where id = '$id'";

$result=mysqli_query($link, $query);

if ($result){

    $row=mysqli_fetch_assoc($result);

    $result=mysqli_query($link, $row['stored_query']);

    //do stuff here

}else{

  //handle error here

  echo 'No stored query for that id';

}

[Jacques1]

Stuffing queries into the database makes absolutely no sense whatsoever.

 

It makes even less sense when I look at the actual query. All that does is retrieve one particular record from one table, which means the only relevant parameters are the ID the possibly the table.

 

Anyway, if you want proper help, you need to tell us the actual problem, not what you think is the solution.

[bazianm]

So, you want to query the db in order to retrieve another query, which then you'll presumably execute? That's 2 queries when it should just be one. Doesn't sound very efficient. Why not just execute the query you want to run in the first place?

 

Well, here's the idea. I am creating a shell controller and view (I am using CodeIgniter). I can write one view and one controller that can control many different master tables for data entry. The differences between the various tables can be stored in a table. So, if I want to run the "customers" page, I can query the database to get all the information I need to run the customers page. Among the things that are different from table to table is the SQL select I will have to run the retrieve data for the page. So, one of the columns in the "pages" table is the SQL Select I will run in order to retrieve a specific customer record. By data driving the specific page information, I can save a lot of time in development.

 

So, I will have a field called cRowSQL and it could have, as a string, the following SQL Select:

SELECT customer.id, customer.name, customer.address, customer.city, customer.state, customer.zip, SUM(invoices.totalamount) as sales 
  FROM customer
  LEFT OUTER JOIN invoice ON invoice.idcustomer = customer.id
 WHERE customer.id = '$id'

I want to retrieve the SQL select and replace in the $id with the real value so I can get the row I want.

 

If I were writing the code directly, I would encase the string in a double quote and it would evaluate the variable immediately. I want the same functionality except I am not storing it as a liternal string into SQL code variable. I am trying to figure out how to do that...

 

Does this help?

[bazianm]

Stuffing queries into the database makes absolutely no sense whatsoever.

 

It makes even less sense when I look at the actual query. All that does is retrieve one particular record from one table, which means the only relevant parameters are the ID the possibly the table.

 

Anyway, if you want proper help, you need to tell us the actual problem, not what you think is the solution.

 

The simple query was just an example. The point of putting the queries into a database saves me from duplicating code. In effect, I am using data driven application development. I could have pages that join multiple tables and have multiple filters in the where clause...

[davidannis]

You can use str_replace like this:

<?php
$var='$id';
$id='123';
$select='SELECT * FROM mytable WHERE id=\'$id\'';
$answer= str_replace($var, $id, $select, $count);
echo $answer;
?>

There must be more elegant solutions with preg_match to make sure you get variables that are whole words ($apple not treated as $a.'pple') and I'm guessing with $$

[davidannis]

I'm thinking use a delimiter to pick out the variables. You can then do something like this:

<?php
$var='id';
$id='123';
$select='SELECT * FROM mytable WHERE id=\'~id~\'';
$answer= str_replace(('~'.$var.'~'), $$var ,$select, $count);
echo $answer;
?>

You can pass str_replace an array of values to replace multiple variables in one pass.

[gizmola]

I know you think that this idea is actually simpler, but it's really not. If you had a decent base model class you could create all the child models in no time at all and be able to implement your admin/crud without sticking sql strings into a database.