Building a Website with PHP, MySQL and jQuery Mobile, Part 1

Building a Website with PHP, MySQL and jQuery Mobile, Part 1

In this two-part tutorial, we will be building a simple website with PHP and MySQL, using the Model-View-Controller (MVC) pattern. Finally, with the help of the jQuery Mobile framework, we will turn it into a touch-friendly mobile website, that works on any device and screen size.

In this first part, we concentrate on the backend, discussing the database and MVC organization. In part two, we are writing the views and integrating jQuery Mobile.

The File Structure

As we will be implementing the MVC pattern (in effect writing a simple micro-framework), it is natural to split our site structure into different folders for the models, views and controllers. Don’t let the number of files scare you – although we are using a lot of files, the code is concise and easy to follow.

The Directory Structure

The Directory Structure

The Database Schema

Our simple application operates with two types of resources – categories and products. These are given their own tables – jqm_categories, and jqm_products. Each product has a category field, which assigns it to a category.

jqm_categories Table Structure

jqm_categories Table Structure

The categories table has an ID field, a name and a contains column, which shows how many products there are in each category.

jqm_products Table Structure

jqm_products Table Structure

The product table has a name, manufacturer, price and a category field. The latter holds the ID of the category the product is added to.

You can find the SQL code to create these tables in tables.sql in the download archive. Execute it in the SQL tab of phpMyAdmin to have a working copy of this database. Remember to also fill in your MySQL login details in config.php.

The Models

The models in our application will handle the communication with the database. We have two types of resources in our application – products and categories. The models will expose an easy to use method – find() which will query the database behind the scenes and return an array with objects.

Before starting work on the models, we will need to establish a database connection. I am using the PHP PDO class, which means that it would be easy to use a different database than MySQL, if you need to.

includes/connect.php

/*
	This file creates a new MySQL connection using the PDO class.
	The login details are taken from includes/config.php.
*/

try {
	$db = new PDO(
		"mysql:host=$db_host;dbname=$db_name;charset=UTF8",
		$db_user,
		$db_pass
	);

    $db->query("SET NAMES 'utf8'");
	$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $e) {
	error_log($e->getMessage());
	die("A database error was encountered");
}

This will put the $db connection object in the global scope, which we will use in our models. You can see them below.

includes/models/category.model.php

class Category{

	/*
		The find static method selects categories
		from the database and returns them as
		an array of Category objects.
	*/

	public static function find($arr = array()){
		global $db;

		if(empty($arr)){
			$st = $db->prepare("SELECT * FROM jqm_categories");
		}
		else if($arr['id']){
			$st = $db->prepare("SELECT * FROM jqm_categories WHERE id=:id");
		}
		else{
			throw new Exception("Unsupported property!");
		}

                // This will execute the query, binding the $arr values as query parameters
		$st->execute($arr);

		// Returns an array of Category objects:
		return $st->fetchAll(PDO::FETCH_CLASS, "Category");
	}
}

Both models are simple class definitions with a single static method – find(). In the fragment above, this method takes an optional array as a parameter and executes different queries as prepared statements.

In the return declaration, we are using the fetchAll method passing it the PDO::FETCH_CLASS constant. What this does, is to loop though all the result rows, and create a new object of the Category class. The columns of each row will be added as public properties to the object.

This is also the case with the Product model:

includes/models/product.model.php

class Product{

	// The find static method returns an array
	// with Product objects from the database.

	public static function find($arr){
		global $db;

		if($arr['id']){
			$st = $db->prepare("SELECT * FROM jqm_products WHERE id=:id");
		}
		else if($arr['category']){
			$st = $db->prepare("SELECT * FROM jqm_products WHERE category = :category");
		}
		else{
			throw new Exception("Unsupported property!");
		}

		$st->execute($arr);

		return $st->fetchAll(PDO::FETCH_CLASS, "Product");
	}
}

The return values of both find methods are arrays with instances of the class. We could possibly return an array of generic objects (or an array of arrays) in the find method, but creating specific instances will allow us to automatically style each object using the appropriate template in the views folder (the ones that start with an underscore). We will talk again about this in the next part of the tutorial.

There, now that we have our two models, lets move on with the controllers.

Computer Store with PHP, MySQL and jQuery Mobile

Computer Store with PHP, MySQL and jQuery Mobile

The controllers

The controllers use the find() methods of the models to fetch data, and render the appropriate views. We have two controllers in our application – one for the home page, and another one for the category pages.

