PHP5 - Normalizing my database [Was PHP: "Variable variables issue"]

I'm missing something on this variable variable issue...

The data I'm pulling from the database is in 10 columns: b1d, b1, b2d, b2, b3d, b3, b4d, b4, b5d, b5

I've found that pulling them from the database and assinging them to variables of the same name as their column makes it less confusing and all around easier. I've successfully got the data working in a loop on another page:
using braces but it's inside of an array so I'm not sure if the way I access it should be different since it's not in an array.

What I've got now is the results of a select binding to $b1d, $b1, $b2d, $b2...
What you see is my last attempt, and failure, at it before I had to head off to work. I've tried many different iterations of moving the "" and '' and {} around with no luck. This last version is closer as it gets past what is line 2 above and chokes on line 6 (bind_param line).

I did some searching here and the threads I found gave me the idea of using ${'b'.$i.'d'} but most of them are focused on variable variables like $a1, $a2, $a3, with nothing after the variable number. I'm sure there's a way to get it done (since I did it in the array) but I'm at a loss currently.

What's my dumb mistake this time?

Thinking that variable variables will make things easier. Really, just stick with normal variables. Actually an array might be nicer.

Or even better it sounds like that table needs some normalization. What's the point of those 5*2 columns? Do you always use all of them? Is there a chance you might want a sixth pair in the future?

Yeah, an array would definitely be nicer. And, I may wind up rewriting this page to throw it all in an array. But, now that I've encountered the problem I'd like to solve it for future reference.

I don't always use all of them but I've designed the program so that there will be up to 5 bounties available.

I'm sure I don't fully (maybe not even at all) understand normalization but I thought that was what I was doing when I made those 10 columns so that they would only contain the reference to the secondary table that has the values for them instead of having the bounty description and value directly in the tourney table.

In any case, I'd still be interested in figuring out how to make this page work as is so I can put it in my ever-growing list of "how the hell did I do that" scripts.

That's one step in the right direction but there is a long path still ahead. (I say "long" to emphasize that there's a lot to normalization, not that you have to do a lot of work to have a good database.)

The next step is realizing that you're storing the same type of data in multiple columns. Using foreign keys is great but your queries will still be complicated if you wanted to, say, search:

That's a lot of redundancy. The solution is to make another table which contains (1) the primary key of the original table you're pulling stuff from and (2) whatever information you're duplicating in all the columns. So here that's presumably three columns: tourney ID , the bounty ID, and its description. One row for each bounty, so five bounties for a tourney means five rows.

And another thing: that description. Is it always tied to the bounty itself? Does a particular bounty always have the same description associated with it? If so then you need to move the description into the bounty table - thus avoiding duplicating that everywhere it's needed.
(But if the description varies everywhere then keep it where it is.)


Fair enough.

Trying something similar to your code with 5.3.10 it works as expected. What version of PHP are you running? How does it "choke" on that line?

Hi,

The expression

actually works on my computer (PHP 5.3.8). What's the error message you get? Is it a syntax error?

Anyway, I must agree with requinix that this is rather a symptom of inappropriate database design. The bounties should be in a separate table with a reference to the main table.

Off the top of my head I don't remember the error and after I got to work as I was writing the post I knew I should have emailed myself that bit of info... I'll post it when I get home.

Any one of the 5 bounties can have any number of descriptions or values. Currently there are 8 different bounty descriptions (in bounty_desc table) and ~15 values (in bounty_value table). Every place I reference the descriptions and values I pull the data using a loop which is based on the count of each.

On normalization... this was the first database I've built from the ground up so I thought I was doing good. I tried to "compartmentalize" things as best I could think of but not knowing where I was going (or what I was doing) I certainly have not done as well as needed.

So, you're saying I should have
`tourney`
tourney, tourney_date, start_time, description, players, chip_count... [no mention of bounties]
`bounties`
tourney, bounty_desc, bounty_value
... multiple rows; one for each bounty in the tourney
`bounty_desc`
bounty_desc, description
`bounty_value`
bounty_value, dollarvalue
?

Basically, ditch the list of bounties in tourney and my queries pull the bounties (an unlimited number of them) from a separate table where tourney = the tourney in question. I actually fought with how to do the bounties 'cause I didn't necessarily want to limit it to 5 even though I have no intention of using more than 2 or 3 myself.

I really like your idea. But, damn, it's gonna make me rewrite a lot of pages...

That looks right, yes.

I'm sure you'll find things are a bit easier this way, even if you need to spend some time up front switching over to it. And you can still enforce the limit of 5 bounties if you wish, it's just that you can change your mind without having to worry about your database.

I'll really have to work on my JOINs... Ideally, I'd like to have every page that needs tourney info to have it all in one array. That means joining from 5 different tables: tourney, bounties, bounty_desc, bounty_value, payout_structure. I've done two in an ugly way (a lot of WHERE table1.column1 = table2.column1) but I know using the actual JOIN statement is probably easier to use and read.

