#1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2007
    Location
    Glendale AZ
    Posts
    183
    Rep Power
    93

    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:
    PHP Code:
    <?php for ($i 1$i <= 5$i++) {
        if (!empty(
    $tourney["b{$i}"])) { ?>
            <div class="createCellIndent">$<?php echo number_format($bounty_value["{$tourney["b{$i}"]}"],0)." - ".h($bounty_desc["{$tourney["b{$i}d"]}"]); ?></div>
        <?php }
    ?>
    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...
    PHP Code:
    for ($i 1$i <= 5$i++) {
        if (${
    'b'.$i.'d'} != 0) {
            
    $selected False;
            
    $select "SELECT bounties_desc.description, bounties_value.value FROM bounties_desc, bounties_value WHERE bounties_desc.bounty_no = ? AND bounties_value.bounty_no = ?";
            if (
    $stmt $conn->prepare($select)) {
                
    $stmt->bind_param('ii', ${'b'.$i.'d'}, ${'b'.$i});
                if (!
    $selected $stmt->execute()) {echo $stmt->error;}
                
    $stmt->bind_result($description$value);
                while (
    $stmt->fetch()) {
                    
    $bounties[$i] = array(
                    
    'no' => ${'b'.$i.'d'},
                    
    'desc' => $description,
                    
    'value' => $value
                    
    );
                    
    $bounties_total += $value;
                }
                
    $stmt->close();
            }
            
    $bountiesNo count($bounties);
            if (!
    $selected) {$note[] = "There was a problem retrieving the bounty values and descriptions.";}
        }

    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?
  2. #2
  3. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,064
    Rep Power
    9398
    Originally Posted by big0mike
    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?

    Comments on this post

    • ptr2void agrees
    • ManiacDan agrees
  4. #3
  5. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2007
    Location
    Glendale AZ
    Posts
    183
    Rep Power
    93
    Originally Posted by requinix
    Thinking that variable variables will make things easier. Really, just stick with normal variables. Actually an array might be nicer.
    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.

    Originally Posted by requinix
    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?
    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.
  6. #4
  7. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,064
    Rep Power
    9398
    Originally Posted by big0mike
    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.
    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:
    Code:
    WHERE b1=value OR b2=value OR b3=value OR b4=value OR b5=value
    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.)

    Originally Posted by big0mike
    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.
    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?
  8. #5
  9. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    The expression
    PHP Code:
    ${'b'.$i.'d'
    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.
  10. #6
  11. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2007
    Location
    Glendale AZ
    Posts
    183
    Rep Power
    93
    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...
  12. #7
  13. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,064
    Rep Power
    9398
    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.
  14. #8
  15. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2007
    Location
    Glendale AZ
    Posts
    183
    Rep Power
    93
    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!
  16. #9
  17. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,064
    Rep Power
    9398
    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?
  18. #10
  19. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2007
    Location
    Glendale AZ
    Posts
    183
    Rep Power
    93
    `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.

IMN logo majestic logo threadwatch logo seochat tools logo