Displaying one and many values

[thatsme2]

Hello,

 

  I have a table (members_table) and another table (purchased_table).  Members contains fields (id, fname, lname, address).  purchased_table has fields (purchased_id, member_id, purchased_item_id).  To display i am using a join statement,

SELECT * FROM members_table m INNER JOIN purchased_table p ON m.member_id=p.member_id WHERE m.member_id=10;

 

The above query returns member_details as many items the member has brought items. I want to display the member details only once and purchased_items as many items as the member has brought.

 

to display iam currently using,

 

while($r = mysql_fetch_assoc($q))

{

  $fname = $r[fname];

  $lname = $r[lname];

  $r[purchased_item_id] = $r[purchased_item_id];

}

 

 

Thanks

[MadTechie]

I want to display the member details only once and purchased_items as many items as the member has brought.

 

Erm.. So if you have 2 members each have 6 items you want.. how many rows ?

12 but 2 ???

your asking to have 1 table with different records set!

 

[thatsme2]

12 rows right.  How to display the member names only once and the items the number of items they bought?

I want to display like this,

 

  member_name1

  item1

  item2

  item3

 

  member_name2

  item1

  item2

  item3

  item4

 

[mkoga]

It might be simpler to query for your members, then run another query for their items.

[Barand]

Method A

<?php 
$sql = "SELECT m.id, m.fname, m.lname, p.purchased_item_id
        FROM members m
            INNER JOIN purchased_table p ON m.id = p.member_id
        ORDER BY m.id";
$res = mysql_query($sql) or die (mysql_error()."<pre>$sql</pre>");
$previd = '';
while (list($id, $fn, $ln, $itm) = mysql_fetch_row($res))                                       // edit: % corrected
{
    if ($previd != $id)                           // is a new member id
    {
        echo "<h3>$fn $ln</h3>";                  // then output it
        $previd = $id;
    }
    echo "$itm<br/>";
}
?>

 

Method B

<?php 
$sql = "SELECT m.id, m.fname, m.lname, GROUP_CONCAT(p.purchased_item_id SEPARATOR '<br/>')
        FROM members m
            INNER JOIN purchased_table p ON m.id = p.member_id
        GROUP BY m.id";
$res = mysql_query($sql) or die (mysql_error()."<pre>$sql</pre>");

while (list($id, $fn, $ln, $items) = mysql_fetch_row($res))                                       // edit: % corrected
{
        echo "<h3>$fn $ln</h3>";

        echo "$items";
}

?>

[MadTechie]

slight typo on Barand post %res should be $res

[Barand]

Good eyes, MT.

 

Thats the trouble with Mysql-related code - you have to set up the tables to run and test it

[MadTechie]

Yeah know that feeling...

worst still is when they reply without any detail like..

that didn't work

 

PS Method B very cool, i would normally use Method A

[Barand]

"B" is especially useful when you want them side-by-side in an HTML table

 

<?php
echo "<tr><td>$fn $ln</td><td>$items</td></tr>";
?>