Stepping through all array keys on returned $query

[jcanker]

I'm trying to develop some php that will execute a MySQL query and move the results into XML.  In this particular instance, only one record is expected to be returned, and that one row will contain all the customer data, such as last name, first name, address1, etc.

 

While I can get my code to work and create the xml if I hard-code in all the keys (MySQL column names), I would like it to dynamically step through all the keys/MySQL column names so that if the db changes later (add more columns, for instance) the code generating the XML won't need to be altered. 

 

It should take the MySQL column name, create an element based on the column name, then create a text node and insert the value. 

 

Currently I'm just at the spot of testing by echoing the appropriate key/value pairs.  The code is stopping after the first key/value without going through the rest of the columns.  What do I need to do in order to get it to dynamically step through each column and get the key/value?

 

Here's the relevant code:

if($_POST['buscode'])
{echo "buscode is: ".$_POST['buscode']."<HR>";}
else 
{echo "ERROR WITH RETRIEVING BUSCODE<HR>";}
$buscode = $_POST['buscode'];
$query = "SELECT * " 
		."FROM customers " 
		."WHERE buscode = '$buscode'";
echo "Query will be: ".$query."<HR>";
$result = mysql_query($query);
if($result){echo "Success retrieving data from ats_ajax.<HR>";}
else{echo "Error retrieving data from ats_ajax\; MySQL error is:".mysql_error($result)."<HR>";}

////////////////////////////////////////
//  Let's test getting the keys and values from the array....
///////////////////////////////////////
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) 
{
	echo "This is step ".$i."<BR>"; 
	$key = key($row);
	echo "Key is: ".$key."<BR>";
	echo "Value is: ".$row[$key]."<BR>";
	$i++;

}//close of while

[gizmola]

2 things:

 

-change your fetch to mysql_fetch_assoc()

- use foreach()

[Jessica]

$row is an array of the one result. You'd need to use array_keys() to get all of the keys, then foreach() through them.

[jcanker]

You two rock!  Thank you!

I got it working with:

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) 
{
$arr = array_keys($row);
foreach ($arr as $a)
{
	echo "<BR>The key is: ".$a;
	echo "<BR>With a value of: ".$row[$a]."<BR>";
	//$i++;
}
}//close of while

 

I used fetch_array to leave me the option of using MYSQL_BOTH if I needed it.  In the long haul I'll probably use mysql_fetch_assoc once I get a good sense of everything working together as it develops. 

[gizmola]

Great.

 

FWIW, all mysql_fetch_assoc is, is a wrapper around mysql_fetch_array($result, MYSQL_ASSOC).    Under the circumstances I can see no reason for you to want the numeric key version, since it's a duplication of the same data you get from the associative key version.

[jcanker]

Yes, it's probably not needed specifically here, but I'm learning AJAX and with the project I'm planning, there might be times I need it, and I just the code to be as consistent across the board as possible, so I chose to use mysql_fetch_array for every instance and use the proper selector.  Mayhaps it's just noob thinking on my part, but soon a few other folks will be working on this project as well and I want consistency.

 

If anyone reading the original post cares, I got the dynamic element creation working:

<?php
///////////////////////////////
// xmldynamicarraytest.php
//
// This page will pull customer data from db
// and dynamically create xml for the 
// requested customer using MySQL column names
//////////////////////////////

//the logged in check using $_SESSION will go here


/////////////////////////////////
// we need this function later to move MySQL query to array--it will eventually be moved to the include file
////////////////////////////////
function db_result_to_array($result)
{
   $res_array = array();

   for ($count=0; $row = @mysql_fetch_array($result); $count++)
     $res_array[$count] = $row;

   return $res_array;
}



//connect to database--this function will also be moved to the include file
function db_connect()
{
$result = mysql_pconnect("localhost", "user", "passwd");
if (!$result)
{
	return false;
	echo "test entry--could not connect to database server  ";
}
if (!mysql_select_db("db_name"))
{
	return false;
	echo "  test entry--did not try to use db_name as database";
}
return $result;
}
$connect = db_connect();


/////////////////////
//  Here we will specify the record to pull, but once the base
//  page is written we will use the buscode sent via post
/////////////////////
$buscode = $_POST['buscode'];
$query = "SELECT * " 
		."FROM customers " 
		."WHERE buscode = '$buscode'";
$result = mysql_query($query);


///////////////////////////////////////////////////////////////
//  Now for the code to build the xml
//////////////////////////////////////////////////////////////

//create a new dom document to put all the elements into...
$doc = new DOMDocument;

//create new top level element, "Customers" in this instance
$XMLtopElem = $doc->createElement("AllCustomers");
$CreateXMLtopElem = $doc->appendChild($XMLtopElem);

//Create a 2nd-tier node to hold each individual customer for cases with more than 1 returned row
$XML2ndTier = $doc->createElement("Customer");
$CreateXMLCustomer = $CreateXMLtopElem->appendChild($XML2ndTier);

//Now get the returned data and include it with the XML for the 3rd-tier elements
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) 
{
$arr = array_keys($row);
foreach ($arr as $a)
{
	//create a 3rd-tier element, dynamically named for the current key
    	$XML[$a] = $doc->createElement($a); //create an element in Dom Doc
    	$createXMLElem = $CreateXMLCustomer->appendChild($XML[$a]);//append this particular child to 2nd-tier named CreateXMLTopElem
   		$textValue = $doc->createTextNode($row[$a]); //create a text node and populate it with data
	$postValue = $createXMLElem->appendChild($textValue); //append the new text node to the parent		
}
}//close of while

echo $doc->saveXML();
?>

[gizmola]

I'm unconvinced by your explanation.    Ajax has nothing to do with what version of these fetch routines you use.  I also think that most experienced developers know the various versions of the routines and even if they don't, there's php.net for that. 

 

At the end of the day, what you used and what I suggested you use are exactly the same function.  Mine is simply an alias that passes the parameter.