Advanced database system
Dr. yazeed alsayed
assignment 1
Khaled Mahmoud
Ci893
Installing and initiating
1- For creating database, first we need to use DBMS like MYSQL workbench or anything else, in this
assignment I will use phpMyAdmin, I will connect to it using xampp.
2- first of all we need to install xampp from this website:
https://www.apachefriends.org/download.html
3- After installing and setting it up I will open xampp and start Apache and mysql connection to
connect me to the DBMS
4- To open DBMS
go to any web browser and write in the link bar (localhost) this page will come:
Then we click on phpMyAdmin on the top right, and this will come:
This is the DBMS, and here we will create our database and tables. But there are three methods to do this:
1- Using phpMyAdmin objects and icons.
2- Using phpMyAdmin code page.
3- Using cmd.
In this assignment I will use phpMyAdmin code page.
Creating database
1- Write the SQL syntax below and run it:
CREATE DATABASE databasename;
You will notice on the left that your database has been created.
Creating tables & inserting data
1- To create table & its columns use this syntax:
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
....
);
I have applied this syntax :
create table test ( Id int auto increment, Fname varchar(255), adress varchar(255) );
notice that now we have a table called test with three columns, to show the table we will write this syntax:
select * from test;
and this will appear:
because the table has no data it will appear empty as you see.
2- To insert data into the table, use the syntax as below:
INSERT INTO test (Fname,adress)
VALUES (khaled, Jeddah);
And we will notice that data have been added.
Adding data using HTML form
Start XAMPP Server.
Open localhost/phpmyadmin in your web browser.
Write HTML and PHP code in your Notepad in a particular folder.
Submit data through HTML Form.
Verify the results.
Steps in details:
We have finished the first two steps now we need to make HTML form and PHP connection.
1- To make HTML form use this code:
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Contact Form - PHP/MySQL Demo Code</title>
</head>
<body>
<fieldset>
<legend>Form</legend>
<form name="frmContact" method="post" action="contact.php">
<p>
<label for="Name">Name </label>
<input type="text" name="txtName" id="txtName">
</p>
<p>
<label for="adress">adress</label>
<input type="text" name="txtadress" id="txtadress">
</p>
<p> </p>
<p>
<input type="submit" name="Submit" id="Submit" value="Submit">
</p>
</form>
</fieldset>
</body>
</html>
It will look like this:
2- To make PHP connection use this code:
<?php
// database connection code
// $con = mysqli_connect('localhost', 'database_user',
'database_password','database');
$con = mysqli_connect('localhost', 'root', '','assignment_1');
// get the post records
$txtName = $_POST['txtName'];
$txtadress = $_POST['txtadress'];
// database insert SQL code
$sql = "INSERT INTO `test` (`Fname`, `adress`) VALUES ('$txtName',
'$txtadress')";
// insert in database
$rs = mysqli_query($con, $sql);
if($rs)
{
echo "Records Inserted successfully";
}
?>
And I will save the two files in this path “E:\xampp\htdocs”.
3- Now we open the html file and change the path from the link bar into "local host\html"
:After completing the form and submitting, this page should appear
:And you will find the data have been inserted into the database
Implementing one function
I will retrieve the record where ID = 2 using this syntax:
SELECT * FROM `test` WHERE ID=2;
this is the result:
