Dynamic SQL queries in code possible?

Question

Instead of hard coding sql queries like Select * from users where user_id =220202 can these be made dynamic like Select * from $users where $user_id = $input.

Reason i ask is when changes are needed to table/column names i can just update it in one place and don't have to ask developers to go line by line to find all references to update. It is very time consuming. And I do not like the idea of exposing database stuff in the code.

My major concern is load time. Like with dynamic pages, the database has to fetch the page content, same way if queries are dynamic first system has to lookup the references then execute the queries, so does it impact load times?

I am using codeignitor PHP.

If it is possible then the next question is where to store all the references? In the app, in a file, in the DB, and how?

---EDIT:
Even better: Can the SQL query itself be made dynamic? I can just reference $sqlA instead of the whole query? This way if I have to re-write the query I can just update 1 file.

Answer 1

Of course you can, if that's what you wish. I'd rather recommend you taking more time to design you database but changes in the schema are inevitable in the long run.

I don't think load time would be an issue with this because ussually the bottleneck in this applications is in the database.

Finally my recommendation is to save this in a file just by declaring the column names as php variables

Answer 2

mysql_query() takes a string and it doesn't need to be a constant string, it can be a variable.

$SQL = "SELECT foo FROM bar b";
SQLSet = mysql_query($SQL);

Aa you can see, you can use ordinary string manipulation to build your whole SQL query.

$SQL="SELECT * FROM MyTable";
$BuzID = 5;
$Filter = "Buz=".$BuzID;
if (is_numeric($BuzID)) SQL .= " WHERE ".$Filter;
SQLSet = mysql_query($SQL);

This will expand to "SELECT * FROM MyTable WHERE Buz=5" if $BuzID is set to any number. If not the statement will just be "SELECT * FROM MyTable"

As you can see, you can build very complex SQL statements on the fly without need of variable support in the SQL server.

IF you want constants such as database name, user login, you can but them in a separate include located outside the public directory.

SecretStuff.inc.php
$__DatabaseName = "localhost";
$__UserName = "DatabaseAccess";
$__Password = "E19A4F72B4AA091C6D2";

Or have the whole PHP database connection code in the same file.

Answer 3

It depends on the database driver(s) you are using. The old PHP database drivers did not support placeholders (PHP 3.x). The modern (PDO) ones do. You write the SQL with question marks:

SELECT * FROM Users WHERE User_ID = ?

You then provide the value of the user ID when you execute the query.

However, you cannot provide the column name like this - only values. But you could prepare a statement from a string such as:

SELECT * FROM Users WHERE $user_id = ?

Then you provide the value at execute time.

Answer 4

Because you are using Codeigniter, I would reccomend utilizing the Active Record Class to accomplish what you are trying to do.

The active record class enables you to build queries dynamically in steps allowing you to build them logically. So to take your example using active record...

( this could be accomplished with less code, I'm just trying to illustrate Active Record )

$this->db->select('*');

$this->db->from($table);

$this->db->where($user_id, $input);

and so to show what I mean about building the query logically, you can build whatever logic you want INTO the query building process. Lets say you have a $limit variable that you set if you want to limit the number of results you get. BUT if it isn't set (or NULL) you don't want to set the limit clause.

if ( $isset($limit) ) {
    $this->db->limit($limit);
}

and now to execute your query now that it has been built

$query = $this->db->get();

Then just deal with $query with your database class just like you would any other CodeIgniter query object.