PHP MySQLi Help

[Nima Tann]

Hey everyone, firstly the spoilers are bunch of codes so if you aren't interested in programming I suppose it isn't your thread. Now as we got it out of the way

So I am having some trouble in one of my projects. I am trying to do a Library Management System where there are two tables named students and books, in a database named libmanagementsystem. In my codes I can update my tables, delete from them, and insert new data. And in borrow.php I am trying to do something like this:

The user clicks Borrow, and it gives them a select box where there are all of the book names, user selects one of them and presses submit.

Pressing submit, it will join the two rows and shows it. I've tried to do it by creating two variables named book in students and bookid in books and tried to make them equal to each other.

For example.

Let's say there is a student1, whose ID is 123
And there is a book name book1, whose bookid is 1234,author is abc

So it is going to look like this

123 student1 1234 book1 abc

How can I do this, I tried everything but failed miserably. Also I've put the codes here while working on them so there can be some errors.

Thank you! Also I am using wampserver 2.5

Spoiler

StudentSystem.php

<form action="StudentSystem.php" method="post">
<p><t><b>Add a Student </b></t></p>
<p>id: <input type="text" name="id" /></p>
<p>First Name: <input type="text" name="firstname" /></p>
<p>Last Name: <input type="text" name="lastname" /></p>
<p>Book ID: <input type="text" name="book" /></p>
<p><input type="submit" name="Add" value = "Add" /></p>
</form>

<?php

include ("connection.php");

