Putting SQL Data into Array and Sorting it

[carleihar]

Well, the title pretty much says it all. I'm trying to extract data from an array, put it into a 2D array and then sort it. Then I need to put it's "place" in the order into the table.

 

I've sort of got something so far but it seems like my while loop isn't working correctly.

 

$q="SELECT COUNT(*), horseID, total FROM enteredHorses";
$r = mysqli_query ($dbc, $q) or trigger_error(mysqli_error($dbc));
while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {

        $count = $row['COUNT(*)'];
        $horse_id = $row['horseID'];
        $total = $row['total'];
        

        

for ($counter = 1; $counter <= $count; $counter += 1) {

$i = 0;

$id[$i] = $horse_id;

$total[$i] = $total;


$i = $i+1;

}

} //end while

array_multisort($total, SORT_DESC, $id);


while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {

for ($counter = 1; $counter <= $count; $counter += 1) {

$i = 0;
$place = '1';

$q = "INSERT INTO enteredHorses (place) VALUES ('$place') WHERE horse_id='$id[$i']'";
$r = mysqli_query ($dbc, $q) or trigger_error(mysqli_error($dbc));

$i = $i + 1;
$place = $place + 1;

}

}

 

I'm getting this error:

 

"array_multisort() [function.array-multisort]: Argument #1 is expected to be an array or a sort flag "

 

I'm really baffled. Any help?

[mikosiko]

  I don't get it... if I understand correctly what you are trying to do is:

 

- Read your table EnteredHorses and depending on the value of the column "total" assign the proper "place" to the horse.... right?

 

so.. why don't do that in this way :

 

$q="SELECT horseID, total FROM enteredHorses ORDER BY total DESC";  

$r = mysqli_query ($dbc, $q) or trigger_error(mysqli_error($dbc));

$place = 1;
while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {

   $q = "UPDATE enteredHorses SET place = '$place' WHERE horse_id='".$row['horseID'] . "'";
   $r = mysqli_query ($dbc, $q) or trigger_error(mysqli_error($dbc));

   $place = $place + 1;

}

 

and even then, I will question why you need the column place if only with a simple select you can have the proper order, hence the horse's place.

 

[carleihar]

Thanks! Yes, this is what I wanted to do I just had a hard time with my loops. The reason I want to do it this way is so that each "horse" can have it's own page and show the placings he's had in recent shows.

 

But now it's giving me errors. I keep getting this error all day!

 

"line 14: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given "

<?php

require_once('includes/mysqli_connect.php');
include('includes/config.inc.php');


include ('includes/header01.inc.php');

$q="SELECT horseID, total FROM enteredHorses";  

$r = @mysqli_query ($dbc, $q); // Run the query.

$place = 1;
while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {

   $q = "UPDATE enteredHorses SET place = '$place' WHERE horseID = '" . $row['horseID'] . "'";
   $r = @mysqli_query ($dbc, $q) or trigger_error(mysqli_error($dbc));

   $place = $place + 1;

}


include('includes/footer01.inc.php');
?>

 

I've checked my mysqli_connect.php, it's working...I've checked spelling on database...urg.

[mikosiko]

$q="SELECT horseID, total FROM enteredHorses";  

$r = @mysqli_query ($dbc, $q); // Run the query.

 

first: In your select (1st line) you didn't include the ORDER BY that I wrote in the code that I gave to you.

second: re-write the second line in this way:

 

$r = mysqli_query ($dbc, $q) or die(mysqli_error($dbc)); // Run the query and show the error on failure... Do not use the "@" in front...

[carleihar]

I had the order by in there earlier, I took it out in a failed attempt to figure out why I was getting the error.

 

Which I am still getting, even after taking your advice. 

[mikosiko]

$r = mysqli_query ($dbc, $q) or die(mysqli_error($dbc)); // Run the query and show the error on failure... Do not use the "@" in front...

 

you have a blank space after mysql_query... delete it and try...

$r = mysqli_query($dbc, $q) or die(mysqli_error($dbc)); // Run the query and show the error on failure... Do not use the "@" in front...

[carleihar]

It's working now. I think the problem was that I had two $r variables, because when I changed the second one to $p, I stopped getting the error.

 

Thanks again for all your help!