[RESOLVED] Associative Arrays: how do you find out the field names?

[conjurer]

I am confused. I looked at the fetch_assoc array example in the manual, and I understand the example code. But what would you do if your query selected * from the table and you weren't sure what the colum names were? How can you figure out what fields are coming back to you so you can then output them to your screen?

So in the example below if the query were:

$query = "SELECT * FROM City ORDER by ID DESC LIMIT 50,5";

Then how would you know what values you could use in the square brackets for the $row[] so you could access your fields on the print line? For instance, in the example they use $row["Name"] and $row["CountryCode"] which were defined in the original query (see example from manual below), but if you returned all fields how would you know which were available to you for use?

while ($row = $result->fetch_assoc()) {
printf ("%s (%s)\n", $row["Name"], $row["CountryCode"]);
}

Hope my question makes sense. 😕

Here is the example code from the php5 manual:

<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

/* check connection */
if (mysqli_connect_errno()) {
   printf("Connect failed: %s\n", mysqli_connect_error());
   exit();
}

$query = "SELECT Name, CountryCode FROM City ORDER by ID DESC LIMIT 50,5";

if ($result = $mysqli->query($query)) {

   /* fetch associative array */
   while ($row = $result->fetch_assoc()) {
       printf ("%s (%s)\n", $row["Name"], $row["CountryCode"]);
   }

   /* free result set */
   $result->close();
}

/* close connection */
$mysqli->close();
?> 

[NogDog]

Since the fetch_assoc returns an array, you can use any of the array functions and commands, such as:

while ($row = $result->fetch_assoc())
{
  echo "<ul>\n";
  foreach($row as $key => $val)
  {
    echo "<li>$key: $val</li>\n";
  }
  echo "</ul>\n";
}

If you want a list of the columns to do some more complex stuff with, you could use:

$columns = array_keys($row);

[conjurer]

$columns = array_keys($row);

What would that give me? How would I use that NogDog? What I want to do is get the key names and then output them in a table format. So if I query the table and get 6 columns back I want to come up with:

Colum1 Colum2 Colum3 etc
Data Data Data

If I can figure out how to read the column names I think I can wrap them in <td> tags and then output the appropriate data under them in the same columns.

[NogDog]

$columns = array_keys($row) would populate an array called $columns with each element being a key in array $row.

For what it sounds like you want to do, I typically do something like:

$headers = FALSE;
echo "<table>\n";
while ($row = $result->fetch_assoc())
{
  if(!$headers)  // only do if header row hasn't been output yet
  {
    $headers = TRUE; // so we only do it the first time
    echo "<tr>";
    foreach($row as $key => $val)
    {
      echo "<th>$key</th>";
    }
    echo "</tr>\n";
  }
  echo "<tr>";
  foreach($row as $val)
  {
    echo "<td>$val</td>"
  }
  echo "</tr>";
} 
echo "</table>\n";

[conjurer]

Thanks! That looks like exactly what I am trying to do.

You are always so helpful. I really appreciate all the help NogDog!