Page 1 of 1
display database record at random and ranking then by order.
Posted: Mon Jul 11, 2005 9:45 am
by paulng
Hi Everyone.
I'm try to retrieve data from a database, displaying record at random and rank them according to the value of a field in my database called DisplayOrder.
I Have a field called DisplayOrder, using the SQL statement below I can display the database record in display order
sql_query = "SELECT * FROM mod_en WHERE StartDate <= '$currdate' AND EndDate >= '$currdate' ORDER BY DisplayOrder ASC LIMIT 4";
I'm trying to display the data at random but keeping the display order. Using both the RAND and ORDER BY doesnt work. I dont know whether I need to write some separate PHP code to process the random.
Can anyone help please.
Clarification
Posted: Mon Jul 11, 2005 1:33 pm
by mltsy
Can you clarify what you're trying to do? All I could make out was that you're trying to display them in order and in a random order at the same time... which is obviously not possible

Posted: Mon Jul 11, 2005 4:46 pm
by itguy69
I think I understand what you are trying to do: select 4 records at random, then sort that result set by the DisplayOrder field. Here's how you would do that.
First thing is to grab 4 random records. You can do this with your query like this:
Code: Select all
$sql_query = "SELECT * FROM mod_en WHERE StartDate <= '$currdate' AND EndDate >= '$currdate' ORDER BY RAND() LIMIT 4";
Next step is to create an array of the results, but set the index of this array to be the value of DisplayOrder for each record:
Code: Select all
$sql_query = "SELECT * FROM mod_en WHERE StartDate <= '$currdate' AND EndDate >= '$currdate' ORDER BY RAND() LIMIT 4";
$rs_data = mysql_query($sql_query) or die ("could not execute query: " . mysql_error());
while($data = mysql_fetch_assoc($rs_data))
{
$arr_data[$data['DisplayOrder']] = $data;
}
Now just resort the array ascending by the key:
Finally, when you're ready to display the data, just use a foreach loop on the array:
Code: Select all
foreach($arr_data as $item)
{
echo($item["id"] . " " . $item["DisplayOrder"] . "<br>);
}
I hope this is what you were looking for.
Posted: Tue Jul 12, 2005 4:46 am
by paulng
Thanks itguy69
I'm glad you can understand me even though I was straggling explaining what I was trying to do. Your response is what i wanted to achieve and I have done it it working well with my code.

Posted: Mon Nov 07, 2005 4:09 am
by paulng
I was wondering if anyone could help, a while ago I posted a question about ranking fields by asceding order as well as displaying them by random and someone provided me with the code below which worked well :
Code: Select all
$sql_query = "SELECT * FROM $tablevar WHERE FIND_IN_SET('download',DisplayOn) AND StartDate <= '$currdate' AND EndDate >= '$currdate' ORDER BY RAND()";
$rs_data = mysql_query($sql_query) or die ("could not execute query: " . mysql_error());
while($data = mysql_fetch_assoc($rs_data))
{
$arr_data[$data['DisplayOrder']] = $data;
}
ksort($arr_data);
foreach($arr_data as $item)
until I changed my table structure by adding a new field into my table called "displayon" this field is causing an error on the page if there is not value in the field. Here is the error
Warning: ksort() expects parameter 1 to be array, null given in /usr/local/apache.....
Warning: Invalid argument supplied for foreach() .....
can anyone plaese help? much appreciated!
paulng.
Posted: Mon Nov 07, 2005 7:31 am
by feyd
$arr_data isn't an array anymore... it sounds like you've changed the code more than what you've indicated..