Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2009
    Posts
    4

    Unanswered: select by rand()

    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

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I'd expect the ORDER BY clause to come after any where clause
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2009
    Posts
    4
    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?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Dhruv_Adhia
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2009
    Posts
    4
    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

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Dhruv_Adhia
    Can you have a look at my first post and tell me whats wrong in it?
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Apr 2009
    Posts
    4
    Thanks its working but not the output I wanted, its continuously showing random images.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •