Link to home
Start Free TrialLog in
Avatar of Jazzy 1012
Jazzy 1012

asked on

I want to echo out my field names with its values into a table

I tried this:
							<?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">&nbsp;</td></tr>';
}
echo '</table>'; ?>

Open in new window


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

Open in new window


How can I just echo out the 'name' which is id ? and its field? I get the above result for each field name
Avatar of Julian Hansen
Julian Hansen
Flag of South Africa image

Try this
<?php
echo '<table>';
$query = mysqli_query($conn,"SELECT * FROM `call`");
// Loop over all result rows
while($row = mysqli_fetch_assoc($query)) {
    // Loop over all fields per row
   // Open row before looping through fields
    echo '<tr>';
    foreach($row as $field => $value) {
        echo '<<td>' . htmlentities($field) . '</td><td>' . htmlentities($value) . '</td>';
    }
    echo '</tr>';
    // close row after looping through fields
    // New data row can optionally be seperated with a blank line here
    echo '<tr><td colspan="2">&nbsp;</td></tr>';
}
echo '</table>'; ?>

Open in new window

Avatar of Jazzy 1012
Jazzy 1012

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

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Julian Hansen
Julian Hansen
Flag of South Africa image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
	<?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 
	}
	?>

Open in new window

I want it to echo all the column values for each row for that certain user
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 
	}
	?>

Open in new window

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
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;

Open in new window

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>'; 

Open in new window

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
<?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