Creating Loaner/Rental Database (Beginner/Intermediate)

[Roq]

Hey guys/gals, 

 

    I work at a fairly large corporation in the IT department and we continually loan out laptops to employees that are going to be traveling for work that don't normally use a laptop.  Until now we've used an excel document which is becoming more and more impractical to track who has our laptops and when they should be returned, and also to inventory what laptops we have available to give out. 

 

I took a little bit of time to learn (at least I think) the basics of PHP and MySQL to hopefully convert from our excel document to a database driven web application.  This will also take over tracking of our loaner monitors, desktops, and hopefully with slight modifications our database for licensed software (like Adobe Acrobat, Visio, and other common applications).  But first, I just want to get the laptop part functioning!

 

I got basic functionality working with PHP.  Essentially I can click on the laptop page and view all of our assets (laptops) in a table (Make, Model, Serial Number)...I also have a little form set up to insert new laptops into the database. 

 

I guess the problem I'm having is I know how to make a table with laptop data in it, and use PHP to insert/select from it, but I don't know how to "link" a laptop to a user that is checking it out in a different table.  I believe what I need to do is make a 3rd table with the primary keys from the "laptops" table and the "users" table.  Then SELECT from the two tables WHERE the primary key from laptops and the primary key from users matches, am I on the right track here?

 

Anyways, any help on this topic would be much appreciated. 

Thank you for your time!

[hitman6003]

Use a join statement...

 

Equipment Table:
  id
  type (laptop, monitor, etc)
  serial
  ...other identification as needed, e.g. barcode...
  short description

Employees Table:
  id
  Name
  Work Phone
  Contact Phone (Cell, etc)
  Work Location (desk position)

Loaned out equipment table:
  id
  employee_id
  equipment_id
  start_date
  return_date

 

Then use something like...

 

SELECT emp.name, equip.type, equip.serial, l.start_date, l.end_date
FROM employees emp
  LEFT JOIN loaned l ON emp.id = l.employee_id
  LEFT JOIN equipment equip ON equip.id = l.equipment_id
WHERE NOW() < return_date

 

Which would get all currently loaned out equipment.