Not showing sub category

[jack55]

Hi, i’ve got categories working great but i want to go one step further and do sub-categories! I can’t see what i’m doing wrong so please can someone with another set of eyes have a look! I’m using PDO and when i remove the bits in // it shows up just not in the dropdown so please can someone have a look and point me in the right direction because my brain really can’t see what is wrong!

Thanks in advance!

Here is my SQL
https://preview.ibb.co/d2mWH7/imghost1.png

Here is my code:

function.php file

public function get_categories($id = null){
   
   $query = $this->db->prepare("SELECT * FROM `dsptesty_categories` WHERE active = 1");
   $query->execute();
   $categories = array();
   while($row=$query->fetch(PDO::FETCH_ASSOC)){
    $categories[] = $row;
    //$this->dispsubcategories($row['id']);
   }
   
   return $categories;
}

public function dispsubcategories($parent_id) {

   $query = $this->db->prepare("SELECT * FROM dsptesty_categories, dsptesty_subcat
									  WHERE ($parent_id = dsptesty_categories.id) AND ($parent_id = dsptesty_subcat.cat_id)");
   $query->execute();
$scategories = array();
		 while($row=$query->fetch(PDO::FETCH_ASSOC)){
		     $scategories[] = $row;
		//echo $row['name'];
		}
		  return $scategories;
	}

Here is the header.php

    <ul class="navbar-nav mr-auto">
      <?php
$category = $product->get_categories();
foreach($category as $cat) {

$subcat = $product->dispsubcategories($cat['id']);
$subcat = $subcat['name'];

?>
      <li class="nav-item dropdown">
        <a class="nav-link dropdown-toggle" href="#" id="navbarDropdown" role="button" data-toggle="dropdown" aria-haspopup="true" aria-expanded="false">
          <?php echo $cat['name']; ?>
        </a>
        
        <div class="dropdown-menu" aria-labelledby="navbarDropdown">
          <a class="dropdown-item" href="<?php echo $setting['website_url']; ?>/category.php?id=<?php echo $cat['id']; ?>"><?php echo $subcat; ?></a>
        </div>

      </li>
      <?php } ?>
    </ul>

Currently it looks like this:

Thanks very much!

[SamA74]

Have you tried running that query directly in sql (or phpmyadmin) to see if it works?

"SELECT * FROM dsptesty_categories, dsptesty_subcat
									  WHERE ($parent_id = dsptesty_categories.id) AND ($parent_id = dsptesty_subcat.cat_id)"

I’m not sure it’s right. It requires id and cat_id to be identical (same as $parent_id), the dsptesty_categories table doesn’t even have a cat_id column, so how will it ever be true?

Aside from the issue you are having, you are not using prepared statements correctly.
In the first function it’s a fixed query with no variables, so no need at all to prepare it.

In the second function you place the variables directly into the query instead of using placeholders. This defeats the object of using prepared statements.

Also the creation of the $categories array and making it an exact duplicate of the $row array seems redundant too. Why not just return $row?

[jack55]

Hi Sam

Thanks for you help!

I’m not sure it’s right. It requires id and cat_id to be identical (same as $parent_id), the dsptesty_categories table doesn’t even have a cat_id column, so how will it ever be true?

Regarding the above the cat_id needs to be the same as id and that should tell it where to put the sub categories! If my code does not say that please can you point out where the mistake is!

Aside from the issue you are having, you are not using prepared statements correctly.
In the first function it’s a fixed query with no variables, so no need at all to prepare it.

Im aware i was just doing it for quickness and testing

Thanks Jack

[jack55]

@SamA74

@jack55

<div class=“dropdown-menu” aria-labelledby=“navbarDropdown”>
<a class=“dropdown-item” href=“<?php echo $setting[‘website_url’]; ?>/category.php?id=<?php echo $cat[‘id’]; ?>”><?php echo $subcat; ?></a>
</div>

I feel like this is the bit that might be messing it up! Do i need an of statement or something?

Thanks

[SamA74]

I still think it’s this query:-

"SELECT * FROM dsptesty_categories, dsptesty_subcat
			WHERE ($parent_id = dsptesty_categories.id) AND ($parent_id = dsptesty_subcat.cat_id)");

I don’t understand why you are selecting from both tables. Should it not just be…

"SELECT name FROM dsptesty_subcat WHERE cat_id = :parent_id");

