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.

OpenSUSE Linux Rants
Official OpenSUSE Linux Site

internal links:

categories:

SUSE Resources

search blog:

archives:

August 2006
S M T W T F S
« Jul   Sep »
 12345
6789101112
13141516171819
20212223242526
2728293031  

58 queries. 0.435 seconds