Sorting information from query using while?

Question

I'm trying to get it so that the information retrieved from this query is sorted before be shown onto the page by the messageid, which I have assigned as the primary key. I keep getting this error though:

Warning: krsort() expects parameter 1 to be array, resource given in ...

Here's my code:

<?php

$id = $_SESSION[id];
$messages = @mysql_query("SELECT * FROM messages WHERE receiver='$id'");
$messagecount = mysql_num_rows($messages);
krsort($messages);

if ($messagecount == 0)
{
echo "<br>You have no messages.";
}
else
{
while ($messages2 = mysql_fetch_array($messages))
    {
    echo "<table width=800 class=\"normaltable\" cellpadding=\"3\" border=\"0\"><tr>
    <td class=\"tdmessagesubject\"><b>Subject:</b><a href=message.php?id=" .   $messages2['messageid'] . "> " . $messages2['subject'] . "</a></td>
    <td class=\"tdmessagefrom\"><b>From:</b> " . $messages2['sendercallname'] . "</td>   </tr>
    </table>";
    }
 }

?>

I thought that $messages was an array but it doesn't seem to be working.

@Sunden did the answers below contributed to you solving this question? If so could you please accept that answer. — Mr. Radical
– Mr. Radical, Commented Feb 10, 2013 at 0:17

fvu · Accepted Answer · 2013-02-10 00:08:37Z

Have a look at the manual page, mysql_query returns a resource, not and array.

And while you're there, read that big red fat warning, the one that says that the mysql_ family of functions is deprecated which among other things mean you should not use them in new code.

I'd also suggest to forget about the more modern mysqli_ successor and skip right away to PDO - it's a modern, well designed API, usable with several database engines and last but not least, it makes working with prepared statements a breeze, and prepared statements are probably the least expensive yet most effective defense against sql injection.

But back to the order of the day: when you want a database resultset to be ordered in some way by far the easiest way is to let the database server sort it, like this:

$messages = @mysql_query("SELECT * FROM messages WHERE receiver='$id' order by messageid");

There are a couple of good reasons why you should let the db sort the data and not try to do it yourself:

that way you're forced to load up the entire resultset in memory, which is inefficient and with big resultsets it can exhaust the memory available to php
if your db is well designed, chances are that the data are already indexed on the column you want to sort on, which means that the server doesn't actually have to sort the data when returning them, making the whole operation a lot faster.

sebast26 · Accepted Answer · 2013-02-10 00:01:43Z

1

your $messages variable is not an array. to build array of messages from database query you should use:

$result = @mysql_query("SELECT * FROM messages WHERE receiver='$id'");
$messages = array();
while ($message = mysql_fetch_assoc($result)) {
    $messages[] = $message;
}

Here you can find an example use of mysql_fetch_assoc: http://php.net/manual/en/function.mysql-fetch-array.php

If you want to order your messages in database query you should use ORDER BY statement. For example:

$result = @mysql_query("SELECT * FROM messages WHERE receiver='$id' ORDER BY id");

answered Feb 10, 2013 at 0:01

sebast26

1,8121 gold badge23 silver badges38 bronze badges

Comments

FoolishSeth · Accepted Answer · 2013-02-10 00:02:04Z

1

Oh man don't use @ to suppress errors unless you have a really good reason.

mysql_query returns a resource: the query result. If you want to sort it you need to either pull out every row into an array first or (better solution) use ORDER BY in the query to get your results in sorted order.

answered Feb 10, 2013 at 0:02

FoolishSeth

4,0212 gold badges22 silver badges28 bronze badges

Comments

Community · Accepted Answer · 2023-11-17 19:39:04Z

0

I'd like to say first that Mysql is deprecated in PHP, it is recommended to use the new Mysql extension, Mysqli

Then, you have to extract the results from the resource:

$data = array();
while($row = mysql_fetch_row($messages)) $data[] = $row;

edited Nov 17, 2023 at 19:39

CommunityBot

11 silver badge

answered Feb 10, 2013 at 0:02

markmb

9024 gold badges12 silver badges33 bronze badges

Collectives™ on Stack Overflow

Sorting information from query using while?

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related