How do I make the all books table lower the quantity of books when someone borrows a book?

[Imkindadumb]

Okay I must say this first I very new to php and this is my first time trying it out so please expect a dumb a question. I have 2 tables allbooks and borrowedbooks. All books has a column name quantity, so when someone borrows a book I want that quantity to go down by -1 and if the quantity is 0 to pass a message saying sorry no books or something like that. I went all over online but I cant even find a basic structure to do what i want to do.  I still have to add the part where I instruct the system to -1 from the quantity table in all books, so far its just asking it to check the number but I get errors and the page doesnt even load. If anyone could point me to page that would give me a structure on how to do this or help me code it id be very grateful.

  <tr>
            <td> Enter User ID:</td>
            <td> <input type="text" name="uid" size="48"> </td>
            </tr>
            <tr>
            <td> Enter Book ID:</td>
            <td> <input type="text" name="bid" size="48"> </td>
            </tr>
            <tr>
            <td> Enter borrowed date:</td>
            <td> <input type="date" name="bdate" size="48"> </td>
            </tr>
            <tr>
            <td></td>
            <td>
            <input type="submit" name="submit3" value="submit">
            
            </td>
            </tr>
        </table>
    </form>
    </div>
    <?php
    
    
    include("dbcon.php");
    
    if(isset($_POST["submit3"]))
    {
        if
            
            
               $sql = mysqli_query("SELECT bid, bstatus FROM Allbooks WHERE bid = '$_POST[bid]';");
                    if (!$sql) {
                        echo 'Could not run query: ' . mysql_error();
                        exit;
                    }
                    $row = mysql_fetch_row($result);

                    echo $row[0]; // 42
                    echo $row[1]; // the email value
                    
                   if $row[0] > 0{
                       
                   $uid = $_POST["uid"];
                   $bid = $_POST["bid"];
                   $bdate = $_POST["bdate"];    
        
                    $query = "insert into Borrowedbooks(uid,bid,borroweddate) values('$uid','$bid','$bdate')"; //Insert query to add book details into the book_info table
                    $result = mysqli_query($conn,$query); 
                                  
                       
                   }
                    echo" Sorry there are no more copies";

[requinix]

33 minutes ago, Imkindadumb said:

Okay I must say this first I very new to php and this is my first time trying it out so please expect a dumb a question. I have 2 tables allbooks and borrowedbooks. All books has a column name quantity, so when someone borrows a book I want that quantity to go down by -1 and if the quantity is 0 to pass a message saying sorry no books or something like that.

That's actually not a great idea. Having that counter means you have to keep that number in sync, and if something happens and interrupts the process then your quantity will be off.

If you have a table for borrowed books then it's easy to find out whether you have available books or not: compare the "all books" quantity with the number of books out being borrowed.
Not sure how the borrowed books table works... Is there also a column for the date the book is returned?

If you do something like that then you're guaranteed to always be accurate. Don't have to manage a counter for it because you already have all the information you need - just in a slightly different form and location.

What this gets at is a concept where you don't store duplicate information in different places. You know the available books with a quick query so storing the available books in a second place would be duplication. And if there's duplication then there's a problem that the two (or more) values could get out of sync. And if that happens then you have a problem because you don't necessarily know which value is correct (if either actually is) and which is incorrect.

That aside,

34 minutes ago, Imkindadumb said:

but I get errors and the page doesnt even load.

If you get errors and need help resolving them, posting what those errors are would be really helpful for us. Kinda hard to do anything if we don't know what the problem is, eh?

[Imkindadumb]

21 minutes ago, requinix said:

That's actually not a great idea. Having that counter means you have to keep that number in sync, and if something happens and interrupts the process then your quantity will be off.

If you have a table for borrowed books then it's easy to find out whether you have available books or not: compare the "all books" quantity with the number of books out being borrowed.
Not sure how the borrowed books table works... Is there also a column for the date the book is returned?

If you do something like that then you're guaranteed to always be accurate. Don't have to manage a counter for it because you already have all the information you need - just in a slightly different form and location.

What this gets at is a concept where you don't store duplicate information in different places. You know the available books with a quick query so storing the available books in a second place would be duplication. And if there's duplication then there's a problem that the two (or more) values could get out of sync. And if that happens then you have a problem because you don't necessarily know which value is correct (if either actually is) and which is incorrect.

That aside,

If you get errors and need help resolving them, posting what those errors are would be really helpful for us. Kinda hard to do anything if we don't know what the problem is, eh?

The borrowedbooks table will only hold the customers ID book ID and the day it was borrowed, which is all the details that the user puts in. Its only just to act as a record of someone borrowing that book. I just need to check the  number of books remaining in the quantity column in the all books table  to be compared and decremented when someone borrows that book. meaning i probably have to write updatee queries for the all books table. call up the cell in the quantity colum that corresponds with the ID the user puts in, check if that number is above 1 and decrement it if is or return a sorry no books message. 

The error i get is to do with the sql part it just tells me that the variable $sql is not recognised sorry I didnt add that earlier.

When you say if "something interrupts the process do you mean if someone tries to mess around and get passed the validation? If so im not really focusing on that because this is for an assignment and i am trying to do the bare minimum.  Surprise deadlines always get you in a fix you knw.

Im just looking for a structure to execute this.  If you knw any websites that has this displayed?  Does my code make any sense, its structure? 

[requinix]

49 minutes ago, Imkindadumb said:

The borrowedbooks table will only hold the customers ID book ID and the day it was borrowed, which is all the details that the user puts in. Its only just to act as a record of someone borrowing that book.

"Borrow" implies that they will eventually return the book. Are you not going to keep track of who has returned their books? I would think that's a crucial piece of information.

 

49 minutes ago, Imkindadumb said:

The error i get is to do with the sql part it just tells me that the variable $sql is not recognised sorry I didnt add that earlier.

I don't see any problematic code regarding $sql. I do see a ton of other problematic code in other places, though.

How about posting your real code and the real error message(s) you're getting?

 

49 minutes ago, Imkindadumb said:

When you say if "something interrupts the process do you mean if someone tries to mess around and get passed the validation? If so im not really focusing on that because this is for an assignment and i am trying to do the bare minimum.  Surprise deadlines always get you in a fix you knw.

Well, if you're putting in the bare minimum then I should probably do the same.

 

49 minutes ago, Imkindadumb said:

Im just looking for a structure to execute this.  If you knw any websites that has this displayed?  Does my code make any sense, its structure? 

I have critiqued your structure and no, the quantity column does not make sense.

[Imkindadumb]

35 minutes ago, requinix said:

"Borrow" implies that they will eventually return the book. Are you not going to keep track of who has returned their books? I would think that's a crucial piece of information.

 

 

when they give it back the admin will delete that record from the borrows table manually. 

[Imkindadumb]

42 minutes ago, requinix said:

 

I don't see any problematic code regarding $sql. I do see a ton of other problematic code in other places, though.

How about posting your real code and the real error message(s) you're getting?

But that is my real code its shameful i knw but thats all I know for the moment. Started self learning on this just yesterday

and the error word for word is this

Parse error: syntax error, unexpected '$sql' (T_VARIABLE), expecting '(' on line 49

[Barand]

The cause of your error ...

[jarvis]

Should line 43 not be:

 if ($row[0] > 0){

Also you're missing a closing } that would pair line 29 but could be a copy/paste error

[Imkindadumb]

28 minutes ago, Barand said:

The cause of your error ...

Thank you the page loaded after that

[Imkindadumb]

26 minutes ago, jarvis said:

Should line 43 not be:

 if ($row[0] > 0){

Also you're missing a closing } that would pair line 29 but could be a copy/paste error

Thank you the page loaded after that

[jarvis]

I could possibly be wrong, but worth checking whether:

$query = "insert into Borrowedbooks(uid,bid,borroweddate) values('$uid','$bid','$bdate')"; 

Just needs to be:

$query = "INSERT INTO Borrowedbooks (uid, bid, borroweddate) VALUES ($uid, $bid, $bdate)"; 

I don't believe you need the single quotes around the variables you're adding.

Worth tidying your code too, makes it easier to read and spot issues

[Barand]

8 minutes ago, jarvis said:

I don't believe you need the single quotes around the variables you're adding.

If they are string values, the quotes are necessary. If they are numeric values the quotes shouldn't be there

[jarvis]

Thanks @Barand - I didn't wish to speak out of term or be incorrect!

But does help the post author as I guess some fields maybe numeric.

[Imkindadumb]

32 minutes ago, jarvis said:

I could possibly be wrong, but worth checking whether:

$query = "insert into Borrowedbooks(uid,bid,borroweddate) values('$uid','$bid','$bdate')"; 

Just needs to be:

$query = "INSERT INTO Borrowedbooks (uid, bid, borroweddate) VALUES ($uid, $bid, $bdate)"; 

I don't believe you need the single quotes around the variables you're adding.

Worth tidying your code too, makes it easier to read and spot issues

Thank youu the insert into query for the borrowedbooks table works fine now its just that the validation that i needed to do before it inserts into the borrowed table that was the problem.