if(isset($_POST['Add'])){
$id=$_POST['id'] ;
$firstname= $_POST['firstname'] ;
$lastname=$_POST['lastname'] ;
$book=$_POST['book'] ;
mysqli_query($conn,"INSERT INTO students(cnt,id,firstname,lastname,book)
VALUES (NULL,'$id','$firstname','$lastname','$book')");

}






$query = "SELECT * FROM students";
$result = mysqli_query($conn,$query);
echo "<table border=1>";
echo "<tr>";
echo "<th>ID</th>";
echo "<th>First Name</th>";
echo "<th>Last Name</th>";
echo "<th>Book ID: </th>";
echo "</tr>";

while($record = mysqli_fetch_array($result)) {

echo "<tr>";
echo "<td>" . $record['ID'] . "</td>";
echo "<td>" . $record['firstname'] . "</td>";
echo "<td>" . $record['lastname'] . "</td>";
echo "<td>" . $record['book'] . "</td>";
echo "<td> <a href =\"update.php?cnt=" . $record['cnt'] . "\">Edit</a></td>";
echo "<span></span>";
echo "<td> <a href =\"delete.php?cnt=" . $record['cnt'] . "\">Delete</a></td>";
echo "<td> <a href =\"borrow.php?cnt=" . $record['cnt'] . "\">Borrow</a></td>";
echo "</tr>";

}
echo "</table>";

mysqli_close($conn);

?>

Spoiler

BookSystem.php

<form action="BookSystem.php" method="post">
<p><t><b>Add a Book </b></t></p>
<p>Book ID: <input type="text" name="bookid" /></p>
<p>Book Name: <input type="text" name="bookname" /></p>
<p>Book Author: <input type="text" name="author" /></p>
<p><input type="submit" name="Add" value = "Add" /></p>
</form>

<?php

include("connection.php");

if(isset($_POST['Add'])){
$bookid=$_POST['bookid'] ;
$bookname= $_POST['bookname'] ;
$author=$_POST['author'] ;
mysqli_query($conn,"INSERT INTO students(cnt1,bookid,bookname,author)
VALUES (NULL,'$bookid','$bookname','$author')");

}


$query = "SELECT * FROM books";
$result = mysqli_query($conn,$query);
echo "<table border=1>";
echo "<tr>";
echo "<th>Book ID</th>";
echo "<th>Book Name</th>";
echo "<th>Author</th>";
echo "</tr>";

while($record = mysqli_fetch_array($result)) {

echo "<tr>";
echo "<td>" . $record['bookid'] . "</td>";
echo "<td>" . $record['bookname'] . "</td>";
echo "<td>" . $record['author'] . "</td>";
echo "<td> <a href =\"update1.php?cnt1=" . $record['cnt1'] . "\">Edit</a></td>";
echo "<span></span>";
echo "<td> <a href =\"delete1.php?cnt1=" . $record['cnt1'] . "\">Delete</a></td>";

echo "</tr>";

}
echo "</table>";


mysqli_close($conn);

?>

Spoiler

connection.php

<?php

$servername = "localhost";
$username = "root";
$password = "";
$dbname = "libmanagementsystem";

$conn = new mysqli($servername, $username, $password,$dbname);

?>

Spoiler

update.php

<?php

include("connection.php");


$q = "SELECT * FROM students WHERE cnt = $_GET[cnt]";
$result = mysqli_query($conn,$q);
$record = mysqli_fetch_array($result);


?>


<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
<p><t><b>Edit the user: </b></t></p>
<p>ID: <input type="text" name="id" value = "<?php echo $record['ID']; ?>" /></p>
<p>First Name: <input type="text" name="firstname" value = "<?php echo $record['firstname'] ; ?>" /></p>
<p>Last Name: <input type="text" name="lastname" value = "<?php echo $record['lastname'] ; ?>" /></p>
<p>Book ID: <input type="text" name="book" value = "<?php echo $record['book'] ; ?>" /></p>
<p><input type="hidden" name="cnt" value = "<?php echo $_GET['cnt'] ; ?>" /></p>
<p><input type="submit" name="submit" value = "Edit" /></p>
</form>

<?php

if(isset($_POST['submit'])){

$u = "UPDATE students SET `ID` = '$_POST[id]', `firstname` = '$_POST[firstname]', `lastname` = '$_POST[lastname]',`book` = '$_POST[book]' WHERE cnt = $_POST[cnt]";
mysqli_query($conn,$u);
header("Location: StudentSystem.php");

}

?>

Spoiler

update1.php

<?php

include("connection.php");


$q = "SELECT * FROM books WHERE cnt1 = $_GET[cnt1]";
$result = mysqli_query($conn,$q);
$record = mysqli_fetch_array($result);


?>


<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
<p><t><b>Edit the books: </b></t></p>
<p>Book ID: <input type="text" name="bookid" value = "<?php echo $record['bookid']; ?>" /></p>
<p>Book Name: <input type="text" name="bookname" value = "<?php echo $record['bookname'] ; ?>" /></p>
<p>Author: <input type="text" name="author" value = "<?php echo $record['author'] ; ?>" /></p>
<p><input type="hidden" name="cnt1" value = "<?php echo $_GET['cnt1'] ; ?>" /></p>
<p><input type="submit" name="submit" value = "Edit" /></p>
</form>

<?php

if(isset($_POST['submit'])){

$u = "UPDATE books SET `bookid` = '$_POST[bookid]', `bookname` = '$_POST[bookname]', `author` = '$_POST[author]' WHERE cnt1 = $_POST[cnt1]";
mysqli_query($conn,$u);
header("Location: BookSystem.php");

}

?>

Spoiler

borrow.php

<?php

include("connection.php");


$q = "SELECT * FROM books";
$result = mysqli_query($conn,$q);
$record = mysqli_fetch_array($result);


?>

<select>
<p>Select the book that the student borrows</p>
<option value= "<?php echo $record[1] ; ?>"> <?php echo $record[2];?></option>
<p><input type="hidden" name="cnt" value = "<?php echo $_GET['cnt'] ; ?>" /></p>
<p><input type="submit" name="submit" value = "Borrow" /></p>
</select>

<?php

if(isset($_POST['submit'])){

$u ="UPDATE books SET `bookid` = 1 WHERE cnt = $_POST['cnt]";
mysqli_query($conn,$u);
header("Location: BookSystem.php");
}

?>

Spoiler

delete.php

<?php
include ("connection.php");

mysqli_query($conn,"DELETE FROM students WHERE cnt = $_GET[cnt]");
header("Location: StudentSystem.php");

?>

Spoiler

delete1.php

<?php
include ("connection.php");
 
mysqli_query($conn,"DELETE FROM books WHERE cnt1 = $_GET[cnt1]"); 
header("Location: StudentSystem.php");
 
?>

 

[Audren Sykes]

Not remotely close to an expert in coding, but I have some experience. Only some with SQL though.

From what you're describing, I'd logically set the book table to have a "borrower" field, then use the student's id as the input there (assuming the ID is the primary key for students). That would allow you to search the books table for <criteria> (and hence, the students table) and output from there. For example, if you want to display the book that was just borrowed, you'd save that book's id in a temporary variable then use that variable to pull the information from the tables and join it where your output code is.

Not sure if that helps, I can expand on the idea if necessary.