php mySQL, retrieve one row by number, store values as assoc.array

Question

Pretty new to all this, so if I am going about my puzzle in a crazy way please tell me!

I have a table in a mySQL database with the columns title, hyperlink, imagelink

I want the php to select a row at random, then be able to save the title, hyperlink and imagelink as php variables. (so I can then output them into html)

so I could have for e.g

psuedoey code to get the idea

chosenNumber = randomNumber();

$chosenTitle = title[chosenNumber]
$chosenHyperlink = hyperlink[chosenNumber]
$chosenImagelink = imagelink[chosenNumber]

echo "<a href = $chosenTitle><img src= $chosenImagelink/> $chosenTitle </a>";

I think I need to use an assoc array like this here but I am very confused, because I looked through the various php mySQL fetch_assoc fetch_row etc and can't find which one to do what i need :(

What I have so far is

// database table name information
$number = "number";
$title = "title";
$hyperlink = "hyperlink";
$imagelink = "imagelink";

// sql to select all rows of adverts
$sql = "SELECT $number, $title, $hyperlink, $imagelink FROM $table";

//execute the sql
$data = mysql_query($sql, $link);

//count the number of rows in the table
$bannerCount = mysql_num_rows($data);

//generate a random number between 0 and the number of rows
$randomNumber = mt_rand(0, $bannerCount); //do I need to do bannerCount-1 or similar here?
$chosenNumber = $randomNumber;

//select data from a random row

First time post, be kind please, and thanks for any replies or explanations!

Answer 1

If you want to get records randomly, you can simply modify your sql query and each time you will get random ordering of records, just add order by rand() to your query and limit clause if you want to get just one random record:

$sql = "SELECT $number, $title, $hyperlink, $imagelink FROM $table
order by rand() limit 1";

Once you have done that, you need to use functions like mysql_fetch_array or mysql_fetch_object to get the rows actually:

$data = mysql_query($sql, $link);

while ($row = mysql_fetch_array($data))
{
  echo $row['title'] . '<br />';
  echo $row['hyperlink'] . '<br />';
  echo $row['imagelink'] . '<br />';
}

With:

$row = mysql_fetch_array($data)

You have $row array available at your disposal to echo values at any place of your page like:

  echo $row['title'];
  echo $row['hyperlink'];
  echo $row['imagelink'];

Answer 2

I think if you use the "limit" clause, it would be quite efficient:

SELECT number, title, hyperlink, imagelink FROM $table order by rand() limit 1

Why do you set up variables for the table name and field names?

Answer 3

Using ORDER BY RAND() LIMIT 1 is a decent way to get a single row out of a smaller table. The downside to using this method is that RAND() must be calculated for every row in the table, and then a sort must be performed on this non-indexed value to calculate the row you want. As your table grows, ORDER BY RAND() becomes horribly inefficient. The better way to handle this is to first get a count of the number of rows in the table, calculate a random row number to read, and use the LIMIT [offest,] count option on your SQL query:

$sql = "SELECT COUNT(*) as rows FROM $table"
$res = mysql_query($sql);
if (!$row = mysql_fetch_assoc($res)) {
  die('Error Checking Rows: '.mysql_error());
}
$rows = $row['rows'];

// now that we know how many rows we have, lets choose a random one:
$rownum = mt_rand(0, $rows-1);
$sql = "SELECT number, title, hyperlink, imagelink FROM $table LIMIT $rownum,1";
$res = mysql_query($sql);
$row = mysql_fetch_assoc($res);

// $row['number'] $row['title'] etc should be your "chosen" row

This first query asks the SQL Server how many rows are available, then LIMITs the result set of the actual query to only return 1 row, starting at the random number row we picked.