Smarty

[ymo]

Hi,
I thought that in database driven system,
If I've seperated SQL from php code, it may more easy to maintanance.
Like this,

[messju]

have a look at monte's SafeSQL:
http://www.phpinsider.com/php/code/SafeSQL/

if it suit's your needs, you only need a framework to obtain the first parameter (the query with the parts to be replaced in %...-notation) from outside your php-code and pass it to SafeSQL::query() with your params.

just an idea

[ymo]

I don't use yet, but I read source... Good!
My needs would be satisfied by SafeSQL.
I try SafeSQL.

Thanks.

[andre]

I have created my own SQL Query builder for such stuff.

[Tom Sommer]

I got my own DB class...

I'm don't see _ANY_ need to have a function write your SQL query, it's a rather simple syntax and seems rather newbie-like to me

$db->connect();
$db->query("SELECT * FROM table");

while ( $rs = $db->fetch_array() )
{

}
etc. etc.

[Wom.bat]

well... it would really be interesting to write a completely object-oriented system to build queries, so that you are independent from the relational database... you do things like $query->addWhereCondition(...) etc, without having one single line of SQL code
unfortunately you cannot use "specialities" (transactions, foreign keys, views, stored procedures; I call them "specialities" because I have to start from MySQL as the lowest common denominator) or rdms-specific functions (e.g. UNIX_TIMESTAMP(), DATE_FORMAT() etc.) with this approach, so I discarded the thought

[ymo]

Interesting code. Thank you.

At present, I feel that template based aproach may good for SELECT clause.
INSERT,UPDATE,DELTE clause may handy andre's code like aproach.

I thought that SELECT clause are more complex than INSERT,DELETE,UPDATE clause. (As I use trigger and stored procedure to change data.)
And almost of SELECT clause has only a little differences depends on user's operation or user's permission etc.
So build SELECT clause by Smarty seems to good idea for me.

Hmmm, I can't say well...
Sorry, my english ability is not enough.　

[Wom.bat]

err... don't build sql queries using smarty, and don't use direct user input etc...
you should always do that in your php code

[ymo]

[boots]

I think its funny that people suggest an "object oriented framework" for SQL queries (SQL is a LANGUAGE, not a data model!) but don't think that SQL queries should be written using the template language. Perhaps you mean that template code intended for output should not be intermixed with application logic -- True. But writing a template to format queries to be sent a query processor isn't wrong in and of itself IMHO it is less wrong than trying to convert SQL's lovely language into a bunch of layered classes, arrays and function calls.

Think about it: Smarty is a template system--it is useful for templating the syntaxes of other languages, like, er, HTML. Also useful for other formats as long as you practice separation techniques.

At least safeSQL doesn't go to far and just provides some reasonable tools without too much framework.

[eadz]

I use ez_sql. http://php.justinvincent.com/ez_sql_help.html

so.. simple. It can get arrarys, rows, colums, and vars.

e.g.

$Smarty->assign("users",$db->get_results("select * from users"));

or

$Smarty->assign("title",$db->get_var("select title from config"));


.. of course you could write the above as 2 lines.

[Wom.bat]

ez_sql is just a simple database abstraction layer...
I'm still dreaming of complete rdbms-independency...

[JonBoy]

...don't try to encapsulate the whole SQL language. Instead, try to encapsulate "things" such as a "user" or a "forum".

In the process of designing your object world, you will find you will need to populate your objects with information from the DB both from "list" queries (e.g. "SELECT * FROM Users WHERE Username LIKE 'G%'" and from "single" queries (e.g. "SELECT * FROM Users WHERE Username='George'" ) Your thing-objects will be able to "self-populate" with an internal single SQL query, but you will also need to expose an interface on your objects to allow your objects to be populated from, say, row 25 of your "list query."

Taking all that into account...your "next level" up will be functions like "GetUsersStartingWithLetter()" which returns an array of User objects. If you can code things up that way, you will never have to use "global SQL" again because your text-based SQL commands are buried in your thing-objects and the functions above which generate arrays of thing-objects.

(In other words, if your intent is simply to dodge "global" text-based SQL queries, get ready to build a s***load of objects.)

The other reason I see for encapsulating SQL queries is that many databases use slightly different syntax, especially when dealing with things like DATES. Encapsulating SQL in this scenario would allow you to look for items from the past 30 days in an Access DB and a MySQL database without regard for the type of database. However, someone still has to write the "low level" DB-specific date-formatting code, and I've found that it's just as useful to have a global "FormatDateForDB()" function which uses a global "database type" variable (set this at the same time you set DB hostname, etc.) to format your date for you.

Finally, there's the issue of string concatenation. Unless you are doing really cool things with buffers on the fly (like .NET's StringBuilder class), string concatenation is often SLOW because there's often lots of "memory reallocation" monkey business going on behind the scenes. In an object which builds up a string, you may be assembling 15 different SQL fragments when you could have done the same thing "by hand" with only 3 SQL fragments, so the object is bound to be slower. (Think of a SQL statement with multiple WHERE clauses and a theoretical SQL object with an array of WHERE clauses hanging off of it.)

Erg...that's all for now...

[ymo]

[BloodRath]

IMHO

i work as andre explain for db access to my component i use
ideas from xoops and xaraya (CMS) and one php article on mysql and OO

i use two object : one which describe field and their params (constructor) , and his methods give me accessor to fields
and a handler object which give me index, relations and table name (through constructor) and ability (through methods) to create,insert, delete ...

for compatibility i use adodb
that's wonderful

i think sometimes u need to insert sql in template but in those case template system become "clone of a description langage" like in SPIP i ve tryout longtime ago to adapt it to smarty, in this project, skeletons give ability to people to select and organize information on html pages

blood
_________________
froggies forever....