SQLite SELECT query in php only retrieves first row of database and combines numbers in wrong manner

Question

I am trying to build an HTML table from the database entries.

But for some reason the table fails, only the first element gets taken from the table called Contacts and all numbers to fill the first row from Phones table.

table contacts

id    firstName lastName
1     sdf       sdf
2     lala      yaya
3     hgj       kj

table phones

contact_id  number
1           34
2           654
3           345
3           455

retrieved results

firstName   lastName    number  number  number
hgj         kj          34      654     345

I am a little afraid about this query

SELECT contacts.firstName, contacts.lastName, group_concat(phones.number) as number 
FROM contacts LEFT JOIN phones WHERE contacts.id = phones.contact_id;

as I believe this is wrong then and only retrieves one row.

<?php
try
{
//open the database
    $db = new PDO('sqlite:db1.sqlite');

//now output the data to a simple HTML table...
    echo '<table id="kontaktid"><thread>';
    echo '<tr><th>Eesnimi</th><th>Perekonnanimi</th><th>Telefon</th><th>Telefon</th><th>Telefon</th></tr></thread><tbord>';
    $result = $db->query("SELECT contacts.firstName, contacts.lastName, group_concat(phones.number) as number 
FROM contacts LEFT JOIN phones WHERE contacts.id = phones.contact_id;")->fetchAll();
    foreach($result as $row)
    {
        $numbers = explode(',', trim($row['number']));
        list($first, $second, $third) = $numbers;
        echo $row['firstName'];
        echo "<tr><td>".$row['firstName']."</td>";
        echo "<td>".$row['lastName']."</td>";
        echo "<td>".$first."</td>";
        echo "<td>".$second."</td>";
        echo "<td>".$third."</td>";
        echo "</tr>";
    }

    echo "</tbody></table>";
}
catch(PDOException $e)
{
    print 'Exception : ' .$e->getMessage();
}
$db = NULL;

forpas · Accepted Answer · 2019-04-16 06:11:09Z

1

You must use group by and group_concat() on the results:

select 
  c.firstname, c.lastname,
  group_concat(p.number) number
from contacts c inner join phones p
on p.contact_id = c.id
group by c.id, c.firstname, c.lastname

See the demo.
Results:

| firstname | lastname | number  |
| --------- | -------- | ------- |
| sdf       | sdf      | 34      |
| lala      | yaya     | 654     |
| hgj       | kj       | 345,455 |

answered Apr 16, 2019 at 6:11

forpas

165k10 gold badges51 silver badges85 bronze badges

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

Collectives™ on Stack Overflow

SQLite SELECT query in php only retrieves first row of database and combines numbers in wrong manner

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related