Select from Database Randomly (Random Query) using PHP/MySQLi

Submitted by nurhodelta_17 on Wednesday, October 11, 2017 - 13:10.
In my previous tutorial, I have discussed on How to Limit Query in MySQL which is significant in this tutorial that tackles on how to randomly select from MySQL Table since where going to limit our random result. This is most appropriate in programs like generating random quizzes. Note: Bootstrap CSS and script used in this tutorial are hosted, therefore, you need internet connection for them to work.

Creating our Database

First step is to create our database. 1. Open phpMyAdmin. 2. Click databases, create a database and name it as testing. 3. After creating a database, click the SQL and paste the below codes. See image below for detailed instruction.
  1. CREATE TABLE `tutorial` (
  2.   `tutorialid` INT(11) NOT NULL AUTO_INCREMENT,
  3.   `title` VARCHAR(150) NOT NULL,
  4.   `link` VARCHAR(150) NOT NULL,
  5. PRIMARY KEY(`tutorialid`)
  6. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  1. INSERT INTO `tutorial` (`tutorialid`, `title`, `link`) VALUES
  2. (1, 'Submit Form using AJAX in PHP/MySQLi', 'https://www.sourcecodester.com/tutorials/php/11649/submit-form-using-ajax-phpmysqli.html'),
  3. (2, 'Image Upload using AJAX in PHP/MySQLi', 'https://www.sourcecodester.com/tutorials/php/11648/image-upload-using-ajax-phpmysqli.html'),
  4. (3, 'Creating an Iterator for While Loop Forms in PHP/MySQLi', 'https://www.sourcecodester.com/php/11643/creating-iterator-while-loop-forms-phpmysqli.html'),
  5. (4, 'Drag, Drop and Insert into Database using AJAX/jQuery in PHP', 'https://www.sourcecodester.com/tutorials/php/11641/drag-drop-and-insert-database-using-ajaxjquery-php.html'),
  6. (5, 'Simple POS and Inventory System', 'https://www.sourcecodester.com/php/11625/simple-pos-and-inventory-system.html'),
  7. (6, 'Performance Indicator System', 'https://www.sourcecodester.com/php/11638/performance-indicator-system.html'),
  8. (7, 'Simple Chat System', 'https://www.sourcecodester.com/php/11610/simple-chat-system.html'),
  9. (8, 'Uploading Multiple Files into MySQL Database using PHP/MySQLi', 'https://www.sourcecodester.com/tutorials/php/11634/uploading-multiple-files-mysql-database-using-phpmysqli.html'),
  10. (9, 'Deleting Multiple Rows using Checkbox in PHP/MySQLi', 'https://www.sourcecodester.com/tutorials/php/11631/deleting-multiple-rows-using-checkbox-phpmysqli.html'),
  11. (10, 'PHP/MySQLi CRUD Operation with Bootstrap/Modal', 'https://www.sourcecodester.com/php/11629/phpmysqli-crud-operation-bootstrapmodal.html'),
  12. (11, 'PHP Passing Value to Modal using jQuery', 'https://www.sourcecodester.com/tutorials/php/11627/php-passing-value-modal-using-jquery.html'),
  13. (12, 'How to Add Class to a Div using JQuery', 'https://www.sourcecodester.com/tutorials/javascript/11619/how-add-class-div-using-jquery.html'),
  14. (13, 'How to Limit Number of Items per Row in While Loop using PHP/MySQLi', 'https://www.sourcecodester.com/tutorials/php/11618/how-limit-number-items-row-while-loop-using-phpmysqli.html'),
  15. (14, 'PHP Prevent the Return to Login Page/Disable Back after Login', 'https://www.sourcecodester.com/tutorials/php/11614/php-prevent-return-login-pagedisable-back-after-login.html');
randomquery

Creating our Connection

Next, we create our connection to our database. This will serve as the bridge between our forms and database. We name this as conn.php.
  1. <?php
  2.  
  3. $conn = mysqli_connect("localhost","root","","testing");
  4. if (!$conn) {
  5.         die("Connection failed: " . mysqli_connect_error());
  6. }
  7.  
  8. ?>

index.php

This contains our result table. Also I have added addition codes in the comment in case your dealing with large volume of data to randomly select.
  1. <!DOCTYPE html>
  2. <html>
  3. <head>
  4.         <title>Select from Database Randomly (Random Query) using PHP/MySQLi</title>
  5.         <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
  6.         <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css">
  7. </head>
  8. <body>
  9. <div class="container">
  10.         <div class="row" style="margin-top:30px;">
  11.                 <form method="POST">
  12.                         <input type="submit" name="refresh" value="Refresh" class="btn btn-primary">
  13.                 </form>
  14.         </div>
  15.         <div class="row" style="margin-top:10px;">
  16.                 <table class="table table-bordered table-striped">
  17.                         <thead>
  18.                                 <th>ID</th>
  19.                                 <th>Title</th>
  20.                                 <th>Link</th>
  21.                         </thead>
  22.                         <tbody>
  23.                                 <?php
  24.                                         //use this if you have small number of data
  25.                                         include('conn.php');
  26.                                         $query=mysqli_query($conn,"select * from tutorial order by rand() limit 10");
  27.  
  28.                                         //for large volume of data
  29.                                         //include('conn.php');
  30.                                         //$q=mysqli_query($conn,"select * from tutorial");
  31.                                         //$num=mysqli_num_rows($q);
  32.                                         //$limit=10; //limit
  33.                                         //$random=$limit/$num;
  34.                                         //$query=mysqli_query($conn,"select * from tutorial order by rand()<=$random limit 10");
  35.  
  36.                                         while($row=mysqli_fetch_array($query)){
  37.                                                 ?>
  38.                                                 <tr>
  39.                                                         <td><?php echo $row['tutorialid']; ?></td>
  40.                                                         <td><?php echo $row['title']; ?></td>
  41.                                                         <td><?php echo $row['link']; ?></td>
  42.                                                 </tr>  
  43.                                                 <?php
  44.                                         }
  45.                                 ?>
  46.                         </tbody>
  47.                 </table>
  48.         </div>
  49. </div>
  50. </body>
  51. </html>
That's it guys. If you have any comments or questions, feel free to write it below or message me. Happy Coding :)
Tags
php
MySQLi
random
random query
random generator
random limit

Comments

is it possible to put the results in a textfield to be save in separate database table?

Add new comment

About text formats