MySQL SQL Query I can not slove it
Hi all members
I have script to diaplay news that users submit it , this sql code to diapsy all news for all users
Code:
select * from Users,News where Users.UsersID=News.NewsID
I write above SQL to display all username and under every username I show the news that this user added
my problem how to display 3 news only for every user
i try this code
Code:
select * from Users,News where Users.UsersID=News.NewsID limit 3
but it's break after the first user, please help me to create this sql
Re: MySQL SQL Query I can not slove it
You need to use a JOIN.
Code:
SELECT * FROM Users LEFT JOIN News WHERE Users.UsersID = News.NewsID LIMIT 3;
Re: MySQL SQL Query I can not slove it
Quote:
Originally Posted by dclamp
You need to use a JOIN.
Code:
SELECT * FROM Users LEFT JOIN News WHERE Users.UsersID = News.NewsID LIMIT 3;
The comma between the table names is equivalent to "inner join".
Re: MySQL SQL Query I can not slove it
this also dispaly 3 news only for the first users in the table like:
username1
news1
news2
news3
but I need to display all user and 3 news under every user like:
username1
news1
news2
news3
username2
news1
news2
news3
username3
news1
news2
news3
username4
news1
news2
news3
Re: MySQL SQL Query I can not slove it
You are going to need 2 queries then.
Here is an example, you should change it to meet your needs.
PHP Code:
$sql = "SELECT * FROM `users` LIMIT 4 ORDER BY userID ASC";
$query = mysql_query($sql);
while ($usrs = mysql_fetch_array($query)) {
$userID = $usrs['userID'];
$sql2 = "SELECT * FROM `news` WHERE userID = '$userID' LIMIT 3 ORDER BY newsID DESC";
$query2 = mysql_query($sql2);
echo "USERID: " . $userID . "NEWS";
while($news = mysql_fetch_array($query2)) {
echo $news['Title'] . ": " . $news['body'] . "<br>";
}
echo "<br><br>";
}
Re: MySQL SQL Query I can not slove it
I want to do this in one SQL becuase SQL inside SQL make server load very high that because there are many visitors on this page,so please to help me to do this in one SQL
Re: MySQL SQL Query I can not slove it
Do you already have the 4 usernames? Are they always the same? If so, you can take dclamp's first query and do something like this:
PHP Code:
$sql2 = "(SELECT * FROM `news` WHERE userID = '$userID1' LIMIT 3 ORDER BY newsID DESC) UNION (SELECT * FROM `news` WHERE userID = '$userID2' LIMIT 3 ORDER BY newsID DESC) UNION (SELECT * FROM `news` WHERE userID = '$userID3' LIMIT 3 ORDER BY newsID DESC) UNION (SELECT * FROM `news` WHERE userID = '$userID4' LIMIT 3 ORDER BY newsID DESC)";
Although this isn't necessarily the best way to do it (or the easiest to extend/change).