php - devolver datos de respuestas múltiples en una respuesta

mysql pdo

Question

en mi tabla de asignaturas tengo todas las clases de estudiantes por semestre y mes con los puntos de cada mes

[
{
"id": "4", - this is the subject id
"userid": "1",
"name": "bio",
"semester": "3", - semester
"month": "5", - the month
"points": "652" - points of this class
"time": "2017-06-18 22:45:04"
},
{
"id": "3", - this is the subject id
"userid": "1",
"name": "math",
"semester": "3", - semester
"month": "4", - the month
"points": "33" - points of this class
"time": "2017-05-15 22:45:04"
},
{
"id": "2", - this is the subject id
"userid": "1",
"name": "chem",
"semester": "1", - semester
"month": "3", - the month
"points": "22" - points of this class
"time": "2017-04-11 22:45:04"
},
{
"id": "1", - this is the subject id
"userid": "1",
"name": "phy",
"semester": "1", - semester
"month": "2", - the month
"points": "10" - points of this class
"time": "2017-02-10 22:45:04"
}
]

esto es lo que probé

$sql = "SELECT users.id userid,users.name username,subjects.id subjectsid, subjects.name subjectname, subjects.points activepts FROM tbusers AS users INNER JOIN tbsubjects AS subjects ON users.id = subjects.userid WHERE users.id = ''$userid'' ORDER BY subjects.time DESC";

try {
    $db = new db();
    $db = $db->connect();
    $stmt = $db->prepare($sql);

$stmt->execute();

$user = $stmt->fetchAll(PDO::FETCH_OBJ);

$db = null;

if(empty($user)) {
    $response->getBody()->write
    (''
    {
        "error":
        {
            "message":"Invalid"
        }
    }'');
} else {
    $response->getBody()->write(json_encode($user));
}
} catch(PDOException $e) {}

el resultado actual que fetchAll de mi consulta es múltiples respuestas para cada uno debido a fetchAll podría cambiarlo para fetch pero no obtendrá los otros datos

[
{
"userid": "1",
"username": "joe",
"subjectid": "4",
"subjectname": "bio",
"activepts": "652"
},
"userid": "1",
"username": "joe",
"subjectid": "3",
"subjectname": "math",
"activepts": "33"
},
"userid": "1",
"username": "joe",
"subjectid": "2",
"subjectname": "chem",
"activepts": "22"
},
"userid": "1",
"username": "joe",
"subjectid": "1",
"subjectname": "phy",
"activepts": "10"
}
]

mi pregunta es cómo puedo combinarlos en una respuesta y devolver los datos a continuación en la salida esperada (he agregado una pequeña descripción de cada campo para explicarlo)

Rendimiento esperado

[
{
"userid": "1", - from users table
"username": "joe",  - from users table 
"subjectsid": "1", - first subject id for the student in this case the one for phy
"subjectname": "bio", - current subject name 
"activepts": "652", - points of current month
"totalpts": "717", - total points of all subjects for this student
"sem1": "32", - total points of all subjects for this student of semester 1
"sem2": "0", - total points of all subjects for this student of semester 2
"sem3": "685", - total points of all subjects for this student of semester 3
}
]

Answer 1

La cuestión es que quieres buscar las materias, no los estudiantes. Entonces, invertí el DE y la IZQUIERDA. Entonces, cuando desea una lista de temas, comienza con SELECT ... FROM subject. Luego, si necesita otros detalles para cada tema (como nombre de usuario, etc.), aplique LEFT JOIN, lo que significa: UNE todos los detalles necesarios (nombre de usuario, etc.) a cada registro de la tabla LEFT, por ejemplo, de la tabla principal (en su caso es la tabla "sujetos").

¡Buena suerte!

<?php

