Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4

Thread: Mysql As

  1. #1
    New Coder
    Join Date
    Jul 2011
    Posts
    52
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Mysql As

    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?
    Need domain? Buy Cheap Domain at Rapid Host :)

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,688
    Thanks
    80
    Thanked 4,655 Times in 4,617 Posts
    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.
    Last edited by Old Pedant; 11-06-2012 at 10:20 PM.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,688
    Thanks
    80
    Thanked 4,655 Times in 4,617 Posts
    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.)
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    I feel like my post here has gone completely ignored :P


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •