Suggestion: SQL wrapper module

[Jurrian91]

Hello,

Recently, I had some toughts about creating a wrapper to combinate SQL clauses more easily.
I decided to try how it would work out for the module i'm currently working on.

An example for usage:

Code: Select all

$sql = new SQLGenerator();

$sql->select = array("u.id", "p.name");
$sql->join = array("LEFT JOIN ".cms_db_prefix()."user_location AS l ON u.id = l.user");
$sql->where = array("l.type = 1");
$sql->limit = 30;

print_r( $sql->UserQuery() ); //when called will return (only) a formatted SQL query

I used a part of this code on the top of an action.default.php module page to set the standard basis SQL code.

What makes it special is demonstrated in the following example:

Code: Select all

if(!empty($params["imageonly"]))
{
	$sql->from[] = "cms_module_uploads_categories AS c";
	$sql->from[] = "cms_module_uploads AS up";
	$sql->where[] = "u.id = c.upload_category_name";
	$sql->where[] = "c.upload_category_id = up.upload_category_id";
	$sql->groupby[] = "up.upload_category_id";
}

If this code is added before the UserQuery() function, those SQL clause values will be added to the basic SQL.

This creates a flexible whay to deal with parameters and SQL.

Here is a draft class that contains the two functions:

[The extension txt has been deactivated and can no longer be displayed.]

There could be even more functionalities when this module was worked out providing more (SQL) flexibility, robustness and error prevention.

I would like to know what you readers think of this module/plugin idea.
Would it be a needed and usefull addition?
I haven't found any projects like it..

I would like to repeat that this is just a draft. If developed, ideas and suggestions will help make it better.


Greetings,
Jurrian

[Jurrian91]

Any comments?
If anything is unclear i'd be happy to explain...
Please tell me if this is a usefull addition so I might consider developing it.

[Wishbone]

I think the idea would be useful for using complicated logic and loops to build queries and not wanting to deal with joining elements of the query.. Have you thought about how to deal with complicated AND/OR situations?

[Jurrian91]

I have thought about this problem and I decided to default to AND for now. In some cases I needed a OR and fixed it this way:

Code: Select all

$where[] = "(a = 1 OR b = 2)"

This way the AND is "overruled".
This is probably not the most convenient way and I'm sure there must be an other way but it is not a really big deal.
Subqueries, etc, work the same way.