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 [...]

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

Create a new database named mydb

Create a new table named article with 4 columns

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

• 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)

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,…)

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!

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.

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.

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);

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

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;

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

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.

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

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);

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"));

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;
?>

How your site should look like for now:

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 **/

The final result of the pagination should look like this:

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.