Hot File

Are You Doing It Correctly PHP Database Access

View: 316    Dowload: 0   Comment: 0   Post by: hanhga  
Author: none   Category: MySQL   Fields: Other

9 point/2 review File has been tested

To put it plainly, if you're still using PHP's old mysql API to connect to your databases, read on!

Introduction

To put it plainly, if you're still using PHP's old mysql API to connect to your databases, read on!

It's possible that, at this point, the only thought in your mind is, "What the heck is PDO?" Well, it's one of PHP's three available APIs for connecting to a MySQL database. "Three," you say? Yes; many folks don't know it, but there are three different APIs for connecting:

  • mysql

  • mysqli - MySQL Improved

  • pdo - PHP Data Objects

The traditional mysql API certainly gets the job done, and has become so popular largely due to the fact that it makes the process of retrieving some records from a database as easy as possible. For example:

/*
 * Anti-Pattern
 */
 
# Connect
mysql_connect('localhost', 'username', 'password') or die('Could not connect: ' . mysql_error());
 
# Choose a database
mysql_select_db('someDatabase') or die('Could not select database');
 
# Perform database query
$query = "SELECT * from someTable";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
 
# Filter through rows and echo desired information
while ($row = mysql_fetch_object($result)) {
    echo $row->name;
}

Yes, the code above is fairly simple, but it does come with its significant share of downsides.

  • Deprecated: Though it hasn't been officially deprecated - due to widespread use - in terms of best practice and education, it might as well be.

  • Escaping: The process of escaping user input is left to the developer - many of which don't understand or know how to sanitize the data.

  • Flexibility: The API isn't flexible; the code above is tailor-made for working with a MySQL database. What if you switch?

PDO, or PHP Data Objects, provides a more powerful API that doesn't care about the driver you use; it's database agnostic. Further, it offers the ability to use prepared statements, virtually eliminating any worry of SQL injection.

When I was first learning about the PDO API, I must admit that it was slightly intimidating. This wasn't because the API was overly complicated (it's not) - it's just that the old myqsl API was so dang easy to use!

Don't worry, though; follow these simple steps, and you'll be up and running in no time.

So you already know the legacy way of connecting to a MySQL database:

# Connect
mysql_connect('localhost', 'username', 'password') or die('Could not connect: ' . mysql_error());

With PDO, we create a new instance of the class, and specify the driver, database name, username, and password - like so:

$conn = new PDO('mysql:host=localhost;dbname=myDatabase', $username, $password);

Don't let that long string confuse you; it's really very simple: we specify the name of the driver (mysql, in this case), followed by the required details (connection string) for connecting to it.

What's nice about this approach is that, if we instead wish to use a sqlite database, we simply update the DSN, or "Data Source Name," accordingly; we're not dependent upon MySQL in the way that we are when use functions, likemysql_connect.

But, what if there's an error, and we can't connect to the database? Well, let's wrap everything within a try/catch block:

