Retrieving data from multiple tables mysql php

Question

I have 2 tables MOVIES and SHOWS.

MOVIES tables contains:

id, name, image, description.

SHOWS table contains:

id, movieid, description.

I'm executing mysql statement to retrieve records from SHOWS table, i'm getting all the records normally. Again I'm executing another mysql statement to get image from MOVIES table based on movies table id which i'm getting from first query. Is there any simple way to retrieve all the records from SHOWS table along with movie image?

These are my queries:

$qry1 = mysql_query("SELECT * FROM shows WHERE id='1'"); 
$res = mysql_fetch_array($qry1); 
$movieid = $res['movieid']; 
$qry2 = mysql_query("SELECT image FROM movies WHERE id='$movieid'");

These are my queries $qry1 = mysql_query(SELECT * FROM shows WHERE id='1'); $res = mysql_fetch_array($qry1); $movieid = $res['movieid']; $qry2 = mysql_query(SELECT image FROM movies WHERE id='$movieid'); — Venkat Akula
– Venkat Akula, Commented Oct 11, 2017 at 11:23
SELECT * FROM SHOWS as s INNER JOIN MOVIES as m ON m.id = s.movieid WHERE s.field = $var; Use * in select query is not best practices. — Parth Shah
– Parth Shah, Commented Oct 11, 2017 at 11:28

OneBeginner · Accepted Answer · 2017-10-11 11:40:38Z

1

SELECT  t1.id, t1.movieid, t1.description, t2.image FROM SHOWS as t1
INNER JOIN 
MOVIES as t2 ON t1.id = t2.id

Some sql join docs

or you can try this, i was not sure witch id is from where:

SELECT id, movieid, description, image FROM SHOWS 
INNER JOIN MOVIES
ON id = movieid

Some foreign key docs

edited Oct 11, 2017 at 11:40

answered Oct 11, 2017 at 11:30

OneBeginner

1392 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

krishna ganisetti · Accepted Answer · 2017-10-11 12:46:30Z

0

Here I am writing with out join you can all so use nested select queries

select movies.image from movies where movies.id in(Select shows.movieid form shows where shows.id= 1);

answered Oct 11, 2017 at 12:46

krishna ganisetti

549 bronze badges

Comments

Samer Abu Gahgah · Accepted Answer · 2017-10-11 12:52:35Z

0

You can retrieve data from multiple tables from one server at the same time. There is a lot of ways to achieve this operation which is called join. One of the possibility would be the LEFT JOIN like this:

SELECT t1.field1, t1.field2,..., t2.field1, t2.field2, ..., t2.fieldn
FROM table1 AS t2 
LEFT JOIN talble2 AS t2 ON t2.some_field = t1.anothed_filed
WHERE some_condition

In you case:

SELECT s.*, m.image
FROM SHOWS AS s
LEFT JOIN movies AS m ON s.movieid = m.id
WHERE some_condition

for more info see the documentation on https://dev.mysql.com/doc/refman/5.7/en/join.html

answered Oct 11, 2017 at 12:52

Samer Abu Gahgah

7511 gold badge9 silver badges18 bronze badges

Collectives™ on Stack Overflow

Retrieving data from multiple tables mysql php

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related