note 97738 deleted from function.mysql-result by danbrown
| From: | danbrown@php.net | Date: | Wed, 05 May 2010 20:33:34 +0000 |
| Subject: | note 97738 deleted from function.mysql-result by danbrown | ||
| References: | 1 | Groups: | php.notes |
| Request: | Send a blank email to php-notes+get-168985@lists.php.net to get a copy of this message | ||
Note Submitter: Chris Howard
----
Here is a nice little function to convert your mysql_query data into multi dimensional php arrays.
It's incredibly useful when you want to sort mysql_result data:
Lets say there is a table representing some sales with the fields: user_id [VARCHAR], currency
[VARCHAR], gross [FLOAT]
and you do a query:
$sqlStr = "SELECT user_id, currency, gross FROM sales";
$sqlRes = mysql_query ($sqlStr, $db) or die (mysql_error());
You can use the function below to create arrays in the following ways:
1) Simply convert the data into a PHP array
$arrData = sqlResToArray ($sqlRes);
Result:
Array ( [0] => Array ( [user_id] => 9e1 [currency] => USD [gross] => -8.49 ) [1] =>
Array ( [user_id] => 9e1 [currency] => USD [gross] => 4.25 ) [2] => Array ( [user_id]
=> 9e1 [currency] => GBP [gross] => -8.49 ) [3] => Array ( [user_id] => 9e1
[currency] => GBP [gross] => 4.25 ) )
2) Convert the data to a PHP array but focus on 'gross'
$arrData = sqlResToArray ($sqlRes, 'gross');
Result:
Array ( [0] => -8.49 [1] => 4.25 [2] => -8.49 [3] => 4.25 )
As you can see, it's simply an array of the field 'gross'
3) The clever bit, Convert the data to a 2 dimensional PHP array focusing on Gross but organised by
user id, then by currency
$arrData = sqlResToArray ($sqlRes, 'gross', 'user_id', 'currency');
Result:
Array ( [9e1] => Array ( [USD] => Array ( [0] => -8.49 [1] => 4.25 ) [GBP] => Array (
[0] => -8.49 [1] => 4.25 ) ) )
Good eh?
Here is the code:
function sqlResToArray ($sqlRes, $strTargetVal = false, $strLevel1 = false, $strLevel2 = false,
$strLevel3 = false) {
#Create the final array
$arrFinal = array();
$varName= "arrFinal";
#If there is a target field (i.e. make the last dimension a value and not an array), set final
value variable variable
if ($strTargetVal) {
$varTarget = "row['$strTargetVal']";
} else {
$varTarget = "row";
}
#Loop through each result as its own array
while ($row = mysql_fetch_assoc($sqlRes)) {
#Destruct the array by stripping out data that intends to be a level
if ($strLevel1) {
#Get the level 1 value
$dim1 = $row[$strLevel1];
unset ($row[$strLevel1]);
}
if ($strLevel2) {
#Get the level 1 value
$dim2 = $row[$strLevel2];
unset ($row[$strLevel2]);
}
if ($strLevel3) {
#Get the level 1 value
$dim3 = $row[$strLevel3];
unset ($row[$strLevel3]);
}
#Reconstruct the array into appropriate levels
if ($strLevel3) {
#If there is a target
if ($strTargetVal) {
${$varName}{$dim1}{$dim2}{$dim3}[] = $row[$strTargetVal];
} else {
${$varName}{$dim1}{$dim2}{$dim3}[] = $$varTarget;
}
} else if ($strLevel2) {
#If there is a target
if ($strTargetVal) {
${$varName}{$dim1}{$dim2}[] = $row[$strTargetVal];
} else {
${$varName}{$dim1}{$dim2}[] = $$varTarget;
}
} else if ($strLevel1) {
#If there is a target
if ($strTargetVal) {
${$varName}{$dim1}[] = $row[$strTargetVal];
} else {
${$varName}{$dim1}[] = $$varTarget;
}
} else {
#If there is a target
if ($strTargetVal) {
${$varName}[] = $row[$strTargetVal];
} else {
${$varName}[] = $$varTarget;
}
}
}
return $arrFinal;
}