MySql : View Data from Database

Now we want to retrieve the data from the database and display them as links on the page. The SQL statement used to get data from a database is SELECT. The format is:
SELECT (columns) FROM (table)
WHERE (exclusive criteria)

For our database we want so select all columns, so a '*' is used instead of listing out each column. We also only want to select a specific category of links, let's say "Local Docs" is the cateogry we want. So our SELECT SQL statement would be:
SELECT * FROM links
WHERE category = 'Local Docs'

Single quotes specify a string value in the WHERE clause, if we were using a column which was a number value no quotes would be needed. The WHERE part is optional, if you want to select everything from the database you can leave off the WHERE portion. Review the SQL Tutorial for more examples of what can be done with SELECT statements and WHERE clauses.

Read more »

MySql : Insert Data

We need to get some data into the database. To do so we use an HTML form to collect the data, and then insert it into the database using PHP. Both these functions can be done on the same page.

The form can submit to the same page it is on. This page checks if the POST method is used. If so, insert into the database. I find it easier to have the form and database insert operation on the same page. Makes one less file to keep track of.

Here is the HTML for the form:

Category:
Site Name:
Site URL:
Description:

The above HTML snippet has the table elements removed because they are for display purpose only. The HTML for the form below is displayed using a table.



This form gathers the information we want to insert into the database and submits back to the same page it is on. We now need the PHP code to process this information. The first block of code is setting up the connection to the database. This is the same as used on the create table page.

Read more »

MySql : Create Database

We know what data we want and what we want to with that data. Now we need to create the database to store the data. The first thing to do is create an empty database to hold our table. For this tutorial, I name the database linksdb. In MySQL you use the following command from the command line to create the new database.

mysqladmin -p create linksdb

The "-p" flag is used so it will prompt you for your password to MySQL. Most setups require a username and password. Read the MySQL doumentation on how to setup the username and password using the mysqladmin tool. It is fairly straight forward, however you may need root access.

Test connecting to the database using the mysql client. On the command line:

mysql -p linksdb

You should get a mysql> prompt if it connects to the database. Else, it returns an error saying Unknown database 'linksdb'


Creating the Database Table
Next we need to create the table in the database. Tables are created using SQL statements, and can be created using the mysql client tool or PHP. The nice thing about using a PHP script to create the table is you can save the script to use later. If something goes wrong, you can recreate the tables. Or, you can use it to refer to the database schema.

The SQL command to create a table is:
CREATE TABLE tablename (
column1 column1type,
column2 column2type,
etc.... )

The table we want to create is:
Table Name: links

Columns: id (integer - primary link key)

sitename (50 characters)

siteurl (75 characters)

description (text field - lots of text)

category (50 characters)


So the SQL code to create the categories table is:
CREATE TABLE links (
id INT NOT NULL AUTO_INCREMENT,
sitename VARCHAR(50),
siteurl VARCHAR(75),
description TEXT,
category VARCHAR(50),
PRIMARY KEY(id) );

The id column is the primary key for this table. In order to be a primary key the column can not be null (NOT NULL). I also set it to automatically increment the number (AUTO_INCREMENT) so when each record is added the id will increase by one. The last line specifies that the id column will be the primary key for this table. A primary key is a unique number for that specific record or row of data.

The sitename, siteurl, and category column each are specifed as a VARCHAR or variable character field each with their respective maximum length. A VARCHAR field holds characters from 0 up to its maximum specified length. Most databases VARCHAR limit is 255 characters. If you have a field which may require more characters you should use a TEXT field, which is what is used for the description field. A text field can hold lots of data, usually dependent on the database.

The most common data types are INT, VARCHAR, TEXT and DATETIME. Look in the MySQL documentation about creating databases for information about other available datatypes in MySQL, and more specifics the data types it supports.


Executing SQL in PHP
The code to execute a SQL statement in PHP has 3 steps:
Connect to database
Create statement
Execute statement

1.The command to connect to the database is:

$cid = mysql_connect($host,$usr,$pwd);

Where $host, $usr, and $pwd are previously specified. Host refers to the machine running MySQL and the username and password to connect to that MySQL machine. This command returns a connection id, which is used for to identify this connection in later queries.

2.Creating the SQL statement simply consists of assigning the SQL statement such as the one above to a string. I usually have it run over multiple lines concatanating the string together as I go, this makes it easier to read.

3.The command to actual send the database the SQL command is:

$result = mysql_db_query($db,"$SQL",$cid);

Where $db is the database to query, $SQL is the SQL statement, and $cid is the connection id created above. This returns a 1 if executed correctly, and undefined or false if an error occurred.

Putting all of this together, and adding a little error detection and displaying of the errors gives us the following script, which you can download and load on to your web server running PHP. Note: Opening the file through the web server will execute the script and create the database tables.

Download: PHP Script to create tables (create_table.phps)
Note: You must edit the username and password at the top of this script to reflect your system.


