Dynamic website part 2

by krike in PHP & MYSQL / Tutorials on 05 Dec 2009

Download Files

This entry is part 2 of 3 in the series Dynamic web tutorials

Welcome to the 2nd part of our “dynamic website” tutorial serie

In this part we will be creating a database and configure a basic table for articles. We will then create the basic configuration files to connect the files with the database and another one to make our lifes easier, some security will be implemented to protect those configuration files.

Finally we’ll create a page artikel.php and fetch the articles from our database and implement a simple pagination.

Ok so let’s start

The database

Make sure you have started wamp (or mamp for mac), left mouse click on the wamp icon and select phpmyadmin

im1_wamp

Create a new database named mydb

im2_create_database

Create a new table named article with 4 columns

im3_create_table

Now fill in the details like below and then I’ll explain more in detail

im4_create_table_columns

  • id : This is the id of the articles, this will be unique. It will be used to identify the article. It must be a primary key because it is the most important column of our table and you need to set this to primary key to turn on auto_increment. What this will do is fill in the id for you. When you add a new article it will increase your id with 1 (ie; article 1 will receive id 1, article 2 will receive id 2).
  • title: This is the title of the article. We set it to Varchar(80). Varchar or char is used for characters (text), the difference between both is that when we set varchar to 80 (which is the maximum amount of charachters which will be stored) it will only take space for that amount of characters you really stored. So if you have a title of only 30 charachters, varchar will only use the space for those charachters while char will still use the maximum of 80 characters, no matter how many characters you have stored. So think performance and go for varchar.
  • body: This will be the article itself. An article can be very long so we will set it to text. Text does not have any restriction in the amount of charachters. Setting a max value won’t matter so leave it blank.
  • date: It’s a good thing to know the date your article was posted on. You would think we need to set this column to date or datetime. If you were thinking this good job, 90% of the time it’s what you need to choose. But here, while using php, we will still set it to int because php provides us with 2 awesome functions, date() and time(). Both function gives us way more possibilities then setting the date column of the database to date or datetime.

Let’s now insert some date, above in the tabs go to sql, paste the following code and execute

