CODEWITHSUNDEEP
phpsql
xitas
xitas

Reputation: 1164

How to show multiple records with same name?

I have a database tables containing four fields.

id is my primary key and the rest of the fields are: name, address and phone number of the restaurant accordingly. Now, I have many restaurants belonging to different chains, so I saved them using same name and their address and phone no fields look like this:

This is how my database looks like:

id |     name    | address  | Phone_no |

1  | restaurant1 | address1 | XXXXXX1  |
2  | restaurant1 | address2 | XXXXXX2  |
3  | restaurant1 | address3 | XXXXXX3  |
4  | restaurant2 | address1 | XXXXXX1  |
5  | restaurant2 | address2 | XXXXXX2  |
6  | restaurant3 | address1 | XXXXXX1  |
7  | restaurant4 | address1 | XXXXXX1  |

How I want to show it In my page :

 |     name    | address  | Phone_no |

 | restaurant1 | address1 | XXXXXX1  |
 |             | address2 | XXXXXX2  |
 |             | address3 | XXXXXX3  |
 | restaurant2 | address1 | XXXXXX1  |
 |             | address2 | XXXXXX2  |
 | restaurant3 | address1 | XXXXXX1  |
 | restaurant4 | address1 | XXXXXX1  |


Now I want to show it using PHP, with name, their address and phone number, but name should be displayed only once.

this is my PHP code: 

    <?php
    //this is my query.
    $query = "SELECT * FROM details WHERE cat_id = $id GROUP BY name";
    $result = mysqli_query($con,$query);

    while($row = mysqli_fetch_array($result)){ 
    ?>
      <table>
         <tr>
           <td><?php echo cfirst($row['name'] ?></td>
           <td><?php echo row['address'] ?></td>
           <td><?php echo row['phone'] ?></td>
         </tr>
       </table>
   <?php } ?>


The result of this query: 

 |     name    | address  | Phone_no |

 | restaurant1 | address1 | XXXXXX1  |
 | restaurant2 | address1 | XXXXXX1  |
 | restaurant3 | address1 | XXXXXX1  |
 | restaurant4 | address1 | XXXXXX1  |

Upvotes: 0

Views: 3048

Answers (4)

Hamza
Hamza

Reputation: 46

First You have to Remove:

  $query = "SELECT * FROM details WHERE cat_id = $id";

This group by is just removing address of restaurant with multiple address.you have to do it in you code in my view.

    <?php
    $last_name = '';
    while($row = mysqli_fetch_array($result)){ 
    ?>
      <table>
         <tr>
         <?php if ($last_name == ''){?>
           <td><?php echo cfirst($row['name'] ?></td>
          <?php 
                $last_name = $row['name']; 
                } elseif($last_name != $row['name']){
          ?>
           <td><?php echo cfirst($row['name'] ?></td>
          <?php }else{ ?>
           <td></td>
          <?php } ?>
           <td><?php echo row['address'] ?></td>
           <td><?php echo row['phone'] ?></td>
         </tr>
       </table>
   <?php } ?>

This will solve your problem just adjust your html accordingly.

Upvotes: 2

Dylan de St Pern
Dylan de St Pern

Reputation: 469

$result = mysqli_query($con,"SELECT * FROM test where cat_id = $id");

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


$name = $row['name'];
$address = $row['address'];
$phone = $row['phone'];
?>
<doctype! html>
<head>
</head>
<body>
Name: <?php echo $name ?> <br>
Address: <?php echo $address ?><br>
Phone: <?php echo $phone ?><br><br>
</body>
</html> 
<?php
}
?>

Output:

Name: rest1

Address: 23 rose street

Phone: 123125325

Name: rest2

Address: 23 bank street

Phone: 24343532523

Name: rest1

Address: 25 green street

Phone: 53425435432

Name: rest2

Address: 54 rover street

Phone: 6434532

Upvotes: 1

SeanWM
SeanWM

Reputation: 16989

This is what I would do:

  1. Query the table for all restaurants and order by restaurant name
  2. Loop through all records and at the end of each loop, store the $last_restaurant name
  3. In the loop, if the $last_restaurant name is the same as the next restaurant name, echo out just the address and phone
  4. In the loop, if the $last_restaurant name is NOT the same as the next restaurant name echo out the all information

Here's a working example, try to implement it within your code.

Upvotes: 1

markatch
markatch

Reputation: 33

You'll still need to get all the rows from SQL, so I think this is something you could fix on the PHP side of things. So Maybe you have a table that has the Name column spanning multiple rows, so that you have unique addresses, but only show the name once?

$command = "select * from table_name";
$result = mysqli_query($link,$command);
$last = "";
$count = 0;
while($data = mysqli_fetch_assoc($result)){
    if($data['name'] == $last){
        //print table row
        ++$count;
    } else {
        //echo new <TD> cell that has rowspan="$count"
        //print table row
        $count = 0;
    $last = $data['name'];
    }
}

Very rough, didn't test.

Upvotes: 1

Related Questions