0

I have the following 3 tables and want to get into the "inventory" table where user_id = user_id and want to show all "ownedskins" from a user with this id.

How can I do this?

Thanks for all replys!

The three tables

Unger Games
  • 11
  • 3
  • 10
  • 1
    Possible duplicate of [Joining three tables using MySQL](https://stackoverflow.com/questions/3709560/joining-three-tables-using-mysql) – Phonolog Sep 19 '17 at 14:56

4 Answers4

1

Joining more than 2 tables with MySql example:

SELECT    i.*
FROM      ownedskins o
          JOIN inventory i
              ON o.inventory_id=i.inventory_id
          JOIN user u
              ON u.user_id = i.user_id
WHERE     u.user_id = 1
Eric Leschinski
  • 146,994
  • 96
  • 417
  • 335
SE1986
  • 2,534
  • 1
  • 10
  • 29
1

Using joins is very easy, use something like this:

SELECT *
FROM table1 t1
JOIN table2 t2 ON t1.id = t2.id
JOIN table3 t3 ON t2.id2 = t3.id

You can also use JOIN variants like LEFT JOIN, INNER JOIN, OUTER JOIN...

António Delgado
  • 169
  • 1
  • 3
0

Let's say you have the following tables:

Driver
Car
Keys

If you want access to all 3 of them you could do the following:

SELECT D.name, C.plate, K.state
FROM Driver D, Car C, Keys K
WHERE C.key_id = K.id and
      K.owner_id = D.id;

Using JOINS:

SELECT    D.name, C.plate, K.state
FROM      Car C JOIN Keys K
              ON C.key_id = K.id JOIN Driver D
                  ON K.owner_id = D.id;
Floaterz
  • 128
  • 9
  • 1
    Promote the use of explict `JOIN` sintaxis, Aaron Bertrand wrote a nice article [Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) about it. – Juan Carlos Oropeza Sep 19 '17 at 14:56
0

Given that you know the user_id, you could do that with a single join, like this:

SELECT
  ownedskins.*
FROM
  ownedskins JOIN inventory ON ownedskins.inventory_id = inventory.inventory_id
WHERE
  inventory.user_id = x

Where x is the id of the user you want

You can also do it with alias to make it look cleaner:

SELECT
  os.*
FROM
  ownedskins os JOIN inventory i ON os.inventory_id = i.inventory_id
WHERE
  i.user_id = x
Piyin
  • 1,823
  • 1
  • 16
  • 23
  • It works, thanks! Do you also know how to get the id from the Player that has logged in before? So I can use this ID for the x. I use the following Code to login in another script: [link](https://imgur.com/a/lELVD) – Unger Games Sep 19 '17 at 17:37
  • No problem. About your other question, what do you mean by `logged in before`? If you're trying to get the `id` of the user that's logging in, you should change your query to `SELECT user_id,userpassword FROM ...` and then store `$row['user_id']` – Piyin Sep 19 '17 at 18:26
  • [link](https://imgur.com/a/fi1LE) I want to call the $_SESSION['user_logged'] in another script, how is that possible? Cause this stores the ID from the user that logged in in my Script. – Unger Games Sep 20 '17 at 12:29
  • Like I said earlier, you should change your password query from `SELECT userpassword FROM...` to `SELECT user_id,userpassword FROM...`, so you can get both `user_id` and `userpassword`. Then, when the login is successful you're able to do this: `$_SESSION['user_logged'] = $row['user_id'];` without more queries since your first one already brings that field – Piyin Sep 20 '17 at 14:22
  • Now I have the second script and want to call the ID but it doesnt work... I get the "Something went wrong". Can you help? [link](https://pastebin.com/994b5AYH) – Unger Games Sep 24 '17 at 16:14
  • How are you setting the variable? – Piyin Sep 24 '17 at 16:41
  • It looks good. Have you modified the way PHP handles sessions? What's the output of `var_dump($_SESSION);`? – Piyin Sep 25 '17 at 14:13
  • So, you're calling the PHP from Unity? – Piyin Sep 26 '17 at 15:01
  • Then the problem might be `WWW` doesn't manage sessions by default, so you should try something like this: http://answers.unity3d.com/answers/255077/view.html – Piyin Sep 27 '17 at 14:15