View with dynamic columns help

warpedghost

Hi. I was wondering if anyone could help me with this view.

I have 2 tables.

TableA (Holds Event Scores)

Player EventID Score
Jim | 1 | 3
Jim | 2 | 2
Jim | 2 | 4
Dave | 1 | 2
Dave | 2 | 2
Dave | 2 | 3
Dave | 3 | 1
Dave | 3 | 3

TableB (Holds Event Names)

Event EventID
Running | 1
Jumping | 2
Diving | 3

I wanted to join the two tables but only have 1 row for each player. The table would have a column for each Event in TableB with the players name and the event with the sum. So the end result would look like this:

View

Player Running Jumping Diving
Jim | 3 | 6 | 0
Dave | 2 | 5 | 4

Is it possible to create column names dynamically based off of a tables column value? If so, could anyone help me with this view?

motowater

What database do you use? I only know mysql, where you would execute something like...

$query = 'CREATE TABLE contact( '.
         'cid INT NOT NULL AUTO_INCREMENT, '.
         '$dynamicColumnName VARCHAR(20) NOT NULL, '.
         'PRIMARY KEY(cid))';
mysql_query("$query");

not sure if i answered your question or not...

Pikachu2000

If you are talking about formatting the result into an HTML table, this should get you started. It will take the names of the events and output them in cells at the top of the table after statically defining the first '<td>Name</td>' cell. This obviously assumes you are using MySQL as a DB.

$q1 = "select distinct from TableB order by EventID";
$r1 = mysql_query($q1);


// CHECK TO MAKE SURE DATABASE FIELDS QUERIED EXISTED AND HELD VALUES
if(mysql_num_rows($r1) > '0')
  {
      while($a1 = mysql_fetch_array($r1))  //CREATE THE ARRAY OF EVENT NAMES
      {  //FORM THE TABLE STRUCTURE AND POPULATE THE FIELDS
      Table = "<table width=\"somevalue\">\n<tr>\n<td>Name</tr>\n";
      Table .= "<td>$a1['Event']</td>\n";
      }
   $Table .= "</tr>\n</table>";  // CLOSE THE TABLE
   }
else  // IF QUERY WAS EMPTY
   {
   $Table = "No Events exist";
   }

There is probably a better way to do this, but I'm still fairly new with PHP.

warpedghost

Hi. Thanks for the replies. I am using MySQL. I was really trying to figure out how to do this in a Stored Procedure.