Knowledge

PHP Fundamentals: Pull and display data

Published on June 08, 2012 under How-To, Web Development

PHP pull data from database

Last time, I started off this series showing you how to set up and put data into a MySQL database using the tools normally found in any normal web hosting plan. Now we're going to move forward and actually get into writing PHP code.

Today we're going to connect up to your MySQL database, pull some data, and display it on a PHP page in a myriad of fashions that you might encounter in your own work. I've included example files here and at the end of this article.

Getting started

I've taken the liberty of creating a MySQL database table for you to use in this exercise. Like I showed you in the introduction, take the exported sql in the files and run one of them. You'll notice both the display.php and dbTableSetup.txt both have "alt" versions. I've noticed some hosts have their databases set to not accept quotation marks or apostrophes without slashes preceding them. If you get errors when running dbTableSetup.txt, try running dbTableSetup_alt.txt. If it works, then use display_alt.php in this exercise.

Setting up your database connection

The next step is to set up the connection to the database you set up. Open the file dbConnect.php. You should see the anatomy of a MySQL database connection using PHP:

$Host = "Host_Address";
$User = "Database_User";
$Password = "Database_Password";
$Link = mysql_connect ($Host, $User, $Password);
mysql_select_db('Database_Name', $Link);

What's going on is you're setting up the variables that will go into the mysql_connect function and then be used in the mysql_select_db function. Both functions are not things you need to write, as they are native in PHP.

Replace Host_Address with the address your host will give you for the database. Do not remove the quotation marks or the variable names in any of these. You should have received the host address when you set up the database. If you missed it, then go back to the database section in your hosting and it should show it to you. If you did not receive any such address, then use localhost. Some hosting will simply make it where you can connect anywhere, but the files have to be on their servers.

The rest is self explanatory. Put the database user name and password in their appropriate spots, and the name of the database in the mysql_select_db area. Again, only replace Database_Name. Do not delete the single quotation marks.

If you look at your display php page, you'll see I've used an include tag calling for dbConnect.php:

include("dbConnect.php");

This is a habit I'd want you to get into when you do PHP. I start and finish the page completely with connecting up to the database, and then closing the connection with the mysql_close function, also native to PHP. The idea is it leaves things so you don't have to put all the information found in dbConnect.php into every spot you want to connect.

Includes are wonderful even for putting repetitive layout items in a site. So you could lay out the navigation in HTML or the footer, and instead of copying the code on each page, you simply put the include statements linking up to a separate page where you put just the code of the item you want brought in. The strength is that if your boss asks you to change something on that included bit, you only change it once, rather than on every page.

Looking at the display PHP page

OK, so you've now entered in your database information into dbConnect.php. Upload both dbConnect.php and your chosen display page into your hosting. Before you run it, let's take a moment and understand the anatomy of this page, as it will represent most PHP pages you'll create.

Look at the code in your preferred editor. You'll see it looks like an HTML page with many added snippets of <?php and ?>. I created this page this way to show you that you don't have to have all your PHP in one spot running together. Your server will simply look at anything between the when it's going to run the PHP. The server will see it as one unified code, and ignore anything outside of it.

The end result is your server will run the scripts, assemble the results into HTML, and then send back the finished HTML code for you to see. This is why I have the include for the connection at the top, and the mysql_close at the bottom. This would allow you to run multiple MySQL calls within that open and close area, but safely shut the database before the end user sees it. Do not ever forget to close your database, as neglecting will leave it open for hackers.

Making the MySQL call

Focus on this code right now:

$Query = "SELECT * from phpDemo";
$Result = mysql_query ($Query, $Link);
while ($Row = mysql_fetch_array ($Result)) {
}

What's going on here is I first set up variable $Query with the appropriate MySQL statement. I'm simply telling the page to pull up any and all data in the database. Hence the *. If I wanted one specific column of data, I would put that in as opposed to the star. If I wanted a specific row, then I'd add a Where in there to call one item. Here's some examples of what I'm talking about:

