2

I have three databases in the same server.. They are margoplatinum, detos and plaza. They also have the same structure of tables. One of the table is called movie. Here is the table structure of movie table:

id_movie     int(11) PK, AI;
movie_title  varchar(100);
status       varchar(30);

Data from margoplatinum in movie table are:

id_movie   |     movie_tittle     |     status
--------------------------------------------------
    1      |     Frozen           |   Now Playing
    2      | The-Amazing-Spidey2  |   Coming Soon

Data from detos in movie table are :

 id_movie   |     movie_tittle     |     status
--------------------------------------------------
    1      |     Comic8           |   Now Playing
    2      |     Godzilla         |   Coming Soon

and data from plaza in movie table are :

id_movie   |     movie_tittle     |     status
--------------------------------------------------
    1      |     The Killer       |   Now Playing
    2      |     Godzilla         |   Coming Soon

So what i want to do is to select from the three databases above (in my php code and convert it into JSON) for table movie where the status = "Now Playing"..

Here is my php code :

<?php

 $db_host  = "127.0.0.1";

 $db_uid  = "root";

 $db_pass = "";

 $db_con = mysql_connect($db_host,$db_uid,$db_pass) or die('could not connect');

 $q = mysql_query('SELECT DISTINCT a.id_movie, b.id_movie, c.id_movie, a.movie_tittle, 
                  b.movie_tittle, c.movie_tittle FROM `margoplatinum`.movie a 
                  INNER JOIN `detos`.movie b ON a.id_movie = b.id_movie 
                  INNER JOIN `plaza`.movie c ON b.id_movie = c.id_movie
                  WHERE a.status = "Now Playing"');
 $v = '{"info" : [';

 while($r=mysql_fetch_array($q))
   {
$ob = array("<br>","<b>","</b>");
if(strlen($v)<12)
    {
    $v .= '{"id_movie" : "'.$r['id_movie'].'", "movie_tittle" : 
             "'.$r['movie_tittle'].'"}';
    }
else
    {
    $v .= ',{"id_movie" : "'.$r['id_movie'].'", "movie_tittle" :
            "'.$r['movie_tittle'].'"}';
    }

    }
  $v .= ']}';
  echo $v;
  echo mysql_error();
  mysql_close();

    ?>

But when I execute the php above, an error occured..Here is the error message :

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\CinemaInfo\list_movie.php on line 17 {"info" : []}Unknown column 'a.status' in 'where clause'

Is my query above correct? or i did something wrong with my query.. Can anyone help me to write the correct query based on my case above? I really appreciate any help from you. Thank you :)

Aprilia
  • 53
  • 2
  • 13
  • Not sure if this is incorrect but why have you given `FROM 'margoplatinum'.movie a` ? Shouldn't it be just the table name as `FROM margoplatinum a` – AyB Feb 04 '14 at 12:04
  • margoplatinum is a database name, if i want to connect three databases at the same time, I have to declare the database name before the table.field :) – Aprilia Feb 04 '14 at 12:08
  • On a sidenote, you should consider creating your PHP result array and then converting it to JSON with `json_encode()`, instead of creating it manually. Then, on the client end, you can just `json_decode()` and get back the same PHP array. – Fanis Hatzidakis Feb 04 '14 at 12:21

4 Answers4

2

I think that what you need is a UNION not a JOIN, a join operation will find movies with same ID but different names.

So, I would go for:

As you mentioned that you only want two columns, here is what you need.

select id_movie, movie_tittle
  from 
   (
   select id_movie, movie_tittle, status
     from `margoplatinum`.movie
    UNION
   select id_movie, movie_tittle, status
     from `detos`.movie
    UNION
   select id_movie, movie_tittle, status
     from `plaza`.movie
   ) as a
where a.status = 'Now Playing'
Jorge Campos
  • 22,647
  • 7
  • 56
  • 87
  • wait..I'd give it a try..and I'll be back to report the result :) – Aprilia Feb 04 '14 at 12:16
  • sorry it still not working..it gives an error again like : `Unknown column 'a.status' in 'where clause'` at line 2` :( – Aprilia Feb 04 '14 at 12:22
  • Sir, i only want to show 2 fields from movie table, so I didn't write the `status` field in my code..is it influence my result? – Aprilia Feb 04 '14 at 12:27
  • Yeas sure it is... You have to have the status field at least on the UNIONs query to filter outside. – Jorge Campos Feb 04 '14 at 12:42
  • @Aprilia glad that it helped you. You should consider in accept the answer if it fits to you, that way it can be helpful to others who came on the same problem. :) – Jorge Campos Feb 04 '14 at 14:07
1

First make sure your query is working by running it in mysql query editor directly.

If it is working then there is something wrong with the way query is executed using php.

The below query worked for me

select a.id from `test1`.app_user a 
inner join `test2`.app_user r on r.id=a.id 
inner join `test`.app_user f on a.id=f.id 
where a.Status="ACTIVE"

So, i'm guessing the column name might be different or something like that

Chakradhar Vyza
  • 285
  • 4
  • 11
0

Try changing your query for the following:

 $q = mysql_query("SELECT DISTINCT a.id_film, b.id_film, c.id_film, a.judul_film, 
                  b.judul_film, c.judul_film FROM `margoplatinum`.film a 
                  INNER JOIN `detos21`.film b ON a.id_film = b.id_film 
                  INNER JOIN `depokplaza21`.film c ON b.id_film = c.id_film
                  WHERE a.status = 'Now Playing'");

Strings in SQL use simple ' not double "

Donnie Rock
  • 552
  • 1
  • 14
  • 27
  • I've tried it but it gives another error `Parse error: syntax error, unexpected 'Now' (T_STRING) in C:\xampp\htdocs\CinemaInfo\list_movie.php on line 14` .. what sould I do? :( – Aprilia Feb 04 '14 at 12:05
0

It would be better to have a single database to handle this. The table structures could be:

CREATE TABLE `movies` (
id_movie INT AUTO_INCREMENT,
movie_title VARCHAR(50),
  PRIMARY KEY( id_movie )
);

CREATE TABLE `locations` (
  id_movie_fk INT,
  location VARCHAR(50),
  STATUS VARCHAR(25)
);

I've set up an sqlFiddle example.