Jazzy 1012
asked on
I want to echo out my field names with its values into a table
I tried this:
But call I get is
How can I just echo out the 'name' which is id ? and its field? I get the above result for each field name
<?php
echo '<table>';
$query = mysqli_query($conn,"SELECT * FROM `call`");
// Loop over all result rows
while($row = mysqli_fetch_field($query)) {
// Loop over all fields per row
foreach($row as $field => $value) {
echo '<tr><td>' . htmlentities($field) . '</td><td>' . htmlentities($value) . '</td></tr>';
}
// New data row can optionally be seperated with a blank line here
echo '<tr><td colspan="2"> </td></tr>';
}
echo '</table>'; ?>
But call I get is
name id
orgname id
table call
orgtable call
def
db ll
catalog def
max_length 0
length 11
charsetnr 63
flags 49667
type 3
decimals 0
How can I just echo out the 'name' which is id ? and its field? I get the above result for each field name
ASKER
Nope I got nothing, it was just an empty page, I just want to echo out the field names I dont want the values with it Just for it to show as
Id Name Date ... etc
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok that worked but also how can I know echo out the values for each column?
I have this code but it only echos out the id because I didn't know how to echo the rest
I have this code but it only echos out the id because I didn't know how to echo the rest
<?php
$query2= "SELECT * from `call` WHERE `By` = '$username'";
$result= mysqli_query($conn, $query2);
while($row = mysqli_fetch_array($result))
{
?>
<tr>
<td>
<?php echo $row[0];?>
</td>
</tr>
<?php
}
?>
I want it to echo all the column values for each row for that certain user
ASKER
And if I do it like this
<?php
$query2= "SELECT * from `call` WHERE `By` = '$username'";
$result= mysqli_query($conn, $query2);
while($row = mysqli_fetch_array($result))
{
?>
<?php
echo '<tr>';
foreach($row as $values){
echo '<td>';
print_r($values);
echo '</td>';
}
echo '</tr>';
?>
<?php
}
?>
It is echo each field value twice. Which is clashing with the order
Typically we refer to "columns" and "rows" when we talk about databases. A column has a name (something like "id" and "email") and a row contains the values of each column (something like "3" and "you@your.org"). This is organized into database "tables," each table is like a spreadsheet.
You can get the names and some other information about the columns with SHOW COLUMNS. If you do not use aliases (the "AS" keyword) in your queries, the column names will be present in the form of keys if you retrieve the query results with fetch_assoc() functions, and in the form of property names if you use fetch_object() functions.
This article shows the general design patterns for table maintenance, including ways of producing tabular output from query results.
https://www.experts-exchange.com/articles/12335/PHP-and-MySQLi-Table-Maintenance.html
You can get the names and some other information about the columns with SHOW COLUMNS. If you do not use aliases (the "AS" keyword) in your queries, the column names will be present in the form of keys if you retrieve the query results with fetch_assoc() functions, and in the form of property names if you use fetch_object() functions.
This article shows the general design patterns for table maintenance, including ways of producing tabular output from query results.
https://www.experts-exchange.com/articles/12335/PHP-and-MySQLi-Table-Maintenance.html
ASKER
Yes but now I would like to echo out the values but they keep its not seeming to work
Most of this is just setup. The moving parts start at line 176. This initial "proof-of-concept" script uses print_r() to let us verify that we are getting the right information. The next step will create the HTML for the tabular display of this data.
<?php // demo/mysqli_example.php
/**
* https://www.experts-exchange.com/questions/28996862/I-want-to-echo-out-my-field-names-with-its-values-into-a-table.html#a41973189
*
* Demonstrate some of the basics of MySQLi
*
* References for PHP and MySQL(i):
*
* http://php.net/manual/en/mysqli.overview.php
* http://php.net/manual/en/class.mysqli.php
* http://php.net/manual/en/class.mysqli-stmt.php
* http://php.net/manual/en/class.mysqli-result.php
* http://php.net/manual/en/class.mysqli-warning.php
* http://php.net/manual/en/class.mysqli-sql-exception.php <-- DID NOT WORK PHP 5.3+, MySQL 5.1+
*
* http://php.net/manual/en/mysqli.construct.php
* http://php.net/manual/en/mysqli.real-escape-string.php
* http://php.net/manual/en/mysqli.query.php
* http://php.net/manual/en/mysqli.errno.php
* http://php.net/manual/en/mysqli.error.php
* http://php.net/manual/en/mysqli.insert-id.php
*
* http://php.net/manual/en/mysqli-result.num-rows.php
* http://php.net/manual/en/mysqli-result.fetch-array.php <-- DO NOT USE THIS
* http://php.net/manual/en/mysqli-result.fetch-object.php
*/
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
echo '<pre>';
// CREATE AN ARRAY OF TEST DATA
$test_data_arrays = array
( array( "fname" => "Walter" , "lname" => "Williams" )
, array( "fname" => "Ray" , "lname" => "Paseur" )
, array( "fname" => "Bill" , "lname" => "O'Reilly" )
, array( "fname" => "Ray" , "lname" => "Capece" )
, array( "fname" => "John" , "lname" => "Paseur" )
)
;
// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";
// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);
// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
$err
= "CONNECT FAIL: "
. $mysqli->connect_errno
. ' '
. $mysqli->connect_error
;
trigger_error($err, E_USER_ERROR);
}
// ACTIVATE THIS TO SHOW WHAT THE DB CONNECTION OBJECT LOOKS LIKE
// var_dump($mysqli);
// CREATING A TABLE FOR OUR TEST DATA
$sql
=
"
CREATE TEMPORARY TABLE my_table
( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
, fname VARCHAR(24) NOT NULL DEFAULT ''
, lname VARCHAR(24) NOT NULL DEFAULT ''
, xwhen TIMESTAMP NOT NULL
)
"
;
// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res = $mysqli->query($sql))
{
$err
= 'QUERY FAILURE:'
. ' ERRNO: '
. $mysqli->errno
. ' ERROR: '
. $mysqli->error
. ' QUERY: '
. $sql
;
trigger_error($err, E_USER_ERROR);
}
// ACTIVATE THIS TO SHOW THE RESULTS OF THE QUERY
// var_dump($res);
// LOADING OUR DATA INTO THE TABLE
foreach ($test_data_arrays as $thing)
{
// ESCAPE THE DATA FOR SAFE USE IN A QUERY
$safe_fn = $mysqli->real_escape_string($thing['fname']);
$safe_ln = $mysqli->real_escape_string($thing['lname']);
// CONSTRUCT THE QUERY USING THE ESCAPED VARIABLES
$sql = "INSERT INTO my_table ( fname, lname ) VALUES ( '$safe_fn', '$safe_ln' )";
// RUN THE QUERY TO INSERT THE ROW
$res = $mysqli->query($sql);
// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
$err
= 'QUERY FAILURE:'
. ' ERRNO: '
. $mysqli->errno
. ' ERROR: '
. $mysqli->error
. ' QUERY: '
. $sql
;
trigger_error($err, E_USER_ERROR);
}
// GET THE AUTO_INCREMENT KEY OF THE RECORD JUST INSERTED
$id = $mysqli->insert_id;
echo "MySQLI INSERTED A ROW CONTAINING <b>$safe_fn $safe_ln</b> WITH AUTO_INCREMENT ID = $id" . PHP_EOL;
}
echo PHP_EOL;
// CONSTRUCT A QUERY
$sql = "SELECT * FROM my_table ORDER BY lname, fname";
$res = $mysqli->query($sql);
// IF mysqli_query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
$err
= 'QUERY FAILURE:'
. ' ERRNO: '
. $mysqli->errno
. ' ERROR: '
. $mysqli->error
. ' QUERY: '
. $sql
;
trigger_error($err, E_USER_ERROR);
}
// IF WE GET THIS FAR, THE QUERY SUCCEEDED AND WE HAVE A RESULT OBJECT IN $res
// NOW WE CAN USE OTHER MYSQLI::RESULT PROPERTIES AND METHODS
// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num = $res->num_rows;
$num_fmt = number_format($num);
if (!$num)
{
echo PHP_EOL . "QUERY: $sql ";
echo PHP_EOL . "RETURNED NO DATA ";
}
else
{
echo PHP_EOL . "QUERY: $sql ";
echo PHP_EOL . "RETURNED $num_fmt ROWS ";
}
echo PHP_EOL;
echo PHP_EOL . 'USING MySQLi_Result::Fetch_<i>Assoc</i>(): ';
echo PHP_EOL;
// FETCH THE TOP ROW FROM THE RESULTS SET AND GET THE KEYS
$row = $res->fetch_assoc();
$keys = array_keys($row);
print_r($keys);
// NOW GET THE VALUES FROM THE TOP ROW
print_r(array_values($row));
// ITERATE OVER THE RESULTS SET
while ($row = $res->fetch_assoc())
{
// ROW BY ROW PROCESSING IS DONE HERE
print_r(array_values($row));
echo PHP_EOL;
}
echo PHP_EOL;
Here is some simple code to dump a whole table.
$conn = mysqli_connect($host,$user, $password, $db);
$result = mysqli_query($conn,"SELECT * FROM `tablename`");
// Get the first row for the headers
$row = mysqli_fetch_assoc($result);
// Extract the keys
$fields = array_keys($row);
// Open the table
echo '<table>';
// Output the header
echo "<tr><th>" . implode('</th><th>', $fields) . "</th></tr>";
// Loop over all result rows
do {
echo "<tr><td>" . implode('</td><td>', $row) . "</td></tr>";
} while ($row = mysqli_fetch_assoc($result));
// Close the table
echo '</table>';
Again, mostly setup. Moving parts start at line 176. Please check the code and comments in the example, and post back if you still have questions.
https://iconoun.com/demo/temp_jasmine.php
https://iconoun.com/demo/temp_jasmine.php
<?php // demo/mysqli_example.php
/**
* https://www.experts-exchange.com/questions/28996862/I-want-to-echo-out-my-field-names-with-its-values-into-a-table.html#a41973189
*
* Demonstrate some of the basics of MySQLi
*
* References for PHP and MySQL(i):
*
* http://php.net/manual/en/mysqli.overview.php
* http://php.net/manual/en/class.mysqli.php
* http://php.net/manual/en/class.mysqli-stmt.php
* http://php.net/manual/en/class.mysqli-result.php
* http://php.net/manual/en/class.mysqli-warning.php
* http://php.net/manual/en/class.mysqli-sql-exception.php <-- DID NOT WORK PHP 5.3+, MySQL 5.1+
*
* http://php.net/manual/en/mysqli.construct.php
* http://php.net/manual/en/mysqli.real-escape-string.php
* http://php.net/manual/en/mysqli.query.php
* http://php.net/manual/en/mysqli.errno.php
* http://php.net/manual/en/mysqli.error.php
* http://php.net/manual/en/mysqli.insert-id.php
*
* http://php.net/manual/en/mysqli-result.num-rows.php
* http://php.net/manual/en/mysqli-result.fetch-array.php <-- DO NOT USE THIS
* http://php.net/manual/en/mysqli-result.fetch-object.php
*/
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
echo '<pre>';
// CREATE AN ARRAY OF TEST DATA
$test_data_arrays = array
( array( "fname" => "Walter" , "lname" => "Williams" )
, array( "fname" => "Ray" , "lname" => "Paseur" )
, array( "fname" => "Bill" , "lname" => "O'Reilly" )
, array( "fname" => "Ray" , "lname" => "Capece" )
, array( "fname" => "John" , "lname" => "Paseur" )
)
;
// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";
require_once('RAY_live_data.php');
// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);
// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
$err
= "CONNECT FAIL: "
. $mysqli->connect_errno
. ' '
. $mysqli->connect_error
;
trigger_error($err, E_USER_ERROR);
}
// ACTIVATE THIS TO SHOW WHAT THE DB CONNECTION OBJECT LOOKS LIKE
// var_dump($mysqli);
// CREATING A TABLE FOR OUR TEST DATA
$sql
=
"
CREATE TEMPORARY TABLE my_table
( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
, fname VARCHAR(24) NOT NULL DEFAULT ''
, lname VARCHAR(24) NOT NULL DEFAULT ''
, xwhen TIMESTAMP NOT NULL
)
"
;
// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res = $mysqli->query($sql))
{
$err
= 'QUERY FAILURE:'
. ' ERRNO: '
. $mysqli->errno
. ' ERROR: '
. $mysqli->error
. ' QUERY: '
. $sql
;
trigger_error($err, E_USER_ERROR);
}
// ACTIVATE THIS TO SHOW THE RESULTS OF THE QUERY
// var_dump($res);
// LOADING OUR DATA INTO THE TABLE
foreach ($test_data_arrays as $thing)
{
// ESCAPE THE DATA FOR SAFE USE IN A QUERY
$safe_fn = $mysqli->real_escape_string($thing['fname']);
$safe_ln = $mysqli->real_escape_string($thing['lname']);
// CONSTRUCT THE QUERY USING THE ESCAPED VARIABLES
$sql = "INSERT INTO my_table ( fname, lname ) VALUES ( '$safe_fn', '$safe_ln' )";
// RUN THE QUERY TO INSERT THE ROW
$res = $mysqli->query($sql);
// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
$err
= 'QUERY FAILURE:'
. ' ERRNO: '
. $mysqli->errno
. ' ERROR: '
. $mysqli->error
. ' QUERY: '
. $sql
;
trigger_error($err, E_USER_ERROR);
}
// GET THE AUTO_INCREMENT KEY OF THE RECORD JUST INSERTED
$id = $mysqli->insert_id;
echo "MySQLI INSERTED A ROW CONTAINING <b>$safe_fn $safe_ln</b> WITH AUTO_INCREMENT ID = $id" . PHP_EOL;
}
echo PHP_EOL;
// CONSTRUCT A QUERY
$sql = "SELECT * FROM my_table ORDER BY lname, fname";
$res = $mysqli->query($sql);
// IF mysqli_query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
$err
= 'QUERY FAILURE:'
. ' ERRNO: '
. $mysqli->errno
. ' ERROR: '
. $mysqli->error
. ' QUERY: '
. $sql
;
trigger_error($err, E_USER_ERROR);
}
// IF WE GET THIS FAR, THE QUERY SUCCEEDED AND WE HAVE A RESULT OBJECT IN $res
// NOW WE CAN USE OTHER MYSQLI::RESULT PROPERTIES AND METHODS
// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num = $res->num_rows;
$num_fmt = number_format($num);
if (!$num)
{
echo PHP_EOL . "QUERY: $sql ";
echo PHP_EOL . "RETURNED NO DATA ";
}
else
{
echo PHP_EOL . "QUERY: $sql ";
echo PHP_EOL . "RETURNED $num_fmt ROWS ";
}
echo PHP_EOL;
echo PHP_EOL . 'USING MySQLi_Result::Fetch_<i>Assoc</i>(): ';
echo PHP_EOL;
// FETCH THE TOP ROW FROM THE RESULTS SET AND GET THE KEYS
$row = $res->fetch_assoc();
$keys = array_keys($row);
// PUT THE KEYS INTO TABLE HEADERS
$table_html = '<tr>';
foreach ($keys as $key)
{
$table_html .= '<th>' . $key . '</th>';
}
$table_html .= '</tr>' . PHP_EOL;
// NOW GET THE VALUES FROM THE TOP ROW
$table_row = '<tr>';
foreach ($row as $column_value)
{
$table_row .= '<td>' . $column_value . '</td>';
}
$table_row .= '</tr>' . PHP_EOL;
// APPEND THIS ROW TO THE TABLE HEAD
$table_html .= $table_row;
// ITERATE OVER THE RESULTS SET TO GET THE REST OF THE ROWS
while ($row = $res->fetch_assoc())
{
$table_row = '<tr>';
foreach ($row as $column_value)
{
$table_row .= '<td>' . $column_value . '</td>';
}
$table_row .= '</tr>' . PHP_EOL;
// APPEND THIS ROW TO THE TABLE
$table_html .= $table_row;
}
// ASSEMBLE THE TABLE AND DISPLAY IT
$out = '<table>';
$out .= $table_html;
$out .= '</table>';
echo $out;
Open in new window