Randomizing the Results. 1

[Karl Blessing]

(I am using PHP on a *nix box)

I would like to make the results returned from this query return in a random order. ie: 1 2 3 4 5 => 2 4 1 3 5 , and different each time the query is run.

select f.ID as fid, d.Title as denom, c.denom as did, c.ID as ID, c.name, c.imgw, c.bio, c.imgh, c.city, s.state as state, co.description as size FROM featured as f, churches as c, states as s, congregation as co, denomination as d where s.stateid = c.state AND co.ID = c.size AND c.ID = f.clientid AND f.type = 'c' AND d.ID = c.denom

(also if anyone knows how to make that shorter/better let me know )

Karl Blessing
PHP/MySQL Developer

http://www.karlblessing.com

 

[TonyGroves]

You just need to add ORDER BY RAND() to the end. The only improvement I would suggest is to use JOIN...ON syntax instead of the comma join operator:
[tt]
select
f.ID as fid, d.Title as denom, c.denom as did,
c.ID as ID, c.name, c.imgw, c.bio, c.imgh,
c.city, s.state as state, co.description as size
FROM
featured as f
JOIN churches as c ON c.id=f.clientid
JOIN states as s ON s.stateid=c.state
JOIN congregation as co ON co.id=c.size
denomination as d ON d.id=c.denom
where f.type = 'c'
ORDER BY RAND()
[/tt]

 

[TonyGroves]

Sorry, I left out a JOIN on the "denomination" line.

 

[Karl Blessing]

Ahh thanks for that bit. Appears to work, will know more once I start getting more church/pastors in for the working demo.

Karl Blessing
PHP/MySQL Developer

http://www.karlblessing.com