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 6 of 6
  1. #1
    New Coder
    Join Date
    Jul 2011
    Posts
    52
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Search in 2 tables

    Hello.
    I have a search in my database for 2 tables, but I need to know from what table the column is taken.
    This is my code:

    PHP Code:
    $query "(select namehe,nameen,poster,plot, 'moviesdb' as namer FROM moviesdb where nameen like '%$keyword%' or namehe like '%$keyword%')
    UNION
    (select namehe,nameen,poster,plot, 'gamesdb' as namer FROM gamesdb where nameen like '%$keyword%' or namehe like '%$keyword%')"
    ;
    mysql_query($query); 
    My problem is: If I have a column with the name "Example" in gamesdb and in moviesdb, it has to show both:
    Example
    Example

    but I want to show the table the data is taken from, like this:
    Example [Movies]
    Example [Games]

    How I can do that?

    Thanks in advance.
    Need domain? Buy Cheap Domain at Rapid Host :)

  • #2
    Regular Coder stevenmw's Avatar
    Join Date
    Jun 2007
    Location
    OK
    Posts
    497
    Thanks
    27
    Thanked 31 Times in 31 Posts
    There are a couple of ways that come to mind. In each table simply name the primary row either movies are games.

    Then use a while loop to output the data from each table and have it print out the primary rows of the table.

    Or if you need the primary row row be an auto increment simply give each table a specific row that identifies the table.

    Basically have a row in each table that stores the word 'movies' or 'games' depending oh which table it is then use a while loop to display it how you want.

  • #3
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    I can't see how the pk autoincrement would help. You would end up with 1 and 1 in both movies and games. This would work if you had information in the table itself, but I would say that's a redundant addition that's not necessary.
    Simply adding the string in each of the queries union's make sense. Now that you have it, you can simply switch on it or user an if/elseif:
    PHP Code:
    switch ($record['namer'])
    {
        case 
    'moviedb':
            
    $suffix '[Movies]';
            break;
        case 
    'gamesdb':
            
    $suffix '[Games]';
            break;

    I suggest the switch as it will be easier to programatically assign new items. On a side note, you don't technically need to provide a name alias in the second query for the union. All field names come from the first results table. I wouldn't remove it myself though.

    BTW, if you simply give your string what you need to display, then it wouldn't require a check at all. Not sure if you need to use it later for an insert/update/delete or whatever else though, but you can add new fields if desired.

  • #4
    Regular Coder stevenmw's Avatar
    Join Date
    Jun 2007
    Location
    OK
    Posts
    497
    Thanks
    27
    Thanked 31 Times in 31 Posts
    What I meant by using auto increment was if he planned to have an ID row that assigned a number to each new DB entry for purposes down the line. i wasn't sure if he was going to do this or not so I simply mentioned in case. I wasn't telling him to do it I was simply covering all my bass if he had planned to.

    Maybe for editing, or something.

    Then have a row that states whether it is movies or game. But I guess he doesn't even have to do that. He could have two while loops and simply add the text movie or games in it.

    For something as simple as this I would really only have one table with a row for the type of entry, and then I would add the term 'movies' or 'games' to that row each time a new entry was made depending on what type it was. But then again I'm not the best at MySql or PHP.
    Last edited by stevenmw; 11-05-2012 at 08:48 PM.

  • #5
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    Oh I see what you mean, like a table level inheritance? A "primary" style table issues the autoincrement for its pk, and records its db type, whilst the "child" tables (movies and games) gather a non-ai PK from the "primary" table. That way you can union and theoretically spawn an instance (a class object for example, or an array) based entirely off of the primary table.

    I do something similar as well, but not quite like this. I use custom uuid style identifiers on an Object table, and include an instanceof datatype linked to a core classes table. So id x is a typeof ForumThread, id y is a typeof Principal, z is a typeof ACL, etc. This way I can spin off and join the classes to create new instances of a type based entirely off of the uuid. Works pretty good. I wouldn't consider using an auto increment for mine since its every object, but plays very well with my code inheritance model.

  • #6
    Regular Coder stevenmw's Avatar
    Join Date
    Jun 2007
    Location
    OK
    Posts
    497
    Thanks
    27
    Thanked 31 Times in 31 Posts
    Quote Originally Posted by Fou-Lu View Post
    A "primary" style table issues the autoincrement for its pk, and records its db type, whilst the "child" tables (movies and games) gather a non-ai PK from the "primary" table. That way you can union and theoretically spawn an instance (a class object for example, or an array) based entirely off of the primary table.
    Yes, exactly. However, I could never say it quite like that. Haha.


  •  

    Posting Permissions

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