I have connected my database from java, I am using mysql. I am able to retrieve images from database , but when I try and do it by random it gives me syntax error.

Here is the part of the java code

String cmd ="SELECT * FROM image ORDER BY rand() LIMIT 6000 " +
"JOIN tag_value tv ON tv.image_id = image.id " +
"WHERE (tv.tag_id = 1 AND "+ red_colorVal + " BETWEEN tv.STARTVAL and tv.ENDVAL) OR " +
"(tv.tag_id = 2 AND " + orange_colorVal + " BETWEEN tv.STARTVAL and tv.ENDVAL) OR "+
"(tv.tag_id = 3 AND " + yellow_colorVal + " BETWEEN tv.STARTVAL and tv.ENDVAL) OR " +
"(tv.tag_id = 4 AND " + green_colorval + "BETWEEN tv.STARTVAL and tv.ENDVAL) OR" +
"(tv.tag_id = 5 AND " + baby_blue_colorval + "BETWEEN tv.STARTVAL and tv.ENDVAL) OR" +
"(tv.tag_id = 6 AND " + dark_blue_colorval + "BETWEEN tv.STARTVAL and tv.ENDVAL) OR" +
"(tv.tag_id = 7 AND " + pink_colorval + "BETWEEN tv.STARTVAL and tv.ENDVAL) OR" +
"(tv.tag_id = 8 AND " + dark_red_colorval + "BETWEEN tv.STARTVAL and tv.ENDVAL)";

If I remove underlined part its working fine but when I add it its giving me an error.

Here is the error
1064You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'JOIN tag_value tv ON tv.image_id = image.id WHERE (tv.tag_id = 1 AND 0.0 BETWEEN' at line 1

What I am trying to do is that I have 8 sliders on front end part and between slider start val and endval specific images appear ,instead of that I want it to be selected randomly.

Thank you,
--
Dhruv Adhia
http://thirdimension.com

I'd expect the ORDER BY clause to come after any where clause

String cmd ="SELECT * FROM image " +
"JOIN tag_value tv ON tv.image_id = image.id " +
"WHERE ORDER BY rand() LIMIT 6000 (tv.tag_id = 1 AND "+ red_colorVal + " BETWEEN tv.STARTVAL and tv.ENDVAL) OR " +
"(tv.tag_id = 2 AND " + orange_colorVal + " BETWEEN tv.STARTVAL and tv.ENDVAL) OR "+
"(tv.tag_id = 3 AND " + yellow_colorVal + " BETWEEN tv.STARTVAL and tv.ENDVAL) OR " +
"(tv.tag_id = 4 AND " + green_colorval + "BETWEEN tv.STARTVAL and tv.ENDVAL) OR" +
"(tv.tag_id = 5 AND " + baby_blue_colorval + "BETWEEN tv.STARTVAL and tv.ENDVAL) OR" +
"(tv.tag_id = 6 AND " + dark_blue_colorval + "BETWEEN tv.STARTVAL and tv.ENDVAL) OR" +
"(tv.tag_id = 7 AND " + pink_colorval + "BETWEEN tv.STARTVAL and tv.ENDVAL) OR" +
"(tv.tag_id = 8 AND " + dark_red_colorval + "BETWEEN tv.STARTVAL and tv.ENDVAL)";

Is that what you mean?

nope

the sequence of SELECT statement clauses is given in the manual, which you should definitely refer to

the ORDER BY clause cannot be made part of the WHERE clause

don't guess -- look it up

---> SELECT Syntax

Hello r937,

Look at my first post, thats how I did it initially. I changed it after I got reply. Can you have a look at my first post and tell me whats wrong in it?

Thank you,
Dhruv

the ORDER BY is in the wrong place

you've stuck it into the middle of the FROM clause

please read da manual, ORDER BY comes at the end

don't guess -- look it up

Thanks its working but not the output I wanted, its continuously showing random images.