Web International Awards

payday loan

6

APR 2010 7

Building a live news blogging system in php. Spiced with HTML5, CSS3 and jQuery [part II]

In the first article in this series we've looked upon coding a html5 layout, styling it with css and adding some basic jQuery functionalities to it. In this second part we're going to tackle something a bit more advanced. We're going to check out our database model, create some SQL queries. Then we're going to move to the php side of the app and see what needs to be done and start building our app's foundation.

The database model

I am not going to throw up a UML design over here. I am just going to code the tables and then explain what the attributes are used for. Having that said, here's the SQL we're going to use to create the database which we're going to use with our app.

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
27
CREATE TABLE News
(
	id INT(10) NOT NULL AUTO_INCREMENT,                           
	title VARCHAR(150),
	body text,
	owner VARCHAR(52),
	publishing_date DATE,
	publishing_time TIME,
	category VARCHAR(52),
	PRIMARY KEY(id)
);
 
CREATE TABLE Users
(
	id INT(10) NOT NULL AUTO_INCREMENT,
	username VARCHAR(52),
	email VARCHAR(52),
	password VARCHAR(52),
	PRIMARY KEY (id)
);
 
CREATE TABLE Categories
(
	id INT(10) NOT NULL AUTO_INCREMENT,
	name VARCHAR(52),
	PRIMARY KEY(id)
);

As you can see the model isn't complicated at all. The Users table contains information about administrators, as described in the first article of the series, the Categories table will contain nothing but the categories to which each news can be assigned, while the News table will contain the actual news.

MySQL explained

  • lines 3,15 and 24: Each element stored in our database, be it user, news or a category needs a unique ID. These lines, together with lines 10,19 and 26 will ensure that we have unique IDs for each element
  • line 4 to 8: Each news will have a short title, body copy, a publisher (owner), a publishing date and publishing time
  • line 9: The ID of the category to which the news belongs to
  • line 16 to 18: Basic user information, nothing special
  • line 25: Each category has a name besides the unique ID and nothing else

Basic app functionality

The app we're building isn't that complicated. However, we need to know exactly what we need to code. First of all, we'll need login and logout methods, for our admins to be able to do their work. We need an index page which will display all news. We also need the ability to add, edit and delete news which gives us 6 different methods. Therefor we need 6 different files.

login.php

Our login file will process the data sent by pressing the login button inside the login window we coded in the first part of the article. This means we only need to process a username and a password, compare it with data in our database and see if we have a valid login or not.

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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
<?
	session_start();
	ob_start();
	include("includes/functions.php");
 
	if (!isset($_POST['username'])) $er='1';
		else $username = $_POST['username'];
	if (!isset($_POST['password'])) $er='1';
		else $password = $_POST['password'];
	if($er==1)
	{
		header("location: index.php?error=Please input username and password");
	}
	else
	{
		$chars = preg_split("//", $password, -1, PREG_SPLIT_NO_EMPTY);
		$i = 0;
		$ok = 1;
		while($chars[$i])
		{  
			$char=$chars[$i];
		    $x = checkvaliddata($char);
			if($x==0) $ok = 0;
			$i++;
		}
 
		$chars = preg_split("//", $username, -1, PREG_SPLIT_NO_EMPTY);
		$i = 0;
		while($chars[$i])
		{
			$char=$chars[$i];
			$x = checkvaliddata($char);
			if($x==0) $ok = 0;
			$i++;
		}
 
		if($ok==1)
		{
			$password = md5($password);
			$sql=mysql_query("SELECT * FROM Users WHERE username='$username' AND password='$password'");
			$login_check=mysql_num_rows($sql);
			if($login_check>0)
			{
				while($row = mysql_fetch_array($sql)) 
				{
	   				foreach( $row AS $key => $val )
						{
	       					$$key = stripslashes( $val );
	    				}
					session_register('logged_in_key');
					session_register('username');
					$_SESSION['username'] = $username;
 
					ob_end_clean();
					header("location: index.php?msg=Logged in succesfully.");
	    		}
			}
			else 
			{
	        	ob_end_clean();
	        	header("location: index.php?error=Invalid login data.");
			}
		}
		else
		{
	        header("location: index.php?error=Hacking attempt!");
		}
	}
?>