try {
    $dbAdapter = new DbAdapter();

    $connection = $dbAdapter->connect();

    /*
     * I renamed user id variable (from $userId to $userid1) in order to show you that you can 
     * provide more users if you wish. Then you just have to extend 
     * the WHERE clause in the sql statement and the bindings array.
     */
    $userid1 = 1;

    /*
     * The sql statement - it will be prepared.
     * 
     * ======================================================
     * I''m not sure about the following fields - because you
     * didn''t provide proper selection criteria for them:
     * 
     * 1) "subjectsid":  "1", - first subject id for the student in this case the one for phy
     * 2) "subjectname": "bio", - current subject name 
     * ======================================================
     */
    $sql = ''SELECT 
                subjects.userid,
                users.name AS username,
                (
                    SELECT id 
                    FROM tbsubjects 
                    WHERE userid = subjects.userid 
                    ORDER BY id ASC 
                    LIMIT 1
                ) AS subjectsid,
                (
                    SELECT name 
                    FROM tbsubjects 
                    WHERE 
                        userid = subjects.userid 
                        ORDER BY time DESC
                        LIMIT 1
                ) AS subjectname,
                (
                    SELECT IFNULL(SUM(points), 0) 
                    FROM tbsubjects 
                    WHERE 
                        userid = subjects.userid 
                        AND month = DATE_FORMAT(NOW(), "%c")
                ) AS activepts,
                IFNULL(SUM(subjects.points), 0) AS totalpts,
                (
                    SELECT IFNULL(SUM(points), 0) 
                    FROM tbsubjects 
                    WHERE 
                        userid = subjects.userid 
                        AND semester = 1
                ) AS sem1,
                (
                    SELECT IFNULL(SUM(points), 0) 
                    FROM tbsubjects 
                    WHERE 
                        userid = subjects.userid 
                        AND semester = 2
                ) AS sem2,
                (
                    SELECT IFNULL(SUM(points), 0) 
                    FROM tbsubjects 
                    WHERE 
                        userid = subjects.userid 
                        AND semester = 3
                ) AS sem3 
            FROM 
                tbsubjects AS subjects 
                LEFT JOIN tbusers AS users ON users.id = subjects.userid 
            WHERE subjects.userid = :userid1 
            GROUP BY subjects.userid 
            ORDER BY subjects.time DESC'';

    /*
     * The input parameters list for the prepared sql statement.
     */
    $bindings = array(
        '':userid1'' => $userid1,
    );

    /*
     * Prepare and validate the sql statement.
     * 
     * --------------------------------------------------------------------------------
     * If the database server cannot successfully prepare the statement, PDO::prepare() 
     * returns FALSE or emits PDOException (depending on error handling settings).
     * --------------------------------------------------------------------------------
     */
    $statement = $connection->prepare($sql);

    if (!$statement) {
        throw new UnexpectedValueException(''The sql statement could not be prepared!'');
    }

    /*
     * Bind the input parameters to the prepared statement.
     * 
     * -----------------------------------------------------------------------------------
     * Unlike PDOStatement::bindValue(), when using PDOStatement::bindParam() the variable 
     * is bound as a reference and will only be evaluated at the time that 
     * PDOStatement::execute() is called.
     * -----------------------------------------------------------------------------------
     */
    foreach ($bindings as $key => $value) {
        $bound = $statement->bindValue(
                getInputParameterName($key)
                , $value
                , getInputParameterDataType($value)
        );

        if (!$bound) {
            throw new UnexpectedValueException(''An input parameter can not be bound!'');
        }
    }

    /*
     * Execute the prepared statement.
     * 
     * ------------------------------------------------------------------
     * PDOStatement::execute returns TRUE on success or FALSE on failure.
     * ------------------------------------------------------------------
     */
    $executed = $statement->execute();

    if (!$executed) {
        throw new UnexpectedValueException(''The prepared statement could not be executed!'');
    }

    /*
     * Fetch users list - array of objects.
     */
    $users = $statement->fetchAll(PDO::FETCH_OBJ);

    if ($users === FALSE) {
        throw new UnexpectedValueException(''Fetching users list failed!'');
    }

    /*
     * Close connection.
     */
    $connection = NULL;

    /*
     * Handle results.
     */
    if (empty($users)) {
        $response->getBody()->write(
            ''{
                "error": {
                    "message":"Invalid"
                }
            }''
        );
    } else {
        $response->getBody()->write(json_encode($users));
    }
} catch (PDOException $exc) {
    echo $exc->getMessage();
    // $logger->log($exc);
    exit();
} catch (Exception $exc) {
    echo $exc->getMessage();
    // $logger->log($exc);
    exit();
}

