This lesson describes the last preliminary step in developing
the Wish List application, that of creating a sample database with test
data. To complete the steps in this tutorial, you will need a database
in which to store data of wishers. With the NetBeans IDE you can
perform all these activities through the IDE interface.
Before starting, see the tutorial requirements described in Creating a CRUD
Application with PHP - Main page.
The current document is a part of the Creating a CRUD
Application in the NetBeans IDE for PHP tutorial.
Registering a MySQL Server
If you do not have a MySQL database server registered in the IDE, or you want general information about using MySQL with NetBeans IDE, see Connecting to a MySQL Database.
Creating
the Database User
Before you create a database you need to create its User who
will be granted the right to perform any operations on the database.
Creating a database User involves:
Start the IDE, switch to the Services window (Ctrl-5), and expand
the Databases node.
To connect to the MySQL
database server, navigate to the MySQL Server
node and from the context menu choose Connect.
The NetBeans IDE connects to the MySQL server, checks for the databases
available through the server, detects the system mysql
database, and adds the corresponding new node mysql
to the Databases tree.
To execute an SQL command, you need to be connected to a
database. Because only the MySQL system is available, you need to
connect to it. To connect to the
system database, navigate to the mysql node and
from the context menu choose Connect. If a connection does not already
exist, the New Database Connection dialog box appears. The User Name
field is by default filled in with root. In the Password field, enter
the root user's password.
Note: If you have connected to the mysql database before, this dialog does not appear. Instead, the new connection
node simply appears in the tree.
The New Database Connection dialog box shows the message "Connection
established." Click OK. A new node named jdbc:mysql://localhost:3306/mysql
is added to the Databases tree.
Navigate
to the jdbc:mysql://localhost:3306/mysql node and
from the context menu choose Execute Command.
An SQL Command window opens. In the SQL Command window, use syntax
similar to the following statement:
CREATE USER 'phpuser'@'localhost' IDENTIFIED BY 'phpuserpw'
From the context menu, choose Run Statement. If the command is executed
successfully, the Status bar shows the message: "SQL Statement(s)
executed successfully". If another message is displayed, check the
syntax and follow the message hints.
Creating
the Wishlist Database
To create the database:
Navigate to the MySQL Server at localhost:3306
node and from the context menu choose Create Database. The Create MySQL
Database dialog box appears. Fill in the fields:
In the Database Name field, enter wishlist.
Switch on the Grant full access to user checkbox and
from the drop down list select
Click OK.
The "Grant full access to user"
function does not always work. If it does not work, connect to the database
as the root user and send the SQL query GRANT
ALL ON wishlist.* TO .
A connection to the database appears in the tree. However the connection is for the root user. You need a connection for the phpuser user.
Establishing Connection
to the Wishlist Database
At the end of the previous section, you created the wishlist database with a connection to the root user. Now you create a new connection for the phpuser user.
In the Services window, right-click the Databases node and select New Connection. The New Connection Wizard opens.
In the New Connection Wizard's Locate Driver panel, select the MySQL (Connector/J Driver). Click Next. The Customize Connection panel opens.
In the Database field, type wishlist.
In the User Name and Password edit boxes, enter the name
and the password specified in section Creating
the Owner (User) of the Database (in our example phpuser
and phpuserpw respectively). Tick Remember Password. Click Test Connection, and if the connection succeeds, click OK.
The
corresponding new connection node is displayed in the Databases tree. Now you can delete the root user's connection
to the wishlist database. Click the jdbc:mysql://localhost:3306/wishlist [root on Default schema] connection
and choose Delete.
Designing
the Structure of the Wishlist Database
To arrange and store all the necessary data you need two
tables:
A wishers table for storing names and passwords of
registered users
A wishes table for storing descriptions of wishes
The wishers table contains three fields:
id - the unique ID of a wisher. This field is used as the
Primary Key
name
password
The wishes table contains four fields:
id - the unique ID of a wish. The field is used as the
Primary Key
wisher_id - the ID of the wisher to whom the wish belongs.
The field is used as the Foreign Key.
description
due_date - the date by when the wish is requested
The tables are related through the wisher's ID. All the fields
are mandatory except due_date in wishes.
Creating
the Tables
To connect to the database, on the jdbc:mysql://localhost:3306/wishlist
connection, click the right mouse button and choose Connect from the
context menu. Note: If the menu item is disabled,
you are already connected. Continue with step 2.
From the same context menu, choose Execute Command. An
empty SQL Command window opens.
To create the wishers table,
Type the following SQL query (note that
you need to explicitly set character sets to UTF-8 for
internationalization):
CREATE TABLE wishers( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name CHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL UNIQUE, password CHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL )
Note: You can get a unique auto
generated number from MySQL by specifying the AUTO_INCREMENT property
for a field. MySQL will generate a unique number by incrementing the
last number of the table and will automatically add to the auto
incremented field. In our example the ID field is auto incremented.
Click the right mouse button on the
query and then choose Run Statement from the context menu.
Note: The
default storage engine for MySQL is MyISAM, which does not support
foreign keys. If you want to use foreign keys, consider using InnoDB as
the storage engine.
To create the wishes table:
Type the following SQL query:
CREATE TABLE wishes( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, wisher_id INT NOT NULL, description CHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, due_date DATE, FOREIGN KEY (wisher_id) REFERENCES wishers(id) )
Click the right mouse button on the
query and then choose Run Statement from the context menu.
To verify that the new tables are added to the database,
switch to the Services window and then navigate to the
jdbc:mysql://localhost:3306/wishlist connection node.
Click the right mouse button and choose Refresh. The nodes
wishers and wishes appear in the tree.
Note:
You
can download a set of SQL commands for creating the MySQL wishlist
database here.
Entering
the Test Data
To test your application you will need some data in the
database. The example below shows how to add two wishers and four
wishes.
On the jdbc:mysql://localhost:3306/wishlist connection,
click the right mouse button and choose Execute Command. An empty SQL
Command window opens.
To add a wisher, use syntax similar to the example below:
INSERT INTO wishers (name, password) VALUES ('Tom', 'tomcat');
Click the right mouse button on the query and from the context menu
choose Run Statement. Note: The statement does not contain
a value for the id field. The values are entered
automatically because the field type is specified as AUTO_INCREMENT.
Enter another test wisher:
INSERT INTO wishers (name, password) VALUES ('Jerry', 'jerrymouse');
To add the wishes, use syntax similar to the example below:
INSERT INTO wishes (wisher_id, description, due_date) VALUES (1, 'Sausage', 080401); INSERT INTO wishes (wisher_id, description) VALUES (1, 'Icecream'); INSERT INTO wishes (wisher_id, description, due_date) VALUES (2, 'Cheese', 080501); INSERT INTO wishes (wisher_id, description) VALUES (2, 'Candle');
Select the queries, click the right mouse button on each query and from
the context menu choose Run Selection.
Note: You can also execute the queries
one after another as described in item 2.
To view the test data, click the right mouse button on the
relevant table and from the context menu choose View Data.
To send comments and suggestions, get support, and keep
informed on the latest developments on the NetBeans IDE PHP development
features, join
the mailing list.
