Skip to main content

Listing in tables for database BOOKSHOP, table BOOKS

EXERCISE 1: Simple Listing

Develop a web-based application (using PHP script) to list all the contents of the table Books.

listing all records in table

The SQL dump for the database (for the database BOOKSHOP),

-- DATABASE NAME: 'bookshop'
-- version 2.11.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jul 26, 2011 at 06:09 AM
-- Server version: 5.0.45
-- PHP Version: 5.2.4

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `bookshop`
--
CREATE DATABASE `bookshop` DEFAULT CHARACTER SET latin1 COLLATE latin1_general_ci;
USE `bookshop`;

-- --------------------------------------------------------

--
-- Table structure for table `books`
--
CREATE TABLE `books` (
`isbn` varchar(15) collate latin1_general_ci NOT NULL,
`title` varchar(100) collate latin1_general_ci NOT NULL,
`authorid` varchar(3) collate latin1_general_ci NOT NULL,
`publisherid` varchar(3) collate latin1_general_ci NOT NULL,
`year` int(4) NOT NULL,
`number` int(11) NOT NULL,
PRIMARY KEY (`isbn`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

--
-- Dumping data for table `books`
--
INSERT INTO `books` (`isbn`, `title`, `authorid`, `publisherid`, `year`, `number`) VALUES
('1047154354', 'Computer Security', 'DG', 'JW', 2000, 5),
('7223020711', 'Anti Hacker Toolkit', 'MS', 'MGH', 2003, 3),
('0201440997', 'Computer Art Security and Science', 'MB', 'AW', 2004, 4);


The PHP code;


<?php
//1. establish connection to database server
//since it is on XAMPP, so no password for root
//BAD practice
$db=mysqli_connect("localhost","root","","BOOKSHOP");

//2. check the connection
if($db==false){
echo "Database connection fail!<br>";
echo "Error: ".mysqli_connect_error($db);
}
else{//if can connect
echo "Yeay... Connected to database<br>";

//3. sql command to SELECT all data
$sql="SELECT * FROM books";

//4. send sql command to database server
$rs=mysqli_query($db, $sql);

//5. display records
if ($rs==false){
echo "SQL Error".mysqli_error($db);
}
else{//display SQL error message
//table header
echo "<table border=1>";
echo "<tr><td>ISBN</td>".
"<td>Title</td>".
"<td>Author</td>".
"<td>Publisher</td>".
"<td>Year</td>".
"<td>Quantity</td> </tr>";
while ($record=mysqli_fetch_array($rs)){
echo "<tr>";
echo "<td>".$record['isbn']."</td>";
echo "<td>".$record['title']."</td>";
echo "<td>".$record['authorid']."</td>";
echo "<td>".$record['publisherid']."</td>";
echo "<td>".$record['year']."</td>";
echo "<td>".$record['number']."</td>";
echo "</tr>";
}
echo "</table>";
}
}
?>


EXERCISE 2: Searching


Use the database Bookshop. Develop a web-based application (using PHP script) to find a book based on the book ‘title’ and ‘year’ published.


search books by title and year


<html>
<head>
<title>Search Book by Title and Year</title>
</head>
<body>
Search by Title and Year (published)<br>
<form method="get" action="">
Book Title <input type="text" name="txttitle"><br>
Year Published
<select name="cmbyear">
<?php
for($i=1999; $i<date('Y');$i++){
echo "<option value='$i'> $i " .
"</option>\n";
}
?>
</select><br>
<input type="submit" value="Search Book">
</form>

<hr>
<?php
//1. establish connection to database server
//since it is on XAMPP, so no password for root
//BAD practice
$db=mysqli_connect("localhost","root","","BOOKSHOP");

//2. check the connection
if($db==false){
echo "Database connection fail!<br>";
echo "Error: ".mysqli_connect_error($db);
}
else{//if can connect
//echo "Yeay... Connected to database<br>";

//extract the search term
$title=$_GET['txttitle'];
$year=$_GET['cmbyear'];
//3. sql command to SELECT all data
$sql="SELECT * FROM books " .
"WHERE title LIKE '%$title%' ".
"AND year='$year'";

//4. send sql command to database server
$rs=mysqli_query($db, $sql);

//5. display records
if ($rs==false){
echo "SQL Error".mysqli_error($db);
}
else if($title==NULL){
//if no input on title field
echo "Please enter the book title";
}
else if(mysqli_num_rows($rs)==0){
//if no record matched
echo "Sorry, NO Book matched<br>";
echo "Search term - Title: $title; year: $year<br>";
}
else{//there is(are) record(s) found
//table header
echo "Search result - Title: $title; year: $year<br>";
echo "<table border=1>";
echo "<tr><td>ISBN</td>".
"<td>Title</td>".
"<td>Author</td>".
"<td>Publisher</td>".
"<td>Year</td>".
"<td>Quantity</td> </tr>";
while ($record=mysqli_fetch_array($rs)){
echo "<tr>";
echo "<td>".$record['isbn']."</td>";
echo "<td>".$record['title']."</td>";
echo "<td>".$record['authorid']."</td>";
echo "<td>".$record['publisherid']."</td>";
echo "<td>".$record['year']."</td>";
echo "<td>".$record['number']."</td>";
echo "</tr>";
}
echo "</table>";
}
}
?>
</body>
</html>

 

EXERCISE 3: Application - Insert Record into a Table

Create a page to receive user’s input (as in the image below). Insert to the Table BOOKS all the information provided by the user in the form. Provide the appropriate message to inform the user whether the record has been successfully saved or else.

Use the BOOKSHOP database structure as above.

BOOKSHOP insert application

Comments

Popular posts from this blog

Several English proverbs and the Malay pair

Or you could download here for the Malay proverbs app – https://play.google.com/store/apps/details?id=net.kerul.peribahasa English proverbs and the Malay pair Corpus Reference: Amir Muslim, 2009. Peribahasa dan ungkapan Inggeris-Melayu. DBP, Kuala Lumpur http://books.google.com.my/books/about/Peribahasa_dan_ungkapan_Inggeris_Melayu.html?id=bgwwQwAACAAJ CTRL+F to search Proverbs in English Definition in English Similar Malay Proverbs Definition in Malay 1 Where there is a country, there are people. A country must have people. Ada air adalah ikan. Ada negeri adalah rakyatnya. 2 Dry bread at home is better than roast meat home's the best hujan emas di negeri orang,hujan batu di negeri sendiri Betapa baik pun tempat orang, baik lagi tempat sendiri. 3 There's no accounting for tastes We can't assume that every people have a same feel Kepala sama hitam hati lain-lain. Dalam kehidupan ini, setiap insan berbeza cara, kesukaan, perangai, tabia

Login JSON Android using Login Activity

I’ve been trying to release this tutorial quite a while. At last after a long hard effort. Since HttpClient is not supported any more in Android SDK 23, I have to resort to org.json.JSONObject and java.net.HttpURLConnection library to do online database with JSON. The objective of this tutorial is to log-in from a mobile client with the username and password stored in an online database facility. STEP 1: Create a new Android project, this time choose the LoginActivity .

Contact Us at blog.kerul.net

Powered by EMF HTML Contact Form

Bootstrap Template for PHP database system - MyCompanyHR

HTML without framework is dull. Doing hard-coded CSS and JS are quite difficult with no promising result on cross platform compatibility. So I decided to explore BootStrap as they said it is the most popular web framework. What is BootStrap? - Bootstrap is the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first web sites. (  http://www.w3schools.com/bootstrap/   ) Available here -  http://getbootstrap.com/ Why you need Flat-UI? Seems like a beautiful theme to make my site look professional. Anyway you could get variety of BootStrap theme out there, feel free to select here  http://bootstraphero.com/the-big-badass-list-of-twitter-bootstrap-resources/ Flat-UI is from DesignModo -   http://designmodo.com/flat/ Web Programming MyCompanyHR – PHP & MySQL mini project (with Boostrap HTML framework) Template 1: Template for the Lab Exercise. This is a project sample of a staff record management system. It has the PHP structured co

The Challenges of Handling Proverbs in Malay-English Machine Translation – a research paper

*This paper was presented in the 14th International Conference on Translation 2013 ( http://ppa14atf.usm.my/ ). 27 – 29 August 2013, Universiti Sains Malaysia, Penang. The PDF version is here: www.scribd.com/doc/163669571/Khirulnizam-The-Challenges-of-Automated-Detection-and-Translation-of-Malay-Proverb The test data is here: http://www.scribd.com/doc/163669588/Test-Data-for-the-Research-Paper-the-Challenges-of-Handling-Proverbs-in-Malay-English-Machine-Translation Khirulnizam Abd Rahman, Faculty of Information Science & Technology, KUIS Abstract: Proverb is a unique feature of Malay language in which a message or advice is not communicated indirectly through metaphoric phrases. However, the use of proverb will cause confusion or misinterpretation if one does not familiar with the phrases since they cannot be translated literally. This paper will discuss the process of automated filtering of Malay proverb in Malay text. The next process is translation. In machine translatio