/**
 * Get the name of an input parameter by its key in the bindings array.
 *  
 * @param int|string $key The key of the input parameter in the bindings array.
 * @return int|string The name of the input parameter.
 */
function getInputParameterName($key) {
    return is_int($key) ? ($key + 1) : ('':'' . ltrim($key, '':''));
}

/**
 * Get the PDO::PARAM_* constant, e.g the data type of an input parameter, by its value.
 *  
 * @param mixed $value Value of the input parameter.
 * @return int The PDO::PARAM_* constant.
 */
function getInputParameterDataType($value) {
    $dataType = PDO::PARAM_STR;
    if (is_int($value)) {
        $dataType = PDO::PARAM_INT;
    } elseif (is_bool($value)) {
        $dataType = PDO::PARAM_BOOL;
    }
    return $dataType;
}

EDITAR:

Para mis proyectos desarrollé una clase DbAdapter. Los nombres de los métodos se explican por sí mismos. Entonces, no más código spagetti dentro de cada página web :-) Pero solo:

• la declaración sql,
• la matriz de enlaces,
• la llamada al método correspondiente en el adaptador db y
• la línea de desconexión de db

La solución a su pregunta se vería así:

<?php

//***********************************************************************************
// Put this in a php file (like db.php) to include whereever you need db data access.
//***********************************************************************************
//
// Db configs.
define(''DB_HOST'', ''...'');
define(''DB_PORT'', 3306);
define(''DB_DBNAME'', ''...'');
define(''DB_CHARSET'', ''utf8'');
define(''DB_USERNAME'', ''...'');
define(''DB_PASSWORD'', ''...'');
define(''DB_DRIVER_NAME'', ''mysql'');

// Create db adapter.
$dbAdapter = new DbAdapter(DB_HOST, DB_DBNAME, DB_USERNAME, DB_PASSWORD, DB_PORT, DB_CHARSET);

//***********************************************************************************

$userid1 = 1;

// Sql statement.
$sql = ''SELECT ... FROM ... WHERE subjects.userid = :userid1 GROUP BY ... ORDER BY ...'';

// Input parameters.
$bindings = array(
    '':userid1'' => $userid1,
);

// Fetch users.
$users = $dbAdapter->fetchAll($sql, $bindings);

// Disconnect from db.
$dbAdapter->disconnect();

/*
 * Handle results.
 */
if (empty($users)) {
    //...
} else {
    //...
}

Los métodos de adaptador para llamar son los public :

• connect : se conecta a la base de datos, por ejemplo, crea una instancia de PDO, por ejemplo, crea una conexión db.
• desconectar : se desconecta de la base de datos.
• fetchAll : recupera más registros a la vez. Devuelve una matriz de matrices. Por lo tanto, cada elemento es una matriz correspondiente a un registro db.
• fetchOne : Obtiene solo un registro.
• fetchColumn : obtiene un valor de columna.
• actualización : realiza una consulta de ACTUALIZACIÓN. Devuelve el número de filas afectadas.
• eliminar : realiza una consulta DELETE. Devuelve el número de filas afectadas.
• insert : realiza una consulta INSERT. Devuelve la última identificación de inserción.
• getLastInsertId : devuelve la última identificación de inserción después de realizar una operación INSERT.

Eso es todo :-)

<?php

/*
 * Database adapter.
 */

/**
 * Database adapter.
 */
class DbAdapter {

    /**
     * Connection configs.
     * 
     * @var array
     */
    private $connectionConfigs;

    /**
     * Database connection.
     * 
     * @var PDO
     */
    private $connection;

    /**
     * PDO statement.
     * 
     * @var PDOStatement
     */
    private $statement;

