CODEWITHSUNDEEP
phpmysql
AndrewFerrara
AndrewFerrara

Reputation: 2403

I need to reorder my results some how

So I just wrote this little script. The problem is I want the newest message at the bottom not at the top so some how I need to take the results from the mysql query and reorder them before echoing them out.. thanks for any help...

EDIT: DESC is the correct order the while loop is the problem.. since php runs line by line

p.s. please do not worry about security features I will add that in later.

<?php
mysql_connect('localhost', '', '');
mysql_select_db('');

if(isset($_POST['submit'])) {

    date_default_timezone_set('America/New_York');

    $_POST['username'] = 'Guest';
    //$_POST['message'] = "Hello my name is Guest!";

    mysql_query("INSERT INTO chat (username,message,date,time) VALUES ('".$_POST['username']."','".$_POST['message']."','".date('n.j.o g:i:s A')."','".time()."')");

}

$result = mysql_query("SELECT * FROM chat ORDER BY time DESC LIMIT 10");

while ($row = mysql_fetch_assoc($result)) {
    $username = $row['username'];
    $message = $row['message'];
    $date = $row['date'];

    echo "<div><b>".$username.":</b> ".$message." <i>".$date."</i></div>";
}
?>

<form method="post">
<input name="message" />
<input type="submit" name="submit" value="Send"/>
</form>
<br />

Upvotes: 1

Views: 301

Answers (2)

Core Xii
Core Xii

Reputation: 6441

You can easily reverse the order in PHP by first storing all the results and accessing them later:

<?php

$records = array();
while ($row = mysql_fetch_assoc($result)) {
    $records[] = $row;
}

$records = array_reverse($records);

foreach ($records as $row) {
    $username = $row['username'];
    $message = $row['message'];
    $date = $row['date'];

    echo "<div><b>".$username.":</b> ".$message." <i>".$date."</i></div>";
}

Upvotes: 1

Marc B
Marc B

Reputation: 360912

You could try a nested query

SELECT * FROM (
     SELECT * FROM chat ORDER BY time DESC LIMIT 10
) AS inner ORDER BY time ASC;

so the inner query fetches the most recent 10 records, and then the outer query reverses the sort order.

The alternative is to fetch the result set into an array in PHP and then output that in reverse order.

Upvotes: 2

Related Questions