CODEWITHSUNDEEP
mysqlsql
Viorel
Viorel

Reputation: 39

How to select the items with foreign keys?

Below there are my tables. On login, I want to select all messages who belong for example to user with id = 1. I have tried something like this but it doesn't work, select id from users where users.id = message_sent.id_user

The error I got is: #1054 - Unknown column 'message_sent.id_user' in 'where clause'

enter image description here enter image description here

Upvotes: 0

Views: 29

Answers (1)

user10791031
user10791031

Reputation:

What you want to use is called a join.

In your case: SELECT id FROM users JOIN message_sent ON users.id = message_sent.id_user

Tip

I see that you use incremental counters as an id for your rows. This is a really bad idea. You should never ever use incremental counters. Where is why:

  • A person can just loop trough your users or messages with a script.
  • People can just count how many users you have

please take a look at this: https://youtu.be/gocwRvLhDf8?t=119

You should just use a random generated string or number (also explained in the video). See if it is taken. If it isn't taken use it. This isn't to much work and improves the security on your site!

Upvotes: 1

Related Questions