Tuesday, July 30, 2013

PHP Database Access: Are You Doing It Correctly?

PHP Database Access: Are You Doing It Correctly?

Tutorial Details

  • Topic - Database Access in PHP
  • Difficulty - Moderate
We've covered PHP's PDO API a couple of times here on Nettuts+, but, generally, those articles focused more on the theory, and less on the application. This article will fix that!
To put it plainly, if you're still using PHP's old mysql API to connect to your databases, read on!

What?

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:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
/*
 * 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.

How?

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.

Connect

So you already know the legacy way of connecting to a MySQL database:
1
2
# 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:
1
$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, like mysql_connect.

Errors

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:
1
2
3
4
5
6
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 is PDO::ERRMODE_SILENT. With this setting left unchanged, you'll need to manually fetch errors, after performing a query.
1
2
echo $conn->errorCode();
echo $conn->errorInfo();
Instead, a better choice, during development, is to update this setting to PDO::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

Fetch

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 and execute. We'll review both.

Query

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
/*
 * 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 the PDO::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 foreach statement.

Prepared Statements

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
/*
 * 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 bindParam method, like so:
1
2
$stmt->bindParam(':id', $id, PDO::PARAM_INT);
$stmt->execute();

Specifying the Ouput

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:
1
2
3
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:
1
2
3
4
5
6
7
8
9
10
11
12
13
$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:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
$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();
}

Multiple Executions

The PDO extension becomes particularly powerful when executing the same SQL query multiple times, but with different parameters.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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.”

CRUD

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.

Create (Insert)

1
2
3
4
5
6
7
8
9
10
11
12
13
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();

Update

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
$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();
}

Delete

1
2
3
4
5
6
7
8
9
10
11
12
13
14
$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();
}

Object Mapping

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:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
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();
}

Closing Thoughts

Bottom line: if you’re still using that old mysql API for connecting to your databases, stop. Though it hasn’t yet been deprecated, in terms of education and documentation, it might as well be. Your code will be significantly more secure and streamlined if you adopt the PDO extension.
Posted by at No comments:

CSS3 coding for hide and Show

This is show and hide in css3.

it consists of hide and  show property .

[hide] this line used for hide the content .

[show]this line used for show the content.

css3 using to focus to the content coding to hide and display content very easily.
 


 Demo: 

Here's a list

[hide]
  1. item 1
  2. item 2
  3. item 3
How about that?


Source Code:
<!DOCTYPE html>
<head>
   <title>CSS3 coding for hide and  Show</title>
   <style type="text/css">
      .show {display: none; }
      .hide:focus + .show {display: inline; }
      .hide:focus { display: none; }
      .hide:focus ~ #list { display:none; }
      @media print { .hide, .show { display: none; } }
   
   </style>
</head>
<body>
   <p>Here's a list</p>
      <div>
        <a href="#" class="hide">[hide]</a>
        <a href="#" class="show">[show]</a>
         <ol id="list">
            <li>item 1</li>
            <li>item 2</li>
            <li>item 3</li>
         </ol>
      </div>
   <p>How about that?</p>
</body>
</html>
Posted by at No comments:

Integrate PHP application with Solr search engine

Posted on by
Apache Solr
So why do you need a search engine, is database not enough? If you create a small website it might not matter. With medium or big size applications it’s often wiser to go for a search engine. Saying that, even a small websites can benefit from Solr if you desire a high level of relevance in search results.

Let’s imagine you have to create a search handler for an e-commerce website. A naive approach would be creating database query like this:
It might work if the search phrase is exactly as part of a title or a description. In the real life items have complex names, for instance: Apple iPhone 4G black 16GB. If somebody looks for “iPhone 16GB” no results will be returned. You can mitigate it by replacing white spaces with “%” character before the phrase is passed to SQL.
It will work for the above problem but what if the phrase is “iPhone 16GB 4G”? Obviously different order of keywords won’t work with the above system. I presume you can have an additional column and order words alphabetically, but what about misspells or synonyms? Coming up with a good solution for search system is a challenging task.
Producing a clever algorithm is not the only problem. Text search is resource consuming exercise. Laying too much stress on a database is never a good idea. The ultimate reason for that is databases don’t scale well. You can’t just add another instance as you would do for a web server or Memcached. Scaling database requires preparation, changes in software, configuration, down time and generally speaking is expensive. The good news is both problems can be solved with Solr.
Solr is an enterprise search platform based on Apache Lucene. It’s fast, stable, has good document and scales very well. While Solr is a robust solution and listing all features it provides is light years beyond scope of this post, it’s relatively easy to start using it.
First, download the latest version of the Service from the official site. Solr is written in Java so you also need Java Runtime Environment to run it.
After few seconds you should see something like
Solr has a web interface which is available under port 8983. Open a web browser and go to http://localhost:8983/solr/.
If you look at the left hand side navigation you will find “collection1″. Collections in Solr are something similar to database table. You can query it. Click on the collection and chose “query” from submenu.
First option is called “Request-Handler (qt)” with default value “/select”. Request handlers are sort of pre-defined queries. If you look into Solr config file you can find all of them.
Second and the most interesting parameter is query. Default value “*:*” selects everything. If you click on “execute query” you should get something like this:
    
    name="response" numFound="0" start="0" />
The index is empty but It’s not a problem. You can quickly insert some example data.
Now you can go back to query interface. This time one document should be returned.
Collection’s data structure is defined in schema file.
The file is has very good comments and you can easy figure out what’s going on there. If you want to amend the schema don’t remove filed named “text” (without a good reason). It’s used by other fields and some request handlers are referring to it (including select, look above).
If you use relational database you don’t want to duplicate data. Solr is not a database. Many fields are copied to the text field. Default request handler will look there on search.
To access Solr from PHP you need a client. I can recommend the one available on PECL. It’s fast, have clear API and is well document. There is one issue with the current version (1.0.2) of the extension. It doesn’t work with Solr4.x ;) . There is a small difference in protocol between 3.x and 4.x. Don’t worry, I’ve fix this issue and you can download working version from here https://github.com/lukaszkujawa/php-pecl-solr. I’ve been using this fix for a while now and it feels stable. It introduces small change to SolrClient constructor – additional parameter to specify version. The patch will go to the official release so you won’t lose consistence.
Edit your php.ini and add
Restart web server.
Now we can create a PHP script which will insert something into the index.
If you insert more then one document commit at the end. It’s resource consuming process and you don’t want commits to clobber.
It’s worth to know how to work with Solr. You can use it with various projects. It has very cool features which will let you pull all required data in one request. You will have to invest some time to master it but it will pay out. Solr is very well document and has active community. If you are serious about using with you projects read Apache Solr 3 Enterprise Search Server. It will get you up to speed not only with the service but also with basics of data mining.
Posted by at No comments:

Friday, July 19, 2013

Create a Zip File Using PHP

 

Creating .ZIP archives using PHP can be just as simple as creating them on your desktop. PHP's ZIP class provides all the functionality you need! To make the process a bit faster for you, I've coded a simple create_zip function for you to use on your projects.

The PHP

/* creates a compressed zip file */
function create_zip($files = array(),$destination = '',$overwrite = false) {
 //if the zip file already exists and overwrite is false, return false
 if(file_exists($destination) && !$overwrite) { return false; }
 //vars
 $valid_files = array();
 //if files were passed in...
 if(is_array($files)) {
  //cycle through each file
  foreach($files as $file) {
   //make sure the file exists
   if(file_exists($file)) {
    $valid_files[] = $file;
   }
  }
 }
 //if we have good files...
 if(count($valid_files)) {
  //create the archive
  $zip = new ZipArchive();
  if($zip->open($destination,$overwrite ? ZIPARCHIVE::OVERWRITE : ZIPARCHIVE::CREATE) !== true) {
   return false;
  }
  //add the files
  foreach($valid_files as $file) {
   $zip->addFile($file,$file);
  }
  //debug
  //echo 'The zip archive contains ',$zip->numFiles,' files with a status of ',$zip->status;
  
  //close the zip -- done!
  $zip->close();
  
  //check to make sure the file exists
  return file_exists($destination);
 }
 else
 {
  return false;
 }
}

Sample Usage

$files_to_zip = array(
 'preload-images/1.jpg',
 'preload-images/2.jpg',
 'preload-images/5.jpg',
 'kwicks/ringo.gif',
 'rod.jpg',
 'reddit.gif'
);
//if true, good; if false, zip creation failed
$result = create_zip($files_to_zip,'my-archive.zip');
The function accepts an array of files, the name of the destination files, and whether or not you'd like the destination file to be overwritten if a file of the same name exists. The function returns true if the file was created, false if the process runs into any problems.
Posted by at No comments:
Subscribe to: Posts (Atom)