Problem 2: Use mysql community server and workbench to create another relation d

Question

Problem 2: Use mysql community server and workbench to create another relation database that stores image, voice and video.

• There are different ways to doing that. It is not easy. You can search internet for clues.

• The challenging part is to design a schema that enables search on these multi-media contents

• Create at least two tables

• Stores at least 5 records on each (image, voice, video)

• Develop at least two views inside workbench to query the database to get results by using two tables

Problem 3:
Develop a web based application to retrieve multi-media that created in the last assignment
•   A image can be retrieved
•   A voice can be retrieved and played
•   A video can be retrieved and played

Include screen shots and a short write up to explain

Explanation / Answer

Problwm 2)
we use blob data type to store images/videos...

Coming to tables...

Table1:
--------
id
name

table2
-------
id
item

******** Inserting 5 data recirds into these tables..*******************
insert into Table1(id,name) values(1, myPhoto);
insert into Table2(id,item) values(1, FILE_READ('my.jpg'));

insert into Table1(id,name) values(2, scenary);
insert into Table2(id,item) values(1, FILE_READ('scenary.jpg'));

insert into Table1(id,name) values(3, myVideo);
insert into Table2(id,item) values(3, FILE_READ('myVideo.mp4'));

insert into Table1(id,name) values(4, song);
insert into Table2(id,item) values(4, FILE_READ('song.3gp'));

insert into Table1(id,name) values(5, speech);
insert into Table2(id,item) values(5, FILE_READ('speech.mp3'));

************** CREATING VIEWS *********************
CREATE VIEW [ItemID ITEM] AS
SELECT id,item
FROM table2

CREATE VIEW [ItemID name] AS
SELECT id,name
FROM table1

---------------------------------------------------------------------------------------------------------------------------------
Problem 3)
<?php
$host = 'localhost';
$username = 'username';
$password = 'password';
$db = 'myDB';

if(isset($_POST['id']) && is_numeric($_POST['id'])) {
//connect to the db
$link = mysql_connect("$host", "$username", "$password")
or die("Not connecting: " . mysql_error());

// select database
mysql_select_db("$db") or die(mysql_error());

// retireving blob data
$sqlStatement = "SELECT item FROM table2 WHERE id=" .$_POST['id'] . ";";

// getting result from the executing query
$result = mysql_query("$sqlStatement") or die("Not a valid query: " . mysql_error());

echo mysql_result($result, 0);

// slose connection
mysql_close($link);
}
?>

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.