Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2006
    Posts
    11

    Unanswered: Doubled rows from SELECT statement??

    My SELECT statement looks something like this

    Code:
    Select * From Table1 a, Table2 b, Table3 c Where a.ID=b.ID And a.Title=c.Title Order By a.Date
    Well it does returned what I need, but it does affected 4 rows(assume suppose two rows will be return), and every single row it doubled it, what is the problem with the statement, it seems to be ok..Thanks.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the sql statement is okay

    your data is causing the duplication

    alternatively your application script could have a loop
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2006
    Posts
    12
    Are you using php and mysql_fetch_array to run this query?

  4. #4
    Join Date
    Oct 2006
    Posts
    11
    After running the mysql_query($query), then I use while($rows=mysql_fetch_assoc($result)) to fetch and loop my result..

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ....can we see the full block of code you are using....
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Oct 2006
    Posts
    11
    Actually I'm doing some basic version of a forum, just simple function of forum, here is the portion of my codes that generate doubled rows for me..
    PHP Code:
    <?php

        $conn 
    mysql_connect('localhost','Forumer','Forumer')or die("Can't connect EX006");
        
    $select mysql_select_db('Forumer',$conn)or die("Can't select database");
        
    $query "Select * From Threads t, Users u, Posts p Where t.tID=" .$_GET['tid']. " And p.pUserID=u.userID";
        
    $result mysql_query($query);
        
    $rows mysql_fetch_assoc($result);
        if(
    mysql_affected_rows($conn)>0){
            while(
    $rows mysql_fetch_assoc($result)){
                echo 
    "<tr><td>" .$rows['userID']. "<br/>" .$rows['userName']. "<br/>" .$rows['pPostDate']. "</td><td>" .$rows['pContent']. "</td></tr>";
            }
        }
    ?>

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    see, there's your problem

    the query you posted in post #1 has no real problem --

    Select *
    From Table1 a, Table2 b, Table3 c
    Where a.ID=b.ID
    And a.Title=c.Title
    Order By a.Date

    however, it does not look like the query you're actually running --

    Select *
    From Threads t, Users u, Posts p
    Where t.tID=" .$_GET['tid']. "
    And p.pUserID=u.userID

    see if you can spot the difference

    HINT: your joins are incomplete!!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Oct 2006
    Posts
    11

    Wink

    Oops, I made some mistake, I copied the wrong codes, but r937 you did pointed out my mistake from another codes, thanks... The code that I wanted to paste here originally was:

    PHP Code:
    <?php
        $conn 
    mysql_connect('localhost','Forumer','Forumer')or die("Can't connect EX004");
        
    $select mysql_select_db('Forumer',$conn)or die("Can't select database");
        
    $query "Select * From Threads t, Users u, Forums f where t.tForumsID='" .$_GET['fid']. "' And t.tUserID=u.userID Order By t.tReplyDate";
        
    $result mysql_query($query);
        if(
    mysql_affected_rows($conn)>0){
            while(
    $rows mysql_fetch_assoc($result)){
                echo 
    "<tr><td><a href='threads.php?tid=" .$rows['tID']. "&pg=1'>" .$rows['tTitle']. "</a></td><td>" .$rows['tAuthor']. "</td><td>" .$rows['tReply']. "</td><td>" .$rows['tView']. "</td><td>" .$rows['tReplyDate']. " by " .$rows['userName']. "</td></tr>";
            }
        }else{
            echo 
    "<tr><td>No threads. <a href='threads.php?action=create&fid=" .$_GET['fid']. "'>Post new thread.</a></td></tr>";
        }
        
    mysql_free_result($result);
        
    mysql_close($conn);
    ?>
    And I found my solution after read what r937's HINT, is my Order By, so I change the Order By t.tReplyDate to DESC and add in Group By t.tTitle, everything works as what I wanted to, but why I need to use Group By so that the rows fetched won't be duplicated? What cause it to be duplicated. Thanks for the helps.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    this is still wrong --

    Select *
    From Threads t, Users u, Forums f
    where t.tForumsID='" .$_GET['fid']. "'
    And t.tUserID=u.userID
    Order By t.tReplyDate

    you are joining to all forums

    here is another suggestion -- learn how to write your queries using JOIN syntax

    then your query will look like this --
    Code:
    select list your columns
      from Threads t
    inner
      join Users u
        on u.userID = t.tUserID 
    inner
      join Forums f
        on f.???? = ????.???? 
     where t.tForumsID = $_GET['fid']
    Last edited by r937; 11-03-06 at 10:29.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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