所以首先我的数据库表是这样设置的:


  id | affsub | offer_name |日期|时间|支付
  
  1 |在这里报价2017-09-12 | 06:47:00 | 1


我想在html表中回显包括affsub字符串的所有行。我已经试过了:

<?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>";
  }
?>


而且我知道它正在获取affsub,因为如果我回显$ affsub,我的affsub会被回显,但是桌上没有任何显示,我不确定发生了什么。

最佳答案

请注意,我使用的sql语句的信用归@Barmar所有,因为他昨天首先有了连接查询的想法。

现在,下面是两种使用方法。注意,我没有使用任何OOP或函数。原因是我希望您对所有步骤都有一个简洁的视图。



如何使用mysqli准备的语句和异常处理

1.使用get_result()+ fetch_object()或fetch_array()或fetch_all():

仅当安装/激活了驱动程序mysqlnd(MySQL本机驱动程序)时,此方法(推荐)才有效。我认为该驱动程序默认是在PHP> = 5.3中激活的。实现代码并使其运行。它应该工作。如果可行,那就完美了。如果没有,请尝试激活mysqlnd驱动程序,例如在php.ini中取消注释extension=php_mysqli_mysqlnd.dll。否则,您必须使用第二种方法(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://stackoverflow.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>


注意:如何使用fetch_array()而不是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;
    }
}
//...


在html代码中也进行相应的更改。

注意:如何使用fetch_all()而不是fetch_object():

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


在html代码中也进行相应的更改。

2.使用store_result()+ bind_result()+ fetch():

无需驱动程序mysqlnd(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://stackoverflow.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>


最后,我建议您使用一种面向对象的方法,例如实现一个MySQLiConnection类(用于处理数据库连接)和一个MySQLiAdapter类(用于处理查询功能)。这两个类仅应实例化一次。 MySQLiConnection应该作为构造函数参数传递给MySQLiAdapter类。 MySQLiAdapter类需要一个MySQLiConnection类来查询数据库并接收结果。您也可以通过实现相应的接口来扩展它们的使用,但是我试图使我的解释保持简单。

我还建议您使用PDO代替MySQLi。我实现该代码时发现的原因之一:MySQLi中具有挑战性的异常处理系统。

祝好运!

09-19 12:27