Auto result should set by using a formula

RomeoX

hi there

actually I'm struggle in php, I'm working on library management system and I have
a coulumn that call "Amount of fine" that if student delay the book after one month the php will show up a specific amount of $10.

I have table for LOAN book
and another table for STOREBOOK

so the name of the column that in LOANBOOK is "amountof_fine". And I want to make a formula of calculation that if student delay the book after one month set this amount of money. I think it has to be in return page because the return page will be in table with chick box to delete the student loan, Another things I want to add I think also the formula will be done from two columns:datedueforreturn AND datereturned and they are from loan table. So any help in making this formula I'll be appreciated

TABLE books
bookid     <--PK
bookisbn
booktitle
numbofcopies
numbcoploan
numbinhand
-------------------
TABLE librarybranch
libraryid    <--PK
libraryname
-------------------
TABLE usersystem
userid    <--PK
username
userpass
-------------------
TABLE studentorstaff
stid    <--PK
stname
stfamily

CREATE TABLE `library`.`bookoutonloan` (
`borrowid` INT( 12 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`stid` INT( 12 ) NOT NULL,
`bookid` INT( 12 ) NOT NULL,
`userid` INT( 12 ) NOT NULL,
`libraryid` INT( 12 ) NOT NULL,
`dateborrow` DATE NOT NULL ,
`datedueforreturn` DATE NOT NULL ,
`datereturned` DATE NOT NULL ,
`amountoffine` INT( 12 )
) ENGINE = MYISAM ;
ALTER TABLE bookoutonloan 
ADD FOREIGN KEY (stid) REFERENCES studentorstaff(stid);
ALTER TABLE bookoutonloan 
ADD FOREIGN KEY (bookid) REFERENCES books(bookid);
ALTER TABLE bookoutonloan 
ADD FOREIGN KEY (userid) REFERENCES usersystem(userid);
ALTER TABLE bookoutonloan 
ADD FOREIGN KEY (libraryid) REFERENCES librarybranch(libraryid);

bradgrafelman

I'm not sure I fully understand what you want.

Are you asking how to set a fine on a processing page (e.g. user submits a form when a person returns the book) if the due date was over a month ago? My suggestion would be to do something like:

$sql = "UPDATE bookoutonloan SET amountoffine = 10 WHERE borrowid = $id AND DATE_ADD(datedueforreturn, INTERVAL 1 MONTH) < CURDATE()";
$exec = mysql_query($sql);

if(mysql_affected_rows($exec)) {
    // due date > 1 month ago, $10 fine set
} else {
    // due date <= 1 month ago, fine unchanged
}

Note that the SQL query sets a value of 10 for the fine, so if there was any previous value in there it will be lost.

johanafm

It can be added to with

amountoffine = amountoffine + 10

Just set it to 0 when the book is inserted into bookoutonloan.

RomeoX

Thanks for your reply.

Is there any additional coding require for your code because I feel it's like something missing as I have to fill it. Could you please if you don't mind explain to me? So do I have to fill a code in the two slashes that you made, and do you think I need extra table for "Fine"?

Thanks again for your help and reply. Your help R kindly appreciated.

$sql = "UPDATE bookoutonloan SET amountoffine = 10 WHERE borrowid = $id AND DATE_ADD(datedueforreturn, INTERVAL 1 MONTH) < CURDATE()"; 
$exec = mysql_query($sql); 

if(mysql_affected_rows($exec)) { 
    // due date > 1 month ago, $10 fine set 

} else { 
    // due date <= 1 month ago, fine unchanged 
}

RomeoX

sorry johanafm I've just notice your reply after submitting my last reply.

by the way, I have this column amountoffine in Loan table not in book table and you can see this in my first topic that I putted my 5 tables. So shall I make it in the same table of loan or shall I change it to studentorstaff table.

I think I have to make this formula in the returned page but I'm not sure how it works. I don't know if it requires more and additional of coding or just paste it as it is.

Any further suggestion in coding will be highly appreciated. Thanks to all.

johanafm

As I see it, the fine is in the right place allready. The fine is after all not related to a specific book, but rather a specific loan of said book, so it belongs in the loan table.

For the code posted by bradgrafel there is no need to insert additional code, unless you want to provide some kind of feedback to the user since the SQL statement allready updated the table.

But, with this approach, the table is updated when a book is actually returned (and you find out the borrowi_id). So, for a book which is never returned, there would be no fine added. Which works fine as well, but it does mean that you can't see what books (should) have fines on them through SELECT stuff FROM bookoutonloan WHERE amountoffine > 0. And personally I find this counterintuitive.

I'd rather
1. change datereturned to allow null values. Else, how will you represent a book which has not yet been returned?
2. Change bradgrafelmans's SQL statmeent to

UPDATE bookoutonloan SET amountoffine = amountoffine + 10 WHERE  DATE_ADD(datedueforreturn, INTERVAL 1 MONTH) < CURDATE() AND datereturned IS NULL";

and run this code every day at midnight.

But, if you decide to stick with his approach, then yes, you execute that statement when a book is returned. You will need to get the information regarding the lates loan of this book. For the above, all you need is the bookid, but you probably want to display other information to the user, such that the date when it should have been returned etc. Thus

"SELECT stuff FROM bookoutonloan WHERE bookid = $bookid ORDER BY datedueforreturn DESC LIMIT 1"

RomeoX

hi there

actually I have set the code according to my page and I got an error. Could anyone please fix it and check where is the problem, I'm going to put two slashed in front of the error.

<?php
include 'functions.php';
$submit = $_POST["checkfine"];
$bookid = $_POST["bookid"];
$returned = $_POST["returned"];

if ($submit)
{

$queryreg = mysql_query("UPDATE bookoutonloan SET datereturned WHERE datereturned = $returned"


//It says in the browser that the below code has problem I'm not sure exactly because PHP point to a line and the problem from something else.
$query = mysql_query("UPDATE bookoutonloan SET amountoffine = amountoffine + 10 WHERE  DATE_ADD(datereturned, INTERVAL 1 MONTH) < CURDATE() AND datereturned IS NULL");


}
?>

<?php

$id = $_POST["c"];
foreach($id as $v)
{
$r= mysql_query("delete FROM bookoutonloan WHERE borrowid='$v'");
}

$queryupdate = mysql_query("UPDATE books SET numbcoploan =(numbcoploan-1), numbinhand =(numbinhand+1) WHERE bookid = '$bookid'");
?>

johanafm

The line you marked with your comment is syntactically correct, while the logic is flawed.

Missing ;
Also, the syntax for update is: SET field = value. Missing a value.

$queryreg = mysql_query("UPDATE bookoutonloan SET datereturned WHERE datereturned = $returned"

The code below translates to
DATE_ADD(null, whatever) -> always returns null.
And null compared to date is false
null < CURDATE() -> false
null > CURDATE() -> false

UPDATE ... WHERE
	DATE_ADD(datereturned, INTERVAL 1 MONTH) < CURDATE() AND
	datereturned IS NULL

So, it should rather be

DATE_ADD(dateofduereturn, INTERVAL 1 MONTH) < CURDATE() AND
datereturned IS NULL

johanafm

Hmm, actually that might still not be what you're looking for. With that code, you don't add a fine until one additional month past the date of due return. But, it could be what you are after.

RomeoX

hi johanafm, Thanks for your reply, guidance and your patience with me.

actually I mentioned that this code is making an error that the error from something else it was the line before this code missing bracket close )

anyway, this code I tried to play with it but with no avail, that doesn't do any thing at all. hopefully if you could find somehow to fix.

$query = mysql_query("UPDATE bookoutonloan SET amountoffine = amountoffine + 10 WHERE  DATE_ADD(datereturned, INTERVAL 1 MONTH) < CURDATE() AND datereturned IS NULL");

you know, I'm using here "datereturned" instead than "dateofduereturn" I think it's redundancy because I made in the table three columns as following:

`dateborrow` DATE NOT NULL ,
`datedueforreturn` DATE NOT NULL ,    <-- I'll not use this column 
`datereturned` DATE NOT NULL ,   <--this column I tried to change it to null but doesn't work as well.

actually the code doesn't work at all, I don't know what's the problem. You know I made a page for returning a book that has a table and when I want to add the date for the student when he/she returns the book I have a "Text Field" that I enter the date and update the system and when the system catch the datereturned I wanted to add $10 once if the datereturned greater than 1 month and so on even if the book took for 3 months it will add $30, like that. By the way, I uploaded my files so you can see it and if needs change hopfully that you modify it.

By the way, open returnbook2.php and then returnbook6.php and for the date of return try to modify the value of date from myphpadmin to make the difference and see if the code of adding 10 dollar working or not. Thanks a lot.

RomeoX

hi there, I just want to say something that I'll be a millionaire because of this statement of update

UPDATE bookoutonloan SET amountoffine = (amountoffine + 10) WHERE DATE_ADD(dateborrow , INTERVAL 31 DAY )> dateborrow AND borrowid = 2



UPDATE bookoutonloan SET amountoffine = (amountoffine + 10) WHERE DATE_ADD(dateborrow , INTERVAL 1 MONTH )> datereturned AND borrowid = 3

Actually I used these statements in phpmyadmin in mysql query and I found that this statements work fine but every time I execute these statement what ever if the person borrow the book for 2 days it will increase $10 what ever😃 it doesn't care of anyone so I'll be millionair but it's a problem, it's not what I want, I want this statement to work that if the one month or 30 days passed it will increase the amount of fine $10 and if the person took the book for example 3 months it will incrase the amount to $30. I hope if anyone can help I really need this statement to work or any other statement will do the job. Thanks

bradgrafelman

WHERE DATE_ADD(dateborrow , INTERVAL 31 DAY )> dateborrow

Er... wait. You're testing to see if 31 days past 'dateborrow' is greater than 'dateborrow' ?

Something tells me you mistyped one of those column names there... otherwise you might as well say "WHERE 1 = 1" 😉

RomeoX

hi bradgrafelman, nice to see you again in my thread. Acutally there isn't any mistyped. Check my reply in No 10, and 1 you will see my tables again but I said that I'm not going to use datedueforreturn becuase no benefit of using it and I'm going to use datereturned so once I enter the date in text field for the date and the statement will check if the date more than one month it will add $10 if not it will leave the Fine 0. Or if the calculation will be only by sing the dateborrow column so if the day of borrow more than one month it will add a Fine of $10. I hope you get my point, please if you know how to do it please guide me. Thanks again for your reply.

dateborrow DATE NOT NULL ,
datedueforreturn DATE NOT NULL , //I'll cancel this coloumn from my DB
datereturned DATE NOT NULL ,

RomeoX

sorry again

this is the code that I used

UPDATE bookoutonloan SET amountoffine = (amountoffine + 10) WHERE DATE_ADD(dateborrow , INTERVAL 1 MONTH )> CURDATE() AND borrowid = 4

that I want to check is "databorrow" larger than today date I mean more than one month, if it's more than one month set $10 if not leave the column $0. But the code is adding $10 so it doesn't restrict and realize that the one month didn't reached.

so if I have "dateborrow" = 2010-01-12
it should not add $10

if the "dateborrow" =2009-12-04
it should add $10

and so one even if the "dateborrow" =2009-08-10
it should be now $50 so every month it will increase $10 so it will be now $50

You know I think no need of these columns to use "datereturned" & "datedueforreturn" because I think it's useless or what do you think?

I hope you get what I want and clear. Thanks

RomeoX

hello, I'm really waiting for your answers. Is it really difficult Q for you guys so you can't give me a solution. Please

johanafm

This code does what you're looking for. Just have to replace the manually entered date with your date field. And put this into your update (strip the select...)

SELECT 10 * (FLOOR(
		DATEDIFF(CURDATE(), 
				DATE_ADD('2009-11-20' , INTERVAL 1 MONTH)
		) / 30
) + 1)

RomeoX

Thanks a lot for your helping.

actually I tried to implement your code in my page and I did like the below code

<?php
include 'functions.php';
$submit = $_POST["checkfine"];
$bookid = $_POST["bookid"];
$returned = $_POST["returned"];
$datereturned = $_POST['datereturned'];
$currentdate = date(y-m-d);


$id = $_POST["c"];
foreach($id as $v)
{

$query = mysql_query("UPDATE 10 * (FLOOR(DATEDIFF(CURDATE(), DATE_ADD('datereturned' , INTERVAL 1 MONTH)) / 30) + 1)");



die();

}

?>

Actually I didn't know how can I implement like this code within your code. And which column should I use dateborrow or datereturned instead of the number of date in your code.

UPDATE bookoutonloan SET amountoffine = (amountoffine + 10) WHERE DATE_ADD(dateborrow , INTERVAL 1 MONTH )> CURDATE() AND borrowid = $v

becuase how can I update specific borrowid unless I have to define the borrowid. By the way I'm providing in my post 10 my two pages as attachment so you can check it if it's working or not because for me didn't work and didn't do any thing, it says there is an error. Please Please only this thing that I want nothing else. You are only the one who is trying to help me. Thanks a lot johanafm. Waiting for your reply

johanafm

My query was just to get the result for the fine field. So, in the query you allready had, fineamount = whatever_the_expression_was, should instead be fineamount = the_expression_in_my_query.

And for the date, that would be the date borrowed.

RomeoX

hi there,

Could you please look to this coding if you don't mind. When I executed it writes NULL in the amountoffine so I don't know. I think it's better if you could fix my code here to retype it with a fix. Thanks johanafm a lot for your patience. I feel very sorry.

<?php
include 'functions.php';
$submit = $_POST["checkfine"];
$bookid = $_POST["bookid"];
$returned = $_POST["returned"];
$datereturned = $_POST['datereturned'];
//$currentdate = date('y-m-d');
$amountoffine = $_POST['amountoffine'];
$borrowid = $_POST['borrowid'];

$id = $_POST["c"];
foreach($id as $v)
{ 


$newquery = ("UPDATE bookoutonloan SET amountoffine = 10 * (FLOOR(DATEDIFF(CURDATE(), DATE_ADD('datereturned' , INTERVAL 1 MONTH)) / 30) + 1) WHERE  borrowid = '$borrowid'");

}
?>

johanafm

RomeoX;10940612 wrote:

$newquery = ("UPDATE bookoutonloan SET amountoffine = 10 *

(FLOOR(
	DATEDIFF(CURDATE(), 
			DATE_ADD('datereturned' , INTERVAL 1 MONTH)
	) / 30) + 1) WHERE  borrowid = '$borrowid'");

First error is the string literal 'datereturned'. It's a string and not a date, so DATE_ADD generates NULL, then DATEDIFF generates null, etc until 10 * null = null is assigned to amountoffine.
Strip the single quotes and you have a field from the table instead.

Second problem is that it is datereturned, which is still null, and even if datereturned was set just prior to this, it would be the wrong date. You have to use the dateborrowed, since this is what you want to check for 1 month since, 2 months since...
i.e.

DATE_ADD(dateborrowed , INTERVAL 1 MONTH)