Jump to content


Photo

Situation involving arrays


  • Please log in to reply
9 replies to this topic

#1 txrandom

txrandom
  • Members
  • PipPip
  • Member
  • 21 posts

Posted 09 August 2006 - 11:58 PM

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?

#2 Caesar

Caesar
  • Members
  • PipPipPip
  • Advanced Member
  • 1,025 posts

Posted 10 August 2006 - 12:02 AM

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

#3 txrandom

txrandom
  • Members
  • PipPip
  • Member
  • 21 posts

Posted 10 August 2006 - 12:25 AM

here is my 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++;
}


#4 hitman6003

hitman6003
  • Members
  • PipPipPip
  • Advanced Member
  • 1,807 posts

Posted 10 August 2006 - 12:32 AM

Use SQL to do this for you:

SELECT gid.name, testbracket.points FROM testbracket LEFT JOIN gid ON testbracket.gid = gid.gid

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:

$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;
  }
}

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

#5 txrandom

txrandom
  • Members
  • PipPip
  • Member
  • 21 posts

Posted 10 August 2006 - 05:55 AM

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.

#6 DylanBlitz

DylanBlitz
  • Members
  • PipPipPip
  • Advanced Member
  • 99 posts
  • LocationOC Baby!

Posted 10 August 2006 - 06:10 AM

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

$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;
  }
}


#7 txrandom

txrandom
  • Members
  • PipPip
  • Member
  • 21 posts

Posted 10 August 2006 - 06:20 AM

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.

#8 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 10 August 2006 - 07:24 AM

I think you need to join using gid.name

<?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>';
?>

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#9 txrandom

txrandom
  • Members
  • PipPip
  • Member
  • 21 posts

Posted 10 August 2006 - 04:23 PM

Thanks, that worked!

#10 hitman6003

hitman6003
  • Members
  • PipPipPip
  • Advanced Member
  • 1,807 posts

Posted 10 August 2006 - 05:07 PM

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users