…?

[phpRob]

@jack55

<?php } ?>

Is this a problem. You have just a brace in a php-tag. Not sure if that would halt script execution. I would think so since not syntactically correct or doing anything.

[jack55]

@SamA74

Hi, i changed the SQL but i still get this error

public function get_categories($id = null){
   
   $query = $this->db->prepare("SELECT * FROM `dsptesty_categories` WHERE active = 1");
   $query->execute();
   $categories = array();
   while($row=$query->fetch(PDO::FETCH_ASSOC)){
    $categories[] = $row;
    //$this->dispsubcategories($row['id']);
   }
   
   return $categories;
}

public function dispsubcategories($parent_id) {

   //$query = $this->db->prepare("SELECT * FROM dsptesty_categories, dsptesty_subcat
								//	  WHERE ($parent_id = dsptesty_categories.id) AND ($parent_id = dsptesty_subcat.cat_id)");
$query = $this->db->prepare("SELECT name FROM dsptesty_subcat WHERE cat_id = :parent_id");
$query->bindParam(':parent_id', $parent_id);
   $query->execute();
$scategories = array();
		 while($row=$query->fetch(PDO::FETCH_ASSOC)){
		     $scategories[] = $row;
		//echo $row['name'];
		}
		  return $scategories;
	}

the php looks fine to me i just cant see why i keep getting the error regarding ‘name’

I feel like its something to do with this bit of code!

    <ul class="navbar-nav mr-auto">
      <?php
$category = $product->get_categories();
foreach($category as $cat) {

$subcat = $product->dispsubcategories($cat['id']);
$subcat = $subcat['name'];

?>
      <li class="nav-item dropdown">
        <a class="nav-link dropdown-toggle" href="#" id="navbarDropdown" role="button" data-toggle="dropdown" aria-haspopup="true" aria-expanded="false">
          <?php echo $cat['name']; ?>
        </a>
        <!--sub cat list here-->
        <div class="dropdown-menu" aria-labelledby="navbarDropdown">
          <a class="dropdown-item" href="#"><?php echo $subcat; ?></a>
        </div>

      </li>
      <?php } ?>
    </ul>

[jack55]

that bit of code is for the foreach

    <ul class="navbar-nav mr-auto">
      <?php
$category = $product->get_categories();
foreach($category as $cat) {

$subcat = $product->dispsubcategories($cat['id']);
$subcat = $subcat['name'];

?>
      <li class="nav-item dropdown">
        <a class="nav-link dropdown-toggle" href="#" id="navbarDropdown" role="button" data-toggle="dropdown" aria-haspopup="true" aria-expanded="false">
          <?php echo $cat['name']; ?>
        </a>
        <!--Sub cat here-->
        <div class="dropdown-menu" aria-labelledby="navbarDropdown">
          <a class="dropdown-item" href="#"><?php echo $subcat; ?></a>
        </div>

      </li>
      <?php } ?>
    </ul>

[SamA74]

@jack55

that bit of code is for the foreach

Is it…

@jack55

$subcat = $subcat[‘name’];

or…

@jack55

<?php echo $cat[‘name’]; ?>

…?
Try a var_dump of the offending variable to see what’s there.

[jack55]

@SamA74

var dump data

The data shows in the right rows etc just it doesnt know what name is

    <ul class="navbar-nav mr-auto">
      <?php
$category = $product->get_categories();
foreach($category as $cat) {

var_dump($subcat = $product->dispsubcategories($cat['id']));
var_dump($subcat = $subcat['name']);
?>
      <li class="nav-item dropdown">
        <a class="nav-link dropdown-toggle" href="#" id="navbarDropdown" role="button" data-toggle="dropdown" aria-haspopup="true" aria-expanded="false">
          <?php echo $cat['name']; ?>
        </a>
        
        <div class="dropdown-menu" aria-labelledby="navbarDropdown">
          <a class="dropdown-item" href="<?php echo $setting['website_url']; ?>/category.php?id=<?php echo $cat['id']; ?>"><?php echo $subcat; ?></a>
        </div>

      </li>
      <?php } ?>
    </ul>

