Dynamically load data into bootstrap accordion navigation bar

Question

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;
 }

• Alex L: I see an SQL injection point.
• Christian Burgos: @AlexL ok, how do i improve it?
• Alex L: We usually used prepared statements. They are more convenient and safe than manually cleansing input, and far better than no cleansing.

Answer 1

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){

Answer 2

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.