There's got to be a better way...

[BelowZero]

I'm new to this, but I'm learning...

 

I manage a football season pool and I'm writing a website just so I can teach myself how. I have a page that works the way I want it, but I fear my coding is terribly inefficient. I was hoping someone could look at it and give me some pointers.

 

On this page, I'm displaying all the matchups for a particular week and then using a form to add  point spreads.

In the database, 1 table is used to store team names, and another table for all the weekly data. I'm using 2 queries, 1 to retrieve a home team and another to get the away team, then repeating the code up to 16 times to accommodate for all the games in a given week. Surely there's a better way to do it.

 

Here's what the page looks like:

www.beat-the-spread.net/admin_spreads.php

 

 

And here's the partial code:

 

<?php include("opendatabase.php"); ?>

<FORM Method = "POST" action ="insert_spreads.php">   

<?php
   
   $result = mysql_query("
   SELECT team_name 
   FROM schedule, teams 
   WHERE schedule.week_id='1' 
   AND schedule.game_id='1'
   AND schedule.A_team=teams.team_id");
   
   $resultb = mysql_query("
   SELECT team_name 
   FROM schedule, teams 
   WHERE schedule.week_id='1' 
   AND schedule.game_id='1'
   AND schedule.H_team=teams.team_id");
?>
<input type="text" size = "5" name="w1g1Aspread">

   <?php
   $row = mysql_fetch_array($result);
     echo " ".$row['team_name'];
   echo " vs. ";
   $rowb = mysql_fetch_array($resultb);
   echo " ".$rowb['team_name'];
   ?>
<input type="text" size = "5" name="w1g1Hspread">
<br /><br />

<?php
   
   $result = mysql_query("
   SELECT team_name 
   FROM schedule, teams 
   WHERE schedule.week_id='1' 
   AND schedule.game_id='2'
   AND schedule.A_team=teams.team_id");
   
   $resultb = mysql_query("
   SELECT team_name 
   FROM schedule, teams 
   WHERE schedule.week_id='1' 
   AND schedule.game_id='2'
   AND schedule.H_team=teams.team_id");
?>
<input type="text" size = "5" name="w1g2Aspread">

   <?php
   $row = mysql_fetch_array($result);
     echo " ".$row['team_name'];
   echo " vs. ";
   $rowb = mysql_fetch_array($resultb);
   echo " ".$rowb['team_name'];
   ?>
<input type="text" size = "5" name="w1g2Hspread">
<br /><br />
<?php
   
   $result = mysql_query("
   SELECT team_name 
   FROM schedule, teams 
   WHERE schedule.week_id='1' 
   AND schedule.game_id='3'
   AND schedule.A_team=teams.team_id");
   
   $resultb = mysql_query("
   SELECT team_name 
   FROM schedule, teams 
   WHERE schedule.week_id='1' 
   AND schedule.game_id='3'
   AND schedule.H_team=teams.team_id");
?>
<input type="text" size = "5" name="w1g3Aspread">

   <?php
   $row = mysql_fetch_array($result);
     echo " ".$row['team_name'];
   echo " vs. ";
   $rowb = mysql_fetch_array($resultb);
   echo " ".$rowb['team_name'];
   ?>
<input type="text" size = "5" name="w1g3Hspread">


<?php
mysql_close($con);
?>
<br /><br />            
<input type="Submit" value="Submit Spreads">

 

It's just the same thing over and over with game numbers changed each time.

Any advice is appreciated!!

[Pikachu2000]

When posting code, please enclose it within the forum's

 . . . 

BBCode tags.

[mikecampbell]

Instead of repeating nearly-identical blocks of code, you could create a function which takes two parameters: week id and game id.

 

insert_spread($week_id, $game_id) {
   $result = mysql_query("
   SELECT team_name
   FROM schedule, teams
   WHERE schedule.week_id='$week_id'
   AND schedule.game_id='$game_id'
   AND schedule.A_team=teams.team_id");
   
   $resultb = mysql_query("
   SELECT team_name
   FROM schedule, teams
   WHERE schedule.week_id='$week_id'
   AND schedule.game_id='$game_id'
   AND schedule.H_team=teams.team_id");
?>
<input type="text" size = "5" name="w1g1Aspread">

   <?php
   $row = mysql_fetch_array($result);
     echo " ".$row['team_name'];
   echo " vs. ";
   $rowb = mysql_fetch_array($resultb);
   echo " ".$rowb['team_name'];
   ?>
<input type="text" size = "5" name="w1g1Hspread">
<br /><br />

<?php

}

insert_spread(1,1);
insert_spread(1,2);
insert_spread(1,3);
...

[KevinM1]

1. Learn about functions.  Functions are reusable portions of code that produce results based on what was passed into them.

 

2. Don't mix and match PHP and HTML unless you need to.  Properly written PHP apps do all of their PHP processing upfront, then display the results.

 

Quick example of both (not tested, but it should give you an idea):

 

<?php
   include("opendatabase.php");

   function createMatchup($week, $game) // note that this is a function DEFINITION.  All this means is that it tells PHP that a function exists.  It does NOT execute it.  That happens elsewhere.
   {
      $query1 = "SELECT team_name FROM schedule, teams WHERE schedule.week_id = $week AND schedule.game_id = $game AND schedule.A_team = teams.team_id";

      $query2 = "SELECT team_name FROM schedule, teams WHERE schedule.week_id = $week AND schedule.game_id = $game AND schedule.H_team = teams.team_id";

      $result1 = mysql_fetch_assoc(mysql_query($query1));
      $result2 = mysql_fetch_assoc(mysql_query($query2));

      $output = '<input type="text" size = "5" name="w$weekg$gameAspread"> {$result1['team_name']} vs. {$result2['team_name']} <input type="text" size = "5" name="w$weekg$gameHspread">';

      return $output;
   }

   $game1 = createMatchup(1, 1); // THIS is where the function runs.  It looks just like how built-in PHP functions (like mysql_query()) are run
   $game2 = createMatchup(1, 2);
   $game3 = createMatchup(1, 3);
?>

<!-- all of your HTML before your form... -->

<form action="insert_spreads.php" method="post">
   <?php echo $game1; ?>
   <br>
   <?php echo $game2; ?>
   <br>
   <?php echo $game3; ?>
   <input type="Submit" value="Submit Spreads">
</form>

<!-- rest of your HTML -->

 

If you can calculate how many matchups you need before printing them to the screen, then you could simplify it further using a counter or an array and a loop.

[BelowZero]

Thank you, Sirs

 

I knew there was a way, just hadn't gotten to the function chapter yet.

 

Nightslyr, I'm experimenting with your code, but there seems to be a syntax problem with:

 

  $output = '<input type="text" size = "5" name="w$weekg$gameAspread"> {$result1['team_name']} vs. {$result2['team_name']} <input type="text" size = "5" name="w$weekg$gameHspread">'; 

 

It keeps returning a Parse error.

 

Thanks for your help!

 

[Pikachu2000]

Change the quotes that enclose the string to double quotes, and escape the double quotes inside the string with backslashes.

[BelowZero]

Thanks. Works great now.

[DavidAM]

You could get all of the matchups using a single query and a PHP loop. The query would look something like this:

SELECT S.game_id, TH.team_name AS HomeTeam, TA.team_name AS AwayTeam
FROM schedule AS S JOIN teams AS TH ON S.H_team = TH.team_id
JOIN teams AS TA ON S.A_team = TA.team_id
WHERE S.week_id = '1'
ORDER BY S.game_id;

The key is joining the same table (teams) twice to the schedule; and using an Alias to reference each of those in other parts of the query.

 

 

So then the PHP code could look something like this:

<?php include("opendatabase.php"); ?>

<FORM Method = "POST" action ="insert_spreads.php">   

<?php
   $weekID = 1;

   $result = mysql_query("
SELECT S.game_id, TH.team_name AS HomeTeam, TA.team_name AS AwayTeam
FROM schedule AS S JOIN teams AS TH ON S.H_team = TH.team_id
JOIN teams AS TA ON S.A_team = TA.team_id
WHERE S.week_id = '$weekID'
ORDER BY S.game_id;");

while ($row = mysql_fetch_array($result)) {
  printf('<input type="text" size="5" name="w%dg%dAspread">', $weekID, $row['game_id']);
  printf(" %s vs. %s", $row['AwayTeam'], $row['HomeTeam']);
  printf('<input type="text" size="5" name="w%dg%dHspread">', $weekID, $row['game_id']);
  print("<br /><br />";
}   
mysql_close($con);
?>
<br /><br />           
<input type="Submit" value="Submit Spreads">

This is completely untested.

 

It gives you one trip to the database and a tight loop for output.  I like to use printf() and sprintf() for HTML (and SQL) strings. But there are other ways to do it.

[BelowZero]

Great. Thanks, David. Works like a charm.

 

Now if I can just figure out how to actually post the spreads to the database in "insert_spreads.php", I'd be set.