Prefetching

[danielhalawi]

Hi all,

I have the following

$result = mysql_query("SELECT vmplay_videos.id, vmplay_videos.artistid, vmplay_artists.website, vmplay_artists.artistfirst, vmplay_artists.artistlast, vmplay_videos.songname FROM vmplay_artists, vmplay_videos WHERE vmplay_artists.id = vmplay_videos.artistid and videoactive = 'yes' and indepic = 'yes' ORDER BY RAND( NOW( ) )  LIMIT 1 ");
while($row = mysql_fetch_array($result)){
$indename = $row["artistfirst"];
$indelastname = $row["artistlast"];
$indesongname = $row["songname"];
$indeaid = $row["artistid"];
$indevid = $row["id"];
$indewebsite = $row["website"];
}

I use this at the top of my page and use echos to display the results.

I was wondering whether or not I could get the information for three entries and then use echos to display the information for three records.

I.E in table one the information for record one is displayed and then information for record two is displayed in table two.

I want to use the same query because its a random selector, I've tried using two different queries, however I receive the same results dupilicated because of the random.

Is there any help?

Dan

[leatherback]

$max = 3;

$result = mysql_query("SELECT vmplay_videos.id, vmplay_videos.artistid, vmplay_artists.website, vmplay_artists.artistfirst, vmplay_artists.artistlast, vmplay_videos.songname FROM vmplay_artists, vmplay_videos WHERE vmplay_artists.id = vmplay_videos.artistid and videoactive = 'yes' and indepic = 'yes' ORDER BY RAND( NOW( ) )  LIMIT $max ");

$i = 0;

while($row = mysql_fetch_array($result)){
$indename[$i] = $row["artistfirst"];
$indelastname[$i] = $row["artistlast"];
$indesongname[$i] = $row["songname"];
$indeaid[$i] = $row["artistid"];
$indevid[$i] = $row["id"];
$indewebsite[$i] = $row["website"];
$i++;
}

This should work, except for typos.

you can now acces
$indename[0]
$indename[1]
and
$indename[2]
as the first, second, third selected '$indename'

[danielhalawi]

This works a treat, but I need to limit wat results I get so that only one artist's information is shown.

Its duplicating the results of the same artist, so I want it not to repeat information of the same artist.

What would be the best wording.

[leatherback]

Yeah,

in your query (Somewhere off screen for me 😉 I see a where statement.

So you have multiple records for one artist?

Otherwise you could just remove the statement: vmplay_artists.id = vmplay_videos.artistid

from your query?

J.

[danielhalawi]

So this is the where statement

WHERE vmplay_artists.id = vmplay_videos.artistid and videoactive = 'yes' and indepic = 'yes'

However I only want the information of one artist per result.
At the moment, it can sometimes show information for two entry's for the same id in artist table.

[leatherback]

Then you probably have to select the artist ID's first, using distinct to get just one record, and the random for a random display. THe three records that come out are subsequently displayed.

Or you select all of them, place the data in an array, and use some rand function to retrieve three records, while checking for uniqie artists

[danielhalawi]

What do u suggest would be the easiest way?

[leatherback]

I would go for the double queries. But there must be a better / more elegant solution..

J.

[danielhalawi]

ok, any clues of where to start? Or got any signs that can point me in the right direction?