Tracking and posting system (social network type)

Asked

Viewed 1,056 times

4


I’m developing a new project and I’m creating a profile following system where the goal is to show only the publications of those I follow.

Such as a social network.
I follow the person, so I get that person’s publications on the home page.

My difficulty lies in showing only the publications of those I follow.
Example:

Esquema



What I think is that I have to join my 2 tables in just 1 query, but I don’t think I can use "UNION" at all...
You’re probably thinking the wrong way, forgive me... Here are my required mysql tables.

tables



I have tested several querys but I could not reach a resolution.
Is it better to make 2 querys and compare with a while or if? If yes... how? Regards to all and thanks again.
PS: I’m doing in Pdo.

php mysql query select-sql
  • 1

    The PDO problem is that it needs to suit any database, but it doesn’t always work. For example, the solution in Mysql is different from the solution in SQL Server, which tends to be different in Postgresql. Generically Voce would do something like [SELECT post. * FROM post, follow WHERE follow.userid=post.userid and follow.whofollowid=CODIGO_USER_LOGADO_ATUAL ORDER BY POST.data_record desc]. But you did not inform the contents of the userid and whofollowid fields. I compared the userid of the two tables, but maybe it’s [WHERE follow.whofollowid=post.userid and follow.userid=CODIGO_USER_LOGADO_ATUAL]

  • You can tell if it worked?

  • I’m sorry, I could only come back today. I will now test.

  • Hello again. Thank you very much for your reply, with some changes it worked as wanted perfectly. Thank you very much. I’m gonna post down the code.

  • SELECT post. * FROM post, follow WHERE follow.whofollowid = post.userid and follow.userid = ? ORDER BY date DESC, time DESC

  • Therefore, I use bind’s for variables that bind 1 - current userid logged in. = ? , as posted the code showed my post so this way will show the posts of those who follow. Thank you very much.

  • I will turn it into an answer so it can be documented as a solution. As I did in the comment, I will post the two possible solutions.

Show 2 more comments

1 answer

1

The PDO problem is that it needs to suit any database, but it doesn’t always work.

For example, the solution in Mysql is different from the solution in SQL Server, which tends to be different in Postgresql.

Generically Voce would do something like 

SELECT post.* FROM post, follow WHERE follow.userid=post.userid and follow.whofollowid=CODIGO_USER_LOGADO_ATUAL ORDER BY POST.data_registro desc

But you did not inform the contents of the userid and whofollowid fields. I compared the userid of the two tables, but maybe it is reversed, such as:

SELECT post.* FROM post, follow WHERE follow.whofollowid=post.userid and follow.userid=CODIGO_USER_LOGADO_ATUAL

How you mentioned that it worked for you was my second option I had posted in the comment (and posted above too):

SELECT post.* FROM post, follow WHERE follow.whofollowid = post.userid and follow.userid = ? ORDER BY date DESC, time DESC
  • 1

    "The PDO problem is that it needs to suit any database, but it doesn’t always work". The idea of PDO is to standardize only the connection and query methods with the database, and not the implementation with the database with SQL. That’s not exactly a problem because its purpose is not to be a abstraction layer of the database

  • As I said in the comments of the question, since my solution was used by him, I turned it into an answer.

  • 1

    The comment is to make clear the purpose of the PDO. It fulfills what it proposes to do: abstraction of the data access layer. I do not see as a problem of PDO having to adapt to the various databases.

Browser other questions tagged php mysql query select-sql

You are not signed in. Login or sign up in order to post.