CODEWITHSUNDEEP
phpmysql
user2971050
user2971050

Reputation: 15

PHP something wrong with while loop

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.

Upvotes: 0

Views: 83

Answers (2)

Ankur Bhadania
Ankur Bhadania

Reputation: 4148

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/

Upvotes: 1

Vipul Hadiya
Vipul Hadiya

Reputation: 882

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.

Upvotes: 0

Related Questions