Ordering MySQL table twice

[KapaGino]

Hi there,

 

I'm trying to make a dynamic website that I can upload poems to and it'll update two sections of the page, one section has the new poems and the other has the top rated poems.

 

I've got it to sort the new poems by date and I know I can get it to sort the poems by the number of likes BUT my way of doing it is potentially messy, and was wondering if any of you know of a more sensible approach?

 

The code is:

        $query = mysql_query("SELECT * FROM `poems` ORDER BY `time_posted`DESC LIMIT 0, 12");

	while($row = mysql_fetch_assoc($query)){
		$title[] = $row['title'];
		$id [] = $row['id'];
		$time_posted [] = $row['time_posted'];
	}

I store the results of the query into seperate arrays and I then use a while loop embedded in my html code to grab each of the details of the poem, i.e. $title[$x]

 

I know I could copy the same code above and replace $time_posted[ ] with $likes[ ] and then make seperate arrays again but it doesn't seem like the best approach to take.

 

Thanks for any help

btw my SQL version is >> 5.5.27

Edited July 24, 2013 by KapaGino

[kicken]

Unless the two lists are going to be showing the same poems, then you'd probably want two queries anyway. Your top-rated poem may not part of the top 12 newest poems, and would be excluded by that query.

 

So do another second query that will grab the top rated poems and read those results into another array. Note that it's generally easier to keep things confined to one array rather than make separate arrays for each column like you are currently doing. What you'd do is create a multi-dimensional array where the first level represents each row, and the second level each column. It's easily done like so:

 

$newestPoems = array();
while ($row=mysql_fetch_assoc($query)){
   $newestPoems[] = $row;
}
var_dump($newestPoems); 

Also, take notice of the big warning on the top of the mysql_query manual page. You need to stop using the mysql_* functions and move to PDO or mysqli_*

[KapaGino]

This is so much cleaner thanks

Wow I should really give that page a look, totally didn't know they were dropping it for PDO & MySQLi, thanks a heap for that info.