MYSQL Evidence Question:
Create a database named Company and do the following task:
Q1. Create a table named user: id(int(4) primary key, auto_increment), username(varchar(20)),
password(varchar(30)).
·
Add two or more
data into the table.
·
Create a login
form to take username and password and write php script to check the valid
user.
Q2. Create two tables:
Manufacturer
(id (auto increment), name (varchar(50)), address (varchar(100)), contact_no
(varchar(50)).
Product (id (auto increment), name
(varchar(50)), price (INT(5)), manufacturer_id (INT(10)).
Add two or more data into the tables.
Create an after delete trigger which
will delete record(s) from the product table when any corresponding
manufacturer_id are deleted from the
manufacturer table.
Create a form to select manufacturer id
and write php script to delete the record from Manufacturer table of
selected manufacturer.
Q3. Create
a stored procedure to insert data into Manufacturer table. Create a form and
write php script to insert data into
Manufacturer table using stored procedure.
CREATE
PROCEDURE `manufact` (IN name
VARCHAR(50),IN address VARCHAR(100),IN contact VARCHAR(50))
BEGIN
insert
into manufacturer values('',name,address,contact);
END//
Q4.
Create a view to display all records from the product table where price is
greater than 5000. Write php script to display the data from the view.
Question 1:
Create table and insert values (in
phpmyadmin use wizard, don’t write sql code)
CREATE
TABLE IF NOT EXISTS `user` (
`username` varchar(20) NOT NULL,
`password` varchar(30) NOT NULL
)
ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT
INTO `user` (`username`, `password`) VALUES
('faruk',
'12345'),
('monir',
'54321'),
('sohel',
'11111');
checkUser.php
<!DOCTYPE
HTML>
<html><head><title>login
form</title></head>
<body>
<h2>This
is login form</h2>
<form
action="#" method="post">
User
Name : <input type="text" name="username"><br>
Password
: <input type="password" name="password"><br>
<input
type="submit" name="submit" id="submit"
value="Go !">
</form>
<?php
if(isset($_POST['submit'])){
$a=$_POST['username'];
$b=$_POST['password'];
$myconnection=
new
mysqli("localhost","root","","company");
$myquery="select
* from user where username='$a' and password='$b'";
$result=$myconnection->query($myquery);
if($result->num_rows
> 0){echo "<h3>you are register user</h3>";}
else
{echo "<h3>you are not register user</h3>";}
$result->free();
$myconnection->close();
}
?></body></html>
Question 2: Create table and insert
values (in phpmyadmin use wizard, don’t write sql code)
CREATE
TABLE IF NOT EXISTS `manufacturer` (
`id` int(7) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`address` varchar(100) NOT NULL,
`contact_no` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
)
ENGINE=MyISAM DEFAULT CHARSET=utf8
AUTO_INCREMENT=4 ;
INSERT
INTO `manufacturer` (`id`, `name`, `address`, `contact_no`) VALUES
(1,
'monirul', 'Dhaka', '123345'),
(2,
'ACI Ltd', 'Narayanganj', '027645825'),
(3, 'Nestly', 'Chittagong', '01819653298');
CREATE
TABLE IF NOT EXISTS `product` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`price` int(5) NOT NULL,
`manufacturer_id` int(10) NOT NULL,
PRIMARY KEY (`id`)
)
ENGINE=MyISAM DEFAULT CHARSET=latin1
AUTO_INCREMENT=15 ;
INSERT
INTO `product` (`id`, `name`, `price`, `manufacturer_id`) VALUES
(1,
'cd rom', 2000, 1),
(2,
'mouse', 4700, 2),
(3,
'printer', 10000, 2),
(4,
'Laptop', 62000, 3);
Now create a trigger: (in phpmyadmin)
CREATE
TRIGGER log_manu_del AFTER DELETE on manufacturer
FOR
EACH ROW
BEGIN
DELETE
FROM product
WHERE product.manufacturer_id = old.id;
END//
Deletemanufacturer.php
<?php
$deleteId=$_GET['deleteid'];
if($deleteId){
$db=new
mysqli("localhost", "root", NULL, "company");
$myShowQuery="delete
from manufacturer where id='$deleteId';";
$db->query($myShowQuery);
$db->close();
echo
"manufacturer with id: $deleteId deleted";}
?>
<html>
<head>
<title>Manufacturer</title></head>
<body>
<h2>Manufacturer
Table</h2>
<form action="#"
method="get">
<table>
<tr><td>Manufacturer</td><td>:</td>
<td>
<?php
$db=new
mysqli("localhost", "root", NULL, "company");
$myShowQuerry="select *
from manufacturer;";
$result =
$db->query($myShowQuerry);
echo "<select
name='deleteid'>";
while
(list($id,$name)=$result->fetch_row()){
echo
"<option value="
.$id.">".$name."</option>";}
echo
"</select>";
$result->free;
$db->close();
?>
</td></tr>
<tr><td></td><td></td>
<td>
<input type="submit"
name="delete"
id="btnDelete" value="delete" />
</td></tr></table></form></body></html>
Question 3:
Create a procedure in mysql : in
phpmyadmin)
CREATE
PROCEDURE `manufact`
(
IN
name VARCHAR(50),
IN
address VARCHAR(100),
IN
contact VARCHAR(50)
)
BEGIN
insert
into manufacturer values('',name,address,contact);
END//
insertintoManufacturer.php
<?php
if(isset($_GET["name"])){
$db = new mysqli("localhost",
"root", NULL, "company");
$name=$_GET["name"];
$address=$_GET["address"];
$tel=$_GET["contact"];
$queryString="call
manufact('$name','$address','$tel');";
$myQuerry="call
manufact($name,$address,$tel)";
$result = $db->query($queryString);
if($result){echo "Data
Added!!";}
else echo "Error!!!";
$result->free;
$db->close();
}
?>
<form action="#"
method="get">
<table
style="width: 100%; border: 1px solid red" >
<tr><td>id</td><td>:</td><td><input
type="text" name="id" id="id"
readonly="readonly"/></td></tr>
<tr><td>name</td><td>:</td><td><input
type="text" name="name"
id="name"/></td></tr>
<tr><td>address</td><td>:</td><td><input
type="text" name="address"
id="address"/></td></tr>
<tr><td>contact_no</td><td>:</td><td><input
type="text" name="contact"
id="contact"/></td></tr>
<tr><td
align="center" colspan="3">
<input type="submit"
name="add" value="Add">
</td></tr>
</table>
</form>
Question4:
<?php
$db=new
mysqli("localhost", "root", NULL, "company");
$createView=
"CREATE VIEW products_view AS
SELECT
id, Name,price, manufacturer_id
FROM
product
WHERE
price>5000";
if
($db->query($createView)){
echo "View creates
successfully";
}else
echo "error";
