CODEWITHSUNDEEP
phpmysqldatabaseforeach
ShiggyDooDah
ShiggyDooDah

Reputation: 497

MYSQL and PHP loop and grab just one item from a joint table?

I am currently building a custom CMS to help me learn PHP and MYSQL. I have two database tables 'users' and 'articles'. When the user submits an article, the field 'author_id' in the 'articles' table places to the users 'users_id' from the 'users' table. This way i can join the tables and get all the articles from that user. Now i am trying to make a feature section on the home page. I want to loop through all authors/users and get one article from that user. Here is a sample of my code...

$author= db::getInstance()->query("SELECT * FROM users, articles WHERE user_id = author_id"); 

   foreach($author->results() as $author) {
      echo $author->profile_img;
      echo $author->user_name
      echo $author->article_title; 
   }

This works fine if the user has only posted one article but if there are more than 1 then it will loop through all the posts of that user. I just want to echo 1 article from each user but not sure how i can achieve this. Can anyone point me in the right direction?

Many Thanks,

Louis Lombardi

Upvotes: 1

Views: 68

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

If you want one arbitrary article for each user, you can use a MySQL (mis)feature that allows you to do "partial" aggregations:

SELECT u.*, a.*
FROM users u JOIN
     articles a
     ON u.user_id = a.author_id
GROUP BY u.user_id;

My preferred method for a random article would be more like this:

select u.*, a.*
from (select u.*,
             (select a.article_id
              from articles a
              where a.author_id = u.user_id
              order by rand()
              limit 1
             ) as article_id
      from users u
     ) u join
     articles a
     on u.article_id = a.article_id;

Finally, my preferred method for getting the first/last would look something like this:

SELECT u.*, a.*
FROM users u JOIN
     articles a
     ON u.user_id = a.author_id
WHERE a.article_id = (SELECT MAX(a2.article_id)
                      FROM articles a2
                      WHERE a2.author_id = u.author_id
                     );

Upvotes: 2

Fakhruddin Ujjainwala
Fakhruddin Ujjainwala

Reputation: 2553

You can use MySql limit:

SELECT * FROM users, articles WHERE user_id = '$author_id' limit 0,1

Complete documentation: https://dev.mysql.com/doc/refman/5.5/en/limit-optimization.html

Upvotes: 1

Related Questions