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!