login.php explained

  • line 2: In order to handle user login we need to be able to store php sessions and check them within the entire app. This means that each of our files need to have session_start() on the first line
  • lines 3, 12,55,61 and 66: The header() allows us to send a raw HTTP header after the page has loaded. We need to do that because our login page will not display any messages, but redirect the user according to his/hers login data. The ob_end_clean() function erases the output buffering so that when we call the header() function we don't see an ugly message that goes with "Headers already sent"!
  • lines 6 to 9: We get the data the user has typed in the login form
  • lines 10 to 13: We check if the login form was submitted and we don't get bogus requests. If the webform was not submitted and this may be a remote request, we redirect the user back to the starting page of our app and display a message. As you can see the error message is embedded in the URL itself (line 12)
  • lines 16 to 35: If the request of the login page is valid we check if data users typed is valid, preventing MySQL injections and more
  • lines 16 and 27: We get the user's typed data as strings, and we need to check out each character against a set we accept as valid input. We use the preg_split() function to break the input string to characters
  • lines 63 to 67: If data is invalid (unexpected chars were found) the user is redirected to the index page and a hacking attempt message displayed
  • lines 39 to 62: We've reached the part where input data is valid. As passwords are encrypted into our database, we encrypt the inputed password, then check the username & password combination for validity. If login_check variable is positive, we've got a valid login. If not, username and passwords don't match anything in our database.
  • lines 44 to 49: If we have a valid login, we used named variables to get all data about the user from the database, then create required sessions.
  • line 50: This session key should be unique, and hard to guess for security reasons. We're going to use this session with a unique name to check if users are indeed logged in or not.

logout.php

The logout.php file is pretty simple and has one job: to distroy user sessions. Once these sessions are destroyed, the user no longer appears as logged in into our app. Here's the code:

1
2
3
4
5
6
7
<?
	session_start();
	ob_start();
	unset($_SESSION['logged_in_key']);
	ob_end_clean();
	header("location: index.php?msg=Successfully logged out");     
?>

The problem is that we can't use the session_destroy() function as we don't want to destroy all sessions. We use sessions and jQuery with ajax to make things sexy in our app. Therefor, we can't destroy all sessions when users log out. We need to destroy the one that told us that the user is actually an administrator. And that's what the unset() function does.

Issues with the login ?

We've finished the login.php file, but as you can see before running a query against our database we do not connect to it! If you check out the login script you'll see that we included the functions.php file. We did this because there are some functions that will be used throughout the app and we need to keep them all in one place. One such function may be checkvaliddata which we used to validate user input.

functions.php

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
function db_connect(& $db) // used to connect to the database
{
	include("config.php");
	$db=mysql_connect($dbhost, $dbuser, $dbpass) or die ('Cannot connect to server' . mysql_error());
	mysql_select_db($dbname);
}
$db;
db_connect($db);
 
function checkvaliddata($char) 
{
  	$salt = "aAbBcCdDeEfFgGhHiIjJkKlLmMnNoOpPqQrRsStTuUvVwWxXyYzZ0123456789.@ ";
    $i = 0;
	$c = 0;
	while ($i <= 63)
	{
	    $tmp = substr($salt, $i, 1);
	    if($tmp==$char) $c=1;
	    $i++;
	}
	return $c;
}

functions.php explained

  • lines 1 to 8: The db_connect function is used to connect to the database. The included config.php file contains our database information. mysql_connect() is used to connect to the mysql server and display a message if failure occurs while mysql_select_db() is used to select our database once we successfully connect to the server. As you can see, there's a call to our connection function. This way every time we include the functions.php file in our app we automatically connect to the database
  • lines 10 to 21: This function is used in the login.php file to validate data the users have typed in the login form against a set of accepted characters. The function returns 1 if data is valid and 0 otherwise

The login form

We only coded the layout and structure of our app in the first part of the tutorial. Our login form is not visible to site visitors. Therefor we need to add some jQuery code for our form to pop out when visitors click the login link located in the footer our webapp.

login window overlay

Adding some more jQuery to it

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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
function overlay()
{
	//create the overlay menu
	var opacity = '0.9'; // can be up to 1
 
	$('a[name=modal]').removeAttr('href');
	//javascript on, no need to redirect to a link here
	$('a[name=modal]').click(function ()
		{ 
 
		var rel = $(this).attr('rel');
 
		var modal_content = $('.'+rel).html();
		$('#overlay-content div').html(modal_content);
 
		var maskHeight = $(document).height();  
		var maskWidth = $(document).width(); 
		var windowHeight = $(window).height();  
		var windowWidth = $(window).width(); 
		var contentWidth = $('#overlay-content').width(); // width
		var contentHeight = $('#overlay-content').height(); // and height of content area
 
		//Set height and width to mask to fill up the whole screen  
		$('#overlay-mask').css({'width':maskWidth,'height':maskHeight});
		$('#overlay-mask').css('opacity',opacity);
		$('#overlay-mask').css('display','block');
 
		// put the overlay content area in the center of the window
		$('#overlay-content').css('display','block');
		$('#overlay-content').css('left',(windowWidth-contentWidth)/2);
		$('#overlay-content').css('top',(windowHeight-contentHeight)/2);				
	});
 
	// move overlay content to center of the window
	$(window).resize(function () { 
		var maskHeight = $(document).height();  
		var maskWidth = $(window).width(); 
		var windowHeight = $(window).height();  
	 	var windowWidth = $(window).width();
		var contentWidth = $('#overlay-content').width(); // width
		var contentHeight = $('#overlay-content').height(); // and height of content area
		//Set height and width to mask to fill up the whole screen  
		$('#overlay-mask').css({'width':maskWidth,'height':maskHeight});
		$('#overlay-content').css({'left':(windowWidth-contentWidth)/2});
		$('#overlay-content').css({'top':(windowHeight-contentHeight)/2});
	});
 
	var $scrollingDiv = $("#overlay-content");	
	$(window).scroll(function() {			
		$scrollingDiv
			.stop()
			.animate({"marginTop": ($(window).scrollTop()) + "px"}, "fast" );			
	});			
	$('#overlay-mask').click(function () { $('#overlay-mask').css('display','none'); $('#overlay-content').css('display','none'); $('#overlay-content div').html(""); });
	$('.close').click(function () { $('#overlay-mask').css('display','none'); $('#overlay-content').css('display','none'); $('#overlay-content div').html(""); });
}

