Free Online Courses for Software Developers - MrBool
× Please, log in to give us a feedback. Click here to login
×

You must be logged to download. Click here to login

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

How to create a dynamic webpage fetching data from a database in PHP

This article talks about the process to create a dynamic webpage that fetches data from a database with PHP.

Talking about the dynamic web pages in this article, they present an efficient technique so as to present data to the user. As far as the web designer is concerned, he just need to create the initial page basically to split the data into segments and add data to the database. The data is added to the database from which the web page draws its content.

Let us see step-by-step process to dynamically create a web page.

Step 1: Creation of an initial web page:

You need to enter the below code in order to create a standard XHTML document, adding the title, "Dynamic Webpages" between the <title></title> tags, as shown:

Listing 1: Creation of an initial web page

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<title>Dynamic Webpages</title>
</head>
<body>
</body>
</html>

Now save the file as "dynamicwebpages.php" and then upload it to your server.

Step 2: Connect to the database

Here in order to connect to the database, you need to enter the following code above the DOCTYPE statement. Enter the information with the capital letters:

Listing 2: Connect to the database

<?php
$dbh=mysql_connect ("SERVERNAME", "USERNAME", "PASSWORD")
or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("DATABASENAME");
?>

Step 3

Here it is required to type the beginning and ending PHP tags between the <body></body> tags:

<?php
?>

Step 4

The below code needs to be typed between the <?php ?> tags:

$query = mysql_query("SELECT * FROM table ORDER BY id");
while($r=mysql_fetch_array($query)) {
extract($r);
echo $variables;
}

Here you need to replace the "table" with the name of the table that is being fetching the information. If you want to display the data in manner of your choosing, substituting "$variables" with your column names, plus a dollar sign, you need to arrange the column names.

Step 5: Save and upload your document

This step is to save and upload the document and the page will render all the data in the database in one large column.

Step 6: Pagination the data

In order to paginate the data, please enter the below code between the second

Listing 3: Paginating the data

$rowsPerPage = 1;
$pageNum = 1;
if(isset($_GET['page']))
{
$pageNum = $_GET['page'];
}
$offset = ($pageNum - 1) * $rowsPerPage;

A variable called "$rowsPerPage" needs to be established with its value equal to one which will be utilized to limit the number of records in the database to one per page.

In addition to it, you need to establish another variable, "$pageNum" with its value again equal to one and come up with an "if" statement in order to create each page number.

Now there will be a last variable called "$offset" added so as to help modify the "select" query's number of records per page.

Step 7: Setting the Limit to Select Query

The following code shows to set the limit to the "select" query, after the "ORDER BY id" statement.

LIMIT $offset, $rowsPerPage

Step 8: Counting the number of Records in a database

The count of records that conform to the “select” query can be found out by typing a "count" query and then assign the results of the "count" query to the variable

Listing 4: Counting the number of Records in a database

"$numrows". Establish another variable, "$maxPage" and divide the value of "$numrows" by "$rowsPerPage". Round the results up to the nearest integer by enclosing the divisor with the "ceil()" function.

// how many rows we have in database
$query = "SELECT COUNT(columnname) AS numrows FROM table ORDER BY id ";
$result = mysql_query($query) or die('Error, query failed Part 2');
$row = mysql_fetch_array($result, MYSQL_ASSOC);
$numrows = $row['numrows'];
// how many pages we have when using paging?
$maxPage = ceil($numrows/$rowsPerPage);

Step 9: Creation of link to each page

  • Establish the variable "$self" that needs to be set equal to the current page by invoking the superglobal variable and its parameter $_SERVER['PHP_SELF'].
  • Establish a blank variable, called "$nav" and create a "for" loop setting the variable "$page" equal to one.
  • State that "$page must be less than or equal to the variable "$maxPage" and then set page to automatically count with the double plus signs.
  • Create an "if/else" statement where if the value of "$page" is equal to the value of the variable "$pageNum", then the variable "$nav" will equal the value of the variable "$page".
  • Else, the variable "$nav" will equal the dynamic link.

Listing 5: Creation of link to each page

// print the link to access each page
$self = $_SERVER['PHP_SELF'];
$nav = '';
for($page = 1; $page <= $maxPage; $page++)
{
if ($page == $pageNum)
{
$nav .= " $page "; // no need to create a link to current page
}
else
{
$nav .= " <a href=\"$self?page=$page\">$page</a> ";
}
}

Step 10: Creation of navigation links for the "first page", "last page", the "next record" link and the "previous record" link.

  • In case the variable "$pageNum" is greater than one, you need to create the dynamic links for the page number ($page), the "previous record" link ($prev) and the "first record" link ($first).
  • If the variable "$pageNum" has a value that is less than one, you need not create a link for we are on page one but create another "if/else" statement that would state that if the variable "$pageNum" is less than the variable "$maxPage", set the variable "$page" equal to the value of the variable "$pageNum" plus one. Create the dynamic links for "next record" and "last page".
  • If the value of the variable "$pageNum" is not less than the value of the variable "$maxPage", there Is no need to create the dynamic links, for we are on the last page.

Listing 6: Creation of navigation links

if ($pageNum > 1)
{
$page = $pageNum - 1;
$prev = " <a href=\"$self?page=$page\">[Prev]</a> ";
$first = " <a href=\"$self?page=1\">[First Page]</a> ";
}
else
{
$prev = ' '; // we're on page one, don't print previous link
$first = ' '; // nor the first page link
}
if ($pageNum < $maxPage)
{
$page = $pageNum + 1;
$next = " <a href=\"$self?page=$page\">[Next]</a> ";
$last = " <a href=\"$self?page=$maxPage\">[Last Page]</a> ";
}
else
{
$next = ' '; // we're on the last page, don't print next link
$last = ' '; // nor the last page link
}

Step 11: Print the navigation links and close the connection to the database:

Listing 7: Print the navigation links

// Print the navigation links
echo $nav . "<br />";
echo $next . "  " . $prev . "<br />";
echo $first . "  " . $last;
// Close the connection to the database
mysql_close();

Conclusion

We have come to end to this tutorial where we learnt to create dynamic web pages and connecting the navigation links to the original page to the dynamic pages that are created by dividing the records in the database table, or rows, into pages.



I am a software developer from India with hands on experience on java, html for over 5 years.

What did you think of this post?
Services
[Close]
To have full access to this post (or download the associated files) you must have MrBool Credits.

  See the prices for this post in Mr.Bool Credits System below:

Individually – in this case the price for this post is US$ 0,00 (Buy it now)
in this case you will buy only this video by paying the full price with no discount.

Package of 10 credits - in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download few videos. In this plan you will receive a discount of 50% in each video. Subscribe for this package!

Package of 50 credits – in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download several videos. In this plan you will receive a discount of 83% in each video. Subscribe for this package!


> More info about MrBool Credits
[Close]
You must be logged to download.

Click here to login