Newbie trying to solve a MySQL query logic issue

Question

I'm going to try to explain this as simply as possible. Any help would be greatly appreciated as I'm not very good with designing queries.

I have 3 tables: (I have cut down the unnecessary columns in the users table for this question)

USERS:
id,    username,    password

PENDINGREVIEW:
id,    articleid,    requestid,    content,    writerid,    datewritten

ARTICLES:
id,    title,    length,    requestedby,    content,    writtenby,    daterequested,    datecompleted

So say I have review.php. On review.php I want it to show all the articles that are in the pendingreview table for a specific user. So something like this:

SELECT * FROM pendingreview WHERE requestid = $userid (where $userid = the id of the user)

What I actually want to display on review.php though is a list of articles from the articles table and how many articles are in the pendingreview table that correspond to each of those articles. The list must only be made up of articles that the user has requested.

So I want review.php to look like this:

Your "How to build a bike" article request has 3 articles pending review.
Your "How to mow the lawn" article request has 12 articles pending review.

Any help on how I would do this would be appreciated!

I don't any question marks. Please try something first. And read [faq] — Mihai Iorga, Mar 19 '13 at 08:04
@KriiV Your example with `SELECT ...` is heading in a good direction. You'll want to `JOIN` your tables: [mysql-manual] (http://dev.mysql.com/doc/refman/5.1/en/join.html) and use `count` on the id of your articles... — michi, Mar 19 '13 at 08:12
You don't need anyone to give you a query. You need to either learn MySql or get someone to work on your project. After this query you'll need another and another... — IROEGBU, Mar 19 '13 at 08:16

John Woo · Accepted Answer · 2013-03-19T08:17:47.550

3

SELECT  a.title, COUNT(*) TotalPending
FROM    Articles a
        INNER JOIN PendingReview b  
            ON a.ID = b.articleID
WHERE   b.RequestID = 'ID HERE'
GROUP   BY a.title

To further gain more knowledge about joins, kindly visit the link below:

Visual Representation of SQL Joins

edited Mar 19 '13 at 08:17

answered Mar 19 '13 at 08:10

John Woo

258,903
69
498
492

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'a.ID = b.articleID WHERE b.RequestID = '3' GROUP BY a.title LIMIT 0, 30' at line 4 – KriiV Mar 19 '13 at 08:12
Thank you so much JW. Itachi, I have tried, I find it all very confusing as to where I should start. – KriiV Mar 19 '13 at 08:21

score 1 · Answer 2 · edited May 23 '17 at 10:32

1

This may help you to build your requests:

MySql Join three tables

http://www.techotopia.com/index.php/Joining_Tables_in_MySQL

edited May 23 '17 at 10:32

Community

1
1

answered Mar 19 '13 at 08:13

Acuao

671
4
15

Newbie trying to solve a MySQL query logic issue

2 Answers2