无法将书籍分配给用户
Here is my project of management of library online an admin can select a student name and check from a multiple choice a book then click on the button borrow to assign this book to the student. The problem is that I have a problem in the assignment: the result shows whenever a same book is always assigned to a student until the stock is exhausted even if we have not chosen this book. Please help me guys.
here is my database 
if(!ISSET($_POST['student_no'])){
echo '
<script type = "text/javascript">
alert("Select student name first");
window.location = "borrowing.php";
</script>
';
}else{
if(!ISSET($_POST['selector'])){
echo '
<script type = "text/javascript">
alert("Selet a book first!");
window.location = "borrowing.php";
</script>
';
}else{
foreach($_POST['selector'] as $key=>$value){
$book_qty = $value;
$student_no = $_POST['student_no'];
$book_id = $_POST['book_id'][$key];
$date = date("Y-m-d", strtotime("+8 HOURS"));
$conn->query("INSERT INTO borrowing VALUES(NULL, '$book_id', '$student_no', '$book_qty', '$date', 'Borrowed')") or die(mysqli_error($conn));
echo '
<script type = "text/javascript">
alert("Successfully Borrowed");
window.location = "borrowing.php";
</script>
';
}
}
}
I have just coded all database, PHP, SQL like the solution for your problem if you want to add every book, every transaction with new borrowingId. If you want the possibility that one borrowing there is more transactions, you must use 4 table. But, try my example and we can discus about. Here is all that you need copy and test and analyze. I used case loop that basic structure you can see on w3schools.com. I'm not scripting Prepared Statements and Bound Parameters when insert, and you script that if my solution is good for you.
lib3.php
<?php
if (!isset($_GET["page"])) {
echo "student drop-down menu";
?>
<p>Student:</p>
<br>
<form action='lib3.php' method='get'>
<select name="student" onchange="this.form.submit();">
<?php
$link = mysqli_connect("localhost", "root", "", "library");
$sql = "SELECT DISTINCT studentId, studentName
FROM students";
$result = mysqli_query($link, $sql);
print "<option value='students' selected>Students</option>";
While ($row = mysqli_fetch_assoc($result))
{
if ($row["studentId"] == $_GET["student"]) {
print "<option value=" . $row["studentId"] . " selected>"
. $row["studentName"] . "</option>";
}
else {
print "<option value=" . $row["studentId"] . ">"
. $row["studentName"] . "</option>";
}
}
?>
</select>
<input type="text" name="page" hidden value="1" />
</form>
<?php
}
else {
$page = $_GET["page"];
switch ($page) {
case "1":
echo "student drop-down menu &
books drop-down-menu";
$student = $_GET["student"];
?>
<p>Student:</p>
<br>
<form action='lib3.php' method='get'>
<select name="student" onchange="this.form.submit();">
<?php
$link = mysqli_connect("localhost", "root", "", "library");
$sql = "SELECT DISTINCT studentId, studentName
FROM students";
$result = mysqli_query($link, $sql);
print "<option value='students' selected>Students</option>";
While ($row = mysqli_fetch_assoc($result))
{
if ($row["studentId"] == $_GET["student"]) {
print "<option value=" . $row["studentId"] . " selected>"
. $row["studentName"] . "</option>";
}
else {
print "<option value=" . $row["studentId"] . ">"
. $row["studentName"] . "</option>";
}
}
?>
</select>
<input type="text" name="page" hidden value="1" />
</form>
<p>Book:</p>
<br>
<form action='lib3.php' method='get'>
<select name="book" onchange="this.form.submit();">
<?php
$link = mysqli_connect("localhost", "root", "", "library");
$sql = "SELECT DISTINCT bookId, bookName
FROM books";
$result = mysqli_query($link, $sql);
print "<option value='books' selected>Books</option>";
While ($row = mysqli_fetch_assoc($result))
{
if ($row["bookId"] == $_GET["book"]) {
print "<option value=" . $row["bookId"] . " selected>"
. $row["bookName"] . "</option>";
}
else {
print "<option value=" . $row["bookId"] . ">"
. $row["bookName"] . "</option>";
}
}
?>
</select>
<input type="text" name="student" hidden value="<?php echo $student ?>" />
<input type="text" name="page" hidden value="2" />
</form>
<?php
//script
break;
case "2":
echo "student drop-down menu &
books drop-down-menu &
button borrow";
echo "student drop-down menu &
books drop-down-menu";
$student = $_GET["student"];
$book = $_GET["book"];
?>
<p>Student:</p>
<br>
<form action='lib3.php' method='get'>
<select name="student" onchange="this.form.submit();">
<?php
$link = mysqli_connect("localhost", "root", "", "library");
$sql = "SELECT DISTINCT studentId, studentName
FROM students";
$result = mysqli_query($link, $sql);
print "<option value='students' selected>Students</option>";
While ($row = mysqli_fetch_assoc($result))
{
if ($row["studentId"] == $_GET["student"]) {
print "<option value=" . $row["studentId"] . " selected>"
. $row["studentName"] . "</option>";
}
else {
print "<option value=" . $row["studentId"] . ">"
. $row["studentName"] . "</option>";
}
}
?>
</select>
<input type="text" name="page" hidden value="1" />
</form>
<p>Book:</p>
<br>
<form action='lib3.php' method='get'>
<select name="book" onchange="this.form.submit();">
<?php
$link = mysqli_connect("localhost", "root", "", "library");
$sql = "SELECT DISTINCT bookId, bookName
FROM books";
$result = mysqli_query($link, $sql);
print "<option value='books' selected>Books</option>";
While ($row = mysqli_fetch_assoc($result))
{
if ($row["bookId"] == $_GET["book"]) {
print "<option value=" . $row["bookId"] . " selected>"
. $row["bookName"] . "</option>";
}
else {
print "<option value=" . $row["bookId"] . ">"
. $row["bookName"] . "</option>";
}
}
?>
</select>
<input type="text" name="student" hidden value="<?php echo $student ?>" />
<input type="text" name="page" hidden value="2" />
</form>
<form action="add_borrowing.php" method="post">
<input type="text" name="book" value="<?php print $book; ?>" />
<input type="text" name="student" value="<?php print $student; ?>" />
<input type="submit" value="Borrow" />
</form>
<?php
if (!isset($_GET["raw"])) {
echo "";
}
else {
echo "Borrowing has been successfully added!";
}
break;
}
}
?>
add_borrowing.php
<?php
$book = $_POST["book"];
$student = $_POST["student"];
$link = mysqli_connect("localhost", "root", "", "library");
$sql = "INSERT INTO borrowing (bookId, studentId) VALUES ($book, $student)";
mysqli_query($link, $sql);
mysqli_close($link);
header("Location:lib3.php?book=$book&student=$student&page=2&raw=1");
database
-- Database: `library`
--
-- --------------------------------------------------------
--
-- Table structure for table `books`
--
CREATE TABLE `books` (
`bookId` int(11) NOT NULL,
`bookName` char(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `books`
--
INSERT INTO `books` (`bookId`, `bookName`) VALUES
(1, 'Book1'),
(2, 'Book2');
-- --------------------------------------------------------
--
-- Table structure for table `borrowing`
--
CREATE TABLE `borrowing` (
`borrowingId` int(11) NOT NULL,
`bookId` int(11) DEFAULT NULL,
`studentId` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `borrowing`
--
INSERT INTO `borrowing` (`borrowingId`, `bookId`, `studentId`) VALUES
(8, 2, 1),
(9, 1, 2),
(10, 2, 2),
(11, 2, 1),
(12, 1, 1),
(13, 2, 2),
(14, 2, 1);
-- --------------------------------------------------------
--
-- Table structure for table `students`
--
CREATE TABLE `students` (
`studentId` int(11) NOT NULL,
`studentName` char(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `students`
--
INSERT INTO `students` (`studentId`, `studentName`) VALUES
(1, 'Student1'),
(2, 'Student2');
--
-- Indexes for dumped tables
--
--
-- Indexes for table `books`
--
ALTER TABLE `books`
ADD PRIMARY KEY (`bookId`);
--
-- Indexes for table `borrowing`
--
ALTER TABLE `borrowing`
ADD PRIMARY KEY (`borrowingId`);
--
-- Indexes for table `students`
--
ALTER TABLE `students`
ADD PRIMARY KEY (`studentId`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `books`
--
ALTER TABLE `books`
MODIFY `bookId` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
--
-- AUTO_INCREMENT for table `borrowing`
--
ALTER TABLE `borrowing`
MODIFY `borrowingId` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=15;
--
-- AUTO_INCREMENT for table `students`
--
ALTER TABLE `students`
MODIFY `studentId` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
COMMIT;