Build a Dynamic Associative Array of Field Names from Table

[jmarco]

Say you type in the shell at the mysql prompt "SHOW COLUMNS FROM table_name;" (DESCRIBE will work too I guess) and you yield:

+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| school   | tinytext    |      |     |         |       |
| band     | tinytext    |      |     |         |       |
| director | tinytext    |      |     |         |       |
| address  | tinytext    |      |     |         |       |
| phone    | varchar(14) |      |     |         |       |
| email    | tinytext    |      |     |         |       |
| bandsize | int(4)      |      |     | 0       |       |
| tickets  | int(4)      |      |     | 0       |       |
| class    | char(2)     | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

If you want to build a dynamic associative array of the field names listed above then this will accomplish the task:

// CONNECT TO DB //
$query = "SHOW COLUMNS FROM bands;";
$result = mysql_query($query, $link);
$i = 0;
while($data = mysql_fetch_array($result, MYSQL_ASSOC)) {
  foreach($data as $item) {
    $build[$i] = "$item";
    $i++;
  }
}
for($i = 0; $i < count($build); $i=$i+6) {
  // NOTICE THE INCREMATION BY 6 TO ONLY POPULATE THE NEW ARRAY ($rebuild) WITH
  // ASSOCIATIVE NAMES OF THE field OF THE TABLE AND NOT type, null, key, default or extra 
  echo $build[$i]."<br />\n"; // SHOWS THE FIELD NAMES
  $rebuild["$build[$i]"] = null; // REBUILD AN ASSOCIATIVE ARRAY WITH THE FIELD NAMES
}
print_r($rebuild);

yields:

school
band
director
address
phone
email
bandsize
tickets
class
Array ( [school] => [band] => [director] => [address] => [phone] => [email] => [bandsize] => [tickets] => [class] => )

you can populate the array by saying:

$rebuild['school'] = "Oakville";
$rebuild['band'] = "Marching Tigers";
$rebuild['director'] = "Mr. Mister";
$rebuild['address'] = "Milburn Rd.";
$rebuild['phone'] = "555-5555";
$rebuild['email'] = "[e-mail]";
$rebuild['bandsize'] = "100";
$rebuild['tickets'] = "15";
$rebuild['class'] = "IIA";

Any suggestions, comments, improvements or usability ideas?
this may be nothing new or exciting but I just thought it up and was curious

[jmarco]

Modulating by 6 inside the foreach structure will get you the same results.

$i = 0;
while($data = mysql_fetch_array($result, MYSQL_ASSOC)) {
  foreach($data as $item) {
    $build[$i] = "$item";
    if ($i % 6 == 0) {
      $rebuild["$build[$i]"] = null;
    }
    $i++;
  }
}

[CaptianChaos]

// CONNECT TO DB //
$query = "SHOW COLUMNS FROM bands;";
$result = mysql_query($query, $link);
$i = 0;
while($data = mysql_fetch_array($result, MYSQL_ASSOC)) {
  while ( list($key, $val) = each ($data) ) {
    if ($key == 'Field')
      $build[$val] = '?';
  }
}
print_r($build);

You will have an associative array that looks like this:
$build['school'] = '?'
$build['band'] = '?'
$build['director'] = '?'
etc.

Is this what you want to do? You want an associative array with the field names as the keys, right?

[CaptianChaos]

or better yet:

// CONNECT TO DB //
$query = "SHOW COLUMNS FROM bands;";
$result = mysql_query($query, $link);
$i = 0;
while($data = mysql_fetch_array($result, MYSQL_ASSOC)) {
  $build[$data['Field']] = '?';
}
print_r($build);

[jmarco]

while($data = mysql_fetch_array($result, MYSQL_ASSOC)) {
  $build[$data['Field']] = '?';
}

Yep, that's the one I want.

I think I did something similar to that earlier, but I was trying to build $data as the array and not the array $build, so it was all jacked up.

That's the best way to code it though, thanks.

Now if I could find a use for this that would be better I guess.

If anyone around has used this method for anything let me know, but I'm going to toss it in my office's "tool box" for now along with one that builds a row array so that I can index it with $i and a for-loop.

[CaptianChaos]

Glad I could help.