Order of members in Database

[Anon]

Hello. I have a site where members join. When a new member is approved, the information automatically moves to the bottom of the list. I am hoping to change this so that the newest profiles are on top and the first seen when a search is done. I believe that I need to change something in the below:
// building insert statement
#$sqlstatement = "select * from $stable where status = 'pending'";
$sqlstatement = "select id, username, firstname, lastname, status from " .
"$stable where status = 'pending'";

Could anyone please help me out on this?

Regards,
Betty

[Anon]

Assuming "id" is an auto-incremented value, add "order by id" to have the database sort by that value. Without that directive the data may or may not be sorted, depending on the database's whim.

To have the sort order reversed, add "desc" to the "order by" clause.

$sqlstatement = "select id, username, firstname, lastname, status from $stable where status = 'pending' order by id desc";

[Anon]

Thank you for your help, it did work now.
I have another question if you do not mind helping out on:
I have a dump list in my admin. section where any member joins and gets approved, his/her email address goes into a list, here is the code :
<?php
include "_pg-lib.php3";

$con = pg_pconnect("", "", "", "", "romboard");
$result = pg_exec($con,"select email from memberinfo where email ~ '@'");

pg_dumpResult($result, "\n", "");
?>

I wanted the list to also have the user's name added next to the email address.
It only worked when I replace the "select email from member info" by "select username from member info"
but not together.
What am I doing wrong?

Thank you
Krenee

[Anon]

Well, I've never used Postgres. What is that "_pg-lib.php3" file doing? According to the docs, this should be as simple as

$con = pg_pconnect("", "", "", "", "romboard");
$result = pg_exec($con,"select * from memberinfo where email ~ '@'");

echo "<table>"
for ($row = 0; $data = @pg_fetch_object ($result, $row); $row++)
{
echo "<tr><td> $data->username </td><td> $data->email </td></tr>"

}
echo "</table>";

[Anon]

We have looked codes for our pending members as we previously put on the message board, and it now works fine after adding "ORDER BY id DESC" to the end of the sql statement as was suggested. However, the members that are already approved on the site are not showing in descending order. I believe that the below code from another page on our site is where the change needs to be made, but I am not sure of where to add "ORDER BY id DESC" because of the word "and" which appears in the sql statement in these codes. I tried putting it both before and after the word and, but I wound up getting parse errors. Does anyone have any ideas? Much thanks.

$sqlstatement = "select * from $stable where status = 'approved' and ";
$sqlstatement .= $condit;
if (strlen($rest2) == 0){
$err=0;
}else{
$sqlstatement .= $rest2;
}
if (strlen($rest3) == 0){
$err=0;
}else{
$sqlstatement .= $rest3;
}
if (strlen($rest) == 0){
$sqlstatement = substr($sqlstatement, 0, -4);
}else{
$sqlstatement .= " (";
$sqlstatement .= $rest;
$sqlstatement .= " )";
}

[Anon]

What you need to do is, after ALL of these conditional operations (which eventually build the query), add

$sqlstatement .= " order by id desc";

The ORDER BY clause must be the last one in the SQL query.

This would go AFTER the last brace in your example.

[Anon]

Thank you so much! It worked!!!