How to base a loop on columns instead of rows?

Question

I have a tournament/ladder script and the DB contains a separate table for every tournament. For each round in the tournament there is a "position" and "score" column. So for example, if I was running a three round tournament you would have the following columns in that table:

teamid
name
round1pos
round1score
round2pos
round2score
round3pos
round3score

Now in the admin control panel I have the following code in the "team management" section to allow admins to change these values in the event they need to regress a player or substitute or something of that nature:

        $stats2=mysql_query("SELECT * FROM tournament_$tournamentid WHERE teamid='$team2[id]'");
        $stats2=mysql_fetch_array($stats2);

        $tournament=mysql_query("SELECT * FROM tournaments WHERE id='$tournamentid'");
        $tournament=mysql_fetch_array($tournament);

        $team_stats="
        <div id='dashboard'>
            <h2 class='ico_mug'>" . LANG_MAN_TOURNAMENT_STATS . "</h2>
            <div class='clearfix'>
                <table class='ucp_fields' cellpadding='4' cellspacing='3' border='0' align='center' width='640px'>

                    <tr valign='top'>
                        <td align='center'>" . LANG_MAN_ROUND . " 1 " . LANG_MAN_POSITION . "</td>
                        <td class='alt1' align='center'>
                            <input type='text' name='team[round1pos]' value='$stats2[round1pos]' size='40' maxlength='5' />
                        </td>
                    </tr>

                    <tr valign='top'>
                        <td align='center'>" . LANG_MAN_ROUND . " 1 " . LANG_MAN_SCORE . "</td>
                        <td class='alt1' align='center'>
                            <input type='text' name='team[round1score]' value='$stats2[round1score]' size='40' maxlength='5' />
                        </td>
                    </tr>

                    <tr valign='top'>
                        <td align='center'>" . LANG_MAN_ROUND . " 2 " . LANG_MAN_POSITION . "</td>
                        <td class='alt1' align='center'><input type='text' name='team[round2pos]' value='$stats2[round2pos]' size='40' maxlength='5' />
                        </td>
                    </tr>

                    <tr valign='top'>
                        <td align='center'>" . LANG_MAN_ROUND . " 2 " . LANG_MAN_SCORE . "</td>
                        <td class='alt1' align='center'>
                            <input type='text' name='team[round2score]' value='$stats2[round2score]' size='40' maxlength='5' />
                        </td>
                    </tr>
                </table>
            </div>
        </div>";

Now obviously I'm calling each position/score manually... But what I would like to do is provide the positions and values for every round within the tournament for that specific team. I would know how to do this if it were rows, but how does it work for columns? Since each tournament can differ in the amount of rounds, I need to make sure it only shows the fields that are available.

before continuing implementing any of the above, please take the time to read http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php — francisco.preller, Jun 28 '13 at 06:10
@francisco.preller Thanks! Definitely a good read... Unfortunately, I'm working off of an already existing script. Eventually I will get around to making its use much cleaner, for now, it just needs to work. — ThatTechGuy, Jun 28 '13 at 20:41

score 2 · Answer 1 · answered Jun 27 '13 at 03:02

2

In a roundabout way breaking the rules of normalazation by keeping lists in your database.

What you should do have a table of tournaments and another table of scores/positions.

So:

Tournaments: tournament_id tournament_name

Teams: team_id team_name

Scores: team_id tournament_id round_num round_score round_pos

So to get the scores for a specific team in a specific tournament, use

SELECT * FROM scores WHERE team_id=TEAM_ID AND tournament_id=TOURNAMENT_NAME

This way, you don't have to deal with the mess of creating a new table for every tournament which can become unwieldy.

answered Jun 27 '13 at 03:02

Lee Avital

542
6
15

I agree with you 100%! Unfortunately, I didn't create this script, it was purchased. It would be way too difficult to change the entire structure of the DB and the code. The tables are created automatically and although it's not the best way it works. However, I am working on building a new script of my own on Code Ignitor framework which will be much different, but in the meantime, is there a way to check columns? – ThatTechGuy Jun 27 '13 at 03:23
Ach, sounds terrible! I think you best bet right now is doing a `SELECT *` and iterating through the row in PHP. You could go the route of reading table schema from information_schema, but that seems like more trouble than it's worth. – Lee Avital Jun 27 '13 at 04:51
Hmmm.... Ok, so like `SELECT * FROM tournament_id` then do loop where it checks to see if a variable is there for each row using a counter and a limit `for ($i=1; $i<=8; $i++) { $check = (mysql query blah all that) SELECT round".$i."pos FROM tournament_id (mysql blah) if (!$check && $check!= 0) { $rcheck[$i] = true; }else{ $rcheck[$i] = false}}` Then using the boolean values I can run through using `count(in_array(true,$array,true))` to figure out which one is false? – ThatTechGuy Jun 27 '13 at 06:06
I entered full code in an answer, do you mind giving it a once over for me? – ThatTechGuy Jun 27 '13 at 09:00

ThatTechGuy · Accepted Answer · 2013-06-28T06:06:42.887

Alright... so this is what I ended up with after some trial and error... works perfectly :) Man that was a lot of work...

                $stats2=mysql_query("SELECT * FROM tournament_$tournamentid WHERE teamid='$team2[id]'");
                $stats2=mysql_fetch_array($stats2);

                $tournament=mysql_query("SELECT * FROM tournaments WHERE id='$tournamentid'");
                $tournament=mysql_fetch_array($tournament);

                $rchkpos = array();
                $rchkscore = array();

                for ($i=0; $i<=$tournament['numRounds']; $i++)
                {
                    $rchkthis = array("$i" => "round".$i."pos");
                    $rchkthis2 = array("$i" => "round".$i."pos");

                    $rchkpos = array_merge($rchkpos, $rchkthis);
                    $rchkscore = array_merge($rchkscore, $rchkthis2);

                    if ($rchkpos[$i] && $rchkpos[$i] != NULL && $i > 0)
                    {
                        $roundpos = $rchkpos[$i];
                        $roundscore = $rchkscore[$i];

                        $team_stats.="
                        <tr valign='top'>
                            <td align='center'>" . LANG_MAN_ROUND . " $i " . LANG_MAN_POSITION . "</td>     
                            <td class='alt1' align='center'>            
                                <input type='text' name='team[".$rchkpos[$i]."]' value='$stats2[$roundpos]' size='40' maxlength='5' />
                            </td>
                        </tr>

                        <tr valign='top'>
                            <td align='center'>" . LANG_MAN_ROUND . " $i " . LANG_MAN_SCORE . "</td>
                            <td class='alt1' align='center'>
                                <input type='text' name='team[".$rchkscore[$i]."]' value='$stats2[$roundscore]' size='40' maxlength='5' />
                            </td>
                        </tr>";
                    }
                }

How to base a loop on columns instead of rows?

2 Answers2