I've started to code in April 2021 so I'm super new to this. Working on my final exam, I'd need your precious help! I want to generate a dynamic list from a database, containing 2 joined tables (users and interest). The 2 tables both contain "ID_user" which links them as a foreign key. The idea is that one the user is logged in, the profile page displays all the interests the user selected at sign up. At the moment I can only display the last interest selected and not all of them.

Here is my php:

$request2 = "SELECT `interest`.`name_Interest` 
            FROM `interest` 
            INNER JOIN `users` 
                ON `interest`.`ID_user` = `users`.`ID_user` 
            WHERE `pseudo` = '".$url_pseudo."'
            ";

$resultat2 = mysqli_query($connexion, $request2) or die (mysqli_error($connexion));


$nb_resultat2 = mysqli_num_rows($resultat2);

if ($nb_resultat2 > 0) {

    while ($row = mysqli_fetch_array($resultat2)){

        $name_Interest = $row["name_Interest"];

    }
}

Here is the HTML displaying the response:

enter image description here

Here is my db:

enter image description here

Any idea why I can get only 1 value?

enter image description here

thanks in advance

CodePudding user response：

Your while loop is writing to the same variable for each iteration of the loop;

while ($row = mysqli_fetch_array($resultat2)){
    $name_Interest = $row["name_Interest"];
}

This will leave $name_Interest containing the last value from your database after the loop has completed.

To resolve this, you will need to keep a list of interest names - this can be achieved by using an array. PHP Array Documentation

// Declares the empty array 
$interests = [];

// Loop through database results
while ($row = mysqli_fetch_array($resultat2)){
    // Add this value to the array
    $interests[] = $row["name_Interest"];
}

Now, $interests will hold all of the values from the database!

You will need to print these out differently in your HTML, by looping through all the values in the array and printing one at a time: (PHP foreach documentation)

<ul>
    <?php foreach ($interests as $interest) { ?>
        <li><?php echo $interest; ?></li>
    <?php } ?>
</ul>

CodePudding user response：

Solution Simple store all user interests on array and then show in iteration on page.

Code:

$user_interests=array();
 while ($row = mysqli_fetch_array($resultat2)){

        $user_interests[] = $row["name_Interest"];
    }

Now $user_interests array holds all interests of users as a result of join.

At last loop over it

 <?php foreach ($user_interests as $interest) { ?>
        <p><?php echo $interest; ?></p>
    <?php } ?>