Assignment in Computer 3 Lec

Monday and Wednesday

3:30-4:30pm

Name: Marifel Baquero

Course: BSA

1.1.1. MySQL is a freely available open source Relational Database Management System (RDBMS) that uses
Structured Query Language (SQL). SQL is the most popular language for adding, accessing and managing
content in a database. It is most noted for its quick processing, proven reliability, ease and flexibility of use.

2.2.2. Basic Commands in MySQL

Below you’ll find some basic MySQL commands in case you are new to it.

First of all I recommend using the MySQL terminal to connect to your database server, because it forces
you to learn the commands by hand. Using a GUI tool like PHPMyAdmin makes the job easier but
removes the learning curve.

I am assuming your MySQL server is on your remote server (i.e., on the hosting account you rent from a
hosting company). In that case you can login to your server via cPanel and create a database and a user
with all privileges for that database (make sure to write down the names and password). After that
make sure to add your IP address to the list of IPs allowed to access the MySQL server remotely.

If you don’t have access to cPanel or prefer to use the command line, here’s the process. First of all
install the MySQL client on your machine:

sudo apt-get install mysql-client

Then login to the MySQL server using the root user:

*mysql -h HOSTNAME -u root -p

Once logged in, create the database:

*create database db_name;

Create the user:

*create user db_user;

And finally add all the privileges, while creating a password to the user:

*grant all on db_name.* to 'db_user'@'HOSTNAME' identified by 'db_password';

Notice that if you are logged in locally, the hostname will be ‘localhost’.

Now you should log out as root and log in as the normal user you just created:

*mysql -h HOSTNAME -u USERNAME -p

You’ll be asked to type the password of the user. If the connection is successful you’ll see a welcome
message and the mysql prompt.

Now here’s a list of commands you can use to play arround:

Creating a database
*create database DBNAME;

Displayingallavailabledatabaseson thrserver
show databases;

Selection a database for usage

*use DBNAME;

Creating a table inside the selected database

*create table users(

name varchar(30),

password int,

email varchar(30)

);

Displaying all tables inside a database

show tables;

Getting information about the table (columns, key, NULL values, etc)
*describe TABLENAME;

Inserting an entry into a table

*insert into users(name, password, email) values('daniel',12345,'[email protected]');

Deleting an entry from a table

*delete from users where name='daniel';

Adding a new column

*alter table users add facebook varchar(30);

Adding a new id column to work as the primary key

alter table users add id int not null auto_increment first, add primary key(id);

Changing the default value of a column

*alter table users modify column email varchar(30) default '[email protected]';

Updating the value of a column

*update users set email='[email protected]' where id=5;

Displaying the last 5 rows of a table

*select * from users order by id desc limit 5;
Creating a table with a foreign key

*CREATE TABLE child (

id INT,

parent_id INT,

INDEX par_ind (parent_id),

FOREIGN KEY (parent_id)

REFERENCES parent(id)

ON DELETE CASCADE

);

Performing a join

*SELECT a.id, a.name FROM users a, payments b WHERE b.status=1 AND a.id = b.id;

Replacing information on columns

*UPDATE table_name SET column_name = REPLACE(column_name, 'search', 'replace');

Selecting and deleting from one table based on data from another

*SELECT * FROM t_salary WHERE employee_id NOT IN (select id FROM t_employee);

DELETE FROM t_salary WHERE employee_id NOT IN (select id FROM t_employee);

Select entries from a specific month or year

*SELECT * FROM t_table WHERE month(date_column) = 10 AND year(date_column) = 2014;

Notice that the above query is very slow though, because it does not use indexes, so the DB will need to
parse all the rows. If you have a lot of entries it will be much better to use this:

*SELECT * FROM t_table WHERE date_column > '2014-10-00' AND date_column < '2014-10-40';

Disconnecting
*quit

3.3.3. Creating a Table

Creating the database is the easy part, but at this point it is empty, as SHOW TABLES tells you:
mysql> SHOW TABLES;

Empty set (0.00 sec)

The harder part is deciding what the structure of your database should be: what tables you need and
what columns should be in each of th
You can probably think of other types of information that would be useful in the pet table, but the ones
identified so far are sufficient: name, owner, species, sex, birth, and death.
Use a CREATE TABLE statement to specify the layout of your table:
mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),

species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

