PHP - rsort() an array of arrays...

[brentW505]

OK, I have an array which contains arrays of numbers/letters..
example:

wholeArray{
array1{"9.3", "4", "photos/bg"}
array2{"9.5", "3", "photos/birthday"}
etc...}

OK so I wanted to have it only sort by the first (and if possible 2nd too) item in the array1, 2 etc... those 9.3 are scores, and the 2nd position is how many people voted.. But I think it's getting messed up because it's also counting the url.
It works for the most part but every once and a while it gets messed up.

Is there a way to sort by the first primarily, then second slots of the arrays? Or will it only count all of it?.. if you want to see how this is working you can click the url below on my site.. if you click the right photos it goes down in rank (or should) but doesn't do it right on a couple, and I'm pretty sure it's because the url.

Thanks!

[sneakyimp]

Try usort. You can define whatever function you want to determine how the sort works.

[brentW505]

hmm thanks... all the sudden someone voted on another photo, and now the rsort works fine... not sure what the deal is...

Not a big deal, but does anyone know a way to sort these in MySql? It's tricky though because I have a table of comments, so I would first have to (which I do in PHP) clump each vote into one group for each photo, then sort those. which you would I think have to create buffer variables which I'm not sure you can do in MySQL?

[sneakyimp]

If you are using one sql statement, you should be able to sort it by any number of fields using the ORDER BY clause. for example:

$sql = 'SELECT field1, field2, field3 FROM table1 t1, table2 t2, table3 t3 WHERE t1.id=t2.t1id blah blah ORDER BY field1, field2, field3'; 

[brentW505]

ok.. I see.. I usually just get WHERE id=$i.. and i is never used more than once since it's an ID.. But if I say $sql = something where there will be multiple rows.. what happens when I refer to $sql['column'].. Or how does that work?

It would be sweet if I could have sql add numbers from multiple rows and output one array with averaged/summed columns.

Thanks a lot for the help!

[sneakyimp]

Hm. I'm not following you. If you have some code that needs help, it would be useful to see it.

[brentW505]

So my PHP code loops through the rows and then finds matching column names and for all the rows where setID and photoID are the same, it takes each row that matches that and adds their contents into an array with sums of each column... is this fine or is there a simpler/faster way to do it in MySql?

by the way.. voteArray is an array of the different photos (this is a table of comments on photos... so when this script checks to see if this photo is found in the voteArray, and if not it creates a new one, else it sums/averages the numbers up, and keeps the voteArray[i][4]=photoID and voteArray[i][3]=photo id..

  $row = mysql_fetch_array(mysql_query('select max(id) from photoComments'));
  $max=$row["max(id)"];
  $maxx=$max+1;
  $rowm = mysql_fetch_array(mysql_query('select min(id) from photoComments'));
  $minn=$rowm["min(id)"]; 
  $min=$minn-1;
  $change=true;
for($i=$min;$i<$maxx;$i++)
{
  $row=mysql_fetch_array(mysql_query("SELECT * FROM `photoComments` WHERE id='$i'"));
  $i_setID=$row['setID'];
  $i_photoID=$row['photoID'];
  $i_rate=$row['rate'];
  $i_id=$row['id'];
if($i_id!=''){

  for($x=0; $x<count($voteArray); $x++){
    if($voteArray[$x][3]==$i_setID && $voteArray[$x][4]==$i_photoID){
      $voteArray[$x][1]++; //Total votes
      $voteArray[$x][2]+=$i_rate; //sum votes
      $voteArray[$x][0]=round(($voteArray[$x][2]/$voteArray[$x][1]), 3); //Average Rate
      $found=true;
    }
  }

  if(!$found){
    $voteArray[count($voteArray)+1][0]=$i_rate;	//Average rating
    $voteArray[count($voteArray)][1]++;		//Total votes
    $voteArray[count($voteArray)][2]=$i_rate;	//Sum votes
    $voteArray[count($voteArray)][3]=$i_setID;	//Set ID
    $voteArray[count($voteArray)][4]=$i_photoID;//Photo ID
  }
  $found=false;
}
}

[sneakyimp]

skip the first two queries and get rid of the loop maybe and do this:

SELECT * FROM photoComments ORDER BY id, field1, field2

where field1 and field2 are the things you'd like to sort by. it's worth noting that the results will first be ordered by field 1, field2, etc but that id might not be contiguous...that is to say it might skip from id=3 to id = 55 or something so you would not iterate through the values in between.

[brentW505]

ok, but then what would the output be? what would happen when I reference $row['setID'].. because before it would just give me one a cell from one column. How would it output it?

[sneakyimp]

1) create a new php file
2) put this code in it

$sql = "SELECT * FROM photoComments ORDER BY id";
$result = mysql_query($sql)
  or die('the query faileD!');

$data = array();
while ($row = mysql_fetch_array($result)) {
  $data[] = $row;
}
mysql_free_result($result);

echo '<table>';
foreach($data as $row) {
  echo '<tr>';
  foreach($row as $value) {
    echo '<td>' . $value . '</td>';
  }
  echo '</tr>';
}
echo '</table>'; 

3) visit the file in your browser

i haven't tested the code but i think it'll work.