Dynamically writing SQL

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View

Dear all,

Apologies in advance that this post is database-related - I'll repost
to alt.php.sql / comp.databases.mysql if the replies I get suggest
that I'd get more help there, however, I think that this is more of a
logic problem rather than a SQL problem.

I've got a fairly complicated relational database (which happens to be
MySQL), which consists of many:many relations.  I want to build a form
(of mainly checkboxes) from which a user can select any data they want
from the database, and hence be able to write the SQL on-the-fly.
Obviously, if they only want data from 1 table, it is a simple select
statement. However, as soon as they want data from more than 1 table
(especially if these tables aren't next to each other in my structure,
not that the user would know that!), then my code needs  to recognise
which tables to join (and all the intermediate tables if necessary).

I assume that this has been done before. I've got some ideas of how to
do it, but they're (probably) highly inefficient! If anyone has any
links to tutorials / open-source solutions that could get me started,
I'd be most grateful.

Thanks in advance,

Re: Dynamically writing SQL

ChrisW wrote:
Quoted text here. Click to load it

That's going to be difficult, Chris.  One way would be to keep track of
your database design somewhere in your PHP logic, so when the user
checks data from tables "A" and "B", you know how the tables are linked.
  This can be a problem, however, if the tables are only linked
indirectly, i.e. through a third (or even fourth or fifth) table.

Alternatively, you can join all of your tables for each query, and only
supply the columns you want retrieved and the WHERE conditions.  That
way you won't need to keep track of all of the table layouts, but not
all queries may be possible with all tables joined.

It's not an easy problem to solve, but I do think it's pertinent to this

Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.

Re: Dynamically writing SQL

On Mon, 21 Sep 2009 09:07:51 -0400, Jerry Stuckle

Quoted text here. Click to load it

Up to a point.  The first question would have to be, is the database
properly normalised?
Geoff Berrow
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Put thecat out to email

Re: Dynamically writing SQL

Geoff Berrow wrote:
Quoted text here. Click to load it

Which has nothing to do with a PHP newsgroup.

Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.

Re: Dynamically writing SQL

Quoted text here. Click to load it


I think that Jerry might have pinned down the easiest solution to
implement here.  If you need a more dynamic solution, you may try an
OOP approach such as:

$t1 = new Table("name");
$t2 = new Table("other name");
$c1 = $t1->addColumn("colName");
$c2 = $t2->addColumn("colName2");
$c3 = $t2->addClumn("colName3");

So, you end up with Table objects that have a list of Columns and know
something about the columns' function in the table.  Columns would
have the respective Table object as a parent.  Then you could
implement a function like this:

$joinSQL = findJoinPath($c1, $c2);

Which would look at the parents of $c1 and $c2 to see if there is a
relationship possible. ($c2 has parent $t2 which has child $c3 which
is foreign key to $c1...)

If you were writing a data abstraction layer to implement this purpose
in a number of applications, I'd recommend something like this.  If
you're dealing with a fixed set of relationships and only on this
project, you might be better off implementing something along the
lines of an array structure:

$joinPath["t1.c1"]["t2.c2"] = array("t1.c1=3Dt2.c3");
$joinPath["t1.c1"]["t3.c4"] = array("t1.c1=3Dt2.c3", "t2.c1=3Dt3.c2");
// etc.

Which would be used to generate queries like
SELECT t1.c1, t2.c2 from t1 join t2 on t1.c1=3Dt2.c3;
SELECT t1.c1, t3.c4 from t1 join t2 on t1.cl=3Dt2.c3 join t3 on


Re: Dynamically writing SQL

Quoted text here. Click to load it

Chris, you're right this has definitely been done before and, at the
end of the day, no matter how fluent in SQL you might be, its always
convenient to encapsulate certain parts of your application such as
generating SQL.

As far as a stand-alone SQL-generation class I can't really recommend
anything but I suggest you take a look at an ORM (http://
en.wikipedia.org/wiki/Object-relational_mapping), such as Doctrine,
dORM, Propel, etc.  Personally, I like Zend_Db very much.

For example (to select where 'age' = $age and order by 'signupDate'):

$table = new MyTable();
$select = $table->select()->where('age = ?', $age)->order('signupDate

It can be as simple as the above or as complex as multiple joins.

Check it out!  (http://framework.zend.com/manual/en/zend.db.html)

Re: Dynamically writing SQL

Quoted text here. Click to load it

If all your foreign and primary keys are properly declared then all
the information you need is in the database, however historically
MySQL just ignored foreign key constraints so a lot of developers
don't bother putting them in. Also, I've seen a lot of schemas where
developers have added autoincrement ids to tables (which by definition
must be the primary key) even though the table already has an
intrinsic PK used as an FK in other tables.

Most ORM tools provide facilities for managing a better model of the
schema than is explicitly stated in the data dictionary (but beware -
there is a lot of crap out there).

Have a look at PEAR::DB_DataObject and Metabase.


Re: Dynamically writing SQL

Quoted text here. Click to load it

Well, the default approach is to just roll up your sleeves and write a massive
block of code creating a query from
nested "if" statements, using table.column syntax.  There is no real theoretical
difficulty.  It just depends on your
tolerance for spaghetti.

Personally, I love doing that kind of thing, LOL.  It's like a Chinese puzzle

It might be possible, depending on your database structure and the specifics of
your situation, to break the data
retrieval process down into more manageable blocks of code generating multiple
queries, store the data in arrays, and
then manipulate the fetched data with PHP for presentation.

It's been my experience in these situations that using outside code can take
longer than writing it yourself. There
might be a PEAR package that would help but there you sometimes run into
documentation problems.

Re: Dynamically writing SQL

I never used them myself in mysql, and I also read some 2007 articles
about their bad performance (no optimizations) but I'd try to use views...

Or another way, if you have tables A B C D E and the table x depends on
table x-1 you just prepare 5 SELECTs/VIEWs:

So if a user asks for data from table A and D you use SELECT ABCD if
using VIEWs or SELECT ...JOIN .... if using SELECTs.. you have only to fill

Of course you have to check with EXPLAIN if mysql is really optimizing...

Re: Dynamically writing SQL

Quoted text here. Click to load it

IME, the MySQL optimizer works very well. Assuming the views are non-
transparent (never used them in MySQL) then you're just as likely to
lock the query into a non-optimal execution over the long term than
get a short term fix.

But now we are getting off-topic.


Site Timeline