math problem

RomeoX

hi everybody

actually I'm creating a website that about library system for university or can be applied in any school. Actually I'm stucking in some point that I don't know what should I do exactly. I'm creating a table in mysql Books and inside this table I have

CREATE TABLE library.books (
bookid INT( 12 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
bookisbn VARCHAR( 25 ) NOT NULL ,
booktitle VARCHAR( 40 ) NOT NULL ,
bookdateofpub DATE NOT NULL ,
bookauthor VARCHAR( 30 ) NOT NULL ,
bookcomindate DATE NOT NULL ,
category VARCHAR( 25 ) NOT NULL ,
numberofcopies INT( 10 ) NOT NULL ,
numbercopiesloan INT( 10 ) NOT NULL ,
numberinhand INT( 10 ) NOT NULL ,
otherdetails VARCHAR( 60 ) NULL
) ENGINE = MYISAM ;

so I have here column that for "NUMBER OF COPIES LOANED" and "NUMBER OF COPIES IN HAND" so how can I make a calculation to be subtracted from the main column "NUMBER OF COPIES". I have another page for Students Loan book so when I apply specific student to loan book so the page of book will be related to this page.

Can anyone help me in solving this problem. Thanks

bradgrafelman

Why would you even need the 'numberinhand' column? Isn't it redundantly redundant?

dagon

random thoughts:

missing\lost\stolen\withdrawn should be added

onloan could be calculated from the loans table

RomeoX

Thanks bradgrafelman, dagon for your reply, I appreciate your answers.

I did it like that so it can be easy for user to read the number very fast instead of thinking of the Number of Copies and subtract from Number Loaned so I make it easy to read for users.

Dear Dagon
I might add only two Lost\stolen

But could you please tell me how can I do the calculation. Please help me or give me an example on the internet similar to my case. Thank you very much again

halojoy

RomeoX;10939181 wrote:
CREATE TABLE library.books (
bookid INT( 12 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
bookisbn VARCHAR( 25 ) NOT NULL ,
booktitle VARCHAR( 40 ) NOT NULL ,
bookdateofpub DATE NOT NULL ,
bookauthor VARCHAR( 30 ) NOT NULL ,
bookcomindate DATE NOT NULL ,
category VARCHAR( 25 ) NOT NULL ,
numberofcopies INT( 10 ) NOT NULL ,
numbercopiesloan INT( 10 ) NOT NULL ,
numberinhand INT( 10 ) NOT NULL ,
otherdetails VARCHAR( 60 ) NULL
) ENGINE = MYISAM ;

Maybe this, if it is the difference between numberofcopies and numbercopiesloan
you are after:
numbercopies - numbercopiesloan AS available

$sql = "SELECT xxx, xxx, xxx, numbercopies, numbercopiesloan,
 numbercopies - numbercopiesloan AS available 
FROM books WHERE bookid = $id";

//in the resulting row, you get
$athand = $row['available'];