2. PHPRunner General questions
  3. [SOLVED]  History of lending out library DVD's
This topic is locked
[SOLVED]

 History of lending out library DVD's

 		3/4/2015 9:33:45 AM
PHPRunner General questions
S
 shoppy author

Hi all,
I have a database with DVD's that I use for keeping track of the ones that are lend out.

Everything works but now I like to have a history of the books that have been lend out and returned.
I like to see witch DVD is lend out the most and also to see the average time it was land out.
Can anyone help with this?
S
 		shoppy author 3/4/2015

Let me explain a little bit more.
I have one table with all the DVD's: title, date of lending out and date of return.

But when the DVD is returned and it must be lend out again the same record changes.

I like to store the previous dates of lending out so I get a list of all the times this DVD is lend out.
is something like this possible?
Sergey Kornilov admin 3/4/2015

Not really sure what causes the trouble here.
You have a table with DVDs/Books. You need another table to store all items movements i.e. date/time out, date/time in etc. DVD is rented - add a new record to history table with setting time out equals to th current time. DVD is back - update that record setting time in to current time. As simple as that.
S
 		shoppy author 3/4/2015



Not really sure what causes the trouble here.
You have a table with DVDs/Books. You need another table to store all items movements i.e. date/time out, date/time in etc. DVD is rented - add a new record to history table with setting time out equals to th current time. DVD is back - update that record setting time in to current time. As simple as that.


Haha, not for you maybe.
So now I made a Table with the name "History" and with the same fields as the DVD table.

If I edit a DVD field and put in a date in the 'retuned' field I want the record to be copied to the History table.

That record must keep restored.

How do I do that.
Sergey Kornilov admin 3/4/2015

Try to read my answer one more time. You should not be making any changes to the main DVD table. Use it as a reference, as a lookup tables. All movement should stored in history table only.
S
 		shoppy author 3/5/2015



Try to read my answer one more time. You should not be making any changes to the main DVD table. Use it as a reference, as a lookup tables. All movement should stored in history table only.


Oke! I misread your explanation. Thanks for the tip Sergey!
So now I have the following situation.

  • I use the DVD table to put in the date of lending out.
  • In "After record updated" a copy is made of that record to the "History" table.
  • When a DVD is returned I put the date of return in the "History" table.
    This works like a charm, but now I have the following problem:

    I made a View of the DVD table to have an overview of the DVD's that are out en in.

    This view doesn't work any more because the return date is missing.
    Is it not possible to put an 'if' statement in the Event tab of the DVD table with something like 'If the return field id < then 0 then make a copy of the record in History'?
    FOUND IT !!!

global $conn,$strTableName;
if ($values["Returned"]>"" || $values["Returned"]>0)

{

$strSQLSave = "INSERT INTO _History (Name, Number, Lendout, Returned, Person) SELECT Name, Number, Lendout, Returned, Person FROM ".$strTableName." where ".$where;

db_exec($strSQLSave,$conn);
header("Location: _DVD_list.php?a=return");

exit();

}
else
{

//********** Redirect to another page ************

header("Location: _DVD_list.php?a=return");

exit();

}
S
 		shoppy author 4/2/2015

Ok, next 'problem'.
Right now only the returned DVD's are visible in the _History table.

This table is printed out as a list (already have a working script for this).

But now we want also the not yet returned DVD copied to _History and that record needs to be replaced when the DVD is returned.
So, as an example:

  1. A DVD is lend out, the record is copied to _History.
  2. This DVD is returned and a return date is entered, now the record has to replace the former record.
    Is something like this possible?
    I fixed in another way!!!