VARCHAR is a good choice for the name, owner, andspecies columns because the column values vary
in length. The lengths in those column definitions need not all be the same, and need not be 20. You
can normally pick any length from 1 to 65535, whatever seems most reasonable to you. If you make a
poor choice and it turns out later that you need a longer field, MySQL provides an ALTER
TABLE statement.
Several types of values can be chosen to represent sex in animal records, such as 'm' and 'f', or
perhaps 'male' and 'female'. It is simplest to use the single characters 'm' and 'f'.
The use of the DATE data type for the birth and death columns is a fairly obvious choice.
Once you have created a table, SHOW TABLESshould produce some output:
mysql> SHOW TABLES;

+---------------------+

| Tables in menagerie |

+---------------------+

| pet |

+---------------------+

To verify that your table was created the way you expected, use a DESCRIBE statement:
mysql> DESCRIBE pet;

+---------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+---------+-------------+------+-----+---------+-------+

| name | varchar(20) | YES | | NULL | |

| owner | varchar(20) | YES | | NULL | |

| species | varchar(20) | YES | | NULL | |

| sex | char(1) | YES | | NULL | |

| birth | date | YES | | NULL | |

| death | date | YES | | NULL |

Use the SHOW statement to find out what databases currently exist on the server:
mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
| mysql |
| test |
| tmp |
+----------+
The mysql database describes user access privileges. The test database often is available as a
workspace for users to try things out.
The list of databases displayed by the statement may be different on your machine; SHOW
DATABASES does not show databases that you have no privileges for if you do not have the SHOW
DATABASES privilege. See Section 13.7.5.14, “SHOW DATABASES Syntax”.
If the test database exists, try to access it:
mysql> USE test
Database changed
USE, like QUIT, does not require a semicolon. (You can terminate such statements with a semicolon if
you like; it does no harm.) The USE statement is special in another way, too: it must be given on a single
line.
You can use the test database (if you have access to it) for the examples that follow, but anything you
create in that database can be removed by anyone else with access to it. For this reason, you should
probably ask your MySQL administrator for permission to use a database of your own. Suppose that
you want to call yours menagerie. The administrator needs to execute a statement like this:

mysql> GRANT ALL ON menagerie.* TO 'your_mysql_name'@'your_client_host';

where your_mysql_name is the MySQL user name assigned to you and your_client_host is the host
from which you connect to the server.

4.4.4. Creating a Database

To create and delete a database you should have admin privilege. Its very easy to create a new MySQL
database. PHP uses mysql_query function to create a MySQL database. This function takes two
parameters and returns TRUE on success or FALSE on failure.

Syntax
bool mysql_query( sql, connection );

Sr.No Parameter & Description

 1 sql

Required - SQL query to create a

database

2 connection

Optional - if not specified then last

opend connection by mysql_connect
will be used.

Example
Try out following example to create a database −

<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);

if(! $conn ) {
die('Could not connect: ' . mysql_error());
}

echo 'Connected successfully';

$sql = 'CREATE Database test_db';

$retval = mysql_query( $sql, $conn );
if(! $retval ) {
die('Could not create database: ' . mysql_error());
}

echo "Database test_db created successfully\n";

mysql_close($conn);
?>

5.5.5. Creating MySQL Database using Php

Once you establish a connection with a database server then it is required to select a particular
database where your all the tables are associated.

This is required because there may be multiple databases residing on a single server and you can do
work with a single database at a time.

PHP provides function mysql_select_db to select a database.It returns TRUE on success or FALSE on
failure.

Syntax
bool mysql_select_db( db_name, connection );

Sr.No Parameter & Description

1 db_name

Required - Database name to be

selected

2 connection

Optional - if not specified then last

opend connection by mysql_connect
will be used.
 Example
Here is the example showing you how to select a database.

<?php
$dbhost = 'localhost:3036';
$dbuser = 'guest';
$dbpass = 'guest123';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);

if(! $conn ) {
die('Could not connect: ' . mysql_error());
}

echo 'Connected successfully';

mysql_select_db( 'test_db' );
mysql_close($conn);
?>

6.6.6. Creating MySqL Table in Php

To create tables in the new database you need to do the same thing as creating the database. First
create the SQL query to create the tables then execute the query using mysql_query() function.

Example
Try out following example to create a table −

<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);

if(! $conn ) {
die('Could not connect: ' . mysql_error());
}

echo 'Connected successfully';

$sql = 'CREATE TABLE employee( '.

'emp_id INT NOT NULL AUTO_INCREMENT, '.
'emp_name VARCHAR(20) NOT NULL, '.
'emp_address VARCHAR(20) NOT NULL, '.
'emp_salary INT NOT NULL, '.
'join_date timestamp(14) NOT NULL, '.
'primary key ( emp_id ))';
mysql_select_db('test_db');
$retval = mysql_query( $sql, $conn );

if(! $retval ) {
die('Could not create table: ' . mysql_error());
}
echo "Table employee created successfully\n";

