Welcome to WebmasterWorld Guest from 54.162.239.134

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

Random show from db from 3 fields

need help showing array

     
5:58 pm on Mar 9, 2011 (gmt 0)

5+ Year Member



I am creating a random song showing from db. I have the song, artist and lyrics in 3 different fields in same db. How do I put that in the array?


$songquery="SELECT * from songdb WHERE lyrics !='NONE'";
$songqueryresult=mysql_query($songquery);
while($songrowdata=mysql_fetch_array($songqueryresult))
{ $song=$songrowdata['song'];
$artist=$songrowdata['artist'];
$lyrics=$songrowdata['lyrics'];


$songs[] = $dbsongs

srand ((double) microtime() * 1000000);
$randomsong=rand((0,count($songs)-1);

echo "<p>" . $songs[$randomsong] . "</p>";
}


Thanks,
Matt
6:07 pm on Mar 9, 2011 (gmt 0)

WebmasterWorld Senior Member topr8 is a WebmasterWorld Top Contributor of All Time 10+ Year Member



you could change your sql to:

SELECT * from songdb WHERE lyrics !='NONE' ORDER BY RAND()

this will return songs in a random order.

it is not the most efficient way of doing it but assuming you don't have 10's of thousands of songs it will be fine
3:41 am on Mar 10, 2011 (gmt 0)

5+ Year Member



Ok thanks I will give it a try.
4:54 am on Mar 10, 2011 (gmt 0)

5+ Year Member



I just can't quite get it working. The following code shows all the songs instead of just 1 ?


$songquery="SELECT * from db WHERE lyrics !='NONE'";
$songqueryresult=mysql_query($songquery);
while($songrowdata=mysql_fetch_array($songqueryresult))
{ $song=$songrowdata['song'];
$artist=$songrowdata['artist'];
$lyrics=$songrowdata['lyrics'];

$songs[]="".$song."<br />".$artist."<br /><a href='http://www.sample.com/".$lyrics."'>Lyrics</a>";

srand((double) microtime() * 1000000);
$randomsong=rand(0, (count($songs)-1));

echo "<p>".$songs[$randomsong]."</p>";
}
4:57 am on Mar 10, 2011 (gmt 0)

WebmasterWorld Administrator brotherhood_of_lan is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month



Add "LIMIT 1" to the end of the query

[dev.mysql.com...]
The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants (except when using prepared statements).
5:19 am on Mar 10, 2011 (gmt 0)

5+ Year Member



Thank you brotherhood. Works great. Now we'll see if the song changes tomorrow.
5:16 pm on Mar 10, 2011 (gmt 0)

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member



Umm . . . it may not. :-) Random can often pick the same record. What you need to do is store a "viewed" value somewhere, so that it only picks from those not viewed in the most recent random go 'round. When they are all used up, reset viewed to 0 and pick again.

select count(*) from table where viewed = 0;

if ($count == 0) {
update table set viewed=0;
}

.... now do your random ....

select id, title from table where viewed=0 order by rand limit 1;

update table set viewed=1 where id=$id;
 

Featured Threads

Hot Threads This Week

Hot Threads This Month