How to echo out table rows from the db (php)
$sql = "SELECT * FROM MY_TABLE";
$result = mysqli_query($conn, $sql); // First parameter is just return of "mysqli_connect()" function
echo "<br>";
echo "<table border='1'>";
while ($row = mysqli_fetch_assoc($result)) { // Important line !!! Check summary get row on array ..
echo "<tr>";
foreach ($row as $field => $value) { // I you want you can right this line like this: foreach($row as $value) {
echo "<td>" . $value . "</td>"; // I just did not use "htmlspecialchars()" function.
}
echo "</tr>";
}
echo "</table>";
How do I echo rows that have a specific variable in it from Database
Please note that the credits for the sql statement that I used belong to @Barmar, because he had yesterday the idea of the joined queries first.
Now, down under are the two methods to use. Notice that I didn't use any OOP or functions. The reason is that I wanted you to have a compact view of all steps.
How to use mysqli prepared statements and exception handling
1. Use get_result() + fetch_object() or fetch_array() or fetch_all():
This method (recommended) works only if the driver mysqlnd (MySQL Native Driver) is installed/activated. I think the driver is by default activated in PHP >= 5.3. Implement the code and let it run. It should work. If it works, then it's perfect. If not, try to activate mysqlnd driver, e.g. uncomment extension=php_mysqli_mysqlnd.dll in php.ini. Otherwise you must use the second method (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>
NB: How to use fetch_array() instead of 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;
}
}
//...
Make the corresponding changes in the html code too.
NB: How to use fetch_all() instead of fetch_object():
//...
if ($numberOfRows > 0) {
/*
* Use mysqli_result::fetch_all to fetch all rows at once.
*/
$fetchedData = $result->fetch_all(MYSQLI_ASSOC);
}
//...
Make the corresponding changes in the html code too.
2. Use store_result() + bind_result() + fetch():
Works without the driver 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,
* 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>
In the end I'd suggest you to use an object-oriented approach, like implementing a MySQLiConnection class (for handling the db connection) and a MySQLiAdapter class (for handling the query functionality). Both classes should be instantiated only once. The MySQLiConnection should be passed as constructor argument to the MySQLiAdapter class. MySQLiAdapter class needs an MySQLiConnection class for querying the db and for receiving the results. You could extend their use by implementing corresponding interfaces too, but I tried to keep my explanation simple.
I'd also suggest you to use PDO instead of MySQLi. One of the reasons I've discovered when I implemented this code: the somewhat challenging exception handling system in MySQLi.
Good luck!
How to echo the correct number of rows of a table
using PDO you could use
$result = $con->prepare("select count(*) from images");
$result->execute();
$total = $result->fetchColumn();
echo $total;
assuming $con is your connection to db
from PHP doc
DOStatement::rowCount() returns the number of rows affected by the
last DELETE, INSERT, or UPDATE statement executed by the corresponding
PDOStatement object.If the last SQL statement executed by the associated PDOStatement was
a SELECT statement, some databases may return the number of rows
returned by that statement. However, this behaviour is not guaranteed
for all databases and should not be relied on for portable
applications.
PHP-Mysql : Php echo values from sql out put from 1st and second row
Your query is returning the correct data and is limited to two rows. When you come to create the HTML tables your while loop is only reading one row at a time, so you never see tomorrow's data.
However, there's no guarantee which order the data is returned in, so you need to add an ORDER BY Date ASC clause.
Since you're only returning two rows you don't need a while statement. You just need two successive fetches, something like this:
<?php
include "dbConn.php"; // Using database connection file here
// Query updated to include an ORDER BY clause
$records = mysqli_query($db,"select * from prayers where Date IN (CURDATE(),
DATE_ADD(CURDATE(), INTERVAL 1 DAY)) ORDER BY Date ASC LIMIT 2;" ); // fetch data from database
// read first row with today's data
$todaysData = mysqli_fetch_array($records);
// Create table for today here
// Now read second row, with tomorrow's data.
$tomorrowData = mysqli_fetch_array($records);
// Create table for tomorrow here.
mysqli_close($db); // not strictly required since PHP will do this anyway
echo two table from database in one time
<?php
include 'dbconfig.php';
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "SELECT * FROM t1";
$sql_1 = "SELECT * FROM t2";
$result = $conn->query($sql);
$result_1 = $conn->query($sql_1);
if ($result->num_rows >0) {
// output data of each row
while($row[] = $result->fetch_assoc()) {
$tem = $row;
$json = json_encode($tem);
$a1 = rtrim($json , ']');
}
} else {
echo "0 results";
}
if ($result_1->num_rows >0) {
// output data of each row
while($row_1[] = $result_1->fetch_assoc()) {
$tem_1 = $row_1;
$json_1 = json_encode($tem_1);
$a2 = ltrim($json_1 , '[');
}
} else {
echo "0 results";
}
echo $a1 , ',' ;
echo $a2 ;
$conn->close();
?>
Echo out information from database using php
This should work:
//Database Settings
$host = "sql.yourserver.com";
$user = "username";
$pass = "password";
$dbnm = "database_name";
//Connect to Database
$conn = mysql_connect ($host, $user, $pass);
if ($conn) {
$db= mysql_select_db ($dbnm);
if (!$db) {
die ("Database Not Found");
}
} else {
notify("Fatal Error. Can not connect to Database", "");
}
//Form Query
$query = "SELECT * FROM `links_tbl`";
//Fetch Results
$data = mysql_query($query) or die(mysql_error());
//Start UL
echo "<ul>\n"
//Loop through results
while($info = mysql_fetch_array( $data ))
{
//echo the list item
echo "<li><a href=".$info['LINKurl'].">".$info['LINKname']."</a></li>\n";
}
//End UL
echo "</ul>\n"
Keep in mind you need to close the <a> tag AFTER you output the name.
PHP - how to echo all the values of a single column from mySQL database
The problem is very clear here.. You are calling mysqli_fetch_array multiple times.
mysqli_fetch_array() moves the pointer forward each time you call it.
Just remove the first $row=mysqli_fetch_array($result,MYSQLI_ASSOC); from code.
How do I echo rows that have a specific ID in it from Database
Use below code:
1 . use $userinfo instead of $query while($row = mysql_fetch_array($query))
2 . Change $mysqli->query to mysqli_query.
3 . Use mysqli instead of mysql.
<?php
$id = $_GET['id'];
//mysqli_connect("host","username","password","dbname")
$conn=mysqli_connect("localhost","root","","dbname");
$sql="SELECT * FROM invoices WHERE userid = '.$id.'";
$result=mysqli_query($conn,$sql);
echo '<table>';
while($row = mysqli_fetch_array($result)){
echo '<tr>
<td>' .$row['id'].'</td>
<td>' .$row['amount'].'</td>
<td>' .$row['date'].'</td>
<td>' .$row['status'].'</td>
</tr>';
}
echo '</table>';
?>
Using PHP to echo specific data from MySQL rows?
You have a couple of options to store the results from the query and then output them in PHP:
- an associative array
while ($row = $result->fetch_assoc()) {
$data[$row['type']] = $row['value'];
}
echo "We found that {$data['total_clicks']} generated {$data['total_revenue']} in revenue.";
- variable variables
while ($row = $result->fetch_assoc()) {
${$row['type']} = $row['value'];
}
echo "We found that $total_clicks generated $total_revenue in revenue.";
How can I echo the position number of SQL row in PHP?
when you fetch records, you may use a variable as position number.
#DB is a class connect to mysql database.
DB::init();
$sql = "SELECT * FROM RowNo ";
$stmt = DB::query( $sql );
$i = 1;
while( $rec1 = $stmt->fetch() ) {
echo "row $i : ";
print_r( $rec1 );
echo '<BR>'.PHP_EOL;
$i++;
};
result :
row 1 : Array ( [Post ID] => 8788277463 [Author] => Me )
row 2 : Array ( [Post ID] => 2894728477 [Author] => Me )
row 3 : Array ( [Post ID] => 3898994718 [Author] => Me )
row 4 : Array ( [Post ID] => 4891784883 [Author] => Me )
row 5 : Array ( [Post ID] => 1185819276 [Author] => Me )
Related Topics
Which Is Faster and Better, Switch Case or If Else If
Why Is Textarea Filled with Mysterious White Spaces
Artisan Migrate Could Not Find Driver
How to Trim White Spaces of Array Values in PHP
Scaling a Chat App - Short Polling VS. Long Polling (Ajax, PHP)
How to Re-Save the Entity as Another Row in Doctrine 2
MySQL Error: 1364 Field 'Display_Name' Doesn't Have Default Value
Remove Diacritics from a String
Print Newline in PHP in Single Quotes
Permission Denied - PHP Unlink