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 ?
Aucun commentaire:
Enregistrer un commentaire