php - MYSQL如果某个字段返回0则选择另一个查询并连接

Question

大家好，我有这些 table ；

USERS( user_id ,fullname ,username etc.)

POSTS ( post_id, user_id, post, orig_post_id, replyto date)

USER_PROFILE (id, user_id, profile_image_path etc)

示例

USERS (1 ,John Doe ,johndoe etc.),( 2 ,Stack Flow ,stackflow etc.)

POSTS (2, 1, My naame is John doe and i approve this message, 0, 0,sometimestamp),
      (3, 12, My naame is Stack Flow and i approve this message, 0, 2,sometimestamp)

USER_PROFILE (1, 1, ppdjodjf.jpg etc),(2, 2, grsdjodjf.jpg etc)

基本上，如果回复字段为 0，我希望查询输出此内容

array('post_id' => 2, 
'user_id' => 1, 
'post' => the post, 
'orig_post_id,' => 0
'replyto,' => 0
username => johndoe,
fullname => John Doe,
profile_image_path => etc)

当它不为零时

array('post_id' => 3, 
'user_id' => 2, 
'post' => Another post, 
'orig_post_id,' => 0
'replyto,' => 2
username => stackflow,
fullname => Stack Flow,
profile_image_path => etc,
'replies' => array(all the replies)

Answer 1

这些是 SQL 的基础知识。你确实应该学习一些 SQL。简单易学，花半个小时就可以学会，非常受益。

首先，给定用户的帖子将是:

select posts.*
from posts
where posts.user_id = '$user_id'

要获取所需的用户字段，请执行联接

select posts.*,users.username,users.fullname
from posts
  inner join users where posts.user_id = users.user_id
where posts.user_id = '$user_id'

您应该能够弄清楚如何加入 user_profile 来获取这些字段。

要仅过滤那些没有 orig_post_id 的记录，您可能需要测试零，或者您可能需要测试 NULL。也许两者都是，所以假设您想测试两者:

where posts.user_id = '$user_id'
  and (orig_post_id = 0 or orig_post_id is null)