CODEWITHSUNDEEP
phpmysql
gew
gew

Reputation: 23

MySQL how to display data from two tables

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");

Upvotes: 2

Views: 1560

Answers (6)

aioobe
aioobe

Reputation: 420931

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

Upvotes: 0

Karthik
Karthik

Reputation: 3271

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

Upvotes: 0

Dom De Felice
Dom De Felice

Reputation: 476

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.

Upvotes: 0

OM The Eternity
OM The Eternity

Reputation: 16194

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

Upvotes: 0

True Soft
True Soft

Reputation: 8786

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

Upvotes: 1

Tom Bartel
Tom Bartel

Reputation: 2258

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.

Upvotes: 0

Related Questions