How to fetch indices and names from the MySQL database?

I’m working on a website and it has a category dropdown. So my database has tabled called “category”, and it has four items in there:

category
id   name
1    first
2    second
3    thrid
4    fourth

And I’m trying to get all this info into an array, so I can use it in my HTML. But I’m getting a conversion error when I try this:

<div class="dropdown">
 <?php
  $db = new mysqli('localhost', 'root', 'password', 'db');
  $query = "SELECT id, name FROM category";
  $statement = $db->prepare($query);
  $statement->execute();
  $result = $statement->get_result();
  $categories = $result->fetch_all(MYSQLI_ASSOC);
?>
    <p class="dropdownSelect">Select Category</p>
       <div class="dropdownContent">
       <?php
        foreach ($categories as $id => $name) {
        echo "<p><a href="category.php?id=$id"> $name</a></p>";
        }
       ?>
            </div>
        </div>
    </div>

The error I’m getting is:

Notice: Array to string conversion (path) on line 35

Line 35 is where the echo statement is. So when I researched, the suggested solution was to use print_r and var_dump but both didn’t work in my case, and it just showed the same error message. What am I doing wrong here?

Answer

So the reason is that a variable that is on that line is being converted to a string for the purposes of echo. The only variables on that line are $id and $name.

Since id is the array key to name it’s not likely to be that!

That leaves us with $name:

• The way you’re getting the data means you’re returning an array OF arrays…

• Which means each $name will be an array in the format:

    $name = [
        'id' => SOME_ID,
        'name' => 'Some name'
    ];
  

• …i.e. not in the format it looks as though you’re assuming.

• With multiple results $categories will look something like:

    $categories = [
        0=>[
            'id'=>1,
            'name'=>'Name number 1'
        ],
        1=>[
            'id'=>2,
            'name'=>'Name number 2'
        ],
        2=>[
            'id'=>3,
            'name'=>'Name number 3'
        ],
        3=>[
            'id'=>4,
            'name'=>'Name number 4'
        ]
    ];
  

• Notice that the index you’re using as $id is actually the array key and not the id from the query? ($name then is the sub array)

Code

<div class="dropdown">
 <?php
  mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);  // Turn on error reporting
  $db = new mysqli('localhost', 'root', 'password', 'db');

  $query = "SELECT id, name FROM category";                   // SQL statement
  $statement = $db->prepare($query);                          // Prepare the query
  $statement->execute();                                      // Run the query
  $statement->store_result();                                 // Store the returned results set
  $statement->bind_result($id, $name);                        // Bind returned fields (id & name) to variables
?>
    <p class="dropdownSelect">Select Category</p>
       <div class="dropdownContent">
       <?php
           while($statement->fetch()) {                                     // Loop through result set
              echo "<p><a href="category.php?id={$id}">{$name}</a></p>";  // Print the line you want to print
           }
       ?>
            </div>
        </div>
    </div>