SitePoint Sponsor

User Tag List

Results 1 to 8 of 8

Thread: Join query loop

  1. #1
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Earth
    Posts
    739
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Join query loop

    Hi ..

    Im trying to join 2 tables and loop each record with the same orderid into the table .. then move down the the next match etc etc

    What I have below is only outputting 1 record per match .. not all records = orderid 10 then move onto all records = orderid 14 etc etc

    PHP Code:
    <?php 

        $sql 
    mysql_query("SELECT O.*, C.* 
                            FROM ordercontent O 
                            LEFT JOIN cart C ON O.orderid = C.orderid 
                            WHERE C.userid = "
    .$_SESSION['userid'].
                            GROUP BY O.orderid 
                            ORDER BY C.orderid DESC"
    ); 
                         
            while(
    $row mysql_fetch_array($sql)){ 
             
            
    $sub $row['qty'] * $row['subtotal']; 

    ?> 

    <table width="98%" border="1" align="center" cellpadding="0" cellspacing="0" bordercolor="#000000"> 
    <tr> 
      <td width="64%"><center><b><?php echo stripslashes($row['orderdate']); ?>Description</b></center></td> 
      <td width="12%"><div align="right"><b>Price</b></div></td> 
      <td width="12%"><center><b>Qty</b></center></td> 
      <td width="12%"><div align="right"><b>Sub Totals</b></div></td> 
    </tr> 
    <tr> 
      <td><?php echo stripslashes($row['itemdesc']); ?></td> 
      <td><div align="right"><?php echo stripslashes(number_format($row['subtotal'], 2)); ?></div></td> 
      <td><center><?php echo stripslashes($row['qty']); ?></center></td> 
      <td><div align="right"><?php echo number_format($sub2); ?></div></td> 
    </tr> 
    <tr> 
      <td>&nbsp;</td> 
      <td>&nbsp;</td> 
      <td><div align="right"><b>Total</b></div></td> 
      <td><div align="right"><?php echo number_format($row['total'], 2); ?></div></td> 
    </tr> 
    </table><br /> 

    <?php 

    ?>
    How should it be?

    The above is only outputting one record per group then loops to the next group and outputs one record ..

    Thanks

  2. #2
    Sell crazy someplace else markl999's Avatar
    Join Date
    Aug 2003
    Location
    Manchester, UK
    Posts
    4,007
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    GROUP BY O.orderid
    That wil group the results together into one row, just remove that and you'll get all the matching rows.

  3. #3
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Earth
    Posts
    739
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I will use image to explain:

    Here is what the code outputs:



    Here is what I want to output from the results



    The first table displays all records from table "ordercontent" with orderid --> 10

    The second table displays all records from table "ordercontent" with orderid --> 14

    etc etc?


    Cheers

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    you definitely want ORDER BY and not GROUP BY

    by the way, this is a little excessive -- SELECT O.*, C.*

    try listing only the columns you want
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Earth
    Posts
    739
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    I want all the columns ..

    Will 2 querys be needed to list all records with orderid 10 in the 2nd pictire above?

    Cheers

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    no, one query will do the job, but you'll have to format the results with a control break script in php
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Earth
    Posts
    739
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Could I get a bit of an example using control break script?


    Thanks ..

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by _matrix_
    Could I get a bit of an example using control break script?
    i'll move this thread to the php forum for you (i don't do php)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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