How do I randomly get variables from a mysql database? (using php with dbo connection)

Question

My question clearly states it in the heading, anyway a more elaborate question is how can I randomly show a variable (eg. name) from a mysql database using php with a dbo connection in xampp environment.

Here is what I've come up with from scratch:-

 <?php
                $db = new PDO 
                ('mysql:dbname=lab1;host=localhost;charset=utf8','root', 
                '');
                $stmt = $db->prepare('SELECT * FROM countries');
 ?>


                <head>
                <meta charset="UTF-8"/>
                <p>RANDOM!</p>
                </head>


                <body>
<?php
                $stmt -> execute();
                foreach($stmt as $row){
                $something = array ($row['name'] . $row['name']);
                }
                $rand_keys = array_rand($something, 1);
                echo $something[$rand_keys[0]] . "\n";
?>
                </body>

Seems to not work when I try opening it (from localhost)

Maybe look into `RAND()` and `LIMIT 1` and just do it in the sql? — IncredibleHat, Nov 07 '17 at 21:36
I'd hope the countries table is not larger than a couple hundred ... =D — IncredibleHat, Nov 07 '17 at 21:38
"seems to not work" doesn't describe the observed behavior sufficiently. Consider enabling PDO exceptions (since there isn't any error handling shown in the code). `->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);`. Also, do `SELECT name FROM` if that's the only column you need to reference, that's more efficient that returning *every* column in the table. And for the future reader, it's much more apparent *how many* columns are being returned, and what the *names* of the columns being returned. The savings on the developer writing `SELECT *` is vastly outweighed by disadvantages. — spencer7593, Nov 07 '17 at 21:39
I'm not sure I follow your question, but it sounds like you want value from a random row in a table? If so, that's what Randall's comment does. If not, please explain. — dwilliss, Nov 07 '17 at 21:39
the table is only taking 1 row with 4 fields supposedly (or i could simply minimize the query to a specific field), not that big however, I don't know how to call a variable using an index in the array in a random manner — Jaeger, Nov 07 '17 at 21:43
sorry I was not able to make my question clear, so basically I have a table in mysql, with 4 columns and as of now it has 2 inputted rows, now I want to be able to echo lets say just 1 field (randomly) as I refresh the file when opened from localhost... — Jaeger, Nov 07 '17 at 21:52

Kamrul Khan · Answer 1 · 2017-11-07T22:00:59.107

0

You can use order by RAND() like others suggested. But I think the way you trying does not work because the way you are adding elements into the array. Like the following line:

$something = array ($row['name'] . $row['name']);

does not push multiple elements (eg. country names) into the array. It replaces the previous value by a concatenated string of the same country name repeated twice. (so if the country name is Afghanistan your array will always have one element AfghanistanAfghanistan

Change your code like below:

<?php
            $stmt -> execute();
            $something = [];
            foreach($stmt as $row){
                $something[] = $row['name'];
            }
            $rand_keys = array_rand($something, 1);
            echo $something[$rand_keys[0]] . "\n";
?>

Hope this will work.

edited Nov 07 '17 at 22:00

answered Nov 07 '17 at 21:50

Kamrul Khan

3,260
4
32
59

this has helped me load in the value properly, however, it's only outputting 1 value, I've got 2 rows and yet it wont randomly output either one but it just outputs 1... – Jaeger Nov 07 '17 at 21:56
so you are saying you always see the same country name? can you show me a dump of you `$something` variable? do a print_r($something) right before the `$rand_keys ...` line ? – Kamrul Khan Nov 07 '17 at 22:00
1

I have edited my answer. This should get rid of the Notice – Kamrul Khan Nov 07 '17 at 22:01

score 0 · Accepted Answer · answered Nov 07 '17 at 22:00

Based off your comment of "sorry I was not able to make my question clear, so basically I have a table in mysql, with 4 columns and as of now it has 2 inputted rows, now I want to be able to echo lets say just 1 field (randomly) as I refresh the file when opened from localhost..." ... you will need to do a compound randomize :)

Start with this sql change to randomly just grab one row (of all columns):

SELECT * FROM countries ORDER BY RAND() LIMIT 1

Then retrieve that one row, and then randomly pick from the available columns:

$stmt->execute();
$row = $stmt->fetch(PDO::FETCH_NUM);// PDO example, important to fetch by indexes, not associative names

echo $row[ rand(0,count($row)) ];

That should randomly pull one column from the randomly grabbed row.

How do I randomly get variables from a mysql database? (using php with dbo connection)

2 Answers2