SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Enthusiast andygout's Avatar
    Join Date
    Jun 2012
    Location
    London, United Kingdom, United Kingdom
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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 Code:
    <?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:
    PHP Code:
    <!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($jokeENT_QUOTES'UTF-8'); ?>
        <blockquote></p>
            </p></blockquote>
        <?php endforeach; ?>

    </body>
    </html>
    Last edited by ScallioXTX; Jun 26, 2012 at 00:39.

  2. #2
    SitePoint Zealot
    Join Date
    Feb 2005
    Location
    Aveiro, Portugal
    Posts
    165
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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:
    PHP Code:
    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 Code:
    <?php foreach ($jokes as $joke): ?>
    <p>
    <?php echo htmlspecialchars($jokeENT_QUOTES'UTF-8'); ?>
        <blockquote></p>
            </p></blockquote>
    <?php endforeach; ?>
    HTH!
    Yours truely
    Mário Ramos

  3. #3
    SitePoint Enthusiast andygout's Avatar
    Join Date
    Jun 2012
    Location
    London, United Kingdom, United Kingdom
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #4
    SitePoint Zealot
    Join Date
    Feb 2005
    Location
    Aveiro, Portugal
    Posts
    165
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.
    You'll have to modify the jokes array to be multidimensional:
    PHP Code:
    $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:
    HTML Code:
    <!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
    Yours truely
    Mário Ramos

  5. #5
    SitePoint Enthusiast andygout's Avatar
    Join Date
    Jun 2012
    Location
    London, United Kingdom, United Kingdom
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #6
    SitePoint Enthusiast andygout's Avatar
    Join Date
    Jun 2012
    Location
    London, United Kingdom, United Kingdom
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #7
    SitePoint Enthusiast andygout's Avatar
    Join Date
    Jun 2012
    Location
    London, United Kingdom, United Kingdom
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Solution

    Solved!

    The index should read something like this:-
    PHP Code:
    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:-
    PHP Code:
    <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. #8
    Non-Member
    Join Date
    Dec 2013
    Posts
    2
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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. #9
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,068
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by andygout View Post
    Solved!

    The index should read something like this:-
    PHP Code:
    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:-
    PHP Code:
    <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>
    PHP Code:
    $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
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •