Connecting to the DB best practices

This is kind of a general question, and I'm sure that there are plenty of ways that people do this, but I wanted to know if what I do is the best way, or if there is room for improvement.

Typically when I write a database driven site, I create a header.php and a footer.php that I include on each page. I then create a mysql_connect statement in the header, and a mysql_close in the footer.

Is this the typical way to do it?
Are there drawbacks to this method?
Are there some better options?
Are there any security risks with this method?

 

I don't want to get too in depth here, but you may want to look at using more MVC style programming. Database and other processor intensive logic occurs outside of files that contain html or user output. This helps to keep code more segmented.

 

I think the best way is to create a database class with functions to call your mysql.
The first result from google gave me a sample database class which you can base your code off it, or just use it. Its up to you.

http://slaout.linux62.org/php/index.html

 

use mysql_pconnect instead of connect, would be better for heavy servers

 

Use the mysqli instead, you then don't have to write your own oo wrapper class.

 

Hello friends, please suggest me easy and best MVC framework.
I have never used any MVC. This will be my first time So suggest as newbie....

 

Thanks for all of the suggestions. I do need to learn more about MVC architecture. Do you have any suggestions on how best to learn more how the MVC style of programming works (links, books, etc.)?

I really like the idea of that class to call all of my SQL. I'll give that a look.

 

Try the zend framework, it is mvc based.

 

I use functions to connect to database, execute a query, and close the connection immediately. I have found this way to work faster than opening a connection at top and closing at bottom. I posted a thread here somewhere with exact times it took to run 30 queries. Opening a connection at query execution worked much faster for some reason.
You can use a php class, but I find regular functions easier to work with.
Thanks

 

Here's the thread: http://forums.digitalpoint.com/showthread.php?t=1671208
All 30 queries were different, and also included insert, select, update etc, and mysql cache was deleted each time mysql_free_result()
Thanks

 

JEET, could you post an example of connecting using functions.

I'm assuming you do something like this:

<?php
function getUserInfo($id){

  dbConnect();
  $qUserInfo = mysql_query("...Whatever...");
  dbClose();

  return $qUserInfo;

}

?>

PHP:

Is that what you are talking about?

 

Yes, you got me right, something like that, but not writing a seperate function for each query, but a function to execute a mysql query like:

function exec_query($q){
db_connect(); mysql_query($q); db_close();
}

$sql= exec_query(" insert into table values() ");

Code (markup):

Thanks

 

I agree. If you have a class, you have a more centralized system that can be used on multiple websites. You can do things like put SQL injection prevention in your functions and other security updates... just a couple ideas.

 

Not only this but it also allows users to visit your site if your sql server happens to be lagging for whatever reason. It's much better to show at least something on the site rather than have a blank white screen just sitting there if your sql server is stalling.

 

This is generally a bad idea. mysql_pconnect is rarely more efficient than mysql_connect, and mysql_pconnect can cause a pile of problems.