mysql_close($conn);
?>

In case you need to create many tables then its better to create a text file first and put all the SQL
commands in that text file and then load that file into $sql variable and excute those commands.

Consider the following content in sql_query.txt file

CREATE TABLE employee(

emp_id INT NOT NULL AUTO_INCREMENT,
emp_name VARCHAR(20) NOT NULL,
emp_address VARCHAR(20) NOT NULL,
emp_salary INT NOT NULL,
join_date timestamp(14) NOT NULL,
primary key ( emp_id ));
<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);

if(! $conn ) {
die('Could not connect: ' . mysql_error());
}

$query_file = 'sql_query.txt';

$fp = fopen($query_file, 'r');

$sql = fread($fp, filesize($query_file));
fclose($fp);

mysql_select_db('test_db');
$retval = mysql_query( $sql, $conn );

if(! $retval ) {
die('Could not create table: ' . mysql_error());
}

echo "Table employee created successfully\n";

mysql_close($conn);
?>

7.7.7. PHP Create MySQL Table:Adding/Inserting

A database table has its own unique name and consists of columns and rows.

Create a MySQL Table Using MySQLi and PDO

The CREATE TABLE statement is used to create a table in MySQL.

We will create a table named "MyGuests", with five columns: "id", "firstname", "lastname", "email" and
"reg_date":

CREATE TABLE MyGuests (

id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP
)
Notes on the table above:

The data type specifies what type of data the column can hold. For a complete reference of all the
available data types, go to our Data Types reference.

After the data type, you can specify other optional attributes for each column:

NOT NULL - Each row must contain a value for that column, null values are not allowed

DEFAULT value - Set a default value that is added when no other value is passed

UNSIGNED - Used for number types, limits the stored data to positive numbers and zero

AUTO INCREMENT - MySQL automatically increases the value of the field by 1 each time a new record
is added

PRIMARY KEY - Used to uniquely identify the rows in a table. The column with PRIMARY KEY setting is
often an ID number, and is often used with AUTO_INCREMENT

Each table should have a primary key column (in this case: the "id" column). Its value must be unique
for each record in the table.

The following examples shows how to create the table in PHP:

Example (MySQLi Object-oriented)

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = newmysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: ". $conn->connect_error);
}

// sql to create table

$sql = "CREATE TABLE MyGuests (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP
)";

if ($conn->query($sql) === TRUE) {

echo "Table MyGuests created successfully";
} else {
echo "Error creating table: ". $conn->error;
}

$conn->close();
?>

Inserting Data into a MySQL Database Table

Now that you've understood how to create database and tables in MySQL. In this tutorial you will learn
how to execute SQL query to insert records into a table.
The INSERT INTO statement is used to insert new rows in a database table.
Let's make a SQL query using the INSERT INTOstatement with appropriate values, after that we will
execute this insert query through passing it to the PHP mysqli_query() function to insert data in table.
Here's an example, which insert a new row to the persons table by specifying values for
the first_name, last_name and email fields.

Example
<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
$link = mysqli_connect("localhost", "root", "", "demo");

// Check connection
if($link === false){
die("ERROR: Could not connect. " . mysqli_connect_error());
}

// Attempt insert query execution

$sql = "INSERT INTO persons (first_name, last_name, email) VALUES ('Peter', 'Parker',
'[email protected]')";
if(mysqli_query($link, $sql)){
echo "Records inserted successfully.";
} else{
echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
}

// Close connection
mysqli_close($link);
8.8.8. MySQL databases - read a record with PHP

To read records from a database, the technique is usually to loop round and find the ones you want.
To specify which records you want, you use something called SQL. This stands for Structured Query
Language. This is a natural, non-coding language that uses words like SELECT and WHERE. At it's
simplest level, it's fairly straightforward. But the more complex the database, the more trickier the SQL
is. We'll start with something simple though.

What we want to do, now that we have a connection to our database, is to read all the records from
our address book, and print them out to the page. Here's some new code, added to the PHP script you
already have. The new lines are in blue:

<?PHP
require '../configure.php'

$db_handle = mysqli_connect(DB_SERVER, DB_USER, DB_PASS );

$database = "addressbook";

$db_found = mysqli_select_db($db_handle, $database);

