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
Join two MySQL tables into PHP
Re: Join two MySQL tables into PHP
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
[syntax=sql]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`[/syntax]
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.
You need to use a join
if you have a company_id column in the games table your SQL might look somethign like this[syntax=sql]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`[/syntax]
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
Hi there,
many Thanks, worgs great.
Cheers
many Thanks, worgs great.
Cheers
Re: Join two MySQL tables into PHP
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,
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
- 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
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.
[syntax=sql]INSERT INTO `users` (`name`) VALUES ('Bob')[/syntax]
then
[syntax=sql]INSERT INTO `user_settings` (`user_id`, `show_email`) VALUES (LAST_INSERT_ID(), 1)[/syntax]
There is a mysql function that gets the last automatic ID value which can help with the linking.
Example.
[syntax=sql]INSERT INTO `users` (`name`) VALUES ('Bob')[/syntax]
then
[syntax=sql]INSERT INTO `user_settings` (`user_id`, `show_email`) VALUES (LAST_INSERT_ID(), 1)[/syntax]