[RESOLVED] Blind mysql_fetch_array() extraction

jkurrle

I am looking at a function that is going to do a blind extraction with mysql_fetch_array(), but I have no idea how to extract the keys, so I can identify their values. I know that it is probably something simple, but I can't figure it out. As an example:

function blind_extract($results)
  {
  while ($row=mysql_fetch_array($results,MYSQL_ASSOC))
    {
    /* some code goes here */
    } // end mysql_fetch_array
  }  // end function blind_extract

How do I identify the keys as I am extracting them out with the data? Will array_search() work? If so, how?

paulnaj

How about array_keys() ?

jkurrle

It might work. I'm trying to create an associative array as I extract each row, so I can process both the key and its value for each pair in the extraction. Example, historical data would be extracting a date keyed to a value ($date=>$value). I need to know both values, so I can write these to a JSON file...

Weedpacket

The keys in array_keys() will be in the same order as their corresponding values, so I don't see why it shouldn't.

Since the keys will be the same for every array fetched, you can start off with [font=monospace]if(empty($keys)) $keys = array_keys($row);[/font].

A couple of additional comments: [man]array_combine[/man] is a neat way to make existing arrays associative; also, that json_encode will use the keys of an associative array for the corresponding JavaScript property names; oh - and this might or might not be helpful, but it's still cool - there's a user note under [man]array_map[/man] that describes a two-line method for creating the transpose of an array (so you can turn an ordinary array of associative arrays into an associative array (with the same keys) of ordinary arrays (with all the corresponding values)). Sorry. I'll stop rambling.

jkurrle

So how exactly am I going to use array_keys() in regards to my extraction? Will it work directly with the results of a query? For example:

$query="select * from table1";
$result=mysql_query($query)
  or die("The query \"$query\" experienced this error: ".mysql_error());
$row=mysql_fetch_array($result,MYSQL_ASSOC);
$array_of_keys=array_keys($row);

Would this be a correct example of how to use this? or would it be better to do this:

$query="select * from table1";
$result=mysql_query($query)
  or die("The query \"$query\" experienced this error: ".mysql_error());
$array_of_keys=array_keys(mysql_fetch_array($result,MYSQL_ASSOC));

I will still be able to access the key values after this, correct?

jkurrle

Took a little playing around, but I got it. Looks like this:

function sample_output($results,$query)
  {
  echo "<br /><font color=\"red\">The sample query is :</font><br /><br />$query<br /><br />";
  echo "<font color=\"red\">The data returned from this query is as follows (up to 5 rows):</font><br /><br />
        <table>\n";
  $row=mysql_fetch_array($results,MYSQL_ASSOC);
  $array_of_keys=array_keys($row);

  echo "<tr>";
  foreach($array_of_keys as $array_key)
    {
    echo "<th>$array_key</th>";
    }
  echo "</tr>\n";
  if($row)
    {
    extract($row);
    echo "<tr>";
    foreach($array_of_keys as $array_key) echo "<td>".$$array_key."</td>";
    echo "</tr>\n";
    } //end if($row)

  for ($i=1;$i<=4;$i++)
    {
    $row=mysql_fetch_array($results,MYSQL_ASSOC);
    if($row)
      {
      extract($row);
      echo "<tr>";
      foreach($array_of_keys as $array_key) echo "<td>".$$array_key."</td>";
      echo "</tr>\n";
      } //end if($row)
    } // end for 1..4 loop
  echo "</table>";
  } // end function

Weedpacket

I can suggest a bit of streamlining for that by using a do{...}while loop. If you get as far as the loop you know that there is at least one row, so you can wait until the end of the loop to check if there are any more.

function sample_output($results, $query)
{
	$sample_size = 5;

echo "<br /><font color=\"red\">The sample query is :</font><br /><br />$query<br /><br />";
echo "<font color=\"red\">The data returned from this query is as follows (up to $sample_size rows):</font><br /><br />";
if(mysql_num_rows($results))
{
	echo "\n<table>";
	$row = mysql_fetch_array($results, MYSQL_ASSOC);
	$array_of_keys = array_keys($row);
	echo "<tr>";
	foreach($array_of_keys as $key) echo "<th>$key</th>";
	echo "</tr>";
	$i = 0;
	do
	{
		$i++;
		extract($row);
		echo "<tr>";
		foreach($array_of_keys as $array_key) echo "<td>".$$array_key."</td>";
		echo "</tr>\n";
	} while(($i < $sample_size) && ($row = mysql_fetch_array($results, MYSQL_ASSOC)));
	echo "</table>\n";
}
}

I've added a test to check that there actually are rows returned, but only because I don't see one and an empty result would be buggy - if you did that before calling this function then doing it again here is unnecessary, of course.

Oh, and <font> tags? This is only for local testing, I hope 🙂