Retrieving Data in MySQL/PHP

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

Introduction:
This tutoral will show you how to simply retrieve data using PHP from a MySQL database.

Required:
Before you can retrieve the data, you must have the following:
1) A webhost or localhost.
2) A database in your MySQL service.
3) A table with data in your MySQL database.

Creating a Database:
To create a database in your MySQL service, simply go to the MySQL Databases feature of your cPanel (Control Panel) and create a new database with your preferred name. If it was a database to hold the information about a sales site named 'biosales', you might call it 'biosalesdb' for simplicity.

Creating a Table:
To create a table within your database, go to the PHPMyAdmin feature of your cPanel, select your database in the left hand side column, enter a name in the 'Create a Table' seciton of the page, enter the amount of columns the table should have, and click 'Create'.

You will now be redirected to the table creation page, here you can set the columns your table will have. For this tutorial, we simply need an ID column. Every table you create should have an ID column.

To create the ID column, type 'id' in to the name of the column box, set the value type to 'Integer/Int', set the length to 5 (values up to 99999), set the index to 'Primary Key' and set 'AI/A_I/Auto Increment' to true/checked/yes. Click save/create.

Done!

Important:
Don't forget to put data in to your table. To do this, simply select your table, click on 'Insert', type in some rows of data and then click 'Save'.

PHP - Connection:
Now, on to the PHP script. The following code is commented so I will only document it minimally...

  1. <?php
  2. $con = mysqli_connect('localhost', 'root', '', 'fln'); //server, username, password, database name
  3. $q = mysqli_query($con, "SELECT * FROM `test`"); //Gets all from table 'test'
  4. while ($row = mysqli_fetch_array($q)) {
  5. //$row = next row of results/all rows from table 'test' within database 'fln'
  6. echo $row['id']; //Echo current $row's column value of 'id'.
  7. }
  8. ?>

So the above code;
First creates the connection to the databaes through the MySQL service taking four parameters of the server, username, password and database name (ensure you have the correct database name. On most web hosts, you will need to add your username/user id followed by an underscore, '_', before the 'database name'). It stores the connection in a 'con' variable.

Next, it stores a query in a 'q' (standing for 'query') variable. This query takes the connection of 'con', and runs the query;
SELECT * FROM `test` - this selects all (*) the rows of information within the table 'test'... which we created earlier.

After getting the rows we then perform a while loop. This while loop iterates through each row of data returned by our earlier query and stores each row iteration in a new 'row' variable which is temporary for the while loop (meaning you can't use it outside of the while loop).

Finally, we simply echo/print/output to the screen the value held within the current row (variable; 'row') and column of 'id'; which should increase by one per row (since we selected the Auto Increment option while creating our table earlier in this tutorial).

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.