How to implement a PHP condition using pure SQL?

Question

I have this tables structure:

// Posts
+----+------------+-----------------------+----------------+-------------+
| id |   title    |        content        |  money_amount  |  author_id  |
+----+------------+-----------------------+----------------+-------------+
| 1  | title 1    | content 1             | NULL           | 12345       |
| 2  | title 2    | content 2             | 25             | 42355       |
| 3  | title 3    | content 3             | 5              | 53462       |
| 4  | title 4    | content 4             | NULL           | 36346       |
| 5  | title 5    | content 5             | 15             | 13322       |
+----+------------+-----------------------+----------------+-------------+
//                                          ^^ NULL means this post is free


// Money
+---------+--------------+
| post_id | user_id_paid | 
+---------+--------------+
| 2       | 42355        |  // He is author of post
| 2       | 34632        |  // This row means besides author, this user 34632 can see this post too. Because he paid the money of this post.
| 3       | 53462        |  // He is author of post
| 5       | 13322        |  // He is author of post
| 3       | 73425        |  // This row means besides author, this user 34632 can see this post too. Because he paid the money of this post.
+---------|--------------+

Note1: All post_id(s) in the Money table are belong to those posts which are non-free.

Note2: Always there is a row belong to author of post (which is non-free) in the Money table.

Note3: Money table is just to determines who can see such a post.

Now this user $_SESSION['current_user'] = '23421' wants to see this post id = 2. Here is my code:

$stm = $this->dbh->prepare(SELECT * FROM Posts WHERE id = '2');
$stm->execute();
$result = $stm->fetch(PDO::FETCH_ASSOC);

if ( $result[money] == '') {   // money_amount is NULL in the Posts table
    this post is free and everybody can see it
} else {

    $stm = $this->dbh->prepare(SELECT count(1) FROM Money WHERE post_id = '2' and user_id = $_SESSION['current_user']);
    $num_rows = $stm->fetchColumn();

    if($num_rows){
        $paid = true;  // This means current user paid the cost of post and he can see it.
    } else {
        $paid = false; // this means current user didn't pay the cost of post and he cannot see it.
    }
}

I want to know, can I implement those two query in one query and do that condition using MySQL instead of PHP ?

@frz3993 Actually I need to check if the value of money_amount column is not NULL then join to Money table and check whether the id of current user is exists or not. How can I implement that condition using MySQL? — stack
– stack, Commented Mar 26, 2016 at 19:04
Try with this query, I've written it very fast i don't know if it's right. If the result is >0 the user can see the post. SELECT count(*) FROM Money, Posts WHERE (Posts.id = 2 and Posts.money_amout is null) OR (Money.post_id = 2 and Money.user_id_paid = $_SESSION['current_user']) — Claudio King
– Claudio King, Commented Mar 26, 2016 at 19:41

RomanPerekhrest · Accepted Answer · 2016-03-26 20:27:52Z

1

Here is solution using IF and EXISTS functions(MySql):

...
$stmt = $conn->prepare(" 
        SELECT IF(p.money_amount,1,0) as notfree, 
        EXISTS(SELECT * FROM `Money` WHERE `post_id` = ? AND`user_id_paid` = ?) as paid
        FROM `Posts` p WHERE p.id = ? ");

$stmt->execute([2, $_SESSION['current_user'], 2]);
$result = $stmt->fetch(\PDO::FETCH_ASSOC);

if (!$result['notfree']) {  // post is free
    // this post is free and everybody can see it
} else {
    $paid = ($result['paid'])? true : false; 
}

answered Mar 26, 2016 at 20:27

RomanPerekhrest

93.1k4 gold badges75 silver badges112 bronze badges

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

1 Comment

RomanPerekhrest Over a year ago

as for subqueries within IF statement - consider the following post: stackoverflow.com/questions/17723808/…

frz3993 · Accepted Answer · 2016-03-26 19:29:09Z

1

You can use a join, and the query below uses LEFT JOIN.

SELECT * FROM Money
LEFT JOIN Posts ON Money.post_id = Posts.id
WHERE ((Posts.money_amount IS NOT NULL AND Money.user_id_paid = :userId)
      OR Posts.money_amount IS NULL) AND Posts.id = :postId

Note that :userId is a placeholder for PDO parameterized query, where you should bind the parameter to the placeholder before execution. Like:

$postId = 2;
$stmt->bindParam('userId', $_SESSION['current_user']);
$stmt->bindParam('postId', $postId);

Also note that when binding the placeholder name doesn't need the colon. Using a RIGHT JOIN means you SELECT from the Posts table and join the Money table.

edited Mar 26, 2016 at 19:29

answered Mar 26, 2016 at 19:20

frz3993

1,63312 silver badges13 bronze badges

Collectives™ on Stack Overflow

How to implement a PHP condition using pure SQL?

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related