In this post, I am going to show you a technique to query a database and return results, but instead of doing that directly on the page you want to display the results, all in one go, I will show you how you can do this with a PHP Class, so that you can keep your queries separate from the display of your data.
This post is a bit long, so use the links below to skip to the relevant part for you, or read on to continue.
This is useful for code re-use, being more efficient and also making it easier to read your code and have a nice structure to your PHP and HTML.
I’ve found in the past, if you put everything on the one page, it can quickly get out of hand, and make it difficult to edit your pages without breaking either the HTML, or the PHP functionality, this technique attempts to limit these kinds of dramas.
You should have a basic working knowledge of HTML and PHP to complete this example.
Step 1 – The PHP Class
The first thing you will need to setup, is your PHP Class. This will be unique to your own setup (such as host, db-name, username and password etc.)
See the following:
<?php
class myData {
protected $connection = null;
public function connect() {
$this->connection = new PDO("mysql:host=localhost;dbname=your-db-name", "db-user-name", "db-user-password");
}
public function getData() {
$query = $this->connection->prepare('SELECT * FROM `my-db-table`;');
$query->execute();
return $query;
}
}
?>
(Edit the values “your-db-name“, “db-user-name“, “db-user-password” to match the equivalent on your own site.)
The connect method, does exactly that, it connects to your database. This method uses “PDO” to do the query, but you may choose to use another function, so long as it queries your database. You can read more about PDO here.
Next is the “getData” method. This method runs a query based on our previous connection when it is executed on your database table and returns the result of the query.
Copy this code, and save it as “myData.php” or similar.
Pretty simple huh? Now with our Class setup, we can call this functionality on an HTML page that we want to display our results on.
back to top
Step 2 – Displaying the query result on a page
Step 2.1 – Creating an instance of the PHP Class on your page
Next, we need to create our display page.
<?php
require 'myData.php';
$myData = new myData();
$myData->connect();
$theDataIwant = $myData->getData();
?>
The first line adds our previously created “myData.php” Class to the page.
The second and third lines create an instance of our Class on the page and then executes the “connect” method of our class.
The the last line above, creates a variable (“theDataIwant“) to store a variable with the result of calling the “getData” method of our Class.
Copy and paste the previous code to the top of a page called “results.php” or similar.
back to top
Step 2.2 – The HTML page
Add your HTML structure to the page, as the following:
<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>Get my data</title>
</head>
<body>
<!-- Your PHP code goes here -->
</body>
</html>
This just creates a standard HTML5 style HTML document to the page.
back to top
Step 2.3 – The PHP to display the results to the page
At the HTML comment in the previous section (“<!– Your PHP code goes here –>“) add the following PHP code.
<?php
foreach ( $theDataIwant as $key => $myReturnedData ) {
echo $key . " - " . $myReturnedData['name'];
}
?>
The above PHP is a foreach loop, that iterates over “theDataIwant” variable we created earlier, and displays it to the page with the “echo” statement in the loop, it will display the “name” field of every row in your table.
This result should look something like this:
1 – Adam
2 – Steve
3 – Jeff
4 – <other names>
etc.
If all goes well, you should have successfully queried your database with a PHP Class!
back to top
Bonus beats
You can extend this Class to do various things other than just merely querying the database for all your data, you can create a sorting method or create a method that looks for a particular field in your table.