    /**
     * 
     * @param string $host [optional] Host.
     * @param string $dbname [optional] Database name.
     * @param string $username [optional] User name.
     * @param string $password [optional] Password.
     * @param string $port [optional] Port.
     * @param string $charset [optional] Character set.
     * @param string $driverName [optional] Driver name.
     * @param array $driverOptions [optional] Driver options.
     * @return string DSN string.
     */
    public function __construct($host = '''', $dbname = ''''
    , $username = '''', $password = '''', $port = 3306, $charset = ''utf8'', $driverName = ''mysql''
    , $driverOptions = array(
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_EMULATE_PREPARES => FALSE,
        PDO::ATTR_PERSISTENT => TRUE,
    )) {
        $this->setConnectionConfigs(array(
            ''host'' => $host,
            ''dbname'' => $dbname,
            ''username'' => $username,
            ''password'' => $password,
            ''port'' => $port,
            ''charset'' => $charset,
            ''driverName'' => $driverName,
            ''driverOptions'' => $driverOptions,
        ));
    }

    /**
     * Connect to db, e.g. create a PDO instance.
     * 
     * @return $this
     * @throws PDOException
     */
    public function connect() {
        if (!isset($this->connection) || !$this->connection) {
            try {
                $this->connection = new PDO(
                        $this->createDsn(
                                $this->connectionConfigs[''host'']
                                , $this->connectionConfigs[''dbname'']
                                , $this->connectionConfigs[''port'']
                                , $this->connectionConfigs[''charset'']
                                , $this->connectionConfigs[''driverName'']
                        )
                        , $this->connectionConfigs[''username'']
                        , $this->connectionConfigs[''password'']
                        , $this->connectionConfigs[''driverOptions'']
                );
            } catch (PDOException $pdoException) {
                echo $pdoException->getMessage();
                exit();
            }
        }

        return $this;
    }

    /**
     * Disconnect from db.
     * 
     * @return $this
     */
    public function disconnect() {
        $this->connection = NULL;

        return $this;
    }

    /**
     * Create a DSN string.
     * 
     * @param string $host Host.
     * @param string $dbname Database name.
     * @param string $port Port.
     * @param string $charset Character set.
     * @param string $driverName Driver name.
     * @return string DSN string.
     */
    private function createDsn($host, $dbname, $port, $charset, $driverName) {
        switch ($driverName) {
            default: // mysql
                $dsn = sprintf(''%s:host=%s;port=%s;dbname=%s;charset=%s''
                        , $driverName
                        , $host
                        , $port
                        , $dbname
                        , $charset
                );
                break;
        }

        return $dsn;
    }

    /**
     * Fetch data by executing a SELECT sql statement.
     * 
     * @param string $sql Sql statement.
     * @param array $bindings [optional] Input parameters.
     * @param integer $fetchMode [optional] Fetch mode for a PDO statement.
     *  Must be one of the PDO::FETCH_* constants.
     * @param mixed $fetchArgument [optional] Fetch argument for a PDO statement.
     * @param array $fetchConstructorArguments [optional] Constructor arguments for a PDO statement 
     *  when fetch mode is PDO::FETCH_CLASS.
     * @return array An array containing the rows in the result set, or FALSE on failure.
     * @throws UnexpectedValueException
     */
    public function fetchAll($sql, array $bindings = array(), $fetchMode = PDO::FETCH_ASSOC, $fetchArgument = NULL, array $fetchConstructorArguments = array()) {
        $this
                ->prepareStatement($sql)
                ->bindInputParameters($bindings)
                ->executePreparedStatement()
        ;

        try {
            if (isset($fetchArgument)) {
                $data = $this->getStatement()->fetchAll($fetchMode, $fetchArgument, $fetchConstructorArguments);
            } else {
                $data = $this->getStatement()->fetchAll($fetchMode);
            }

            if ($data === FALSE) {
                throw new UnexpectedValueException(''Fetching data failed!'');
            }

            return $data;
        } catch (Exception $exception) {
            echo $exception->getMessage();
            exit();
        }
    }

