Retrieving Specific Data in MySQL/PHP

Language

Introduction: This tutorial is on how to retrieve specific data from a database through PHP and MySQL/MySQLi. Latest: In my latest tutorials I have shown how to retrieve all rows from a database table, how to insert data, and how to validate data. Now I will show you how to retrieve specific data through slightly more advanced queries. Database: First you will obviously need a database with table. My database is named 'fln' and table is named 'test'. It has two columns; id, 5 length, primary key, auto increment, integer score, 255 length, varchar You can download the entire .sql file to import from the bottom of this tutorial article page. It is already populated with data so I will not run over that set, if you need to insert data, refer to one of my previous tutorials which can be found on my profile's tacking page. Connection: First we must make the PHP connection, like so...
  1. <?php
  2. $con = mysqli_connect('localhost', 'root', '', 'fln'); //server, username, password, database name
  3. ?>
What Data? Next we must decide what data we want to have returned to us. Since my table only really consists of scores, I am first going to retrieve all scores between 100 and 1000. Query Method: We could do this through a MySQLi query, like so...
  1. $q = mysqli_query($con, "SELECT * FROM `test` WHERE `score`>'99' AND `score`<'1001'");
  2. while ($row = mysqli_fetch_array($q)) {
  3. echo 'Found query result; ' . $row['score'] . '<br/>';
  4. }
PHP Method: Or, we could retrieve all the rows and filter them through PHP, like so...
  1. $q = mysqli_query($con, "SELECT * FROM `test`");
  2. $scores = array();
  3. while ($row = mysqli_fetch_array($q)) {
  4. if ($row['score'] > 99 && $row['score'] < 1001)
  5. array_push($scores, $row['score']);
  6. }
  7. foreach ($scores as $key => $value) {
  8. echo $value . '<br/>';
  9. }
So now we have an array of the correct score results. - We also output each one through a simple foreach loop. Finished!

Note: Due to the size or complexity of this submission, the author has submitted it as a .zip file to shorten your download time. After downloading it, you will need a program like Winzip to decompress it.

Virus note: All files are scanned once-a-day by SourceCodester.com for viruses, but new viruses come out every day, so no prevention program can catch 100% of them.

FOR YOUR OWN SAFETY, PLEASE:

1. Re-scan downloaded files using your personal virus checker before using it.
2. NEVER, EVER run compiled files (.exe's, .ocx's, .dll's etc.)--only run source code.

Add new comment