[Resolved] problem creating dynamic html table

OttoBufonto

Hi, not wure where I'm going wrong, but maybe someone can point me in the right direction.

I want to create a dynamic table with M rows and and N colums. Both are pulled from two sperate DB tables. One table (Table1) describes seasons and one (Table2) roles.
Building the table layout is not the problem.

I would like to add display logic, that adds the name of person 1 where his role and year matches the html table row/column intersect.

While building the table, for each cell I need to compare whether the row/comlumn value combination exist in the T3 array and if so pull the corresponding person name_ID /name.

Maybe I'm I'm thinking too complex, but I either end in indefinate loops or my differnt query result arrays don't get the matching result..

Cheers in advance for any pointers,
Otto

paulnaj

Hi Otto, welcome to the forum ...

I'm not sure I understand exactly what you mean ... could you try again.

Give us an hard example of what you want the table to look like (it sounds like a crosstab to me).

Paul 🙂

OttoBufonto

Hi Paul,
thanks for the warm welcome...

Ok I'll try again.

My table should look like this

__|role1|role2|role3|role4|role5|............|roleN
year1|name1|name2|__||_|||
year2||name3|_|name4||||
year3||_|name2||name6|||
year4|name5|name6||name9||||
yearN||_|||_||___|

My table3 has

year|role|name

Who had which role in what year.
But not all roles were covered in each year, so i will have a large number of empty cells.

Not sure how to explain better.

Otto

kkobashi

Otto... probably best if you posted the three database table schemas.

paulnaj

Hi ya Otto,

Yes ... that's what I call a crosstab ... it's quite an expensive (time-wise) operation. There's no slick method to this, just grind out the table.

Here's the sort of thing. I've guessed the names of the fields and since I can't debug this code easily, there could be many typo's ...

<html>
<body>
<?php
// This is going to contain a matrix (array of rows)
// mainly consisting of empty cells
$crosstab = array();

// Used to initialise each row of the crosstab
$default_row = array();

$role_ids = array();
$role_res = mysql_query("SELECT role_id FROM roles ORDER BY role_id");
while($row = mysql_fetch_row($role_res)){
	// Use this for the column headers
	$role_ids[] = $row[0];

// Build our default 'empty' row at same time
$default_row[$row[0]] = '&nbsp;';
}

$year_ids = array();
$year_res = mysql_query("SELECT year_id FROM years ORDER BY year_id");
while($row = mysql_fetch_row($year_res)){
	// Use this for the row headers
	$year_ids[] = $row[0];

// Build the crosstab row

// Start with the default empty row
$crosstab_row = $default_row;

// Get all the people for this year
$person_res = mysql_query("SELECT role_id, name FROM people ORDER BY role_id WHERE year_id = ".$row[0]);
while($hash = mysql_fetch_array($person_res)){

	// Over-write only those cells that have a name
	$crosstab_row[$hash['role_id']] = $hash['name'];

}

// ... and add it to the matrix
$crosstab[] = $crosstab_row;
}

echo '
<table border="1" cellpadding="2" cellspacing="0">
	<tr>
		<th>&nbsp;</th>
		<th>'.join('</th><th>', $role_ids).'</th>
	</tr>';
for($i = 0; $i < count($year_ids); $i++){
	echo '
	<tr>
		<th>'.$year_ids[$i].'</th>
		<td>'.join('</td><td>', $crosstab[$i]).'</td>
	</tr>';
}
echo '
</table>';
?>
</body>
</html>

Come back if anything goes wrong or you don't get what's going on.

Paul 🙂

OttoBufonto

Hi Paul,
thanks heaps. I'll have a look at your suggestion. Plus I will try and read up about crosstab as well.

I'll go now and give it a try.

Cheers,
Otto

OttoBufonto

Paul,

It WORKED !!!! thank you so much... !!!!!

You are a star..

Otto

[edit: if I only knew how to change the topic titel (on forum level) to resolved..]

paulnaj

Hi Otto,

Glad it worked. 😉

There's a link just underneath the last post to mark the thread resolved.

Paul.