Jump to content

Archived

This topic is now archived and is closed to further replies.

txrandom

Situation involving arrays

Recommended Posts

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?

Share this post


Link to post
Share on other sites
Can you please provide some code? Maybe you are not taking the most efficient approach with this.

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.