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;
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.
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";
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);
}
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();
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();
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();
If your ad system allows Javascript calls, then you can use Javascript within an Ad Module to randomize content.