PHP loop

[SiKing]

Hey, Need a little help. Basically the following script should be checking the users credits and if they have more than 1, then display their link....But it doesn't. The script just takes forever to load. Then again I'm with Dreamhost and they're...well...awful.

$o=0;
while ($o<1)
{
$res = mysql_query ("SELECT * FROM sites WHERE active='1' ORDER BY rand() LIMIT 1");
while ($row = mysql_fetch_array($res)) 
{ 
$id = $row['id'];
$member = $row['member'];
$url = stripslashes($row['url']);
}



$res = mysql_query ("SELECT * FROM users WHERE id='$member'");
while ($row = mysql_fetch_array($res)) 
{ 
$credits = $row['credits'];
}

if ($credits>=1) {$o = "1"; }
else {$o = "0"; }
}

$credits--;

$res = mysql_query ("UPDATE users SET credits='$credits' WHERE id='$member'");

 

[Dan]

The only thing I see missing is to end your first loop. Try adding a } to the end of it.

 

[Kate]

Not sure about your purpose but you could do that in SQL

Fetch one random record:

SELECT * FROM sites WHERE active='1' and credits > 1 ORDER BY rand() LIMIT 1

BTW why do you use quotes for active and credits fields ? These fields are numeric right ?

For the updates you could do that in one shot too... using SQL again. I don't see the purpose of the PHP code here.

 

[SiKing]

well the active row is in the 'sites' table and the credits row is in the 'users' table

 

[Kate]

You can do a join between the two tables:

SELECT field1, field2...
FROM sites
INNER JOIN users
ON sites.id=users.id

WHERE sites.active='1'
AND users.credits > 1

In this example I assume the ID field is the one to match the two tables together.

 

[SiKing]

Thanks but how do I use that code to choose a random row?

 

[Kate]

Well you just add this at the end:

ORDER BY RAND() LIMIT 1

ORDER BY RAND() means sort in random order and LIMIT 1 means limit the query to 1 record.