CODEWITHSUNDEEP
phpmysqlapache
marlaw21
marlaw21

Reputation: 41

Not returning a resultset from a PHP db query

I have created a simple php mysql system that diagnose diseases based on symptoms. The user gets to select those symptoms that best describe his/her condition. The problem is that I can't get the query to work. I have the following tables:

  1. m_health_diseases which holds the diseases with their unique ids (i.e. 1, 2, 3, 4, ....).
  2. m_health_symptoms which holds the symptoms with their unique ids.
  3. m_health_relations which holds the relations of diseases and their symptoms based on primary keys from the other two tables.

I'm not allowed to post images as a new user, but I hope you have an idea of the tables. Actually the tables are similar to this link post: Symptom checker with php-mysql in the answer of Akhilesh B Chandran.

This is the SQL query:

SELECT m_health_diseases.DiseaseName 
FROM m_health_relations 
LEFT JOIN m_health_diseases 
  ON m_health_relations.DiseaseId = m_health_diseases.DiseaseId 
WHERE m_health_relations.SymptomId = '14' 
AND m_health_relations.SymptomId = '15' 
AND m_health_relations.SymptomId = '16' 
AND m_health_relations.SymptomId = '4'

The query above should return the disease: 'Common Cold' but it does not return any result set. I have tried changing the query in different ways, but still does not give me the result I need. Any help will be highly appreciated. Thanks.

Upvotes: 2

Views: 190

Answers (3)

manuskc
manuskc

Reputation: 794

Another try,

Since your requirement is to fetch all those DiseaseName whose set of symptoms include all the symptoms mentioned by user, i would suggest you to add logic in your code to handle this, but one way of doing (although not optimized) this via SQL query would be:

Create a table as follows:

#tblRel - holds relation between diseases and symptoms
######################################################
relID  | dieaseID | symptomSet
-----------------------------
1         1          1, 2            //Store the symptom set sorted (let symptom set start with a space)
2         2          1, 2, 3

Now, 

SELECT diseaseID from tblRel where symptomSet like (" 4,% 14,% 15,% 16"); // Sort input set also

This should work for any number of symptoms, but some logic would be required in your code to sort the input set.

Upvotes: 1

Paul Dixon
Paul Dixon

Reputation: 300975

If you want the disease which has all the symptoms 14,15,16,4, you can join another copy of the m_health_relations table for each symptom you want to check.

SELECT d.DiseaseName 
FROM m_health_diseases d
INNER JOIN m_health_relations s1 ON (s1.DiseaseId = d.DiseaseId)
INNER JOIN m_health_relations s2 ON (s2.DiseaseId = d.DiseaseId)
INNER JOIN m_health_relations s3 ON (s3.DiseaseId = d.DiseaseId)
INNER JOIN m_health_relations s4 ON (s4.DiseaseId = d.DiseaseId)
WHERE
s1.SymptomId = 14 AND
s2.SymptomId = 15 AND
s3.SymptomId = 16 AND 
s4.SymptomId = 4;

If you imagine the result without the where clause, you have every disease with every 4-way combination of its symptoms, and the where clause selects a specific combination (if it exists).

Because I've used the same table several times, I've used table aliases for each table, which makes the query easier to read too.

Also, note I used INNER rather than LEFT join, since you are not interested in any null results.

Upvotes: 2

manuskc
manuskc

Reputation: 794

WHERE m_health_relations.SymptomId = '14' 
AND m_health_relations.SymptomId = '15' 
AND m_health_relations.SymptomId = '16' 
AND m_health_relations.SymptomId = '4'

How can m_health_relations.SymptomId be 14 and 15 and 16 and 4 at the same time?

It should be OR or m_health_relations.SymptomId in (14,15,16,4)

Upvotes: 0

Related Questions