get data from different tables

Codes here !

Moderators: egami, macek, gesf

Post Reply
juulphp
New php-forum User
New php-forum User
Posts: 18
Joined: Tue Jan 24, 2017 5:41 am

Mon Mar 13, 2017 10:10 am

I want to get data from some different tables. This is the situation:

I have an table: users with an user_id
I have an table: movie with an title, img and more and also an movie_id.
I have an table: reviews with an review_id, the user_id and the movie_id

I want to show the review from an specific user with the user_id. By the review i want to show the movie info from the movie_id. Like title, img and more. How can i do the select query in php to show these things?

Thank you!

chorn
php-forum Active User
php-forum Active User
Posts: 302
Joined: Fri Apr 01, 2016 2:18 am

Mon Mar 13, 2017 10:59 pm

JOIN them together. refer to any searchengine with the highlighted keyword for tutorials.

juulphp
New php-forum User
New php-forum User
Posts: 18
Joined: Tue Jan 24, 2017 5:41 am

Tue Mar 14, 2017 3:38 am

I have got now this:

Code: Select all

SELECT id.users, firstname.users, lastname.users, id.review, itemid.review, rnumber.review, review.review, viewed.review, id.entertaiment, title.entertaiment FROM users JOIN review, entertaiment WHERE id.users = id.review AND itemid.review = id.entertaiment AND id.users = '12'
But i get an error: unknown column 'id.users' in field list. But thats the name.

chorn
php-forum Active User
php-forum Active User
Posts: 302
Joined: Fri Apr 01, 2016 2:18 am

Tue Mar 14, 2017 4:00 am

so there's no USER column in your ID table. just try

Code: Select all

select * from id

juulphp
New php-forum User
New php-forum User
Posts: 18
Joined: Tue Jan 24, 2017 5:41 am

Tue Mar 14, 2017 4:33 am

I had reversed the column name and table name. This is now my query:

Code: Select all

SELECT users.id, users.firstname, users.lastname, review.id, review.itemid, review.rnumber, review.review, review.viewed, entertaiment.id, entertaiment.title FROM users JOIN review, entertaiment WHERE users.id = review.id AND review.itemid = entertaiment.id AND users.id = '12'
Only I get to see anything. Empty rows. Is there a mistake somewhere.

chorn
php-forum Active User
php-forum Active User
Posts: 302
Joined: Fri Apr 01, 2016 2:18 am

Tue Mar 14, 2017 5:15 am

don't know due to missing data and a lack of formatting, just try an online SQL editor like sqlfiddle.com and post example data here.

juulphp
New php-forum User
New php-forum User
Posts: 18
Joined: Tue Jan 24, 2017 5:41 am

Tue Mar 14, 2017 5:33 am

I have done the sql query in PhpMyAdmin.

chorn
php-forum Active User
php-forum Active User
Posts: 302
Joined: Fri Apr 01, 2016 2:18 am

Tue Mar 14, 2017 5:41 am

how should that help?

juulphp
New php-forum User
New php-forum User
Posts: 18
Joined: Tue Jan 24, 2017 5:41 am

Tue Mar 14, 2017 6:08 am

Because the site: sqlfiddle.com didn't work. I get an error: Table 'db_9_bcbf0b.users' doesn't exist.

AdoptiveSolution
New php-forum User
New php-forum User
Posts: 159
Joined: Wed Jun 15, 2016 8:35 am

Tue Mar 14, 2017 8:45 am

First, don't change the names of tables and vars during the conversation.
Second, entertainment is with an n in the middle.

Try this :

Code: Select all

SELECT
	users.id,
	users.firstname,
	users.lastname,

	review.id,
	review.itemid,
	review.rnumber,
	review.review,
	review.viewed,

	entertaiment.id,
	entertaiment.title

FROM review

INNER JOIN 
	users
ON
	users.id = review.id

INNER JOIN
	entertaiment
ON
	entertaiment.id - review.itemid
	
WHERE
	users.id = '12'
https://www.w3schools.com/sql/sql_join.asp

juulphp
New php-forum User
New php-forum User
Posts: 18
Joined: Tue Jan 24, 2017 5:41 am

Tue Mar 14, 2017 11:59 am

Your code is not quite right. For users.id = review.userid. So I did it like this:

Code: Select all

SELECT users.id, users.firstname, users.lastname, review.id, review.itemid, review.userid, review.rnumber, review.review, review.viewed, entertaiment.id, entertaiment.title FROM review INNER JOIN users ON users.id = review.userid INNER JOIN entertaiment ON entertaiment.id - review.itemid WHERE review.userid = '13'
Now he only has entertainment all rows, and they all give the same ID, review, first name and last name. And that's not the intention. I just want the entertainment title and review where the ID is 13. And that there is at present only one.

juulphp
New php-forum User
New php-forum User
Posts: 18
Joined: Tue Jan 24, 2017 5:41 am

Wed Mar 15, 2017 1:36 am

It works, thanks for the answers!

Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 4 guests