The Final Part: Pulling the Information Out

The first thing we are going to do is create 2 files in our root folder: index.php and database.php.

Open up database.php first. It should be a blank file; no HTML or anything. When writing PHP code, it should be contained within the tags. That way, the server knows to read it as PHP code and process it as such. We are going to reference our database and be able to work with it in our PHP file.

Type in the following code:

1.$host = "localhost";
2.$user = "root";
3.$password = "";
4.$db = "tut-portfolio";
6.$mysqli = new mysqli($host, $user, $password, $db) or die('Could not connect to database.');

In PHP, the $ sign represents a variable. A variable is a method of storing data; in our case, we are simply storing strings, and indicating the start of a new function.

What we have done is opened up a connection to our MySQL database. The $host is the location of the database; in our case, since it’s on our own computer, we will reference it as localhost. The $user is the username to the database; if you are using WAMP, it will be “root” unless you changed it. The $password is obviously the password; for WAMP, the default password is nothing, so leave it as such. Finally, the $db is the name of the database. I called mine tut-portfolio. It must be exactly the same as the one you created in phpmyadmin.

The last line is where the magic happens. We start a new variable called mysqli; within it, we are using the MySQL Improved method to make our database connection. You may have seen people using mysql_connect, but this method is semantically correct and a much better way. We indicate the start of the mysqli class by writing “new mysqli”. Within brackets, we call the variables we set earlier; when starting a new mysqli class, the parameters go in the following order: host, username, password, database. You could either type it in within strings (in quotation marks), or use variables like we did. I prefer the variable method; it allows you to use those variables multiple times and update them easily. The point of PHP is to simplify the amount of code you have to write; if you are typing in the same strings over and over again, when you go back to change even one character, you’ll have to do it multiple times. For example, what if you change the password? Having a variable will allow you to easily update the entire code by changing it in one place.

The or die() statement will only be shown if the script fails. If, for whatever reason, you are unable to connect to the database, the string, “Could not connect to the database.” will be shown. It will also stop the script from going any further. This helps as it points out exactly what’s wrong with your page according to your specifications, and tells the viewer that something is not right rather than just displaying a blank page.

Always remember that each PHP statement must be ended by the semi-colon, otherwise the browser will throw an error.

Alright, we’re done with database.php, so close that up. Now, open index.php. Since we’re going to be combining PHP and HTML on this page, you should start off with the standard HTML code (doctype, title declaration, etc.) On top of the doctype declaration, we will start our PHP code. Start your PHP tags () and enter this code:

1.require 'database.php';
3.$query = 'SELECT image, description, name, link, technologies_used FROM clients' or die('Could not complete query.');
4.$results = $mysqli->query($query) or die('Could not process results.');

Alright, let’s analyze this code. The “require” call selects the file we specify (in this case, database.php) and makes it absolutely necessary for the script to run. There are other calls like this, such as include, but I am using require because without connecting to the database, our page won’t run. If the script doesn’t find database.php, the script dies right there and does not execute any further.

1.$query = 'SELECT image, description, name, link, technologies_used FROM clients' or die('Could not complete query.');

We then create a new variable called query. A query is a statement that will be given to the MySQL database, and will perform the actions we tell it to. This is SQL code, by the way. Everything you see in caps is a command we’re giving to the database, basically. We’re telling it to SELECT certain fields from the database. There are two ways to select fields; you can either type their names out if you only want some of them, or you can use the asterisk (*) to select all the fields. We’re selecting all the fields, however, we’re listing them all out. Why? Because if we happen to use the asterisk and add, for example, 6 more fields to the table that don’t pertain to our script, those fields would be selected too. Listing them all out is not only a safer method in terms of overloading the script with data, but it is also easier to see which fields are being used.

The FROM states which table we’re selecting the fields from, which is called clients for us. We then end our query variable with an or die statement, just to stop the script from executing further if this part fails. Basically, what this query will do is get all the items under the fields we specified.

1.$results = $mysqli->query($query) or die('Could not process results.');

