|
Smarty
WARNING: All discussion is moving to https://reddit.com/r/smarty, please go there! This forum will be closing soon. |
|
View previous topic :: View next topic |
Author |
Message |
ymo Smarty Rookie
Joined: 03 May 2003 Posts: 14 Location: Tokyo
|
Posted: Mon May 05, 2003 2:12 am Post subject: Seperate SQL from PHP code |
|
|
Hi,
I thought that in database driven system,
If I've seperated SQL from php code, it may more easy to maintanance.
Like this,
Code: | -- customers.sql --
SELECT *
FROM customers
WHERE customer_id = {$params.customer_id}
-- customers.php --
<?php
function GetSQL($tpl_file, $params) {
$tpl = new Smarty();
$tpl->assign('params', $params);
return $tpl->fetch($tpl_file);
}
$sql = GetSQL('customers.sql',array('customer_id' => 10));
$result = $db->query($sql);
...
?>
|
To begin with, this idea is reasonable?
Do you have another good way?
Thanks. |
|
Back to top |
|
messju Administrator
Joined: 16 Apr 2003 Posts: 3336 Location: Oldenburg, Germany
|
Posted: Mon May 05, 2003 8:15 am Post subject: |
|
|
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 |
|
Back to top |
|
ymo Smarty Rookie
Joined: 03 May 2003 Posts: 14 Location: Tokyo
|
Posted: Mon May 05, 2003 9:00 am Post subject: |
|
|
I don't use yet, but I read source... Good!
My needs would be satisfied by SafeSQL.
I try SafeSQL.
Thanks. |
|
Back to top |
|
andre Smarty Pro
Joined: 23 Apr 2003 Posts: 164 Location: Karlsruhe, Germany
|
Posted: Mon May 05, 2003 12:06 pm Post subject: |
|
|
I have created my own SQL Query builder for such stuff.
Code: |
/**
* Builds an SQL query string
*
* Very basic example:
* <code>
* $db =& ngDB::getConnection();
* $query = $db->buildQuery("MyTable"); // SELECT * FROM MyTable
* </code>
*
* A bit more complex:
* <code>
* $db =& ngDB::getConnection();
* $query = $db->buildQuery(
* array("MyTable" => "MyAlias"),
* array("col1", "col2", "col3"),
* array("col1 <> col2", "col2 = col3 OR col1 = col3"),
* "col1",
* null,
* 0, 0,
* null
* );
* // SELECT col1, col2, col3 FROM MyTable AS MyAlias
* // WHERE (col1 <> col2) AND (col2 = col3 OR col1 = col3)
* </code>
*
* @param array $dbtable
* @param array $dbcolumns
* @param array $where
* @param array $orderBy
* @param array $groupBy
* @param int $limitMax
* @param int $limitMin
* @param array $joins
*/
function buildQuery ($dbtable, $dbcolumns = null, $where = null, $orderBy = null, $groupBy = null, $limitMax = null, $limitMin = null, $joins = null) {
// [snip]
}
|
EDIT: Additionally there are functions for adding, updating and removing database entries which are also very flexible (array of key column names etc):
Code: |
function loadRecord ($dbtable, $keyColumnName, $key) {
}
function insertRecord ($dbtable, $newRecord, $ignoreErrors = false) {
}
function updateRecord ($dbtable, $newRecord, $keyColumnName, $ignoreErrors = false) {
}
/**
* Update or Insert
*/
function storeRecord ($dbtable, $newRecord, $keyColumnName, $ignoreErrors = false) {
}
function deleteRecord ($dbtable, $keyColumnName, $key) {
}
|
[/code] |
|
Back to top |
|
Tom Sommer Administrator
Joined: 16 Apr 2003 Posts: 47 Location: Denmark
|
Posted: Mon May 05, 2003 12:30 pm Post subject: |
|
|
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. |
|
Back to top |
|
Wom.bat Smarty Pro
Joined: 24 Apr 2003 Posts: 107 Location: Munich, Germany
|
Posted: Mon May 05, 2003 12:44 pm Post subject: |
|
|
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 |
|
Back to top |
|
ymo Smarty Rookie
Joined: 03 May 2003 Posts: 14 Location: Tokyo
|
Posted: Mon May 05, 2003 1:27 pm Post subject: |
|
|
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. |
|
Back to top |
|
Wom.bat Smarty Pro
Joined: 24 Apr 2003 Posts: 107 Location: Munich, Germany
|
Posted: Mon May 05, 2003 1:38 pm Post subject: |
|
|
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 |
|
Back to top |
|
ymo Smarty Rookie
Joined: 03 May 2003 Posts: 14 Location: Tokyo
|
Posted: Mon May 05, 2003 1:54 pm Post subject: |
|
|
Wom.bat wrote: | 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 |
Yes, some kind of check (XSS, sql injection etc.) does in php code.
Anyway I try SafeSQL and some aproach tomorrow.
Thank you.
|
|
Back to top |
|
boots Administrator
Joined: 16 Apr 2003 Posts: 5611 Location: Toronto, Canada
|
Posted: Mon May 05, 2003 2:10 pm Post subject: |
|
|
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. |
|
Back to top |
|
eadz Smarty Regular
Joined: 30 Apr 2003 Posts: 61 Location: Auckland, New Zealand
|
Posted: Mon May 05, 2003 2:19 pm Post subject: |
|
|
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. |
|
Back to top |
|
Wom.bat Smarty Pro
Joined: 24 Apr 2003 Posts: 107 Location: Munich, Germany
|
Posted: Mon May 05, 2003 6:43 pm Post subject: |
|
|
ez_sql is just a simple database abstraction layer...
I'm still dreaming of complete rdbms-independency... |
|
Back to top |
|
JonBoy Smarty Rookie
Joined: 09 Oct 2003 Posts: 6 Location: Madison, Wisconsin, USA
|
Posted: Thu Oct 09, 2003 7:34 pm Post subject: OK, if you really have OBJECTS on the brain... |
|
|
...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... |
|
Back to top |
|
ymo Smarty Rookie
Joined: 03 May 2003 Posts: 14 Location: Tokyo
|
Posted: Mon Nov 24, 2003 4:54 pm Post subject: Re: OK, if you really have OBJECTS on the brain... |
|
|
JonBoy wrote: | 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."
|
Yes, I know that, I think it's depends on what kind of Database is used.
for example,
PostgreSQL: SELECT empno,ename,salary from emp order by salary desc limit 10 offset 0
MySQL: SELECT empno,ename,salary from emp order by salary desc limit 10,0
Oraclle: SELECT empno,ename,salary from (SELECT empno,ename from emp order by salary desc) WHERE rownum <= 10 order by salary
Many DB library does'nt support this kind of problem.
So, I've thought that Smarty is good for sql generation language.
Currently I use SafeSQL and Smarty as SQL generation two Database(PostgreSQL and Oracle9).
And I am Happy.
Other commetnts are difficult for my english ability....
Anyway, Thank you. |
|
Back to top |
|
BloodRath Smarty Rookie
Joined: 06 Jul 2003 Posts: 23 Location: France
|
Posted: Tue Nov 25, 2003 1:27 pm Post subject: |
|
|
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.... |
|
Back to top |
|
|
|
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
|
Powered by phpBB © 2001, 2005 phpBB Group
|
|