Putting the results of a QUERY into an ARRAY?

denislav

Hi everyone

I'm trying to get a random result from a query. I don't want to use ORDER BY RAND() LIMIT 1 because it needs a query for itself and i'm afraid that on a large table it might get slow. So I want to use the array_rand function but I'm not sure how to put the results of the query into the array.
I use the code from php.net

srand((float) microtime() * 10000000);
$input = array("Neo", "Morpheus", "Trinity", "Cypher", "Tank");
$rand_keys = array_rand($input, 2);
echo $input[$rand_keys[0]] . "\n";
echo $input[$rand_keys[1]] . "\n";

but instead of "Neo", "Morpheus" etc I want to have the values of a field from the results. I tried something like

$input = array();
while ($row2 = mysql_fetch_array($result)) { 
$input[] =  $row2['myfield'];
}

but it doesn't work.
Maybe this makes you laugh but please help me out!

Castle

give this a try

$counter = 0
$input = array();
while($row2=mysql_fetch_array($result)){
$input[$counter]=$row2['myfield'];
$counter++;
}

denislav

Nothing...

still gives me
Warning: array_rand(): Second argument has to be between 1 and the number of elements in the array

do you think $row2['myfield'] has the right syntax?

Castle

Are you sure about the nubmer of objects you are returning. We can make it dynamicly count the number of values in the array based on the array built to make sure you are getting the correct number of elements for the array_rand() function by doing a count on the array to ge the number of elements in the array.

$arraySize = count($input);
$keys = array_rand($input,$arraySize);

This would eliminate the chance you are pulling back a different number of items then you thought you were.

Also check to see if you are pulling you results back being associtaed with the field name

$row = mysql_fetch_array($result, MYSQL_ASSOC)

Also you can try changing the single tics to quotes

$input[] = $row2["myfield"];

Foo

You realise that $row2 is already an array containing the returned values from your query? You could simply use that, I think.

Also, you could use the foreach loop to create a new array from the data like this:


foreach ( $row2 as $rowkey => $rowvalue )
{

$Myarray[$rowkey] = $rowvalue;

}

That should work. I had a similar question to you last night, and resolved it in these 2 ways.

denislav

yes I know that $row2 is an array with the values from the query but it seems I don't use it properly.
I tried

$row2 = mysql_fetch_array($result);
$input = array($row2[myfield]);
and 
$input = $row2[myfield];[/

but it returns the same error. I'm not sure about the syntax..where and how should i insert the returned values to be understod and used by the script?!
I tried also with foreach but didn't get it right. What $rowvalue should be? $myfield?

psycorpse

Are the fields that you are pulling out going to be random? or just the rows?

It sound like you want to randomly pick a row and display it. Is that correct?

If it is you could do something like this..

function random_number($min, $max){
 return (rand() % (($max - $min) +1) +$min);
}

$sql = "SELECT count(*) FROM table";
$result = mysql_query($sql);
$row = mysql_fetch_array($result);
$num_rows = $row["count(*)"];

$id_to_pick_from_sql = random_number('1',$num_rows);

// Now run your sql statement again
$sql = "SELECT * FROM table WHERE id='$id_to_pick_from_sql'";
$result = mysql_query($sql);
if($row = mysql_fetch_array($sql)){
  echo $row[0]; //or What ever you field name is
}

This will run a count of records first.
Then it will run a function to get a random number.
Then it will run a query based on the id of your records.....

Just thinking about it this might lead to some problems every now and then... The problems could be if the row was deleted then that number wouldn't be available and the random number generator doesn't know that. Another issue might be if you dont do an id type field with an auto_increment then you wouldn't be able to use this. Other wise i hope this helps

denislav

Got it right!

$sql = mysql_query ("SELECT views FROM stats WHERE 1"); 
while($row = mysql_fetch_array ($sql)) {
	$row_array[] = $row['views']; 
  }
$random_row = $row_array[rand(0, count($row_array) - 1)]; 
$row_array2[] = $random_row;
echo "Random value: $random_row <p>";

Thanks to everyone who replyed!🙂