CREATE TABLE classics (
author VARCHAR(128),
title VARCHAR(128),
category VARCHAR(16),
year SMALLINT,
isbn CHAR(13),
INDEX(author(20)),
INDEX(title(20)),
INDEX(category(4)),
INDEX(year),
PRIMARY KEY (isbn)) ENGINE InnoDB;
INSERT INTO classics(author, title, category, year, isbn)
VALUES('Mark Twain','The Adventures of Tom Sawyer','Fiction','1876','6578484949');
INSERT INTO classics(author, title, category, year, isbn)
VALUES('Jane Austen','Pride and Prejudice','Fiction','1811', '18565645');
INSERT INTO classics(author, title, category, year, isbn)
VALUES('Charles Darwin','The Origin of Species','Non-Fiction','1856','47657583');
INSERT INTO classics(author, title, category, year, isbn)
VALUES('Charles Dickens','The Old Curiosity Shop','Fiction','1841', '4532924');
INSERT INTO classics(author, title, category, year, isbn)
VALUES('William Shakespeare','Romeo and Juliet','Play','1594', '1987655');
SELECT * FROM classics;
<?php // sqltest.php
if ($mysqli->connect_error) die("Fatal Error");
if (isset($_POST['delete']) && isset($_POST['isbn']))
{
$isbn = get_post($mysqli, 'isbn');
$query = "DELETE FROM classics WHERE isbn='$isbn'";
$result = $conn->query($query);
if (!$result) echo "DELETE failed<br><br>";
}
if (isset($_POST['author']) &&
isset($_POST['title']) &&
isset($_POST['category']) &&
isset($_POST['year']) &&
isset($_POST['isbn']))
{
$author = get_post($mysqli, 'author');
$title = get_post($mysqli, 'title');
$category = get_post($mysqli, 'category');
$year = get_post($mysqli, 'year');
$isbn = get_post($mysqli, 'isbn');
$query = "INSERT INTO classics VALUES" .
"('$author', '$title', '$category', '$year', '$isbn')";
$result = $mysqli->query($query);
if (!$result) echo "INSERT failed<br><br>";
}
echo <<<_END
<form action="sqltest.php" method="post"><pre>
Author <input type="text" name="author">
Title <input type="text" name="title">
Category <input type="text" name="category">
Year <input type="text" name="year">
ISBN <input type="text" name="isbn">
<input type="submit" value="ADD RECORD">
</pre></form>
_END;
$query = "SELECT * FROM classics";
$result = $mysqli->query($query);
if (!$result) die ("Database access failed");
$rows = $result->num_rows;
for ($j = 0 ; $j < $rows ; ++$j)
{
$row = $result->fetch_array(MYSQLI_NUM);
$r0 = htmlspecialchars($row[0]);
$r1 = htmlspecialchars($row[1]);
$r2 = htmlspecialchars($row[2]);
$r3 = htmlspecialchars($row[3]);
$r4 = htmlspecialchars($row[4]);
echo <<<_END
<pre>
Author $r0
Title $r1
Category $r2
Year $r3
ISBN $r4
</pre>
<form action='sqltest.php' method='post'>
<input type='hidden' name='delete' value='yes'>
<input type='hidden' name='isbn' value='$r4'>
<input type='submit' value='DELETE RECORD'></form>
_END;
}
$result->close();
$mysqli->close();
function get_post($mysqli, $var)
{
return $mysqli->real_escape_string($_POST[$var]);
}
?>