Using PHP to pull data from MySQL table column randomly

Question

I am using the following (assume I already plan to change these to mysqli at a later date and am aware of the insecurity of the queries used), to pull text strings from rows in one column in a MySQL table and the output in a browser would, ideally, be a randomly selected string from this column:

mysql_connect($host,$username,$password); 
mysql_select_db($database) or die(mysql_error ()); 

$query="SELECT * FROM `tablename` ORDER BY RAND() LIMIT 0,1;";
$result=mysql_query($query);
$rows = array();
while($row = mysql_fetch_array($rs)) {
$rows[] = $row;
 }
mysql_close();
$max = count($rows) - 1;

Using the following echo line to achieve the last bit in the browser:

echo $rows[rand(0, $max)][0] . " " . $rows[rand(0, $max)][1] . " " . $rows[rand(0, $max)][2] . " " . $rows[rand(0, $max)][3] . " " . $rows[rand(0, $max)][4]$
?>

I receive the error "PHP Notice: Undefined offset: 0 in script.php on line 19" in reference to this echo line (which, admittedly, was pieced together from other threads and tutorials, so I do not follow completely), however, I've since resolved all other errors logged and observed, so if possible, how can I amend this so the output is just a single row (the text within it) from the column?

var_dump() is your friend - look at what you are trying to access. Undefined offset:0 means that there is nothing in array[0]. — Josh Dean
– Josh Dean, Commented Mar 4, 2013 at 1:17

ithcy · Accepted Answer · 2013-11-15 16:32:40Z

3

Faster and better than using RAND()

$conn = mysqli_connect($host,$username,$password, $database);
if($conn->connect_errno > 0) {
  die('Unable to connect to database [' . $conn->connect_error . ']');
}

$total_rows = 20; //Generate Random number. You could get $total_rows with a first query counting all rows.
$selected_row = mt_rand(0, $total_rows);
//$selected_row -= 1; //just in case you randomized 1 - $total_rows and still need first row.

//Use the result in your limit.
$query="SELECT * FROM `tablename` LIMIT $selected_row, 1;";
$result=$conn->query($query);

while($row = $result->fetch_assoc()) {
  echo $row["columnname"];
}

Edit it from mysql to mysqli (on the fly). You would not want to use RAND() if your table is very large. Believe me!

edited Nov 15, 2013 at 16:32

ithcy

5,5892 gold badges26 silver badges38 bronze badges

answered Mar 4, 2013 at 1:21

Steward Godwin Jornsen

1,1791 gold badge7 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Kermit Over a year ago

I almost want to -1 for using mysql_ functions. Please fix this.

Steward Godwin Jornsen Over a year ago

I just based on this: he said, assume I already plan to change these to mysqli at a later date and am aware of the insecurity of the queries used), . I'd fix that in an edit

Kermit Over a year ago

It is for future visitors.

Steward Godwin Jornsen Over a year ago

@AarolamaBluenk I've just updated to mysqli Object oriented. See if you could spot anything. Doing it on the fly, itchy eyes. I thought it was wise to stick to the OP's request to migrate later to mysqli. There is great advantage having this in mysqli over mysql

Steward Godwin Jornsen Over a year ago

Did you provide value for $total_rows? try echoing out the random value line to see what you get. Let me know if you get any errors. I've added $total_rows = 20; in my answer. Other than that, limit would get incomplete values.

|

michi · Accepted Answer · 2013-03-04 01:16:19Z

1

In your SELECT-statement, you are telling the database to order the strings randomly. So just get the first one and echo it:

$row = mysql_fetch_array($rs)

echo $row['name_of_field_you_want_to_echo'];

answered Mar 4, 2013 at 1:16

michi

6,6255 gold badges35 silver badges56 bronze badges

Comments

Eli White · Accepted Answer · 2013-03-04 01:18:45Z

1

You never define the variable $rs. Other than that...

If you are selecting the first items from a SQL query, you don't need to specify both the limit and top.

$result = mysql_query("SELECT * FROM `tablename` ORDER BY RAND() LIMIT 1");

Since that will ever return one row, you can use mysql_fetch_row

$row = mysql_fetch_row($result);

and then you can get the field from that row with

echo $row["column_name"];

answered Mar 4, 2013 at 1:18

Eli White

1,0241 gold badge10 silver badges21 bronze badges

1 Comment

Eli White Over a year ago

But just as everyone else is saying, still don't use RAND().

Collectives™ on Stack Overflow

Using PHP to pull data from MySQL table column randomly

3 Answers 3

10 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

10 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related