PHP Help?

[MVLSports]

Ok, so let me introduce what I'm doing so you may better understand my problem. I may be going about things the wrong way.

I'm wanting to run a sports website using MySQL and PHP. I want to be able to enter a master schedule with scores into the database and then use that master schedule and list of scores to generate individual pages for each school in the coverage area. I plan on setting up tables for each sport (football2011, volleyball2011, etc., for example). So I have no problem returning the data that I enter. The problems that I have are two-fold.

Here are the two "test" files I'm using:
ohioprimetime.com/test.php (Master schedule and results)
ohioprimetime.com/zanesville.php (School schedule and results)

1. How do I display the result? I can't enter win or loss into the master schedule (or can I and not display it?). At any rate, I currently have a bunch of IF statements that will display the results. So here are some notable variables:

$f1=mysql_result($result,$i,"month");
$f2=mysql_result($result,$i,"day");
$f3=mysql_result($result,$i,"year");
$f4=mysql_result($result,$i,"hometeam");
$f5=mysql_result($result,$i,"homescore");
$f6=mysql_result($result,$i,"awayteam");
$f7=mysql_result($result,$i,"awayscore");

So to create a "Win" or "Loss" result, here is my statement (Zanesville is the "dummy school" I'm using for this example):
<?php if ($f4=="Zanesville" && $f5>$f7) echo "Win"; if ($f4=="Zanesville" && $f7>$f5) echo "Loss"; if ($f6=="Zanesville" && $f7>$f5) echo "Win"; if ($f6=="Zanesville" && $f5>$f7) echo "Loss"; ?>

Is there another way I should look at doing this?

2. My second problem is I'd like to have the win-loss record updated automatically after each game. I figure I need to use a loop, but I'm not sure on this. I also don't have any experience in using a loop. I'm in a class learning how loops work but they've been simple loops thus far, I'm thinking this is a bit more complicated.

Any advice here is appreciated.

I can provide more code if needed.

Thanks,
mvlsports

For #2, if I'm wanting to use that win-loss data for a standings file, should I create a table containing that information separately?

[weegillis]

If you wish to display "Record 3-2" above your table, then you will need to parse your data first, tabulate and accumulate to '$win' and '$loss' accordingly. Then echo the entire table.

In actuality, since 'echo' is so slow, repeated calls take much longer than one call for an entire html block so it makes good sense to build your table in advance.

[weegillis]

<?php 

//  push all data into an array so you can release MySQL

$array = array();

// make sure to index it so you don't have keys tripping over each other

$table = <<< HDR

<table border="0" cellspacing="4" cellpadding="4" width="100%">
<tbody>
<tr>
  <td><u><b>Date</b></u></td>
  <td><u><b>Home Team</b></u></td>
  <td><u><b>Home Score</b></u></td>
  <td><u><b>Away Team</b></u></td>
  <td><u><b>Away Score</b></u></td>
</tr>

HDR;

// now cycle through $array, extracting the seven datum in each row and generating appropriate HTML

$win = $loss = 0;

foreach($array as $key => $value) {

 foreach($value as $v) {

  $table .= "<tr>\n";

  $table .= "<td>$v[0]</td>\n<td>$v[1]</td>\n<td>$v[2]</td>\n<td>$v[3]</td>\n<td>$v[4]</td>\n<td>$v[5]</td>\n<td>$v[6]</td>\n";

  $table .= "</tr>\n";

  $win .= ($v[4]>$v[6]) ? 1 : 0;

  $loss .= ($v[4]<$v[6]) ? 1 : 0;
 }
}

$table .= "</tbody>\n</table>\n";

$table = "<p>Record: " . $win . " - " . $loss . "</p>\n" . $table;

echo $table;

 ?>

[weegillis]

We may find the above is a gotcha in that it is over complex, and overkill. Here is something simpler that should do the job: (This is scratch code, not tested.)

<?php 

//  push all data into an array so you can release MySQL

$array = array();

// make sure to index it so you don't have keys tripping over each other

$table = <<< HDR

<table border="0" cellspacing="4" cellpadding="4" width="100%">
<tbody>
<tr>
  <td><u><b>Date</b></u></td>
  <td><u><b>Home Team</b></u></td>
  <td><u><b>Home Score</b></u></td>
  <td><u><b>Away Team</b></u></td>
  <td><u><b>Away Score</b></u></td>
</tr>

HDR;

// now cycle through $array, extracting the seven datum in each row and generating appropriate HTML

$win = $loss = 0;

foreach($array as $key => $v) {

 // record index = $key

  $table .= "<tr>\n";

  $table .= "<td>$v[0]</td>\n<td>$v[1]</td>\n<td>$v[2]</td>\n<td>$v[3]</td>\n<td>$v[4]</td>\n<td>$v[5]</td>\n<td>$v[6]</td>\n";

  $table .= "</tr>\n";

  $win .= ($v[4]>$v[6]) ? 1 : 0;

  $loss .= ($v[4]<$v[6]) ? 1 : 0;

}

$table .= "</tbody>\n</table>\n";

$table = "<p>Record: " . $win . " - " . $loss . "</p>\n" . $table;

echo $table;

 ?>

As loops go, the above may be described as a self indexing end-of-file (EOF) loop. It doesn't need to be told how many records there are (though it will find out on its own). It just starts at the first row in $array and keeps going until it runs into what used to be called the 'first non-existent row', or EOF. Of course we need an array, and if you examine the above foreach() you'll see that the array is set up with an index (0,1,..,n) and a corresponding array within each row.

Eg.

<?php 

$array = array();

// cycle through database table to build each row

while(!$EOF) {

// MySQL retrieve $f1..$f7

$array[] = array($f1,$f2,$f3,$f4,$f5,$f6,$f7);
}
 ?>

If this is ahead of what you're being taught, don't use it. You'll get your wings clipped. Stay on par with the course material.

[xtempore]

There are a number of areas that I think you can improve, both for performance, maintainability, and overall code-stability.

1. Database design

DON'T create separate tables for each sport/year. You are just creating extra work for yourself in the long run.

You should have one table that lists your sports. It can be very simple, just an autoincrement id field, and the sport name.

You then have a second table that holds your results, with a field that maps to the id of the sport, a field for the year, and then whatever fields you need for the result information. Then to extract the data you just need to make sure you include criteria for the sport (by id), and the year, in the WHERE clause.

You should similarly have tables for the teams, and then only include their id in your data.

By doing this you make your data more reliable, and most of your coding of forms etc will actually be easier.

2. Variable names

Use MEANINGFUL variable names, your code will be much more readable, and less prone to errors.

If a field contains the name of the home-team call it &quot;$hometeam&quot; or &quot;$fHomeTeam&quot;, not &quot;$f4&quot;.

3. Use appropriate MySQL functions

There are a few to choose from. For a nice clean piece of code, I'd suggest you use mysql_fetch_row along with list(). E.g. ...

list($month, $day, $year, $hometeam, $homescore, $awayteam, $awayscore) = mysql_fetch_row($result);

One thing to consider with this, is you need to be clear about the order that your fields are returned from the query. I suggest explicitly naming the fields in the SQL query. E.g.
SELECT month, day, year, hometeam, homescore, awayteam, awayscore FROM ...

You should also consider whether a DATE field would be a better choice rather than 3 separate fields (YEAR, MONTH, DAY).

Hope that helps.

[williamc]

I agree with weegillis what he suggested to you, but keep in mind always use Adobe Dreamweaver and follow w3schools

Did you even read what was being discussed? In this context:

1) Dreamweaver is less than useless.
2) w3schools info is consistantly incorrect.