[RESOLVED] Reference Specific Row

[Markbad311]

I am having a slight difficultly with my SELECT statement.

first here is what my DB looks like

ROW ID INFO
1 4 stuff
2 6 stuff
3 1 stuff


I have a script like this that counts how many rows are there and picks a random number between the first row and the total rows. Problem is I don't know how to reference the data by row in the next Select Statement. Here is the PHP

<?
function Banners()
{
$table_name = "inbound_links";
$sqlCount = "SELECT id FROM $table_name";
$resCount = mysql_query($sqlCount) or die(mysql_error());
$x = mysql_num_rows($resCount);
$row = rand(1,$x);


/*RIGHT HERE I NEED THE ROW REFERENCE NOT ID LIKE IT IS CURRENTLY*/
$sqlBanner = "SELECT * FROM $table_name WHERE `id` = '$row'";
$resBanner = mysql_query($sqlBanner) or die(mysql_error());
while($rowBanner = mysql_fetch_array($resBanner))
{
$img = ($rowBanner['sub_banner']);
$name = ($rowBanner['site_name']);
$url = ($rowBanner['site_url']);
$desc = ($rowBanner['site_description']);

$banner = "<br/><a href=\"$url\"><img src=\"$img\" alt=\"$name\" border=\"0\"></a><br/>$desc<br/><br/>";

}
echo "$banner";
}
?>

[chazzy]

you can try using a HAVING clause.

[Markbad311]

This script displays one random banner out of a table containing numerous banners with auto incrementing ID#s which can be deleted so therefore ID#s don't always go in order.

See the prob is if I count rows and end up with five rows. Then I need to get a random number from 1-5 say I got 4.... How do I then select Row 4 withou using any of the contents in any of the fields to SELECT it.

$sqlBanner = "SELECT * FROM $table_name WHERE ROW = '$row'";

I mean how else can I select a random ROW in the TABLE? The Having clause I don't think apply's to this.

[chazzy]

you can try using rand then..

straight from mysql.com


when selecting a single random row you have to use a query like this: SELECT ... FROM my_table ORDER BY RAND() LIMIT 1.
as explain shows, mysql optimizes this VERY badly (or may be better said, doens't optimize it at all): it uses an temporary table and an extra filesort.
couldn't this be optimized?!
if not, may be add a syntax like SELECT RANDOM_ROW .... FROM my_table ...

[Markbad311]

I used the first one. can't figure out the second keep getting errors.

[chazzy]

what first oen and what second?


<?
function Banners()
{
$table_name = "inbound_links";
$sqlBanner = "SELECT * FROM $table_name ORDER BY RAND() LIMIT 1";
$resBanner = mysql_query($sqlBanner) or die(mysql_error());
while($rowBanner = mysql_fetch_array($resBanner))
{
$img = ($rowBanner['sub_banner']);
$name = ($rowBanner['site_name']);
$url = ($rowBanner['site_url']);
$desc = ($rowBanner['site_description']);

$banner = "<br/><a href=\"$url\"><img src=\"$img\" alt=\"$name\" border=\"0\"></a><br/>$desc<br/><br/>";

}
echo "$banner";
}
?> should be what you need, no?

[Markbad311]

thats what I used thank you for all your help everyone!