Display randomly ordered MySQL query results on mult pages.
Moderator: General Moderators
Display randomly ordered MySQL query results on mult pages.
I have created a MySQL query that randomly orders the results ( ORDER BY RAND() ). I am displaying the results on multiple pages, with 15 records per page. I have no problems implementing multiple pages so that no duplicate records are shown if I order the results by some criteria. However, if I throw in RAND() I may get the same record on page two that I saw on page one.
Is there a way to remember the results of a MySQL query from page to page? I do not want to have to requery each page because I loose all control and can not gaurantee that a record will only be displayed once.
I do not want to use session variables.
Wondering if I am overlooking something here, is there an easy way I can accomplish this?
I was thinking of conceptually creating a temporary file and then have the results of each query saved in that file. I would then want to have the temp files deleted after 60 minutes or so. To be honest, I am not thrilled about doing this because it would take some time to learn how on my end.
Thanks for any help offered in advance.
CS
Is there a way to remember the results of a MySQL query from page to page? I do not want to have to requery each page because I loose all control and can not gaurantee that a record will only be displayed once.
I do not want to use session variables.
Wondering if I am overlooking something here, is there an easy way I can accomplish this?
I was thinking of conceptually creating a temporary file and then have the results of each query saved in that file. I would then want to have the temp files deleted after 60 minutes or so. To be honest, I am not thrilled about doing this because it would take some time to learn how on my end.
Thanks for any help offered in advance.
CS
- jayshields
- DevNet Resident
- Posts: 1912
- Joined: Mon Aug 22, 2005 12:11 pm
- Location: Leeds/Manchester, England
Re: Display randomly ordered MySQL query results on mult pag
Why? That's the only option I can think of.csingsaas wrote:I do not want to use session variables.
- jayshields
- DevNet Resident
- Posts: 1912
- Joined: Mon Aug 22, 2005 12:11 pm
- Location: Leeds/Manchester, England
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
Code: Select all
session_start();
// .. SELECT * FROM `users` LIMIT 1
while ($row = mysql_fetch_assoc($result))
{
$_SESSION['userdata'] = $row;
// or
$_SESSION['username'] = $row['username'];
$_SESSION['access'] = $row['access'];
//etc
}Jcart -
I followed your suggestion and have a couple of additional questions.
1) So this is essentially creating an array for each field correct? If so, won't I need to define the index each time I loop through such as:
I do not think what you defined in your example works.
2) Also, when I use a while loop I am always leaving out one record. For example, I have a query where I know it returns two records. When I use a while loop like you gave in your example. The array size will only be 1, when I use a do while loop, it appears to be correct.
I followed your suggestion and have a couple of additional questions.
1) So this is essentially creating an array for each field correct? If so, won't I need to define the index each time I loop through such as:
Code: Select all
$count = 0;
while($row = mysql_fetch_assoc($RotatingAds)) {
$HTTP_SESSION_VARS['username'][$count] = $row['user'];
$count++;
}2) Also, when I use a while loop I am always leaving out one record. For example, I have a query where I know it returns two records. When I use a while loop like you gave in your example. The array size will only be 1, when I use a do while loop, it appears to be correct.
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
I don't get what you just said, do you mean it will create a new array element for each row?1) So this is essentially creating an array for each field correct? If so, won't I need to define the index each time I loop through such as:
Sorry, I was under the impression you only wanted to do this for a single user, thats why I included the LIMIT 1 in the query I provided.csingsaas wrote:The array size will only be 1, when I use a do while loop, it appears to be correct.
You basically have it right on, except there is no need to manually assign the array indexes.
Code: Select all
$HTTP_SESSION_VARS['username'] = array();
while($row = mysql_fetch_assoc($RotatingAds)) {
$HTTP_SESSION_VARS['username'][] = $row['user'];
}Also, I recommend you update your version of php, seriously. That is wayyyyyy out of date. If you have a version more recent than 4.3 you should be using $_SESSION and not $HTTP_SESSION_VARS
I'm using PHP version 4.3.10. The reason I was using $HTTP_SESSION_VARS is out of ignorance. I was using the syntax that Dreamweaver produces. Then again, my version of Dreamweaver is a tad bit outdated.
I am developing a web site for classified ads. More specifically, right now I am writing a script to search the database. A customer can perform a search by specifying all or just one of 5 different fields (search criteria). I want the results to be returned in random. So if two customers search for widgets located in Minnesota they receive the same set of results but in a different order. I am also going to be limiting the results to 15 per page. If there are 45 results, I want them displayed on 3 different pages. I want a record to only be displayed once.
One follow-up question. Will this method you have outlined for me have trouble handling a large number of search results? Say someone searches for widgets in Minnesota and there are 12000 qualified records. Is it unrealistic to think that we can handle search results using arrays or this size?
Thanks
I am developing a web site for classified ads. More specifically, right now I am writing a script to search the database. A customer can perform a search by specifying all or just one of 5 different fields (search criteria). I want the results to be returned in random. So if two customers search for widgets located in Minnesota they receive the same set of results but in a different order. I am also going to be limiting the results to 15 per page. If there are 45 results, I want them displayed on 3 different pages. I want a record to only be displayed once.
One follow-up question. Will this method you have outlined for me have trouble handling a large number of search results? Say someone searches for widgets in Minnesota and there are 12000 qualified records. Is it unrealistic to think that we can handle search results using arrays or this size?
Thanks
If you seed the RAND function (i.e. ORDER BY RAND(1234)) then the result set will be returned in the same random order each time (if that makes sense).
You then just need to generate a new seed value for each new search then pass the value from result page to result page by which ever method you prefer, session variable, within the URL etc...
You then just need to generate a new seed value for each new search then pass the value from result page to result page by which ever method you prefer, session variable, within the URL etc...
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
That is very useful to know! Thanks for that bit of informationredmonkey wrote:If you seed the RAND function (i.e. ORDER BY RAND(1234)) then the result set will be returned in the same random order each time (if that makes sense).
You then just need to generate a new seed value for each new search then pass the value from result page to result page by which ever method you prefer, session variable, within the URL etc...
That is very very useful. Thanks! One last question.
What is your opinion on what is more taxing on system resources, using session variables or incorporate more sql queries.
I now have the option of either saving all of the search results in session variables and displaying them on subsequent pages as requested by the user. Or, I can invoke the seeded value into my sql query RAND() as stated above. This would mean I perform about 4-5 different select commands for each search results page that is viewed.
Regards
What is your opinion on what is more taxing on system resources, using session variables or incorporate more sql queries.
I now have the option of either saving all of the search results in session variables and displaying them on subsequent pages as requested by the user. Or, I can invoke the seeded value into my sql query RAND() as stated above. This would mean I perform about 4-5 different select commands for each search results page that is viewed.
Regards