Page 1 of 1

Join two MySQL tables into PHP

Posted: Mon Feb 06, 2012 3:10 pm
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

Re: Join two MySQL tables into PHP

Posted: Mon Feb 06, 2012 10:01 pm
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.

Re: Join two MySQL tables into PHP

Posted: Wed Feb 08, 2012 5:23 am
by Smithers
Hi there,
many Thanks, worgs great.
Cheers

Re: Join two MySQL tables into PHP

Posted: Wed Feb 22, 2012 9:11 am
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,

Re: Join two MySQL tables into PHP

Posted: Thu Feb 23, 2012 11:23 pm
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)