Comparing INT (11) with FLOOR () results in MySQL
I have a table called quotes and I wanted to create a MySQL query that returns data from a random quote. I searched through the internet and found how to generate a random number between 1 and the number of quotes in a table:
SELECT FLOOR(RAND() * (SELECT ((SELECT COUNT(*) FROM DAILYS) - 1))) + 1 AS RANDNUM;
I 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 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 same, but when I try to compare the quote ID to today's ID, I will compare it to a random number. The type from the ID column is "int (11)" according to phpMyAdmin, so I don't know what might be wrong. Thank you for your time!
source to share
The first query gives an integer. You cannot compare this to a date (assuming what you are trying to do).
I have 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 pretty sure it should work (if I understand your database design correctly).
Edit: As per another answer on the page I linked to, "offset is zero indexing", so I removed the "+ 1".
source to share