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 NO开发者_Python百科T 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");

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

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.

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

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.

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

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