database design help mysql

craptakular · 1 Feb 2012 at 20:58

Am I going to run into trouble when I join 10 tables and then attempt to select columns that aren't in all tables?

I want to select only 5 of the last rows on 10 tables, but some of the columns don't exist in all tables.

Is this a problem?

craptakular · 1 Feb 2012 at 21:44

I suppose I could select the Nth row when all tables are joined instead of worrying about this?

At most I would want to do a top10, so that's 10 separate queries I guess....

RobH · 1 Feb 2012 at 21:53

Could you post an example of what you're trying to do?

craptakular · 1 Feb 2012 at 22:11

Ok, lets say I am a project manager and I have to document how much I spend for each month a project is alive:

Project 1

Jan--Feb--Mar---timestamp
10---20---22----

Project 2

Jan--Feb--Mar--Apr----timestamp
12---08----14---15

I want to join the two tables and then something like:

while($row = mysql_fetch_array($result))
{
echo $row['Jan'];
echo " " . $row['Feb'];
echo " " . $row['Mar'];
echo " " . $row['Apr'];
echo "<br />";
}

But this will do funky things in the html as project1 doesn't have an Apr column

RobH · 1 Feb 2012 at 22:27

So you have a new table for each project? If you are able to change the database design I would suggest doing so as it would make your life a lot easier. You wouldn't typically join tables like this as they have no relationship to each other. If you detail the sort of thing you are trying to create we can probably point you in the right direction.

craptakular · 1 Feb 2012 at 22:54

it's logging bingo balls so one table that only logs one type of game might have say 8 columns ball1 up to ball8

a second table may have a game with 10balls so will have ball1 column up to ball10 column.

I want to select the last 5 rows by timestamp and print all the rows for balls 1-8 or 10 if it has more balls.

Am I making sense?

fez · 2 Feb 2012 at 00:20

Can you not merge the two and simply leave the excess columns blank. For example if the game with the most balls has 14, you would just create a table with 14 columns and if a game only uses 8, then you just use the first 8 columns and leave the other 6 blank. You can add a column to flag the type of game so you only retrieve those that you want for a particular type of game results.

Then your query for the last 5 results of any type of game would look like.

SELECT * FROM results_table WHERE game_type = '8Ball' ORDER BY timestamp_col DESC LIMIT 5;

As you know the game type you are after you can control how many rows it outputs or simply loop over all columns, check if the column name has "col" in it and the value is not NULL.

For a 10 ball game it will print all 10; for an 8 ball game it will print 8 etc.

Then again I may be missing the point...

craptakular · 2 Feb 2012 at 07:16

would that not leave blank html tags?

i want to put $rows[ball1] - $rows[ball14] into <il. tags so i can style then.

ah so by checking for if its null it will remove them from the array but still print games that have it?

I guess before I print the results I need to check for null values and remove them from there $row arrays?

I could use array_filter, does this work on multi-dimensional arrays?

fez · 2 Feb 2012 at 10:14

There is no point array_filtering the result rows as there are values in there that you won't be printing anyway as they hold things like the type of bingo game the row relates to and the PK.

Just loop over the results (which you want in an associative array so you have the column names) and check if the column name conforms to the "colx" format, if so, check its not null and print it. When you hit the first null value you can consider the row dealt with as all the subsequent columns that follow should be null too.

Does that make sense?

craptakular · 2 Feb 2012 at 12:27

I think so. I'm new to all this. The rows will be associative, so do I do foreach loop if not null

fez · 2 Feb 2012 at 12:59

Efffectively

foreach($assoc_results_arr as $row)

foreach($row as $col_name => $value)

if $col_name follows the format "colX", if so, check if its not null

print the result

endif

endforeach

endforeach

Thats a basic summary of what to do.

djkav · 2 Feb 2012 at 15:20

Here would be my schema

Code:

Table #1 : BingoGames

BingoGameID
GameName
TotalBalls
DateCreated
DateEdited

Table #2 : GameResults

GameResultID
BingoGameID
GameTime
GameDate
DateCreated
DateEdited

Table #3 GameResultBalls

ResultBallID
GameResultID
BallNumber 
BallValue
DateCreated
DateEdited

Relationships

BingoGame has a GameResult
GameResult has many GameResultBalls

I know how many balls are used per game (as defined in BingoGames)
I know how many balls i need to loop thru in GameResultBalls from the relationship with BingoGames and GameResults.
Ball results are uniquely related to a GameResult.

I hope that helps in your quest.