Jump to content

Requête MYSQL


Recommended Posts

Bonjour

Je veux me créer une requête PHP pour me faire un rapport de vente.

J'ai besoin de connaitre les tables qui ont les objets à récupérer 

Objet à récupérer: Nom, mail, tel, commentaire, commande détaillé avec nombre et portion (déclinaison) 

Qlq1 pour me pointer vers les bonne tables et colonnes ??

Mon rapport final devrait ressembler à ceci (sans Excel): 

rapport.thumb.png.b3d3685720d1f30cf2e1d57b7d9adf65.png

Edited by Samx (see edit history)
Link to comment
Share on other sites

Bonjour.

 

À date, j'ai trouvé

Quote

ps_address
$sql = "SELECT * FROM `ps_address` ORDER BY `id_customer` ASC";
Dans ps_address il a id_customer, , lastname, firstname, phone

ps_orders
$sql = "SELECT * FROM `ps_orders`";
Dans ps_orders il y a des commandes avec l'ID de facture (déclinaison), id_cart, id_customer

ps_customer
$sql = "SELECT * FROM `ps_customer`";
Dans ps_customer il y a email

ps_order_detail
$sql = "SELECT * FROM `ps_order_detail`";
Dans ps_order_detail, product_id, product_name, total_price_tax_excl

ps_ganalytics_data
$sql = "SELECT * FROM `ps_ganalytics_data`";
Dans ps_ganalytics_data il y a id_cart et data

<html>
<head>
<title>Test de BD </title>
    <head>
        <title>Cours PHP / MySQL</title>
        <meta charset="utf8mb4_general_ci">
        <link rel="stylesheet" href="cours.css">
    </head>
<body>
<h1>Bases de données MySQL</h1>  
<h1>Objet à récupérer: Nom, mail, tel, commentaire, commande détaillée avec nombre et portion</h1> 
<?php

    $servername = '127.0.0.1';
    $user = 'stephte_test';
    $pass = 'wfmir!n839';
	$dbname  = 'stephte_test'; 

//On établit la connexion
            $conn = new mysqli($servername, $user, $pass);
            
            //On vérifie la connexion
            if($conn->connect_error){
                die('Erreur : ' .$conn->connect_error);
            }
            echo 'Connexion réussie';
			
 try{
                $dbco = new PDO("mysql:host=$servname;dbname=$dbname", $user, $pass);
                $dbco->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                
                /*Sélectionne les valeurs dans les colonnes firstname et lastname de la table
                 *users pour chaque entrée de la table*/
				$sth = $dbco->prepare("SELECT firstname, lastname, phone, id_customer FROM ps_address");
				      $sth->execute();
				$sth2 = $dbco->prepare("SELECT reference, id_customer, id_cart FROM ps_orders");
				      $sth2->execute(); 
				$sth3 = $dbco->prepare("SELECT email FROM ps_customer");
				      $sth3->execute(); 
				$sth4 = $dbco->prepare("SELECT product_id, product_name, total_price_tax_excl FROM ps_order_detail");
				      $sth4->execute(); 
				$sth5 = $dbco->prepare("SELECT id_cart, data FROM ps_ganalytics_data");
				      $sth5->execute(); 
                /*Retourne un tableau associatif pour chaque entrée de notre table
                 *avec le nom des colonnes sélectionnées en clefs */
                $resultat = $sth->fetchAll(PDO::FETCH_ASSOC);
				$resultat2 = $sth2->fetchAll(PDO::FETCH_ASSOC);
				$resultat3 = $sth3->fetchAll(PDO::FETCH_ASSOC);
				$resultat4 = $sth4->fetchAll(PDO::FETCH_ASSOC);
				$resultat5 = $sth5->fetchAll(PDO::FETCH_ASSOC);
                
                /*print_r permet un affichage lisible des résultats,
                 *<pre> rend le tout un peu plus lisible*/
                echo '<pre>';
                print_r($resultat);
				print_r($resultat2);
				print_r($resultat3); 
				print_r($resultat4);
				print_r($resultat5);
                echo '</pre>';
            }
                  
            catch(PDOException $e){
                echo "Erreur : " . $e->getMessage();
            }			
			
			
			    // Et pour mettre fin à la connexion
    mysql_close();
	
        ?>

