Protoype
de la Solution
de l'examen Programmation Web
2016-2017

Partie 1:

<?php

$c=mysql_connect("@adresse","U","PW") or die ("Erreur de connexion");

$req="drop database `BDD`";
$res=mysql_query($req);
if ($res) echo "Ancienne BDD supprimée.";

$r="create database `BDD`";
$res=mysql_query($r, $c) or die("erreur de requête ou BDD existante...");

$bdd=mysql_select_db("BDD", $c);
if(!$bdd) die("Impossible d'accéder à la base de données :".mysql_error());

/// Création de Table1
$req="drop table `Table1`";
$res=mysql_query($req);
if ($res) echo "Ancienne table Table1 supprimée.";

$req="create table `Table1` ( `Ch11` int(4), `Ch12` varchar(30), `Ch13` varchar(30), `Ch14` int(2))";
$res=mysql_query($req) or die(mysql_error());

$req="insert into `Table1` ( `Ch11` , `Ch12` , `Ch13`, `Ch14` ) values
('1000','Chaine11','Chaine13','24'),
('2000','Chaine12 ','Chaine14 ','36')";
$res=mysql_query($req) or die(mysql_error());

/// Création de Table3
$req="drop table `Table3`";
$res=mysql_query($req);
if ($res) echo "Ancienne table Table3 supprimée.";

$req="create table `Table3` ( `Ch31` int(3), `Ch32` varchar(50), `Ch33` decimal(10,2))";
$res=mysql_query($req) or die(mysql_error());

$req="insert into `Table3` ( `Ch31` , `Ch32` , `Ch33` ) values
('100','Chaine15','400000.00'),
('200','Chaine16','150000.00'),
('300','Chaine17 ','350000.00')";
$res=mysql_query($req) or die(mysql_error());

/// Création de Table4
$req="drop table `Table4`";
$res=mysql_query($req);
if ($res) echo "Ancienne table Table4 supprimée.";

$req="create table `Table4` ( `Ch41` int(3), `Ch42` int(2), `Ch43` varchar(30))";
$res=mysql_query($req) or die(mysql_error());

$req="insert into `Table4` ( `Ch41`, `Ch42`, `Ch43` ) values
('100','55','Chaine13'),
('200','56','Chaine14 '),
('300','57','Chaine13'),
('100','56','Chaine14')";
$res=mysql_query($req) or die(mysql_error());

/// Création de Table2
$req="drop table `Table2`";
$res=mysql_query($req);
if ($res) echo "Ancienne table Table2 supprimée.";

$req="create table `Table2` ( `Ch21` int(3), `Ch22` int(4), `Ch23` decimal(9,2), `Ch24` int(2), `Ch25` varchar(10))";
$res=mysql_query($req) or die(mysql_error());

$req="insert into `Table2` ( `Ch21` , `Ch22` , `Ch23`, `Ch24`, `Ch25` ) 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(mysql_error());
///
mysql_close($c);
echo "Base de données Créée avec succés";
?>

Partie 2:

a.

1000 Chaine11 Chaine15 400000

b.

100 Chaine15 Chaine11 400000
100 Chaine15 Chaine11 400000
200 Chaine16 Chaine12 150000

c.

100 Chaine15 400000 17000
200 Chaine16 150000 185000

d.

1000 Chaine11 Chaine13 Chaine15

Partie 3 :

<?php

$c=mysql_connect("@adresse","U","PW") or die ("Erreur de connexion");

$bdd=mysql_select_db("BDD", $c);
if(!$bdd) die("Impossible d'accéder à la base de données :".mysql_error());

/// Création de Table5
$req="drop table `Table5`";
$res=mysql_query($req);
if ($res) echo "Ancienne table Table5 supprimée.";

$req="create table `Table5` ( `Ch31` int(3), `Ch32` varchar(50), `Ch43` varchar(30))";
$res=mysql_query($req) or die(mysql_error());

// Insertion des enregistrements
$req = "Select distinct `Ch41`, `Ch32`, `Ch43`
            From `Table3`, `Table4`
            Where `Ch31` = `Ch41` and `Ch33` >= '350000' ";

$res= mysql_query ( $req );
while ( $i = mysql_fetch_row ( $res ) )
{
    $x= $i [ 0 ];  
    $y= $i [ 1 ];
    $z= $i [ 2 ]; 

    $req="insert into `Table5` ( `Ch31` , `Ch32` , `Ch43` ) values ($x, $y, $z)";
    $r=mysql_query($req) or die(mysql_error());
}