Documentation Details :: Image gallery tutorial

Hi,

Just working my way through the tutorials and I seem to have come across a problem in the Image Gallery > When trying to create a random image for the default page I get this error relating to the SQL in the recordset Dialog box

SELECT *, rand() as random_number
FROM image_img
ORDER BY random_number DESC LIMIT 1

Syntax error ORDER BY clause

Something tells me I had this problem too quite some time ago. Oddly enough I was doing this sort of thing today and here is what I am using and it has been working for months with different situations. I have used it for Random Images, Random comments, etc. :

SELECT *
FROM image_img
ORDER BY RAND() Limit 1

Try it and see if it works for you.

Hi Frederick,

Both SQL queries work on our testing server. The problem may depend on the actual database system you are using - MySQL, Access, etc. so please provide this information.

Regards,
Cristian

Thanks for the help but that doesn't work either?

Hi Cristian,

I'm using an access data base in fact its the one shipped with the tutorial so i don't think thats the problem?

You should probably post the whole page here so either the support crew or one of the forum dwellers can look it over. I suspect that your query isn't the only problem. Also post your OS, code languange (PHP, ASP, etc.) and Database of choice so we can help you fix it.

As i mentioned earlier i am only working through the image gallery tutorial from the support files, nevertheless i have attached the file . Im using Access and ASP to complete the tutorials? sorry for being a pain, just learning?

Hi Stephen,

There are two problems with the queries and Microsoft Access:

1. Access does not support the LIMIT clause in SQL queries.
2. The Rand() function does not exist. Instead you should use rnd().

The query that works is:

SELECT *
FROM image_img
ORDER BY RND();

Hope this helps,
Cristian

Thanks for that at least its got rid of the ORDER BY error i was getting but this statement still does not randomly pick the images i keep getting the same image each time i refresh or reopen the browser?

Hi Stephen,

And sorry for the late reply, but we have been very busy lately. The problem that you are facing is actually related to ACCESS - although it has rnd() as a random number generating function, it will not return true random results.

To get a random record, you must also use some ASP code. There are two steps to follow in order to get this to work:

1. Create a simple recordset on the image_img table. This is done in order to find the last record's ID.
2. Switch to code view. After the code defining the query, you must create a new code block that will initialize the random number generator, and create the random number. The code would be similar to the following (assuming the first recordset is named rsNumbers):
   <%
   Randomize()
   random_number = Int(Rnd*RsNumbers_last+1)
   %>
3. Create a second recordset that retrieves the images. In the recordset user interface click the Advanced button and define a new variable (e.g. random). For the default value enter -1 and for the run-time value use random_number. Then, in the SQL code add a WHERE clause:
   SELECT * FROM image_img WHERE id_img <=random
4. Click OK to create the recordset, then switch to code view. Make sure that the code block you created is placed before the second recordset code, and after the first one. If it is not, move it in the right position (the random number must be computed before it is used).
5. Test the page. The query should return a random image

Note that using Randomize() will not provide fully random results, as it has trends, but is an efficient and fast way of getting pseudo-random data.

Hope this helps,
Cristian

 Thanks Christian,

Tried your solution with some partial success, not sure what ive got wrong but i have attached the page if its any help.