FRIHOST • FORUMS • FAQ • TOS • BLOGS • DIRECTORY
You are invited to Log in or Register a Frihost Account!

select x random rows from database

   Frihost Forum Index -> Scripting -> Php and MySQL
 


alalex
I want to select 4 random rows from a mysql database that doesn't have numerical id's. The primary key is a column called name, which contains names Wink
What I want is to select 4 random rows from that table, and right now im using this query:
Code:

$nr = 4; //number of rows to select
$sql = 'SELECT * FROM `enlaces` ORDER BY rand() LIMIT 0,'.$nr.';';
$result = mysql_query($sql);
while($row = mysql_fetch_array($result)){
   echo row['name'].'-'.row['url'];
   return true;
}


It works, but not as I wanted it to work, since it only returns one row, at random.
So then I tried to loop it and get 4 rows, but some times I get duplicate rows, so what I need is a way to get all four rows at the same time, so they are not duplicate.

Thanks in advance! Wink
Star Wars Fanatic
When I tried it, I was getting 4 rows returned, though I ran it through phpMyAdmin.
Make sure it returns 4 rows by running it via whatever MySQL database accessing tool you have, be it phpMyAdmin, or something else.

If it doesn't work, try this query instead:

Code:
$sql = 'SELECT * FROM `enlaces` ORDER BY rand() LIMIT '.$nr.';';
alalex
OK, I think I know what is the problem, although I'm still not being able to get it to work.
The query is ok, but the problem is that to display the results on the webpage it is not looping four times.
Code:
$sql = 'SELECT * FROM `enlaces` ORDER BY rand() LIMIT 4;';
$result = mysql_query($sql);
if(mysql_num_rows($result) == 4){
while($row = mysql_fetch_array($result)){
   echo row['name'].'-'.row['url'];
   return true;
}
}else{
echo 'Not enough links in database';
}


And it returns only one link! So there must be something wrong in the while loop!
alalex
I just got it to work!! Very Happy
Thanks for the sql Star Wars Fanatic, it really helped

The while loop worked when I removed the return true; line from it!
So now is:
Code:
$nr = 4; //number of rows to select
$sql = 'SELECT * FROM `enlaces` ORDER BY rand() LIMIT 0,'.$nr.';';
$result = mysql_query($sql);
while($row = mysql_fetch_array($result)){
   echo row['name'].'-'.row['url'];
}
Star Wars Fanatic
Oh my gosh, now I'm mad at myself for not seeing that...

In case you didn't know, the return true lines ends the loop, so it ends on the first time through. Which is why you were only getting one line out...

If you still need the return true, put it after the while loop ends.

And sorry I didn't see it, I didn't look carefully enough over the entire code... Embarassed
alalex
its ok! Very Happy
I just thought the same as you! I was just not looking at the loop, and only looking at the sql query, and then suddenly I realised that there was return true;! Embarassed

is funny to see what mistakes we sometimes make!
SlowWalkere
Star Wars Fanatic wrote:
In case you didn't know, the return true lines ends the loop


That's not entirely accurate...

A "return" statement ends the current function and returns a value to the original function/script that called it.

So, for example, in the function mysql_fetch_array() there is a return statement that ends the function and sends the information back... so it can be saved in the $row variable.

If you place a return statement in a main php page (instead of a function called by that page) it will halt execution of the page altogether. If there was extra code below the return (outside of the loop), it would never be called.

What you're probably thinking of is a "break" statement. This is commonly used to end a select/case statement, but it can also be used for loops (and I think "if" statements, too). This will terminate the current loop and continue the execution of the rest of the script.

For anyone that's unclear on the difference between "break" and "return," try running these simple scripts. You'll notice that in the first example, the last line is never echo'ed. In the second example, the loop ends, but the last line is still executed.

Code:
<?php
   echo "Point 1.<br />";
   $x = array('One', 'Two', 'Three');
   foreach ($x as $y)
   {
      echo $y . "<br />";
      return true;
   }
   echo "And now we're at the end... <br />";
?>


Code:
<?php
   echo "Point 1.<br />";
   $x = array('One', 'Two', 'Three');
   foreach ($x as $y)
   {
      echo $y . "<br />";
      break;
   }
   echo "And now we're at the end... <br />";
?>


- Walkere
Star Wars Fanatic
SlowWalkere wrote:
Star Wars Fanatic wrote:
In case you didn't know, the return true lines ends the loop


That's not entirely accurate...

A "return" statement ends the current function and returns a value to the original function/script that called it.


Yea, but I see no function there, so I didn't mention it, but thanks for the lengthy explanation. Wink
SlowWalkere
Star Wars Fanatic wrote:
Yea, but I see no function there, so I didn't mention it, but thanks for the lengthy explanation. Wink


Aye, I figured you knew the difference... I just didn't want any unsuspecting newbies to come across the thread in a search and get confused.

- Walkere
alem
a small note:

we can also break the loop we are in and the outer loop or loops. For this we should use break with a number as
Quote:
Code:
break 2;//means break the inner and outer loop


for info here you are.
AftershockVibe
You need to be careful using break; though - it can be a poor man's GOTO when used to break multiple loops.

Ideally you just want to keep the ending condition in the starting condition of the loop so everything is obvious.
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2007 Frihost, forums powered by phpBB.