CODEWITHSUNDEEP
phpmysqlsql
user520476
user520476

Reputation:

MySQL, linking data

I'm a volunteer in a hospital and I'm developing a web app. I'm a sysadmin and I know some php, html and javascript.
We have laptops we lend out to patients, they use them, go home and we get the laptop back. I've written a custom version of clonezilla which images the laptops, they are cleaned externally and then then can be given out to another patient.
A former volunteer has set up a webpage from which the nurses can submit a request for a laptop, or request one to get picked up because the patient has gone home. Its a little bit like the self-service module op TOPdesk/HP Openview.
We still use paper forms for the administration, and they want me to make it digital. There is a mySQL database with the requests from the form in it.
Now what I want is to create another table with the laptop numbers (0-79), to which the request can be linked to.
Then the volunteers can select a laptop and see all the requests for that laptop. Also there is a field for each laptops status (inclinic, notclean, clean).

This is what they want in the end:
A volunteer logs in
There is a new request for a laptop for patient X
Volunteer grabs laptop 65, sets status of laptop to "In Clinic", links the request to laptop 65 and goes to patient.
Patient uses laptop
Patient goes home, nurse puts a request in the system, laptop can be picked up at the nurse station.
Volunteer picks up laptop and starts cleaning process,
Volunteers opens page for laptop 65, sets status to Cleaned and links nurses request to laptop 65.

This is the dev version: http://activiteitenbegeleidingerasmusmc.nl/dev/rodev

SQL to get all the requests of today:

$sql = "
  SELECT * 
  FROM  `TESTFORM` 
  WHERE date(submission_date)>=date( NOW())
  ORDER BY  `submission_id` DESC 
  LIMIT 0 , 20

PHP to show them all:

   while($row = mysql_fetch_assoc($res)) 
      { 
        if( oneven($row['submission_id']) ) { $stijl="donker"; } else { $stijl="licht"; }
      echo '<tr class='.$stijl.'><td>'.$row['soortmelding'].'</td><td>'.$row['afdeling'].'</td><td>'.$row['kamer'].'</td><td>'.$row['patient'].'</td><td>'.$row['submission_date'].'</td></tr>'."\r\n";

    }

I have very little SQL knowledge, but I don't need a copy paste solution. I have read about SQL joins and such, but I have not been able to find a clear explanation. My database experience stopped at DBASE.
I hope someone can give some links or information so that I can continue the project.

Update: My question is to get some information about how I can link a request to a laptop in phpsql and show all the request history for a laptop...

Upvotes: 2

Views: 140

Answers (2)

Mel
Mel

Reputation: 6157

The first step is to separate the different entities:

  • Laptops
  • Requests
  • People
  • States

These are your tables. We 'link' them (foreign keys) using numeric ID's, because it allows us to change names without the link changing and because indexes using fixed size entities perform faster. As such, we get columns for the tables like this:

Laptops

  • laptopId
  • brand
  • manufacturerSerial
  • ... etc - w/e information about the laptop itself you want to store
  • currentState (foreign key to States.stateId)

Requests

  • requestId (primary key)
  • requestDate
  • performedBy (foreign key to People.personId)
  • laptop (foreign key to laptops.laptopId)
  • requestState (foreign key to States.stateId)
  • ... remarks or w/e information about the event you want to store

People

  • personId (primary key)
  • firstName
  • surName
  • ... etc

States

  • stateId (primary key)
  • shortName
  • comment (useful explanation for the short name)

Now some examples on how to retrieve useful information. The joining parts have extra indentation:

List the current state of the laptops

SELECT l.*, s.shortName
FROM laptops l JOIN states s
    ON l.currentState = s.stateId

Same as above, using WHERE instead of JOIN

SELECT l.*, s.shortName
FROM laptops l, states s
    WHERE l.currentState = s.stateId

Show the last 10 requests

SELECT l.laptopId, l.brand, r.*, p.firstName, p.surName, s.shortName
FROM requests r, laptops l, people p, states s
   WHERE r.performedBy = p.personId
   AND r.laptop = l.laptopId
   AND r.requestState = s.stateId
ORDER BY r.requestDate DESC
LIMIT 0,10

So in conclusion, once we have properly identified our different entities, we can link them together using foreign keys and compile information we need using these links and by selecting the proper columns from the different tables.

Upvotes: 1

Michael J.V.
Michael J.V.

Reputation: 5609

If you need explanation of what JOINS there are and how they differ - the creator of SO has published a great article (that has recently been shamelessly copied by Sitepoint) which should give you an insight about them.

Upvotes: 0

Related Questions