    /**
     * Fetch the next row from the result set by executing a SELECT sql statement.
     * The fetch mode property determines how PDO returns the row.
     * 
     * @param string $sql Sql statement.
     * @param array $bindings [optional] Input parameters.
     * @param integer $fetchMode [optional] Fetch mode for a PDO statement.
     *  Must be one of the PDO::FETCH_* constants.
     * @param integer $fetchCursorOrientation [optional] For a PDOStatement object representing 
     *  a scrollable cursor, this value determines which row will be returned to the caller.
     * @param integer $fetchCursorOffset [optional] The absolute number of the row in the result 
     *  set, or the row relative to the cursor position before PDOStatement::fetch() was called.
     * @return array An array containing the next row in the result set, or FALSE on failure.
     * @throws Exception
     */
    public function fetchOne($sql, array $bindings = array(), $fetchMode = PDO::FETCH_ASSOC, $fetchCursorOrientation = PDO::FETCH_ORI_NEXT, $fetchCursorOffset = 0) {
        $this
                ->prepareStatement($sql)
                ->bindInputParameters($bindings)
                ->executePreparedStatement()
        ;

        try {
            /*
             * =========================================================
             * NB:
             * =========================================================
             * PDOStatement::fetch returns FALSE not only on failure,
             * but ALSO when no record is found! This is a BUG. That''s
             * why I made the try-catch block: maybe on failure will
             * throw an exception.
             * 
             * Instead, PDOStatement::fetchAll returns FALSE on failure,
             * but an empty array if no record is found. This is the
             * correct behaviour.
             * =========================================================
             */
            $data = $this->getStatement()->fetch($fetchMode, $fetchCursorOrientation, $fetchCursorOffset);

            return $data;
        } catch (Exception $exception) {
            echo $exception->getMessage();
            exit();
        }
    }

    /**
     * Returns a single column from the next row of a result set 
     * or FALSE if there are no more rows.
     * 
     * =================================================================
     * Note:
     * -----
     * PDOStatement::fetchColumn() should not be used to retrieve 
     * boolean columns, as it is impossible to distinguish a value 
     * of FALSE from there being no more rows to retrieve.
     * Use PDOStatement::fetch() instead.
     * 
     * Warning:
     * --------
     * There is no way to return another column from the same row if you
     * use PDOStatement::fetchColumn() to retrieve data.
     * =================================================================
     * 
     * @param string $sql Sql statement.
     * @param array $bindings [optional] Input parameters.
     * @param integer $columnNumber [optional] 0-indexed number of the 
     *  column you wish to retrieve from the row. If no value is supplied, 
     *  PDOStatement::fetchColumn() fetches the first column.
     * @return mixed A single column from the next row of a result set 
     *  or FALSE if there are no more rows.
     * @throws Exception
     */
    public function fetchColumn($sql, array $bindings = array(), $columnNumber = 0) {
        $this
                ->prepareStatement($sql)
                ->bindInputParameters($bindings)
                ->executePreparedStatement()
        ;

        try {
            return $this->getStatement()->fetchColumn($columnNumber);
        } catch (Exception $exception) {
            echo $exception->getMessage();
            exit();
        }
    }

    /**
     * Store data by executing an INSERT sql statement.
     * 
     * @param string $sql Sql statement.
     * @param array $bindings [optional] Input parameters.
     * @return int Last insert id.
     */
    public function insert($sql, array $bindings = array()) {
        $this
                ->prepareStatement($sql)
                ->bindInputParameters($bindings)
                ->executePreparedStatement()
        ;

        return $this->getLastInsertId();
    }

    /**
     * Update data by executing an UPDATE sql statement.
     * 
     * @param string $sql Sql statement.
     * @param array $bindings [optional] Input parameters.
     * @return int Number of affected rows.
     */
    public function update($sql, array $bindings = array()) {
        $this
                ->prepareStatement($sql)
                ->bindInputParameters($bindings)
                ->executePreparedStatement()
        ;

        return $this->getStatement()->rowCount();
    }

    /**
     * Delete data by executing a DELETE sql statement.
     * 
     * @param string $sql Sql statement.
     * @param array $bindings [optional] Input parameters.
     * @return int Number of affected rows.
     */
    public function delete($sql, array $bindings = array()) {
        $this
                ->prepareStatement($sql)
                ->bindInputParameters($bindings)
                ->executePreparedStatement()
        ;

        return $this->getStatement()->rowCount();
    }