includes/controllers/home.controller.php

/* This controller renders the home page */

class HomeController{
	public function handleRequest(){

		// Select all the categories:
		$content = Category::find();

		render('home',array(
			'title'		=> 'Welcome to our computer store',
			'content'	=> $content
		));
	}
}

Each controller defines a handleRequest() method. This method is called when a specific URL is visited. We will return to this in a second, when we discuss index.php.

In the case with the HomeController, handleRequest() just selects all the categories using the model’s find() method, and renders the home view (includes/views/home.php) using our render helper function (includes/helpers.php), passing a title and the selected categories. Things are a bit more complex in CategoryController:

includes/controllers/category.controller.php

/* This controller renders the category pages */

class CategoryController{
	public function handleRequest(){
		$cat = Category::find(array('id'=>$_GET['category']));

		if(empty($cat)){
			throw new Exception("There is no such category!");
		}

		// Fetch all the categories:
		$categories = Category::find();

		// Fetch all the products in this category:
		$products = Product::find(array('category'=>$_GET['category']));

		// $categories and $products are both arrays with objects

		render('category',array(
			'title'			=> 'Browsing '.$cat[0]->name,
			'categories'	=> $categories,
			'products'		=> $products
		));
	}
}

The first thing this controller does, is to select the category by id (it is passed as part of the URL). If everything goes to plan, it fetches a list of categories, and a list of products associated with the current one. Finally, the category view is rendered.

Now lets see how all of these work together, by inspecting index.php:

index.php

/*
	This is the index file of our simple website.
	It routes requests to the appropriate controllers
*/

require_once "includes/main.php";

try {

	if($_GET['category']){
		$c = new CategoryController();
	}
	else if(empty($_GET)){
		$c = new HomeController();
	}
	else throw new Exception('Wrong page!');

	$c->handleRequest();
}
catch(Exception $e) {
	// Display the error page using the "render()" helper function:
	render('error',array('message'=>$e->getMessage()));
}

This is the first file that is called on a new request. Depending on the $_GET parameters, it creates a new controller object and executes its handleRequest() method. If something goes wrong anywhere in the application, an exception will be generated which will find its way to the catch clause, and then in the error template.

One more thing that is worth noting, is the very first line of this file, where we require main.php. You can see it below:

main.php

/*
	This is the main include file.
	It is only used in index.php and keeps it much cleaner.
*/

require_once "includes/config.php";
require_once "includes/connect.php";
require_once "includes/helpers.php";
require_once "includes/models/product.model.php";
require_once "includes/models/category.model.php";
require_once "includes/controllers/home.controller.php";
require_once "includes/controllers/category.controller.php";

// This will allow the browser to cache the pages of the store.

header('Cache-Control: max-age=3600, public');
header('Pragma: cache');
header("Last-Modified: ".gmdate("D, d M Y H:i:s",time())." GMT");
header("Expires: ".gmdate("D, d M Y H:i:s",time()+3600)." GMT");

This file holds the require_once declarations for all the models, controllers and helper files. It also defines a few headers to enable caching in the browser (PHP disables caching by default), which speeds up the performance of the jQuery mobile framework.

Continue to Part 2

With this the first part of the tutorial is complete! Continue to part 2, where we will be writing the views and incorporate jQuery Mobile. Feel free to share your thoughts and suggestions in the comment section below.

Join our newsletter and get our PSDs!19,398 people learn about HTML5, JS and more. Join them!

by Martin Angelov

Martin is a web developer with an eye for design from Bulgaria. He founded Tutorialzine in 2009 and it still is his favorite side project.

