Page 1 of 1

Sorting Database Entries

Posted: Thu Feb 05, 2009 2:42 pm
by Crispin_Java
Hi,
I have an sql database which I am accessing through PHP, but I can`t figure out how to sort the entries by how recent they are... When I list the entries as shown below, they seem to appear in almost random order:

$theUser = mysql_query("SELECT user FROM Comments");
while($userRow = mysql_fetch_array($theUser))
{
echo $userRow["user"];
}

-----

Any ideas, I`m a little new to this :D
Thanks,
C

Re: Sorting Database Entries

Posted: Thu Feb 05, 2009 2:47 pm
by pickle
You want to use the SQL ORDER BY clause.

Code: Select all

SELECT 
   * 
FROM 
   `myTable` 
ORDER BY 
   `columnToSortBy`

Re: Sorting Database Entries

Posted: Thu Feb 05, 2009 4:23 pm
by Crispin_Java
Thanks a lot for the answer, I just got it working perfectly :)

One more question, if I may:

I am connecting to different fields in the database the following way:

$theComment = mysql_query("SELECT comment FROM Comments ORDER BY thisorder DESC");
$theUser = mysql_query("SELECT user FROM Comments ORDER BY thisorder DESC");
$theVideo = mysql_query("SELECT video FROM Comments ORDER BY thisorder DESC");
$theSubmitter = mysql_query("SELECT submitter FROM Comments ORDER BY thisorder DESC");

It seems like the database is being sorted four times - is there a way to sort all four fields at the same time? Is seems like that would make it much more efficient.

Cheers,
C

Re: Sorting Database Entries

Posted: Thu Feb 05, 2009 4:35 pm
by jayshields

Code: Select all

SELECT `comment`, `user`, `video`, `submitter` FROM `Comments` ORDER BY `thisorder` DESC
This is very simple SQL, you could easily find this stuff on Google.

Re: Sorting Database Entries

Posted: Thu Feb 05, 2009 4:36 pm
by Skoalbasher
jayshields wrote:

Code: Select all

SELECT `comment`, `user`, `video`, `submitter` FROM `Comments` ORDER BY `thisorder` DESC
This is very simple SQL, you could easily find this stuff on Google.
Or you could do

Code: Select all

SELECT * FROM `Comments` ORDER BY `thisorder` DESC

Re: Sorting Database Entries

Posted: Mon Feb 09, 2009 12:03 pm
by Crispin_Java
Thanks SkoalBasher,

If I sort the entries as you suggested, like this:
$total = mysql_query("SELECT * FROM `Comments` ORDER BY `thisorder` DESC");

Then how do I put each field into a seperate string?

Cheers,
Crispin

Re: Sorting Database Entries

Posted: Mon Feb 09, 2009 12:21 pm
by Ziq
I think you should read something about mysql and php cooperation. Firstly read the manual.
Then how do I put each field into a seperate string?
Read about this function mysql_fetch_assoc() or similar.

Re: Sorting Database Entries

Posted: Mon Feb 09, 2009 1:04 pm
by Skoalbasher
Crispin_Java wrote:Thanks SkoalBasher,
Then how do I put each field into a seperate string?

Cheers,
Crispin
You already know how. :) You did it in the example you provided us.