Doubled rows from SELECT statement??

Stephensaw · #1 (**permalink**) 11-01-06, 03:53

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.

r937 · #2 (**permalink**) 11-01-06, 07:22

the sql statement is okay

your data is causing the duplication

alternatively your application script could have a loop

ironCode · #3 (**permalink**) 11-01-06, 16:31

Are you using php and mysql_fetch_array to run this query?

Stephensaw · #4 (**permalink**) 11-01-06, 23:39

After running the mysql_query($query), then I use while($rows=mysql_fetch_assoc($result)) to fetch and loop my result..

healdem · #5 (**permalink**) 11-02-06, 03:58

....can we see the full block of code you are using....

Stephensaw · #6 (**permalink**) 11-02-06, 19:51

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>";

        }

    }

?>

r937 · #7 (**permalink**) 11-02-06, 19:57

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!!!

Stephensaw · #8 (**permalink**) 11-03-06, 09:18

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.

r937 · #9 (**permalink**) 11-03-06, 09:25

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']

Internet Services

Web Development

Digital Marketing

Consumer Tech