Problems: The most common problem when running this script is connecting to the database. Make sure you replace the username and password variables with your username and password setup to connect to your MySQL server. You can trouble shoot connection problems using the mysql client from the shell, use mysql -? for help.

MySql : Getting Started

Getting started with a database means first figuring out what you want to do. Our sample project in this tutorial is building a database which manages links to sites. I want to be able to add, edit and remove links in the database, as well as display links from the database. I want the links to be categorized so I can display groups of links, instead of all links at once.

I want to record the following data for each link in the database:
Name of site
URL of site
Description of site
Link Category

Each of these will become one column in a table in the database. If you are not familiar with databases, a table can be thought of somewhat as a spreadsheet in Excel. The columns in a spreadsheet relate to the columns in a database. Each row in the spreadsheet would be considered a record (or row) in the database. There also can be multiple spreadsheets in a single Excel file, as there can be multiple tables in one database.

This application could use two tables, one table to store all of the categories and another table to store all the links, with a link back to the categories table. Because this is a beginner tutorial, I keep it simple by using just one table to store the info.

Advanced Topic
Two tables is the better way to create this application. The second table would store all the category names seperately with a unique key. The unique key is just a number for each category, usually starting at 1 for the first category, 2 for the next, and so on. A record in the links table, which are the individual links, would also store this category number with the link. This number is then used to refer back to the category table to get the category name when needed. It is a little more complicated but it is a much better way to create the database.

The benefits to using multiple tables is you get faster queries, and it is easier to manage the data. Some examples of the benefits of multiple tables:
Faster query when selecting the list of all categories because you are querying a smaller set of data.
Faster queries when selecting links by category, because the it is faster to select, group and sort numbers much faster then strings.
Easier to manage because you can change the category names seperately from the links, since they only store the numbers as reference to the category name.
Easier to enter data by category because you can pull the category name from the table and display it in a select form element instead of requiring the use to type it in.

Using multiple tables (or databases) and relating their data to each other using keys is referred to as relational databases. Read more about relational database design from the related links below.

RDBMS : Architecture of a Database Management System

The architecture of a database management system can be broadly divided into three levels :

a. External level
b. Conceptual level
c. Internal level



The External Level
This is the highest level, one that is closest to the user. It is also called the user view. The user view is different from the way data is stored in the database. This view describes only a part of the actual database. Because each user is not concerned with the entire database, only the part that is relevant to the user is visible. For example, end users and application programmers get different external views.

For example, an instructor will view the database as a collection of students and courses offered by the university. An administrator will view the database as a collection of records on the stock of course material provided by the university. The instructor is concerned with only a portion of database that is relevant to the instructor and the administrator is concerned with only the portion of database that is relevant to the administrator. These portions of database, which are viewed, by the instructor and administrator are reffered as their user’s view or external view.

Each user uses a language to carry out database operations. The application programmer uses either a conventional third-generation language, such as COBOL or C, or a fourth-generation language specific to the DBMS, such as visual FoxPro or MS Access.

The end user uses a query language to access data from the database. A query language is a combination of three subordinate language :
 Data Definition Language (DDL)
 Data Manipulation Language (DML)
 Data Control Language (DCL)

The data definition language defines and declares the database object, while the data manipulation language performs operations on these objects. The data control language is used to control the user’s access to database objects.

The Conceptual Level
This level comes between the external and the internal levels. The conceptual level represents the entire database as a whole, and is used by the DBA. This level is the view of the data “as it really is”. The user’s view of the data is constrained by the language that they are using. At the conceptual level, the data is viewed without any of these constraints.

The Internal Level
This level deals with the physical storage of data, and is the lowest level of the architecture. The internal level describes the physical sequence of the stored records.
Following is an example of the three levels :

External
Cout << “Emp#” << Employee_Code;
Cout << “Dept#” << DepartmentEmployee_Code;
Cout << “Salary#” << Salary;

Conceptual
Employee
Employee_Code Character 6
Department_Code Character 4
Salary Numeric 5

Internal
Stored Employee Length=18
Prefix Type=Byte(6), offset=0
Emp# type=byte(6), offset=6, index empx
Dept# type=byte(6), offset=12,
Salary type=byte(6), offset=6

Mappings
Mapping determines the correspondence between one level an another. There are two levels of mapping involved in this architecture. One is between the external and the conceptual levels, while the other is between the conceptual and the internal levels. The external-conceptual mapping determines the correspondence between the conceptual anad the user views. This specifies how a user views the conceptual data. The conceptual-internal mapping determines the correspondence between the conceptual and internal views. It specifies how the conceptual data is stored.

The first step in designing a databse is to define the conceptual level. The conceptual level is then mapped to the external level. Each user view and the requirement is taken into consideration. Next, the conceptual-internal mapping is done. The way data is stored is derived from the conceptual level. Ths three-level architecture of a DBMS helps achieve data independence.