php random mysql data

Question

I have a MySQL database with 6 columns in a table. There will eventually be about 100 rows, for now I have 3.

Column titles: FirstName, SecondName, Sentence1, Sentence2, Sentence3, Sentence4

All tables are set to VARCHAR

I want to use php on a web page to call random data from each row, eg mix and match row1 FirstName with row3 SecondName and row2 Sentence1 etc.

I read it is quicker to randomise using php but I really can't grasp how to do this despite searching.

I can connect to my MySQL database and get results returned using this code:

    <?php
    // Connect to database server
    mysql_connect("localhost", "xxx", "yyy") or die (mysql_error ());
    // Select database
    mysql_select_db("zzz") or die(mysql_error());
    // SQL query
    $strSQL = "SELECT * FROM Users";
    // Execute the query (the recordset $rs contains the result)
    $rs = mysql_query($strSQL);
    // Loop the recordset $rs
    // Each row will be made into an array ($row) using mysql_fetch_array
    while($row = mysql_fetch_array($rs)) {
    // Write the value of the column FirstName (which is now in the array $row)
    echo $row['FirstName'] . "<br />";
      }
    // Close the database connection
    mysql_close();
    ?>

but this just returns one column of data. I need the random code to be returned in the webpage using something like:

echo $firstname . $lastname . $sentence1 . $sentence2 . $sentence3 . $sentence4;

Note, this will be repeated for another 3 or 4 rows afterwards too

echo $firstname_2 . $lastname_2 . $sentence1_2 . $sentence2_2 . $sentence3_2 . $sentence4_2;

I'm not too hot on arrays but if someone can get me started it would be great, thanks.

MatthewMcGovern · Accepted Answer · 2012-10-05 16:23:54Z

5

All those telling you to use rand in the SQL query have not read the question. To those people: the asker wants a random combination of data from the rows, not a random row.

Something like this. It will take all the results from the database and echo a totally random combination. I couldn't avoid using arrays as they are super useful.

<?php
// Connect to database server
mysql_connect("localhost", "xxx", "yyy") or die (mysql_error ());
// Select database
mysql_select_db("zzz") or die(mysql_error());
// SQL query
$strSQL = "SELECT * FROM Users";
// Execute the query (the recordset $rs contains the result)
$rs = mysql_query($strSQL);
// Array to hold all data
$rows = array();
// Loop the recordset $rs
// Each row will be made into an array ($row) using mysql_fetch_array
while($row = mysql_fetch_array($rs)) {
// add row to array.
$rows[] = $row;
  }
// Close the database connection
mysql_close();

// Max rand number
$max = count($rows) - 1;

// print out random combination of data.
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] . " " . $rows[rand(0, $max)][5];

?>

edited Oct 5, 2012 at 16:23

answered Oct 5, 2012 at 16:18

MatthewMcGovern

3,5062 gold badges24 silver badges19 bronze badges

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

5 Comments

RobMasters Over a year ago

+1 for actually reading the question! (and for pipping me to the post by 3 minutes)

Sara44 Over a year ago

That's nice Matthew and it works well thanks. The only thing I need to know now is if it can be called later on in the webpage php without connecting to the database again?

MatthewMcGovern Over a year ago

Yeah it can. All the data has been stored in the $rows variable.

MatthewMcGovern Over a year ago

Also, @Sara44, people will be more likely to answer any future questions you may have if you accept an answer by clicking the green tick to the top left of the desired post.

Sara44 Over a year ago

Sorry Matthew, thought I had done that. Thanks again for the help.

FirmView · Accepted Answer · 2012-10-05 16:16:36Z

2

Store all the values which you want to show in random in a variable, use rand() http://php.net/manual/en/function.rand.php and shuffle() http://php.net/manual/en/function.shuffle.php to make the random data and display them

answered Oct 5, 2012 at 16:16

FirmView

3,1508 gold badges36 silver badges50 bronze badges

Comments

Alfabravo · Accepted Answer · 2012-10-05 16:22:09Z

2

there are several methods to get random data from db in php

SELECT * FROM `table` ORDER BY RAND() LIMIT 0,1;

another method: -

$range_result = mysql_query( " SELECT MAX(`id`) AS max_id , MIN(`id`) AS min_id FROM    `table` ");
$range_row = mysql_fetch_object( $range_result ); 
$random = mt_rand( $range_row->min_id , $range_row->max_id );
$result = mysql_query( " SELECT * FROM `table` WHERE `id` >= $random LIMIT 0,1 ");

one more method:-

$offset_result = mysql_query( " SELECT FLOOR(RAND() * COUNT(*)) AS `offset` FROM `table` ");
$offset_row = mysql_fetch_object( $offset_result ); 
$offset = $offset_row->offset;
$result = mysql_query( " SELECT * FROM `table` LIMIT $offset, 1 " );

edited Oct 5, 2012 at 16:22

Alfabravo

7,5786 gold badges49 silver badges83 bronze badges

answered Oct 5, 2012 at 16:19

omnath

5098 silver badges26 bronze badges

Comments

chokrijobs · Accepted Answer · 2012-10-05 16:14:01Z

1

SELECT * FROM `Users` ORDER BY RAND() LIMIT 0,1;

answered Oct 5, 2012 at 16:14

chokrijobs

7711 gold badge6 silver badges10 bronze badges

1 Comment

MatthewMcGovern Over a year ago

This is not what was asked though. They want a random combination of data fields from the rows, not just a random row.

Dmytro Zarezenko · Accepted Answer · 2012-10-05 16:15:42Z

1

Use ORDER BY RAND() for random records selection.

answered Oct 5, 2012 at 16:15

Dmytro Zarezenko

10.7k12 gold badges67 silver badges105 bronze badges

Comments

Thomas Nordquist · Accepted Answer · 2012-10-05 16:17:27Z

1

Split it into two tables, one for the user

Users: id | firstname | lastname

Sentences: id | userId | sentence

Join both at the "id / userId" and do a ORDER BY RAND() probably followed by a LIMIT 20

answered Oct 5, 2012 at 16:17

Thomas Nordquist

6434 silver badges14 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 10:32:08Z

1

Trying to implement this but taking an entry from every column (14 at present) instead of a small random number. Would love to have Matthew McGovern's opinion since his code suited me except that it only called a few entries...

Here: Random Sentence Using PHP & MySQL

edited May 23, 2017 at 10:32

CommunityBot

11 silver badge

answered Jul 10, 2013 at 2:43

Joe

313 bronze badges

Collectives™ on Stack Overflow

php random mysql data

7 Answers 7

5 Comments

Comments

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

5 Comments

Comments

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related