[Résolu][SQL] Aide pour construire une requête

[Plume]

Verdict : Ma requête n'était pas bonne. J'la corrige ^^

Code PHP :

<?php 

/* SELECT

     *  warehouses.id AS warehouse_id,

     *  warehouses.capacityMech,

     *  warehouses.capacityWeight,

     *  warehouses.countries_id,

     *  player_has_warehouse.name AS warehouse_name,

     *  (

     *    (

     *      SELECT SUM(armors.weight)

     *      FROM armors, player_has_armor

     *      WHERE player_has_armor.players_id = $this->playerSess['id']

     *        AND player_has_armor.armors_id = armors.id

     *    )

     *    +

     *    (

     *      SELECT SUM(weapons.weight)

     *      FROM weapons, player_has_weapon

     *      WHERE player_has_weapon = $this->playerSess['id']

     *        AND player_has_weapon.weapons_id = weapons.id

     *    )

     *  ) AS weight,

     *  (

     *    SELECT COUNT(player_has_exomech.id)

     *    FROM player_has_exomech

     *    WHERE player_has_exomech.players_id = $this->playerSess['id']

     *  ) AS nb_mechs,

     *  countries.name AS country_name

     * FROM

     *  warehouses

     * JOIN countries

     *  ON warehouses.countries_id = countries.id

     * JOIN player_has_warehouse

     *  ON player_has_warehouse.warehouses_id = warehouses.id

     * WHERE player_has_warehouse.players_id = $this->playerSess['id']

     */

    $selectWarehouse =

        'SELECT ' .

        ' %s, ' .

        ' %s, ' .

        ' %s, ' .

        ' %s, ' .

        ' %s, ' .

        ' ( ' .

        '   ( ' .

        '     SELECT SUM(%s) ' .

        '     FROM %s, %s ' .

        '     WHERE %s = %d ' .

        '       AND %s = %s ' .

        '   ) ' .

        '   + ' .

        '   ( ' .

        '     SELECT SUM(%s) ' .

        '     FROM %s, %s ' .

        '     WHERE %s = %d ' .

        '       AND %s = %s ' .

        '   ) ' .

        ' ) AS %s, ' .

        ' (' .

        '   SELECT COUNT(%s) ' .

        '   FROM %s ' .

        '   WHERE %s = %d ' .

        ' ) AS %s, ' .

        ' %s ' .

        'FROM %s ' .

        'JOIN %s ' .

        ' ON %s = %s ' .

        'JOIN %s' .

        ' ON %s = %s ' .

        'WHERE %s = %d;';

    $selectWarehouseQuery = sprintf(

      $selectWarehouse,

      $this->tableWarehouses.'.id AS warehouse_id',

      $this->tableWarehouses.'.capacityMech',

      $this->tableWarehouses.'.capacityWeight',

      $this->tableWarehouses.'.countries_id',

      $this->tablePlayerHasWarehouse.'.name AS warehouse_name',

      $this->tableArmors.'.weight',

      $this->tableArmors,

      $this->tablePlayerHasArmor,

      $this->tablePlayerHasArmor.'.players_id',

      $this->playerSess['id'],

      $this->tablePlayerHasArmor.'.armors_id',

      $this->tableArmors.'.id',

      $this->tableWeapons.'.weight',

      $this->tableWeapons,

      $this->tablePlayerHasWeapon,

      $this->tablePlayerHasWeapon.'.players_id',

      $this->playerSess['id'],

      $this->tablePlayerHasWeapon.'.weapons_id',

      $this->tableWeapons.'.id',

      'weight',

      '*',

      $this->tablePlayerHasExomech,

      $this->tablePlayerHasExomech.'.players_id',

      $this->playerSess['id'],

      'nb_mechs',

      $this->tableCountries.'.name AS country_name',

      $this->tableWarehouses,

      $this->tableCountries,

      $this->tableWarehouses.'.countries_id',

      $this->tableCountries.'.id',

      $this->tablePlayerHasWarehouse,

      $this->tablePlayerHasWarehouse.'.warehouses_id',

      $this->tableWarehouses.'.id',

      $this->tablePlayerHasWarehouse.'.players_id',

      $this->playerSess['id']

    );