PHP + SQL

Today, I learned that how to work PHP with SQL.

This is the algorithms:

1. Connect to the database.
2. Handle connection errors.
3. Execute the SQL query.
4. Process the results.
5. Free resources and close connection.

PDO: PHP data Objects

$connectionString = "mysql:host=localhost; dbname=bookcrm";
$user = "testuser";
$pass = "mypassword";

$pdo = new PDO($connectionString, $user, $pass);

Normal MySQL:

$host = "localhost";
$database = "bookcrm";
$user = "testuser";
$pass = "mypassword";

$connection = mysqli_connection($host, $user, $pass, $database);

 

PDO Error-handling Modes:

PDO::ERRMODE_SILENT

This is the defaule mode. PDO will simply set the error code for you to inspect using PDO::errorCode()... 

Preferred once the site is in normal production use.

 

PDO::ERRMODE_WARNING

In addition to setting the error code, PDO will output a warning message. This setting is useful during debugging/testing. If you just want to see what problems occurred without interrupting the flow of the application.

 

PDO::ERRMODE_EXCEPTION

In addtion to setting the error code, PDO will throw a PDOException and set its properties to reflect the error code and error information. This setting is especially useful during debugging, as it stops the script at the point of the error.

 

$pdo = new PDO(DBCONNSTRING, DBUSER, DBPASS);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

 

Executing the Query:

• exec(): used for queries that do not return a resullt set

$sql = "DELETE FROM artists WHERE LastName = 'Connolly";
$count = $pdo->exec($sql); // returns number of rows that were deleted

 

• query(): used when you expect a result set to be returned. It returns a PDOStatement object to you that you can iterate over to get the individual rows. 

$sql = "SELECT * FROM Categories ORDER BY CategoryName";
$result = $pdo->query($sql);

 

 

Processing the Query Results:

$sql = "SELECT * FROM Paintings ORDER BY Title";
$result = $pdo->query($sql);

// fetch a record from result set into an associative array
while($row = $result->fetch()) {
	echo $row['ID']. "-".$row['Title']; // the keys match the field names from the table
    echo "<br/>";
}

Freeing Resources and Closing Connection:

When you are finished retrieving and displaying your requested data, you should release the memory used by any result sets and then close the connection so that the database system can allocate it to another process.

 

$pdo = new PDO(DBCONNSTRING, DBUSER, DBPASS);

// closes connection and frees the resources used by the PDO object
$pdo = null;

 

Parameters and Prepared Statements:

$from = $_POST['old'];
$to = $_POST['new'];
$sql = "UPDATE Categories SET categoryName='$to' WHERE CategoryName="$from";
$count = $pdo->exec($sql);

// but this is vunlnerable to SQL injection attack

 

A prepared Statement is actually a way to sanitize user data to remove any special characters from a desired piece of text.

// use a name parameter
$sql = "UPDATA Categories SET CategoryName=':tn' WHERE CategoryName =':fo'";

$statement = $pdo->prepare($sql);

//bind values to placeholders by passing an array
$statement->execute(array(
	':tn'=> $_POST['new'],
    ':fo'=> $_POST['old']));