MySQL how to display data from two tables

Question

I'm trying to display the username of the person who has submitted the most articles but I don't know how to do it using MySQL & PHP, can someone help me?

Here is the MySQL code.

CREATE TABLE users (
user_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
username VARCHAR(255) DEFAULT NULL,
pass CHAR(40) NOT NULL,
PRIMARY KEY (user_id)
);

CREATE TABLE users_articles (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
user_id INT UNSIGNED mNOT NULL,
title TEXT NOT NULL,
acontent LONGTEXT NOT NULL,
PRIMARY KEY (id)
);

Here is the code I have so far.

$mysqli = mysqli_connect("localhost", "root", "", "sitename");
$dbc = mysqli_query($mysqli,"SELECT COUNT(*) as coun, user_id 
                             FROM users_articles 
                             GROUP BY user_id 
                             ORDER BY coun DESC
                             LIMIT 1");

True Soft · Accepted Answer · 2010-05-21 07:00:19Z

1

If you want to get the user's name, you should use the next query:

SELECT users.name, COUNT(users_articles.id) AS coun 
FROM users_articles
LEFT JOIN users_articles ON users.id=users_articles.user_id
GROUP BY users_articles.user_id
ORDER BY coun DESC
LIMIT 1

answered May 21, 2010 at 7:00

True Soft

8,7976 gold badges58 silver badges86 bronze badges

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

1 Comment

OM The Eternity Over a year ago

+1 Now this can give u the USERNAME :) he knows what user id and whats username :)

Tom Bartel · Accepted Answer · 2010-05-21 06:56:23Z

0

select u.user_id, count(ua.id) as num_articles from users u left outer join users_articles ua on u.user_id = ua.user_id group by u.user_id order by num_articles desc

The left outer join (as opposed to an inner join) ensures that all users are represented in the result, no matter if they have a record in users_articles or not.

EDIT: Since you only want the person who has submitted the most articles, you do not necessarily need the left outer join (as long as there is at least one user who has written any articles). For a complete list, it would be useful, however.

answered May 21, 2010 at 6:56

Tom Bartel

2,2581 gold badge15 silver badges18 bronze badges

2 Comments

gew Over a year ago

this wont let me display the username just the number of articles?

Tom Bartel Over a year ago

Come on, just add u.username to the field list. Stackoverflow doesn't relieve you of having to think for yourself.

OM The Eternity · Accepted Answer · 2010-05-21 07:01:39Z

0

Whichever above queries given by geeks u use just DO NOT FORGET TO INCLUDE "username" field in select query as none of them has included the username field

answered May 21, 2010 at 7:01

OM The Eternity

16.2k44 gold badges127 silver badges187 bronze badges

Comments

Dom De Felice · Accepted Answer · 2010-05-21 07:07:19Z

0

What you want to do is a join.

The SQL query you need is this:

SELECT COUNT(*) as coun, users.user_id, username
FROM users_articles
INNER JOIN users
ON users_articles.user_id = users.user_id
GROUP BY user_id
ORDER BY coun DESC
LIMIT 1

I tested this and it works.
The result table contains the number of articles of the user, its user id and its username.

edited May 21, 2010 at 7:07

answered May 21, 2010 at 6:55

Dom De Felice

4764 silver badges10 bronze badges

Comments

Karthik · Accepted Answer · 2010-05-21 07:24:00Z

0

use like this,

SELECT COUNT(users_articles.*) as coun, users_articles.user_id, users.username 
FROM users_articles, users
WHERE users_articles.user_id = users.user_id
GROUP BY users.user_id
ORDER BY coun DESC

answered May 21, 2010 at 7:24

Karthik

3,3415 gold badges31 silver badges38 bronze badges

Comments

aioobe · Accepted Answer · 2010-05-21 09:27:49Z

0

SELECT COUNT(*) as coun, user_id, users.username
FROM users_articles, users
WHERE users_articles.user_id = users.user_id
GROUP BY user_id
ORDER BY coun DESC
LIMIT 1

edited May 21, 2010 at 9:27

answered May 21, 2010 at 6:44

aioobe

423k115 gold badges831 silver badges844 bronze badges

1 Comment

Gunjan Over a year ago

its better to use explicit INNER JOIN syntax. makes it much more readable

Collectives™ on Stack Overflow

MySQL how to display data from two tables

6 Answers 6

1 Comment

2 Comments

Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

2 Comments

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related