CODEWITHSUNDEEP
phpmysqlsubquery
Aloysia de Argenteuil
Aloysia de Argenteuil

Reputation: 892

How to display results of a query (with subqueries) where one of the columns can have more than one info using mysql and php?

I'm running following query and displaying the results with php:

<?php
    $con=mysqli_connect("host","user","pass","db");
    // Check connection
    if (mysqli_connect_errno())
        {
            echo "Failed to connect to MySQL: " . mysqli_connect_error();
        }

$query = "select 
    p.id,
    p.date,
    (select 
        t.title
    from
        table02 t
    where
        p.family = t.family) title,
    (select 
        a.author
    from
        table03 a
    where
        p.family = a.family) author,
    (select 
        n.note
    from
        table04 n
    where
        p.family = n.family) note,
from
    table01 p
where
    p.family in (48766 , 276197, 265242, 334879)";

$result = mysqli_query($con,$query);
    echo "<table border='1'><tr><th>ID</th><th>Date</th><th>Title</th><th>Author</th><th>Note</th></tr>";

while($row = mysqli_fetch_assoc($result))
    {
        echo "<tr>";
        echo "<td>" . $row['id'] . "</td>";
        echo "<td>" . $row['date'] . "</td>";
        echo "<td>" . $row['title'] . "</td>";
        echo "<td>" . $row['author'] . "</td>";
        echo "<td>" . $row['note'] . "</td>";
        echo "</tr>";
    }
    echo "</table>";
mysqli_close($con);
?>

So we would have a table like that:

ID - DATE ---------- TITLE -------------------------- AUTHOR --- NOTE

01 - 07/01/2013 - Thank you for your help - Some Guy - 8.3

07 - 07/03/2013 - You are welcome - Mr. Nice - 7.6

11 - 09/27/2013 - I hope you enjoy us - J. Growth - 8.9

etc.

This works fine if I find only one result for each column.

But the problem is that we can have more than one author for some "songs" as we will see below (when ID = 13).

So when we run the query we receive a message that author in table03 has more than one value and nothing is displayed at all.

How could I manage to have a table that gives me the results of this column with multiple authors too?

ID - DATE ---------- TITLE -------------------------- AUTHOR --- NOTE

01 - 07/01/2013 - Thank you for your help - Some Guy - 8.3

07 - 07/03/2013 - You are welcome - Mr. Nice - 7.6

11 - 09/27/2013 - I hope you enjoy us - J. Growth - 8.9

13 - 11/14/2013 - Houston, we have a problem - B. Lee & T. Hanks - 6.4

17 - 12/09/2013 - Now we have only one - P. Neuer - 7.1

Many thanks!

Upvotes: 0

Views: 79

Answers (2)

Javier Valencia
Javier Valencia

Reputation: 695

Use LIMIT (and ORDER BY optionally) clausule in subqueries or GROUP_CONCAT() grouping function in subqueries too.

LIMIT => https://dev.mysql.com/doc/refman/5.5/en/select.html

GROUP_CONCAT => http://dev.mysql.com/doc/refman/5.5/en/group-by-functions.html#function_group-concat

Upvotes: 1

Aiias
Aiias

Reputation: 4748

One option is to use MySQL's GROUP_CONCAT() in your author subquery.

For your case, your subquery might look like:

(select 
    GROUP_CONCAT(a.author)
from
    table03 a
where
    p.family = a.family) authors

Note:

The default separator between values in a group is comma (“,”)

Upvotes: 1

Related Questions