Select Two Random Results from One Array?

[suleiman]

Okay, so here's my problem:

I have a table structure like so:

###blog_id#####domain####
--------2-------------dude.dude.com
--------3-------------man.dude.com
--------4-------------etc.etc.com

I want to be able to loop through the table and pull out a random domain but ALSO pull out the corresponding blog_id.

This is important so that I can later use code like [$values[rand_id[2]] and [$values[rand_domain[2]] and have that output what i want.

Here's what I've already got cracking:

$sql="SELECT * FROM wp_blogs WHERE (public = '1' AND mature = '1' AND spam = '0' AND deleted = '0' AND blog_id != '1')";



$result = mysql_query($sql);


while ($row = mysql_fetch_assoc($result)) {

 $randomdomain = $row['domain'];
 $randompath = $row['path'];
 $randomids = $row['blog_id'];
 if ('/' == substr($randompath, strlen($randompath)-1)) $randompath = substr_replace($randompath, '', strlen($randompath)-1);  // strip trailing slash

 $randomurl = $randomdomain . $randompath;
 array_push($values, "$randomurl");
array_push($randomid, "$randomids");	
}
shuffle($values);
shuffle($randomid);

$rand_keys = array_rand($values, 6);
$rand_id = array_rand($randomid, 6);

I can use $values[$rand_keys[4]] to get the 4th random entry but $randomid[$rand_id[4]] does not produce the correspdoning random id for the domain that gets pulled when i use $values[$rand_keys[4]].

The second problem that I'm having isn't really a problem but more of an efficiency question. Right now I'm using the following:

$sql2 = $wpdb->get_var("SELECT ID FROM wp_users WHERE user_url = 'http://" .$values[$rand_keys[1]]."/'");
$sql3 = $wpdb->get_var("SELECT ID FROM wp_users WHERE user_url = 'http://" .$values[$rand_keys[2]]."/'");
	$sql4 = $wpdb->get_var("SELECT ID FROM wp_users WHERE user_url = 'http://" .$values[$rand_keys[3]]."/'");
	$sql5 = $wpdb->get_var("SELECT ID FROM wp_users WHERE user_url = 'http://" .$values[$rand_keys[4]]."/'");

And I'm fairly sure there's a more efficient way of doing that also! lol...does anyone have any help to offer? I would be much obliged. 🙂

[devinemke]

are you simply trying to get random rows from your db table? if so...

SELECT * FROM wp_blogs ORDER BY RAND()

[Sxooter]

Note that select * from wp_blogs order by rand()

will NOT scale to any large size table.

[suleiman]

What should i be using instead Sxooter?

[laserlight]

You could take a look at Sxooter's Fast Random Results thread, though honestly it may depend on your needs.

[Sxooter]

suleiman wrote:

What should i be using instead Sxooter?

That really depends.

select * from table order by rand() limit 1

is kind of the bubble sort of getting a random row. Fine for small sets, mediocre for moderate sets, and horribly slow for big sets.

If your table will only ever hold 100 or 1000 rows, then order by rand() is ok.

The thread mentioned above (which should really be made sticky I think) has two different solutions in it which will work well for most data sets. Basically, there's no way to get a truly random row cheaply without some overhead of a seconday table, but you can get somewhat random results cheaply if your setup will work for the select * from table where id >=random_number limit 1, i.e. it doesn't have a lot of / any holes in the sequence.

[suleiman]

Thanks sxooter and everyone else who replied.

I took a look at your thread on optimizing the randomizer function, and to be honest at this point it doesn't really suit my needs, because I'm dealing with a very small database.

But I also noticed that no one responded to my original inquiries. Is it possible to select from 1 column randomly, and also extract the corresponding random values from a separate column?

[Sxooter]

just select the whole row randomly. you'll get the two columns that go together.

[suleiman]

could you perhaps give me some sample code sxooter? I'm doing some readups on doing joins and the like because i'm actually needing to get data from 3 different tables.

Also, if i select a row randomly, how would i go about referencing that later on in the code? Right now I use $rand_keys[1]], $rand_keys[2]], etc., and this gives me a single value.

But if there's 2 values in a given row that I'm interested in now, how would I later output the data?

[Sxooter]

This is pretty basic. I've already posted example code of how to select a random row in the sticky thread at the top of this page. Work with that, and when you get stuck, post again. I'm not writing your code for ya today, as I'm babysitting a production outage at the moment.

[suleiman]

Thanks Sxooter, I'll take a look at the code and try and figure it out on my own.

I'm less concerned with figuring out how to Join the tables and select a random row. I'm more concerned with how to extract the results into variables.

i need to be able to use code like $rand_url[1]] and $rand_id[1] and have those two correspond to each other.

[Sxooter]

This is the method. All you need is max(id), min(id) and then use php to create a random number between with rand(min,max)

select * from table where id >= $id limit 1;

[suleiman]

select * from table where id >= $id limit 1; looks to me like it's only inserting one value into the $id variable.

I need to generate 4 random values for $id and $url. would I just change it to this:

select * from table where id >= $id limit 4; ?

[Sxooter]

If they need to all be random, then you need to run it four times. If they can be sequential, then limit4 would work.

OTOH, if you use the "select id into newtable from oldtable order by random()" method in my other post, then limit 4 would give you random results.

[suleiman]

and then I would use what? $id[1]...$id[2] and so on and so forth? And the value for $id[1] would be in the same row as the value for $url[1] ?

[suleiman]

EDIT: double post.

[Sxooter]

Yep. Each row returned would be coherent on its own.

write some code and test it out and see how it works.