Create an html table like this one

[sarahaziz]

Hello guys
i have a table in DB that has messages exchanged between groups and i need yo create a table like this one (messages exchanged between 2 groups)
knowing that Gr1,.... are group names from DB and numbers are from DB too.

http://www.freeimagehosting.net/uploads/10b1fb8eeb.jpg

[Atli]

Hey.

If we are to be of any real help, we need to see how your database is set up.

[adsense]

hi,
php gdb function or use database

[sarahaziz]

@Atli ok sorry.First I just want to say too that you pointed out this mistake in a nice way not like most of the people in other forums and tutorials do.
Second there is table groups with group_id and login from where i get the to_group and from_group.
And there is table messages that has the to_group,from_g roup and messages.
Attached the structure of these 2 tables.
I feel confident that you are going to solve this problem.
Thanks

[code=sql]
--
-- Table structure for table `groups`
--

CREATE TABLE `groups` (
`id` int(5) NOT NULL auto_increment,
`complete_name` varchar(50) default NULL,
`login` varchar(50) default NULL,
`passwd` varchar(50) default NULL,
`email` varchar(50) default NULL,
`real_name` varchar(50) default NULL,
`phone_number` varchar(10) default NULL,
`observer` tinyint(4) default '0',
`observer_of` tinyint(4) default NULL,
`grp_abr` varchar(5) default NULL,
`bg_color` varchar(10) default NULL,
`kind_of_user` tinyint(4) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT= 42 ;

-- --------------------------------------------------------

--
-- Table structure for table `messages`
--

CREATE TABLE `messages` (
`Msg_ID` int(5) NOT NULL auto_increment,
`From_user` int(5) default '0',
`To_user` int(5) default '0',
`Date_Time` varchar(10) default NULL,
`Type_of_msg` varchar(50) default NULL,
`Msg_body` varchar(255) default NULL,
`Group_ID` int(3) default NULL,
`send_public` tinyint(1) NOT NULL default '0',
`To_Group_ID` int(3) default NULL,
`Simulation_ID` int(5) default '0',
`status_anim` tinyint(1) default '0',
`status_obs` tinyint(1) default '0',
`status_user` tinyint(1) default '0',
PRIMARY KEY (`Msg_ID`),
KEY `Group_ID` (`Group_ID`),
KEY `Simulation_ID` (`Simulation_ID `),
KEY `From_user` (`From_user`),
KEY `To_user` (`To_user`),
KEY `To_Group_ID` (`To_Group_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT= 165 ;
--
-- Constraints for dumped tables
--

--
-- Constraints for table `messages`
--
ALTER TABLE `messages`
ADD CONSTRAINT `messages_ibfk_ 39` FOREIGN KEY (`Group_ID`) REFERENCES `groups` (`id`),
ADD CONSTRAINT `messages_ibfk_ 40` FOREIGN KEY (`To_Group_ID`) REFERENCES `groups` (`id`);
[/code]

[sarahaziz]

@ adsense:Could you elaborate?

[Atli]

OK, that we should be able to do.

First, we could use a SQL query like this to count all the messages exchanged between groups.
[code=sql]SELECT
s.`complete_nam e` AS 'from',
r.`complete_nam e` AS 'to',
COUNT(*) as 'message_count'
FROM `messages` AS m
LEFT JOIN `groups` AS s
ON m.`Group_ID` = s.`id`
LEFT JOIN `groups` AS r
ON m.`To_Group_ID` = r.`id`
GROUP BY m.`Group_ID`, m.`To_Group_ID` ;[/code]

The, to process that into an array in PHP, we might do:
[code=php]
$data = array();
while($row = mysql_fetch_ass oc($result)) {
$data[$row['from']][$row['to']] += $row['message_count'];
}
[/code]
Now $data should contain something like:

Array
(
    [G1] => Array
        (
            [G2] => 5
            [G3] => 2
        )

    [G2] => Array
        (
            [G1] => 2
            [G3] => 4
        )

    [G3] => Array
        (
            [G1] => 7
            [G2] => 3
        )
)

Now all we have to do is loop through that and print it into a table. You would have to write that, as it would have to fit into your page.
But, as an example, you could print it as a plain-text table like so:
[code=php]header('content-type: text/plain; charset=utf8');

// Print headers
$columns = array_keys($dat a);
echo "*\t";
foreach($column s as $_column) {
echo "{$_column} \t";
}
echo "\n";

// Print data
foreach($data as $_row_name => $_row_data) {
// Add the dash (-) for empty cells
$_row_data[$_row_name] = '-';

echo "{$_row_name}\t ";
foreach($column s as $_col_name) {
echo "{$_row_dat a[$_col_name]}\t";
}
echo "\n";
}[/code]
Which might give you something like:

>>	G1	G2	G3	
G1	-	5	2	
G2	2	-	4	
G3	7	3	-

Hope that helps.

[sarahaziz]

Thank you so much Alti , of course it helped and worked but just out of curiosity if i want to build an HTML table what should i do?

[sarahaziz]

Thank you Alti i used your same code like that if you have any comments tell me please .YOU ROCK.

       $sql = mysql_query("SELECT s.login AS 'from', r.login AS 'to',COUNT(*) as 'message_count'
					FROM messages AS m
					LEFT JOIN groups AS s
					ON  m.Group_ID = s.id
					LEFT JOIN groups AS r
					ON  m.To_Group_ID = r.id
					GROUP BY m.Group_ID, m.To_Group_ID") or die (mysql_error());

$data = array();
while($row = mysql_fetch_assoc($sql)) 
{
    $data[$row['from']][$row['to']] += $row['message_count'];
}
 
// Print headers
	$columns = array_keys($data);
	echo "<table><tr><th>&gt;&gt;</th>";
	//echo ">>\t";
	foreach($columns as $_column) 
	{
      echo "<th>{$_column}</th>";
	}
	echo "</tr>";
 
// Print data
foreach($data as $_row_name => $_row_data) 
{
    // Add the dash (-) for empty cells
     $_row_data[$_row_name] = '-';
 
    echo "<tr><td>{$_row_name}";
    foreach($columns as $_col_name) 
    {
        echo "<td>{$_row_data[$_col_name]}</td>";
    }
    echo "</td></tr>";
}
echo "</table>";

[Dormilich]

note on line #17: althoug the string is correct, you may under certain circumstances encouter HTML problems due to the ">>" part (e.g. in XHTML). it would be safest, if you escape it as ">>" (maybe "»").

[sarahaziz]

Thanks Dormilich for the heads up i changed it

[Dormilich]

never mind, I’m glad if I can be of help (and additionally help improving HTML code).

what would happen in XHTML:
if you were to serve XHTML (as "applicatio n/xhtml+xml"), the XML parser would quit at "< " [note the whitespace] or "<<"* with an error message (something along "not well-formed") ("<" denotes the beginning of an XML tag). something similar would happen, is you were using "& " or "&" followed by characters, that do not form an entity (entity: "&nbsp;" or "&#38#160;" , not an entity: "you&me."), you’d get a "undefined entity" error.

* - or anything that is not a "Name" type character