PHP something wrong with while loop

Question

Okay so now its display results like 3 times in a row

$user_apps = mysql_query("SELECT a.name,a.download_url FROM user_apps as ua LEFT JOIN 
apps as a ON  (ua.app_id=a.app_id)  
WHERE ua.user_id='$user_id'") or die(mysql_error());
while($raw = mysql_fetch_array($user_apps)){
    $name = $raw['name'];
    $url = $raw['download_url'];
    echo $name;
    echo "<br />";
    echo $url;
}

Database Table Structure(since I am new to the site and did not know how to display the table structure I just exported the sql)

CREATE TABLE IF NOT EXISTS `users` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `username` varchar(255) NOT NULL,
 `password` varchar(255) NOT NULL,
 PRIMARY KEY (`id`),
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

CREATE TABLE IF NOT EXISTS `user_apps` (
 `user_id` int(11) NOT NULL,
 `app_id` int(11) NOT NULL,
 KEY `user_id` (`user_id`,`app_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `apps` (
 `app_id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(255) NOT NULL,
 `description` text NOT NULL,
 `icon` varchar(255) NOT NULL,
 `download_url` varchar(255) NOT NULL,
 `default` int(20) NOT NULL DEFAULT '0',
 PRIMARY KEY (`app_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

I'v tried different Join types but that does not seem to work.

you are overwriting $app_id on each loop. you need to put your 2nd query inside your 1st loop. Or just learn how to do a JOIN query, and prevent doing a loop in a loop. — Sean
– Sean, Commented Dec 23, 2014 at 5:36

Ankur Bhadania · Accepted Answer · 2014-12-23 06:59:35Z

1

Used the join query for get the result check bellow example query

$user_apps = mysql_query("SELECT DISTINCT a.name,a.download_url FROM user_apps as ua LEFT JOIN apps as a ON (ua.app_id=a.app_id)  WHERE ua.user_id='$user_id'") or die(mysql_error());
while($raw = mysql_fetch_array($user_apps)){
$name = $raw['name'];
$url = $raw['download_url'];
echo $name;
echo $url;
}

change the join type as per your requirement. the above query for only example

INNER JOIN: Returns all rows when there is at least one match in BOTH tables
LEFT JOIN: Return all rows from the left table, and the matched rows from the right table
RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table
FULL JOIN: Return all rows when there is a match in ONE of the tables

more about join click here AND also check this http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/

edited Dec 23, 2014 at 6:59

answered Dec 23, 2014 at 5:45

Ankur Bhadania

4,1681 gold badge25 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

user2971050 Over a year ago

Here is what it says:

user2971050 Over a year ago

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '==a.app_id) WHERE ua.user_id='7'' at line 1

user2971050 Over a year ago

thanks it display the results now BUT its like duplicating the results for some reason.

Ankur Bhadania Over a year ago

it's depend on the join type. read more about join type and used join type as per your requirement

user2971050 Over a year ago

so I took a look a the link you provided about Join and tried the different joins but none of them seemed to take away the duplicate results

|

Vipul Hadiya · Accepted Answer · 2014-12-23 05:41:31Z

0

You have used single quotes in query at user_id='$user_id' . Are you sure your user_id is char, varchar or text? Just print_r($user_apps) and check it has any records or not? If user_id is int,tinyin than remove single quote.

answered Dec 23, 2014 at 5:41

Vipul Hadiya

8831 gold badge11 silver badges23 bronze badges

Collectives™ on Stack Overflow

PHP something wrong with while loop

2 Answers 2

10 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related