Mysql As

topdown.me · 11-06-2012, 07:57 PM

Hello all.
I have PHP Query:

PHP Code:


			
$query = "(select movieid,namehe,nameen,poster,plot,imdb,janer, 'moviesdb' as movies FROM moviesdb where nameen like '%$keyword%' or namehe like '%$keyword%')

UNION

(select gameid,namehe,nameen,poster,plot,rating,janer, 'gamesdb' as movies FROM gamesdb where nameen like '%$keyword%' or namehe like '%$keyword%')";

and this line:

PHP Code:


			
while($row = mysql_fetch_array($result)){

and when I use $row['imdb'] (from movie tables) it works, but when I Use
$row['rating'] from games table, its not.

why?

Old Pedant · 11-06-2012, 09:17 PM

In a UNION, only the names from the *FIRST* SELECT are accessible.

If you had done that same query using a DB tool, instead of trying to first do it in your PHP code, you would have seen that.

Code:

select movieid,namehe,nameen,poster,plot,imdb,janer, 'moviesdb' as movies 
UNION 
select gameid,namehe,nameen,poster,plot,rating,janer, 'gamesdb' as movies

In this case, you are correctly using your movies field to discriminate between the two SELECTs, so your PHP logic simply needs to be somthing like

Code:

if ( $row["movies"] =="gamesdb" )
{
    $gameid = $row["movieid"];
    $rating = $row["imdb"];
} else {
    $movieid = $row["movieid"];
    $imdb = $row["imdb"];
}

And alternative, if you really want the two fields to be separate, would be to do:

Code:

select movieid,...,plot,imdb, NULL AS rating, janer, ...
UNION 
select gameid,...,plot,NULL AS imdb, rating, janer, ...

And, in fact, you *SHOULD* do that if imdb and rating are not compatible data types.

For example, if imdb is INT and rating is CHAR(2) then they should *NOT* be combined into the imdb name as you had them in your original query.

MySQL is fairly loose about "compatible types" (more so than most DBs), but sometimes that can get you in trouble.

Old Pedant · 11-06-2012, 09:22 PM

By the way, why do you name them "moviesdb" and "gamesdb"? They are *NOT* DBs. They are only TABLES.

They *could* have been in separate DBs, but clearly they are not, as the code is written.

(To be in separate DBs you would have then had to write

Code:

SELECT ... FROM moviesDB.moviesTable ...
UNION
SELECT ... FROM gamesDB.gamesTable ...

or similar.)

Fou-Lu · 11-06-2012, 10:30 PM

I feel like my post here has gone completely ignored :P