Perfect Database Connection

Been using the same code all over, want to make sure it's right

[bateman_ap]

I have started on PHP from classic ASP, the leap to .net was jsut too much for my non-programming brain to handle.

I have made a few sites and it's all going well, however I remember when I learnt ASP I began by using a terrible connection method to the database and only realised a couple of years later, changing it to a better method speeded up my pages no end.

So I wonder if you could look what I am doing at the moment in PHP and tell me if it's OK or could be made better?

I currently use a include file, ie connect.php, that contains my database connection and connection string:

<?
/*--------- DATABASE CONNECTION INFO---------*/
$hostname="localhost";
$mysql_login="username";
$mysql_password="password";
$database="db_test";
// connect to the database server
if (!($db = mysql_connect($hostname, $mysql_login , $mysql_password))){
 die("Can't connect to database server."); 
}else{
 // select a database
 if (!(mysql_select_db("$database",$db))){
  die("Can't connect to database.");
 }
}
?>

I then in my code would do something like this:

$sql = "SELECT tbl_test.fld_test FROM tbl_test";
$result= mysql_query($sql) or die(mysql_error());
while ($sqlrow= mysql_fetch_assoc($result)) {
...do my thing
}

One prob obvious thing is that I still use the above loop even when I know I'm only getting back one result.

Many thanks for any help

[jatar_k]

well, I think the added if statements are unnecessary, within the code above, it could just be

$db = mysql_connect($hostname, $mysql_login , $mysql_password) or die("Can't connect to database server.");
// select a database
mysql_select_db("$database",$db) or die("Can't connect to database.");

it does the same thing, I doubt you would ever notice a speed difference abd the other model might be useful if the error handling was more complex.

I also must mention, though I am sure you know, to store your db login credentials in a separate file and outside of the web root if at all possible

your query extraction code looks standard, if you know you are getting a single result you could use mysql_result or an if statement or num_rows to be sure, then whichever you like

I also must mention that we don't leave 'or die' code in live/production scripts, you need to handle the errors nicely

[andrewsmd]

A big benefit to storing your db connection info in a outside file is for changes later on. Say you make a site with 100 pages and you put the connection information in every page, then you migrate to a new database. Now you have to go change it in every single page, where if you store it in one file you can just change it there and viola! For the production issue jatar_k mentioned, since you already have the if statements in there, just redirect to a default error page.

On a different note, I'm surprised you did classic asp but couldn't handle the move to .net. Coming from someone who has done both, php is a lot more coding intensive than .net. Asp.net has all of the fancy gui based interfaces to code with. I personally like PHP better, but that is because I fell more in control because of the coding requirements.

[bateman_ap]

Thanks for the replys. Yeah I had my connection info in a seperate file, learnt that trick many years ago when having to change a load of classic asp pages!

I think the GUI interfaces are the ones I can't get to grips with, I think if someone just showed me how to do a select, loop through some results and format them how I want to in .net I would have been OK, it kept wanting to put them in stupid datagrids and suchlike, I just found php to be closer to what I was used to.

[andrewsmd]

I suppose I can see your point. I just like php better because you don't spend so much darn time deploying a website. Plus working on existing sites in asp that are a couple years old is a nightmare. With php, if you have the file, you can edit it. Also, I have not seen this performance increase that asp.net people boast about. Once a site is "compiled" to me it seems that that's the only time it runs as fast a well developed php site. Once you make a change, you have to recompile and then it has to build itself on the first page load which takes forever.