将INT（11）与FLOOR（）进行比较会导致MySQL

Question

I have a table called quotes and I wanted to create a MySQL query that returns the data from a random quote. I searched over the internet and found how to create a random number from 1 to the number of quotes in the table:

SELECT FLOOR(RAND() * (SELECT ((SELECT COUNT(*) FROM DAILYS) - 1))) + 1 AS RANDNUM;

I have tested this and it works, it returns a random number every time and in the range that I need.

The problem is that I need to get information from two tables, QUOTES and AUTHORS. This has worked before:

SELECT QUOTES.QUOTE, AUTHORS.NAME, AUTHORS.LINK
FROM QUOTES JOIN AUTHORS ON QUOTES.AUTHOR_ID = AUTHORS.ID
WHERE QUOTES.ID =
(SELECT QUOTE_ID FROM DAILYS WHERE DAY = CURDATE());

Now I wanted to do the exact same thing, but insted of comparing the quote ID with today's ID, I will compare it to the random number. The type from the ID column is "int(11)" according to phpMyAdmin, so I don't know what might be wrong with it. Thank you for your time!

Answer 1

Your first query yields an integer. You can't compare that to a date (assuming that's what you're trying to do).

I combined your queries using the answer to this question:

SELECT QUOTES.QUOTE, AUTHORS.NAME, AUTHORS.LINK
FROM QUOTES
JOIN AUTHORS ON QUOTES.AUTHOR_ID = AUTHORS.ID
WHERE QUOTES.ID =
(SELECT QUOTE_ID FROM DAYLYS ORDER BY DAY LIMIT
(SELECT FLOOR(RAND() * (SELECT ((SELECT COUNT(*) FROM DAILYS) - 1)))),1);

I haven't tested this, but I'm fairly confident it should work (if I understand your database design correctly).

Edit: According to another answer on the page I referenced, "the offset is zero-indexed", so I removed "+ 1".