select query not executing correctly in php

Question

Hi in the below code select query executing but output giving 3 times all the record values. For example where I am passing username as user2 it giving output 45New45New45New46New46New46New47New47New47NewSUCCESSFULLY But In my database it contains only 3 records:

id       47  48 49
groupname:New New New

I am expeting output above one.But I am getting 3 times.

Where I did the mistake.

php

case "DispalyGroupDetails":
        $userId = authenticateUser($db, $username, $password);

        if ($userId != NULL)

        {

            if (isset($_REQUEST['username']))           
            {               
                 $username = $_REQUEST['username'];



                 $sql = "select Id from users where username='$username' limit 1";

                 if ($result = $db->query($sql))

                 {
                        if ($row = $db->fetchObject($result))

                        {    

                                     $sql = "select g.id,g.groupname from `users` u, `friends` f,`group` g 
                                     where u.Id=f.providerId and f.providerId=g.providerId";
                                    $theResult = $db->query($sql);

                                     if ($theResult) {
                        //$theRow = $db->fetchObject($theResult);
                        while( $theRow = $db->fetchObject($theResult))
                        { 
                        echo $theRow->id; 
                        echo $theRow->groupname;
                        }       
                                $out = SUCCESSFUL;
                                } else {
                                        $out = FAILED;
                                }

                        }
                        else
                        {
                            $out = FAILED;                      
                        }
                 }

                 else
                 {
                        $out = FAILED;
                 }              
            }

            else
            {
                    $out = FAILED;
            }           
        }
        else
        {
            $out = FAILED;
        }   
    break;

Just play this : select g.id,g.groupname from `users` u, `friends` f,`group` g where u.Id=f.providerId and f.providerId=g.providerId; You should get more than 3 rows — Bang, Feb 12 '15 at 09:57

score 0 · Answer 1 · answered Feb 12 '15 at 09:49

0

You use echo $theRow->id; , so all the results about this row are print.

answered Feb 12 '15 at 09:49

Thomas Rbt

1,483
1
13
26

score 0 · Accepted Answer · edited May 23 '17 at 11:56

The problem is with your select statement:

SELECT g.id,g.groupname 
FROM users u, friends f, group g 
WHERE u.Id=f.providerId and f.providerId=g.providerId

You are joining three tables, users, friends and groups. This will yield several rows containing groupname and group.id. If you have the possibility, try executing your query in PHPMyAdmin or a similar tool and you will see that the table returned by your query contains more than three rows, each having g.id and g.groupname.

The easiest way to get groupname and id only once is to use GROUP BY:

SELECT g.id,g.groupname 
FROM users u, friends f, group g 
WHERE u.Id=f.providerId and f.providerId=g.providerId
GROUP BY g.id, g.groupname

To read more about how JOINS work, you can read this StackOverflow question.

select query not executing correctly in php

2 Answers2