Table layout using php/mysql to extract golf statistics

[daddyg]

I'm trying to produce a table layout to display the finish position stats like this: http://www.golfbettingguide.com/cont...le-layout.html

The above is the layout I want. The problem I'm having is putting it all together when I'm actually extracting the finishing position results from my mysql database for two golfers.

I'm using a foreach loop to extract the finish position results for each golfer in turn. However my first attempt hasn't produced the result I'm looking for.

When I enter two golfers names in the text box the output is displayed in two seperate tables, rather than one table with a column for each golfers results as per the page in the link at the top.

You can see what I mean here (excuse the messy layout for the form, it's just a test page) if you type in Tiger woods and Ernie els for example:
http://www.golfbettingguide.com/cont...guide-test.php

Here's what my php code looks like:

if(isset($_POST['golferentry#1']))
{
    
    $golfers = array();  //create empty array of golfers - see pg 110 of sitepoint book
    $golfers[0] = $_POST['golferentry#1'];         //assign first golfer entered into first index of $golfer  
    $golfers[1] = $_POST['golferentry#2'];         //assign first golfer entered into first index of $golfer
    $tour = $_POST['tour'];
    
    
    $ctr = 0;    //set this to 0 to check if first time through foreach loop
    
    foreach($golfers as $golfer)
    {
        $result = @mysql_query("SELECT G.Golfer, T.T_Date, T.Tournament, F.Position FROM tbl_tournaments AS T,
        tbl_golfers AS G LEFT OUTER JOIN tbl_finish_pos AS F ON T.ID = F.TournamentID AND F.GolferID = G.ID
        WHERE T.Tour = '$tour' AND G.Golfer = ('$golfer') ORDER BY T.ID DESC, G.Golfer DESC LIMIT 10 ");
    
        if (mysql_num_rows($result) == 0)
        {
             echo ("



The golfer name $golfer does not exist in the database. Please check your spelling or try with a different name</p>
");
            
        }
        


        
        else if($result && ctr == 0) // if first iteration of foreach loop and valid result, echo top of table
        {

        
            echo ("<table id=\"tour_results\" cellpadding=\"0\" cellspacing=\"1\">
            
                    <tbody><tr>
                                <th colspan=\"4\" id=\"golf_form_title\">Golfer Form Guide</th>
                    </tr>

                    <tr class=\"golf_form_hdr\">

                        <td rowspan=\"2\" class=\"width_15pc\">Date</td><td rowspan=\"2\" class=\"width_35pc\">Tournament</td><td colspan=\"2\" class=\"width_50pc tops\">Finish Positions for:</td>
                    </tr>
                    
                    
                    <tr>
                      <td class=\"tops\">$golfer</td>
                 ");            
        }
        
        else {
                        die('

Error performing query: ' . mysql_error() . '</p>');
                }
        
        if(mysql_num_rows($result)!=0 && ctr == 1)          //if 2nd iteration of foreach loop and valid result, echo 2nd golfer name
        {    
                                    echo("<td class=\"tops\">$golfer</td></tr>");                     
        }    else if(ctr == 1)
            {
                        echo("<td class=\"tops\"></td></tr>");
            }
        
        
        
    
            
        
            
            while ($row = mysql_fetch_array($result) )
            {
                $Golfer = $row['Golfer'];
                $fp = $row['Position'];
                $Tournament = $row['Tournament'];
                $TournamentDate = $row['T_Date'];
                
                if(!isset($fp))
                {
                  // $var is null (does not exist at this time) and the execution goes here
               $fp = "-";
                }
                
            
                if(ctr == 0)
                {
                    echo('<tr><td class="odds_even">' . date('d-m-y', strtotime($TournamentDate)) . '</td>');
                    echo("<td class=\"odds_even\">$Tournament</td><td class=\"odds_even\">$fp</td>");
                }
                else
                {
                    echo("<td class=\"odds_even\">$fp</td></tr>");
                }
                
                            
                      
            }        
        
        
    $ctr++;         //increment ctr to set it to 1
    }  //end of foreach loop
    
    
    
            echo '</table>';
        
                    
}

else {
        echo ("


A golfer name must be entered in the first box.  Please try again...</p>
");
    }

?>

In writing the code I was trying to achieve these conditions :-
i) The first text box is mandatory but the second is optional, ie you can only enter one golfers name if you want but it must be in the first text box.
ii) If either of the golfers names is mispelt then no results are published, just a validation error telling the user to check the spelling.

I'm really stuck on this. If anyone could give me some advice on how to get both golfers finish position stats in the same table rather than in two seperate ones I'd really appreciated it.

Many thanks.

[Evic]

Me personally, I would do this in 2 queries. Not gonna write out all the code, just some pseudo-ish for you.

$result = mysql_query("SELECT date, tournament, position FROM tournaments WHERE name = '" . $_POST['golfer1'] . "'");
while ($golfer1 = mysql_fetch_array($result)) {
  $date[1][] = $golfer1['date'];
  $tournament[1][] = $golfer1['tournament'];
  $position[1][] = $golfer1['position'];
}

$result = mysql_query("SELECT date, tournament, position FROM tournaments WHERE name = '" . $_POST['golfer2'] . "'");
while ($golfer2 = mysql_fetch_array($result)) {
  $date[2][] = $golfer2['date'];
  $tournament[2][] = $golfer2['tournament'];
  $position[2][] = $golfer2['position'];
}

echo "<table><tr><td>Date></td><td>Tournament></td><td>" . $_POST['golfer1'] . "</td><td>" . $_POST['golfer2'] . "</td></tr>";

$i = 0;

while ($i < = count($date[1])) {
  echo "<tr><td>" . $date[1][$i] . "</td><td>" . $tournament[1][$i] . "</td><td>" . $position[1][$i] . "</td><td>" . $position[2][$i] . "</td></tr>";
  $i++;
}

echo "</table>";

Of course, this code won't work perfectly for you. It doesn't check to see if the two players played on the same date, same tourney, but that's a simple query change (I don't know the structure of your table).

I would probably omit the date and tourney from the seperate array returns and make it part of the query, change the while loop to run off of mysql_num_rows($result).

Actually, now that I think about it this could be done in one query easily... just assign the positions to arrays like I did $position[1][x] and $position[2][x] where x is a counter for the rows you are looping through.

Then do your echos in a while loop like I did above.

Bah, kind of rambling and not even sure if this is going to help. I'll post anyway, maybe it'll get your creative juices flowing and you can come up with something. I know what I would do, but it's hard to give an exact solution w/o seeing how the database is setup.

[daddyg]

Evic,

Many thanks for your reply. I'll go through this at the weekend based on what you've suggested.

In the meantime if its any help, here's how my database is set up.

tbl_finish_pos
Field Type Attributes Null
GolferID smallint(5) UNSIGNED No
TournamentID smallint(5) UNSIGNED No Position varchar(10) No

GolferID and TournamentID are primary keys


tbl_golfers
ID smallint(5) UNSIGNED No Golfer varchar(40) No

ID is primary key and auto increments
Golfer is unique key

tbl_tournaments
ID smallint(5) UNSIGNED No Tournament varchar(75) No
T_Date date No
Tour varchar(5) No

ID is primary key

I'll let you know how I get on at the weekend.

Thanks again.

[daddyg]

I've managed to make some progress on this which you can see at

http://www.golfbettingguide.com/cont...uide-test2.php

Enter two golfers names eg Tiger Woods and Vijay Singh and the results display correctly.

However enter just one golfer in the first box and the table truncates before any of the finishing positions get output. Here's what my code looks like:-

if(isset($_POST['golferentry#1']))

{
    
    $golfers = array();  //create empty array of golfers - see pg 110 of sitepoint book
    $golfers[0] = $_POST['golferentry#1'];         //assign first golfer entered into first index of $golfer  
    $golfers[1] = $_POST['golferentry#2'];         //assign first golfer entered into first index of $golfer

    $result =  array();                           //set up array of results
    $tour = $_POST['tour'];
    
    
    $ctr = 0;    //set this to 0 to check if first time through foreach loop
    
    foreach($golfers as $golfer)
    {
        $result[$ctr] = @mysql_query("SELECT G.Golfer, T.T_Date, T.Tournament, F.Position FROM tbl_tournaments AS T,
        tbl_golfers AS G LEFT OUTER JOIN tbl_finish_pos AS F ON T.ID = F.TournamentID AND F.GolferID = G.ID
        WHERE T.Tour = '$tour' AND G.Golfer = ('$golfer') ORDER BY T.ID DESC, G.Golfer DESC LIMIT 10 ");
    
        if (mysql_num_rows($result[$ctr]) == 0)
        {
             echo ("



The golfer name $golfer does not exist in the database. Please check your spelling or try with a different name</p>
");
            
        }
        
        if(!$result)
        {
                        die('

Error performing query: ' . mysql_error() . '</p>');
        }
        
        $ctr++;
        
    }

    
    
    
    

        
        
        
    echo ("<table id=\"tour_results\" cellpadding=\"0\" cellspacing=\"1\">
            
                    <tbody><tr>
                                <th colspan=\"4\" id=\"golf_form_title\">Golfer Form Guide</th>
                    </tr>

                    <tr class=\"golf_form_hdr\">

                        <td rowspan=\"2\" class=\"width_15pc\">Date</td><td rowspan=\"2\" class=\"width_35pc\">Tournament</td><td colspan=\"2\" class=\"width_50pc tops\">Finish Positions for:</td>
                    </tr>
                    
                    <tr><td class=\"tops\">$golfers[0]</td><td class=\"tops\">$golfers[1]</td></tr>
            ");            
        
        
            $ctr = 0;
            
            
                while ($row = mysql_fetch_array($result[$ctr]) )
                {
                    
                        $Golfer = $row['Golfer'];
                        $fp = $row['Position'];
                        $Tournament = $row['Tournament'];
                        $TournamentDate = $row['T_Date'];
                    
                
                    if(!isset($fp))
                    {
                      // $var is null (does not exist at this time) and the execution goes here
                   $fp = "-";
                    }
                
            
                    if($ctr == 0)
                    {
                        echo('<tr><td class="odds_even">' . date('d-m-y', strtotime($TournamentDate)) . '</td>');
                        echo("<td class=\"odds_even\">$Tournament</td><td class=\"odds_even\">$fp</td>");
                        
                    }
                    
                    if($ctr > 0)
                    {
                        if(isset($row))
                        {
                            echo("<td class=\"odds_even\">$fp</td></tr>");
                            $ctr = 0;
                        }
                        
                        else
                        {
                            echo("<td class=\"odds_even\"></td></tr>");
                            $ctr = 0;
                        }
                
                    }
                    else if ($ctr == 0)
                    {
                            $ctr++;    
                    }
                    
                    
                        
                }    
                    
            echo '</table>';
                    
}

Fyi, the 1st line:-
if(isset($_POST['golferentry#1']))

is in there because I wanted the first box to be mandatory but the second optional.

If anyone could help I'd appreciate it.

Also, as a php newbie I'm not sure if the way I've coded this is efficient or whether there are better ways so I'd appreciate any comments from the php gurus out there.

Many thanks for any help.[/code]

[wsmeyer]

What about instead of typing in the name you use a select box. That would eliminate typos and you could set the first option as "select player" with a value of zero.

William.

[daddyg]

I want to use text boxes for now because I want users to be able to access the stats quickly, and only having the option to find a golfer via drop downs would slow the process and be frustrating if you know how to spell a golfers name.

I do actually want to use drop downs eventually as a second alternative method where someone can't remember a spelling for a golfer.

[Evic]

Why not take the Google Suggest method and use a combination text entry and drop-down? Would require some fancy Javascript (I think that's what it is in, haven't really looked), but would be damn awesome I think. Best of both worlds.

[Evic]

Or another option is to do a Levenshtein/SoundEx comparison.

The soundex() function represents how a word is spoken via a string of characters.

The levenshtein() function represents a numerical distance between two strings.

Using a combination of the two functions, you can create a rather powerful "return best result" search engine.

<?
// Get the name they entered
$golfer[1] = $_POST['golferentry#2'];
// Return a list of all golfers in db
$sql = @mysql_query("SELECT Golfer FROM tbl_golfers");
// This var will hold the distance between the two strings, for our best match throughout the loop
$shortest = -1;

// Begin looping through Golfers in DB
while ($row = mysql_fetch_array($result)) {
  // Get the Levenshtein distance between how the two golfers name's are pronounced
  $lev_dist = levenshtein(soundex($golfer[1]), soundex($row['Golfer']));

  // Do we have an exact match? If so, store it in the vars and get out of the loops
  if ($lev_dist == 0) {
    $closest = $row['Golfer'];
    $shortest = 0;
    break;
  }

  // Is this lev distance shorter than the last? If so, it's a better match, store it in the vars and continue on to the next comparison
  if ($lev_dist <= $shortest || $shortest < 0) {
    $closest = $row['Golfer'];
    $shortest = $lev_dist;
  }
}
echo "Closest Match is: " . $closest . "
";
?>

Using this method golfer#2 will never be null, because it will always return the closest match. This can be a good and/or bad thing. If you're only golfer is Tiger Woods in the database, and the user searches for Marilyn Manson - Tiger Woods will be the closest found and returned. This form of search (and any search method) is only as good as the info in the database. The more golfers you have, the better the chances of the user finding who they want.

EDIT: Commented code

[daddyg]

Thanks for both of those ideas. I particularly like the Google suggest idea...I think it's exactly what i'm after. Damn awesome indeed :)

I've found a tutorial on implementing it http://www.phpriot.com/d/articles/ph...xac/page1.html
which should hopefully get me up and running.

I'll let you know how I get on.

Thanks again.