Join two MySQL tables into PHP

Post here if you need help with SQL.
Post Reply
Smithers
Posts: 8
Joined: Mon Feb 06, 2012 3:09 pm

Join two MySQL tables into PHP

Post by Smithers »

Hello there,
Currently I am learning PHP/MySQl and there is one issue that most likely is extremly simple but still I just don't get it into my head. Let's say I have two tables with this content:
Company
- company_name
- company_location
Game
- game_name
- game_rating
Now I would like to combine these two tables into a PHP form, for example:
RockstarGames an American Company publishes a new game called Red Dead Redemption 3 and it is rated with 4,5 stars
How exactly am I doing this? I watched a lot of tutorials but I just don't get it.
I think I understood that I need another field in game such as company_id
But how exactly can I make sure that PHP is giving me the right game to the company that has created the game and how can I display this information (including location) into PHP?
Many Thanks for your help,
Daniel
User avatar
jacek
Site Admin
Posts: 3262
Joined: Thu May 05, 2011 1:45 pm
Location: UK
Contact:

Re: Join two MySQL tables into PHP

Post by jacek »

My attempt at explaining JOINs http://www.youtube.com/watch?v=NOiCr-LYWmY

You need to use a join :) if you have a company_id column in the games table your SQL might look somethign like this
SELECT
    `games`.`game_name`,
    `games`.`game_rating`.
    `companies`.`company_name`,
    `companies`.`company_location`
FROM `games`
INNER JOIN `companies`
ON `games`.`company_id` = `companies`.`company_id`
The best thing to do would be to read up on JOINs and then try running a few queries in phpmyadmin to see what they can do.
Image
Smithers
Posts: 8
Joined: Mon Feb 06, 2012 3:09 pm

Re: Join two MySQL tables into PHP

Post by Smithers »

Hi there,
many Thanks, worgs great.
Cheers
Smithers
Posts: 8
Joined: Mon Feb 06, 2012 3:09 pm

Re: Join two MySQL tables into PHP

Post by Smithers »

Hi Jacek,
another question regarding sql joins.
I managed to show the results of two tables but I am not sure how to insert information from one table automatically to anothers.
In this case I have a table of users and topics

Users
- id
- username
- email
- topics

Topics
- id
- title
- content
- user_id

Once the user is logged I want to allow him to create topics but obviously I would like show who wrote the topic.
My queries is this here:

$sql = "INSERT INTO topics(title, content, user_id)
VALUES('" . mysql_real_escape_string($_POST['title']) . "',
'" . mysql_real_escape_string($_POST['content']) . "',
THIS PART I AM NOT SURE ABOUT

What would I need to write down in order to automatically connect the logged in user to the topic (user_id)
Many Thanks,
User avatar
jacek
Site Admin
Posts: 3262
Joined: Thu May 05, 2011 1:45 pm
Location: UK
Contact:

Re: Join two MySQL tables into PHP

Post by jacek »

As far as I know you just have to run two queries. That is how I always do this anyway.

There is a mysql function that gets the last automatic ID value which can help with the linking.

Example.
INSERT INTO `users` (`name`) VALUES ('Bob')
then
INSERT INTO `user_settings` (`user_id`, `show_email`) VALUES (LAST_INSERT_ID(), 1)
Image
Post Reply