Looping through Array in PHP returned from MySQL Q

Question

I have a part of an application that loops through a return of a MySQL query (as we all know) in the form of an Array. However, I need several different format settings placed on some of the items returned, for example, one column needs Japanese currency, the other has American currency and one of the returned items is a link to an image.

I would use the names of the column, however this same function that I am using to accomplish this will be used for many different tables.

This is what I have for the loop so far.

while($row = mysql_fetch_array($result)) {      
    for($i=0;$i<=count($row);$i++) {
    if($row[i]==$row['Yen_Price']) {// I didn't expect this to work...but this is what I would like to do.
        echo "Hello";
    }
    echo "<td>" . $row[$i] . "</td>";
    }
}

Answer 1

I'd markup these results specific to each table but if you want it to be ultimately flexible, try this smelly code

// using mysql_fetch_assoc() as we don't need the numeric indices
while($row = mysql_fetch_assoc($result)) {
    foreach ($row as $col => $val) {
        echo '<td>';
        switch ($col) {
            case 'US_Price'  :
                printf('$%0.2f USD', $val);
                break;
            case 'Yen_Price' :
                printf('¥%0.2f', $val);
                break;
            case 'image'     :
                printf('<img src="%s">', htmlspecialchars($val));
                break;
        }
        echo '</td>';
    }
}

Note that this is a known antipattern and you should really think about another way to approach the problem.

Answer 2

Use the below code. You can modify it as you want.

$select=" WRITE YOUR SELECT QUERY HERE ";
$queryResult= mysql_query($select);

//DECLARE YOUR ARRAY WHERE YOU WILL KEEP YOUR RECORD SETS
$data_array=array();

//STORE ALL THE RECORD SETS IN THAT ARRAY 
while ($row = mysql_fetch_array($queryResult, MYSQL_ASSOC)) 
{
    array_push($data_array,$row);
}


mysql_free_result($queryResult);


//TEST TO SEE THE RESULT OF THE ARRAY 
echo '<pre>';
print_r($data_array);
echo '</pre>';
// YOU CAN USE HERE FOR EACH LOOP AS PER YOUR REQUIREMENTS. 

Thanks

Answer 3

while ($row = mysql_fetch_assoc($result)) {
    foreach ($row as $key => $value) {
        if ($key == 'Yen_Price') {
            echo "Hello";
        }
        echo "<td>$value</td>";
    }
}

Having said that, using the same function to process all results from all possible tables will soon be rather unmanageable. You should customized this to fit the occasion like so:

while ($row = mysql_fetch_assoc($result)) {
    echo "<td>Foo: $row[foo]</td>";
    echo "<td>Bar: $row[bar]</td>";
}

Answer 4

Before I became a framework fanatic I used to have a bit different approach. My db library had set of methods that returned me array of record sets. This way I keep my db interaction totally separate from how I consume the record sets. Having done this, its easy to set up a grid template which can look at array and then act accordingly. Here is some pseudo code

$recordSets = $db->returnRecordSets("select some, columns from tablename");//extra param if I need array to be  associative
$recordSetsCount = count($recordSets);
if($recordSetsCount == 0){ echo 'Nothing to be done!'; //exit or return or break here}
for($i=0; $i< $recordSetsCount; $i++ == 0){
  $recordSet = $recordSets[$i];
  /*Inspect the $recordSet array and use it*/
}