jQuery explained

I've embedded the login and edit/add news forms into an overlay to declutter the user interface. The opacity variable is a real number in the [0,1] range which sets the transparency of the overlay mask. 1 means it's opaque and 0 means it's transparent.

  • line 6: Prevents default behavior when login link or add/edit links are clicked
  • line 11: We use the rel attribute to map on each overlay() call which content should be loaded (login form/edit form or add news form). The rel attribute should coincide with the class attribute of the webform itself (login, editnews or addform)
  • lines 13 and 14: The relevant content according to function call is copied to the overlay content div
  • lines 16 to 21: We want our overlay to be centered in the browser window. Therefor, we calculate the widths and heights of the browser window, content area and overlay mask which spans all over the page
  • lines 24 to 26: We set the dimensions of the overlay mask and its color
  • lines 28 to 31: Display the overlay content div and put it in the middle of the browser's window
  • lines 35 to 46: Recalibrate widths and heights and reset the position of the overlay if the browser's window is resized
  • lines 29 to 53: Do the same if the users scrolls down or up the window
  • lines 54 and 55: Add close buttons and functionality so that users can get the overlay out of their face

End of part II

And we've reached the end of our second part. We've seen during the first part how we can create a pretty complex layout using html5, how to style it using CSS3 and we've also seen how we can achieve equal heights for two independent columns using javascript (we've also got a dedicated article about this, and we're using the same technique for this site too). Today we saw how our database model looks like, how we can connect to our database and how we can allow our administrators to login and manage the app. We also added some jQuery to create some nive overlays.

I hope you really enjoyed this article. Please share the link with your friends. You can stay updated to new content via our RSS feed or by email.

Further reading

Published on Tuesday, April 6th, 2010 at 8:26 pm in tutorials.

About Bogdan Pop

Bogdan Pop is a young Romanian entrepreneur who runs WebRaptor. He is a web developer with awesome design skills, who enjoys writing about everyday's work and usability. He relaxes by taking photos every once in a while and by mixing french electronic music. Connect with him via Twitter.
 
  1. Nicula Vlad says: April 11th, 2010 at 10:19 am

    Hi, I must say I really like this tutorial! I haven’t coded it yet, but the first time I get a few days free, I’ll look into this and take it to the next level!

    I really appreciate that you took the time to write such a tutorial. PHP from scratch is not always a hot topic these days!

  2. Bogdan Pop says: April 11th, 2010 at 12:14 pm

    Hi Vlad,

    Nice to see you found the tutorial useful.

    I already have planned to enhance this series with additional back-end languages. Based on the popularity of the series I think PHP tutorials are still needed.

  3. php tuts says: April 22nd, 2010 at 8:12 pm

    Nice post. Thanks for sharing

  4. kevin says: March 19th, 2011 at 2:04 am

    Hey

    nice tutorial, but i’m a little stuck. Picking up jquery for the first time so. Your tutorials sais “Adding some more jQuery to it” but where exactly do I add that code??

    Thanks

  5. Bogdan Pop says: March 21st, 2011 at 10:54 am

    Hello Kevin,

    you need an external javascript file and append that function to the top of it. A good idea would be adding it into the javascript file create at first step of the tutorial.

    If you have any more trouble finding where each piece of code goes you can jump to the source codes of the tutorial and take a peek.

  6. Kyle Hindle says: July 3rd, 2012 at 12:53 am

    Hi there. I have done everything that you have said and shown.

    But the only problem I have got is that I have added the database to it, but when I enter my Username and Password that I put into the Database, it keeps showing as error when the username is right on the database.

  7. Bogdan Pop says: July 13th, 2012 at 11:21 pm

    Hi Kyle,

    Did you encrypt the password field using md5 function? If you do not encrypt your password the login will always be invalid





Save time next time! You won't have to fill out all these fields again. Register in just a few clicks and then login.


If you do not have a username, you can register in just a few clicks.