    /**
     * Prepare and validate an sql statement.
     * 
     * ----------------------------------------------------
     * If the database server cannot successfully prepare 
     * the statement, PDO::prepare() returns FALSE or emits 
     * PDOException (depending on error handling settings).
     * ----------------------------------------------------
     * 
     * @param string $sql Sql statement.
     * @return $this
     * @throws PDOException
     * @throws UnexpectedValueException
     */
    private function prepareStatement($sql) {
        $this->connect();
        try {
            $statement = $this->getConnection()->prepare($sql);

            if (!$statement) {
                throw new UnexpectedValueException(''The sql statement can not be prepared!'');
            }

            $this->setStatement($statement);
        } catch (PDOException $pdoException) {
            echo $pdoException->getMessage();
            exit();
        } catch (Exception $exception) {
            echo $exception->getMessage();
            exit();
        }

        return $this;
    }

    /**
     * Bind the input parameters to a prepared PDO statement.
     * 
     * @param array $bindings Input parameters.
     * @return $this
     * @throws UnexpectedValueException
     */
    private function bindInputParameters($bindings) {
        foreach ($bindings as $key => $value) {
            try {
                $bound = $this->getStatement()->bindValue(
                        $this->getInputParameterName($key)
                        , $value
                        , $this->getInputParameterDataType($value)
                );

                if (!$bound) {
                    throw new UnexpectedValueException(''A value can not be bound!'');
                }
            } catch (Exception $exception) {
                echo $exception->getMessage();
                exit();
            }
        }

        return $this;
    }

    /**
     * Get the name of an input parameter by its key in the bindings array.
     *  
     * @param int|string $key The key of the input parameter in the bindings array.
     * @return int|string The name of the input parameter.
     */
    private function getInputParameterName($key) {
        return is_int($key) ? ($key + 1) : ('':'' . ltrim($key, '':''));
    }

    /**
     * Get the PDO::PARAM_* constant, e.g the data type of an input parameter, by its value.
     *  
     * @param mixed $value Value of the input parameter.
     * @return int The PDO::PARAM_* constant.
     */
    private function getInputParameterDataType($value) {
        $dataType = PDO::PARAM_STR;
        if (is_int($value)) {
            $dataType = PDO::PARAM_INT;
        } elseif (is_bool($value)) {
            $dataType = PDO::PARAM_BOOL;
        }
        return $dataType;
    }

    /**
     * Execute a prepared PDO statement.
     * 
     * @return $this
     * @throws UnexpectedValueException
     */
    private function executePreparedStatement() {
        try {
            if (!$this->getStatement()->execute()) {
                throw new UnexpectedValueException(''The statement can not be executed!'');
            }
        } catch (Exception $exception) {
            echo $exception->getMessage();
            exit();
        }

        return $this;
    }

    /**
     * Get the ID of the last inserted row or of the sequence value.
     * 
     * @param string $sequenceObjectName [optional] Name of the sequence object 
     *  from which the ID should be returned.
     * @return string The ID of the last row, or the last value retrieved from the specified 
     *  sequence object, or an error IM001 SQLSTATE If the PDO driver does not support this.
     * @throws PDOException
     */
    public function getLastInsertId($sequenceObjectName = NULL) {
        $this->connect();
        try {
            return $this->getConnection()->lastInsertId($sequenceObjectName);
        } catch (PDOException $pdoException) {
            echo $pdoException->getMessage();
            exit();
        }
    }

    /**
     * Get connection configs.
     * 
     * @return array
     */
    public function getConnectionConfigs() {
        return $this->connectionConfigs;
    }

    /**
     * Set connection configs.
     * 
     * @param array $connectionConfigs Connection configs.
     * @return $this
     */
    public function setConnectionConfigs($connectionConfigs) {
        $this->connectionConfigs = $connectionConfigs;
        return $this;
    }

    /**
     * Get database connection.
     * 
     * @return PDO Database connection.
     */
    public function getConnection() {
        return $this->connection;
    }

    /**
     * Set database connection.
     * 
     * @param PDO $connection Database connection.
     * @return $this
     */
    public function setConnection(PDO $connection) {
        $this->connection = $connection;
        return $this;
    }

    /**
     * Get PDO statement.
     * 
     * @return PDOStatement
     */
    public function getStatement() {
        return $this->statement;
    }

    /**
     * Set PDO statement.
     * 
     * @param PDOStatement $statement PDO statement.
     * @return $this
     */
    public function setStatement(PDOStatement $statement) {
        $this->statement = $statement;
        return $this;
    }

}