PHP MySQL Nested Lists for Customer Data

Question

I have a nested list that will show customers and their phone numbers like this:

Jason

555 111-1111
555 222-2222

Kristen

555 333-3333

John

555 444-4444
555 555-5555
555 656-6666

I'm having trouble adding their emails, though. This code just duplicates the email under each phone number, but I need the phone numbers and emails to be grouped under each customer like:

Jason

555 111-1111
555 222-2222
[email protected]
[email protected]

Kristen

555 333-3333
[email protected]
[email protected]
[email protected]

John

555 444-4444
555 555-5555
555 656-6666
[email protected]

PHP CODE:

<?php
$result = mysqli_query($dbc,"
SELECT 
fname, 
phone, 
email
FROM 
customer, 
phone, 
email, 
customer_phone, 
customer_email
WHERE
customer.id=customer_phone.customer_id
AND
phone.id=customer_phone.phone_id
AND
customer.id=customer_email.customer_id
AND
email.id=customer_email.email_id
order by fname
");

$oldname = null;
while($row = mysqli_fetch_array($result))
{
    if($oldname != $row['fname'])
    {
        echo "<h3>".$row['fname']."</h3>";
        $oldname = $row['fname'];
    }
    echo "<p>".$row['phone']."</p>";
    echo "<p>".$row['email']."</p>";
}
?>

Hi what's the schema of your tables? Is it duplicating the same email for each user right now even if they have multiple emails stored? What's your current output. — Hass
– Hass, Commented Mar 22, 2013 at 3:18
What's the difference between phone and customer phone | email and customer email? — Hass
– Hass, Commented Mar 22, 2013 at 3:19
Sorry, customer_phone and customer_email are junction tables. Yes, it is duplicating the same email and not displaying all the emails for each customer. — Jason Cooper
– Jason Cooper, Commented Mar 22, 2013 at 3:23

Ed Gibbs · Accepted Answer · 2013-03-22 03:42:01Z

1

This query will slot the emails in after the phone numbers, by getting the phone numbers first and the emails second. The column with '1-Phone' and '2-Email' makes sure the emails sort after the phone numbers.

SELECT fname, '1-Phone' AS InfoType, phone AS TheInfo
  FROM Customer
  INNER JOIN phone ON phone.id=customer_phone.phone_id
  INNER JOIN customer_phone ON customer.id=customer_phone.customer_id
UNION SELECT fname, '2-Email', email
  FROM Customer
  INNER JOIN email ON email.id=customer_email.email_id
  INNER JOIN customer_email ON customer.id=customer_email.customer_id
ORDER BY fname, InfoType

I changed the joins to ANSI syntax because it made it easier for me to work on this; no other reason. Feel free to push the join conditions back to the WHERE clause if that's your preference - I don't feel strongly about it :)

The only change to the rendering code is to replace this...

echo "<p>".$row['phone']."</p>";
echo "<p>".$row['email']."</p>";

... with this:

echo "<p>".$row['TheInfo']."</p>";

answered Mar 22, 2013 at 3:42

Ed Gibbs

26.4k4 gold badges50 silver badges75 bronze badges

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

3 Comments

Jason Cooper Over a year ago

Thanks, but yeah, the JOIN and UNION stuff confuses me still. I just recently wrapped my head around the SELECT query for this, so I was hoping to build on that for now.

Ed Gibbs Over a year ago

I understand. The JOIN syntax was hard for me at first but it was the standard for a gig I had years ago. I came to prefer it because it separates the join logic from the filter logic; it's hard to go back :). As for UNION, the first SELECT just gets a bunch of rows, then each successive one just appends more rows. There's a tiny bit more to it, but that's the basics.

Jason Cooper Over a year ago

Cool. Thanks, Grace. I'm going to look more into that and study your example.

Collectives™ on Stack Overflow

PHP MySQL Nested Lists for Customer Data

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related