CODEWITHSUNDEEP
phpmysql
Hare Kumar
Hare Kumar

Reputation: 719

query to display four random data from database

This is my php code for displaying the data from the database. I am trying to display the random data from table. 

<?php
include('connection.php');
$query="SELECT * FROM `banner_ad` ORDER BY RAND() LIMIT 4";
if($query_run=mysql_query($query))
{
    $i=4;
    $rows=mysql_fetch_array($query_run);
    while($rows)
    {
        echo $rows['banner_no'];
        echo $rows['banner_name'];
        echo "<a href=\"".$rows['Banner_website_url']. "\">";
        echo "<img src=\"".$rows['banner_image_url']."\" width=\"100px\" height=\"100px\">";
        echo"</a>";
    }
} else {
    echo'<font color="red"> Query does not run. </font>';
}
?>

But the problem with this code is:

It is displaying nothing. But whenever I am trying to make a little modification in the above code like: 

<?php
include('connection.php');
$query="SELECT * FROM `banner_ad` ORDER BY RAND() LIMIT 4";
if($query_run=mysql_query($query))
{
    $i=4;
    $rows=mysql_fetch_array($query_run);
    while($rows && $i<4)
    {
        echo $rows['banner_no'];
        echo $rows['banner_name'];
        echo "<a href=\"".$rows['Banner_website_url']. "\">";
        echo "<img src=\"".$rows['banner_image_url']."\" width=\"100px\" height=\"100px\">";
        echo"</a>";
        $i=$i-1;
    }
} else {
    echo'<font color="red"> Query does not run. </font>';
}
?>

It is displaying the same single output 4 times. But It has to display the four different output. So, Please tell me where is the bug ... And how am i suppose to display four different random output.

Any help will be appreciated Thanks in advance

Upvotes: 2

Views: 7120

Answers (4)

The Alpha
The Alpha

Reputation: 146191

$query_run=mysql_query($query);
if(!$query_run)
{
    echo'<span style="color:red">Query did not run.</span>';//font tag is ancient
}
else
{
    if(mysql_num_rows($query_run) > 0)
    {
        while($row = mysql_fetch_assoc($query_run))
        {
            echo $rows['banner_no'];
            echo $rows['banner_name'];
            // more...
        }
    }
}

Upvotes: 0

dognose
dognose

Reputation: 20899

Your first Query is fine, but the while is wrong:

Just look at what you did here:

$rows=mysql_fetch_array($query_run);
while($rows)
{
    echo $rows['banner_no'];
    echo $rows['banner_name'];
    echo "<a href=\"".$rows['Banner_website_url']. "\">";
    echo "<img src=\"".$rows['banner_image_url']."\" width=\"100px\" height=\"100px\">";
    echo"</a>";
}

this will end in an "infinite Loop" cause $rows will always be set. What you need is:

while($rows=mysql_fetch_array($query_run))

this will cause myslq_fetch_array to return a new line everytime the while condition is checked. And if all 4 rows are returned, $rows will be false and the loop is stoped.

And to be complete: In your second Example you are exactly iterating 4 times over the SAME row, you just fetched one time by calling myslq_fetch_array.

A possible solution to that will be to fetch the row again INSIDE the while-loop:

$i=4;
while ($i>0){
    $rows = mysql_fetch_array(...);
    $i--;
}

However you should prefer the first solution, because then you dont need to take care that the result count matches your iterator variable.

sidenode: Call it $row without the 's', because you always just getting ONE row back.

Upvotes: 2

Peter Krejci
Peter Krejci

Reputation: 3192

Using ORDER BY RAND() is not the best practice because the random value must be generated for every single row. Better way would be to randomly generate primary keys (e.g. ID) in PHP and then select according to them.

$random_id = rand(1,4);
$query="SELECT * FROM `banner_ad` WHERE id = $random_id";

Will select exactly one random row. Similar whould be selecting multiple rows using IN statement.

More info you can find here.

Upvotes: 0

Žan Kusterle
Žan Kusterle

Reputation: 570

Try constructing while loop like so

while(($rows=mysql_fetch_array($query_run)) !== false)

Upvotes: 0

Related Questions