Logic for my php project---need urgent assistance

[EdwinMurgoh]

I am working on a blood bank mobile app.I seem to have stalled at the logic for matching donors to their respective blood_groups that can donate to them. Curently i have a database called test and 3 tables: health_profile, contact_info and register.

register has (id,firstname,lastname,email,username and password)

health_profile has (id,username,blood_group,age and weight)

contact_info has (username, phone, address, town).

 

I need the php script to get the value of blood_group of the current user and and  display the contact_info all the other users in the database that can donate to them.

 

 

O-  can be donated  by O-

O+  can be donated by  O+ and O-

A-  can be donated by A- and O-

A+ can be donated by   A+ A- O+ O-

B- can be donated by B- and O-

B+ can be donated by B+ B- O- O+

AB- can be donated by AB- B- A- O-

AB+ can be donated by AB+ AB- B+ B- A+ A- O+ O-

 

 

 

 

[scootstah]

You need to use SQL JOIN's.

 

Your database schema needs some tweaking as well. You should not be storing multiple username fields across multiple tables. Instead, use a column to store the primary key of the related table. Also, contact_info should have a primary key.

 

register has (id,firstname,lastname,email,username and password)
health_profile has (id,user_id,blood_group,age and weight)
contact_info has (id, user_id, phone, address, town).

Now you can make user_id a foreign key associated with register.id and perform efficient queries.

[Barand]

Looks like you need another table to store

bloodGroup | compatibleGroup
-----------+----------------
   O-      |      O-
   O+      |      O+
   O+      |      O-
   A-      |      A-
   A-      |      O-
   A+      |      A+
   A+      |      A-
   A+      |      O+
   A+      |      O-
etc

[scootstah]

I figured he'd be doing that with application logic.

[Barand]

You need that to query for possible donors

[scootstah]

Why? The matched blood group is already known.

 

If I am A-, I can just say SELECT ... FROM health_profile WHERE blood_group IN('A-', 'O-').

 

Your way is more portable, but it can work either way.

[EdwinMurgoh]

i don't think i need to create another column for compatible blood types.........The compatible blood types are known all i need is a script to get the current user in session blood group and using the compatible list that i have display contact info  all the users that are able to donate to the user. Is it possible to store the users blood group in a session and retrieve it during the matching???

or is there anyone who can help me with the script to get the blood group value which we can later match.

 

I will also make the database changes as advised by scootstah.........I am a newbie in php any assistance i would encourage scripts that i can review.....thank you

[Barand]

     +--------------+         +-----------+            +----------------+               +-------------+                 
     | contact_info |         | register  |            | health_profile |               | bloodgroup  |                 
     +--------------+         +-----------+            +----------------+               +-------------+                 
     | username     |---+     | id (PK)   |            | id (PK)        |               | id (PK)     |                 
     | phone        |   |     | firstname |    +-------| username       |       +-------| user_group  |                 
     | address      |   |     | lastname  |    |       | blood_group    |-------+-------| donor_group |                 
     | town         |   |     | email     |    |       | age            |               +-------------+                 
     +--------------+   +----<| username  |----+       | weight         |                                               
                              | password  |            +----------------+                                               
                              +-----------+                                                             

then

SELECT 
   user.firstname as user_firstname
 , user.lastname as user_lastname
 , bg.user_group 
 , donor.firstname as donor_firstname
 , donor.lastname as donor_lastname
 , bg.donor_group
 , ci.phone
 , ci.address
 , ci.town
FROM health_profile as hpu
    INNER JOIN register as user ON hpu.username = user.username
    INNER JOIN bloodgroup as bg ON hpu.blood_group = bg.user_group
    INNER JOIN health_profile as hpd ON bg.donor_group = hpd.blood_group
    INNER JOIN register as donor ON hpd.username = donor.username
    INNER JOIN contact_info as ci ON donor.username = ci.username
WHERE hpu.username = '$patient'
AND hpu.username <> hpd.username
ORDER BY donor_group

Job done

[scootstah]

 

     +--------------+         +-----------+            +----------------+               +-------------+                 
     | contact_info |         | register  |            | health_profile |               | bloodgroup  |                 
     +--------------+         +-----------+            +----------------+               +-------------+                 
     | username     |---+     | id (PK)   |            | id (PK)        |               | id (PK)     |                 
     | phone        |   |     | firstname |    +-------| username       |       +-------| user_group  |                 
     | address      |   |     | lastname  |    |       | blood_group    |-------+-------| donor_group |                 
     | town         |   |     | email     |    |       | age            |               +-------------+                 
     +--------------+   +----<| username  |----+       | weight         |                                               
                              | password  |            +----------------+                                               
                              +-----------+                                                             

 

Did you do that by hand? O.o

[Barand]

I have done in the past but I am in the middle of creating a small application to automate the process

Edited July 2, 2015 by Barand

[scootstah]

That's a pretty sweet idea!

[QuickOldCar]

 

I have done in the past but I am in the middle of creating a small application to automate the process

 

I was always impressed you did them by hand.