Examen Prog-Web 2014-2015

Partie 1: Soit le script PHP ci-dessous.

1. Donner une interprétation des messages Message 1,.., Message 17.
2. Donner la base de données générée sous forme d’un modèle Entités/Associations.
3. Donner le contenu de chaque table générée (sous forme de tableau).

<?php

   $c=mysql_connect("localhost","root","") or die ("Message 1");

   $req="drop database `bdd`"; $res=mysql_query($req);

   if ($res) echo "Message 2.";

   $r="create database `bdd`";  

   $res=mysql_query($r,  $c) or die("Message 3.");



   $bdd=mysql_select_db("bdd",  $c);  if(!$bdd) die("Message 4.");

   $req="drop table `Table1`";  $res=mysql_query($req);  

   if ($res) echo " Message 5.";



   $req="create table `Table1` ( `Num1` int(4), `lib1` varchar(25), 

       `lib2` varchar(25),  `val1` int(2))";

   $res=mysql_query ($req)  or die("Message  6.");



   $req="insert into `Table1` ( `Num1` , `lib1` , `lib2`, `val1` ) 

      values

       ('1000','occ11','L11','24'),  ('2000','occ12 ','L12 ','36')";

   $res=mysql_query($req)  or die("Message  7.");



   $req="drop table `Table3`";  $res=mysql_query($req); 

   if ($res) echo "Message 8.";



   $req="create table `Table3` ( `Code1` int(3), `lib4` varchar(30),

      `val4`  decimal(10,2))";

   $res=mysql_query($req) or die("Message 9.");



   $req="insert into `Table3` ( `Code1` , `lib4` , `val4` ) values 

      ('100','N31','400000.00'),  ('200','N32','150000.00'),

      ('300','N33 ','350000.00')";

   $res=mysql_query($req)  or die("Message  10.");



   $req="drop table `Table4`";

   $res=mysql_query($req); if ($res) echo  "Message 11.";



   $req="create table `Table4` ( `Code2` int(3), `Code3` int(2),

    `lib3` varchar(25))";

   $res=mysql_query($req)  or die("Message  12.");



   $req="insert into `Table4` ( `Code2`, `Code3`, `lib3` ) values 

      ('100','55','L11'),  ('200','56','L12 '), ('300','57','L11'),

      ('100','56','L12')";

   $res=mysql_query($req)  or die("Message  13.");

   $req="drop table `Table2`";   $res=mysql_query($req);

   if ($res) echo  "Message 14.";



   $req="create table `Table2` ( `Code4` int(3), `Num2` int(4),

     `val2`  decimal(9,2), `val3` int(2), `lib5` varchar(15))";

   $res=mysql_query($req)  or die("Message  15.");



   $req="insert into `Table2` ( `Code4` , `Num2` , `val2`, `val3`,

     `lib5` )  values

   ('100','1000','100000','26',''),  ('200','1000','110000','28',''),

   ('300','2000','250000','34',''), ('100','2000','240000','35',''),

   ('200','2000','260000','42','')";

   $res=mysql_query($req)  or die("Message 16.");

  

   mysql_close($c);

   echo "Message 17.";

   ?>

<?php
   $c= mysql_connect ( "localhost","root","") or die ( "erreur 1");
   $bdd= mysql_select_db ( "BDD") or die ( "erreur 2"); 
   $req = "Select `Num1` as `R1`, `Lib1` as `R2`,
          `Lib4` as `R3`, `Val4` as `R4`
   From `Table1`, `Table2`, `Table3`
   Where `Code1` = `Code4` and `Num1` = `Num2`
     and `Val4` >= '3' * `Val2` ";
   $res= mysql_query ( $req ) or die ( "erreur 3");

   while ( $i = mysql_fetch_array ( $res ) ) {
       $x= $i [ "R1" ];  $y= $i [ "R2" ]; 
       $z= $i [ "R3" ];  $w= $i [ "R4" ]; 
       echo "$x $y $z $w <br>"; }
   mysql_close ( $c );
   ?>

1. Donner les résultats affichés dans le navigateur après l'exécution de ce script.
2. Même question avec la requête suivante :
   $req = "Select `Code2` as `R1`, `Lib4` as `R2`, `Lib1` as `R3`, `Val4` as `R4`
                From `Table1`, `Table2`, `Table3`, `Table4`
                Where `Code1` = `Code4` and `Num1` = `Num2`
                     and `Code2` = `Code4` and `Lib2` =`Lib3` ";
3. Même question avec la requête suivante :
   $req= "select `Num1` as `R1`, `Lib1` as `R2`, `Lib2` as `R3`, avg(`Val2`) as `R4`
   from `Table1`, `Table2`  where `Num1` = `Num2` group by `Num1`
   order by avg(`Val2`) Desc
4. Même question avec la requête suivante :
       $req = "Select `Code4` as `R1`, `LIB4` as `R2`, `Val4` as `R3`,
                          sum(`Val2`) * '0.5' as `R4`
                   From `Table3`, `Table2`, `Table1`
                  Where `Code1` = `Code4` and `Num1` = `Num2`
                  Group by `Code1`
                  Having sum(`Val2`) > (`Val4` * '0.5') and count(`Num1`) >= 2";

   <?php
   $c= mysql_connect ( "localhost","root","") or die ( "erreur 1");
   $bdd= mysql_select_db ( "BDD") or die ( "erreur 2"); 
   $req = "Select distinct `Num2` , min (`Val2`) as `Val_min`
   From `Table2` Group by `Num2`";
   $res= mysql_query ( $req ) or die ( "erreur 3");

   while ( $i = mysql_fetch_array ( $res ) ) 
   {
       $NAO= $i [ "Num2" ];  $Val_min= $i [ "Val_min" ]; 

       $req="Select `Code1` , `LIB4`   
         From `Table3`, `Table2`, `Table1`, `Table4`
         Where `Code1` = `Code4` and `Num1` = `Num2` 
           and `Code2` = `Code4` and `Lib2` =`Lib3`
           and `Val2` = '$Val_min' and `Num2` = '$NAO' 
           and `Val2` < `Val4` / '3' ";

       $res2= mysql_query ( $req ) or die ( mysql_error());

       while ( $g = mysql_fetch_array ( $res2 ) ) 
       {
         $x= $g["Code1"]; $y= $g["Lib4"];  echo "$x $y<br>"; 
       }
   }
   mysql_close ( $c );
   ?>