It shows NULL for the 2nd var dump so something is not right

Thanks for your help so far Sam

[phpRob]

This doesn’t directly help you, but I want to suggest that you think about how to structure this solution better. “Better” to me being a separation of the php and html markup. I don’t use a templating engine nor am I suggesting one–but I bet you could debug/maintain easier if you created a single string in your php (that includes needed html markup) and just spit it out to the browser. This frees you from bugs related to seeing when php stops and the html markup begins in the page-processing on the server. This I understand is one of those areas of personal preference. And then about the sql. I would consider doing a join on the parent and child table and get a single recordset and process it and it only it when generating all of your output. This would/could simplify the logic–but also an area of preference. You would do a sort by parent, child column in the sql and simply transition the html output when a record changes from one parent to the next parent.

[jack55]

Thanks i will consider that but for now i want to focus on getting categories working! If you look at the post above its shows the results in the array so i just cant see whats wrong!

Thanks

[jack55]

No worries i fixed it myself! I needed another forloop!

Thanks

[phpRob]

Great you got it. You beat me to the punch. I had this for you. I worked on it. Forgive the variable name changes.

<?php
$html = '<ul class="navbar-nav mr-auto">';

$arrCategories = $product->get_categories();
foreach($arrCategories as $cat){
	$html .= '<li class="nav-item dropdown">';

	$html .= '<a class="nav-link dropdown-toggle" href="#" id="navbarDropdown" role="button" data-toggle="dropdown" aria-haspopup="true" aria-expanded="false">';
	$html .= $cat['name'];
	$html .= '</a>';

	$arrSubCategories = $product->dispsubcategories($cat['id']);
	foreach($arrSubCategories as $subCat){
		$html .= <<<RFGTFR
				<div class="dropdown-menu" aria-labelledby="navbarDropdown">\'
				<a class="dropdown-item" href="{$setting['website_url']}/category.php?id={$cat['id']}">
				{$subCat['name']}
				</a>
				</div>
RFGTFR;
	}

	$html .= '</li>';
}

$html .= '</ul>';
echo $html;

[jack55]

wow, so this is what you meant by the PHP + HTML bit!

Even though my code works i am going to convert over to what you produced for me its really good!

Thanks Rob

One other thing to ask tho is as ur name is phpRob im sure you are really good at PHP so is there a way to have 2 radio buttons one saying main category and the other sub categroty and when u click the main one it runs the main insert sql and when u click the sub one it runs the sub cat insert sql?

This is my current code!

	public function addcat($name){
    
		$name = trim($name);
	    if(empty($name)){
		$this->error = 'Please input all details';
		return false;
		}
		$add = $this->db->prepare("INSERT INTO " . PFX . "categories (`id`, `name`, `active`) VALUES (NULL, :name, '1')");
      	$add->bindParam(':name', $name);
		$add->execute();
	    	if($add){
		$this->msg = "Category added successfully";
		return true;
		}	
		$this->error = 'Category saved';
		return false;	
		
}

Thanks in advance

[phpRob]

I gotta take a little break so anyone please feel free to help jack please if you thought about doing so. Thanks.

[phpRob]

Hi. You might want to start a new post on this in the html/javascript section of the forums, giving specific code you have so far. You will likely get some targeted help this way. In this instance, it would be hard to think about what php is needed when you really are still nailing down what you should do, or how to do it, in the UI. Good luck! Oh, about php “heredoc” syntax in the sample code I gave you. Read up on that in the php manual if you don’t know about it yet. It breaks if you indent the closing label in the heredoc. And then how to reference array/object properties in a heredoc: braces are needed as you see there. example: {$obj->prop} {$arr[$idx]}

[droopsnoot]

Yes, all you need to do is look at which of the radio buttons is selected in the code your submit button calls, and run the appropriate query.