Announcement

Collapse
No announcement yet.

Creating a PHP Module using data from a MySQL database

Collapse
X
Collapse

  • Creating a PHP Module using data from a MySQL database

    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
    Last edited by Wayne Luke; Fri 16th Oct '15, 10:28am.

    • Macro Man
      #1
      Macro Man commented
      Editing a comment
      The PHP module doesn't appear to be on the cloud option - is this only ever going to be on the self-hosted version?

    • Wayne Luke
      #2
      Wayne Luke commented
      Editing a comment
      I am not going to say it will never be available in Cloud. Right now, the PHP module is considered a security risk on cloud sites because it can access databases directly. This may be resolved in the future.
    Posting comments is disabled.

Related Topics

Collapse

Working...
X