try {
    $conn = new PDO('mysql:host=localhost;dbname=myDatabase', $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
    echo 'ERROR: ' . $e->getMessage();
}

That's better! Please note that, by default, the default error mode for PDO isPDO::ERRMODE_SILENT. With this setting left unchanged, you'll need to manually fetch errors, after performing a query.

echo $conn->errorCode();
echo $conn->errorInfo();

Instead, a better choice, during development, is to update this setting toPDO::ERRMODE_EXCEPTION, which will fire exceptions as they occur. This way, any uncaught exceptions will halt the script.

For reference, the available options are:

  • PDO::ERRMODE_SILENT

  • PDO::ERRMODE_WARNING

  • PDO::ERRMODE_EXCEPTION

At this point, we've created a connection to the database; let's fetch some information from it. There's two core ways to accomplish this task: query andexecute. We'll review both.

/*
 * The Query Method
 * Anti-Pattern
 */
 
$name = 'Joe'; # user-supplied data
 
try {
    $conn = new PDO('mysql:host=localhost;dbname=myDatabase', $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 
    $data = $conn->query('SELECT * FROM myTable WHERE name = ' . $conn->quote($name));
 
    foreach($data as $row) {
        print_r($row); 
    }
} catch(PDOException $e) {
    echo 'ERROR: ' . $e->getMessage();
}

Though this works, notice that we're still manually escaping the user's data with thePDO::quote method. Think of this method as, more or less, the PDO equivalent to use mysql_real_escape_string; it will both escape and quote the string that you pass to it. In situations, when you're binding user-supplied data to a SQL query, it's strongly advised that you instead use prepared statements. That said, if your SQL queries are not dependent upon form data, the query method is a helpful choice, and makes the process of looping through the results as easy as a foreachstatement.

/*
 * The Prepared Statements Method
 * Best Practice
 */
 
$id = 5;
try {
    $conn = new PDO('mysql:host=localhost;dbname=myDatabase', $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);    
     
    $stmt = $conn->prepare('SELECT * FROM myTable WHERE id = :id');
    $stmt->execute(array('id' => $id));
 
    while($row = $stmt->fetch()) {
        print_r($row);
    }
} catch(PDOException $e) {
    echo 'ERROR: ' . $e->getMessage();
}

In this example, we're using the prepare method to, literally, prepare the query, before the user's data has been attached. With this technique, SQL injection is virtually impossible, because the data doesn't ever get inserted into the SQL query, itself. Notice that, instead, we use named parameters (:id) to specify placeholders.

Alternatively, you could use ? parameters, however, it makes for a less-readable experience. Stick with named parameters.

Next, we execute the query, while passing an array, which contains the data that should be bound to those placeholders.

1
$stmt->execute(array('id' => $id));

An alternate, but perfectly acceptable, approach would be to use the bindParammethod, like so:

$stmt->bindParam(':id', $id, PDO::PARAM_INT);
$stmt->execute();

After calling the execute method, there are a variety of different ways to receive the data: an array (the default), an object, etc. In the example above, the default response is used: PDO::FETCH_ASSOC; this can easily be overridden, though, if necessary:

while($row = $stmt->fetch(PDO::FETCH_OBJ)) {
    print_r($row);
}

Now, we've specified that we want to interact with the result set in a more object-oriented fashion. Available choices include, but not limited to:

  • PDO::FETCH_ASSOC: Returns an array.

  • PDO::FETCH_BOTH: Returns an array, indexed by both column-name, and 0-indexed.

  • PDO::FETCH_BOUND: Returns TRUE and assigns the values of the columns in your result set to the PHP variables to which they were bound.

  • PDO::FETCH_CLASS: Returns a new instance of the specified class.

  • PDO::FETCH_OBJ: Returns an anonymous object, with property names that correspond to the columns.

One problem with the code above is that we aren't providing any feedback, if no results are returned. Let's fix that:

$stmt->execute(array('id' => $id));
 
# Get array containing all of the result rows
$result = $stmt->fetchAll();
 
# If one or more rows were returned...
if ( count($result) ) {
    foreach($result as $row) {
        print_r($row);
    }
} else {
    echo "No rows returned.";
}

At this point, our full code should look like so:

$id = 5;
try {
  $conn = new PDO('mysql:host=localhost;dbname=someDatabase', $username, $password);
  $stmt = $conn->prepare('SELECT * FROM myTable WHERE id = :id');
  $stmt->execute(array('id' => $id));
 
  $result = $stmt->fetchAll();
 
  if ( count($result) ) { 
    foreach($result as $row) {
      print_r($row);
    }   
  } else {
    echo "No rows returned.";
  }
} catch(PDOException $e) {
    echo 'ERROR: ' . $e->getMessage();
}

 

The PDO extension becomes particularly powerful when executing the same SQL query multiple times, but with different parameters.

try {
  $conn = new PDO('mysql:host=localhost;dbname=someDatabase', $username, $password);
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 
  # Prepare the query ONCE
  $stmt = $conn->prepare('INSERT INTO someTable VALUES(:name)');
  $stmt->bindParam(':name', $name);
 
  # First insertion
  $name = 'Keith';
  $stmt->execute();
 
  # Second insertion
  $name = 'Steven';
  $stmt->execute();
} catch(PDOException $e) {
  echo $e->getMessage();
}

Once the query has been prepared, it can be executed multiple times, with different parameters. The code above will insert two rows into the database: one with a name of "Kevin," and the other, "Steven."

Now that you have the basic process in place, let's quickly review the various CRUD tasks. As you'll find, the required code for each is virtually identical.

try {
  $pdo = new PDO('mysql:host=localhost;dbname=someDatabase', $username, $password);
  $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 
  $stmt = $pdo->prepare('INSERT INTO someTable VALUES(:name)');
  $stmt->execute(array(
    ':name' => 'Justin Bieber'
  ));
 
  # Affected Rows?
  echo $stmt->rowCount(); // 1
} catch(PDOException $e) {
  echo 'Error: ' . $e->getMessage();
$id = 5;
$name = "Joe the Plumber";
 
try {
  $pdo = new PDO('mysql:host=localhost;dbname=someDatabase', $username, $password);
  $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 
  $stmt = $pdo->prepare('UPDATE someTable SET name = :name WHERE id = :id');
  $stmt->execute(array(
    ':id'   => $id,
    ':name' => $name
  ));
   
  echo $stmt->rowCount(); // 1
} catch(PDOException $e) {
  echo 'Error: ' . $e->getMessage();
}
$id = 5; // From a form or something similar
 
try {
  $pdo = new PDO('mysql:host=localhost;dbname=someDatabase', $username, $password);
  $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 
  $stmt = $pdo->prepare('DELETE FROM someTable WHERE id = :id');
  $stmt->bindParam(':id', $id); // this time, we'll use the bindParam method
  $stmt->execute();
   
  echo $stmt->rowCount(); // 1
} catch(PDOException $e) {
  echo 'Error: ' . $e->getMessage();
}

One of the neatest aspects of PDO (mysqli, as well) is that it gives us the ability to map the query results to a class instance, or object. Here's an example:

class User {
  public $first_name;
  public $last_name;
 
  public function full_name()
  {
    return $this->first_name . ' ' . $this->last_name;
  }
}
 
try {
  $pdo = new PDO('mysql:host=localhost;dbname=someDatabase', $username, $password);
  $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 
  $result = $pdo->query('SELECT * FROM someTable');
 
  # Map results to object
  $result->setFetchMode(PDO::FETCH_CLASS, 'User');
 
  while($user = $result->fetch()) {
    # Call our custom full_name method
    echo $user->full_name();
  }
} catch(PDOException $e) {
  echo 'Error: ' . $e->getMessage();
}

 

 

Are You Doing It Correctly PHP Database Access

Are You Doing It Correctly PHP Database Access Posted on 12-01-2016  To put it plainly, if you're still using PHP's old mysql API to connect to your databases, read on! 4.5/10 316

Comment:

To comment you must be logged in members.

Files with category

  • SQL Query to Find Nth Highest Salary of Employee with java

    View: 329    Download: 0   Comment: 0   Author: none  

    SQL Query to Find Nth Highest Salary of Employee with java

    Category: MySQL
    Fields: Other

    0/0 review
    This is a very common interview question if you are going for the role at junior level. Most of the people learn it character by character, but few understand how this query works.

  • World OOP With PHP and MySQL

    View: 1089    Download: 0   Comment: 0   Author: none  

    World OOP With PHP and MySQL

    Category: MySQL
    Fields: Other

    0.32142857142857/14 review
    Setting up the skeleton of our class is fairly simple once we figure out exactly what we need.

  • How to Build a Newsletter System With PHP and MySQL

    View: 1395    Download: 0   Comment: 0   Author: none  

    How to Build a Newsletter System With PHP and MySQL

    Category: MySQL
    Fields: Other

    1.125/4 review
    When starting any project, I like to layout the folders before I start coding, so lets do that now.

  • Top 20+ MySQL Best Practices that can not be ignored

    View: 1024    Download: 0   Comment: 0   Author: none  

    Top 20+ MySQL Best Practices that can not be ignored

    Category: MySQL
    Fields: Other

    1.6666666666667/3 review
    Database operations often tend to be the main bottleneck for most web applications today.

  • Create a PHP/MySQL Powered Forum from Scratch

    View: 1243    Download: 0   Comment: 0   Author: none  

    Create a PHP/MySQL Powered Forum from Scratch

    Category: MySQL
    Fields: Other

    1.5/3 review
    In this tutorial, we're going to build a PHP/MySQL powered forum from scratch.

  • Triggers Introduction to MySQL

    View: 361    Download: 0   Comment: 0   Author: none  

    Triggers Introduction to MySQL

    Category: MySQL
    Fields: Other

    1.6666666666667/3 review
    I would guess, even armed with this knowledge, that a good many of you are not taking advantage of triggers with MySQL.

  • How to Stored Procedures in MySQL 5

    View: 361    Download: 0   Comment: 0   Author: none  

    How to Stored Procedures in MySQL 5

    Category: MySQL
    Fields: Other

    2.25/2 review
    MySQL 5 introduced a plethora of new features - stored procedures being one of the most significant. In this tutorial, we will focus on what they are, and how they can make your life easier.

  • The strength of the PDO and. MySQLi: Which Should You Use?

    View: 368    Download: 0   Comment: 0   Author: none  

    The strength of the PDO and. MySQLi: Which Should You Use?

    Category: MySQL
    Fields: Other

    1.5/3 review
    When accessing a database in PHP, we have two choices: MySQLi and PDO. So what should you know before choosing one?

 
Newsletter Email

File suggestion for you

File top downloads

logo codetitle
Codetitle.com - library source code to share, download the file to the community
Copyright © 2015. All rights reserved. codetitle.com Develope by Vinagon .Ltd