Situation involving arrays

[txrandom]

In my script I'm querying a set of names and numbers. Certain numbers are totaled up and associated with the name.  I am then trying to display the list of numbers in descending  order with the associated name with it.  Now I'm not really sure how I'm going to do this, I tried using an associated array, but I don't know how I would find the highest value to start the for loop.

Is it possible to have an array that has two values that "stick" together.  So if I sorted the array by number, the numbers would stay associated with the name?

[Caesar]

Can you please provide some code? Maybe you are not taking the most efficient approach with this.

[txrandom]

here is my code:
[code]$query = "SELECT * FROM gid";
$result=mysql_query($query);
$num=mysql_num_rows($result);
$i=0;
while ($i<$num) {
$gid=mysql_result($result,$i,"name");

$queryb = "SELECT * FROM testbracket WHERE gid='$gid'";
$resultb=mysql_query($queryb);
$numb=mysql_num_rows($resultb);
$ib=0;
$grouppoints=0;
while ($ib<$numb) {
$points=mysql_result($resultb,$ib,"points");
$grouppoints += $points;

$ib++;
}

//form an array or something else here

$i++;
}
[/code]

[hitman6003]

Use SQL to do this for you:

[code]SELECT gid.name, testbracket.points FROM testbracket LEFT JOIN gid ON testbracket.gid = gid.gid[/code]

Then you can use SUM and GROUP BY to lump them together by name with the total points, or you can use an array to store the totals for each name:

[code]$result = mysql_query("SELECT gid.name, testbracket.points FROM testbracket LEFT JOIN gid ON testbracket.gid = gid.gid");
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
  $name = $row['name'];
  if (!$data[$name]) {
    $data[$name] = $row['points'];
  } else {
    $data[$name] += $points;
  }
}[/code]

Then you can use the array sort or key sort functions to sort by name or points.

[txrandom]

Thanks, I tested the script out and I'm getting a "mysql_fetch_array(): supplied argument is not a valid MySQL result resource" error.  I think the query statement must be wrong somewhere.

[DylanBlitz]

This will tell you if something is wrong with the statement.

[code]$result = mysql_query("SELECT gid.name, testbracket.points FROM testbracket LEFT JOIN gid ON testbracket.gid = gid.gid");
if (!$result) { echo mysql_errno().": ".mysql_error()."\n"; exit; }

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
  $name = $row['name'];
  if (!$data[$name]) {
    $data[$name] = $row['points'];
  } else {
    $data[$name] += $points;
  }
}[/code]

[txrandom]

I get the error 1054: Unknown column 'gid.gid' in 'on clause'.  Does that mean it's trying to access table gid . field gid? Cause that doesn't exist.  I've never used SQL like this so I have no idea.

[Barand]

I think you need to join using gid.name

[code]<?php
$sql = "SELECT gid.name, SUM(testbracket.points) AS points
      FROM gid LEFT JOIN testbracket ON testbracket.gid = gid.name
      GROUP BY gid.name
      ORDER BY points DESC";
$res = mysql_query($sql) or die(mysql_error());

$data = array();
while (list($name, $points) = mysql_fetch_row($res)) {
    $data[$name] = $points;
}

//view results
echo '<pre>', print_r($data, true), '</pre>';
?>[/code]

[txrandom]

Thanks, that worked!

[hitman6003]

I was hoping Barand would help here...he is an SQL genius