PHP

PHP: swapping round columns & rows in field/record data

July 20th, 2010

I'm blogging this just because I couldn't find a solution via Google so I had to figure this out myself and it might save you some time if you're trying to do the same thing.

I have some PHP script that grabs some data out of a MySQL table. However, rather than display it in the normal way:

resultField1 resultField2 resultField3 resultField4 resultField5
2010-12-24 55 red 1 Miles Davis
2011-03-30 65 yellow 0 Kevin Coyne
2011-06-16 82 purple 1 Kate Bush

I want to swap round the rows and columns so that I display it like this:

resultField1 2010-12-24 2011-03-30 2011-06-16
resultField2 55 65 82
resultField3 red yellow purple
resultField4 1 0 1
resultField5 Miles Davis Kevin Coyne Kate Bush

The thing to be aware of is that when you use mysql_fetch_assoc to get stuff out of a database, for example:

while($resultArray = mysql_fetch_assoc($selectQuery)) {
    Do stuff here ...
}


what you get back is an associative array (that's a hash for folks like me who learnt this stuff in Perl before arriving at PHP). So in the above example $resultArray is an associative array - that is, a set of key/value pairs where the key is the field name and the value is the value in that field for that record. Each iteration through the while loop you get a different record.

So, in effect, it's like an array of associative arrays.

However, what you want in order to be able to swap round fields and columns is an associative array of arrays - that is, a set of key/value pairs where each key is a field name and each value is an array of the values for that field in each record in the data set. So what you need to do is:

  • Iterate through each record in the data set (i.e. each row in your results) .
  • For each field in the record, assign the field value to an array, a reference to which is the value of the key/value pair in the outer associative array.

If it sounds complicated, then I'm glad I'm not the only one. Actually, it's probably easier to understand by looking at the code:

assocArrayOfArrays-example-PrettyPrinting.html - This is a syntax-highlighted representation of the PHP.
assocArrayOfArrays-example.php.txt - This is the actual PHP saved as a .txt file so that you can see and download it.

And here's the PHP in action:
assocArrayOfArrays-example.php

Leave a comment

 

PHP (or Perl) one line if/then/else statements

August 8th, 2009

If you're toggling something between two states in PHP or Perl it's often handy to use an if/then/else one liner.

In pseudocode this goes like this:

<if this evaluates to TRUE> then <parse this> else <parse this>

All you need to do is replace the "then" with a question mark and the "else" with a colon:

<if this evaluates to TRUE> ? <parse this> : <parse this>

For example:

print  $trueOrFalse ? "you're telling the truth" : "you're lying";

Ignore the print command, it's not part of the if/then/else statement, it's just here to do something with the outcome of that statement.

The expression immediately to the left of the question mark is evaluated. The expression between the question mark and the colon is parsed if the expression evaluates to TRUE, otherwise the expression immediately to the right of the colon is parsed. So in the above example, either "you're telling the truth" or "you're lying" is printed, depending on whether $trueOrFalse is ... you guessed it ... TRUE or FALSE.

But perhaps a more common situation is toggling the value assigned to a variable. For example, toggling between TRUE and FALSE:

$trueOrFalse = $trueOrFalse ? FALSE : TRUE;

Here's a practical example of the use of if/then/else one liners. There's two in this chunk of PHP. The scroll box list below the code is the kind of thing this PHP produces.

<div style="overflow:auto; height:100px; width:300px; border:3px groove #DDD; padding:0">
<?php
    $alternateLine = FALSE;
    while($presidentsArray) {
        print "<div style=\"background-color:";
        print $alternateLine ? "#F5F8F9" : "white";
        print "; padding-bottom: 1px\"> &nbsp; &nbsp; <a href=\"someURL\" title=\"This link goes nowhere\">" .
          $presidentsArray['name'] . "</a></div>";
        $alternateLine = $alternateLine ? FALSE : TRUE;
    }
?>
</div>

Leave a comment



^ back to top ^

Page 1 of 11