SELECT strExample01 from phpDemo

SELECT strExample01,numExample from phpDemo

SELECT * from phpDemo Where id='1'

$id = "1";
SELECT * from phpDemo Where id='$id'

The first item would only call up any information in the strExample01 column. If there were more than one entry in our database, you would see more. The second item would pull strExample01 and anything in the numExample column. Imagine this if you entered merchandise and prices, and only want them displayed. The third item is an example of calling one piece of data, so if you had 10 entries and only wanted to see one of them, that's how you do it.

The last one I added in to show you can also set a PHP variable elsewhere and then bring it into your MySQL statement. Thus you don't have to write one statement for each piece of data you pull. In the end, I generally use * and will select what data I want when I write the print or echo statements.

The next part of the code I wanted to point out is the keys to connecting, running the MySQL statement, and then getting the data displayed. The mysql_query function will take the MySQL statement and run it with the $Link connection made in dbConnect.php. The while loop I put in next is how you go through and pull data. The mysql_fetch_array function combined with the while loop will go through the table row by row and pull any data matching what you asked for in the MySQL statement, placing it into an array called $Row.

The Great Debate: Print or Echo?

Moving along, the first example I put in for data display is a simple echo statement. The code can as follows:

echo "". $Row[strExample01] . "\n";

print("$Row[strExample01]\n");

I put the print statement version in there to show both ideologies. There's been sizable debate on using print versus echo. In the end, it's up to you. I started PHP using print, and only lately have been trying to change my discipline to echo. The main reason is that echo will run a fraction of a second sooner than a print statement. Now this might seem miniscle, and it is, but imagine when you have a very large page running with 100 or more print statements.

You'll also notice I put HTML paragraph tags in there. Generally when you're pulling up a raw piece of data like the string in strExample01, you'll need to add in the HTML that would need to go in the name of page formatting. You could put it outside of the statement like this:

<p>
<?php echo $Row[strExample01]; ?>
</p>

I personally say "do what's easiest for you. There is no right or wrong".

The \n at the end of both statement is merely a line break for the code. It won't put in a <br /> tag in the visual, but if you look at the compiled source code after running the PHP, it will put in a line break in the code. It's mainly good for organization.

String Replacement

The str_replace function is a handy means to change things in your data when you display it. I originally would use them to replace some special characters with their HTML counterparts, but there's an easier way I'll get to in a bit. A typical string replacement goes as follows:

$finalString = str_replace("brown", "gray", $finalString);

I've pulled the data already from the database and placed it in the variable $finalString. The first part inside the parenthesis should be what you want this to look for, in this case brown. The second item is what you want put in its place, gray. The last is the item you want searched through for items to replace.

I have $finalString both as the final item it equals and the item to be searched simply because there is no need to make a new variable every time you want to do this. The function will take the string as is, do the replacement, and thus delete the old data in your page for the new one. As you can see, I ran it four times to change how the statement ends up reading.

HTML Character Replacement

If you're dealing with web forms and items like a custom-built CMS where people enter data, you'll have to look for the means to deal with all the quotation marks, apostrophes, and other special characters that cause your code to have errors. Use the htmlspecialchars function:

$finalString = htmlspecialchars($finalString, ENT_QUOTES);

It's pretty self explanatory. You place whatever item you want sifted through in the parentheses and it does all the work nicely. Parameters like ENT_QUOTES can be added to do specific things. In this case it's to make sure single and double quotation marks are replaced by their HTML counterparts.

If you're using the alt version of the PHP, you'll also notice I put in the stripslashes function to remove all those forward slashes before anything else. If your host/database is temperamental on single and double quotation marks going in, then you'll need to use this every time you pull data.

Pulling content that has HTML

Simple strings and text are the norm in your data, but if you want to eventually use rich text editors like TinyMCE, you'll need to be able to deal with data that has HTML in it. The database can store the code in a TEXT data type, and pulling it down won't be an issue. The PHP only just pulls what's there, it doesn't challenge or ask what it is.

