1

specs: PHP 5 with mySQL built on top of Codeigniter Framework.

I have a database table called game and then sport specific tables like soccerGame and footballGame. these sport specific tables have a gameId field linking back to the game table. I have corresponding classes game and soccerGame/footballGame, which both extend game.

When I look up game information to display to the user, I'm having trouble figuring out how to dynamically link the two tables. i'm curious if it's possible to get all the information with with one query. The problem is, I need to query the game table first to figure out the sport name.

if that's not possible, my next thought is to do it with two queries. have my game_model query the game table, then based off the sport name, call the appropriate sport specific model (i.e. soccer_game_model) and get the sport specific info.

I would also pass the game object into the soccer_model, and the soccer_model would use that object to build me a soccerGame object. this seems a little silly to me because i'm building the parent object and then giving it to the extending class to make a whole new object?

thoughts?

thanks for the help.

EDIT:

game table

gameId
sport (soccer, basketball, football, etc)
date
other data

soccerGame table

soccerGameId
gameId
soccer specific information

footballGame table

footballGameId
gameId
football specific information

and so on for other sports

So I need to know what the sport is before I can decide which sport specific table I need to pull info from.

UPDATE:

Thanks all for the input. It seems like dynamic SQL is only possible through stored procedures, something I'm not well versed on right now. And even with them it's still a little messy. Right now I will go the two query route, one to get the sport name, and then a switch to get the right model.

From the PHP side of things now, it seems a little silly to get a game object, pass it to, say, my soccer_game_model, and then have that return me a soccer_game object, which is a child of the original game. Is that how it has to be done? or am I missing something from an OO perspective here?

Andrew Brown
  • 5,330
  • 3
  • 22
  • 39
  • I'm having trouble understanding the issue and setup. Can you post the structure of your tables? – Bafsky Mar 13 '13 at 18:02

3 Answers3

1

UPDATE

Consider passing the "sport" parameter when you look up game data. As a hidden field, most likely. You can then use a switch statement in your model:

switch($gameValue) {
  case 'football': $gameTable = "footballGame"; break;
  case 'soccer': $gameTable = "soccerGame"; break;
}

Then base your query off this:

"SELECT * 
FROM ". $gameTable . "
...etc

You can combine the tables with joins. http://www.w3schools.com/sql/sql_join.asp

For example, if you need to get all the data from game and footballGame based on a footballGameId of 15:

SELECT *
FROM footballGame a
LEFT OUTER JOIN game b ON a.id = b.gameId
WHERE footballGameId = 15
Devin Young
  • 841
  • 7
  • 21
  • yup, i'm well acquainted with joins. the problem is i don't know which table to join with before I've gotten some info from the `game` table. it may not be possible w/o two queries. curious if it was – Andrew Brown Mar 13 '13 at 18:23
  • thanks for the update. the `game` table does know what sport it belongs to so this is definitely an option, and a suggestion that others have given. I guess I was curious if it's possible to do it with 1 query. ie. dynamically use field name of first table for table name of joined table. – Andrew Brown Mar 13 '13 at 18:48
1

To extend on Devin Young's answer, you would achieve this using Codeigniter's active record class like so:

public function get_game_by_id($game_id, $table)
{
    return $this->db->join('game', 'game.id = ' . $table .  '.gameId', 'left')
    ->where($table . '.gameId', $game_id)
    ->get('game')
    ->result();
}

So you're joining the table by the gameId which is shared, then using a where clause to find the correct one. Finally you use result() to return an array of objects.

EDIT: I've added a second table paramater to allow you to pass in the name of the table you can join either soccerGame, footballGame table etc.

If you don't know which sport to choose at this point in the program then you may want to take a step back and look at how you can add that so you do know. I would be reluctant to add multiple joins to all sport tables as you''ll run into issues down the line.

jimbo2087
  • 1,034
  • 8
  • 20
  • thanks for the comment. the problem is not how to do joins though, the problem is how to figure out which table to join. some `game` tables may have to join with the `footballGame` table, while others have to join with `soccerGame` table. – Andrew Brown Mar 13 '13 at 18:30
  • I'd look at the overall design of you're application as the only other way would be to loop through the tables until you find a match. Perhaps storing the type of the game in the game table would be and idea. If the name corresponded to the table a lookup on the right table using the method above would be easy. I'm assuming a game can only be one type e.g. soccer, football of course. – jimbo2087 Mar 13 '13 at 18:36
  • the `game` table does know what sport it is. can I use that to dynamically join the correct table? the way you're suggesting would require a call first to the `game` table and then to the `soccerGame` table. maybe this is the only way, but i was hoping someone knew of a way using only one query. – Andrew Brown Mar 13 '13 at 18:45
  • Don't worry too much about the extra query. Scale when you need to and not before. Once you have the correct table name you don't need to do a JOIN query so it's a simple, nice and fast select. You can join the data in the application. – jimbo2087 Mar 13 '13 at 18:47
  • I agree with the edit. I will know the sport type at this point. 2 queries seems to be the answer. – Andrew Brown Mar 13 '13 at 19:27
1

Check this Stack Overflow answer for options on how to do it via a standard query. Then you can turn it into active record if you want (though that may be complicated and not worth your time if you don't need DB-agnostic calls in your app).

Fow what it's worth, there's nothing wrong with doing multiple queries, it just might be slower than an alternative. Try a few options, see what works best for you and your app.

Community
  • 1
  • 1
Aken Roberts
  • 13,012
  • 3
  • 34
  • 40
  • thanks for the link. I think that is getting closer to an answer, and it brings me back to something else I had thought of. Is it possible to use a field name as the table name in a join? i.e. SELECT `tableName` FROM `game` as g LEFT JOIN g.`tableName` as newTable. is this possible? btw, I am currently using AR throughout the site – Andrew Brown Mar 13 '13 at 18:39
  • No, you can't. Best you can do is something like this (which comes with its own caveats): http://stackoverflow.com/questions/8171592/mysql-join-tables-based-on-column-data-and-table-name – Aken Roberts Mar 13 '13 at 18:56