Breadcrumbs

[rocky4126]

MySQL is used in many PHP scripts and you may notice some use something called PDO (PHP Data Objects). This allows you to connect to a database and execute code much like mysql_* and therefore you may ask, why should I use PDO over mysql_*? The truth is, mysql_* was outdated 6-7 years ago. It was meant for MySQL versions before 4.1 was released which was released in 2004.

You may also ask why PHP guides still use it, well we'd all love to know. It might be because most of it was written pre 4.1 when it was still handy.

Also, the mysql_* library is insecure if you don't secure it yourself. This is something PDO does for you (when used correctly).

So, let's take a look at how we can utilise PDO for ourselves.

Code: Select all

<?php
	$host = "localhost"; //Defines the server you want to connect to. Most commonly, localhost.
	$port = 3306; //Defines the port that MySQL is running on. In most cases, this is 3306 unless your host says otherwise.
	$database = "name_of_database"; //This should be the name of the database you want to connect to (those are what are displayed down the left side of PHPMyAdmin)
	$username = "mysql_username"; //This should be the username you want to use to connect to the database.
	$password = "mysql_password"; //This should be the password associated with the username above.
	
	$dsn = "mysql:host=$host;port=$port;dbname=$database"; //This defines the DSN (Data Source Name) which tells PDO what and where to connect to.
	
	$dbh = new PDO($dsn,$username,$password); //Seeing as though PDO is a PHP class, we initiate a connection with "new".
	
	//You can also use a try/catch which will allow you to view errors and if needs be, parse out any confidential details.
	
	try{
		$dbh = new PDO($dsn,$username,$password);
	}catch(PDOException $e){
		die("PDO Error! ".$e->getMessage());
	}
	
	//Querying the database
	$statement = $dbh->query("SELECT * FROM table");
	while($result = $statement->fetch()){ //You can also use $statement->fetchObject()
		var_dump($result); //dumps $result which is an array.
		//NOTE: If you used fetchObject, it wouldn't be an array, it would be in the form of $result->column_name.
	}
	
	//Using Prepared statements to insert into the database
	//Prepared statements play an important role in security. This would be something mysql_query definately lacks.
	//Prepared statements escape the input in quite a few ways to stop SQL Injection.
	$query = $dbh->prepare("INSERT INTO table (column1,column2) VALUES (?,?)");
	$val1 = "'; SELECT * FROM table";
	$val2 = "'; SELECT * FROM table WHERE column1 LIKE %";
	$query->execute(array($val1,$val2)); //This will escape ' in $val1 (That's all it needs to escape) in $val2, it'll escape ' and % (% = wildcard).

With that, I rest my case that PDO is definately better to use that mysql_*.


Sources
http://webdevrefinery.com/forums/topic/ ... ode-sucks/
http://lynxphp.com/php/pdo-basics/

[Livengood]

Thanks for the share , learning something new with php today