Or just go back and rewrite the numbers so they are consecutive.

Every time something's deleted? I dunno ...

Okay -- so we pick a random number -- what's the upper ceiling, or do we hit the database for that beforehand?

Then we do select charactername, quotation, quotes.season, quotes.episode, ep_title from quotes left join episodes on episodes.show_name=quotes.show_name and episodes.episode=quotes.episode and episodes.season=quotes.season where is_approved='Y' where quotes.id >= randomnumber limit 1, you're saying?

Personally, for something as trivial as quote frequency, I *suspect* the overhead in connecting, counting, returning, tearing down, connecting, selecting, returning, tearing down is more than the ORDER BY RAND() LIMIT 1.

But I admit I have no benchmarks.

what's the upper ceiling, or do we hit the database for that beforehand?

My quick fix is to say "how many do we have now? hard-code that in and remember to change it when we put some new quotes in" because only you are going to be putting new quotes in anyway.

select charactername, quotation, quotes.season, quotes.episode, ep_title from quotes left join episodes on episodes.show_name=quotes.show_name and episodes.episode=quotes.episode and episodes.season=quotes.season where is_approved='Y' where quotes.id >= randomnumber limit 1

That seems incredibly long to me. What's all that "show_name" and "season" stuff doing in there? If we don't display it? I'm puzzled, sorry.

Corrected.

select charactername, quotation, quotes.season, quotes.episode, ep_title
from quotes
left join episodes
on episodes.show_name=quotes.show_name and episodes.episode=quotes.episode and episodes.season=quotes.season
where is_approved='Y' and quotes.id >= randomnumber limit 1

Showname is in the join, not the fields returned -- a join on episode 4, season 2 in the episode database will return Inca Mummy Girl and Untouched. We need to know which show a quote is from too. The season is in the fields returned because that's a field in the quote data structure, as is the episode number. It's not mandatory for the display, but puts little additional drain on the system, so I didn't remove it after we started displaying titles instead of ep numbers.

I have to repeat my position against hardcoding. The only reason I'm the only one massaging the table is because the code hasn't been written yet.

Admittedly I'm absolutely not bothered by having one quote come up much more than the other. I don't read them. And I really don't want to write kludgey code or double database hits for them either.

Showname is in the join, not the fields returned -- a join on episode 4, season 2 in the episode database will return Inca Mummy Girl and Untouched.

D'oh. Sorry about that. Staring me in the face.

I have to repeat my position against hardcoding. The only reason I'm the only one massaging the table is because the code hasn't been written yet.

I only meant that as a quick and dirty fix.

So the next best thing is to just select a COUNT from quotes and make that the randomnumber. Can we put that into the select, or do we have to do two selects, one to get the count and one to get the quote? it's been a while since I've thought about SQL.

We'd have to do two hits.

Is this *really* worth coding? That's my skepticism. I think the quotes are cute, and that's about it.

I don't care about the quotes, or the less-than-truly-random-ness of them, I'm just interested in the tech. I'd really like to know why it's not being very random. I certainly didn't see a whole bunch of posts in usenet saying "that damn ORDER BY RANDOM() is broken, everyone knows that", just a few saying it wasn't very efficient.

t I just love eavesdropping in here, listening to all the smart people speaking knowledgeably about stuff of which I understand about one word in ten. I think I'm a geek voyeur

Too add some contrast, I'm so ignorant of SQL that I can't even spell it.

Okay, So does this have any importance? I got it when I hit "read New" while in Natter:

Parse error: parse error, unexpected '[' in /home/buffist/public_html/classes/user.php on line 270

Warning: Cannot send session cache limiter - headers already sent (output started at /home/buffist/public_html/classes/user.php:270) in /home/buffist/public_html/classes/giles.php on line 11

Fatal error: The script tried to execute a method or access a property of an incomplete object. Please ensure that the class definition user of the object you are trying to operate on was loaded _before_ the session was started in /home/buffist/public_html/classes/thread.php on line 99

Then I hit refresh and it loaded the page.