So, how far does one take this "normalization?" When I remove the bounties from the tourney table I'll have 13 columns, most of which can have any number of similar definitions. Should I make a table for buyins with a list of dollar values (like I did with bounty_value)? Possibly combine those two and just have one table `dollar_value` and reference it for both uses? The starting chip count and maximum players will vary... another set of tables?

My thoughts where to design it with as few tables as practical but still keep as much compartmentalized as needed. I know part of my problem when I started was I wanted to be able to have a look in phpMyAdmin and be able to actually read everything there and not have to reference multiple tables to see what each tourney meant. I'm sure that's a very novice approach (I was/am) but I did start to steer properly on some accounts.

I'm working on the last bit of functionality for the site now so I'll finish it and ensure it's working then make a new virtual host and start "fixing" it. I started it about 3 years ago and took 2 years off with almost no coding of anything in between. When I came back and started coding new pages I found myself thinking two things about a lot of the pages: 1. I have no idea what I did here but it works. 2. Why did I do it this way?

And, I couldn't repeat the error in my code. Just checked this morning and it works fine. I tried it at work last night (have Xampp with 5.3.8 at work and home) and it worked there and thought, "great, it's gonna work when I get home, too." I guess my refresh in Chrome didn't quite refresh it.

Thanks for the insight!

Moved, since this turned into a question about normalization and not PHP syntax.

Can you post the table structures with a (very) brief explanation of the columns?

`tourney`
tourney: tourney number (auto increment) and primary key
tourney_date: ...
title: ...
description: brief description
details: long description
start_time: ... (varies)
chip_count: starting chips (varies)
buyin: fee to play (varies)
rebuy: whether or not rebuys are allows (only Y or N)
players: maximum players allowed
cancelled: if the game has been cancelled
finished: if the game has finished
b1d... all those that will be deleted eventually

`donkeys`
donkey: player number (auto increment) and primary key
last_name:
first_name:
nick_name:
email: (must be unique)
password: sha256 hashed
phone:
joined: date joined
photo: url to photo (future implementation)
approved: whether or not I've approved them to play (only Y or N)
invite: whether or not I should invite them (only Y or N)
admin: whether or not they have admin priveliges

`bounty_desc`
bounty_no: primary key (auto increment)
description:

`bounty_value`
bounty_no: primary key (auto increment) (and maybe confusing 'cause it has no relation to bounty_desc.bounty_no)
value: dollar value of bounty

`bounties` (needs to be renamed bounties_won to accomodate bounties being the list of bounties referencing bounty_desc/value)
tourney: primary key
donkey:
description: (reference bounty_desc)
value: (reference bounty_value)

`payout_structure`
payout_no: primary key (auto increment)
description:
min_players:
max_players: (creates a range for determining payouts)
first:
second:
third:
...
tenth: (yeah, looks like this one needs help too)

`invite`
invite_no: (auto increment) (generic index and likely needs to be removed)
tourney:
invite_date:
donkey:
last_name:
first_name:
email: (yep, no need for the names or email here)
invite_code: hashed code that gives them a specific link for themselves (delivery via ?code= in the URL)

`signed_up` (used to help identify people that don't respond as well as list those that can play)
tourney: primary key
donkey: (tourney & donkey must be unique combo)
no_players: (0: can't play; 1: can play)
signup_date:
noshow: did they not show up?

`signin`
tourney: primary key
donkey: (tourney & donkey must be unique combo)
time_in: when they arrived

`busted_out`
bo_index: primary key (auto increment) (and probably useless not that I'm thinking about it; to the best of my recollection I don't reference it for anything.)
tourney: should be primary key
donkey: (tourney & donkey must be unique combo)
time_in: tourney start time or signin time, whichever is later
time_out: when they busted out

`finished` (after everyone has busted out there's a finished button which populates this data and calculates points & cash)
tourney: primary key
donkey: (tourney & donkey must be unique combo)
placed: what position they finished in (tourney & placed must be unique combo)
time_in:
time_out: (yeah, duplicated. Thought it easiest if it's all in one place)
cash: amount won
points: points earned
b1d, b1... (yep, here they are again)

As you can see from some of my comments after looking at it all together I can see some of my mistakes. Some of the insertions of last_name & first_name into tables comes from my wanting to be able to see the data clearly in phpMyAdmin while I'm building the site. For a while I was still struggling with the reading in inserting of data so I wanted to know the correct data was being inserted without having to consult 2, 3 or more tables. If I were more comfortable with JOINs there likely wouldn't be such an issue.

Yeah, I'm real close to finished with it and I've already got a lot of work ahead of me fixing this thing... Oh well, I did this as a learning experience I suspect I'll be learning every time I sit down to code.