MySQL generic entry method?

Neko_D

So I am wondering if there is a way for me to generically enter values into a database. This is only for INSERTS UPDATES and DELETEs. I want to write a functions that build queries for me. But I can only build the first part with my current knowledge, I can only build "INSERT INTO table(col1, col2, col3)" or "UPDATE table SET ". I want to be able to write the entire query without having to worry about types. Is there a way to do that in PHP or MySQL?

bretticus

Certainly, one of the very nice features of MySQL is the ability to send every value wrapped in quotes. For example, if you have a int type field, you can set a value of '1', for example, and MySQL converts it to an integer. This makes looping through an array of key/value pairs and dynamically writing SQL possible. Here's an example of how you might go about this.

<?php
class DataAutomator
{
    public $pairs = array();
    public $sql = '';
    function __construct(){
        /*** code to connect and initialize data connection goes here ***/
    }
    function makepair($key, $value){
        $this->pairs[$key]=mysql_real_escape_string($value);
    }
    function fixupquery($table, $update=false){
        $this->sql .= "INSERT INTO `$table` (`";
        $this->sql .= implode("`,`", array_keys($this->pairs));
        $this->sql .= "`) VALUES ('";
        $this->sql .= implode("','", $this->pairs);
        $this->sql .= "')";
        if ($update) {
            $count = 0;
        	$this->sql .= " ON DUPLICATE KEY UPDATE ";
            foreach ($this->pairs as $key=>$value) {
                $this->sql .= "`$key` = '$value'";
                $this->sql .= ( ++$count < count($this->pairs) ) ? ", " : "";
            }
        }
    }
    function exec() {
        /*** You implement this part. ***/
    }
}

$d = new DataAutomator();
$d->makepair("column_name1", "some value");
$d->makepair("column_name2", 3);
/*** ...and so on... ***/
$d->fixupquery("table_name");
$d->exec();
?>

ehsanullah

:mad:

bretticus wrote:

<?php
class DataAutomator
{
    public $pairs = array();
    public $sql = '';
    function __construct(){
        /*** code to connect and initialize data connection goes here ***/
    }
    function makepair($key, $value){
        $this->pairs[$key]=mysql_real_escape_string($value);
    }
    function fixupquery($table, $update=false){
        $this->sql .= "INSERT INTO `$table` (`";
        $this->sql .= implode("`,`", array_keys($this->pairs));
        $this->sql .= "`) VALUES ('";
        $this->sql .= implode("','", $this->pairs);
        $this->sql .= "')";
        if ($update) {
            $count = 0;
        	$this->sql .= " ON DUPLICATE KEY UPDATE ";
            foreach ($this->pairs as $key=>$value) {
                $this->sql .= "`$key` = '$value'";
                $this->sql .= ( ++$count < count($this->pairs) ) ? ", " : "";
            }
        }
    }
    function exec() {
        /*** You implement this part. ***/
    }
}

$d = new DataAutomator();
$d->makepair("column_name1", "some value");
$d->makepair("column_name2", 3);
/*** ...and so on... ***/
$d->fixupquery("table_name");
$d->exec();
?>

Roger_Ramjet

Might I also suggest that you start using PDO for your database interface. Just using prepared statements will take care of this for you.

Neko_D

@: Oh you CAN send over integers and floats like '4' or '5.333' and MySQL will just parse out the quotes? Sweet.

@ RamjetHmmm looks interesting, does not seem to be doing much much less for me. I will look into it.

Roger_Ramjet

I wish I had started using PDO much sooner myself.

Wondered about the utility of prepared statements as few queries are called multiple times in a script, but then I found out that it takes care of escaping for sql injection attacks etc, which I used to handle myself.

Simplifies coding no end, now I have real utility classes that accept diverse query parameters and return results all with 4 lines of simple code in the calling page - and mostly it is only the $params that change so pretty soon that will be 1 line of code calling a function with the params.

bretticus

Roger Ramjet wrote:
I wish I had started using PDO much sooner myself.

Wondered about the utility of prepared statements as few queries are called multiple times in a script, but then I found out that it takes care of escaping for sql injection attacks etc, which I used to handle myself.

Simplifies coding no end, now I have real utility classes that accept diverse query parameters and return results all with 4 lines of simple code in the calling page - and mostly it is only the $params that change so pretty soon that will be 1 line of code calling a function with the params.

