Displaying individual rows with same key as part of larger associative array using PHP and HTML

Question

I am trying to display data from multiple MySQL tables in individual text fields in an HTML form using PHP. Here are three sample tables from a database, followed by code that I am using to select the data and echo it to text fields in HMTL.

item_id	item_title	user_id
1	Abc	2
2	Def	4

document_id	document_title	item_id
1	Ghi	1
2	Jkl	1
3	Mno	1
4	Pqr	2

user_id	user_name
1	John Doe
2	Jane Doe
3	James Doe
4	Jan Doe

Here is the code that I am using:

<?php
$sql = "SELECT i.item_title, d.document_title, u.user_name 
       FROM items as i 
       INNER JOIN documents as d
       ON i.item_id = d.item_id
       INNER JOIN users as u
       ON i.user_id = datat.item_number
       WHERE i.item_id = 1;";
    $result = mysqli_query($db_server, $sql);
    while($row=mysqli_fetch_assoc($result))
        {foreach($row as $key=>$value) ${$key}=$value;}
?>
    
    <form method="POST" action="updateItem.php">
    <p><textarea name="item_title"> <?php echo $item_title;?> </textarea></p>
    <p><input type="text" name="user_name" value="<?php echo $user_name?>"/></p>
    <p><input type="text" name="document_title[]" value="<?php echo $document_title?>"/></p>
    <p><input type="text" name="document_title[]" value="<?php echo $document_title?>"/></p>
    <p><input type="text" name="document_title[]" value="<?php echo $document_title?>"/></p>

The problem that I am running into is with the three fields that are supposed to display the document titles. The associate array produces a key=>value combo for these three rows in the document table that all have the same key (document_title). So how can I access these and display these three rows individually in individual fields in the HTML form?

Remember to always use htmlspecialchars when displaying in HTML and please never do ${$key}=$value;. It's a terrible coding practice that will only give you headaches later. — Dharman
– Dharman ♦, Commented May 7 at 11:54

IT goldman · Accepted Answer · 2025-05-13 00:37:31Z

2

In your example and by your logic, a user has an item with many documents. So for each user-item you need to loop over its documents.

The way it is now you are finishing the loop before echoing each value, and you only output the last row's values.

So here's a fixed version:

<form method="POST" action="updateItem.php">

    <?php
    $first = true;
    while ($row = mysqli_fetch_assoc($result)) {
        $item_title = $row['item_title'];
        $user_name = $row['user_name'];
        $document_title = $row['document_title'];

        if ($first) {
            $first = false;
            ?>
            <p><textarea name="item_title"> <?php echo htmlspecialchars($item_title); ?> </textarea></p>
            <p><input type="text" name="user_name" value="<?php echo htmlspecialchars($user_name); ?>" /></p>
            <?php
        }
        ?>

        <p><input type="text" name="document_title[]" value="<?php echo htmlspecialchars($document_title); ?>" /></p>
        <?php
    }
    ?>
    <p><input type="submit" value="Update" /></p>

</form>

UPDATE: The correct query should be:

SELECT i.item_title, d.document_title, u.user_name
FROM
    items AS i
    LEFT JOIN documents AS d ON i.item_id = d.item_id
    LEFT JOIN users AS u ON u.user_id = i.user_id
    /* LEFT JOIN another_table ON another_table.id = our_table.another_table_id */
WHERE
    i.item_id = 1

edited May 13 at 0:37

answered May 7 at 0:56

IT goldman

20.4k2 gold badges18 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Dave May 12 at 11:05

Thank you. I tried what you indicated but only the title of the first of the three documents ("Ghi") is being output with <?php echo htmlspecialchars($document_title); ?>. If I include additional <?php echo htmlspecialchars($document_title); ?> statements as well, I am still only getting the title of the first document each time. How can I echo the second and third document titles ("Jkl" and "Mno") associated with item_id 1?

IT goldman May 12 at 12:03

what is the query you are using? the one provided has error in it: i.user_id = datat.item_number has two errors: datat is not a table alias. also you are comparing user_id with item_number which is wrong.

Dave May 12 at 16:58

Sorry, IT goldman, that was indeed a typo! The query that I used is at the end of this message. What I am trying to do is to display five fields in a single form: the item title, the user name of the user that created the item, and the three documents associated with the item. I can only retrieve one of the documents. $sql = "SELECT i.item_title, d.document_title, u.user_name FROM items as i INNER JOIN documents as d ON i.item_id = d.item_id INNER JOIN users as u ON u.user_id = u.user_id WHERE i.item_id = 1;

IT goldman May 13 at 0:39

Hi Dave, I've updated the answer with a correct query.

Dave May 13 at 2:09

Thank you, IT goldman. The updated/correct query was the run that I was actually running. OK, so I was able to produce all of the output with that new query plus the PHP code you sent earlier, but I'm stuck on one thing: the output produces nine document form fields, with repeating patterns of three. So document 'Ghi' in three consecutive fields, and same for documents 'Jkl', and 'Mno'. Rather than three fields total with each of those documents occupying one field each. This is also going to be a problem when I try to post to my database: significant duplication. I can't figure it out!

|

Collectives™ on Stack Overflow

Displaying individual rows with same key as part of larger associative array using PHP and HTML

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related