Retrieving Specific Data in MySQL/PHP

Submitted by: 
Language: 
Visitors have accessed this post 1779 times.

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

Filtered HTML

  • Web page addresses and e-mail addresses turn into links automatically.
  • You may insert videos with [video:URL]
  • Allowed HTML tags: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd> <table> <tr> <td> <th> <img> <h1> <h2> <h3> <iframe> [video]
  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>, <asp>, <c>, <cpp>, <csharp>, <css>, <html4strict>, <java>, <javascript>, <mysql>, <php>, <python>, <sql>, <vb>, <vbnet>. The supported tag styles are: <foo>, [foo].
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Lines and paragraphs break automatically.
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.