get data from multiple mysql tables and display in PHP ( arrray )

Question

i have a moviesdb with tables

movies table ;
data table
persons table
characters table
Modes table

MOVIES TABLE

===============================================
CODE : TITLE :        DESCRIPTION       YEAR
=====================================================
1      IRON MAN 3       xxxxx          2013
2      STAR WARS        xxxxx          2013

PERSONS TABLE

===============================================
CODE : NAME:                 BIRTH_DATE
=====================================================
1      SHANE BLACK         1965:04:04 00:00:00
2      ROBERT DOWNEY       1965:04:04 00:00:00
3      Gwyneth Paltrow     1965:04:04 00:00:00
4      Don Cheadle         1965:04:04 00:00:00

CHARACTERS TABLE

===============================================
PERSON  : NAME:             MOVIE
=====================================================
2      TONY STARK          1
3      PEPPER POTTS        1
4      Col. James Rhodes   1

MODES TABLE

===============================================
CODE : NAME : 
=====================================================
1      DIRECTOR 
2      WRITER
3      ACTOR
4      CHARACTER

DATA TABLE

===============================================
CODE : MOVIE :     PERSON   MODE
=====================================================
1      1             1       1
2      1             2       3 
3      1             3       3 
4      1             4       3

FINAL ANSWER WOULD BE like :

===============================================
CODE : MOVIE TITLE :  DIRECTOR  :     ACTORS           CHARACTERS
=====================================================
1      IRON MAN 3     SHANE BLACK   Robert Downey Jr.   Tony Stark
                                    Gwyneth Paltrow     Pepper Potts
                                    Don Cheadle         Col. James

MySQL command is :

SELECT 
  movies.title,
  movies.year,
  persons.name,
  modes.name,
  persons.birth_date,
  characters.name
FROM
  data
  INNER JOIN movies ON (data.movie = movies.code)
  INNER JOIN persons ON (data.person = persons.code)
  INNER JOIN modes ON (data.mode = modes.code)
  INNER JOIN characters ON (persons.code = characters.person)
  AND (characters.movie = data.movie)
WHERE
  MOVIES.code = '1'

SQL Command WORKS fine;

i am having problem with PHP CODE

$movie_query = mysql_query("
SELECT 
  movies.title,
  movies.year,
  persons.name,
  modes.name,
  persons.birth_date,
  characters.name
FROM
  data
  INNER JOIN movies ON (data.movie = movies.code)
  INNER JOIN persons ON (data.person = persons.code)
  INNER JOIN modes ON (data.mode = modes.code)
  INNER JOIN characters ON (persons.code = characters.person)
  AND (characters.movie = data.movie)
WHERE
  MOVIES.code = '1';");

WHILE ($rows = mysql_fetch_array($movie_query ))
{ 
    $movie_code = $rows['movies'.'code'];
    $movies_title = $rows['movies'.'title'];
    $movies_year = $rows['movies'.'year'];
    $movies_date_add = $rows['movies'.'date_add'];
    $movies_tagline = $rows['movies'.'tagline'];

    echo "$movie_code<br>$movies_title<br>$movies_year<br>$movies_date_add<br>$movies_tagline<br>";
}

any problem with my PHP CODE ..? ( seems SQL commands are not same as MySQL syntax )

in $movie_query remove;And use something else other than mysql. — Mihai
– Mihai, Commented Jul 28, 2013 at 9:58
Enable error_reporting(E_ALL); and use print_r($rows) to see how the field names differ from what you use in the loop (you're concatenating tablename and column in PHP). — mario
– mario, Commented Jul 28, 2013 at 10:11
@Mihai i understand removing the ";" but "use something else other than mysql" ? are you suggesting he use a different db ? and all why the down votes ? — mcgrailm
– mcgrailm, Commented Jul 28, 2013 at 10:16
@mcgrailm he is talking about mysqli because mysql function is depracated: php.net/manual/en/book.mysqli.php — Christian Giupponi
– Christian Giupponi, Commented Jul 28, 2013 at 10:19
@mcgrailm Yeah I formulated that wrong,use mysqli or PDO.Its almost an echo on this site but it should be repeated until everybody dumps mysql api. — Mihai
– Mihai, Commented Jul 28, 2013 at 10:22

mcgrailm · Accepted Answer · 2013-07-28 10:38:58Z

1 you need to use PDO instead of using mysql see christian-giupponi comment above for insturctions on that

2 you have a ";" in your sql statment that should be removed

2 the results of your query do not include the table name

3 you can't access data in the table unless you select it so $row['code'] would not work unless you ask for movies.code in your sql

4 when you are selecting fields with the same name you need to reassign them or you will end up with something wrong

$movie_query = mysql_query("
SELECT 
    movies.code,
    movies.title,
    movies.year,
    persons.name as pname,
    modes.name as mname,
    persons.birth_date,
    characters.name as cname
FROM
    data
    INNER JOIN movies ON (data.movie = movies.code)
    INNER JOIN persons ON (data.person = persons.code)
    INNER JOIN modes ON (data.mode = modes.code)
    INNER JOIN characters ON (persons.code = characters.person)
    AND (characters.movie = data.movie)
WHERE 
    MOVIES.code = '1'
");

WHILE ($rows = mysql_fetch_array($movie_query ))
{ 
    $movie_code = $rows['code'];
    $movies_title = $rows['title'];
    $movies_year = $rows['year'];
    $movies_date_add = $rows['date_add'];
    $movies_tagline = $rows['tagline'];

    echo "$movie_code<br>$movies_title<br>$movies_year<br>$movies_date_add<br>$movies_tagline<br>";
}

Good luck and Remember if you don't USE PDO now you will suffer for it later

Collectives™ on Stack Overflow

get data from multiple mysql tables and display in PHP ( arrray )

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related