#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Location
    New York City, New York
    Posts
    28
    Rep Power
    0

    Get Random Quote


    I need some help working out this MySQL query:

    I need it to pull a random quote, that hasn't been used before (that's the 'checked' part of the query), and it needs to find it based on a specific category. I am using this alongside PHP with MySQLI Prepared method.

    Code:
    SELECT `id`, `word`, `def` FROM `dictionary` AS `r1` JOIN (SELECT (RAND() * (SELECT MAX(`id`) FROM `dictionary`)) AS `id`) AS `r2` WHERE `r1`.`id` >= `r2`.`id` AND `category`=? AND `checked`=0 ORDER BY `r1`.`id` ASC LIMIT 1
    This query yields an error stating that the id column is ambiguous.

    I'm trying to base this on this: ~jk - ORDER BY RAND()
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Location
    New York City, New York
    Posts
    28
    Rep Power
    0
    Solved. Needed to add `r1` to the SELECTed columns.

IMN logo majestic logo threadwatch logo seochat tools logo