simplify insert-update-delete from database

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View

I'm looking for some functions or objects allowing to  
select-insert-update-delete from any table in a mysql database without the  
need to create a new query every time. Example:

join,idy','inner join, idx'))

then the function build the query, execute it and then return an object with  
the query result.

I must build a database driven site that is 90% of the time spent on  
building queries. If I may avoid build them manually it will help me a lot  
and let me gain some days of programming.


Re: simplify insert-update-delete from database

Bob Bedford wrote:
Quoted text here. Click to load it
Quoted text here. Click to load it

I've tried that in the past. My conclusion was that in most cases, if you
want your queries to be flexible, use joins, where-clauses, etc, you end up
with exactly the same amount or work in writing the queries. Think about it:
if queries could be shorter, the SQL would be shorter. The advantage you do
get is an extra check wether a certain query is allowed to the specific
user, and you can control some of the output.

For a certain project specifically you could always try to figure out which
queries are used several times, and make a template for them. It won't save
you much coding time in my opinion though, but it will make your code more

Now I have a database object, that:
- takes car of connecting.
- saves querystrings and errors for debugging purposes.
- on selects returns a complete associative array, with (if existing) the
primary key as main key, 0 if no rows match, false if there's an error in
the query.
- on insert returns mysql_insert_id() (or array of insert_id()'s or false on
- on updates and deletes returns the number of rows affected, or false on

One thing that's usefull here that the object calls a certain
database-connection specifically, so it's easier when working with more than
one database in a script.

That's about as much as I can gain from it without inventing my own database
syntax. I'm no genius, and don't think I personally can replace SQL with a
better alternative :-).

Rik Wasmus

Re: simplify insert-update-delete from database

Hello Rik, thanks for advice.

In fact SQL is a language that allow to do a lot of different queries. If  
the only think it may do is select-insert-update-delete I think it would be  
simpler. I'll try or have a look around.

In any case, I've seen on some open-source project that some code to get the  
result from a query is one line, as many times the code would be bigger (at  
least 2 lines without caring about error handling). I'll start to simplify  
this then try to get further.

Thanks again.


Re: simplify insert-update-delete from database

Bob Bedford wrote:
Quoted text here. Click to load it

Yup, terribly simplified for instance:

class database{
    var $queries = array();
    var $errors = array();
    var $connection;
    var $print_errors;

    function __construct($host, $database,$user='root',$pass=''){

    function database($host,$database,$user,$pass){
        if($connection = @mysql_connect($host,$user,$pass){
            $this->connection = $connection;
            if($database !=''){
                    $this->errors[] = 'Could not select database';
        } else {
            $this->errors[] = 'Could not connect to server';

    function print_errors($bool=true){
        $this->print_errors = $bool;
    function log_queries($query, $result='',$error=''){
        $log = array('query' => $query, 'result'=>$result,'errors'=$error);
        $this->queries = $log;
        if($this->print_errors && $error!=''){
            print("ERROR:\nquery:$query\nMySQL said:$error");
    function debug(){
    function debug_all(){
    function select_query($query,$index=false){
            return false;
        if(@mysql_num_rows($result) > 0){
            $return_array = array();
            while($row = @mysql_fetch_assoc($result)){
                    $return_array[$row[$index]] = $row;
                } else {
                    $return_array[] = $row;
            return $return_array;
        } else {
            return 0;

Now you you can use:

/* initialize object */
$db = new database('host','database','user','pass');

/* option debugging: print all errors, usefull in building fase
this can be turned of by giving false as argument */

/* run query */
$result = $db->select_query('SELECT list, of, fields FROM table',

/* optional debugging per query */
if($result===false) $db->debug();

/* working with the code */
    //do stuff with it

I have never used it but maybe mysqli is something for you:

Rik Wasmus

Site Timeline