if ($db_found) {

$SQL = "SELECT * FROM tbl_address_book";

$result = mysqli_query($db_handle, $SQL);

while ( $db_field = mysqli_fetch_assoc($result) ) {

print $db_field['ID'] . "<BR>";

print $db_field['First_Name'] . "<BR>";
print $db_field['Surname'] . "<BR>";
print $db_field['Address'] . "<BR>";

}
else {

print "Database NOT Found ";

mysqli_close($db_handle);

?>
Before we go through the new code to see what's happening, run your script. You should find that the
address you added in a previous section is printed out. (We only have one record at the moment.)

1
Test
Name
12 Test Street

The first line in the new code is this:

$SQL = "SELECT * FROM tbl_address_book";

The $SQL is just a normal variable. But we're putting into it a long string. This is a SQL statement. Here's
a brief run down on SQL.

Structured Query Language

SQL (pronounced SEEKwel), is a way to query and manipulate databases. The basics are quite easy to
learn. If you want to grab all of the records from a table in a database, you use the SELECT word. Like
this:

SELECT * FROM Table_Name

SQL is not case sensitive, so the above line could be written:

Select * From Table_Name

But your SQL statements are easier to read if you type the keywords in uppercase letters. The keywords
in the lines above are SELECTand FROM. The asterisk (*) means "All Records". Table_Name is the name
of a table in your database. So the whole line reads:

"SELECT all the records FROM the table called Table_Name"

You don’t have to select all the records from your database. You can just select the columns that you
need. For example, if we wanted to select just the first name and surname columns from this table, we
can specify that in our SQL String:

"SELECT First_Name, Surname FROM tbl_address_book";

When this SQL statement is executed, only the First_Name and Surname columns from the database
will be returned.

There are a lot more SQL commands to get used to, and you'll meet more of them as you go along. For
now, we're just selecting all the records from our table.
Back to the Code

The first line of our code, then, was this:

$SQL = "SELECT * FROM tbl_address_book";

SO we have a SQL statement, but we need to pass it to another inbuilt function:

mysqli_query( )

The mysqli_query( ) function is used to send a SQL query to your database. If you have typed out your
SQL correctly, then the function will return a value. This value will be true, false, or a file handle.
Because we're using the SELECTkeyword, the value returned by will be a file handle. In our code, the
line was this:

$result = mysqli_query( $SQL );

The file handle returned in our $result variable just points to the results. It doesn't actually bring
anything back. To bring back the data, we had this inside a while loop:

$db_field = mysqli_fetch_assoc($result );

The inbuilt function we're using to bring results back is this:

mysqli_fetch_assoc( $result )

The assoc part means Associative. As in "associative array". So we're asking that the results be brought
back in an array format. In between the round brackets of mysqli_fetch_assoc we have typed the name
of our file handle – the one that was pointing to the results of SQL statement.

Remember: an associative array is one where the keys are text. So it's this format:

Array['One'] =
Array['Two'] =
Array['Three]' =

And not this:

Array[1] =
Array[2] =
Array[3] =

When the mysql_fetch_assoc function returns an array, we're putting it all into a variable
called $db_field. The Key part of the array is all the Column names from our database tables. This is
done automatically for you. So the array format will be this:
 $db_field[Column_Name] = Value

The reason why you're doing this is so that you can loop round the array and access the values from
the table. Here's our loop, without anything between the round brackets:

while ( ) {

print $db_field['ID'] . "<BR>";

print $db_field['First_Name'] . "<BR>";
print $db_field['Surname'] . "<BR>";
print $db_field['Address'] . "<BR>";

So we're printing whatever the value is in the array

position $db_field['ID'],$db_field['First_Name'], $db_field['Surname']and $db_field['Address']. We're
also adding a HTML line break at the end, just for printing purposes.

If all that is confusing, just remember the format:

Array_Name[Table_Coulmn_Name] = Value_From_Record

Our whole while loop, then, is this:

while ($db_field = mysqli_fetch_assoc($result) ) {

print $db_field['ID'] . "<BR>";

print $db_field['First_Name'] . "<BR>";
print $db_field['Surname'] . "<BR>";
print $db_field['Address'] . "<BR>";

Because that is a bit complex, let's go through the steps we've used to access the records from our
table:

Set up a SQL Statement that can be used to get the records from the database table

Use mysqli_query() to bring back the records we've specified in Step 1

Use mysqli_fetch_assoc() to set up an array. The array will contain all the records that were returned
in Step 2

Loop round all the data in the array using a While loop

Step 1 was this, in the code:

 $SQL = "SELECT * FROM tbl_address_book";

Step 2 was this:

$result = mysqli_query($SQL);

Step 3 was this:

$db_field = mysqli_fetch_assoc($result)

And Step 4 was this:

while ($db_field = mysqli_fetch_assoc($result) ) {

print $db_field['ID'] . "<BR>";

print $db_field['First_Name'] . "<BR>";
print $db_field['Surname'] . "<BR>";
print $db_field['Address'] . "<BR>";