Making JavaScript Applications Degrade Gracefully with AJAX and MySQL - Fetching database rows using a typical approach
(Page 2 of 4 )
In order to demonstrate how to retrieve some basic database records from MySQL using a combination of PHP and AJAX, I'm going to define a pair of PHP classes which will be tasked with connecting and sending queries to the server, as well as handling the returned result sets.
Actually, I used these classes in some of my previous PHP tutorials, so possibly at this point you'll find them very familiar. Having said that, here is how the classes in question look:
// define 'MySQL' class
class MySQL{
private $conId;
private $host;
private $user;
private $password;
private $database;
private $result;
const OPTIONS=4;
public function __construct($options=array()){
if(count($options)!=self::OPTIONS){
throw new Exception('Invalid number of connection
parameters');
}
foreach($options as $parameter=>$value){
if(!$value){
throw new Exception('Invalid parameter '.$parameter);
}
$this->{$parameter}=$value;
}
$this->connectDB();
}
// connect to MySQL
private function connectDB(){
if(!$this->conId=mysql_connect($this->host,$this-
>user,$this->password)){
throw new Exception('Error connecting to the server');
}
if(!mysql_select_db($this->database,$this->conId)){
throw new Exception('Error selecting database');
}
}
// run query
public function query($query){
if(!$this->result=mysql_query($query,$this->conId)){
throw new Exception('Error performing query '.$query);
}
return new Result($this,$this->result);
}
}
// define 'Result' class
class Result {
private $mysql;
private $result;
public function __construct(&$mysql,$result){
$this->mysql=&$mysql;
$this->result=$result;
}
// fetch row
public function fetchRow(){
return mysql_fetch_assoc($this->result);
}
// count rows
public function countRows(){
if(!$rows=mysql_num_rows($this->result)){
throw new Exception('Error counting rows');
}
return $rows;
}
// count affected rows
public function countAffectedRows(){
if(!$rows=mysql_affected_rows($this->mysql->conId)){
throw new Exception('Error counting affected rows');
}
return $rows;
}
// get ID form last-inserted row
public function getInsertID(){
if(!$id=mysql_insert_id($this->mysql->conId)){
throw new Exception('Error getting ID');
}
return $id;
}
// seek row
public function seekRow($row=0){
if(!is_int($row)||$row<0){
throw new Exception('Invalid result set offset');
}
if(!mysql_data_seek($this->result,$row)){
throw new Exception('Error seeking data');
}
}
}
Now that you've hopefully familiarized yourself with the signature of the PHP classes shown above, I'm going to create a sample "ARTICLES" MySQL database table, which will be filled in with the following data:
Id title author content
1 This is the title of article 1 Mary Wilson This is the content of article 1
2 This is the title of article 2 Alejandro Gervasio This is the content of article 2
3 This is the title of article 3 John Doe This is the content of article 3
So far, so good. Having at our disposal the previous MySQL database table, the next thing to do here consists of displaying part of the above records on the browser, by using the following PHP script:
try{
// connect to MySQL
$db=new MySQL(array('host'=>'localhost','user'=>'user',
'password'=>'password','database'=>'database'));
$result=$db->query("SELECT id,title,author FROM articles");
echo '<h2>Article List</h2><div id="articlecontainer">';
while($row=$result->fetchRow()){
echo '<div id="article'.$row['id'].'">';
echo 'Title: '.$row['title'].'<br /> Author: '.$row
['author'].'<a href="showdetail.php?id='.$row['id'].'"> Read
more</a>';
echo'</div><hr />';
}
echo '</div>';
}
catch(Exception $e){
echo $e->getMessage();
exit();
}
As you can see, the above PHP script simply displays part of the data stored in the "ARTICLES" database table that you saw before, including the respective articles' titles and authors. However I decided to include a link called "showdetail.php." It shows the full contents of the mentioned articles in a different web page. Quite simple, right?
Additionally, the output generated by the previous PHP script can be seen clearly in the following screen shot:
All right, at this point I built a simple PHP application that retrieves the contents of a sample "ARTICLES" MySQL database table, and then displays this data on the browser, of course including an additional link for showing the complete contents of these articles in a different web page.
Maybe at this point you're asking yourself the following question: where does AJAX fit into the previous schema? I'm glad you asked! Suppose that instead of displaying the full contents of a certain article on a separate web page, you want to show this data on the same web document to make this process a bit more attractive to users.
Here's where AJAX comes in. It's possible to develop a simple JavaScript application that displays the entire contents of a given article on the same web page. Nonetheless, the most important thing to notice here is the manner in which I built the original PHP application, which is completely independent of JavaScript.
If eventually there's a user that has disabled JavaScript on the browser, he or she will still be able to see the contents of a specific article on another web page, because the AJAX-based routines will degrade gracefully. Now, did you grasp the concept surrounding the development of JavaScript applications that fail silently? I bet you did!
Considering that you already understand the way that the previous PHP classes work, in the following section I'm going to create a simple AJAX-based script which will be responsible for displaying the full contents of a chosen article on the same web page on which the respective titles and authors are shown.
To see how this will be achieved, please click on the link below and keep reading.
Next: Displaying additional database records with AJAX >>
More JavaScript Articles
More By Alejandro Gervasio
