Page 1 of 1

Strict Query or Pattern Query

Posted: Tue Jul 17, 2007 9:15 am
by TheMoose
Not sure what to call it, but I had an "idea" regarding how a lot of framework/custom install web apps deal with SQL queries and the respective tables they query from.

I know a few apps that do something like:

Code: Select all

DEFINE("UserTable", $prefix . "_users");
...
$db->query("SELECT * FROM " . UserTable . " WHERE id=1");
Which to me, is ugly. There's no finesse involved.

I usually do things differently with a CONFIG item to hold the global config for the site. Still almost the same as above, just doesn't use DEFINE(). When it came across to me, would it be bad to add in a "pattern" to the SQL query itself, so that it uses pattern matching to decide the table name instead of defines/config in the query itself? IE:

Code: Select all

$config['tables']['user_table'] = "users";
...
$db->query("SELECT * FROM {tables[user_table]}");
I know there's essentially no difference, and would take extra processing time because of the pattern match/replace (if I was aiming for high efficiency), but it just seems easier on the eyes when I go back through code.

Mostly just a rant about an idea that I had. :)

Posted: Wed Jul 18, 2007 11:56 pm
by Benjamin
I spent a year writing a huge application and used table constants all over the place. I think it's a waste of time. I never had to change 1 table name and it just wastes memory, takes longer to type and as you said, it is ugly. With the exception of a few specialized applications, I just hardcode all the table names.

The last thing I would do is run every query through a regex expression. To me that is just inefficient. I'm a lean and mean kind of guy.

But to answer your question, yes it does look better.

Posted: Thu Jul 19, 2007 3:35 am
by stereofrog
I use an sql formatting library that allows me to

Code: Select all

$db->query("SELECT * FROM ?=TABLE_NAME WHERE id=1"); 
or
$db->query("SELECT * FROM ?_users WHERE id=1");
where "?=TABLE_NAME" stands for "insert constant TABLE_NAME here" and "?_" is "insert the value of $db->table_prefix".

Posted: Thu Jul 19, 2007 8:48 am
by TheMoose
astions wrote:I spent a year writing a huge application and used table constants all over the place. I think it's a waste of time. I never had to change 1 table name and it just wastes memory, takes longer to type and as you said, it is ugly. With the exception of a few specialized applications, I just hardcode all the table names.

The last thing I would do is run every query through a regex expression. To me that is just inefficient. I'm a lean and mean kind of guy.

But to answer your question, yes it does look better.
Yeah I agree wholeheartedly that it is extremely heavy as an app, but hardcoding isn't exactly what I want to resort to. Part of the stuff I'm working on, I want to eventually release it as one of those "install it yourself" apps, and the tablenames have to be configurable dependent upon the hosts requirements/constraints.

As for a custom one-off app, absolutely it's more efficient to hardcode table names, but does that efficiency come at a cost of encapsulation? Each time a table name changes for some reason, you'd have to go back through and change each reference to that tablename. However unlikely it is, I don't like to leave that open to risk especially given the nature of some of the people I deal with :(

Posted: Thu Jul 19, 2007 10:19 am
by Begby
If you want to do it as a install it type app, then I think the only thing you want to worry about is a table prefix, I don't think your users will be able to name each individual table. So that would be as easy as just using a single value or define in a config file and appending that to the beginning of each of your table names. So instead of defining UserTable you would define DB_PREFIX and use it like

Code: Select all

//in some config
define('DB_PREFIX', 'myApp_') ;

$sql = "SELECT * FROM " . DB_PREFIX. "users" ;

Posted: Thu Jul 19, 2007 5:26 pm
by alex.barylski
astions wrote:I spent a year writing a huge application and used table constants all over the place. I think it's a waste of time. I never had to change 1 table name and it just wastes memory, takes longer to type and as you said, it is ugly. With the exception of a few specialized applications, I just hardcode all the table names.

The last thing I would do is run every query through a regex expression. To me that is just inefficient. I'm a lean and mean kind of guy.

But to answer your question, yes it does look better.
I agree