1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Query Results Based on Columns, Not Rows

Discussion in 'PHP' started by tflight, Aug 18, 2006.

  1. #1
    I'm trying to figure out how to retrieve query results by column, not by row. I've never had to do this before and I'm having a difficult time trying to figure out how to approach it. Let's say I have a table of employees....

    -|- employeeID -|- FirstName -|- LastName -|- HomePhone -|-

    And I want to display the query results in an HTML table. But instead of the normal table format, I want to "rotate" the table and display the results by columns, not rows.

    -|- employeeID(1) -|- employeeID(2) -|- employeeID(3) -|-
    FirstName(1) -|- FirstName(2) -|- FirstName(3) -|-
    LastName(1) -|- Firstname(2) -|- FirstName(3) -|-
    HomePhone(1) -|- Firstname(2) -|- Firstname(3) -|-
    Department(1) -|- Department(2) -|- Department(3) -|-


    What is the most efficient way to "rotate" the results like this?
     
    tflight, Aug 18, 2006 IP
  2. sketch

    sketch Active Member

    Messages:
    898
    Likes Received:
    26
    Best Answers:
    0
    Trophy Points:
    68
    #2
    You can't select "colums" per se... you just select data. We just think of them in "rows" because that's how arrays are arranged.

    That said, I recommend placing the column names in an array, then doing a loop within a loop to output your data, like this:

    
    /* which columns you want from the DB */
    $keys = array ("ID", "firstname", "lastname", "phone", "department");
    
    // code to connect and select DB goes here
    
    // fetch the data, use implode so we select columns in the same
    // order as the "keys" array
    $find = mysql_query("SELECT ".implode(',', $keys)." FROM tablename ;");
    
    while ($found = mysql_fetch_assoc($find)) {
    	$data[]	= $found;
    }
    
    // go thru the data array within the $keys array
    for ($i = 0; $i < count($keys); $i++) {
    	
    	echo "<tr>";
    	
    	for ($j = 0; $j < count($data); $j++) {
    		echo "<td>".$data[$j][$keys[$i]]."</td>";
    	}
    	
    	echo "</tr>";
    }
    
    Code (markup):
     
    sketch, Aug 18, 2006 IP
    tflight likes this.
  3. tflight

    tflight Peon

    Messages:
    617
    Likes Received:
    38
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Thanks a bunch, Sketch. Some green coming your way! One problem I'm having though. The query is working find and pulling the correct data. And even the table is being drawn with the correct number and rows and cells based on the query results, however there isn't any data being displayed in each cell.

    Is there something wrong in this part of the code?
    echo "<td>".$data[$j][$keys[$i]]."</td>";
    PHP:
    It looks like everything else is working perfectly as the correct number of rows and columns are being drawn... just without any data in them.
     
    tflight, Aug 18, 2006 IP
  4. sketch

    sketch Active Member

    Messages:
    898
    Likes Received:
    26
    Best Answers:
    0
    Trophy Points:
    68
    #4
    I tried it out locally and it worked... could be a difference in PHP or MySQL versions. Do a print_r($data) and see if that's populated.
     
    sketch, Aug 18, 2006 IP
  5. tflight

    tflight Peon

    Messages:
    617
    Likes Received:
    38
    Best Answers:
    0
    Trophy Points:
    0
    #5
    Got it figured out. I was winding up with elements like $data[0][employeeID] instead of $data[0][0]. Thanks for the help, works perfectly now!
     
    tflight, Aug 18, 2006 IP
  6. sketch

    sketch Active Member

    Messages:
    898
    Likes Received:
    26
    Best Answers:
    0
    Trophy Points:
    68
    #6
    Actually, you were supposed to have $data[0][employeeID] ... that's why I had the column names in $keys, so that when you called $data[$j][$keys[$i]] you'd get the right output. But, as long as you got it to work that's what matters :)
     
    sketch, Aug 19, 2006 IP