Dividing to fields

cmccomas

I know you can add/subtract in the $query, but can you divide?

$query = "SELECT pid, lastname, games, totpoints, (totpoints / games) AS pointspg FROM roster ORDER BY pointspg DESC LIMIT 0, 1";

If now, how can I do this?

DeltaRho2K

It depends on exactly, what you are trying to do, but for what it sounds like in your case, probably not...

Try this on for starters...


$query = "SELECT pid, lastname, games, totpoints FROM roster WHERE pid = '$pid'";
$result = mysql_fetch_array($query);

$pointspg = $result["totpoints"]/$result["games"];

print $pointspg;

cmccomas

Here's the code. So, if I do it your way, I just insert $average where I want the average displayed?

 <?php
// includes
include("../conf.php");

// open database connection
$connection = mysql_connect($host, $user, $pass) or die ("Unable to connect!");

// select database
mysql_select_db($db) or die ("Unable to select database!");

// generate and execute query
$query = "SELECT pid, lastname, totgames, totpoints, (totpoints / totgames) AS pointspg FROM roster ORDER BY pointspg DESC LIMIT 0, 1";
$result = mysql_query($query) or die ("Error in query: $query. " . mysql_error());

// if records present
if (mysql_num_rows($result) > 0)
{
	// iterate through resultset
	// print article titles
	while($row = mysql_fetch_object($result))
	{
	?>
    <td><font class="smallmidText"><b>Points</b></font></td>
    <td><a href="http://www.pistonscentral.com/pc/team.php?pid=<?php echo $row->pid; ?>" class="gameHigh"><? echo $row->lastname; ?></a></td>
    <td><font class="smallmidText"><? echo $row->pointspg; ?></font></td>
	             <?php
	}
}
// if no records present
// display message
else
{
?>

DeltaRho2K

Yep, but what I have here actually only pulls one row of data defined by the WHERE clause. (assuming you are searching on a unique index.)

If you pull multiple rows, then you will want to make sure you actually include the calculation in your while loop. Otherwise, it will only calculate the one time, and display the one result for all of your rows...

BeastRider

Yes, you can do division and any of the functions listed in the mysql doc.

http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#Arithmetic_functions

You can also do branching, comparisons, and much more

http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#Functions

cmccomas

If I add it right below $result it'll still find all the pid's, lastname, totpoints, games, right?

DeltaRho2K

Hey Beastrider, can you use the variables themselves in the query? I have tried many different times, and many different ways, and have had troubles with it in the past... I kind of just gave up on it, because I could never get the actual desired results, so I started resorting to this method...

Hey cmccomas, in each looping row of the while statement for a particulat team/person, you can actually just do the calculation.

$query = "SELECT pid, lastname, games, totpoints FROM roster"; 
$result = mysql_fetch_array($query); 


if (mysql_num_rows($result) > 0) 
{ 
    // iterate through resultset 
    // print article titles 
    while($row = mysql_fetch_object($result)) 
    { 
    ?> 
    <td><font class="smallmidText"><b>Points</b></font></td> 
    <td><a href="http://www.pistonscentral.com/pc/team.php?pid=<?php echo $row->pid; ?>" class="gameHigh"><? echo $row->lastname; ?></a></td> 
    <td><font class="smallmidText"><?php echo ($row("totpoints")/$row("games"))?></font></td> 

             <?php 
} 
} 
// if no records present 
// display message 
else 
{

BeastRider

You must use the field names (or columns) in the query. So, this query is OK.

$query = "SELECT pid, lastname, games, totpoints, (totpoints / games) AS pointspg FROM roster";

because it will now cause a calculated field to be returned in the results array. But, the calculated field cannot be used in an ORDER BY clause as in:

$query = "SELECT pid, lastname, games, totpoints, (totpoints / games) AS pointspg FROM roster ORDER BY pointspg";

Instead, the entire calculation must be repeated again in the ORDER BY clause as in:

$query = "SELECT pid, lastname, games, totpoints, (totpoints / games) AS pointspg FROM roster ORDER BY (totpoints / games) ";

So, the calculation is done twice in the SELECT statement... not too efficient in my book... and when the calculations start to get hairy, it just seems like a messy way to do it to me.

So, I move the results to an array, make the computations I want, then sort the array into the desired order.

$query = "SELECT pid, lastname, games FROM roster"; 
// load results to array
while ($row = @ mysql_fetch_array($result))
  {
  $myarray[] = $row;
  }
// perform average points calculation
$arrcnt = count($myarray);
for ($i = 0; $i < $arrcnt ;$i++)
  {
  $pointspg = $myarray[$i]["totpoints"] / $myarray[$i]["totgames"];
  $myarray[$i]["pointspg"] = $pointspg;
  }

// sort the query by the distance
$myarray2 = array_csort($myarray,"pointspg");

// now do whatever with the sorted array

// the array sort looks like this

function array_csort($marray, $column)
{
foreach ($marray as $row)
  {
  $sortarr[] = $row[$column];
  }
array_multisort($sortarr, SORT_NUMERIC, SORT_ASC, $marray);
return $marray;
}

By the way, I notice that the original query has a LIMIT 0,1 clause in it which guarantees only the first record will be returned. ????

cmccomas

Yes, the first and only one. I want the highest total to be returned on this page. On the actual stats page all will be returned.

THANKS FOR ALL YOUR HELP!

BeastRider

Originally posted by cmccomas
Yes, the first and only one. I want the highest total to be returned on this page. On the actual stats page all will be returned.

THANKS FOR ALL YOUR HELP!

Well, I reckon in that case you have to have a calculated ORDER BY field