What is the best way to connect to and query a MySQL database using PHP?

Question

Hi ,

This is a pretty easy question but I would like some clarity on something.

I have seen a number of different ways to connect and query a MySql Database using PHP but which is the best way to connect and create a query using php ?

Hope this makes sense.

Thanks

Answer 1

Use the object-oriented versions, assuming your php version is new enough to support 'em. They're far cleaner IMHO than the random function soup.

Answer 2

I don't think it's quite as simple as saying "the best way is..."

Personally, I hardly ever connect to a database using my own code, I normally have a framework doing that for me. That said, I'd use the PHP Data Object (PDO) approach to connect and query a database, if I were writing a small standalone application.

See the manual pages for all the information and examples you'll need.

Answer 3

By far the best way is to use prepared statements. You can do this using PDO or mysqli, but I prefer the PDO extension for its named parameters.

Why are prepared statements by far the best way? Because they take care of parameter quoting and escaping for you.

Bad, old, error-prone, tedious way:

$result = mysql_query("SELECT * FROM users WHERE 
                       password='".mysql_real_escape_string($password)."'");

You can bet that, if you've written an application like this, you will have forgotten at some point to escape the user input, and left a gaping SQL injection hole.

Nice prepared statement way:

$stmt = $dbh->prepare("SELECT * FROM users WHERE password=:password");
$stmt->bindParam(':password', $password);
$stmt->execute();

Escaping is done for you, and you don't even have to worry about putting quotes around the parameter types that need them.