How do I display database data on the fly in a table?

Question

For example, I am creating a page that will be a menu for a deli and bakery. I have created a page that will allow the owners to enter new menu items into the database. On the menu page how can I make it display the new items as they are entered? I am making the connection to the database using a PDO connection.

For a little more detail I have an item section, a description section, a price, and a category. The category would tell the script where to put the item in the menu. Is it possible to have the table auto create with the same css properties too. I have the item formatted one way, the description a little different, the price is formatted the same as the item.

Answer 1 · 2014-07-08T23:13:03Z

July 8, 2014 11:13pm

I have a PDO version which might help you out not sure....

DB Wrapper

<?php

class DB {
    private static $_instance = null;
    private $_pdo, 
            $_query, 
            $_error = false, 
            $_results,
            $_count = 0;

    private function __construct() {
        try {
            $this->_pdo = new PDO('mysql:host='.Config::get('mysql/host').';dbname='.Config::get('mysql/db').';charset=utf8', Config::get('mysql/username'), Config::get('mysql/password'), array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
        } catch(PDOException $e) {
            die($e->getMessage());  
        }

    }

    public static function getInstance() {
        if(!isset(self::$_instance)) {
            self::$_instance = new DB();
        }
        return self::$_instance;
    }

    public function query($sql, $params = array()) {
        $this->_error = false;
        if($this->_query = $this->_pdo->prepare($sql)) {
            $x = 1;
            if(count($params)) {
                foreach($params as $param) {
                    $this->_query->bindValue($x, $param);   
                    $x++;
                }
            }

            if($this->_query->execute()) {
                $this->_results = $this->_query->fetchAll(PDO::FETCH_OBJ);
                $this->_count = $this->_query->rowCount();
            } else {
                $this->_error = true;   
            }
        }

        return $this;
    }
} ?>

Using it

<?php
                            $menu= DB::getInstance()->query("SELECT * FROM menu");
                            foreach($menus->results() as $menu) {
                                echo '<ul>';
                                echo '<li>'.$menu->Item.'</li>';    
                                echo '<li>'.$menu->Description.'</li>'; 
                                echo '<li>'.$menu->Price.'</li>';   
                                echo '<li>'.$menu->Category.'</li>';    
                                echo '</ul>';
                        } ?>

As for the category determining the placement on the page you can just repeat this for each of the sections and use a WHERE clause to limit the results to only the results of the given category.

Answer 2 · 2014-07-07T07:46:15Z

July 7, 2014 7:46am

I'm really rusty with PDO, but plain old PHP and MySQL should give you the general outline.

Here's a way to iterate through the categories and then list out the items. (Below that I mention a way to manually place the categories on the page, which means you can't give the user options to add more categories.)

<?php

// This query is just to get the categories. That's the whole purpose here of the GROUP BY.
$query = "SELECT * FROM menu GROUP BY category"; // assuming they're named 'title', 'description', 'price', 'category'

$result = @mysqli_query($databaseConnection, $query);

if ($result) {

    while ($row = @mysqli_fetch_array($result, MYSQLI_ASSOC)) {

        echo '<div class="category">';
        echo '<h1>' . $row['category'] . '</h1>'; // We can call it by name because we used MYSQLI_ASSOC.

// Now that we have the category name, let's return everything in that category.
        $query = "SELECT * FROM menu WHERE category='$row['category']'"; // There might be too many parenthesis in this line of code, it's hard to tell without syntax highlighting. You might need to use the dot notation to concatenate the string instead.

        $r = @mysqli_query($databaseConnection, $query); // Probably don't want to reuse the "$result" variable name, since we're still iterating through the results. I'm actually not sure if that will break it or not.

        if ($r) {

            while ($row = @mysqli_fetch_array($r, MYSQLI_ASSOC)) {

                echo '<div class="menu-item-row"'> // Containment to help you make new rows

                echo '<div class="item">' . $row['item'] . '</div>';
                echo '<div class="description">' . $row['description'] . '</div>';
                echo '<div class="price">$' . $row['price'] . '</div>'; // Or just reuse the "item" css.

                echo '</div>'; //end of "menu-item-row" div
            }
        }

        echo '</div>'; // ending the "category" div
    }
}
?>

Of course, your HTML will be really ugly if output it as echoes like this with no tabs. You could either add tab characters into the echo statements, or interleave your HTML and PHP more frequently. There are lots of different ways you could do this whole thing, and you don't have to include the option for iterating through the categories like I did. It really just depends on if you want to do the extra work up front, or when the client asks you to add a new menu item for them. (Then again, if you give them the ability to add a new category, then you also have to give them the ability to modify the name, since the name will appear on the page. And then you have to worry about ordering. Maybe it's best to just create a single PHP file that run a subset of this code, minus the category part, and is just used as an "include" in the main file. That way you can access a local variable for each menu item, like this:

<div class="category">
<h1>Appetizers</h1>
<?php
    $category = "appetizers";
    include('list-items-for-category.php');
    ?>
</div>


<div class="category">
<h1>Entrees</h1>
    <?php
    $category = "entrees";
    include('list-items-for-category.php');
?>
</div>

The included files would have access to the $category variable—or rather, the main PHP file would have access to the code in the include files, which would be in the same scope as the $category variable.

Hope this helps! Cheers.

Answer 3 · 2014-07-09T01:02:01Z

July 9, 2014 1:02am

Ok so I have to be honest I think this is a little to advanced for my knowledge as of right now. I have to ask both of you a stupid question the first part of the code that you both wrote is that supposed to go into a different file than the file that I am trying to show the menu on? I think I bit off more than I can chew here. Is there any way that either or both of you can break down the code a little simpler? I know it's more complicated but I just don't think that I am getting in the right places or I'm not altering it to my names the right way or something. I'm sorry to bother you guys with this. I really want to understand this. Thank you both in advance if you both do this for me.

Welcome to the Treehouse Community

Looking to learn something new?

James Nelson

James Nelson

How do I display database data on the fly in a table?

3 Answers

Rodger Voelkel

Rodger Voelkel

Mike Baxter

Mike Baxter

Mike Baxter

Mike Baxter

James Nelson

James Nelson

Mike Baxter

Mike Baxter

James Nelson

James Nelson

Mike Baxter

Mike Baxter

Rodger Voelkel

Rodger Voelkel

Mike Baxter

Mike Baxter