8768 sujets

Développement web côté serveur, CMS

[Résolu] Passer a PDO

Bonjour à tous,
je fais une migration de mon site vers PDO et j'avoue que j'ai quelques soucis.
Le code suivant me permet de sortir des statistiques. Mais je n'arrive pas du tout à le convertir en PDO
<?php
$sql1 = "SELECT year(date_vente) an1,
        SUM(CASE WHEN MONTH(date_vente) = 1 THEN ttc/(1+(tva/100)) ELSE 0 END) Jan,    
        SUM(CASE WHEN MONTH(date_vente) = 2 THEN ttc/(1+(tva/100)) ELSE 0 END) Fev,    
        SUM(CASE WHEN MONTH(date_vente) = 3 THEN ttc/(1+(tva/100)) ELSE 0 END) Mar,
        SUM(CASE WHEN MONTH(date_vente) = 4 THEN ttc/(1+(tva/100)) ELSE 0 END) Avril,
        SUM(CASE WHEN MONTH(date_vente) = 5 THEN ttc/(1+(tva/100)) ELSE 0 END) Mai,
	SUM(CASE WHEN MONTH(date_vente) = 6 THEN ttc/(1+(tva/100)) ELSE 0 END) Juin,
	SUM(CASE WHEN MONTH(date_vente) = 7 THEN ttc/(1+(tva/100)) ELSE 0 END) Juil,
	SUM(CASE WHEN MONTH(date_vente) = 8 THEN ttc/(1+(tva/100)) ELSE 0 END) Aout,
	SUM(CASE WHEN MONTH(date_vente) = 9 THEN ttc/(1+(tva/100)) ELSE 0 END) Sept,
	SUM(CASE WHEN MONTH(date_vente) = 10 THEN ttc/(1+(tva/100)) ELSE 0 END) Oct,
        SUM(CASE WHEN MONTH(date_vente) = 11 THEN ttc/(1+(tva/100)) ELSE 0 END) Nov,
        SUM(CASE WHEN MONTH(date_vente) = 12 THEN ttc/(1+(tva/100)) ELSE 0 END) `Dec`, 
        SUM(ttc/(1+(tva/100))) as Total
FROM contacts WHERE $annee1 AND $commercial1 GROUP BY an1 ";
$req1 = mysql_query($sql1) or die('Erreur SQL !<br />'.$sql1.'<br />'.mysql_error());
$tab1 = array();
while($data1 = mysql_fetch_array($req1) ) {
//construction du tableau à partir du resultat de la requête
$tab1[ $data1['an1'] ] = $data1; 
}
?>
<table>
	<thead>
			<tr>
<th>Année</th><th>Janv</th><th>Fevr</th><th>Mars</th><th>Avr</th><th>Mai</th><th>Juin</th><th>Juil</th><th>Août</th><th>Sept</th><th>Oct</th><th>Nov</th><th>Dec</th>
			</tr>
	</thead>
	<tbody>
			<?php foreach( $tab1 as $an1=>$mois ) 
				{ ?>
				<tr>
					<th><?php echo $an1 ?></th>
					<td><?php echo number_format($mois[1], 2, ',', '');?></td>
					<td><?php echo number_format($mois[2], 2, ',', '');?></td>
					<td><?php echo number_format($mois[3], 2, ',', '');?></td>
					<td><?php echo number_format($mois[4], 2, ',', '');?></td>
					<td><?php echo number_format($mois[5], 2, ',', '');?></td>
					<td><?php echo number_format($mois[6], 2, ',', '');?></td>
					<td><?php echo number_format($mois[7], 2, ',', '');?></td>
					<td><?php echo number_format($mois[8], 2, ',', '');?></td>
					<td><?php echo number_format($mois[9], 2, ',', '');?></td>
					<td><?php echo number_format($mois[10], 2, ',', '');?></td>
					<td><?php echo number_format($mois[11], 2, ',', '');?></td>
					<td><?php echo number_format($mois[12], 2, ',', '');?></td>
				</tr>
		   <?php } ?>

Quelqu'un a une idée svp?
Modifié par jimmo (23 May 2014 - 14:09)
Bonjour,

Je proposerais bien ceci :
<?php
	// Configuration MySQL
	$bdd = new PDO('mysql:host=...;dbname=...', 'user', 'password');
	$requete = "
	SELECT year(date_vente) annee,
        SUM(CASE WHEN MONTH(date_vente) = 1 THEN ttc/(1+(tva/100)) ELSE 0 END) Jan,    
        SUM(CASE WHEN MONTH(date_vente) = 2 THEN ttc/(1+(tva/100)) ELSE 0 END) Fev,    
        SUM(CASE WHEN MONTH(date_vente) = 3 THEN ttc/(1+(tva/100)) ELSE 0 END) Mar,
        SUM(CASE WHEN MONTH(date_vente) = 4 THEN ttc/(1+(tva/100)) ELSE 0 END) Avril,
        SUM(CASE WHEN MONTH(date_vente) = 5 THEN ttc/(1+(tva/100)) ELSE 0 END) Mai,
		SUM(CASE WHEN MONTH(date_vente) = 6 THEN ttc/(1+(tva/100)) ELSE 0 END) Juin,
		SUM(CASE WHEN MONTH(date_vente) = 7 THEN ttc/(1+(tva/100)) ELSE 0 END) Juil,
		SUM(CASE WHEN MONTH(date_vente) = 8 THEN ttc/(1+(tva/100)) ELSE 0 END) Aout,
		SUM(CASE WHEN MONTH(date_vente) = 9 THEN ttc/(1+(tva/100)) ELSE 0 END) Sept,
		SUM(CASE WHEN MONTH(date_vente) = 10 THEN ttc/(1+(tva/100)) ELSE 0 END) Oct,
        SUM(CASE WHEN MONTH(date_vente) = 11 THEN ttc/(1+(tva/100)) ELSE 0 END) Nov,
        SUM(CASE WHEN MONTH(date_vente) = 12 THEN ttc/(1+(tva/100)) ELSE 0 END) `Dec`, 
        SUM(ttc/(1+(tva/100))) as Total
	FROM
		contacts
	WHERE :annee AND :commercial
	GROUP BY an1;";

	$reponse = $bdd->prepare( $requete );
	if ( $reponse->execute(array(':annee' => $annee1, ':commercial' => $commercial1)) )
	{
		$ventes = $reponse->fetchAll();
		$reponse->closeCursor();
?>
<table>
	<thead>
		<tr>
			<th>Année</th>
			<th>Janv</th>
			<th>Fevr</th>
			<th>Mars</th>
			<th>Avr</th>
			<th>Mai</th>
			<th>Juin</th>
			<th>Juil</th>
			<th>Août</th>
			<th>Sept</th>
			<th>Oct</th>
			<th>Nov</th>
			<th>Dec</th>
		</tr>
	</thead>
	<tbody>
<?php
		foreach ( $ventes as $annee )
		{
?>
		<tr>
			<td><?php echo $annee['annee']; ?></td>
			<td><?php echo number_format($annee['Jan'], 2, ',', '');?></td>
			...
			<td><?php echo number_format($annee['Dec'], 2, ',', '');?></td>
		</tr>
<?php
		}
?>
	</tbody>
</table>
<?php
	}
?>
Sujet clos
Aller à