Show publications according to the interests of the users

Question

I want to show publications according to the interests (in keywords) of the users.

USERS TABLE

id | username
5  | joelsilva

PUBLICATIONS TABLE

id | publication title
8  | The best car of the year!

TABLE OF INTERESTS

id | username | interest
8  | joelsilva | car
9  | joelsilva | year

It shows all the publications with the car title and year on the home page.

I've tried this:

$pubs = mysql_query(
    "SELECT * 
      FROM PUBLICATIONS 
      WHERE (interest LIKE '%".$interests."%')
      ORDER BY id desc"
) or die(mysql_error());

It works, but only with one type of interest in the table. If adding more than one interest shows nothing.

Please explain what you mean. What algorithm do you want to use? — Gordon Linoff, Dec 31 '18 at 00:09
A lot will depend on how you populate $interests.... if you are just concatenating the keywords together you would get like %car year% so unless you have those words in that order with nothing between them then it will not work..... If you concatenate a % between the keywords it might work better, or you may need to build up the query adding a OR condition for each of the interests. — Shaun Peterson, Dec 31 '18 at 00:15
This is my code: $request_username_user_cookie= $requestusercookie["username"]; $request_interesses_total = mysql_query("SELECT * FROM interesses WHERE user='$request_username_utilizador_cookie'"); $request_interesses = mysql_fetch_assoc($request_interesses_total); $Interesses= $request_interesses["interesses"]; $ pubs = mysql_query ("SELECT * FROM PUBLICATIONS WHERE (interesse como '%". $ Interesses. "%') ORDER BY id desc") ou morrer (mysql_error ()); — Joel Silva, Dec 31 '18 at 00:19
https://stackoverflow.com/questions/4172195/mysql-like-multiple-values similar question — Pooja Aggarwal, Dec 31 '18 at 04:31

score 1 · Accepted Answer · answered Dec 31 '18 at 00:22

1

Join the publications and the interests on the title containing the interest and get the distinct rows of publication. Filter for the user.

SELECT DISTINCT
       p.*
       FROM publications p
            INNER JOIN interests i
                       ON p.title LIKE concat('%', i.interest, '%')
       WHERE username = ?
       ORDER BY p.id DESC;

(BTW: Instead of the user's name, their ID should be in the interests table. And you should probably have a look here to learn why you shouldn't use string concatenation when building queries but parameterized queries.)

answered Dec 31 '18 at 00:22

sticky bit

36,626
12
31
42

I´m using this code, you can become $ in the right place ...?$request_username_user_cookie= $requestusercookie["username"]; $request_interesses_total = mysql_query("SELECT * FROM interesses WHERE user='$request_username_utilizador_cookie'"); $request_interesses = mysql_fetch_assoc($request_interesses_total); $Interesses= $request_interesses["interesses"]; $ pubs = mysql_query ("SELECT * FROM PUBLICATIONS WHERE (interesse como '%". $ Interesses. "%') ORDER BY id desc") ou morrer (mysql_error ()); – Joel Silva Dec 31 '18 at 00:29

Show publications according to the interests of the users

1 Answers1