How to fetch indices and names from the MySQL database?

Question

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?

Steven · Accepted Answer · 2020-11-10 00:35:51Z

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>

Emad Aljumaily · Accepted Answer · 2020-11-10 00:09:23Z

0

change this line

 <p><a href=\"category.php?categoryId=$id\"> $name</a></p>

with

 echo "<p><a href="'category.php?categoryId='$id"> '"$name"'</a></p>";

answered Nov 10, 2020 at 0:09

Emad Aljumaily

16 bronze badges

Collectives™ on Stack Overflow

How to fetch indices and names from the MySQL database?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related