CODEWITHSUNDEEP
phpmysql
max
max

Reputation: 331

is it safe to run a another query in side a mysql loop

I have a left join query to get posts liked by a users. if 2nd logged in user visit the 1st user profile will show the likes by 1st user and also will show a text on the post if 2nd user (logged in user) like the same post

user table

user_id | username

likes table

like_id | post_id | uid

MySQL

$SQL = "SELECT * FROM  likes LEFT JOIN users ON users.user_id = likes.uid WHERE likes.uid = 'user1'"

If i run another query inside the while loop of above query it will work

$check_id = row['post_id']; //get post id from 1st loop

if(isset($_SESSION['userid'])){

    $check = "SELECT * FROM  likes WHERE post_id='$check_id' AND uid='LOGED-IN-USER-ID'"

}

Then i can get the num_rows and add text. This work perfectly fine but i like to know is there a better way to do this without running so many queries inside the while loop. Is there a way to combine the queries or do the 2nd query outside of the loop.

Upvotes: 0

Views: 72

Answers (2)

Julien Palard
Julien Palard

Reputation: 11636

That's "safe" from "data consistency point of view", but querying in a while after a query is called a "1+N" and is typically a performance killer, you may easily find documentation about SQL 1+N problem.

The solution is to let the SQL server do the job for you in a single query, avoiding playing ping pong with it (read: TCP packets back-and-forth, query parsing, ...).

Given:

> SELECT * FROM user;
+---------+----------+
| user_id | username |
+---------+----------+
|       1 | root     |
|       2 | user2    |
|       3 | user3    |
+---------+----------+


> SELECT * FROM `like`;
+---------+---------+---------+
| like_id | post_id | user_id |
+---------+---------+---------+
|       1 |       1 |       1 |
|       2 |       2 |       1 |
|       3 |       3 |       1 |
|       4 |       4 |       1 |
|       5 |       2 |       2 |
+---------+---------+---------+

> SELECT * FROM `post`;
+---------+--------+
| post_id | text   |
+---------+--------+
|       1 | post 1 |
|       2 | post 2 |
|       3 | post 3 |
|       4 | post 4 |
+---------+--------+

There's multiple way to request what you want, but one way may be:

> SELECT like_id, like.post_id, text,
         (SELECT 1 FROM `like`
           WHERE post_id = post.post_id AND
                 user_id = 2 /* logged in user */) AS I_like_it_too
    FROM `like`
    JOIN post USING (post_id)
   WHERE user_id = 1 /* user id of seen profile  */;

+---------+---------+--------+---------------+
| like_id | post_id | text   | I_like_it_too |
+---------+---------+--------+---------------+
|       1 |       1 | post 1 |          NULL |
|       2 |       2 | post 2 |             1 |
|       3 |       3 | post 3 |          NULL |
|       4 |       4 | post 4 |          NULL |
+---------+---------+--------+---------------+

The use the I_like_it_too alias to display post differently as needed.

From a performance point of view you'll need an index on like.user_id to restrict the selected rows on a little subset, the dependent subquery will only be ran for this subset, so that's OK.

Another possibility may be:

>  SELECT displayed.like_id, displayed.post_id, text, my_likes.like_id is not null AS i_also_like
     FROM `like` AS displayed
     JOIN post USING (post_id)
LEFT JOIN `like` AS my_likes ON
          displayed.post_id = my_likes.post_id AND
          my_likes.user_id = 2 /* logged user */
    WHERE displayed.user_id = 1 /* user id of seen profile  */;

+---------+---------+--------+-------------+
| like_id | post_id | text   | i_also_like |
+---------+---------+--------+-------------+
|       1 |       1 | post 1 |           0 |
|       2 |       2 | post 2 |           1 |
|       3 |       3 | post 3 |           0 |
|       4 |       4 | post 4 |           0 |
+---------+---------+--------+-------------+

Upvotes: 2

KikiTheOne
KikiTheOne

Reputation: 523

Do u mean like this ?

Table SO_LIKES ( ur "Like" Table )

like_id | post_id | uid
      1 |       1 | 1
      2 |       2 | 1
      3 |       1 | 2

Table SO_USERS ( ur "Users" Table )

   user_id | username 
         1 | User1 
         2 | User2

SQL

SELECT * FROM SO_LIKES as t1 LEFT JOIN SO_USERS as t2 ON t1.uid = t2.user_id INNER JOIN SO_LIKES as t3 ON t1.post_id = t3.post_id WHERE t2.user_id = 1 AND t3.uid = 2

SO Simply call the Same Table in ur query again and use the ID of user 2 there

WHERE t2.user_id = 1 AND t3.uid = 2

Output Looks then like this

like_id | post_id | uid | user_id | username | like_id | post_id | uid 
      1 |       1 |   1 |       1 |    User1 |       3 |       1 | 2

SO u get the POST_id 1 That both Users has Liked

Upvotes: 0

Related Questions