RETURN 1 RANDOM RECORD TO WEBSITE!

[nwt002]

Hey all,

I am using Frontpage and have used its 'Database Interface Wizard' to create a page that displays

database results and also a page for me to edit my database from the web. The database that it

created for me is an Access database and will just contain a list of quotes. The two columns in

the DB are 'Key' which is autonumbered, and 'quote' which is just text. Whenever I find quotes

that I like I will be adding them to the database, so the number of records will always be

changing.


OJECTIVE:

I want to have the webpage display just 1 random quote from the database everytime the page is

refreshed. It is ok that it displays the same record more than once, but I would like every record

to have a chance to be displayed.


-----------------------------------
The only thing that I have found so far that kinda works is this:

SELECT TOP 1 Key, quote
FROM Results
ORDER BY Rnd(Key) * Second(Time()) * 1000 mod 1000

I added this to the custom query section of the 'Database Results Wizard' and this appears to

return random records, but the problem is every so often it will return all my records.

------------------------------------

I have tried to give you as much info as I could, so if there is anything else that would be

helpful to know, please let me know. I don't have much experience with databases, querying, or

scripting, so as much info as possible would be helpful. The more detailed the better!
Forgive me if this is not the right forum to post this question. I am not sure if this is more of a

website scripting question or a database question or both, so i will try posting this in some other

forums as well. Thanks for your time!

 

[steven290]

how about

SELECT Key, quote 
FROM Results
ORDER BY Rnd(Key) * Second(Time()) * 1000  mod 1000
limit 1

 

[nwt002]

Thanks for the reply,

When I try and add LIMIT 1 I get this error message when I 'Verify Query':


Server error: Unable to retrieve schema information from the query:

SELECT Key, quote FROM Results ORDER BY Rnd(Key) * Second(Time()) * 1000 mod 1000 limit 1

against a database using the connection string

DRIVER={Microsoft Access Driver (*.mdb)};DBQ=URL=fpdb/quotes.mdb.

The following error message comes from the database driver software; it may appear in a different language depending on how the driver is configured.
-------------------------------------------------------
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Rnd(Key) * Second(Time()) * 1000 mod 1000 limit 1'.

Source: Microsoft OLE DB Provider for ODBC Drivers
Number: -2147217900 (0x80040e14)

 

[sleipnir214]

That would be because you are not running MySQL as your database backend. This forum is dedicated to the MySQL database server, not to general SQL questions.

Want the best answers? Ask the best questions!

TANSTAAFL!!