Now, a handy tool to remove the code is strip_tags function:

$finalHtml = strip_tags($finalHtml);

Why would you use this? Imagine you make a comment section on your own blog, or a form people outside of your company fill out, or a message board. Would you want people dropping in code that could mess up your site? strip_tags will remove any and all HTML tags, and it knows the whole library. Notice though that it will not remove HTML special characters.

String Concatenation

For those who don't know what the term Concatenation means, it's the idea of combining things. In this case, it's strings. You've already seen some concatenation today in how I wrote the echo statements. The example I created combines three strings into one:

$conCatExAdd = $Row[conCatEx01] . " " . $Row[conCatEx02];

echo "Abraham Lincoln: "" . $conCatExAdd . " " . $Row[conCatEx03] . "..."n";

The first line shows a simple concatenation between two strings. The period is really how things are combined, but the single space in the quotes is how you add in other strings. Always bear in mind that anything in quotes like that will be seen as a string. If you had not added that, then you would have not seen a space between the two strings.

The second line shows another example of using concatenation to combine a myriad of items. From the text like Abraham Lincoln to the HTML code, and even showing you can either concatenate variable items or pulled data. You simply need to use the periods and encapsulate any strings, spaces, and HTML code in quotes so the server knows what to do with them.

If you're looking for an example of why you would want to use this, imagine sending a statement to the screen saying "Hello John! You last logged in 4 hours ago." (if John was the user's name). Something like that would be coded perhaps like this:

echo "Hello " . $personName . "! You last logged in" . $lastLoginHours . " hours ago.n";

Numbers and Decimals

Dealing with numbers and decimals is a bit different than dealing with strings. With numbers, you can have them in the database as text if you want (I wouldn't), but if you wish to treat them as numbers in the PHP code, then you do not encapsulate them in quotation marks unless you want to just print the number.

You can of course do math with these numbers in the code, as I've shown in the examples:

// Addition
$numSum = $numberItem + $deciItem;

// Subtraction
$numDiff = $deciItem - $numberItem;

// Multiplication
$numProd = $deciItem * $numberItem;

// Division
$numQuot = $numberItem / $deciItem;

// A Simple Equation
$numEquat = ($numberItem * $deciItem) / 100;

In many ways, if you've dealt with Excel spreadsheets, this isn't much different in terms of the logic. I know some of you hate math, but this is handy if you need to do more with numbers than just display them. Perhaps you want to show prices for items, add them, or average out scores listed in the database. The sky is the limit.

Dates and Time

Last, but not least are your dealings with date and time. A timestamp goes into the database in the format YYYY-MM-DD HH:MM:SS, but you don't want to display that every time you post a date. Use the date function instead:

date("l, F j, Y", strtotime($origTStamp))

This function will take a UNIX Timestamp and convert it to any format you wish based on the parameters you place in the first part. The l will say to use the full name of the day, the F is the full name of the month, the j is a number for the day in the calendar with no proceeding zero, and Y is the full year in four digits.

If you look at the PHP manual page on date, you can see all the possibilities for how to format dates and times. Also look at my examples as well. Notice you have to put it in somewhat like a string with the commas, hyphens, and colons you might want.

The strtotime function is how you deal with the need for a UNIX Timestamp. Since the MySQL database timestamp is not a UNIX Timestamp, the strtotime function will convert it.

Next time...

I know today was a lot, but if you really examine things it's simply showing how to pull up data and display it. Most of the rest are simply examples of what you can do with this data.

Next time I'm going to show you how to use an HTML form to add data to your database, so you don't have to sit there using phpMyAdmin to enter, edit, and delete data. Play around with what I gave you this week, and ask questions.

Questions? Ask here in comments or drop me an email.

Download example files Download the example files from this article

Tags: php fundamentals, php, mysql, technology, display

comments powered by Disqus