simplify insert-update-delete from database

[Bob Bedford]

hello

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:

selectdatas(array('field1','field2','fieldn'),arra y('table1','tablen'),array('left
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.

Bob

[Rik]

Bob Bedford wrote:

hello

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:

selectdatas(array('field1','field2','fieldn'),arra y('table1','tablen'),array
('left 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.

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
readable.

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
error.
- on updates and deletes returns the number of rows affected, or false on
error.

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 :-).

Grtz,
--
Rik Wasmus

[Bob Bedford]

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.

Bob

[Rik]

Bob Bedford wrote:

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.

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=''){
$this->database($host,$database,$user,$pass);
}

function database($host,$database,$user,$pass){
if($connection = @mysql_connect($host,$user,$pass){
$this->connection = $connection;
if($database !=''){
if(!@mysql_select_db($database,$this->connection)){
$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(){
print_r(end($this->queries));
}
function debug_all(){
print_r($this->queries);
}
function select_query($query,$index=false){
$result = @mysql_query($query, $this->connection);
if(@mysql_error()){
$this->log_queries($query,'',mysql_error());
return false;
}
if(@mysql_num_rows($result) > 0){
$return_array = array();
while($row = @mysql_fetch_assoc($result)){
if($index){
$return_array[$row[$index]] = $row;
} else {
$return_array[] = $row;
}
}
$this->log_queries($query,$return_array,'');
return $return_array;
} else {
$this->log_queries($query,'','');
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 */
$db->print_errors();

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

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

/* working with the code */
if(is_array($result)){
//do stuff with it
}

I have never used it but maybe mysqli is something for you:
http://www.php.net/manual/en/ref.mysqli.php

Grtz,
--
Rik Wasmus