50 Comments

  1. jacked says:

    Wow! Man you rock! Exelent tutorial! I start to visit this blog more often!

  2. MathiasS says:

    nice thank!!!

  3. Excellent write up, the web needs more articles like this. My only critic is viewing the demo on my phone, the footer seemed to be bugy. It displays at the bottom of my screen, but when I scroll down it stays in place, (ex. now the middle of the screen) then jumps to the new bottom after about one second delay. It just seems to be a delay in keeping the footer at bottom:0; when scrolling down. I noticed this on Android 2.2 Samsung Galaxy S.

    1. Martin Angelov says:

      Thank you for catching that. It seemed logical to have the footer fixed at the bottom of the screen, but mobile devices do not update the page while scrolling. It is now fixed.

      1. Lauro Atienza says:

        Hi Martin, I think this is a nice tutorial. Perfect for a basic web application. However, Is there way to elaborate it more, like when the product is clicked, it will then display more information about that product. Hope to hear from you soon. cheers.

  4. Saquib says:

    Amazing tut, can't wait for second part.

    You always rocks Martin

  5. Rey thibaut says:

    Hi,

    There is an issue with the footer. It is fixed in the middle of the screen. I have tested the website in the iPhone simulator app on my mac.

    Ps : Sorry for my english i come from france.

    1. Martin Angelov says:

      Thank you! It is now fixed. You can re-download it.

      1. Rey thibaut says:

        Good jobs think you so much

  6. Very nice tutorial.. Thanks for share.

  7. Thaninrat says:

    Wow. That was what i am looking for. You will try with codeigniter project, thankyou.

  8. Alberto says:

    Woow!! You rock!! really cool stuff!!

  9. Thomas P says:

    Martin your tutorials are still very high-quality. Thx !

  10. Bruce says:

    cool! nice job, and for those who don't have an iphone like me :) you can download mobilizer and see how the final protect looks in on a smartphone

    http://www.springbox.com/mobilizer/

  11. M.Rizwan says:

    Excellent Tutorial,very precise and to the point ... Thanks

  12. Damian Clem says:

    Martin -

    Nice job man! Nice light weight framework. Is that MVC framework something you created and use for most of your projects? I really enjoy your tutorials. Looking forward to part II.

    Now, if I can just get something completed for my blog. ;)

    1. Martin Angelov says:

      Thank you!

      Although I called it a framework, the code here merely follows the MVC pattern. It lacks the features that a real framework would give you. That said, it does demonstrate that writing your own MVC code isn't hard at all. The problem is that people associate MVC with large frameworks like Zend and CodeIgniter and feel it is beyond them to do it themselves.

      1. Damian Clem says:

        Well said. Haven't written my own MVC yet. I do see some benefits to writing your own MVC pattern. However, I also see the benefits of using a framework like CI that provides the tools / features right out of the box.

        I guess you would just need to weigh out the pro's and con's of your project and decide which would work better. I think you went with the right choice for your project being that you don't really need all the "bells and whistles" that come with a standard MVC like CodeIgniter.

        I've used CI and CakePHP in the past and found it to be a bit cumbersome at first. Once you understand the general workflow you can really take full advantage of writing solid applications / websites quickly.

        For those that want to learn and use an MVC ( Model / View / Control ) framework, I would recommend CodeIgniter first. It's light weight and offers excellent documentation. There's also a great community out there and video tutorials to pick it up quickly.

        1. scr3ws0f7 says:

          Okey.
          Thanks ! :)

  13. abr4xas says:

    interesting, but there is a chance to explain how I can use wordpress feed? thanks!

    1. Martin Angelov says:

      The easiest way would be to have a script run periodically, fetch and parse the RSS feed, and write it to the database. The rest is integrating it with the mobile site by adding new models, views and controllers.

  14. Jonathan says:

    very, very cool!

  15. Michael says:

    Wow! What a thorough and useful tutorial. No excuse not to have a mobile version of the site!

  16. Great tutorial as usual. Having my first look on jQuery Mobile.

  17. Jonas says:

    I get a problem, PHP Catchable fatal error: Object of class Category could not be converted to string. It's the class name strtolower that's causing it. Anyone else getting it?

    1. Martin Angelov says:

      Strange. Which version of PHP are you running?

  18. scr3ws0f7 says:

    Hmm,
    Yeaaa, cool.
    But..

    $db = new PDO(
    "mysql:host=$db_host;dbname=$db_name;charset=UTF-8",
    $db_user,
    $db_pass
    );

    $db->query("SET NAMES 'utf8'");

    Isn't enough that you create the PDO object with charset utf8 ? Why do you query the database again with the same charset?
    Hmm, hmm.

    1. Martin Angelov says:

      When I originally wrote this code (and copy-pasted it ever since), this proved the only way to force MySQL to switch the connection to UTF-8. I will run some tests the next time I use it.

      1. scr3ws0f7 says:

        Okey.
        Thanks for the answer.
        I was a little bit confused, so I asked my self if isn't that enough to create a PDO object with charset.
        Greetings!

  19. WhazeInc says:

    Yeah, thanks for this tutorial its helpful i also work like WebDeveloper this post will help me alot to understand structure in PHP & MysqL, am waiting for part 2 thanks.

  20. Srabon says:

    awesome tutorial :) ...just love it :)

  21. Jerome says:

    Good article, I just finished my new CMS/Framework based on HTML5/CSS3 with Mobile Browser Support, I will have a closer look into your tutorial!

    Cheers
    Jérôme

  22. Sophocles says:

    Hi,
    Congratulations for a very clever script.
    I am trying to adopt it, in order to query an already existing database which contains both English and Greek text, with “utf8_general_ci” collation, and I run into some very disturbing results when it was to show Greek characters.

    After experimenting a lot, since I don’t know php or mysql, I came up with the following changes that display Greek characters correctly. Eventually users that want to use non western European languages might find it useful.

    1st change was at “includes/connect.php”
    Line that was "mysql:host=$db_host;dbname=$db_name;charset=UTF-8", has changed to "mysql:host=$db_host;dbname=$db_name;",
    At the end of the try code block, I added the array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES \'UTF8\'') line of code and finally I deleted the $db->query("SET NAMES 'utf8'"); line.
    So the “connect.php” produces fine Greek characters and looks like this.

    try {
    	$db = new PDO(
    		"mysql:host=$db_host;dbname=$db_name;",
    		$db_user,
    		$db_pass,
    		array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES \'UTF8\'')
    	);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }
    catch(PDOException $e) {
    	error_log($e->getMessage());
    	die("A database error was encountered");
    }
    

    2nd change was a minor one. At “includes/views/_header.php”, I added the following line.

    Please revise and if necessary correct these changes. Once again congratulations for a great site.

    1. Guillaume says:

      Hi, I'm trying to do exactly that. Which line did you add in _header.php?

  23. mareno says:

    Exelent tutorial!
    I start to visit this blog for all time!
    Man you rock!
    awesome tutorial :)

  24. Dan says:

    Thank you very much for this tutorial :) It has been a great help!!!.. This is my first night learning about JQ mobile. Im excited to get onto the developement of our mobile site here very soon.

  25. Greetings - thanks for an awesome tutorial. The jMobil framework is awesome for creating a mobile website. Nevertheless, I use Slackware Linux with a compiled from source PHP-5.3.9 as well as mysql 5.5.20 Source distribution. In the connect.php file, I had to change "mysql:host=$db_host;dbname=$db_name;charset=UTF-8", to reflect "mysql:host=$db_host;dbname=$db_name;charset=utf8",. as my
    /usr/local/mysql/share/charsets/Index.xml listed charset name = utf8. After changing this UTF-8 to utf8, everything worked as explained. I hope that this will help some one who might otherwise abandon the project. Thanks again for the most elite tutorial.

    1. Zachary P says:

      Wow, Thank you! I was banging my head on the desk for an hour.

  26. DanO says:

    Hi,

    One question in the model file for category, how do the categories product count dynamically by querying the products table instead of using the 'contains' field in category table ?

    Thanks and nice tutorial, keep it up!

  27. Shakeel Ahmed S says:

    Thanks alot :)

  28. Fern says:

    I Solved my problem with this code before the conditional
    $category = isset($_GET['category']) ? $_GET['category'] : NULL;

  29. Chris says:

    Hi,
    i tried to update that for a detail view. I created the View "products" where i wanted to show specific on one Site a HTC or IPhone and so on. (Another Link that is on the Products)
    I rly dont get it... how should that work? My problem is that this Tutorial was the only one where i figured out to get data from a external database(mysql) into my jquery code. I tryed it with normal Php script but jquery / phonegap works local and there is no php support on the mobile device...
    Any idea?

  30. Lijesh says:

    Nice explanation.... Cool stuff....

  31. Simon Finney says:

    Hi,

    Great tutorial, I have a problem with the jQuery side of things though.

    I have jQuery functions attached to buttons I've created for adding items to the shopping cart but they only work once (for example when the page has loaded up) and not as you traverse through the website.

    Any idea on what could be happening?

    Thanks

  32. Bhavesh says:

    very, very cool!

  33. AJ says:

    Can anyone tell me where the HTML file is come from for the demo site?

  34. dennis says:

    very nice tutorial. It's much easier to follow tutorials that use real life examples. Thanks :)

  35. Senthil Kumar V says:

    It's truly fantastic and appreciable Martin. You are helping the developer community in a great way. Best wishes!!!

  36. Zmerli says:

    why it dosen't work on Trigger.io ? (load dynamically listview)

Add Comment

Add a Reply

HTML is escaped automatically. Surround code blocks with <pre></pre> for readability.
Perks:   **bold**   __italics__   [some text](http://example.com) for links