I am creating a very basic library system that will allow a user to login, register, search for a book and reserve that book. I have the login and register functions working, also the function to search the database is working. My problem arises when i want to reserve the book. When i click the reserve button it works does not show any errors however my database will not update. If any of you can help me discover where the problem/problems lie i will be very grateful.
Also a side note i know my code is not very good and is open for injections i will approach injections once i have the apllication functioning correctly.
My code is below
<?php
session_start();
if(!isset($_SESSION['username']) || (trim($_SESSION['username']) == '')) {
header("location: login.php");
exit();
}
?>
<html>
<head>
<style type="text/css">
ul li{
list-style-type:none;
}
</style>
<title>Search for and Reserve a Book</title>
</head>
<p><body>
<h3>Book Details</h3>
<p>You may search either by Book Title, Author or Book Category</p>
<form method="post" action="search_submit.php?go" id="searchform">
<input type="submit" name="submit" value="Search">
</form>
</body>
</html>
<?php
if(isset($_POST['submit'])){
if(isset($_GET['go']))
{
if(preg_match("/[^a-zA-Z0-9]+/", $_POST['name']))
{
$name=$_POST['name'];
//connect to the database
$db=mysql_connect ("localhost", "root", "") or die ('I cannot connect to the database because: ' . mysql_error());
//-select the database to use
$mydb=mysql_select_db("bookreservations");
//-query the database table
$sql="SELECT * FROM books WHERE Author LIKE '%" . $name . "%' OR BookTitle LIKE '%" . $name ."%'";
//-run the query against the mysql query function
$result=mysql_query($sql);
//-create while loop and loop through result set
while($row=mysql_fetch_array($result))
{
$ID = $row['id'];
$ISBN = $row['ISBN'];
$Author =$row['Author'];
$BookTitle=$row['BookTitle'];
$CategoryId=$row['CategoryId'];
echo '<table cellpadding ="10" border = "2">';
echo '<th> ID </th>';
echo '<th> Author </th>';
echo '<th> Book Title </th>';
echo '<th> CategoryId </th>';
echo '<th> ISBN </th>';
echo '<th> Reserved </th>';
echo '<th> Reserve Book </th>';
echo'<tr>';
echo'<td>' .$ID.'</td>';
echo'<td>' .$BookTitle.'</td>';
echo'<td>' .$Author.'</td>';
echo'<td>' .$CategoryId.'</td>';
echo'<td>' .$ISBN.'</td>';
echo'<td>' .$Reserved.'</td>';
echo'<td><form method = "post" action="reserve.php">';
echo'<input type="submit" name="submit1" value="Reserve"> </input>
</td>';
if(isset($_POST['submit1']))
{
$sql = "UPDATE books SET 'Reserved = 1' WHERE 'Reserved=0'";
}
echo'</tr>';
echo '</table>';
}
}
else
{
echo "<p>Please enter a valid search query</p>";
}
}
}
?>
a) There is a mistake in your sql. Remove those single quotes around Reserved=X and use:
"UPDATE books SET Reserved = 1 WHERE Reserved=0"
Problem: This would reserve all of the books.
b) To make this statement only reserve 1 book you need to add the id of the book to be reserved to your reserve form:
echo'<td><form method = "post" action="reserve.php?book=' . $ID. '">';
echo'<input type="submit" name="submit1" value="Reserve">
c) Use the $ID from b) to reserve only the book specified by $ID
"UPDATE books SET Reserved = 1 WHERE Reserved=0 and id = ". $_GET['book']
d) SQL injections are a serious problem within your code. Please double and triple check all sql statements. E.g. the statement from d) is affected. I will leave this as an exercise for you or simply ask another question about how to make your code secure.