MySQL query generator - best practices?

[treyk4]

The title is pretty self-explanatory. I'm working on a PHP engine, and I'm trying to implement a SQL query generator. The hardest part of this will be designing the system for using the SELECT command.

Are there any practices that I should try to follow when designing this, or any engines already in existence that I could model my engine after?

Thanks,
~Trey

[sDJh]

Don't know what you really want, but you could have a look at phpMyAdmin.

$action . $fields . $table . $jointype . $jointable . $joinfields . $setclause . $whereclause . $orderclause . $limitclause

The above should work for simple queries:

$action options
(1) SELECT
(2) INSERT INTO
(3) UPDATE
(4) DELETE FROM

$fields
(1) array of fields to display (with leading space)
(2-4) nothing

$table
(1-4) name of table/s (with leading space)

$jointype
(1) nothing OR join clause (with leading space), e.g. INNER/LEFT/RIGHT JOIN
(2-4) probably no need

$jointable
(1) nothing OR 2nd table (with leading space)
(2-4) probably no need

$joinfields
(1) nothing OR tablefield1=tablefield2 (with leading space)
(2-4) probably no need

$setclause
(1) nothing
(2-3) SET with field=value array (leading space)
(4) nothing

$whereclause
(1-4) nothing OR - WHERE field=value array including OR/AND/NOT/BETWEEN etc (leading space)

$orderclause
(1-4) nothing OR - ORDER BY fieldarray (leading space)

$limitclause
(1-4) nothing OR - LIMIT x or LIMIT x,y (leading space)

Obviously, more complicated/uncommon queries will require modification, e.g. GROUP/COUNT/SUM etc., but the above should work reasonably well.
You could create a dynamic ajax-like form to build the query in a step-wise way.

[treyk4]

Hey, thanks ardav! I've already started building the engine myself, your post should really help.

[digital-ether]

You're probably after the functionality in ORMs. http://en.wikipedia.org/wiki/Object-relational_mapping

The most popular for PHP being: http://www.doctrine-project.org/