Today, I am going to show you how to pull information out of a database and show it in PHP Module in vBulletin 5. We'll be covering several different PHP techniques here but none of them are extremely difficult. However knowledge of PHP will help with this exercise. During the exercise, we'll learn how to access a second database within PHP, run queries, and output the data.

Getting Started

First, we need a database to store our data in. I've called my database shop. You can create this using any database tool available. Since I am working on my local server, I used a Windows Program called HeidiSQL. Currently the database has one table in it called products.

The table has several fields within it. Here is the SQL to duplicate it:
Code:
CREATE TABLE IF NOT EXISTS `products` (
    `productid` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `title` varchar(200) NOT NULL DEFAULT '0',
    `description` text NOT NULL,
    `image` varchar(200) NOT NULL DEFAULT '0',
    `price` decimal(8,2) unsigned NOT NULL DEFAULT '0.00',
    PRIMARY KEY (`productid`),
    KEY `title` (`title`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Once the table is created, you will need to add some data.

Creating a New Page.

To show our new product list, we'll have to create a new page. To do this, go to your vBulletin 5 site and turn on Site Builder by clicking the Edit Site toggle. Once Site Builder is turned on, click on the New Page Link. You should be given a new page with a default 2 column layout. For our example, we're going to use a single column layout so click on the Layout tab and select the 100% width layout that is available. We should get a page that looks like this:
100% layout.png



Once this is done, we're going to add a few modules to the page. The first module we're going to add is a Page Title Module. This will show the title of our new Product List as a proper H1 tag. The Page Title has no real options to configure at this time so we're going to leave it alone and add our second module. The next module will be the basic PHP module. Drag it down on the page under the Page Title module. This module does have some options that we're going to set. When both modules are added the page will look like this:
layout with modules.png


I am a fan of saving my work often and since we're not using an IDE to program in, I suggest you save the page now. Let's give it the name of Products and the url of products. Also name the template as Products as well. This will make sure we recognize it later. After saving, your product page won't look like much because we haven't added anything to make it work yet. That is the next step.

The PHP Code.

The PHP code will consist of several steps:
  • Connecting to the Database
  • Running the Query
  • Outputting our HTML
  • Closing the database connection and cleaning up our vaariables.
For our purposes, we need to assume that PHP is always live and parsing in the Module. Using open and close tags will not work. This means we need to use echo or print commands to output our PHP. Otherwise we'll get errors and no output.

Connecting to the Database

For our database connection, we're just going to use standard MYSQLI at this time. We don't want to mess with the standard vBulletin connection yet so there is no chance of breaking anything.

First let's check if we can connect to our database. The first lines allow us to setup our connection variables. The second part performs the actual connection and outputs an error if there is a problem. Use the code below and it will create output information about the connection if successful.

PHP Code:
/* Database Connection */
$products_server     =     'localhost';
$products_database     =     'shop';
$products_user         =     'root';
$products_password     =     '';

$products_conn = new mysqli($products_server$products_user$products_password$products_database);
if (
$products_conn->connect_errno) {
    echo 
"Failed to connect to MySQL: (" $products_conn->connect_errno ") " $products_conn->connect_error;
}
echo 
$products_conn->host_info "\n"
A successful connection
successful connection.png


Running the Query

For the query, we're going to use prepared statements. This is because the module is a user-facing piece of technology. However the extra security is minimal because it is a user-facing piece of technology.

PHP Code:
if ($result $mysqli->query("SELECT * FROM products")) {
    
printf("Select returned %d rows.\n"$result->num_rows);


Building the Array

In order to loop through the results, we're going to put them in an array of variables.
[php]
// Create array of Products
while($row = $result->fetch_array())
{
$products[] = $row;
}

Output the HTML

This next bit of code takes our data and creates a table to output to the page. It can be made to look better with CSS but that is a different tutorial.
PHP Code:
// Start Output Buffering.
ob_start();

// Output the table header
printf ("<table>");
printf ("<thead><tr><th>SKU</th><th>Title</th><th>Description</th><th>Price</th></tr></thead><tbody>");
// Output the rows.
foreach($products as $product)
{
    
printf ("<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>"$product['productid'], $product['title'], $product['description'], $product['price']);
}
// Close Table
printf ("</tbody></table>");

// Flush the output to the browser.
ob_end_flush(); 
In this bit of code, we turn on output buffering. This allows us to build our output and send it to the browser in a single chunk instead of line by line. This is done with the ob_start() function call. Next we start our table and print out the table header using <th> tags. After that we start a loop that will go through all the products and create a table row for them. When we are done with the content of the table, the code closes the table. Finally we send the entire chunk of output to the browser by flushing and closing our buffer.


Cleaning Up
While not always necessary, the last part of the code cleans up our memory use and closes the connection to the database.
PHP Code:
// Cleanup
unset($products);
unset(
$result);
$products_conn->close(); 
Completed Code
PHP Code:
/* Database Connection */
$products_server     =     'localhost';
$products_database     =     'shop';
$products_user         =     'root';
$products_password     =     '';


$products_conn = new mysqli($products_server$products_user$products_password$products_database);

ob_start();
if (
$products_conn->connect_errno) {
    echo 
"Failed to connect to MySQL: (" $products_conn->connect_errno ") " $products_conn->connect_error;
}

// Pull the product information out of the database.
if ($result $products_conn->query("SELECT * FROM products")) {
    
printf("<p>Total Products Found: %d</p>"$result->num_rows);

}
// Output the table header
printf ("<table>");
printf ("<thead><tr><th>SKU</th><th>Title</th><th>Description</th><th>Price</th></tr></thead>");


while(
$row $result->fetch_array())
{
$products[] = $row;
}

foreach(
$products as $product)
{
    
printf ("<tbody><tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr></tbody>"$product['productid'], $product['title'], $product['description'], $product['price']);
}


// Close Table
printf ("</table>");
ob_end_flush(); 
product output.png
Attached Files