Php PDO query issue for navigation and content from MySQL db

[Skorpio]

Hi

I have a 4 button navigation, home, projects, about and contact. This is in one table, menu, this table has the relevant icon to display with the appropriate link and another field is the link itself, ?page=home. Menu is joined with the pages table using the following query

SELECT * FROM menu LEFT JOIN pages ON menu.id = pages.page_id

When I run this through phpmyadmin sql console everything is returned as expected, all pages match up with the navigation.

When I print_r($row); all 4 fields from the nav table and all 7 fields from the pages table, totalling 11 fields, display as per the query running in phpmyadmin which was as expected however, on the actual page it isn't going as hoped and expected.

I have 

    /* query for page data(navigation & title) */
       

 

 $sql= "SELECT * FROM menu LEFT JOIN pages ON menu.id = pages.page_id"; 
        $stmt = $db->query($sql); 
        $row = $stmt->fetchAll(PDO::FETCH_ASSOC);

    /* to go in php tags where table data required -

<?php echo $row['header']; ?>

*/
    // Sidenote, I realise prepare rather than query but need to get this working first
    
     

        echo "<pre>";
        print_r($row);
        echo "</pre>"; 

        // Everything, all records from both tables, showing up as expected in array
        
    If I use 

      

 $row = $stmt->fetch(PDO::FETCH_ASSOC);

        
        As expected I only get 1 record returned, all nav buttons are home which is the 1 record with all page data from the pages table and all icons from the menu table are the home icon, again as expected since it is the 1 record.
        
When I use fetchAll everything is returned undefined index,
e.g. Undefined index: content3 in C:\xampp\htdocs\test\index.php on line 70

The php used for the navigation is

      

        <?php echo $row['url']; ?>
        <?php echo $row['icon']; ?>
        <?php echo $row['header']; ?>

        
        the full link is
        
      

 <a href="<?php echo $row['url']; ?>"><i class="<?php echo $row['icon']; ?>"></i>&nbsp;<?php echo $row['header']; ?></a> 

        
        The above is listed 4 times for Home, Projects, About and contact.
        

As I know the SQL is working I realise I have made an error in the PHP but cannot work out what. As far as I can see I should be using fetch, rather than fetchAll, so as to only return 1 record as requested via the navigation links however fetchAll would give me the data required for the navigation to be labelled correctly.

I cannot work out where I am going wrong. Any help would be appreciated.

Edited October 31, 2020 by Skorpio
Update with further information

[Skorpio]

In addition, the returned array when using fetch all.

[Barand]

Illegible.

[Skorpio]

What is?

As I say the SQL is working, it is the PHP thats the issue.

Edited October 31, 2020 by Skorpio

[dodgeitorelse3]

Very very hard to see the returned array

 

[Skorpio]

Apologies, here is the array

[Skorpio]

Although still looking to resolve the issue with my php above I am looking at other solutions. Although the followi8ng code populates my navigation menu correctly I am unable to get the page data to sync with the appropriate navigation choice.

        $sql= "SELECT * FROM menu LEFT JOIN pages ON menu.id = pages.page_id"; 
        $stmt = $db->query($sql); 
        $row = $stmt->fetchAll(PDO::FETCH_ASSOC);

        // To print array out
        echo "<pre>";
        print_r($row);
        echo "</pre>";

Then where the navigation is I have 

<?php
            
 foreach($db->query($sql) as $row){ ?>
    <a href="<?php echo $row['url']; ?>"><i class="<?php echo $row['icon']; ?>"></i>&nbsp;<?php echo $row['header']; ?></a>
 <?php } ?>

As I say the nav menu is returned correctly but I am unable to sync with the corresponding page from the pages table, the join SQL statement above returns the full array from both tables synced but the issue I am having is the closing brace. I am unable to place it further down the page to enable all the relevant areas of the page to echo the relevant data from the pages table.

As per screenshot2 attached it is the placement of the closing brace preventing me from having the corresponding navigation selection and corresponding page showing as the data areas for the pges table are outside of the foreach .

[requinix]

I can't tell: are you still trying to solve the undefined index problem?

When you use fetchAll, $row (that being the variable you chose to assign the results to) will be an array of arrays. You need to loop over that just like you did with ->query.

If you write that code out you'll get

foreach ($row as $row) {

Now, doesn't that look a little weird?

[Skorpio]

I am trying to resolve the initial problem, getting nowhere fast. so it should read $

8 hours ago, Skorpio said:

$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

As the variable is storing all data records? 

So

11 minutes ago, requinix said:

foreach ($row as $row) {

Should read 

foreach ($row as $rows){

Is that what you are meaning requinix?

The foreach that I am using in the 2nd example

1 hour ago, Skorpio said:

foreach($db->query($sql) as $row){ ?> <a href="<?php echo $row['url']; ?>"><i class="<?php echo $row['icon']; ?>"></i>&nbsp;<?php echo $row['header']; ?></a> <?php } ?>

works as far as the navigation however I cannot include the content for the page within the foreach which makes the join redundant. I am unsure what the solution is to this but I feel as though I am going round in circles.

 

8 hours ago, Skorpio said:

$sql= "SELECT * FROM menu LEFT JOIN pages ON menu.id = pages.page_id";          $stmt = $db->query($sql);          $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

The above, my first code with the foreach loop would present me with the same issue of not being able to sync the page contact with the navigation.