Как эхо строк, имеющих в нем определенную переменную из базы данных

Обратите внимание, что кредиты для оператора sql, который я использовал, принадлежат @Barmar, потому что у него вчера была идея связанных запросов.

Теперь ниже используются два метода. Обратите внимание, что я не использовал ООП или функции. Причина в том, что я хотел, чтобы у вас был компактный вид всех шагов.

Как использовать подготовленные инструкции mysqli и обработку исключений

1. Используйте get_result () + fetch_object () или fetch_array () или fetch_all ():

Этот метод (рекомендуется) работает только в том случае, если установлен драйвер / драйвер mysqlnd ( MySQL Native Driver ). Я думаю, что драйвер по умолчанию активирован в PHP> = 5.3. Внедрите код и дайте ему запустить. Он должен работать. Если это сработает, значит, это прекрасно. Если нет, попробуйте активировать драйвер mysqlnd, например uncomment extension=php_mysqli_mysqlnd.dll в php.ini. В противном случае вы должны использовать второй метод (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, * eg 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, * eg 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. Eg 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: Как использовать fetch_array () вместо fetch_object ():

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

Внесите соответствующие изменения в код html.

NB: Как использовать 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 Native Driver ).

 <?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, * eg 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, * eg 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. * Eg store, eg 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. * Eg 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 (для обработки соединения db) и класс MySQLiAdapter (для обработки функциональности запроса). Оба класса должны создаваться только один раз. MySQLiConnection должен быть передан как аргумент конструктора классу MySQLiAdapter. Для класса MySQLiAdapter требуется класс MySQLiConnection для запроса db и получения результатов. Вы могли бы расширить их использование, реализовав соответствующие интерфейсы, но я попытался упростить мое объяснение.

Я также предлагаю вам использовать PDO вместо MySQLi. Одна из причин, которые я обнаружил, когда я реализовал этот код: несколько сложная система обработки исключений в MySQLi.

Удачи!

Вы должны просто присоединиться к двум запросам:

 $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"); 

Я думаю, что проблема заключается в том, что вам не хватает перевернутых запятых для поискового запроса в предложении WHERE. Он должен выглядеть так: affsub = '$ affsub.

Попробуй это.

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