|
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 |
Hielke Hoeve Smarty Elite
Joined: 06 Jan 2006 Posts: 406 Location: Netherlands
|
Posted: Sat Jan 28, 2006 11:31 am Post subject: Generic Abstract Database Layer |
|
|
Here is a nice little function that you can use to implement layers for different database servers, MySQL Postgres, MSSQL etc.
It simply allows you to request information from the database without knowing flavors of SQL. All you have to do is provide some basic information like the projection elements and selection elements.
Ofcourse this function isn't very useful to you if you don't intent to make your website code available to others, or when you use only one type of database.
Code: | /*
* all vars must be arrays or NULL
*
* $projection array of elements to become columns
*
* $table array of tables to use in the other vars
*
* $joins array of arrays of 3 elements:
* JOIN TYPE: eg LEFT JOIN, INNER JOIN, RIGHT JOIN
* table to join with: eg table2
* ON CONDITION: eg table1.a = table2.a
* or
* USING CONDITION: eg USING(table1.a, table2.a)
*
* $selection array of arrays of 3 elements:
* ITEM TO BE CHECKED: table1.a
* OPERATOR: eg =, >, <, LIKE, !=
* ITEM OR VALUE TO BE USED IN CHECK: eg '1', 'somestring', table2.a
*
* $group array of items to GROUP on
*
* $order array of items to order on ended by a var that is either DESC or ASC: eg 'table1.a ASC' or 'table1.a, table1.b DESC'
*
* $limit array of 2 items that determine the amount of results:
* eg 1,2 -> return 2 elements starting with the 2nd item: return 2nd item and 3rd item you find.
* eg 0,10 -> return 10 elements starting with the 1st item: return 1st item through 10th item you find.
*
*/
function select_simple_query( $projection, $table, $joins, $selection, $group, $order, $limit ) {
$query = "SELECT ";
$query .= $projection[0];
for($i=1; $i<count($projection); $i++){
if($projection[$i] != "" && $projection[$i] != null)
$query .= ", " . $projection[$i];
}
$query .= " FROM ";
$query .= $table[0];
for($i=1; $i<count($table); $i++){
if($table[$i] != "" && $table[$i] != null)
$query .= ", " . $table[$i];
}
if($joins != NULL){
$query .= " ".$joins[0][0]." ".$joins[0][1]." ON ".$joins[0][2];
for($i=1; $i<count($joins); $i++){
if($joins[$i] != "" && $joins[$i] != null) {
$query .= " ".$joins[$i][0]." ".$joins[$i][1]." ON ".$joins[$i][2];
}
}
}
if($selection != NULL){
$query .= " WHERE ";
$query .= $selection[0][0]." ".$selection[0][1]." ".$selection[0][2];
for($i=1; $i<count($selection); $i++){
if($selection[$i] != "" && $selection[$i] != null) {
$query .= " ".$selection[$i]." ";
$i++;
$query .= $selection[$i][0]." ".$selection[$i][1]." ".$selection[$i][2];
}
}
}
if($group != NULL) {
$query .= " GROUP BY ";
$query .= $group[0];
for($i=1; $i<count($group); $i++){
if($group[$i] != "" && $group[$i] != null)
$group .= " ".$group[$i]." ";
}
}
if($order != NULL) {
$query .= " ORDER BY ";
$query .= $order[0];
for($i=1; $i<(count($order)-1); $i++){
if($order[$i] != "" && $order[$i] != null)
$query .= ", ".$order[$i]." ";
}
$pos = count($order)-1;
$query .= " ".$order[$pos];
}
if($limit != NULL) {
$query .= " LIMIT ".$limit[0].",".$limit[1];
}
$this->debug[] = $query;
$result = mysql_query($query);
$this->numqueries++;
if( mysql_errno == 0 ) {
$results = array();
while($item = mysql_fetch_array($result, MYSQL_ASSOC)) {
$results[] = $item;
}
return $results;
}
else{
echo "There is some mysql error:<br>".mysql_errno().": ".mysql_error()."<br>";
return NULL;
}
} |
|
|
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
|