3
\$\begingroup\$

I have made a bootstrap accordion navigation bar that loads data dynamically from a database with PHP.

Here are the two tables that I use:

CREATE TABLE IF NOT EXISTS `top_tier` (
  `id` varchar(50) NOT NULL,
  `name` varchar(255) NOT NULL,
  `date_created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `created_by` varchar(50) NOT NULL,
  `date_edited` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `status` int(11) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
)

CREATE TABLE IF NOT EXISTS `sub_tier` (
  `id` varchar(50) NOT NULL,
  `name` varchar(255) NOT NULL,
  `type` varchar(50) NOT NULL,
  `date_created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `created_by` varchar(50) NOT NULL,
  `date_edited` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `edited_by` varchar(50) NOT NULL,
  `parent_id` varchar(50) NOT NULL,
  `status` int(11) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
)

Basically the table top_tier has the main items of the accordion navigation bar. An entry in the top_tier table can have multiple children from the table sub_tier and that said entry from the sub_tier table can have children too from the column parent_id. When I click an item from the accordion that's from the top_tier table, it shows its children. Then if I click an item from the top_tier item, it shows its children items if it has any.

Here is the code for the accordion:

    <div id="MainMenu">
  <div class="list-group panel">
    <?php
    $top_items =  $obj->getTopTierItems();
    foreach($top_items as $top_item) {
    extract($top_item);
    ?>

    <a href="#<?php echo $id;?>" class="strong list-group-item" data-toggle="collapse" data-parent="#MainMenu">
      <?php echo $name;?> 
      <i class="fa fa-caret-down"></i>
    </a>

    <div class="output collapse" id="<?php echo $id; ?>">
      <?php 
      $sub_items = $obj->getSubTierItems($id); 
      foreach($sub_items as $sub_item) {
        $id2 = $sub_item['id'];
        $name2 = $sub_item['name'];
        $parent_id = $sub_item['parent_id'];
      ?>
        <a href="#<?php echo $id2?>" class="strong list-group-item" data-parent="#<?php echo $parent_id; ?>">
          <?php echo $name2;?>
        </a>

        <div class="output collapse" id="<?php echo $id2; ?>">

        </div>
        <?php
        }
        ?>
    </div>

    <?php 
    }
    ?>
  </div>
</div>

Here is the PHP that fetches the data from the top_tier table:

public function getTopTierItems(){

        $SQL = "
            SELECT

            id,
            name

            FROM 
                top_tier
            WHERE
                status = 1  
        ";              
        $results = $this->db_query_listx($SQL);
        return $results;
    }

Here is the PHP that fetches the data from the sub_tier table:

public function getSubTierItems($id){

        $SQL = "
            SELECT

            id,
            name,
            parent_id

            FROM
                sub_tier
            WHERE
                status = 1
            AND
                parent_id='$id'

        ";              
        $results = $this->db_query_listx($SQL);
        return $results;
    }

Here is the function that controls the data from the database:

public function db_query_listx($SQL){
        global $connect;
        if ($resultset = $connect->query($SQL)) {
            if ($resultset->num_rows > 0) {

                $data = array();
                    while( $row = $resultset->fetch_assoc() ) {
                        $data[] = array_change_key_case($row);
                    }
                }else {
                $data = NULL;
            }
        } else {
            $data = die("<CENTER><DIV class='error'>ERROR: " . $connect->error . "<BR> ( $SQL ) <HR>
                             Please report this to <a href='#'>webmaster</a>.</DIV></CENTER>");
        }
        $resultset->close();
        return $data;
    }
\$\endgroup\$
3
  • 1
    \$\begingroup\$ I see an SQL injection point. \$\endgroup\$
    – Alex L
    Jun 27, 2014 at 4:24
  • \$\begingroup\$ @AlexL ok, how do i improve it? \$\endgroup\$ Jun 27, 2014 at 4:25
  • \$\begingroup\$ We usually used prepared statements. They are more convenient and safe than manually cleansing input, and far better than no cleansing. \$\endgroup\$
    – Alex L
    Jun 27, 2014 at 4:29

2 Answers 2

3
\$\begingroup\$

Timestamp

Why do you insert an invalid value '0000-00-00 00:00:00' into your TIMESTAMP columns by DEFAULT? I think you would be much better served by using something relevant, like CURRENT_TIMESTAMP...

  `date_created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

And...

  `date_edited` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

Identifiers

Is there any particular reason your identifiers are varchar? Seems odd. Example:

  `id` varchar(50) NOT NULL,

To me should be:

  `id` INT NOT NULL AUTO_INCREMENT,

PDO

This function could use a MySQL procedure (and PHP PDO):

public function getTopTierItems(){

And this one especially:

public function getSubTierItems($id){
\$\endgroup\$
1
\$\begingroup\$

Your database structure needs work.

  1. id columns should almost always be INTs, not VARCHARs. That's just silly design. Similarly, parent_id should be an INT.
  2. created_by should be foreign keyed to some users table.
  3. status is not necessarily a good name. If you only need to differentiate between active and inactive, use a TINYINT and name it something like isActive. If you need more states, then assign a value like 'active' or 'inactive' or 'draft', instead of 1 and 0.
  4. There's no reason to have a separate table for top_tier and sub_tier. Rename sub_tier to something else (nav_items?) and get rid of top_tier. Whenever you have a top-tier item, set its parent_id to 0 or NULL.
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.