Insert MySQL database fields into separate columns on HTML table using PHP

PHP
1

I’m new to PHP and working my way through Kevin Yank’s Sitepoint book (Build Your Own Database Driven Web Site using PHP and MySQL; 4th Edition).

I can create a list of all existing jokes in the database (pp.126-131), but it does not say and I cannot figure out how to list corresponding data from another field for each entry in a separate adjacent column (i.e. how to list the date of each joke alongside the text).

The code I’m using is below (using separate docs for the ‘index’ (php) and ‘jokes’ (html with php), as per Yank’s advice.

Thanks in advance for advice of what I need to include to do this,

Andy

index.php reads:


<?php

...

$result = mysqli_query($link, 'SELECT joketext FROM joke');
if (!$result)
{
$error = 'Error fetching jokes: ' . mysqli_error($link);
include 'error.html.php';
exit();
}

while ($row = mysqli_fetch_array($result))
{
$jokes[] = $row['joketext'];
}

include 'jokes.html.php';
?>

jokes.html.php reads:


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
	"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd".
<html xmlns="http://www.w3.org/1999/xhtml".
 xml :lang="en" lang="en">
	<head>
		<title>List of Jokes</title>
		<meta http-equiv="Content-Type"
		content="text/html; charset=utf-8" />
</head> 
<body>
<p>Here are all the jokes in the database:</p>

<?php foreach ($jokes as $joke): ?>
<p>
<?php echo htmlspecialchars($joke, ENT_QUOTES, 'UTF-8'); ?>
	<blockquote></p>
		</p></blockquote>
	<?php endforeach; ?>

</body>
</html>
2

Hi andygout!

First, You have to alter your query to select more fields from your database/table.
In this case you’re only selecting the “joketext”.
Something like: SELECT joketext, otherfield1, otherfield2 FROM joke

Then you have to assign those field values to your jokes array:


while ($row = mysqli_fetch_array($result))
{
$jokes['joketext'] = $row['joketext'];
$jokes['otherfield1'] = $row['otherfield1'];
$jokes['otherfield2'] = $row['otherfield2'];
}

Last, in your jokes.html.php:


<?php foreach ($jokes as $joke): ?>
<p>
<?php echo htmlspecialchars($joke, ENT_QUOTES, 'UTF-8'); ?>
    <blockquote></p>
        </p></blockquote>
<?php endforeach; ?>

HTH!

3

Hi Mario,

Thanks for responding.

I’ve made those changes but now it’s just presenting the last joke entry and the separate fields are presented as separate rows underneath rather than columns in the same row.

But the section of the ‘jokes.html.php’ code appears to be identical to the one I gave - have I missed the change I should be making to this?

Thanks so much,

Andy

4

Hi,

You’re right. Only the last joke gets outputed because the jokes array gets overwritten all the time as the keys are always the same. :slight_smile:
You’ll have to modify the jokes array to be multidimensional:


$i = 0;
while ($row = mysqli_fetch_array($result))
{
	$jokes[$i]['joketext'] = $row['joketext'];
	$jokes[$i]['otherfield1'] = $row['otherfield1'];
	$jokes[$i]['otherfield2'] = $row['otherfield2'];
	$i++;
}

And then in jokes.html.php:


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd".
<html xmlns="http://www.w3.org/1999/xhtml".
 xml :lang="en" lang="en">
    <head>
        <title>List of Jokes</title>
        <meta http-equiv="Content-Type"
        content="text/html; charset=utf-8" />
</head> 
<body>
<p>Here are all the jokes in the database:</p>

<table border="1">
<tr>
	<td>JokeText</td>
	<td>Otherfield1</td>
	<td>Otherfield2</td>
</tr>
<?php
for ($i = 0; $i < count($jokes); $i++)
{
	echo "<tr><td>".$jokes[$i]['joketext']."</td><td>".$jokes[$i]['otherfiled1']."</td><td>".$jokes[$i]['otherfiled2']."</td></tr>";
}

?>
</table>

</body>
</html>

HTH

5

Hi Mario,

That should completely work and I see how it should, but for whatever reason I’m still getting this message:

Undefined variable: jokes

Any more ideas? (sorry!).

Andy

6

I think this might have been down to my PDO ((PHP Data Objects) not being permitted in my php.ini file. I have now permitted it and while I haven’t got the table displaying the data exactly how I want it, at least I now have a table! So I should be okay from here on in - will let you know once I have a solution.

7

Solved!

The index should read something like this:-


while ($row = mysqli_fetch_array($result))
{
$jokes[] = array('id' => $row['id'], 'otherfield1' => $row['otherfield1', 'otherfield2' => $row['otherfield2']);
}

And the display page should be like this:-


<div><p>
<table>
<tr><th colspan="3">Jokes</th></tr>
<?php foreach ($jokes as $joke): ?>
<tr>
<td><?php echo htmlspecialchars($joke['id'], ENT_QUOTES, 'UTF-8'); ?></td>
<td><?php echo htmlspecialchars($joke['otherfield1'], ENT_QUOTES, 'UTF-8'); ?></td>
<td><?php echo htmlspecialchars($joke['otherfield2'], ENT_QUOTES, 'UTF-8'); ?></td>
</tr>
<?php endforeach; ?>
</table></p>
</div>
8

I’ve another method,

$jokes=array();
$fields=array();
while($res=mysql_fetch_array($a))
{
$fields[‘field1’]=$res[‘field1’];
$fields[‘field2’]=$res[‘field2’];
$fields[‘field3’]=$res[‘field3’];
array_push($jokes,$fields);
}

Now all values are stored into array. So you can get it on jokes.html.php page

9 
$jokes=array(); // Setup an empty array for the jokes
while ($row = mysqli_fetch_array($result)) {
    $jokes[] = $row;
}

That simplifies it, each joke is added to the $jokes array in turn.

@guruparthi; are you aware that the old mysql_* extension is depreceiated as of version 5.5 of PHP and will very likely be removed from version 5.6 of PHP? You should be using the newer mysqli_* (MySQL Improved) extension like the OP is or use PDO