example - select php mysql ejemplo

¿Cómo hago eco de las filas que tienen una variable específica en ella desde la base de datos?

Question

Entonces, primero, mi tabla de base de datos está configurada así:

id | affsub | nombre_oferta | fecha | tiempo | pagar

1 | stringhere | oferta | 2017-09-12 | 06:47:00 | 1

y quiero hacer eco de todas las filas que incluyen el affsub stringhere en una tabla html. He intentado esto:

<?php
   $id = $get_info_id;
   $mysqli = new /mysqli(''localhost'', ''user'', ''pass'', ''db'');
   $aff = $mysqli->query("SELECT affsub FROM users WHERE id = $id")->fetch_object()->affsub;
   $affsub = $aff;
   $userinfo= $mysqli->query("SELECT offer_name, time, payout FROM conversions WHERE affsub = ". $affsub . "");

  if ($userinfo->num_rows > 0) {
     while($row = $userinfo->fetch_assoc()) {
        echo ''<tr>
           <td><b><color=black>'' .$row[''offer_name''].'' </b></td>
           <td><color=black>'' .$row[''time''].''</td>
           <td>$<color=black>'' .$row[''payout''].''</td>
        </tr>'';
     }
  }
  else {
     echo "<b><center>No Conversions Have Happened.</center></b>";
  }
?>

y sé que está recibiendo el affsub porque si hago echo $ affsub mi affsub se hace eco, pero no se muestra nada en la mesa y no estoy seguro de lo que está pasando.

Answer 1

Simplemente debe unir las dos consultas:

