CODEWITHSUNDEEP
phpmysqlsql
Saff
Saff

Reputation: 563

MYSQL if a field return 0 then select another query and join

Hello guys i have these tables;

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)

Examples

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)

Basically, i want the query to output this if the replyto field is 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)

And when it's not zero 

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)

Upvotes: 0

Views: 313

Answers (2)

Saff
Saff

Reputation: 563

After spending the whole day on this, My code ended up looking like this.

  public function get_connected_post()
    {
        $user_id = $this->session->userdata('user_id');

        $sql = "SELECT  p.*,up.fullname,u.username,upi.file_path_thumb,IF(hi5c.hi5_count is NULL,'0',hi5c.hi5_count) AS count_hi5,
                        IF(brn.branch_count is NULL,'0',brn.branch_count) AS count_branch, IF(rply.reply_count is NULL,'0',rply.reply_count) AS count_reply,
                        IF((SELECT COUNT(*) FROM post_highfives ph WHERE ph.user_id = $user_id AND ph.post_id = p.post_id),'1','0') AS count_is_hi5ed,
                        IF((SELECT COUNT(*) FROM posts pt WHERE pt.user_id = $user_id AND pt.is_branch_of_id = p.post_id),'1','0') AS count_is_branched
                FROM    (
                         SELECT  user_id
                         FROM    user_followers
                         WHERE   follower_id = $user_id
                         UNION ALL
                         SELECT  $user_id
                        ) uf
                JOIN    posts p
                ON      p.user_id = uf.user_id
                JOIN   user_profile up
                ON     up.user_id = p.user_id
                JOIN user_profile_images upi
                ON     upi.image_id = up.profile_image_id
                JOIN users u
                ON     u.user_id = p.user_id
                LEFT OUTER JOIN (SELECT ph.post_id, count(*) AS hi5_count
                                 FROM post_highfives ph
                                 GROUP BY ph.post_id) hi5c
                ON p.post_id = hi5c.post_id
                LEFT OUTER JOIN (SELECT pst.post_id,pst.is_branch_of_id, count(*) AS branch_count
                                 FROM posts pst
                                 GROUP BY pst.is_branch_of_id) brn
                ON p.post_id = brn.is_branch_of_id
                LEFT OUTER JOIN (SELECT pst.post_id,pst.reply_to, count(*) AS reply_count
                                 FROM posts pst
                                 GROUP BY pst.reply_to) rply
                ON p.post_id = rply.reply_to
                ORDER BY p.post_date DESC";


        $query = $this->db->query($sql);
        if ($query) {
            $result = array();
                foreach($query->result_array() as $r){
                    $branch_id = $r['is_branch_of_id'];
                    if($branch_id != 0){
                        $branch_array = $this->branch_query($this->postid_return_user_id($branch_id),$branch_id);
                    }else{
                        $branch_array = array();
                    }
                    $result[] = array_merge((array)$branch_array, (array)$r);
                }
            return $result;
        }
         else {
            return false;
        }


    }

I know this looks different from the question i asked but i was trying to simplfy what i was actually doing.I didn't think asking a question stating this problem would get me anywhere, although simplyfying the question still didn't get me anywhere as no one understood me haha. For my question anyway, the relevant part is the last bit of the code where i put a condition ssaying if branch_id is not zero get some array data and if it is return an empty array.The I merged the array together with then query result. Now i have to think of how i can simplyfy this.

Upvotes: 1

Marlin Pierce
Marlin Pierce

Reputation: 10089

These are basics in SQL. You really should learn some SQL. It's easy to learn, you could spend half an hour on it, and it would be very beneficial.

First, the posts for a given user would be:

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

To get the user fields you want, do a join

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

You should be able to figure out how to join to user_profile to get those fields.

To filter only those records without orig_post_id, you might need to test for zero, or you might need to test for NULL. Maybe both, so assuming you want to test for both:

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

Upvotes: 1

Related Questions