</body>
</html>

Soyez indulgent, c'est mon premier script et je l'ai fait comme ça pour m'assurer que j'ai les bonnes tables et les bonnes colonnes.

Résultat:

Bases de données MySQL
Objet à récupérer: Nom, mail, tel, commentaire, commande détaillée avec nombre et portion
Connexion réussie
Array
(
    [0] => Array
        (
            [firstname] => Stéphane
            [lastname] => Racette
            [phone] => 4504388122
            [id_customer] => 1
        )

    [1] => Array
        (
            [firstname] => supplier
            [lastname] => supplier
            [phone] => 4504388122
            [id_customer] => 0
        )

)
Array
(
    [0] => Array
        (
            [reference] => HLHCGEZGL
            [id_customer] => 1
            [id_cart] => 3
        )

    [1] => Array
        (
            [reference] => XXTEUQYNP
            [id_customer] => 1
            [id_cart] => 4
        )

)
Array
(
    [0] => Array
        (
            [email] => [email protected]
        )

)
Array
(
    [0] => Array
        (
            [product_id] => 5
            [product_name] => Salade de melon aux cevettes (1 Portion : 3 portion)
            [total_price_tax_excl] => 14.250000
        )

    [1] => Array
        (
            [product_id] => 10
            [product_name] => Vinaigrette (1 Portion : 1 Portion)
            [total_price_tax_excl] => 3.000000
        )

    [2] => Array
        (
            [product_id] => 12
            [product_name] => Danoise (1 Portion : 3 portion)
            [total_price_tax_excl] => 39.000000
        )

)
Array
(
    [0] => Array
        (
            [id_cart] => 1
            [data] => {"0":[[[[[[[[[{"0":{"5":{"id":5,"name":"salade-de-melon-aux-crevettes","category":"home","brand":"mg-services-traiteur","variant":"4-portion","type":"typical","position":"0","quantity":6,"list":"cart","url":"","price":19}},"5":{"id":5,"name":"salade-de-melon-aux-cevettes","category":"accueil","brand":"mg-services-traiteur","variant":"2-portion","type":"typical","position":"0","quantity":-1,"list":"cart","url":"https%3A%2F%2Fst-jerome.com%2Fqc%2Faccueil%2F5-salade-de-melon-aux-cevettes.html","price":10.922625},"10":{"id":10,"name":"vinaigrette","category":"accueil","brand":"mg-services-traiteur","variant":"3-portion","type":"typical","position":"0","quantity":1,"list":"cart","url":"","price":8.5},"12":{"id":12,"name":"danoise","category":"accueil","brand":"mg-services-traiteur","variant":"4-portion","type":"typical","position":"0","quantity":1,"list":"cart","url":"","price":11.75}}]]]]]]]]],"5":{"id":5,"name":"salade-de-melon-aux-cevettes","category":"accueil","brand":"","variant":"","type":"virtual","position":"0","quantity":-1,"list":"cart","url":"https%3A%2F%2Fst-jerome.com%2Fqc%2Faccueil%2F5-salade-de-melon-aux-cevettes.html","price":5.74875},"10":{"id":10,"name":"vinaigrette","category":"accueil","brand":"","variant":"","type":"virtual","position":"0","quantity":-1,"list":"cart","url":"https%3A%2F%2Fst-jerome.com%2Fqc%2Faccueil%2F10-vinaigrette.html","price":3.44925},"12":{"id":12,"name":"danoise","category":"accueil","brand":"","variant":"","type":"virtual","position":"0","quantity":-1,"list":"cart","url":"https%3A%2F%2Fst-jerome.com%2Fqc%2Faccueil%2F12-danoise.html","price":2.874375}}
        )

    [1] => Array
        (
            [id_cart] => 2
            [data] => [[[[[[[[[[[{"5":{"id":5,"name":"salade-de-melon-aux-cevettes","category":"accueil","brand":"mg-services-traiteur","variant":"3-portion","type":"typical","position":"0","quantity":0,"list":"cart","url":"","price":14.25},"10":{"id":10,"name":"vinaigrette","category":"accueil","brand":"mg-services-traiteur","variant":"2-portion","type":"typical","position":"0","quantity":2,"list":"cart","url":"","price":5.75},"12":{"id":12,"name":"danoise","category":"accueil","brand":"mg-services-traiteur","variant":"4-portion","type":"typical","position":"0","quantity":3,"list":"cart","url":"","price":11.75}}]]]]]]]]]]]
        )

    [2] => Array
        (
            [id_cart] => 3
            [data] => [[[[[[{"5":{"id":5,"name":"salade-de-melon-aux-cevettes","category":"accueil","brand":"mg-services-traiteur","variant":"3-portion","type":"typical","position":"0","quantity":1,"list":"cart","url":"","price":14.25}}]]]]]]
        )

    [3] => Array
        (
            [id_cart] => 4
            [data] => [[[[[{"10":{"id":10,"name":"vinaigrette","category":"accueil","brand":"mg-services-traiteur","variant":"1-portion","type":"typical","position":"0","quantity":1,"list":"cart","url":"","price":3},"12":{"id":12,"name":"danoise","category":"accueil","brand":"mg-services-traiteur","variant":"3-portion","type":"typical","position":"0","quantity":4,"list":"cart","url":"","price":9.75}}]]]]]
        )

    [4] => Array
        (
            [id_cart] => 5
            [data] => [{"5":{"id":5,"name":"salade-de-melon-aux-cevettes","category":"accueil","brand":"mg-services-traiteur","variant":"4-portion","type":"typical","position":"0","quantity":1,"list":"cart","url":"","price":19}}]
        )

    [5] => Array
        (
            [id_cart] => 6
            [data] => {"12":{"id":12,"name":"danoise","category":"accueil","brand":"mg-services-traiteur","variant":"1-portion","type":"typical","position":"0","quantity":2,"list":"cart","url":"","price":2.5}}
        )

    [6] => Array
        (
            [id_cart] => 7
            [data] => {"12":{"id":12,"name":"danoise","category":"accueil","brand":"mg-services-traiteur","variant":"1-portion","type":"typical","position":"0","quantity":-1,"list":"cart","url":"https%3A%2F%2Fst-jerome.com%2Fqc%2Faccueil%2F12-danoise.html","price":2.874375},"5":{"id":5,"name":"salade-de-melon-aux-cevettes","category":"accueil","brand":"mg-services-traiteur","variant":"1-portion","type":"typical","position":"0","quantity":1,"list":"cart","url":"","price":5}}
        )

    [7] => Array
        (
            [id_cart] => 8
            [data] => {"12":{"id":12,"name":"danoise","category":"accueil","brand":"mg-services-traiteur","variant":"1-portion","type":"typical","position":"0","quantity":0,"list":"cart","url":"","price":2.5}}
        )

    [8] => Array
        (
            [id_cart] => 9
            [data] => {"5":{"id":5,"name":"salade-de-melon-aux-cevettes","category":"accueil","brand":"mg-services-traiteur","variant":"","type":"typical","position":"0","quantity":-1,"list":"cart","url":"https%3A%2F%2Fst-jerome.com%2Fqc%2Faccueil%2F5-salade-de-melon-aux-cevettes.html","price":5.74875}}
        )

)

 

Edited by Samx
Je corrige mes phottes ;-) (see edit history)
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...