Question Locked

PHP change value in table (1 or 0) auto by checking value of another table

0 helpful
4 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hi,

Please help me im having a nightmare.

I have a simple database that records the loaning of laptops! It consists of 4 tables:

user table - user details

Laptop table - laptop details (that are loaned)

Teacher table - teachers details (that laptops are loaned too).

Loan table - details the loans of the laptops to teachers

The structure of the tables can be viewed on attachement!!!

Heres my problem.......

When a loan is created for a laptop a form is filled out that holds the followiing data:

Loan ID
Username
Laptop Name
Teachers Name
Date
Time
Loan completed (Y/N)

When the form is filled out and a loan is arranged, the completed field is entered as N (for uncompleted loan). As soon as the laptop is returned the completed field of the loan form is changed to Y (loan complete).

The field " Laptop name" (in the loan table) is a dynamic list that pulls all the laptop names from the laptop name table.

The problem is that when a loan has been initiated and the details in loan form have been entered i want to make it so that if a NEW loan was initiated that when the user came to selecting the laptop names (from dynamic list menu) it would ONLY display the laptops that are currently NOT on loan or avaliable....!!!!!

To do this, so far i have added another field into the laptop table that displays the avaliability of the laptops by either a 1 or 0!!!!

I need to make it that when a laptop loan is entered, on the updating of the form that somehow the "completed" field on the loan table is checked to see if it is either Y or N and that if it is N (to signal loan NOT complete) the laptop table also updates the avaliability field of that specific laptop which has just been loaned!

This would then mean that once a laptop has been loaned, the laptop table would mark the avaliability of that specific laptop to 0 (in the laptop table)

....Then when the dynamic menu is used in the loan form, it would ONLY display the laptops with avaliable 1 in the field.

This would mean that the same laptop could never be loaned twice!!!

I hope i have been clear enough. Please please help me this is doing my head in now!!!

Thanks

Tom

p.s. this isk inda of what im looking for: ONLOAD update laptop name table to 1 WHERE loan table completed field = Y

4 replies

by tony hopkinson

Select Distinct laptopname from (Select laptops.*,loans.complete as joinID from laptops left join loans on loans.laptopname = laptops.laptopname) temp where JoinID is null or JoinID = 'Y'"

or similar
the above does an outer join between laptops and loans by laptop name returning all laptops that have or have not lent selects only those that aren't on loan from that.

On a big database that could get large and
Select * from laptops Where laptopname not in(select laptopname from loans where complete = 'N') might end up being faster.

Personally I'd have had a laptop transactions table and made load and return two records with different transaction codes, you could expand that for out for maintenance, scrapped, sold ....

Collected more info and attached it to the transaction id lots more scope for adding operations without chnaging the existing structure or code.

Have you considered what would happen if two people attempted to lend a laptop at the same time?.
If it's only 'you' lending them out you'll be OK, but if two people put the loan screen up at the same time...
If so you'll need to open a transaction and check to see if it's on loan before you add / update the loan entry to complete = 'N'

by twanstall

Hi Tony,

Thankyou for your help with this issue im having. I understand what you say and have worked out the code i would need (terms of table\field names) to be:

SELECT * From laptopname LEFT JOIN ON loan.LaptopName = laptopname.LaptopName WHERE loan.complete = 'N'

I have tried added this query directly into phpmyadmin and it says "choose at least one column"....Am i missing something here??

i assume this bit of code would be used where the dynamic list menu would be on the loan form???

Am i missing the echo?? What am i missing!???

Thanks again Tony it is much appreciated!! :)

by tony hopkinson

LEFT JOIN TABLE ON condition
e.g.
LEFT JOIN loan on loan.laptopname = laptopname.laptopname

This discussion is closed to new replies.