set value according to date

[baabdaam]

Hi there,

Actually my problem is I want to get the date difference if date return of book more than one month add $10 if not leave the amountoffine to $0 and if the loaner took the book and return it after 3 months I want the code to add by monthly $10 so after 3 months I want the result to be $30. Here is the code

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

if ($datereturned > 30 Days))
{
$query = mysql_query("UPDATE bookoutonloan SET amountoffine = (amountoffine + 10) WHERE DATE_ADD(datereturned , INTERVAL 1 MONTH )> CURDATE() AND borrowid = '$borrowid'");
die("The Record of Fine Has Been Updated");

}
?>

So I want to add an amount of Fine like if someone loan a book and return it after 1 month I want to add $10 in the column of amountoffine actually I stuck here. This is my Table in mysql as well

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 ;

I want to check is "databorrow" larger than one month, if it's more than one month set $10 if not leave the column $0. But the code is adding $10 every time that I refresh the page and also there is an error in this code when I used IF condition.

so if I have "dateborrow" = 2010-01-12
it should not add $10 in case if today is 2010-01-26

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

I hope you get what I want and clear. Thanks

[baabdaam]

Can any one help me. I really need someone to fix the code. I just need someone to fix the update code that not add $10 dollars when I press the button that related to this code so it will only add $10 for people who return the book more than one month. and if the person return the book after three months so I want the book to add $30. I hope really someone can help me in this code. I don't have any other question so please help me in this and I'll give him/her the complete system as a gift.

Thanks a lot

Before looking at this - you talk about months, but are using increments of 30 days. Which one do you mean? For example if book taken out and should be returned within a calendar month: 2010-02-14 to 2010-03-14 or 30 days 2010-02-14 to 2010-02-16. This may be a triviality for you, but could mean the difference between a $10 fine or a 'thank you' to a customer.

Once you decide on the increment (1 month/3 months/30 days/90 days etc) - the php code or mysql query to determine this will become clearer.

Both formats should be easy to deal with, although you can imagine the problems with 2011-01-31 to 2011-03-01 - is this a fine or not? 30 days (no) or calendar month (yes).

Sorry for being picky.

BTW: your table structure looks good. Perhaps you could have a 'paid' column (assume that you only accept full payments - otherwise it gets tricky or you may need a dedicated payments table). This field could be a tinyint (used as boolean) - defaulted to 0 (false).

PS. Is this an assignment or coursework?

[baabdaam]

First of all thanks a lot my dear for reply to my thread, your passing and replying is lighten my thread. Second thing, I'm taking a course in php and it's my first time dealing with this language and I want to do something in php to learn it well so I picked up to do a library system to loan and return book. Actually I'm trying to develop a big system but as I'm in the beginning of php so I don't know about the syntax and keywords that for this language and that's why I'm seeking for a help. I feel that this language is not easy but I'm trying to do my best. I did a lot of things which I'm proude of that but the problem I stuck in this system I even trying to improve it within one month and give it to this forum so people can learn from it and practice in php.

Now what I want is, check this column is greater than today by 1 month or not, if greater one month add $10 in the borrowid. So I'm trying to make it easy for me not to have different table that dedicated for students because it seems to my much difficult unless if you can make it for me that will be appreciated from you. And as I'm not professional in php so I'm trying to do small thing like it will add the amount of fine in the borrowid and once the student returned the book either he has the pay the amount of money ex, $10 = 1month or $20 = 2 months so the system can calculate the date difference and put the amount in the column of amountoffine which is in the same that table that I mentioned in my first post. Please if you can do something like that it will help me a lot because I did my effort but with no avail

I tried to execute this query direct in the mysql query but I found that it add $10 every time that I executed and even the date less than a month or more than one month it will add which is not correct

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

OK, now I know the score - you have made a considerable effort yourself - I'll give you some help, but I stress that I'm not an expert, just a hobbyist.

Your query looks pretty good, but unfortunately, you have an increment of 'add $10' to the fine every time the script is run. A better way, in my opinion would be to set the fine increment as a php variable ($fine = 10). Then you can use this type of query:

"UPDATE table SET fine = $fine * (multiplier) WHERE id = $id"

The (multiplier) getting the date difference between duedate and returndate ( calculate the number of months late).

$query = mysql_query("UPDATE [B]table[/B] SET [B]fine[/B] = if((period_diff(date_format(now(),"%Y%m"),date_format([B]duedate[/B],"%Y%m")) < 1) AND (date_format(now(),"%d") > date_format([B]duedate[/B],"%d")) ,0,(period_diff(date_format(now(),"%Y%m"),date_format([B]duedate[/B],"%Y%m")) + if(date_format(now(),"%d") > date_format([B]duedate[/B],"%d"),1,0))*[B]$fine[/B]) WHERE [B]id[/B] = [B]$id[/B]");

Obviously you'll have to substitute the table name and fieldnames - fine / duedate / id for your own.