INSERT INTO `article` (`id`, `title`, `body`, `date`)
VALUES
(NULL, 'My first article', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Fusce enim sapien,
  condimentum ac pharetra in, aliquam vel ante. Sed faucibus enim vehicula ligula tincidunt condimentum.
  Suspendisse gravida convallis pretium. Mauris eu erat neque. Aliquam non odio vel purus rutrum elementum.
  Maecenas varius consequat est, in pretium tellus rutrum sed. Nunc aliquet urna id dui ornare posuere. Cras
  sit amet dui odio, ut placerat odio. Nulla non dolor massa. Nam gravida velit ut ligula venenatis feugiat.
  Nunc nibh neque, aliquam a dignissim nec, porta ac orci. In consequat, enim ac sollicitudin viverra, justo
  lectus vehicula sem, at rhoncus purus diam non arcu. Integer rhoncus, lectus ut consectetur sollicitudin,
  neque est posuere odio, ut ornare turpis justo sed quam. Vivamus non diam augue. ', '1247181438'),
(NULL, 'My second article', 'Aliquam commodo porta venenatis. Fusce justo felis, volutpat sit amet porta
  bibendum, interdum non lectus. Maecenas lobortis placerat ligula et vehicula. Mauris id mattis nisi. Mauris
  luctus nulla nec orci ultricies quis tincidunt turpis bibendum. Fusce vestibulum mi id dolor convallis ut
  facilisis sem feugiat. Nunc nec arcu vel massa luctus rhoncus porta sagittis nunc. Integer faucibus pretium
  justo ac scelerisque. Sed ut augue sit amet urna faucibus molestie in nec ante. Donec sit amet nisi
  ipsum. ', '1247181438'),
(NULL, 'My third article', 'Aliquam commodo porta venenatis. Fusce justo felis, volutpat sit amet porta
  bibendum, interdum non lectus. Maecenas lobortis placerat ligula et vehicula. Mauris id mattis nisi.
  Mauris luctus nulla nec orci ultricies quis tincidunt turpis bibendum. Fusce vestibulum mi id dolor
  convallis ut facilisis sem feugiat. Nunc nec arcu vel massa luctus rhoncus porta sagittis nunc. Integer
  faucibus pretium justo ac scelerisque. Sed ut augue sit amet urna faucibus molestie in nec ante. Donec sit
  amet nisi ipsum. ', '1247181438');

Configuration Files

In your root folder create a folder named config (for more security you can give it a more complex name so that nobody can find it)

im5_create_config_folder

In that folder create 2 new files

  • config.inc.php – This file will contain the link to our database
  • configuration.php – This file will contain some general information about our website (ie, sitename, siteurl,…)

im6_create_2_new_files

Add the following code to your config.inc.php, this is the code to connect to your database. As you can see we use mysqli and not mysql, mysqli is much more secure then mysql.

<?php
$db_host = "localhost";
  $db_username = "root";
  $db_password = "";
  $db_database = "mydb";
$link = mysqli_connect($db_host,$db_username,$db_password) or die("Cannot connect");
  mysqli_select_db($link, $db_database) or die("Cannot select database");
  ?>

In the configuration.php we will store the site title, description and other things into re-usable variables. Add the following code into configuration.php

<?php
//Your sitename
$sitename = "My website name";
//Your site slogan
$siteslogan = "My website slogan";
//the site url should end with a slash
$siteurl = "http://localhost/dynamicweb"; //when working local your path should look like http://localhost/nameofyourmap
?>

If you can think of any other configuration setting/variable that might be usefull right now for you, go ahead and add it. In the next tutorials we might need to add a few other things.

Let’s secure this folder now, we don’t want people browsing our map so a good solution would be to block the access to the folder. right mouse click -> new file and name the file .htaccess
Don’t forget the period in front of the name!

im7_htaccess

Open it and add the following code to it. This will prevent any access to that folder. Browsing for the folder in your browser should give you an error (permission denied).

deny from all

Now go to header.php and at the top include config.inc.php and configuration.php.

The articles page

Create a new file named articles.php, include then the header.php and the footer.php.

im8_includes_article_file

Now it’s time for the fun part, query the database to display the articles. Here are the steps in pseudocode (pseudocode means we will right in human language what we will be coding).

  1. query -> Select all the articles from our database
  2. See how many rows were returned
    1. If none was returned display “no articles in the database” message
    2. If rows were returned, loop through them and display on the page (Step 3)
  3. While there are rows, fetch/get the data and print them on the screen.

So first thing, add a query under the header include. Unlike mysql, mysqli query requires 2 parameters. The first parameter is the connection to the database (see config.inc.php).

$sql=mysqli_query($link, "SELECT * FROM article");

We use sql as variable name but we can call it whatever we want. For example $query_articles.

im9_first_query_articles

Next let’s see how many rows were returned, the only parameter you should pass to the mysqli_num_rows() function is the variable which contains the result of the query. Here we pass the variable $sql.

$sql_num=mysqli_num_rows($sql);

im10_num_rows

If you echo out $sql_num you should see in your browser the amount of rows returned.

im11_result_num_row

Let’s build the code who will check if there are any rows returned or not. Copy paste the following code into articles.php

//if the result of the function mysqli_num_rows IS null then show a message
  //when we store something into a variable we use = , but when we want to check/compare for something we use == or when comparing strings ===
  if($sql_num == 0):
//so if there is nothing show the message
?>
<h1>OOPS!</h1>
<p>We are terribly sorry but it looks like we have no articles in our database yet! Check back later.</p>
<?php
else:
//else if there is something in the database loop through the articles
endif;

im12_if_else_nothing_database

As you can see I don’t use curly brackets to open and close my if-lus but at the end, where I open my if-lus, I add a colon (same for the else) and I close my if-lus by typing endif;It’s a new way of typing an if-lus and it is used a lot in wordpress (this also works for while and foreach statements)

Ok so let’s fetch/get the articles now and display them on the screen, add the following code between the else: and endif;

//while there are rows print them on the screen
while($result = mysqli_fetch_object($sql)):
?>
<h1><?php echo $result->title; ?></h1>
<p><?php echo $result->body; ?></p>
<p><strong><?php echo date("d M Y",$result->date); ?></strong></p>
<?php
endwhile;

Same as mysqli_num_rows(), mysqli_fetch_object() requires the same parameter. Then we specify which part we want to display where.
ie: The title; $result->title – where title is the name of the column in your table

im13_loop_throug_articles

Now you can see why we didn’t set the date column of our table to date or datetime. The date function allow us to display the date and even hour the way we want. You could for example display the date as 12/05/09 or 5 Dec 2009 without changing anything to the database, it’s the first parameter which tells how to display the date. In our code it’s “d M Y”, so small d will display the numeric date, capital M will display the first 3 letters of the month and capital Y will display the year (2009 not 09).

If you want to know which options are made available to display the date go to http://www.w3schools.com/PHP/func_date_date.asp

If you wonder how we generate the date format we stored in the database (1247181438), you can get this format by using the time() function (http://www.w3schools.com/PHP/func_date_time.asp).
We’ll use that function when creating the page to insert a new article in the database. But if you want to check it out now just add the following code somewhere on your page:

echo time();

Pagination

Create a new folder and name it classes. Later in this tutorial serie I will teach you how to make your own classes but for now just download the pagination class provided for this tutorial (see top -> download source file). Once you have downloaded the pagination class place the file in the newly created classes folder.

im14_add_pagination

Include this file in your header.php under the configuration includes.

im15_include_pagination

Above your query you need to add the following code:

//PAGINATION SETTINGS
  $page = 1;
  $size = 2;
  if (isset($_GET['page'])){
$page = (int) $_GET['page'];
}
//Pagination class
$pagination = new Pagination();
//here we use the variable $siteurl which is defined in the configuration.php, should you ever use mod_rewrite to rewrite your url's
//it would be a good idea to add the $siteurl variable to your urls, same for images or links to your styles and scripts
$pagination->setLink($siteurl."articles.php?page=%s");
$pagination->setPage($page);
$pagination->setSize($size);
$pagination->setTotalRecords($total_records);

im16_add_pagination_settings

The only thing you will need to edit is the variable size, this variable specify how many articles should be shown each page. For now set it to 2 to test out the pagination, later you might want to change it to 10 or 15. Another setting to change if you need the pagination elsewhere is the $pagination->setLink() function, you will need to change articles.php to whatever the name of your new file is. But for now you don’t need to change it.

Now above all of this you will need to add the same query as we made earlier, the pagination needs the total number of articles. Copy paste the following code above the pagination settings:

//the pagination requires the total amount of articles
$total_records = mysqli_num_rows(mysqli_query($link, "SELECT * FROM article"));

im17_total_articles_query

Now add the following code to the first query we made. This will set the limit of rows that needs to be returned by the query. The limit depends on what number you have stored in the variable $size.

. $pagination->getLimitSql()

Now under the endwhile; add the following code which will generate the pagination links.

>?php<br />
$navigation = $pagination->create_links();
echo $navigation;
?>

im18_query_limit_result

How your site should look like for now:

im19_final_result

You don’t need to add this code into a div, it will be done for you.

You can now style your pagination as you please. I have allready created the basic required styles for you:

.pagination
{
height:20px;
margin:20px 0;
}
.pagination ul li
  {
  float: left;
  display:inline;
  padding:2px 4px;
  background:#f5f5f5;
  border:1px solid #949494;
  margin:0px 5px;
  }
.pagination ul li a
  {
  color:#949494;
  }
.pagination ul li.currentpage
  {
  float: left;
  display:inline;
  padding:2px 4px;
  background:#f5f5f5;
  border:2px solid #5c5c5c;
  }
.pagination ul li.currentpage a
  {
  color:#949494;
  font-weight:bold;
  }

As we are trying to keep the css structured I suggest you add these styles under /** Basic styles **/ but before /** header **/ and add another commented title /** General sytles **/

This is because you might need to add the pagination somewhere else on your site, therefore you best add the styles under /** General sytles **/

im20_add_pagination_styles

The final result of the pagination should look like this:

im21_pagination_final_result

Conclusion

So that’s the end of the 2nd part, we learned how to create a new database and table, we then queried the database for articles and displayed them using the while statement. We then finally implemented a simple but kick ass pagination.

Series Navigation

<< Dynamic website using php – part 1Dynamic website part 3 – inserting data into a database >>

Written by krike

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam sit amet nisl nisl. Ut interdum libero vitae quam ultricies et lacinia elit aliquet. Praesent tincidunt, sem tempus feugiat feugiat, turpis tellus scelerisque erat, sit amet feugiat neque arcu ac lectus. Sed at mi et elit interdum scelerisque vitae eu felis.

krike has written 77 posts.

  • Pingback: Tweets that mention Dynamic website part 2 | CMS tutorial site -- Topsy.com()

  • Pingback: Dynamic website part 2 | CMS tutorial site Cms Made Simple()

  • Pingback: Dynamic website part 2 | CMS tutorial site Hello CMS - the best cms website()

  • mark

    great post as usual

  • Cris

    hi there…do you have the download link for pagination.php? I couldn’t find it anywhere. I like the tutorial, but i can’t finish it. Thanks

    • @Cris: You can download the sourcefile for this tutorial at
      We just updated the wordpress layout, and thanks to your comment we noticed we forgot to add the source/demo link at the top of the article.

  • pless84

    Great tutorial, alas I have a problem with the pagination. When i click next I get a 404?

    • @pless84: glad you like it, the links of the pagination are generated by the class itself. is there a link to your site where I could have a closer look to see what’s giving you this error?

  • pless84

    I have it at pless84.dk/cms/article.php

  • pless84

    nevermind I got it, didn’t edit the configuration.php 🙂

    • @pless84: glad you could find it yourself. This means you are on the good path of creating dynamic websites because debugging and finding your own mistakes is something you will do quite a lot 🙂

  • Chris

    Wow, nice! thanks alot.
    Will there be a part 3?
    and how can i import post thumbnails easily? (:

    • @Chris I will try to create part 3 and 4 as soon as possible, which will include adding new posts with thumbnails and maybe other fields and 4 will be about creating a rating system. You can subscribe to the blog rss feed or follow on twitter (@cmstutorial) to get nodified about the new tutorials

  • Chris

    sorry, one more question. how can i make new posts easily? (like in wordpress)

  • Cris

    @Krike Cool, i’m waiting too for the 3rd and 4th part of this tutorial. 1st and 2nd part really helped me a lot understanding how to build a dynamic site. Thanks!!!

    • @Cris you are welcome

  • Chris

    Ok, thanks. because i dont want to run my whole website on wordpress or so, i just want a blog kinda systemfor my portfolio page wich shows a title, image (& maybe a rating) & in the single page it shows multiple images, description etc. but i dont have alot php knowledge. Thanks for these tutorials (: & i will deffently subscribe!

    • @Chris It’s not that difficult, I’ll try to write that in a tutorial asap. You should see these as well -> . These won’t teach you what you need know to create your blog but it’s defenitly the best php video tutorials which will teach you anything you need in php. It will give you a very good basic to understand the tutorials that are coming but also how to develop php websites 🙂

  • Chris

    Oh, thanks! I will check them out.
    I will also recommend this tutorial/website to any webdesign friends i know and i will link to this website whenever my website is done (: Thanks alot. Cant wait till part 3 and 4.

    • @Chris you are welcome 🙂

  • As for adding htsccess filoes to your cfg directory you can do that in a much cleaner way. One way is to place you config folder outside your document root – E.g. outside public_html. Another is disbale directory browsing globally from an htaccess file in your doc root.

    Also, for the page count, your running an sql query for each page. THis isn’t required as you cache the page count variable in a session. E.g.

    if (isset($_SESSION[‘row_count_blog’]) {
    $num_rows = $_SESSION[‘row_count_blog’];
    } else {
    $num_rows = mysqli_num_rows(mysqli_query($link, “SELECT * FROM article”));
    $_SESSION[‘row_count_blog’] = $num_rows;
    }

    Just my input 🙂

  • Mark

    Great tut! Learned a lot.
    It would be fantastic if you could made a next tut about
    a platform by which wou can update the database as a sort of texteditor. This way your dynamic website tut 1 & 2 could be used as a usefull tool by which clients can update there own website.

    • @Mark: part 3 (which covers exactly what you are asking) is on the way

  • Mark

    Magnificent! I am looking forward to it…

    • @Mark here you go, just published: http://blog.cmstutorials.org/reviews/general/dynamic-website-part-3-inserting-data-into-a-database

  • Mark

    Tnx for sharing! Very usefull tutorial

    • @Mark you are welcome

  • John

    Hi,
    I downloaded your files because mine wasnt working and when I go to articles.php it displays the first 2 articles from my database but when i click the next button it cant find that page i.e. articles.php?page=2

    • @John: so does it work? did you figure out what was wrong?

  • leo

    hy Krike,

    just finished the tutorial. I have only included confi.inc.php and $siteurl variable .
    My article page looks like yours sort’a but when i click next or 2 i get an error..404 not found…
    what could it be..maybe the pagination class??

  • leo

    Hy again,

    fixed it. the siteurl was one folder up. So when u click next it cant find any pages…so you get a 404..heheh
    The tutorial is a blast..

    Thank you

    • @leo: Sorry for my late response, been very busy lately. Glad you could fix it 🙂

  • Paul

    How do I change the order in which the articles are displayed? Currently they are in order of id. (1, 2, then 3..etc.) I want them to be the opposite (3,2, then 1) so that they are show in order of recently updated.

    amazing tutorial!

    Paul

  • Paul

    Actually. I added this to my query

    $sql=mysqli_query($link, “SELECT * FROM articles ORDER BY create_date DESC “. $pagination->getLimitSql());

    and it worked. Is there a better way to do it?

    • @Paul: No your way is perfectly fine :), some people order by id others by date and you can use ASC ord DESC to change the order in which they are being displayed.

  • Paul

    @krike, thanks for the response! I was also wondering if you will be creating more tutorials? (friendly URLs, creating/altering a pagination file) – looking forward to more!

    Again, this is the best tutorial!

    • @Paul: Yes there will be more, i made a list. It’s not a definitive list, a few things could still be changed

      Part 4 – Delete articles
      Part 5 – Classes& objects
      Part 6 – Registration/Login
      Part 7 – Usergroups
      Part 8 – Administration panel
      Part 9 – Optimization (css-sprite, minify javascript, …)
      Part 10 – Clean URL’s & regular expression

  • Ferose Ali

    hey dude,I can’t find the pagination class file,where is that?

    • krike06

      Sorry I forgot to update a few posts when migrating to the new wordpress version. You will now find at the top a download button where you can download the pagination class.