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(arr ay('field1','fi eld2','fieldn') ,array('table1' ,'tablen'),arra y('left
join,idy','inne r 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]

Re: simplify insert-update-delete from database

Bob Bedford wrote:[color=blue]
> 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:
>
>[/color]
selectdatas(arr ay('field1','fi eld2','fieldn') ,array('table1' ,'tablen'),arra y
('left[color=blue]
> join,idy','inne r 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.[/color]

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]

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.

Bob

[Rik]

Re: simplify insert-update-delete from database

Bob Bedford wrote:[color=blue]
> 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.[/color]

Yup, terribly simplified for instance:

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

function __construct($ho st, $database,$user ='root',$pass=' '){
$this->database($host ,$database,$use r,$pass);
}

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

function print_errors($b ool=true){
$this->print_errors = $bool;
}
function log_queries($qu ery, $result='',$err or=''){
$log = array('query' => $query, 'result'=>$resu lt,'errors'=$er ror);
$this->queries = $log;
if($this->print_errors && $error!=''){
print("ERROR:\n query:$query\nM ySQL said:$error");
}
}
function debug(){
print_r(end($th is->queries));
}
function debug_all(){
print_r($this->queries);
}
function select_query($q uery,$index=fal se){
$result = @mysql_query($q uery, $this->connection);
if(@mysql_error ()){
$this->log_queries($q uery,'',mysql_e rror());
return false;
}
if(@mysql_num_r ows($result) > 0){
$return_array = array();
while($row = @mysql_fetch_as soc($result)){
if($index){
$return_array[$row[$index]] = $row;
} else {
$return_array[] = $row;
}
}
$this->log_queries($q uery,$return_ar ray,'');
return $return_array;
} else {
$this->log_queries($q uery,'','');
return 0;
}
}
}

Now you you can use:

/* initialize object */
$db = new database('host' ,'database','us er','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===fa lse) $db->debug();

/* working with the code */
if(is_array($re sult)){
//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