Query help

[geniuscrew]

For The Ultimate Quote hack by Kurafire, the quotes are shown randomly using this query.

// random quote hack by KuraFire
$quot=$DB_site->query_first("SELECT quote,name FROM quotes ORDER BY rand() LIMIT 1");
$thequote = $quot['quote'];
$thequoter = $quot['name'];

how can I change it so it goes through all the quotes in order?

[They each have an id and ORDER BY id doesn't seem to be working :/]

Thanks

[Boofo]

Originally posted by geniuscrew
For The Ultimate Quote hack by Kurafire, the quotes are shown randomly using this query.

// random quote hack by KuraFire
$quot=$DB_site->query_first("SELECT quote,name FROM quotes ORDER BY rand() LIMIT 1");
$thequote = $quot['quote'];
$thequoter = $quot['name'];

how can I change it so it goes through all the quotes in order?

[They each have an id and ORDER BY id doesn't seem to be working :/]

Thanks

I think it would be ORDER BY DESC LIMIT 1

[geniuscrew]

That gives an error. I tried ORDER BY id DESC - but now it just shows the last quote added

[Boofo]

Originally posted by geniuscrew
That gives an error. I tried ORDER BY id DESC - but now it just shows the last quote added

Try this:

ORDER BY quotesid desc LIMIT 1

[Boofo]

If that doesn't work, maybe this will?

ORDER BY quotes desc LIMIT 1

[geniuscrew]

Still no luck - thanks anyway

[Steve Machol]

geniuscrew, to download hacks and get support you will need to go to this (http://www.vbulletin.com/members/forums.php) page and enter your email address, to show you are licensed. (you will need to use your customer number and password to access that page)
Thank you.

[geniuscrew]

sorry i just changed my email and fogot to update the thing -

Back on topic: can anyone help please?

[Erwin]

Take out the LIMIT 1 - that only limits it to 1 entry.

[Boofo]

I think they only want them to list 1 at a time but in order and not randomly, if I understood right, that is.

[geniuscrew]

Yup that's right Boofo :)

[Erwin]

Use this:


$quot=$DB_site->query_first("SELECT quotesid,quote,name FROM quotes ORDER BY quotesid DESC LIMIT 1");

[Boofo]

Originally posted by Erwin
Use this:


$quot=$DB_site->query_first("SELECT quotesid,quote,name FROM quotes ORDER BY quotesid DESC LIMIT 1");


I was close. I forgot to do the first quotesid (actually I didn't know it had to be done that way). ;)

Let me know how it works, geniuscrew.

[geniuscrew]

Actually Boofo even though you didn't say, I was putting the id bit in the SELECT part [at the beginning] ;)

Still it's only showing the last quote. :/

[Erwin]

That is because that is what you wanted. The query chooses the very last quote listed.

Change DESC to ASC will make it choose the first quote listed. But it will not be random. It will always choose the first quote, or last quote.

[geniuscrew]

So there's no way of making it go through the list of quotes and display them one by one?

If not how do i make it even more random?

Thanks for the help guys.

[Boofo]

Originally posted by geniuscrew
So there's no way of making it go through the list of quotes and display them one by one?

If not how do i make it even more random?

Thanks for the help guys.

Use RAND(NOW()) in place of RAND like I posted earlier in the thread. The NOW() supplies the necessary seed for the newer versions of mySQL. Trust me. It works. ;)

[mr e]

you could add another column that would display if it's been listed yet, not sure how to make it more random though

[geniuscrew]

Mr E: But would that let the same quote be used twice?

Boofo: Is that the most randomest of randoms? :) I did try it before ya know and it worked, but wasn't what I was looking for. But I'll use it :P


Thanks ppl.

[Boofo]

According to the mySQL site, that is more random than the old way (with RAND() alone) ever was. The seed needs to be there now and this is the automatic way to do it. It's all I use anymore and I have noticed a world of difference. But then I have over 642 quotes, so that helps. I use this for the member of the day hack and it makes a big difference there, too. Try it for a few days and let me know what you think. You will be amazed. ;)

[geniuscrew]

Ah- Hah - I thought I needed to add more quotes - I only have 4 and now it's sitting nicely ont the second one -_- - I'll add more and get back to ya.

Thanks for all your help and for putting up with my annoyance for that matter lol

Peace.

[Boofo]

No problem. Happy to help. ;) Keep me posted.

[mr e]

@geniuscrew you'd add another column with default of 0 then after it's displayed the quote the column would be set to 1, after all the columns are one, then all columns would be set back to 0 and everything would start over again

[Erwin]

Originally posted by geniuscrew
So there's no way of making it go through the list of quotes and display them one by one?

To make it go down the list one by one, you need to make your site remember each member somehow, and remember which quote they have looked at, store it, so that the next time they return, the site can go to the next quote.

You can do this by adding a new column to the user table, and saving the quoteid that the member has looked at, and just add 1 after that OR use cookies to save the information.

Either way, it can be done, but would take some effort. :)

[geniuscrew]

Thanks Mr E and Erwin. Unfortunately I don't have the brains to do that kinda stuff.