Chosing a variable from random columns and rows

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Citizen
Forum Contributor
Posts: 300
Joined: Wed Jul 20, 2005 10:23 am

Chosing a variable from random columns and rows

Post 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?
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post 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)
Last edited by josh on Sun Mar 26, 2006 8:02 pm, edited 2 times in total.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post 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]];
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
Citizen
Forum Contributor
Posts: 300
Joined: Wed Jul 20, 2005 10:23 am

Post 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.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post by josh »

my solution would take care of that for you but you could use is_empty??
Post Reply