Building SQL request for JSON API

Question

I have got a website built on wordpress and would like to create a SQL query for displaying users points by their username.

Points in the table linked to users IDs and not to usernames. My SQL query is:

SELECT ID 
FROM wp_users 
WHERE user_login='{username}'

SELECT points 
FROM wp_cp 
WHERE uid=ID

Which is wrong. Separately they work fine but how can I make only one SQL query?

The logic should be by typing username - SQL gets its ID from wp_users and automatically points number from wp_cp by matching users ID.

Can anyone please help me with this problem? Thank you.

Melon · Accepted Answer · 2014-01-09 10:50:35Z

1

here you hav a litle hint,

SELECT points 
    FROM wp_cp join wp_users 
    on wp_cp.uid=wp_users.ID 
    where wp_users.user_login ='username'

DISCLAIMER: I DIDNT TEST IT, this is just a hint!

answered Jan 9, 2014 at 10:25

Melon

8838 silver badges17 bronze badges

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

2 Comments

Thank you Melon. It is very close but still not working. ;-( Maybe because wp_cp has got column uid and wp_users has got column ID

try it now, it just missed the tablenames on colums. you can finish it from there.