Introduction

MySQL and MariaDB are free relational databases. They run on many systems including Windows. They are very popular and simple to use.

You will need to learn a little about SQL (Structured Query Language). Examples of the MySQL commands you will need are provided in this document.

Note: Other databases can be used. Just substitute them for MySQL/MariaDB. The SQL commands may be slightly different.

Click HERE for some useful Python files.

Project Steps

Step 1. Install MySQL. There is plenty of documentation on the web on how to do this. It is a simple process. (see XAMPP for more information.)

Note: It may be already installed or available on another system via the network.

Step 2. Download the MySQL libraries,etc. for the language you are programming in. (Perl, Python, Jave, etc.)

Step 3. Code and test the program.

Design

1. The program will have three major commands
   • Enter data
   • Search for data
   • Quit
2. The database will contains phone numbers, first names, and last names
3. The program will loop until the user enters a "quit" command
4. The program will generate error messages

Things to consider

• Allow several phone number input formats but convert to a standard format when stored in the database
  • Database phone number format
    For Example: (999) 123-4567, or 999-123-4567, or 9991234567, or ...
  • Input phone number formats
    For example: (999) 123-4567, (999)123-4567, 999-123-4567, 9991234567, 123-4567, and ...
• Allow/disallow international phone number (phone numbers with country codes)
• Capitalization of first and last name in the database (all caps, no caps, ...)
• Case insensitive searches
  • Enter last name only (no first name)
  • Enter first name only (no last name)
  • Substring searches (area code, last name, etc.)
• Multiple matches when searching

My SQL Commands

Windows MySQL command-line tool (Good for testing MySQL commands)

Create database

DROP DATABASE IF EXISTS phonebook;

CREATE DATABASE phonebook;

Create database table

USE phonebook;

CREATE TABLE     info(
id               int            not null auto_increment,
firstname        varchar(128),
lastname         varchar(128),
phonenumber      varchar(32),
PRIMARY KEY      (id)
)                ENGINE=InnoDB;

Display database table information

USE phonebook;

DESCRIBE info;

Enter data

USE phonebook;

INSERT INTO info (firstname,lastname,phonenumber)
       VALUES ('firstname','lastname','phonenumber');

Return data

USE phonebook;

SELECT firstname,lastname FROM info WHERE phonenumber='phonenumber';

SELECT phonenumber FROM info WHERE lastname='lastname';

SELECT * FROM info;

Links

Java - using MySQL
Python - MySQL Connector

Try This

Add more fields to the database. (address, multiple phone numbers, ...)
Add a delete command.
Add a modify command.

Expand The Project

What do you do if someone has more that one phone number? You create a database with two tables; one table for person information and one for phone numbers. You connect the two tables with foreign keys. Click HERE for an example.