Skip to main content

Storing and Retrieving Data from MySQL Database

Storing and Retrieving Data from MySQL Database

You may first want to read Installing, Configuring and Testing MySQL Database Server on 'localhost', About MySQL, Databases and SQL Commands.

Now that you have installed MySQL database server and have learnt some SQL commands to query MySQL, its time to use our knowledge to do what database servers are used for, storing and retrieving data.

To make for a nice example, we’ll create a simple Phonebook Application which would store phone numbers and names of peoples. It’d be able to illustrate the storage and retrieval of data from database quite well.

OK let’s first start with the datable design. We’d need to create a database first and table in that database. One table is enough because we only need to store name and phone number which should be stored in the same table. For the table we should have two fields (column), one for name and the other for phone number. Both can be of data type (MySQL) VARCHAR as we don’t need to even the phone number to be interpreted as a number. The creation of the database and table can be done with the following SQL query:

CREATE TABLE phbook (name VARCHAR(20), phno VARCHAR(20));

For inserting data to the table just created we’ll use the following SQL query:

INSERT INTO phbook(name, phno) VALUES (‘$name’,’$phno’);

And retrieval of data can be done using:

SELECT * FROM phbook;

[Please read About MySQL, Databases and SQL Commands of you don’t understand these.]

One thing different with the above SQL query is that unlike all the other queries above, it ‘return’ data, when queried like :

$result=$db->query("select * from phno");

‘$result’ variable will contain the data retrieved from MySQL as per the query. In this case, all (*) the rows and columns i.e. the whole table.

The number of rows returned can be known by the 'num_rows' member function as follows::


And each row of data can be retrieved one-by-one from the ‘$result’ object by the following function:


[NOTE: After querying the database for data retrieval, we work with the data object returned ($result) and not with the database ($db) to work with the data.]

Now since ‘$row’ contains one row having two columns (fields), name and phno, each of it can be separately accessed using the indices:


yeah, ‘fetch_row()’ function returns an array containing different data fields as different elements.

Now we have enough knowledge to clearly understand the code, here it is:

//----My Phone Book----
//Copyright 2008
//This code is free to use and republish but credit 
//and copyright information must remain intact.

//connect to MySQL
//provide your 'username' and 'pass'
//change 'localhost' to the MySQL server
//host, if not using on 'local server'
$db=new mysqli('localhost','-USERNAME-','-PASS-');
//if 'save' button was pressed
//uesr wants to store phone number
//if data supplied are not empty
if(!$name=='' || !$phno=='')
//if this is the first time
        //and database is not craeted
//create the database
$db->query('create database one');
//select the databasw to work with
//if table is not craeted, craete it
if(!$db->query('select * from phno'))
$db->query('create table phno(name varchar(50), phnum varchar(20))');
//ready to insert data
$db->query("insert into phno (name, phnum) values ('$name', '$phno')");
//show the form
<title>My Phone Book</title>

<h1>My Phone Book</h1>
<h2 style="background: #000; color: #fff;">Store New Phone Number</h2>
<form name="form1" id="form1" method="post" action="">
  <table width="250" border="0" cellspacing="0" cellpadding="0">
      <td width="83">Name</td>
      <td width="417"><input name="name" type="text" id="name" /></td>
      <td>Ph. No.</td>
      <td><input name="phno" type="text" id="phno" value=""></td>
      <td><input name="save" type="submit" id="save" value="Save" /></td>
      <td><input type="reset" name="Submit2" value="Reset" /></td>
<h2 style="background: #000; color: #fff;">Previously Stored</h2>

//if database does not exits
//first time operation
$result=$db->query("select * from phno");
//find number of rows
//process all the rows one-by-one
//fetch one row
//print the values
echo "<p><span style=\"font-size: 200%;\">$row[0]: </span> $row[1]</p>";
//close MySQL connection

Previous Articles:

Popular posts from this blog

Fix For Toshiba Satellite "RTC Battery is Low" Error (with Pictures)

RTC Battery is Low Error on a Toshiba Satellite laptop "RTC Battery is Low..." An error message flashing while you try to boot your laptop is enough to panic many people. But worry not! "RTC Battery" stands for Real-Time Clock battery which almost all laptops and PCs have on their motherboard to power the clock and sometimes to also keep the CMOS settings from getting erased while the system is switched off.  It is not uncommon for these batteries to last for years before requiring a replacement as the clock consumes very less power. And contrary to what some people tell you - they are not rechargeable or getting charged while your computer or laptop is running. In this article, we'll learn everything about RTC batteries and how to fix the error on your Toshiba Satellite laptop. What is an RTC Battery? RTC or CMOS batteries are small coin-shaped lithium batteries with a 3-volts output. Most laptops use

The Best Way(s) to Comment out PHP/HTML Code

PHP supports various styles of comments. Please check the following example: <?php // Single line comment code (); # Single line Comment code2 (); /* Multi Line comment code(); The code inside doesn't run */ // /* This doesn NOT start a multi-line comment block /* Multi line comment block The following line still ends the multi-line comment block //*/ The " # " comment style, though, is rarely used. Do note, in the example, that anything (even a multi-block comment /* ) after a " // " or " # " is a comment, and /* */ around any single-line comment overrides it. This information will come in handy when we learn about some neat tricks next. Comment out PHP Code Blocks Check the following code <?php //* Toggle line if ( 1 ) {      // } else {      // } //*/ //* Toggle line if ( 2 ) {      // } else {      // } //*/ Now see how easy it is to toggle a part of PHP code by just removing or adding a single " / " from th

C++ Hello World Program: Example Code with Detailed Explanation

C++ Hello World Program Output C++ is one of the most popular and powerful programming languages in the world. In this short article, we are going to go back to the basics and take a look at the simplest of the C++ program - the "Hello World" program which prints "Hello World!" to the screen. While you can set up a C++ environment very easily using one of the IDEs (including free ones such as CodeBlock  or Eclipse ), we'll be using a free online C++ compiler (or runner) for the sake of keeping things very simple and straightforward. C++ Hello World Program Code #include <iostream>   int main ( ) { // Comment std :: cout << "Hello World!" ; return 0 ; } Line-by-line Explanation C++ is a vast and very flexible language and a lot of functionality is offered by different libraries which we can include at the start of the program depending on what we need to do. Anything starting with a pound sign # is called a preprocessor