txrandom Posted August 9, 2006 Share Posted August 9, 2006 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? Quote Link to comment Share on other sites More sharing options...
Caesar Posted August 10, 2006 Share Posted August 10, 2006 Can you please provide some code? Maybe you are not taking the most efficient approach with this. Quote Link to comment Share on other sites More sharing options...
txrandom Posted August 10, 2006 Author Share Posted August 10, 2006 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] Quote Link to comment Share on other sites More sharing options...
hitman6003 Posted August 10, 2006 Share Posted August 10, 2006 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. Quote Link to comment Share on other sites More sharing options...
txrandom Posted August 10, 2006 Author Share Posted August 10, 2006 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. Quote Link to comment Share on other sites More sharing options...
DylanBlitz Posted August 10, 2006 Share Posted August 10, 2006 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] Quote Link to comment Share on other sites More sharing options...
txrandom Posted August 10, 2006 Author Share Posted August 10, 2006 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. Quote Link to comment Share on other sites More sharing options...
Barand Posted August 10, 2006 Share Posted August 10, 2006 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 resultsecho '<pre>', print_r($data, true), '</pre>';?>[/code] Quote Link to comment Share on other sites More sharing options...
txrandom Posted August 10, 2006 Author Share Posted August 10, 2006 Thanks, that worked! Quote Link to comment Share on other sites More sharing options...
hitman6003 Posted August 10, 2006 Share Posted August 10, 2006 I was hoping Barand would help here...he is an SQL genius Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.