--
-- Structure de la table `videos_tbl`
--

CREATE TABLE IF NOT EXISTS `videos_tbl` (
  `idv` int(5) NOT NULL AUTO_INCREMENT,
  `nom_vid` varchar(40) NOT NULL,
  `type` enum('clip','documentaire','film') NOT NULL DEFAULT 'film',
  `taille` int(11) NOT NULL DEFAULT '0',
  `duree_secondes` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`idv`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=8 ;

--
-- Contenu de la table `videos_tbl`
--

INSERT INTO `videos_tbl` (`idv`, `nom_vid`, `type`, `taille`, `duree_secondes`) VALUES
(1, 'The Last Samurai', 'film', 0, 9240),
(2, 'Mission Impossible', 'film', 0, 6600),
(3, 'Enquete Exclusive', 'documentaire', 0, 5400),
(4, 'Mon ami la rose', 'clip', 0, 284),
(5, 'Planet Food', 'documentaire', 0, 3600),
(6, 'Thriller', 'film', 0, 5580),
(7, 'Fire Man', 'film', 0, 8760);

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

--
-- Structure de la vue `duree_totale_par_type`
--
DROP TABLE IF EXISTS `duree_totale_par_type`;

CREATE VIEW `duree_totale_par_type` AS select `videos_tbl`.`type` AS `type`,sum(`videos_tbl`.`duree_secondes`) AS `duree_totale` from `videos_tbl` group by `videos_tbl`.`type`;

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

--
-- Structure de la vue `videos_par_type`
--
DROP TABLE IF EXISTS `videos_par_type`;

CREATE VIEW `videos_par_type` AS select `v`.`idv` AS `idv`,`v`.`nom_vid` AS `nom_vid`,`v`.`type` AS `type`,`v`.`taille` AS `taille`,`v`.`duree_secondes` AS `duree_secondes`,`d`.`duree_totale` AS `duree_totale` from (`videos_tbl` `v` join `duree_totale_par_type` `d` on((`v`.`type` = `d`.`type`))) order by `v`.`type`;

<?php
#connexion BDD
mysql_select_db('test', mysql_connect('localhost','root','')) or die(mysql_error());

#executer la requête : liste des videos par type
$sql = 'SELECT * FROM `videos_par_type`';
$result = mysql_query($sql) or die(mysql_error());

#ordonner les videos listées dans un tableau
$tab_videos = array();
while( $result && $row = mysql_fetch_array($result))
{
	#stockage des vidéos par type
	$type = $row['type'];
	$tab_videos[$type]['duree_totale'] = $row['duree_totale'];
	$tab_videos[$type]['videos'][] = $row; #stockage des videos de même type
}
#debug
echo '<pre>'; print_r($tab_videos); echo '</pre>'; #ligne à désactiver

#affichage du tableau des vidéos par type
/*
* EXEMPLE D'AFFICHAGE
*
Films: Duree totale est: XXX
The Last Samurai: AAA
Mission Impossible: BBB
Thriller: CCC
Fire Man: DDD

Clips: Duree totale est: YYY
Mon ami la rose: EEE

Documentaire: Duree totale est: ZZZ
Enquete Exclusive: FFF
Planet Food: GGG
*/

echo '<h1>Liste des vidéos par type</h1>';
foreach($tab_videos as $type=>$valeur)
{
        #affichage du type et sa durée totale
	$duree_type_format_hms = date('H:i:s',strtotime('00:00:00')+$valeur['duree_totale']);
	echo '<p>'.$type .' : Duree totale est : '. $duree_type_format_hms .'</p><ul>';

        #affichage des vidéos de ce type
	foreach($valeur['videos'] as $video)
	{
		$duree_vid_format_hms = date('H:i:s',strtotime('00:00:00')+$video['duree_secondes']);
		echo '<li>'. $video['nom_vid'] .' : '. $duree_vid_format_hms .'</li>';
	}
	echo '</ul>';
}
?>

--
-- Table structure for table `videos_tbl`
--

CREATE TABLE IF NOT EXISTS `videos_tbl` (
  `idv` int(5) NOT NULL AUTO_INCREMENT,
  `nom_vid` varchar(40) NOT NULL,
  `clip` enum('0','1') NOT NULL DEFAULT '0',
  `documentaire` enum('0','1') NOT NULL DEFAULT '0',
  `films` enum('0','1') NOT NULL DEFAULT '0',
  `taille` varchar(10) NOT NULL,
  `duree` varchar(10) NOT NULL,
  PRIMARY KEY (`idv`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=8 ;

--
-- Dumping data for table `videos_tbl`
--

INSERT INTO `videos_tbl` (`idv`, `nom_vid`, `clip`, `documentaire`, `films`, `taille`, `duree`) VALUES
(1, 'The Last Samurai', '0', '0', '1', '', ''),
(2, 'Mission Impossible', '0', '0', '1', '', ''),
(3, 'Enquete Exclusive', '0', '1', '0', '', ''),
(4, 'Mon ami la rose', '1', '', '', '', ''),
(5, 'Planet Food', '', '1', '', '', ''),
(6, 'Thriller', '', '', '1', '', ''),
(7, 'Fire Man', '0', '0', '1', '', '');