This query looks complicated, but I'll run through the idea:

First check to see how many months are between the current date and the due date (expected date) - if the number of months is 0 or less AND number of days between dates is more than 0 - if not THIS IS NOT A FINE!

Otherwise calculate the number of months and add the part month late -
(number of whole months + part month) X fine per month

Come back if you don't understand. It's very late here and my brain has overheated.

//EDIT - sorry just realised - this needs a 'returned' and 'paid' fields to stop the 'paid' and the 'returned' records from being calculated every time the script is run

Just use a "WHERE returned IS NOT NULL AND paid = 1" or something similar.

[baabdaam]

Thanks Mr.Ardav for your reply. This is your kidness to reply and assist me. So really thanks a lot.

I modified your statement according to my table like the one below but it says there is an error, actually I tried to understand it.

<?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)
{ 
	$query = mysql_query("UPDATE bookoutonloan SET amountoffine = if((period_diff(date_format(now(),"%Y%m"),date_format(datereturned,"%Y%m")) < 1) AND (date_format(now(),"%d") > date_format(datereturned,"%d")) ,0,(period_diff(date_format(now(),"%Y%m"),date_format(datereturned,"%Y%m")) + if(date_format(now(),"%d") > date_format(datereturned,"%d"),1,0))* $amountoffine) WHERE borrowid = $borrowid"); 

}
?>

you said I have to add two new field in my table like this one as well

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 ,//This column I'm going to delete
`datereturned` DATE NOT NULL , 
`returned`  INT() NULL,
`paid` INT() NULL, 
`amountoffine` INT( 12 ) ) ENGINE = MYISAM ;

So here I added in two columns as you said `returned`, `paid`.
where shall I write this line as well
WHERE returned IS NOT NULL AND paid = 1

I'm very sorry sir for bothering. Have a good day and hope that you are fine.

Ok will get back to you later. Have to go to work now

Sorry I had a '>' instead of a '<'!!

$fine = 10;

$q = mysql_query("UPDATE bookoutonloan SET amountoffine = if((period_diff(date_format(now(),"%Y%m"),date_format(datedueforreturn,"%Y%m")) < 1) AND (date_format(now(),"%d") < date_format(datedueforreturn,"%d")),0,(period_diff(date_format(now(),"%Y%m"),date_format(datedueforreturn,"%Y%m")) + if(date_format(now(),"%d") > date_format(datedueforreturn,"%d"),1,0))*$fine) WHERE datedueforreturn < now() AND returned = 0 AND paid = 0");

This is used with:

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 ,//This column I'm going to delete !!! But this is the one I'm using to calculate late fees
`datereturned` DATE NOT NULL ,  //strictly - I don't really need this - just a returned - but if you want this, you don't need returned field - one or the other - see below for example
`returned`  INT() NULL, //change to tinyint(1) default (0)
`paid` INT() NULL, //change to tinyint(1) default (0)
`amountoffine` INT( 12 ) ) ENGINE = MYISAM ; //do you really need 12 integers? That sounds crazy. Is it possible that you could charge 11.50 per month? In which case, your datatype could be wrong (not integer).

If you want to use the datereturned as opposed to returned:

$q = mysql_query("UPDATE bookoutonloan SET amountoffine = if((period_diff(date_format(now(),"%Y%m"),date_format(datedueforreturn,"%Y%m")) < 1) AND (date_format(now(),"%d") < date_format(datedueforreturn,"%d")),0,(period_diff(date_format(now(),"%Y%m"),date_format(datedueforreturn,"%Y%m")) + if(date_format(now(),"%d") > date_format(datedueforreturn,"%d"),1,0))*$fine) WHERE datedueforreturn < now() AND datereturned IS NOT NULL AND paid = 0");

This works for me as a SQL query. COme back if problem.

[vaultdweller123]

I'm not an expert, just a hobbyist.

dont believe him... hes just too humble to admit it.hes an expert in PHP... you hear me... E.X.P.E.R.T.....your soooo lucky ardav help you.... hes the most knowledgable guy i know here in PHP.

[vaultdweller123]

and let's just say im a big fan...

[baabdaam]

Thanks a lot Mr.Ardav for this contribution with me.

By the way, I caught a flu with fever today but I'll come back to you if I face any problem. I'll send you pm to tell you that I asked a question here so you can know that. Execuse me, could you tell me if you don't mind, is there a best book talking about these things so I can get improve in php and mysql.

May God bless you and I'll never forget this helping from you forever.

I don't know of any modern books as I bought some PHP5/MySQL5 a few years ago, so they're probably out of date /possibly out of print. I generally peruse websites for the more advanced stuff these days - but I prefer books for learning the basics until I can fly solo.

However O'Reilly, Sitepoint and Wrox generally have good books. Amazon isn't a bad stop either.

Hope you get better soon (flu's bad!)

[vaultdweller123]

just google it away dude! were in the new era now were not in medieval times... ahahaha