Welcome to the Treehouse Community

The Treehouse Community is a meeting place for developers, designers, and programmers of all backgrounds and skill levels to get support. Collaborate here on code errors or bugs that you need feedback on, or asking for an extra set of eyes on your latest project. Join thousands of Treehouse students and alumni in the community today. (Note: Only Treehouse students can comment or ask questions, but non-students are welcome to browse our conversations.)

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and a supportive community. Start your free trial today.

PHP

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

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.

3 Answers

Rodger Voelkel
Rodger Voelkel
21,736 Points

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.

Mike Baxter
Mike Baxter
4,442 Points

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.

Mike Baxter
Mike Baxter
4,442 Points

There was some weird formatting issue with the @ symbols. They're used in PHP to suppress error warnings in production code. Because nothing's worse than having a PHP error that lists out "secure" information. I fixed it now, but let me know if you run into issues and I'll check over the code again.

Thank you Mike for answering my question. I will be looking at this to see if I can get it to work with my connection tomorrow night. I will let you know how I do.

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.

Mike Baxter
Mike Baxter
4,442 Points

James Nelson , for production code (or anything that is accessible via the internet), you'd want a single PHP file that handles connecting to the database, and then "require" that everywhere you need it (instead of just "include").

You'll also want to put that connection file somewhere above your "htdocs" directory, as it's commonly called, so that your database connection file is only accessible from the server, not the rest of the web. (Don't worry about this right now unless you're publishing something to the web; just know that you'll need to cross that bridge eventually, and it's pretty easy to learn how to do it by searching Google for a tutorial.)

Also, don't let the code intimidate you. That's just part of learning; you have to motivate yourself by the possibility of what you'll soon be able to do. To be honest, I don't understand much of Rodger's code, since I haven't used object-oriented PHP aside from the tiny bits used in the PHP tutorial on Team Treehouse. I learned most of my PHP and MySQL from a book called "PHP and MySQL" by Larry Ullman; it's a great book, and I highly recommend it. It covers a lot more depth than Treehouse, but it's a little bit harder since it's reading rather than awesome videos. He covers how to set up a user signup and login, which is really useful; he also covers how to make forums. All this to say, even though I can and have done a lot with PHP and MySQL (I built an analytics engine for an app), I still find other people's code confusing if I haven't seen anything like it before. It would probably take me a solid hour to understand Rodger's code, just because I haven't seen object-oriented PHP before, and I'd have to Google a ton of things. So well I can't offer help with Rodger's code, let me know if you have any questions about what I wrote; I should be able to get back to you within 24 hours at the most.

Cheers!

Rodger Voelkel
Rodger Voelkel
21,736 Points

The DB wrapper is its own file and is designed to handle the database connection and basic manipulations essentially CRUD (Create, Retrieve, Update, Delete). I include that DB class anywhere I need to make a connection and invoke it simply by..

DB::getInstance();

This uses the singleton method which makes it so that you only have 1 connection to the database active at a time, essentially your reusing that same connection over and over rather then invokeing a new one each time. So in the code that i provided you would need to require this DB class file at the top of the page, then you can invoke it and use the query method like i showed to retrieve the data from the database.

Mike Baxter
Mike Baxter
4,442 Points

Thanks for the explanation Rodger Voelkel , that's really helpful!