Newbie Question

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Leg
Forum Newbie
Posts: 5
Joined: Sat Jun 22, 2002 10:53 pm

Newbie Question

Post by Leg »

OK...im building this stats tracking for a video game where I input final results into the DB and it generates some output...i have most of it working...here is a Breif descrption of my problem



PHP:--------------------------------------------------------------------------------
<?php
$db = mysql_connect($databaseserver, $databaseuser, $databasepass);
mysql_select_db($databasename,$db);
$sortby = "margin_permap ASC";
$sql="SELECT * FROM $playerstable ORDER BY $sortby";
$result=mysql_query($sql,$db) or die (mysql_error());
$num = mysql_num_rows($result);
$cur = 1;

echo "<ol>";
while ($num >= $cur) {

$row = mysql_fetch_array($result);
$name = $row["name"];
$squad = $row["squad"];
$scores = $row["scores"];
$kills = $row["kills"];
$deaths = $row["deaths"];
$maps_played = $row["maps_played"];
$margin_permap = $row["margin_permap"];
$margin = $scores-$deaths;
$ratio = $kills/$deaths;
$deaths_permap = $deaths/$maps_played;
$scores_permap = $scores/$maps_played;
$kills_permap = $kills/$maps_played;
$margin_permap = $margin/$maps_played;

?>

--------------------------------------------------------------------------------


I Can Not get the Output to show $MARGIN_PERMAP in the correct order best to worst..I can change the $sortby to kills and it will output correctly so that kinda tells me the Calculation Im making in this cant do the Fuction twice?? Its not storing the calculations to the DB for these:

$margin = $scores-$deaths;
$ratio = $kills/$deaths;
$deaths_permap = $deaths/$maps_played;
$scores_permap = $scores/$maps_played;
$kills_permap = $kills/$maps_played;
$margin_permap = $margin/$maps_played;

by looking at the above info I supplied can you understand what Im trying to do...here is a Link of the output and notice how the LAST COLUMN doesnt sortby best to worst score..ADMIN should be listed in 1st place with 18.17





My Code may be CRUDE but I am TOTAL Newbie..actually just finished reading my 1st book...lol...but I love learning from everyone...any help would be appreciated...i think i know what problems are..just dont know how to fix itOUTPUT PIC
kaizix
Forum Commoner
Posts: 50
Joined: Tue Jun 18, 2002 9:16 pm
Location: california
Contact:

Post by kaizix »

if you're calculating $margin_permap with stuff you get out of the db ($margin_permap = $margin/$maps_played; ) what's in the db for margin_permap? is it possible that it's sorting by that but it's a different order than what you think? i mean...if you change it after the query, it must be something different in the db when you make the query so maybe it's working correctly, just the stuff in it isn't right (or whatever)....hope that makes sense.
will
Forum Contributor
Posts: 120
Joined: Fri Jun 21, 2002 9:38 am
Location: Memphis, TN

Post by will »

you say that it's not storing the calculations for those variables... do you mean it's not storing them in the DB? are you making another query later on along the lines of

Code: Select all

"UPDATE $playerstable SET margin_permap='$margin_permap',&#1111;all the other variables] WHERE &#1111;unique ID stuff]"
when you get information from a database, you get a COPY of it, so that when you edit the copy you received, it doesn't actually change the DB... you have to do that manually.
Leg
Forum Newbie
Posts: 5
Joined: Sat Jun 22, 2002 10:53 pm

Post by Leg »

I dont have it updating the table...unsure how to accomplish this..everything I tried fails....

Im a Failure.....goodbye cruel World

:cry: :cry: :idea:
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

hey, don't blame yourself. It's always the computer's failure. ALWAYS ;)
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

There's not a lot of point storing values in a table that are just calculated from other values in the table because you can get all the info you need when you do your select,

Code: Select all

$sql="SELECT name, squad, scores, kills, deaths, maps_played, margin_permap, (scores-deaths) AS margin, (kills/deaths) AS ratio,  (deaths/maps_played) AS deaths_permap, (scores/maps_played) AS scores_permap, (kills/maps_played) AS kills_permap, ((scores-deaths)/maps_played) AS margin_permap FROM $playerstable ORDER BY $sortby";
Then to order by margin_permap have:

Code: Select all

$sortby = "margin_permap ASC";
You can access all the variables in a similar way to what you did before:

Code: Select all

$result=mysql_query($sql) or die (mysql_error()); 

echo '<ol>'; 
while ($row = mysql_fetch_assoc($result)) &#123; 
    $name = $row&#1111;'name'];
    .
    .
    .
    $maps_played = $row&#1111;'maps_played'];
    $margin = $row&#1111;'margin']; 
    .
    .
    .
    $margin_permap = $row&#1111;'margin_permap'];
&#125;
Mac
Leg
Forum Newbie
Posts: 5
Joined: Sat Jun 22, 2002 10:53 pm

Post by Leg »

Thanks for your help..it did what I wanted it to do by Listing the Top margin-permap person..but it only lists that person..I need it to List EVERYONE in the Database...now it just outputs 1 person

Can you still help me please??? :lol: :lol: 8O :D
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

What does your code look like?

Mac
Leg
Forum Newbie
Posts: 5
Joined: Sat Jun 22, 2002 10:53 pm

Post by Leg »

Code: Select all

<?php
$db = mysql_connect($databaseserver, $databaseuser, $databasepass);
mysql_select_db($databasename,$db);
$sortby = "margin_permap ASC";
$sql = "SELECT name, squad, scores, kills, deaths, maps_played,  (scores-deaths) AS margin, (kills/deaths) AS ratio,  (deaths/maps_played) AS deaths_permap, (scores/maps_played) AS scores_permap, (kills/maps_played) AS kills_permap, ((scores-deaths)/maps_played) AS margin_permap  FROM  $playerstable ORDER BY $sortby";
$result=mysql_query($sql) or die (mysql_error()); 

echo "<ol>"; 
while ($row = mysql_fetch_assoc($result)) &#123; 
    $name = $row&#1111;"name"]; 
    $squad  = $row&#1111;"squad"];
	$scores = $row&#1111;"scores"];
	$kills = $row&#1111;"kills"];
	$deaths = $row&#1111;"deaths"];
    $maps_played = $row&#1111;"maps_played"]; 
    $margin = $row&#1111;"margin"]; 
    $kills_permap = $row&#1111;"kills_permap"];
	$deaths_permap = $row&#1111;"deaths_permap"];
	$ratio = $row&#1111;"ratio"];
	$scores_permap = $row&#1111;"scores_permap"]; 
    $margin_permap = $row&#1111;"margin_permap"]; 
&#125;

	?>
	

	
  <tr> 
    <td width="12%" bgcolor="#222222" align="center" nowrap> <font face="Verdana, Arial, Helvetica, sans-serif" size="1" color="#CCCCCC"> 
      <?php echo "$name" ?>
      </font></td>
    <td width="8%" bgcolor="#222222" align="center" nowrap> <font face="Verdana, Arial, Helvetica, sans-serif" size="1" color="#CCCCCC"> 
      <?php echo "$squad" ?>
      </font></td>
    <td width="8%" bgcolor="#111111" align="center" nowrap> <font face="Verdana, Arial, Helvetica, sans-serif" size="1" color="#CCCCCC"> 
      <?php echo "$scores" ?>
      </font></td>
    <td width="8%" bgcolor="#222222" align="center" nowrap> <font face="Verdana, Arial, Helvetica, sans-serif" size="1" color="#CCCCCC"> 
      <?php echo printf("%.2f", $scores_permap); ?>
      </font></td>
    <td width="8%" bgcolor="111111" align="center" nowrap> <font face="Verdana, Arial, Helvetica, sans-serif" size="1" color="#CCCCCC"> 
      <?php echo "$kills" ?>
      </font></td>
    <td width="8%" bgcolor="#222222" align="center" nowrap> <font face="Verdana, Arial, Helvetica, sans-serif" size="1" color="#CCCCCC"> 
      <?php echo printf("%.1f", $kills_permap); ?>
      </font></td>
    <td width="8%" bgcolor="111111" align="center" nowrap> <font face="Verdana, Arial, Helvetica, sans-serif" size="1" color="#CCCCCC"> 
      <?php echo "$deaths" ?>
      </font></td>
    <td width="8%" bgcolor="#222222" align="center" nowrap> <font face="Verdana, Arial, Helvetica, sans-serif" size="1" color="#CCCCCC"> 
      <?php echo printf("%.1f", $deaths_permap); ?>
      </font></td>
    <td width="8%" bgcolor="111111" align="center" nowrap> <font face="Verdana, Arial, Helvetica, sans-serif" size="1" color="#CCCCCC"> 
      <?php echo "$maps_played" ?>
      </font></td>
    <td width="8%" bgcolor="#222222" align="center" nowrap> <font face="Verdana, Arial, Helvetica, sans-serif" size="1" color="#CCCCCC"> 
      <?php echo printf("%.2f", $ratio);  ?>
      </font></td>
    <td width="8%" bgcolor="#222222" align="center" nowrap> <font face="Verdana, Arial, Helvetica, sans-serif" size="1" color="#CCCCCC"> 
      <?php echo printf("%.2f", $margin); ?>
      </font></td>
    <td width="8%" bgcolor="#222222" align="center" nowrap> <font face="Verdana, Arial, Helvetica, sans-serif" size="1" color="#CCCCCC"> 
      <?php echo printf("%.2f", $margin_permap); ?>
      </font></td>
  </tr>
  <?php
	

echo "</ol>";

?>
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

Your problem is with the while loop and what it emcompases,

Code: Select all

while ($row = mysql_fetch_assoc($result)) &#123; 
    $name = $row&#1111;"name"]; 
    $squad  = $row&#1111;"squad"]; 
   $scores = $row&#1111;"scores"]; 
   $kills = $row&#1111;"kills"]; 
   $deaths = $row&#1111;"deaths"]; 
    $maps_played = $row&#1111;"maps_played"]; 
    $margin = $row&#1111;"margin"]; 
    $kills_permap = $row&#1111;"kills_permap"]; 
   $deaths_permap = $row&#1111;"deaths_permap"]; 
   $ratio = $row&#1111;"ratio"]; 
   $scores_permap = $row&#1111;"scores_permap"]; 
    $margin_permap = $row&#1111;"margin_permap"]; 
&#125;
Every time that loop is run the values of the variables are all overwritten so all you have saved by the end is the last row from the result set.

Try changing

Code: Select all

// bunch of php
   $scores_permap = $row&#1111;"scores_permap"]; 
   $margin_permap = $row&#1111;"margin_permap"]; 
&#125; 

   ?> 
<!-- LOTS OF HTML -->
<?php 

echo "</ol>"; 

?>
to

Code: Select all

// bunch of php
   $scores_permap = $row&#1111;"scores_permap"]; 
   $margin_permap = $row&#1111;"margin_permap"]; 

   ?> 
<!-- LOTS OF HTML -->
<?php 
&#125;

?>
Note how the closing brace (}) for the while loop has been moved to after the HTML (I removed the closing <ol> tag as it didn't seem to have any purpose). This way the HTML becomes part of the loop and is created for each record in the result set.

Mac
Leg
Forum Newbie
Posts: 5
Joined: Sat Jun 22, 2002 10:53 pm

Post by Leg »

Wahoooo

Thank you SO much for the help over the last few days.
Works like a charm....Thank you.... Thank you :)
Post Reply