EdwinMurgoh Posted July 1, 2015 Share Posted July 1, 2015 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- Quote Link to comment Share on other sites More sharing options...
scootstah Posted July 1, 2015 Share Posted July 1, 2015 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. Quote Link to comment Share on other sites More sharing options...
Barand Posted July 1, 2015 Share Posted July 1, 2015 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 Quote Link to comment Share on other sites More sharing options...
scootstah Posted July 1, 2015 Share Posted July 1, 2015 I figured he'd be doing that with application logic. Quote Link to comment Share on other sites More sharing options...
Barand Posted July 1, 2015 Share Posted July 1, 2015 You need that to query for possible donors Quote Link to comment Share on other sites More sharing options...
scootstah Posted July 2, 2015 Share Posted July 2, 2015 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. Quote Link to comment Share on other sites More sharing options...
EdwinMurgoh Posted July 2, 2015 Author Share Posted July 2, 2015 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted July 2, 2015 Share Posted July 2, 2015 +--------------+ +-----------+ +----------------+ +-------------+ | 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 Quote Link to comment Share on other sites More sharing options...
scootstah Posted July 2, 2015 Share Posted July 2, 2015 +--------------+ +-----------+ +----------------+ +-------------+ | 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted July 2, 2015 Share Posted July 2, 2015 (edited) 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 Quote Link to comment Share on other sites More sharing options...
scootstah Posted July 2, 2015 Share Posted July 2, 2015 That's a pretty sweet idea! Quote Link to comment Share on other sites More sharing options...
QuickOldCar Posted July 2, 2015 Share Posted July 2, 2015 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. 1 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.