OpenSUSE Linux Rants

OpenSUSE Linux Tips, tricks, how-tos, opinions, and news

My Resume  -  My LinkedIn Profile

August 31, 2006

Database Connection Class in PHP for your local LAMP stack

by @ 6:03 am. Filed under SUSE Tips & Tricks

Man, school is kicking me in the face. I’m working (at least) full-time, and taking 16 credit hours at school. Dude, who thought that was a good idea?

Well, once upon a time (maybe it didn’t even happen) I seem to remember that I said I was going to post some code for all to have, see, and benefit from (I hope). It is a database connection class that I wrote in PHP and have been using for several years. With how many databases I have worked with in all that time, it’s been robust enough to handle everything I’ve ever needed.

There is a ton of explanation I could do, had I the time. However, it does have a lot of comments in it, so I’ll be somewhat abbreviated here, as I’m super short on time.

First, you’ll need to create a user on your database for use with this class. This is generally done with a command similar to the following:

GRANT ALL PRIVILEGES ON [DATABASE NAME].* to [USERNAME]@[HOST] IDENTIFIED BY ‘[PASSWORD]’ WITH GRANT OPTION

If my db is called ‘customer’, my username is ‘scottmorris’ and I’ll be using ‘testpassword’ as my password (which I would never, ever, ever, ever, ever really do) and the host I’ll be connecting from is 192.168.0.123, my SQL query may look like this:

GRANT ALL PRIVILEGES ON customer.* to scottmorris@192.168.0.123 IDENTIFIED BY ‘testpassword’ WITH GRANT OPTION

Alternatively, you can create users however you normally do, maybe with phpMyAdmin or something.

In any case, create a user that the database class will be using.

Next, you’ll edit the class. Open it up. On lines 59 through 62, you’ll notice a place to put your server, username, password, and database name. Put in the appropriate values. Next, you’ll want to put the class in the include_path (look in /etc/php.ini for the ‘include_path’ directive) to make sure you can use it in your scripts.

You can now use this database connection class in your scripts. You just include( “dbconn.class.php” ); and then just start using it. It does have documentation inside of it on the simple methods of how to use it.

Now for a couple of features that it has. If you insert a new record with it, a member called ‘insert_id’ will contain the new id of the last inserted record. No more screwing around with how to get this. Second, you can do multiple database transactions without having to open and close the database a bunch of times.

It has two modes, which are ‘true’ and ‘false’. If the mode is ‘true’, it will handle opening and closing the database connection automatically when you run the SQL. If it is in ‘false’ mode, you are given control over when to open and close the database connection.

For example, if you had a ‘for’ loop, and you were going to do 1000 inserts, you would definitely NOT want to open and close the database for each insert. What you would do is something like the following:

//CREATE THE OBJECT
$db = new dbConn();

//TELL THE CLASS TO LET US CONTROL THE OPENING AND CLOSING OF THE CONNECTION
$db->mode = false;

//OPEN THE CONNECTION
$db->openConn();

//RUN THROUGH OUR 'FOR' LOOP
for( $x = 0; $x < 1000; $x++ ){

	$sql = "insert into [whatever your query is]";
	$db->execute( $sql );
	
}

//CLOSE THE CONNECTION
$db->closeConn();

//TELL THE CLASS TO RESUME CONTROL OF AUTOMATICALLY OPENING AND CLOSING CONNECTIONS
$db->mode = true;

Obviously, you’d have your query there set up to do whatever you needed done. It may be that you are looping through some kind of array to do inserts or something. But that way, you could do all your inserts without beating the living snot out of your database server.

I sincerely apologize for the horrible documentation/instructions I’m providing. However, ya’ll are smart cookies. Besides, if you have questions, you can always leave them to me as comments, and I’ll answer them so that everyone can benefit who is interested.

Also, with school, I may only be able to make an appearance here once or twice a week, unfortunately.

Heh, before I forget, here’s a link to the database connection management class. Uncompress it with this command:

tar -jxvf dbconn.class.php.tar.bz2

Please do let me know if you have questions, problems, comments, or whatever. My hope is that this class will make someone’s life easier.

4 Responses to “Database Connection Class in PHP for your local LAMP stack”

  1. Steve Says:

    Brilliant. Wish i had your
    enthusiasm and patience.

  2. Scott Morris Says:

    🙂 Thx.

  3. Francisco Says:

    I stumbled upon your dbConnection class, it’s super helpful. I’ve made some minor changes – I added two methods, one for returning a dataset as XML, the other for returning it as JSON. Quick and dirty, but it works.

    Thanks a lot for this post, I was looking to abstract a database connection.

    function selectXML($sqlquery)
    {
    //If we are in ‘normal'(true) mode, go ahead and create the connection.
    //If we are in ‘manual'(false) mode, do not create the connection.
    if($this->mode){
    $this->openConn();
    }
    //Run the query and store the results
    $result=mysql_query($sqlquery);

    // Get table name to which field[0] points to
    $tableName = mysql_field_table($result, 0);

    // Perform query to retrieve field names (required for returning XML)
    $fieldQuery = “SHOW COLUMNS FROM ” . $tableName;
    $fieldsResult = mysql_query($fieldQuery) or die(mysql_error());

    // Get number of fields in result
    $fieldsNo = mysql_num_rows($fieldsResult);

    //We don’t always want to close the database after a query.
    //In the case of for loops, we will close the connection after
    //the loop has completed.
    if($this->mode){
    //Close the database
    mysql_close($this->dbconn);
    }

    //Retrieve the number of rows in the result
    $this->numrows=mysql_num_rows($result);

    $returndata = ”.”\n”;
    if($this->numrows > 0){

    //Construct the XML representation of the data
    for($x=0;$xnumrows;$x++)
    {

    $returndata .= “\t\n”;
    for($y=0;$y” . mysql_result($result, $x, $fieldName) . “\n”;

    }
    $returndata .= “\t\n”;

    }

    }

    // return the dataset
    $returndata .= ”;
    return $returndata;
    }

    function selectJSON($sqlquery)
    {
    //If we are in ‘normal'(true) mode, go ahead and create the connection.
    //If we are in ‘manual'(false) mode, do not create the connection.
    if($this->mode){
    $this->openConn();
    }
    //Run the query and store the results
    $result=mysql_query($sqlquery);

    // Get table name to which field[0] points to
    $tableName = mysql_field_table($result, 0);

    // Perform query to retrieve field names (required for returning JSON)
    $fieldQuery = “SHOW COLUMNS FROM ” . $tableName;
    $fieldsResult = mysql_query($fieldQuery) or die(mysql_error());

    // Get number of fields in result
    $fieldsNo = mysql_num_rows($fieldsResult);

    //We don’t always want to close the database after a query.
    //In the case of for loops, we will close the connection after
    //the loop has completed.
    if($this->mode){
    //Close the database
    mysql_close($this->dbconn);
    }

    //Retrieve the number of rows in the result
    $this->numrows=mysql_num_rows($result);

    $returndata = ”;
    if($this->numrows > 0){

    //Construct the JSON representation of the data

    for($x=0;$xnumrows;$x++)
    {
    $returndata .= ‘{‘.”\n”;
    for($y=0;$y

  4. Francisco Says:

    Oh well, seems that comment didn’t post allright. I’ll upload it to my server and post the link later.

    Cheers!

    FGB.

OpenSUSE Linux Rants
Official OpenSUSE Linux Site

internal links:

categories:

SUSE Resources

search blog:

archives:

March 2022
S M T W T F S
« Feb    
 12345
6789101112
13141516171819
20212223242526
2728293031  

63 queries. 0.820 seconds