CODEWITHSUNDEEP
phpsql
DigiNet Events
DigiNet Events

Reputation: 347

How to display multiple names from different Database tables

I"m trying to display names that connected to the same table.

There are 3 different DB tables:

  1. Tables
  2. Guests
  3. Info

First I get the data from the "Table" to get the table id.

Than I get the data from the "Info" table to figure which guests are connected to the table id so I get their id's (can be multiple id's).

And last I get the name of every guest by it's id.

My issue is that I can only get the final name I'm expecting and not all names that are connected to the same table.

The last result needs to display each table and every name that connected to table.

PHP:

$sql_e1 = "SELECT `tid` FROM `table`";
$result_e1 = $con->query($sql_e1);
if ($result_e1->num_rows > 0) {
    $i = 0;
    while($row0 = $result_e1->fetch_assoc()) {
        $table_id = $row0['tid'];
        $array[$i]['table_id'] = $table_id;

        $sql_e2 = "SELECT `id` FROM `info` WHERE `tid`='".$table_id."'";
        $result_e2 = $con->query($sql_e2);
        if ($result_e2->num_rows > 0) {
            while($row2 = $result_e2->fetch_assoc()) {
                $guest_id = $row2['id'];
                $array[$i]['guest_id'] = $guest_id;         

                $sql_e3 = "SELECT `name` FROM `guests` WHERE `id`='".$guest_id."'";
                $result_e3 = $con->query($sql_e3);
                if ($result_e3->num_rows > 0) {
                    while($row3 = $result_e3->fetch_assoc()) {
                        $array[$i]['name'] = $row3['name'];
                    } 
                }
            } 
        }                   

        $i++;
    }
}

$counter = 0;

HTML:

<? 
if (isset($i)) {
while ($counter < $i) {
include 'infodialog.php';                           
?>
<div class="<? echo $array[$counter]['table_id']; ?>">
<p><? echo $array[$counter]['name']; ?></p>
</div>
<? 
$counter++;
} } ?>

Upvotes: 0

Views: 299

Answers (2)

HazardousGlitch
HazardousGlitch

Reputation: 160

Another solution besides Rohit Rasela's is using JOINS. JOINS will be a much better solution in the long run especially when you start adding a lot of data and speed is important. It's been a while since I've done PHP and MySQL and I haven't tested this but it should work I believe:

MySQL:

SELECT
  guests.name as GuestName,
  table.tid as TableId,
  info.id as InfoId 
FROM table AS table 
JOIN info AS info ON info.tid = table.tid
JOIN guests AS guests ON guests .id = info.[guest_id_column_name]

This will return a row for each match it finds when it goes through each table and you'll be able to loop through and access the GuestName, TableId and InfoId for each match. If you don't care about the table ids, you can leave them out in the SELECT list. You can add ORDER BY if the order matters.

HTML loop:

while ($row = $result->fetch_assoc()) {
    <div>Guest name: <php echo $row['GuestName']; ?></div>
    <div>Table Id: <php echo $row['TableId']; ?></div>
    <div>Info Id: <php echo $row['InfoId']; ?></div> 
}

You can get more information on JOINs at https://www.w3schools.com/sql/sql_join.asp

Upvotes: 0

Rohit Rasela
Rohit Rasela

Reputation: 445

If you want to get multiple names in array your code should be:

$array[$i]['name'][] = $row3['name'];

Or according guest ids code should be

$array[$i][$guest_id]['name'][] = $row3['name'];

This will get all the names but you have to change your HTML code according array.

Upvotes: 1

Related Questions