Page 1 of 1

Chosing a variable from random columns and rows

Posted: Sun Mar 26, 2006 7:56 pm
by Citizen
I have columns website, website1, website2, website3, and website4

Each has a link as its value.

I need $website to be one random link from those 4 columns from a random row.

My current code is this:

Code: Select all

$sql="SELECT * FROM `accounts` WHERE points > 1 ORDER BY RAND() LIMIT 1";
$result = mysql_query($sql);
$row = mysql_fetch_array($result);
$website = $row["website"];
But now I want it to pick $website from more than just the `website` column. What do I do?

Posted: Sun Mar 26, 2006 8:00 pm
by josh
Sounds like you need a relational table, remove the `websiteN` fields and create a `websites` table, then do a

select `website` from `websites` WHERE `account_id` = 34 order by rand() limit 1

(also this allows an arbitrary number of webistes.. relational tables are usually the way to go when dealing with more then 2 dimensions of data)

Posted: Sun Mar 26, 2006 8:00 pm
by s.dot
hmm

i can think of one solution

Code: Select all

$fields = array('website','website2','website3','website4');
$rand = rand(0,4);

$sql = "SELECT * FROM `table` ORDER BY RAND() LIMIT 1";
$result = mysql_query($sql);
$row = mysql_fetch_array($result);
$website = $row[$fields[$rand]];

Posted: Sun Mar 26, 2006 8:05 pm
by Citizen
Thanks guys, Scott's was what I was looking for. How to I make it so that it only selects it if the field isn't empty? All of the `website` cells have a value but not alll of the website1, website2, etc have values. Some are null by default or empty if the user emptied it.

Posted: Sun Mar 26, 2006 9:14 pm
by josh
my solution would take care of that for you but you could use is_empty??