$userinfo = $mysql->query("SELECT c.offer_name, c.time, c.payout
    FROM conversations AS c
    JOIN users AS u ON u.affsub = c.affsub
    WHERE u.id = $id");

Answer 2

Creo que el problema es que te faltan las comillas para el término de búsqueda en la cláusula WHERE. Debería verse así: affsub = ''$ affsub''.

Prueba esto.

$userinfo= $mysqli->query("SELECT offer_name, time, payout FROM conversions WHERE affsub = ''$affsub'' ");

Answer 3

Tenga en cuenta que los créditos para la declaración sql que utilicé pertenecen a @Barmar, porque ayer tuvo primero la idea de las consultas unidas.

Ahora, abajo hay dos métodos para usar. Tenga en cuenta que no usé ningún OOP ni funciones. La razón es que quería que tengas una vista compacta de todos los pasos.

Cómo usar las declaraciones preparadas de mysqli y el manejo de excepciones

1. Use get_result () + fetch_object () o fetch_array () o fetch_all ():

Este método (recomendado) funciona solo si el controlador mysqlnd ( controlador nativo de MySQL ) está instalado / activado. Creo que el controlador está activado por defecto en PHP> = 5.3. Implementa el código y déjalo funcionar. Deberia de funcionar. Si funciona, entonces es perfecto. Si no, intente activar el controlador mysqlnd, por ejemplo, extension=php_mysqli_mysqlnd.dll en php.ini. De lo contrario, debe usar el segundo método (2).

<?php
/*
 * Define constants for db connection.
 */
define(''MYSQL_HOST'', ''...'');
define(''MYSQL_PORT'', ''...'');
define(''MYSQL_DATABASE'', ''...'');
define(''MYSQL_CHARSET'', ''utf8'');
define(''MYSQL_USERNAME'', ''...'');
define(''MYSQL_PASSWORD'', ''...'');

/*
 * Activate PHP error reporting.
 * Use ONLY on development code, NEVER on production code!!!
 * ALWAYS resolve WARNINGS and ERRORS.
 * I recommend to always resolve NOTICES too.
 */
error_reporting(E_ALL);
ini_set(''display_errors'', 1);

/*
 * Enable internal report functions. This enables the exception handling, 
 * e.g. mysqli will not throw PHP warnings anymore, but mysqli exceptions 
 * (mysqli_sql_exception). They are catched in the try-catch block.
 * 
 * MYSQLI_REPORT_ERROR: Report errors from mysqli function calls.
 * MYSQLI_REPORT_STRICT: Throw a mysqli_sql_exception for errors instead of warnings. 
 * 
 * See:
 *      http://php.net/manual/en/class.mysqli-driver.php
 *      http://php.net/manual/en/mysqli-driver.report-mode.php
 *      http://php.net/manual/en/mysqli.constants.php
 */
$mysqliDriver = new mysqli_driver();
$mysqliDriver->report_mode = (MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

try {
    // To delete (just for test here).
    $get_info_id = 1;

    $userId = $get_info_id;
    $fetchedData = array();

    /*
     * Create the db connection.
     * 
     * Throws mysqli_sql_exception.
     * See: http://php.net/manual/en/mysqli.construct.php
     */
    $connection = new mysqli(
            MYSQL_HOST
            , MYSQL_USERNAME
            , MYSQL_PASSWORD
            , MYSQL_DATABASE
            , MYSQL_PORT
    );
    if ($connection->connect_error) {
        throw new Exception(''Connect error: '' . $connection->connect_errno . '' - '' . $connection->connect_error);
    }

    /*
     * The SQL statement to be prepared. Notice the so-called markers, 
     * e.g. the "?" signs. They will be replaced later with the 
     * corresponding values when using mysqli_stmt::bind_param.
     * 
     * See: http://php.net/manual/en/mysqli.prepare.php
     */
    $sql = ''SELECT 
                cnv.offer_name, 
                cnv.time, 
                cnv.payout 
            FROM conversions AS cnv
            LEFT JOIN users AS usr ON usr.affsub = cnv.affsub 
            WHERE usr.id = ?'';

    /*
     * Prepare the SQL statement for execution.
     * 
     * Throws mysqli_sql_exception.
     * See: http://php.net/manual/en/mysqli.prepare.php
     */
    $statement = $connection->prepare($sql);
    if (!$statement) {
        throw new Exception(''Prepare error: '' . $connection->errno . '' - '' . $connection->error);
    }

    /*
     * Bind variables for the parameter markers (?) in the 
     * SQL statement that was passed to mysqli::prepare. The first 
     * argument of mysqli_stmt::bind_param is a string that contains one 
     * or more characters which specify the types for the corresponding bind variables.
     * 
     * See: http://php.net/manual/en/mysqli-stmt.bind-param.php
     */
    $bound = $statement->bind_param(''i'', $userId);
    if (!$bound) {
        throw new Exception(''Bind error: The variables could not be bound to the prepared statement'');
    }

    /*
     * Execute the prepared SQL statement.
     * When executed any parameter markers which exist will 
     * automatically be replaced with the appropriate data.
     * 
     * See: http://php.net/manual/en/mysqli-stmt.execute.php
     */
    $executed = $statement->execute();
    if (!$executed) {
        throw new Exception(''Execute error: The prepared statement could not be executed!'');
    }

    /*
     * Get the result set from the prepared statement. In case of 
     * failure use errno, error and/or error_list to see the error.
     * 
     * NOTA BENE:
     * Available only with mysqlnd ("MySQL Native Driver")! If this 
     * is not installed, then uncomment "extension=php_mysqli_mysqlnd.dll" in 
     * PHP config file (php.ini) and restart web server (I assume Apache) and 
     * mysql service. Or use the following functions instead:
     * mysqli_stmt::store_result + mysqli_stmt::bind_result + mysqli_stmt::fetch.
     * 
     * See:
     *      http://php.net/manual/en/mysqli-stmt.get-result.php
     *      https://.com/questions/8321096/call-to-undefined-method-mysqli-stmtget-result
     */
    $result = $statement->get_result();
    if (!$result) {
        throw new Exception(''Get result error: '' . $connection->errno . '' - '' . $connection->error);
    }

    /*
     * Get the number of rows in the result.
     * 
     * See: http://php.net/manual/en/mysqli-result.num-rows.php
     */
    $numberOfRows = $result->num_rows;

    /*
     * Fetch data and save it into $fetchedData array.
     * 
     * See: http://php.net/manual/en/mysqli-result.fetch-array.php
     */
    if ($numberOfRows > 0) {
        /*
         * Use mysqli_result::fetch_object to fetch a row - as object - 
         * at a time. E.g. use it in a loop construct like ''while''.
         */
        while ($row = $result->fetch_object()) {
            $fetchedData[] = $row;
        }
    }

    /*
     * Free the memory associated with the result. You should 
     * always free your result when it is not needed anymore.
     * 
     * See: http://php.net/manual/en/mysqli-result.free.php
     */
    $result->close();

    /*
     * Close the prepared statement. It also deallocates the statement handle.
     * If the statement has pending or unread results, it cancels them 
     * so that the next query can be executed.
     * 
     * See: http://php.net/manual/en/mysqli-stmt.close.php
     */
    $statementClosed = $statement->close();
    if (!$statementClosed) {
        throw new Exception(''The prepared statement could not be closed!'');
    }

    // Close db connection.
    $connectionClosed = $connection->close();
    if (!$connectionClosed) {
        throw new Exception(''The db connection could not be closed!'');
    }
} catch (mysqli_sql_exception $e) {
    echo ''Error: '' . $e->getCode() . '' - '' . $e->getMessage();
    exit();
} catch (Exception $e) {
    echo $e->getMessage();
    exit();
}

/*
 * Disable internal report functions.
 * 
 * MYSQLI_REPORT_OFF: Turns reporting off.
 * 
 * See:
 *      http://php.net/manual/en/class.mysqli-driver.php
 *      http://php.net/manual/en/mysqli-driver.report-mode.php
 *      http://php.net/manual/en/mysqli.constants.php
 */
$mysqliDriver->report_mode = MYSQLI_REPORT_OFF;
?>

<!DOCTYPE html>
<html>
    <head>
        <meta charset="UTF-8">
        <title>Example code: Mysqli prepared statements & exception handling</title>
    </head>
    <style>
        table {
            font-family: "Verdana", Arial, sans-serif;
            font-size: 14px;
            border-collapse: collapse;
        }

        table, th, td {
            border: 1px solid #ccc;
        }

        th, td {
            padding: 7px;
        }

        thead {
            color: #fff;
            font-weight: normal;
            background-color: coral;
        }

        tfoot {
            background-color: wheat;
        }

        tfoot td {
            text-align: right;
        }
    </style>
    <body>

        <?php
        $countOfFetchedData = count($fetchedData);

        if ($countOfFetchedData > 0) {
            ?>
            <table>
                <thead>
                    <tr>
                        <th>Crt. No.</th>
                        <th>OFFER NAME</th>
                        <th>TIME</th>
                        <th>PAYOUT</th>
                    </tr>
                </thead>
                <tbody>
                    <?php
                    foreach ($fetchedData as $key => $item) {
                        $offerName = $item->offer_name;
                        $time = $item->time;
                        $payout = $item->payout;
                        ?>
                        <tr>
                            <td><?php echo $key + 1; ?></td>
                            <td><?php echo $offerName; ?></td>
                            <td><?php echo $time; ?></td>
                            <td><?php echo $payout; ?></td>
                        </tr>
                        <?php
                    }
                    ?>
                </tbody>
                <tfoot>
                    <tr>
                        <td colspan="7">
                            - <?php echo $countOfFetchedData; ?> records found -
                        </td>
                    </tr>
                </tfoot>
            </table>
            <?php
        } else {
            ?>
            <span>
                No records found.
            </span>
            <?php
        }
        ?>

    </body>
</html>

NB: Cómo usar fetch_array () en lugar de fetch_object ():

//...
if ($numberOfRows > 0) {
    /*
     * Use mysqli_result::fetch_array to fetch a row at a time.
     * e.g. use it in a loop construct like ''while''.
     */
    while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
        $fetchedData[] = $row;
    }
}
//...

Realice los cambios correspondientes en el código html también.

NB: Cómo usar fetch_all () en lugar de fetch_object ():

//...
if ($numberOfRows > 0) {
    /*
     * Use mysqli_result::fetch_all to fetch all rows at once.
     */
    $fetchedData = $result->fetch_all(MYSQLI_ASSOC);
}
//...

Realice los cambios correspondientes en el código html también.

2. Utilice store_result () + bind_result () + fetch ():

Funciona sin el controlador mysqlnd ( controlador nativo de MySQL ).

<?php
/*
 * Define constants for db connection.
 */
define(''MYSQL_HOST'', ''...'');
define(''MYSQL_PORT'', ''...'');
define(''MYSQL_DATABASE'', ''...'');
define(''MYSQL_CHARSET'', ''utf8'');
define(''MYSQL_USERNAME'', ''...'');
define(''MYSQL_PASSWORD'', ''...'');

/*
 * Activate PHP error reporting.
 * Use ONLY on development code, NEVER on production code!!!
 * ALWAYS resolve WARNINGS and ERRORS.
 * I recommend to always resolve NOTICES too.
 */
error_reporting(E_ALL);
ini_set(''display_errors'', 1);

/*
 * Enable internal report functions. This enables the exception handling, 
 * e.g. mysqli will not throw PHP warnings anymore, but mysqli exceptions 
 * (mysqli_sql_exception). They are catched in the try-catch block.
 * 
 * MYSQLI_REPORT_ERROR: Report errors from mysqli function calls.
 * MYSQLI_REPORT_STRICT: Throw a mysqli_sql_exception for errors instead of warnings. 
 * 
 * See:
 *      http://php.net/manual/en/class.mysqli-driver.php
 *      http://php.net/manual/en/mysqli-driver.report-mode.php
 *      http://php.net/manual/en/mysqli.constants.php
 */
$mysqliDriver = new mysqli_driver();
$mysqliDriver->report_mode = (MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

try {
    // To delete (just for test here).
    $get_info_id = 1;

    $userId = $get_info_id;
    $fetchedData = array();

    /*
     * Create the db connection.
     * 
     * Throws mysqli_sql_exception.
     * See: http://php.net/manual/en/mysqli.construct.php
     */
    $connection = new mysqli(
            MYSQL_HOST
            , MYSQL_USERNAME
            , MYSQL_PASSWORD
            , MYSQL_DATABASE
            , MYSQL_PORT
    );
    if ($connection->connect_error) {
        throw new Exception(''Connect error: '' . $connection->connect_errno . '' - '' . $connection->connect_error);
    }

    /*
     * The SQL statement to be prepared. Notice the so-called markers, 
     * e.g. the "?" signs. They will be replaced later with the 
     * corresponding values when using mysqli_stmt::bind_param.
     * 
     * See: http://php.net/manual/en/mysqli.prepare.php
     */
    $sql = ''SELECT 
                cnv.offer_name, 
                cnv.time, 
                cnv.payout 
            FROM conversions AS cnv
            LEFT JOIN users AS usr ON usr.affsub = cnv.affsub 
            WHERE usr.id = ?'';

    /*
     * Prepare the SQL statement for execution.
     * 
     * Throws mysqli_sql_exception.
     * See: http://php.net/manual/en/mysqli.prepare.php
     */
    $statement = $connection->prepare($sql);
    if (!$statement) {
        throw new Exception(''Prepare error: '' . $connection->errno . '' - '' . $connection->error);
    }

    /*
     * Bind variables for the parameter markers (?) in the 
     * SQL statement that was passed to mysqli::prepare. The first 
     * argument of mysqli_stmt::bind_param is a string that contains one 
     * or more characters which specify the types for the corresponding bind variables.
     * 
     * See: http://php.net/manual/en/mysqli-stmt.bind-param.php
     */
    $bound = $statement->bind_param(''i'', $userId);
    if (!$bound) {
        throw new Exception(''Bind error: The variables could not be bound to the prepared statement'');
    }

    /*
     * Execute the prepared SQL statement.
     * When executed any parameter markers which exist will 
     * automatically be replaced with the appropriate data.
     * 
     * See: http://php.net/manual/en/mysqli-stmt.execute.php
     */
    $executed = $statement->execute();
    if (!$executed) {
        throw new Exception(''Execute error: The prepared statement could not be executed!'');
    }

    /*
     * Transfer the result set resulted from executing the prepared statement.
     * E.g. store, e.g. buffer the result set into the (same) prepared statement.
     * 
     * See:
     *      http://php.net/manual/en/mysqli-stmt.store-result.php
     *      https://.com/questions/8321096/call-to-undefined-method-mysqli-stmtget-result
     */
    $resultStored = $statement->store_result();
    if (!$resultStored) {
        throw new Exception(''Store result error: The result set  could not be transfered'');
    }

    /*
     * Get the number of rows from the prepared statement.
     * 
     * See: http://php.net/manual/en/mysqli-stmt.num-rows.php
     */
    $numberOfRows = $statement->num_rows;

    /*
     * Fetch data and save it into $fetchedData array.
     * 
     * See: http://php.net/manual/en/mysqli-result.fetch-array.php
     */
    if ($numberOfRows > 0) {
        /*
         * Bind the result set columns to corresponding variables.
         * E.g. these variables will hold the column values after fetching.
         * 
         * See: http://php.net/manual/en/mysqli-stmt.bind-result.php
         */
        $varsBound = $statement->bind_result(
                $resOfferName
                , $resTime
                , $resPayout
        );
        if (!$varsBound) {
            throw new Exception(''Bind result error: The result set columns could not be bound to variables'');
        }

        /*
         * Fetch results from the result set (of the prepared statement) into the bound variables.
         * 
         * See: http://php.net/manual/en/mysqli-stmt.fetch.php
         */
        while ($row = $statement->fetch()) {
            $fetchedObject = new stdClass();

            $fetchedObject->offer_name = $resOfferName;
            $fetchedObject->time = $resTime;
            $fetchedObject->payout = $resPayout;

            $fetchedData[] = $fetchedObject;
        }
    }

    /*
     * Frees the result memory associated with the statement,
     * which was allocated by mysqli_stmt::store_result.
     * 
     * See: http://php.net/manual/en/mysqli-stmt.store-result.php
     */
    $statement->free_result();

    /*
     * Close the prepared statement. It also deallocates the statement handle.
     * If the statement has pending or unread results, it cancels them 
     * so that the next query can be executed.
     * 
     * See: http://php.net/manual/en/mysqli-stmt.close.php
     */
    $statementClosed = $statement->close();
    if (!$statementClosed) {
        throw new Exception(''The prepared statement could not be closed!'');
    }

    // Close db connection.
    $connectionClosed = $connection->close();
    if (!$connectionClosed) {
        throw new Exception(''The db connection could not be closed!'');
    }
} catch (mysqli_sql_exception $e) {
    echo ''Error: '' . $e->getCode() . '' - '' . $e->getMessage();
    exit();
} catch (Exception $e) {
    echo $e->getMessage();
    exit();
}

/*
 * Disable internal report functions.
 * 
 * MYSQLI_REPORT_OFF: Turns reporting off.
 * 
 * See:
 *      http://php.net/manual/en/class.mysqli-driver.php
 *      http://php.net/manual/en/mysqli-driver.report-mode.php
 *      http://php.net/manual/en/mysqli.constants.php
 */
$mysqliDriver->report_mode = MYSQLI_REPORT_OFF;
?>

<!DOCTYPE html>
<html>
    <head>
        <meta charset="UTF-8">
        <title>Example code: Mysqli prepared statements & exception handling</title>
    </head>
    <style>
        table {
            font-family: "Verdana", Arial, sans-serif;
            font-size: 14px;
            border-collapse: collapse;
        }

        table, th, td {
            border: 1px solid #ccc;
        }

        th, td {
            padding: 7px;
        }

        thead {
            color: #fff;
            font-weight: normal;
            background-color: coral;
        }

        tfoot {
            background-color: wheat;
        }

        tfoot td {
            text-align: right;
        }
    </style>
    <body>

        <?php
        $countOfFetchedData = count($fetchedData);

        if ($countOfFetchedData > 0) {
            ?>
            <table>
                <thead>
                    <tr>
                        <th>Crt. No.</th>
                        <th>OFFER NAME</th>
                        <th>TIME</th>
                        <th>PAYOUT</th>
                    </tr>
                </thead>
                <tbody>
                    <?php
                    foreach ($fetchedData as $key => $item) {
                        $offerName = $item->offer_name;
                        $time = $item->time;
                        $payout = $item->payout;
                        ?>
                        <tr>
                            <td><?php echo $key + 1; ?></td>
                            <td><?php echo $offerName; ?></td>
                            <td><?php echo $time; ?></td>
                            <td><?php echo $payout; ?></td>
                        </tr>
                        <?php
                    }
                    ?>
                </tbody>
                <tfoot>
                    <tr>
                        <td colspan="7">
                            - <?php echo $countOfFetchedData; ?> records found -
                        </td>
                    </tr>
                </tfoot>
            </table>
            <?php
        } else {
            ?>
            <span>
                No records found.
            </span>
            <?php
        }
        ?>

    </body>
</html>

Al final, le sugiero que use un enfoque orientado a objetos, como implementar una clase MySQLiConnection (para manejar la conexión db) y una clase MySQLiAdapter (para manejar la funcionalidad de consulta). Ambas clases deben crearse una instancia solo una vez. MySQLiConnection se debe pasar como argumento constructor a la clase MySQLiAdapter. La clase MySQLiAdapter necesita una clase MySQLiConnection para consultar el db y para recibir los resultados. Puede extender su uso implementando las interfaces correspondientes también, pero traté de mantener mi explicación simple.

También te sugiero que uses PDO en lugar de MySQLi. Una de las razones que descubrí cuando implementé este código: el sistema de manejo de excepciones un tanto desafiante en MySQLi.

¡Buena suerte!