CODEWITHSUNDEEP
phpmysql
Venkat Akula
Venkat Akula

Reputation: 31

Retrieving data from multiple tables mysql php

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'");

Upvotes: 0

Views: 63

Answers (3)

Samer Abu Gahgah
Samer Abu Gahgah

Reputation: 741

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

Upvotes: 0

krishna ganisetti
krishna ganisetti

Reputation: 54

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);

Upvotes: 0

OneBeginner
OneBeginner

Reputation: 139

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

Upvotes: 1

Related Questions