所以首先我的数据库表设置如下：
id | affsub | offer_name | date | time | payout
1 | stringhere | offer | 2017-09-12 | 06:47:00 | 1
我想将包含affsub stringhere的所有行回显到html表中.我试过这个：
<?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中具有一定挑战性的异常处理系统.
祝好运！
来源：https://www.icode9.com/content-1-483051.html联系客服