I'm stuck in Debian Sarge "stable" land. I was aware of PDO, but have yet to use it in a live production environment. The modularity and transactions alone definitely make it very attractive. before PHP5 (and some years ago yet) I created my own database abstraction layer (no, not the sample code, I wrote that off the top of my head in about 3 minutes) but after seeing the docs, PDO pretty much would have done exactly what I wanted my own abstraction class to do. Better yet, it will be an ongoing feature that is supported as database technology advances.

I'm going to be doing a ticketing system on an offsite server with MySQL5 and PHP5. I can see I need to use PDO, thanks for the tip Roger Ramjet.

Peace!

P.S. PDO seems to be modeled after DBI in perl perhaps?

bretticus

BTW...did my code sample make you angry?

Roger_Ramjet

It also supports many different dbs so your code can be ported from one backend to another with only the minimum of modification, usually to the sql as no one uses the same dialect. If you can do it all with named views and stored procs then even that modification disappears.

bretticus

Roger Ramjet wrote:
It also supports many different dbs so your code can be ported from one backend to another with only the minimum of modification, usually to the sql as no one uses the same dialect. If you can do it all with named views and stored procs then even that modification disappears.

Yeah, I actually wrote my custom data class with that in mind. In other words, by switching to another RDBMS, the class would handle the "dialect" issues. Problem is, we have only ever used MySQL, and the code for other RDBMS never was implemented 😃

Neko_D

PDO reminds me of writing stuff in Perl. I would REALLY like to use something like that. However, it is not 100% up to me. It is up to the school really. I will ask around and see if it is used by the guy who will be keeping this program maintained while I am gone.

Then I have to figure out how to get it on Ubuntu

bretticus

Neko_D wrote:
PDO reminds me of writing stuff in Perl. I would REALLY like to use something like that. However, it is not 100% up to me. It is up to the school really. I will ask around and see if it is used by the guy who will be keeping this program maintained while I am gone.

There's really no "generic" query building class that I know of. Perhaps a google query or two will find something for you to use, My sample code however, should provide idea about how you might write your own.

Neko_D wrote:
Then I have to figure out how to get it on Ubuntu

Ubuntu same as debian....

$sudo apt-get update
$sudo apt-get install libapache2-mod-php5 php5 php5-mysql

...ah Ubuntu (my desktop as we speak.)

Roger_Ramjet

Using PDO should make your code more maintainable as it is a well documented and maintained db interface which many programmers will already know; while an abstraction interface that you write yourself will have none of those advantages, no matter how well written.

So if the other guy objects, go over his head and explain the advantages to his boss. The learning curve is really easy and in reality it is only a habit of notation that you and he will soon get used to, just as you did with mysql_query etc.

bretticus

Roger Ramjet wrote:
while an abstraction interface that you write yourself will have none of those advantages, no matter how well written.

Please keep in mind, I'm not suggesting that your home-brewed database abstraction layer take the place of PDO (which I do think is a great idea.) But you might use similar code to my sample code to build queries that implement PDO over RDBMS. In other words, a further abstraction for the level of "generic-ness" you are obviously after.

Roger_Ramjet

Quite right, Bretticus, sorry if it sounded as if I were saying otherwise.

Certainly one should wrap pdo in an abstraction layer both for db connection and query generation/execution.

Now I am going to move this thread in another direction - object-relational mapping. The nice thing about this is that tools exist to do the work for you php object generator. Create an object for every table that you need to work with and use it for insert/update/delete. If you think this is overkill then ok, but the code generated can give you a lot of tips

bretticus

Actually, one of the things that impresses me when I use Visual Studio for ASP.NET is how VS does basically the same thing. In other words you have strongly typed properties for each table as an object. Pretty nifty. Again Roger Ramjet, thanks for the tip.

Neko_D

@: I was talking about getting PDO on my ubunutu not PHP

bretticus

Neko_D wrote:
@: I was talking about getting PDO on my ubunutu not PHP

Ah....my bad.

Any luck?

I tried it on my ubuntu dapper. This article was very helpful http://bhiv.com/getting-pdo-to-work-with-ubuntus-606-lts-dapper-drake/

I had to install...

$ apt-get install php5-dev php-pear

to use pecl.