...

View Full Version : Search in 2 tables



topdown.me
11-05-2012, 11:05 AM
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:


$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.

stevenmw
11-05-2012, 12:23 PM
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.

Fou-Lu
11-05-2012, 01:53 PM
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:


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.

stevenmw
11-05-2012, 08:40 PM
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.

Fou-Lu
11-05-2012, 09:36 PM
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.

stevenmw
11-05-2012, 09:53 PM
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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum