0

I have a simple database for hockey stats for my son. I can enter data into the db and pull it back, but I wanted to have points, which is goals + assists, displayed without entering into the DB. I enter both Goals and assists into the database and display that all in a table on the page:

http://hjmoore420.com/hockey_display.php

I added the points in here so I could get around the problem but I want to have the two variable add up to make points as I said above, and then I want another row of totals, goals, assists points at the bottom. I tried a few things and nothing worked, any guidance would be welcome.

Thanks.

$result = mysql_query("SELECT * FROM hockey");
$goals= mysql_query("SELECT goals FROM hockey");


[echo "<table border='1'>]
[<tr>]
[<th>Game</th>]
[<th>Goals</th>]
[<th>Assists</th>]
[<th>Points</th>]
[<th>PIM</th>]
[<th>Plus/ Minus</th>]
[</tr>";]

[while($row = mysql_fetch_array($result))]
{
[echo "<tr>";]
[ echo "<td>" . $row['game'] . "</td>";]
[ echo "<td>" . $row['goals'] . "</td>";]
[echo "<td>" . $row['assists'] . "</td>";]
[echo "<td>" . $row['points'] . "</td>";]
[echo "<td>" . $row['PIM'] . "</td>";]
[echo "<td>" . $row['plus'] . "</td>";]
[ echo "</tr>";]

[ }]

i added the []'s and I am not sure if it mattered or not, theya re not really in my code.

2
Contributors
5
Replies
6
Views
11 Years
Discussion Span
Last Post by sn4rf3r
0

you can do this two ways, in php pr in sql.

$totgoal = 0;
$totassist = 0;
[while($row = mysql_fetch_array($result))]
  {
$totgoal = ($totgoal + $row['goal']);
$totassist = ($totassis + $row['assis']);
$html = "<tr>";
$html .= "<td>".$row['game']."</td>"
$html .="<td>" . $row['goals'] . "</td>";
$html .=  "<td>" . $row['assists'] . "</td>";
$html .= "<td>" . $row['points'] . "</td>";
$html .= "<td>" . $row['PIM'] . "</td>";
$html .="<td>" . $row['plus'] . "</td>";
$html .= "</tr>";
}
echo $html;

I only did a couple of variables, but you should get the picture.
As for SQL, you can use the sum() function to get a column total, it might take two queries to do this but it would work the same.

$sql = "SELECT SUM('goals') AS 'Goal Total', SUM('assis') AS 'Assist Total' FROM hockey";
$totals = mysql_query($sql);

Hope this helps

0

you can do this two ways, in php pr in sql.

$totgoal = 0;
$totassist = 0;
[while($row = mysql_fetch_array($result))]
  {
$totgoal = ($totgoal + $row['goal']);
$totassist = ($totassis + $row['assis']);
$html = "<tr>";
$html .= "<td>".$row['game']."</td>"
$html .="<td>" . $row['goals'] . "</td>";
$html .=  "<td>" . $row['assists'] . "</td>";
$html .= "<td>" . $row['points'] . "</td>";
$html .= "<td>" . $row['PIM'] . "</td>";
$html .="<td>" . $row['plus'] . "</td>";
$html .= "</tr>";
}
echo $html;

I only did a couple of variables, but you should get the picture.
As for SQL, you can use the sum() function to get a column total, it might take two queries to do this but it would work the same.

$sql = "SELECT SUM('goals') AS 'Goal Total', SUM('assis') AS 'Assist Total' FROM hockey";
$totals = mysql_query($sql);

Hope this helps

i implemeted some of what you gave me, i am trying to get the simpler variables added but i am having an issue:

http://hjmoore420.com/hockey_display.php still.

I added the code:

$sql = "SELECT SUM('goals') AS 'Goal Total', SUM('assis') AS 'Assist Total' FROM hockey";
$totals = mysql_query($sql); 

and the bottom code that I added int eh call looks like this:
while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['game'] . "</td>";
  echo "<td>" . $row['goals'] . "</td>";
  echo "<td>" . $row['assists'] . "</td>";
 echo "<td>" . $totals. "</td>";
  echo "<td>" . $row['PIM'] . "</td>";
echo "<td>" . $row['plus'] . "</td>";
  echo "</tr>";

  }

but totals is not coming up. I took the :

echo "<td>" . $totals. "</td>";

line out and added an echo $totals elsewhere outside of the loop and its still coming up as the resource #4 thing.

bit more help needed, thanks for your time.

0

you need to run

$totals = mysql_fetch_row($totals);

or

$totals = mysql_fetch_assoc($totals);

that is why you are seeing resource id 4

0

ok i did get it to work with some help from here, i probably could have had it sooner but I am still learning, but thanks for those who helped. I did get some info from some google searches, I am putting the code that i finished with to see if I could get pointers as too if I could have done it better to clean the code a bit. i just worked with it until it worked but I bet there is a better way to go through.

thanks again:

<?php
 $con=mysql_connect ("localhost", "un", "pw");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("db", $con);
$result = mysql_query("SELECT * FROM hockey"); 
$result2 = mysql_query("SELECT SUM(goals) as goals FROM hockey"); 
$result3 = mysql_query("SELECT SUM(assists) as assists FROM hockey");  
$result4 = mysql_query("SELECT SUM(points) as points FROM hockey"); 
$result5 = mysql_query("SELECT SUM(PIM) as pim FROM hockey"); 
$result6 = mysql_query("SELECT SUM(plus) as plusM FROM hockey"); 


echo "<table border='1'>
<tr>
<th>Game</th>
<th>Goals</th>
<th>Assists</th>
<th>Points</th>
<th>PIM</th>
<th>Plus/ Minus</th>
</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['game'] . "</td>";
  echo "<td>" . $row['goals'] . "</td>";
  echo "<td>" . $row['assists'] . "</td>";
 echo "<td>" . $row['points']. "</td>";
  echo "<td>" . $row['PIM'] . "</td>";
echo "<td>" . $row['plus'] . "</td>";
  echo "</tr>";

  }

  echo "<tr>";
  echo "<td>" . "total" . "</td>";
  while($row2 = mysql_fetch_array($result2))
  {
  echo "<td>" . $row2['goals'] . "</td>";
  }
  while($row3 = mysql_fetch_array($result3))
  {
  echo "<td>" . $row3['assists'] . "</td>";
  }
  while($row4 = mysql_fetch_array($result4)){
 echo "<td>" . $row4['points']. "</td>";
}
while($row5 = mysql_fetch_array($result5)){
  echo "<td>" . $row5['pim'] . "</td>";
}
while($row6 = mysql_fetch_array($result6)){
echo "<td>" . $row6['plusM'] . "</td>";
}
  echo "</tr>";
  

echo "</table>";
0
$result = mysql_query("SELECT * FROM hockey"); 
$result2 = mysql_query("SELECT SUM(goals) as goals FROM hockey"); 
$result3 = mysql_query("SELECT SUM(assists) as assists FROM hockey");  
$result4 = mysql_query("SELECT SUM(points) as points FROM hockey"); 
$result5 = mysql_query("SELECT SUM(PIM) as pim FROM hockey"); 
$result6 = mysql_query("SELECT SUM(plus) as plusM FROM hockey");

all that can be crunched into one query:

$sql = "select sum(goals) as goals, sum(assists) as assists, sum(points) as points, sum(PIM) as pim, sum(plus) from hockey"
$totals = mysql_query($sql);
if (mysql_num_rows($totals)) {
  $totals = mysql_fetch_assoc($totals, MYSQL_ASSOC);
} else {
  echo 'sorry no data here';
}
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.