Posts Tagged ‘databases’

My new course on Doctrine ORM is now available. If you are a PHP developer, adding Doctrine to your CV is a much=sought-after skill to have, being used by Symfony and thousands of other projects.

Here’s the trailer:

If you’re not using PDO in your PHP projects yet, you should be.

I finally got round to taking a proper look at it recently, and I’m really impressed. I managed to convert an entire open source project to it in under two hours! Granted, I was helped a lot by the fact that the PDO naming conventions luckily match up with the naming conventions I used in my original data access layer, but still, it was smoother than I expected.

PDO has some great advantages too:

1. It’s cross platform, so you can use it on a number of different database platforms including MySQL, MSSQL, Oracle, Postgres, SQLite and many others! All you do is enter the protocol and login details to the connection and it handles everything else.

2. It has prepared queries so you never need to escape anything again! You simple pass it some SQL with a series of question marks in, and an array of values those question marks represent and all the SQL injection negation is taken care for you.

3. If something goes wrong, it throws an exception rather than producing an error message.

4. It’s easy to extend, to add your own functionality. Just use Class MyPDO extends PDO and you can add extra functionality on top of it.

All in all, it’s a great addition to PHP and one I now wish I had gotten to sooner.

So you have your shiny new MySQL database your web host has given you and you are already to begin your PHP scripting. If not then you can get an account with all this from www.tripod.lycos.co.uk. Or you could when I wrote this anyway.

For those of you used to connecting to Microsoft Access Databases, like I am, the difference here is that rather than the database being a normal file like a word document or a music file which can be easily opened, etc, MySQL databases are stored on the server. So rather than connecting to a file you connect to the server.

First I am going to jump straight in to the code and then explain it after.

<?php
$db = mysql_connect("localhost", "username", "password");
mysql_select_db("database",$db);
?>

That is a basic connection. Ignoring the php open and close tags, the second link in the code makes the connection to the database of your choose. In this case it connects to a database simply called “database” so change this to your database name.

The top line tells the script where the database is. In most cases you can leave this as “localhost” as most hosts keep this as standard. If you get told your MySQL host is different though replace localhost with the new address your web host gives you.

Once you have established a connection you can then enter SQL underneath:

<?php
$db = mysql_connect("localhost", "username", "password");
mysql_select_db("database",$db);

$sql="SELECT * FROM members WHERE posts > 10";
$query=mysql_query($sql);
?>

In five lines you have connected to a database and even prepaed some SQL to select a record set from it. That is pretty simple I recon.

Server.MapPath is used to create a file path from a virtual path.

Here’s a typical DNS-less database connection:

Connection = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=C:\wwwroot\users\someaddress\db\data.mdb"

This isn’t much good though if you don’t know the full file path and if you are using a web host the chances are that you won’t know it. So you can use Server.MapPath instead.

Lets say that database is on the internet at the following address. Not that it is a good idea to have your database web accessible of course.

http://www.someaddress.com/db/data.mdb

Instead of having to use the file path as the top you could use a virtual path:

Connection = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" + Server.MapPath("/db/data.mdb")

This would get the server to “map” a file path to the database from the virtual path you give it. Notice that after DBQ= the tag (“) is closed as you don’t want the server to think + Sever.Map… is part of the file path.