display database record at random and ranking then by order.

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Locked
paulng
Forum Commoner
Posts: 28
Joined: Mon Jul 11, 2005 9:31 am

display database record at random and ranking then by order.

Post 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.
mltsy
Forum Newbie
Posts: 9
Joined: Mon Jul 11, 2005 12:46 pm
Location: Minnesota, USA

Clarification

Post 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 :)
itguy69
Forum Newbie
Posts: 1
Joined: Tue May 31, 2005 5:36 pm

Post 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:

Code: Select all

ksort($arr_data);
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"] . "&nbsp;" . $item["DisplayOrder"] . "<br>);
}
I hope this is what you were looking for.
paulng
Forum Commoner
Posts: 28
Joined: Mon Jul 11, 2005 9:31 am

Post by paulng »

Thanks itguy69
:D

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. :D
paulng
Forum Commoner
Posts: 28
Joined: Mon Jul 11, 2005 9:31 am

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

$arr_data isn't an array anymore... it sounds like you've changed the code more than what you've indicated..
Locked