Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2012
    Posts
    2

    Unanswered: How to group results from my DB

    I need your help please. I am trying a little project in PHP and I am Stuck in
    this . You help is much appreciate.
    I have two tables
    1- members and
    2 - transactions
    table members has 5 fileds with the following data
    No title Fname Mname Surname
    99 Mr Jack Amanuel Smith
    602 Mr David Junior Hill
    601 Ms Alberta Jackson Geoff
    table transactions has
    No Recno RecDate RecAmnt Reason
    99 1400 10/10/06 $40 Fee to 31/10/2006
    99 1500 20/10/07 $40 Fee to 31/10/2007
    99 1600 11/10/08 $40 Fee to 31/10/2008
    60 1700 06/04/06 $40 Fee to 31/10/2006
    60 1800 03/2/07 $40 Fee to 31/10/2007
    60 3000 02/09/06 $40 Fee to 31/10/2006
    601 3100 20/10/07 $40 Fee to 31/10/2007
    601 3200 13/6/08 $40 Fee to 31/10/2008
    601 3300 05/6/09 $40 Fee to 31/10/2009
    601 3400 05/6/09 $40 Fee to 31/10/2007
    However when I applied the following PHP Code
    <?php
    include('connect-db.php');
    mysql_connect("localhost", "username", "") or die(mysql_error());
    mysql_select_db("mydba") or die(mysql_error());
    $data = mysql_query("SELECT * FROM members, transactions
    WHERE members.no = transactions.no
    And members.no Group by members.no ") or die(mysql_error());
    echo "<table border='1'>
    <tr>
    <th>Number</th>
    <th>Title</th>
    <th>First Name</th>
    <th>Midd Name</th>
    <th>surname</th>
    <th>RecNo</th>
    <th>Amount </th>
    <th>Reason</th>
    </tr>";
    while($info = mysql_fetch_array( $data ))
    {
    echo "<tr>";
    echo "<td>" . $info['no'] . "</td>";
    echo "<td>" . $info['Title'] . "</td>";
    echo "<td>" . $info['Fname'] . "</td>";
    echo "<td>" . $info['Mname'] . "</td>";
    echo "<td>" . $info['Surname']."</td>";
    echo "<td>" . $info['RecNo']. "</td>";
    echo "<td>" . $info['RecAmnt']."</td>";
    echo "<td>" . $info['Reason']."</td>";
    echo "</tr>";
    }
    echo "</table>";
    ?>
    i get repeated member id results like this
    No Title FName surname Rec No Amnt Paid Reason
    99 Mr Jack Smith 1400 $40 Fee to 31/10/2006
    99 Mr Jack Smith 1500 $40 Fee to 31/10/2007
    99 Mr Jack Smith 1600 $40 Fee to 31/10/2008
    60 Mr David Hill 1700 $40 Fee to 31/10/2006
    60 Mr David Hill 1800 $40 Fee to 31/10/2007
    60 Mr David Hill 3000 $40 Fee to 31/10/2006
    601 Ms Glenda Winston 3100 $40 Fee to 31/10/2007
    601 Ms Glenda Winston 3200 $40 Fee to 31/10/2008
    601 Ms Glenda Winston 3300 $40 Fee to 31/10/2009
    601 Ms Glenda Winston 3400 $40 Fee to 31/10/2007

    what I need is grouped by member id like:

    mem no 99 Jack Smith
    Rec No Amnt Paid Reason
    1400 $40 Fee to 31/10/2006
    1500 $40 Fee to 31/10/2007
    1600 $40 Fee to 31/10/2008

  2. #2
    Join Date
    Jul 2012
    Posts
    5
    I think you're confused about what GROUP BY will do. Try this - it's untested, but it'll probably work.

    Code:
    <?php
    
    $sql = "SELECT
    	members.No AS member_number,
    	members.Title,
    	members.Fname,
    	members.Mname,
    	members.Surname,
    	transactions.No AS transaction_member_number,
    	transactions.Recno,
    	transactions.RecDate,
    	transactions.RecAmnt,
    	transactions.Reason
    	FROM members
    	INNER JOIN transactions ON members.No = transactions.No
    	ORDER BY transactions.No";
    
    include('connect-db.php'); 
    mysql_connect("localhost", "username", "") or die(mysql_error()); 
    mysql_select_db("mydba") or die(mysql_error()); 
    $data = mysql_query($sql) or die(mysql_error());
    
    echo "<table border='1'>
    <tr>
    <th>Number</th>
    <th>Title</th>
    <th>First Name</th>
    <th>Midd Name</th>
    <th>surname</th>
    <th>RecNo</th>
    <th>Amount </th>
    <th>Reason</th>
    </tr>";
    
    $current_transaction_member_number = 0;
    
    while($info = mysql_fetch_array( $data )){
    
    	$this_transaction_member_number = (int) $info['transaction_member_number'];
    	if ( $this_transaction_member_number != $current_transaction_member_number ){
    		echo "<tr>";
    		echo "<td colspan="8">Member number " . $info['member_number'] . ": " . $info['Fname'] . " " . $info['Surname'] . "</td>";
    		echo "</tr>";
    		$current_transaction_member_number = $this_transaction_member_number;
    	}
    
    	echo "<tr>";
    	echo "<td>" . $info['transaction_member_number'] . "</td>";
    	echo "<td>" . $info['Title'] . "</td>";
    	echo "<td>" . $info['Fname'] . "</td>";
    	echo "<td>" . $info['Mname'] . "</td>";
    	echo "<td>" . $info['Surname']."</td>";
    	echo "<td>" . $info['RecNo']. "</td>";
    	echo "<td>" . $info['RecAmnt']."</td>";
    	echo "<td>" . $info['Reason']."</td>";
    	echo "</tr>";
    
    }
    
    echo "</table>";
    ?>

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    there's nowt wrong with the SQL, the problem is how you are handling the data having retrieved it
    if you want to go down the table route, whi9ch is fine, then you need to have anoither layer of processign that handles changes in members
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Sep 2012
    Posts
    2
    thanks for the reply , appreciated... I will try that

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •