SiteCrafting Blah Blah Blog
Oct. 13, 2006 at 10:59am
A Piece of Query Cake
Making Inserts Sucks
If you've ever created SQL queries with PHP, you probably know what a pain it can be to create insert and update statements. I really, really (really) don't like it. As I was working on my personal site, and exploring possible frameworks to use, I came across CodeIgniter. They have a great database interaction library, especially the function for creating the insert queries.
Today, armed with only the descriptions of CodeIgniter's query helper functions, I spent 20 minutes trying to duplicate some the effect of the insert and update functions. I've never seen the code, or even used it, but I didn't have to see the code to write a similar function. Both functions take a table name and an associative array of column names and values. The update function also requires a WHERE statement, and it can't be blank. This is different from CodeIgniter, and that's so you don't accidentally reset all of the passwords in the mysql users table, or any table for that matter. And then, *poof*, the function gives you a nice sql statement.
I'll never have to write another "INSERT blah blah blah" again. Yay!
<?php
/**
* Generates an insert sql query from the parameters
*
* @param string $table The name of the table
* @param array $array An associative array with the values similar to column=>value
* @return string The sql query
*/
function getInsertSQL($table, $array) {
$sql = 'INSERT INTO '.$table;
$columns = '';
$values = '';
foreach($array as $key => $value) {
$columns .= $key.', ';
if($value != "") {
$values .= "'".addslashes($value)."', ";
}
else {
$values .= "'NULL', ";
}
}
$columns = substr($columns, 0, -2);
$values = substr($values, 0, -2);
$sql .= "($columns) VALUES ($values)";
return $sql;
}
/**
* Generates an update sql query from the parameters
*
* @param string $table The name of the table
* @param array $array An associative array with the values similar to column=>value
* @param string $where What to limit the update to. Cannot be blank.
* @return string The sql query
*/
function getUpdateSQL($table, $array, $where) {
if(trim($where) == "") {
return;
}
$sql = 'UPDATE '.$table.' SET ';
foreach($array as $key => $value) {
if($value != "") {
$sql .= $key."='".addslashes($value)."', ";
}
else {
$sql .= $key."='NULL', ";
}
}
$sql = substr($sql, 0, -2);
$sql .= " WHERE $where";
return $sql;
}
?>
Posted in Coding Techniques, MySQL, PHP by Dave Poole
Comments (2)
CodeIgnitor is good (except that I think it only is PHP4), but one that I think is pretty cool is CakePHP. Having learned Ruby on Rails I found this to be pretty close to Rails and gives you the ability to very quickly create a web app. Another one I'm sure you have already heard of is Symfony. I haven't had much experience with that framework, it looks pretty cool and I have heard a lot of great things about it.
I think that any time you work off of a solid framework that utilizes an MVC principles then it helps by quite a bit, especially if that MVC framework has a data managment layer (sorry I've been coding too much so my terms are not spot on, but I'm sure you get what I'm saying). In Rails it's called ActiveRecord or at least that is the class that handles all of that. I will tell you this, once you work with ActiveRecord, or something *very* close to it you will *never* want to work with queries by hand. LOL!
Cheers!
Tony
1 | Left by Tony Bianco | Oct. 11, 2007 at 10:10pm
CodeIgniter will work with either PHP4 or PHP5. I've used both CodeIgniter and Symfony and I'd have to say I've been much more impressed by Symfony, although Symfony is PHP5 only. Symfony uses the Propel object-relational model tool which is much more robust than CodeIgniter's SQL helpers. I agree that, after working with something like that, you'll never want to go back. Depending on your needs, CodeIgniter may be a better choice over Symfony as it is much more lightweight.
2 | Left by Jason | Oct. 19, 2007 at 10:00am