Making a vertical menu with PHP and populating it with SQL

Question

Problem number 1 - Status = solved

The used table is:

CREATE TABLE Rubriek(
rubrieknaam     char(50)        not null, /* char(24) */
rubrieknummer   numeric(38)     not null, /* numeric(3) */
rubriek         numeric(38)     null, /* numeric(3) */
volgnr          numeric(38)     not null, /* nuemric(2) */
constraint pk_rubrieknummer primary key(rubrieknummer),
constraint fk_rubriek foreign key(rubriek) references Rubriek (rubrieknummer)
)

I've been trying to make a vertical menu in PHP that will be populated with the provided table but I'm having some difficulties with it. I've tried some code but I can't get it to work for me :(. The menu consists of categories (rubrieken) and subcategories.

A bit more clarification on the table:
rubrieknaam -> name of the category
rubrieknummer -> id of the category
rubriek -> tells us if a category is a subcategory, if not then the value will be null

A that the moment I'm getting a few 'Notice: Undefined index' errors. More specifically on the following lines:

 if($parent != $menu['rubriek']){//if not seen item before
 echo "<li>".$menu['rubrieknummer']."</li>";//echo item
 if($parent != $menu['rubriek']){//if not seen before not then the value will be null:

I've tried the following but to no avail though.

//connection to SQL Database
$connectionInfo = array( "UID"=>$uid,"PWD"=>$pwd,"Database"=>$databaseName);

//Connecting with SQL Authentication
$conn = sqlsrv_connect( $serverName, $connectionInfo);

$query = sqlsrv_query($conn, "SELECT * FROM Rubriek");
$parent=0;
$sub=0;
echo "<ul>";//start list
while($menu = sqlsrv_fetch_array($query)){
   if($parent != $menu['rubriek']){//if not seen item before
      if($sub != 0){echo "</ul>";}else{$sub++;}//if not first submenu, close submenu before. If first sub sub++.
      echo "<ul>";}//open submenu
   echo "<li>".$menu['rubrieknaam']."</li>";//echo item
   if($parent != $menu['rubriek']){//if not seen before
      $parent = $menu['rubriek']; //set to seen before so next loop will be recognised
   }
}
echo "</ul>"; //end list
?>

Problem Number 2

I've implemented the fix Royal BG mentioned and it works! But there is a different problem now. I also want to implement subcategories and I made subcategories for a few categories.

For example I made the next testdata:

    Rubrieknaam          | rubrieknummer | rubriek
    (= Name of Category) | (=Category ID)| (=Category is a subcategory of the following category):
     ----------------------------------------------------------------------------------
     Cars                | 1             | Null
     Audio               | 2             | Null
     Ford                | 3             | 1 (subcategory of Cars)
     Toyota              | 4             | 1 (subcategory of Cars)
     Speakers            | 5             | 2 (subcategory of Audio)
     Microphones         | 6             | 2 (subcategory of Audio)
     Instruments         | 7             | Null
     Guitar              | 8             | 7 (subcategory of Instruments)

The records are listed in the database in this order. the first two categories are listed as a category. The rest is all listed as a subcategory of 'Audio', even while 'Instruments' is supposed to be a new category instead of a subcategory.

I like the explanations you did for your SQL table, but how is it not working? Is it returning an error? Is it just not displaying correctly? Is it not displaying at all? — Chris Forrence
– Chris Forrence, Commented Jun 4, 2013 at 12:41
I'm sorry I forgot to elaborate a bit more on what's not working in the code. A that the moment i'm getting a few 'Notice: Undefined index' errors. More specifically on the following lines: if($parent != $menu['rubriek']){//if not seen item before echo "<li>".$menu['rubrieknummer']."</li>";//echo item if($parent != $menu['rubriek']){//if not seen before — Tippi
– Tippi, Commented Jun 4, 2013 at 12:52
as Royal Bg mentions in his answer you are not selecting rubriek and rubrieknummer in your sql statement so $menu isnt being populated with those fields so hence the undefined index. — Patrick Evans
– Patrick Evans, Commented Jun 4, 2013 at 13:39

Ivan Yonkov · Accepted Answer · 2013-06-04 12:46:07Z

1

$query = sqlsrv_query($conn, "SELECT rubrieknaam FROM Rubriek");

...

while($menu = sqlsrv_fetch_array($query)){
   if($parent != $menu['rubriek']){//if not seen item before

You have selected only one column -> rubrieknaam, and you are requesting columns named rubriek and rubrieknumer. You need to select them aswell, to be able to use them while fetching.

answered Jun 4, 2013 at 12:46

Ivan Yonkov

7,0291 gold badge20 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tippi Over a year ago

I've implemented the fix you've mentioned and it works! But there is a different problem now. I also want to implement subcategories and I made subcategories for a few categories. The problem is that the subcategories are all listed under the last category and are not added to their respective category. Does anyone know what I'm doing wrong? I'll edit my post to clarify it a bit more in a minute.

Collectives™ on Stack Overflow

Making a vertical menu with PHP and populating it with SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related