MyBB Community Forums

I have a Table XXX, Column post_id that stores posts PIDs?

How can I retrieve user UID only knowing post pid?

Edit: Apparently I had misread your post. If you only need the UID, a simple left join will work.

$query = $db->query("
		SELECT *
		FROM ".TABLE_PREFIX."table t
		LEFT JOIN " . TABLE_PREFIX . "posts p ON p.uid = t.post_id
		WHERE post_id=". (int) $PostID);
while($querydata = $db->fetch_array($query)) {
    echo $querydata['uid']; // echo UID
}   

Haven't tested it, but something like that should do the trick.

there is a uid field in the posts table.

just a simple select on the posts table where pid=<post_id> andr equest the <uid> field.

I have post PIDs and I try to:
Fetch post username
Count POSTs with the same UID

SELECT u.uid, u.username, post_id, COUNT(post_id) AS count
           FROM ".TABLE_PREFIX."post_pids 
           LEFT JOIN ".TABLE_PREFIX."users u ON (post_id=u.uid)
           group by post_id ORDER BY count DESC

(2014-08-21, 10:49 PM)marcus123 Wrote: [ -> ]I have post PIDs and I try to:
Fetch post username
Count POSTs with the same UID

Meant, you have only $some_post_id, and want to know who made this post and how many other posts he made ?

SELECT username, count(pid) as countofposts FROM ".TABLE_PREFIX."posts
WHERE username = (SELECT username FROM ".TABLE_PREFIX."posts WHERE pid=".$some_post_id.")

This will return username, countofposts

But if you meant that you have many PID's in custom table (table name post_pids with column post_id) and want to select each post_id directly from there, then find user who made this post and count his total posts then you could do something like this

SELECT username, post_id, (SELECT count(pid) FROM ".TABLE_PREFIX."posts WHERE username=mp.username) as countofposts
FROM ".TABLE_PREFIX."post_pids cp
LEFT JOIN ".TABLE_PREFIX."posts as mp ON (mp.pid=cp.post_id)

and you will get array with username, post_id, countofposts for each post_id from post_ids table
(if you need uid too, you can simply add it after first select like SELECT uid, username, post_id ect ect)

Thanks very much +1. I made a few changes to your query and works awesome