After our query variable, we start another variable called results. This variable will contain the results from our query. But we have only created a query variable; we have not actually sent it to the database. The way we do that is access our database connection variable ($mysqli) and use an arrow (->) to access the query parameter. Basically, what we’ve done is accessed our database, and used the query call to send our $query variable to the database. If that doesn’t work, the script dies and tells you that it could not process the results.

That’s it for our PHP ABOVE the HTML. This accesses the database, sends the query, and keeps the the results in a variable to be used later.

We have our results…but they’re not displayed on the page. The way we’re going to make them display is by using PHP code within our body. I created a div called page-wrap (just to center the page; I’m not getting into the CSS bit) and put the following PHP code in there:

01.if($results) {
03.while($row = $results->fetch_object()) {
04.$name = $row->name;
05.$image = $row->image;
06.$description = $row->description;
07.$link = $row->link;
08.$technologies_used = $row->technologies_used;
10.echo "<div class='project'>";
12.echo "<img src='$image' alt='$name' />";
14.echo "<div class='projects_links'>";
15.echo "<ul>";
16.echo "<li><a class='link'' href='#'>View Details</a></li>";
17.echo "<li><a class='link' href='$link'>View Site</a></li>";
18.echo "</ul>";
19.echo "</div>";
21.echo "<div class='technologies_used'> Technologies Used: $technologies_used </div>";
23.echo "<img src='images/projectSeparator.jpg' alt='project_separator' class='project_separator' />";
25.echo "<div class='project_title'>$name</div>";
27.echo "<div class='project_desc'>$description</div>";
29.echo "</div>";

Alright, this last chunk of code is pretty repetitive. We’re using an if statement to run our code; if there is data in our $results variable, then all this code will be run. The while statement will repeat itself as long as there are rows of data in the $results variable; we take each row one at a time and put all the data in a temporary variable called $row. This data will be contained as an array, which, in layman’s terms, contains multiple objects in 1 variable. I’ll get into more of that in another tutorial. Then, we fetch that data using the fetch_object command, and set each of the fields from the database to a variable. The way we target each field is using the $row->[field name] method; when using arrays, you call the variable first, use and use an arrow to the specify the object you want to use.

After we store each field as a variable, we begin to echo out our information. I’m not going to get into the HTML because it’s the way Total Pixelation Studios’ portfolio page is done (and it’s irrelevant to this tutorial). Basically what I’ve done is create a div with a class of “project”, and echoed out all our information within it.

The echo command basically spits out everything in front of it as HTML.

1.echo "<img src='$image' alt='$name' />";

We echo out an image tag, but instead of statically referencing an image, we put our variable there. Every row of data has a different image; since this echo statement is in the while loop, it will show a different image for every project. We also set the alt tag to the name of the project.

After that, I’ve basically done the same thing for each line. For the project links, I’ve made a simple unordered list navigation and put in the $link variable for the location. The project title is stored in the $name variable, so within the “project_title” div, I put wrote $name, rather than an actual name.

Now echoing all this information out may not be the best way to do this, but it sure is the simplest. I wanted to keep this tutorial simple for the newcomers to PHP, so there you go.

After the while loop is complete, it will run over and over again until there are no rows of data left within the $results variable. This way, it displays all the data that was retrieved from the MySQL database.

With a little bit of CSS, you can have a portfolio page that looks like this:

Final Product

I hope this helped you on your trek to learn PHP, and get coding!

PHP: Making Your Life Simpler – Part 1

As a versatile programming language, PHP is an almost essential tool in your skill set as a web developer. In this article, you’ll learn how to work with PHP locally, as well as a basic introduction to using PHP with MySQL.

Uses for PHP range from something simple such as reading information from a database (which you’ll learn in this tutorial), to running a blog like this one! Let’s get started!

A database is a mechanism that will organize your data for easy access and editing. Using a database with PHP has many benefits; you can easily update information in a database, you can access it from an infinite number of files, and many more. With regard to what we’re making, the primary benefit of having a dynamic portfolio is that once you have this set up, all you have to do is add a new row to your database for your portfolio to update.

The scope of the tutorial:

– a page that displays a portfolio of work
– a MySQL database that contains each project’s information
– a folder for all the images to be kept in and located from the database
– a PHP script that calls the MySQL database and pulls each project in

Note: I am assuming you have a basic knowledge of PHP (ex. how to use it, basic parameters, variables, etc). I will still explain it as best as I can for those that are unfamiliar with it/need to brush up.

Setting Up a Testing Environment

Running a PHP page on your computer locally isn’t as simple as just opening a file in your browser. Unlike HTML files, PHP requires you to run it on a server. You can get a web host that supports PHP, but unless you’re actually going to run a website, it’s not worth the money to subscribe to a web hosting company. For that reason, we’re going to install a local development environment that will allow you to open PHP in any browser.

There are lots of programs available that can set this environment up on your computer. The software I use is called WAMP (Windows, Apache, MySQL, PHP). I’ve tried others such as apache2triad, but I find that WAMP is quick to set up and easy to use.

WAMP Overview

After you have downloaded WAMP, go through the installation procedure. I installed my server in the my primary drive, which is C, but you can install it wherever you want. It will be accessed through the localhost call. Once the installation is complete, you can access the server from any browser of your choice. But before you do that, you have to start the service. Go into your Start Menu, and navigate to the WAMP Server folder. From there, click on Start WAMP Server.

Start WAMP Services

Sometimes, the program itself is running but the service itself is not. Not to worry; all you have to do is click on the WAMP icon in your Windows taskbar, and press Start All Services.

Now that you have your WAMP server all set up, it’s time to test it. Type in http://localhost in the browser of your choice. If you see the main page, congratulations; you’re not all set to work with PHP on your computer!

Getting Started With MySQL

Go to your WAMP5 Homepage, and click on phpmyadmin, which is under the “Tools” subheading. phpmyadmin is a graphical interface which is extremely popular in terms of operating MySQL. This is the method with which I use MySQL; the other option is using the command line to access it. That makes it complicated, and I prefer something that I can see and navigate through easily.

When you access phpmyadmin, your databases will be listed in the column on the left. You can navigate through the program using the tabs spanning across the top. What we are concerned with at this point is the text box smack dab under the title, “Create new database”. You can call your database whatever you want; for this tutorial, I will call mine “tut-portfolio”. Don’t change any settings unless you know what you’re doing. Press create to…well, create it, obviously!

Now you will have something like this:

Holy crap. That’s really overwhelming. Taking it in bit by bit will ease the pain. The “Field” will be the title of your…field. The type will define what kind of field it is; INT for an integer, Varchar for a string, etc. Length/Values is how long you want it to be (ex. 250 characters for varchar). Don’t worry about Collation and Attributes. Null defines whether it is required or not; if you check it off, it means that it’s okay to leave that field blank. The Index column has 4 options; the only one that concerns basic MySQL is the “PRIMARY” setting; this indicates that the specific field is the primary key by which a row of data is identified. You’ll see what I mean in a second. Finally, A_I means auto increment (increase by 1 automatically), and Comments is just that.

Call the first field “id”. This is what will identify each row. Set it to INT, and set the Length to 2 (you’re not going to have more than 99 projects on display…but if you are, feel free to increase it). Under Index, set this as PRIMARY, and tick A_I.

The next fields are much simpler; we are just going to define the name, type, and length. Follow this picture:

Values For Table

Basically, we created 5 more fields: name, description, image, links, and technologies_used. For name, we set it as varchar and 250 characters; description is text (which allows 64 kilobytes of text – more than enough for a description), image is a varchar of 500 characters; links is a varchar of 250 characters; technologies_used is a varchar of 600 characters – again, more than enough.

Press save to create your table. Next, input some data. To do this, click on the “Insert” tab on the top of the page. Next to each of the fields we made, you’ll see an input box. Fill in your information. For the image field, enter a location RELATIVE to the root folder (ex. in your images folder). Place the specific images within your image folder.

Note: Make sure you leave “id” blank. It will automatically fill itself in for each row of data.

To make sure you’ve done it right, click on the “Browse” tab, and you should see all the data you inserted. I’ve put in 3 rows of data.

Now we’re done the MySQL part, and it’s time to move onto the PHP!