How to run multiple conditions on one field

[smash120]

I am having a very difficult time trying to get my query to work correctly and at the moment it does not do what I need it to do. Here is what I want to do

SELECT account_num from patient_info where dob='1901' OR dob ='1912' OR dob ='1940'

Than loop throu the result set and run another query like so

foreach ($accounts as $account) {
$q=$this->db->query("SELECT account_num
FROM (
SELECT account_num, CONCAT(prescription,'-',dosage) as script
FROM ci_prescription_history
)a
WHERE script='".implode("' OR script ='",$doses)."' AND account_num=$account");
}

Then run another query similar to the one above. But this does not work for me because it will grab any row which matches either of the OR statements when I need it to match both. So I tried running this query but it returns an empty set.

$foreach ($accounts as $account) {
q=$this->db->query("SELECT account_num
FROM (
SELECT account_num, CONCAT(prescription,'-',dosage) as script
FROM ci_prescription_history
)a
WHERE script='".implode("' AND script ='",$doses)."' AND account_num=$account");
}

I also tried using the IN clause but thats the same as the OR statement. How can I accomplish this query? I am still learning advance queries so please bare with me.

[Barand]

WHERE dob IN (1901, 1912, 1940)

[Psycho]

You should NEVER run queries in loops. You need to learn how to use JOINs.

 

For example, the looping query can be replaced with this (i.e. no need for the first query)

$query = "SELECT history.account_num, CONCAT(history.prescription,'-',history.dosage) as script
          FROM ci_prescription_history as history
          JOIN patient_info as patient
            ON patient.account_num = history.account_num
          WHERE history.script IN ({$scripts})
            AND patient.dob IN ('1901', '1912', '1940')";

Note; this isn't tested since I don't have your database, but it should be close.

Edited May 3, 2013 by Psycho

[smash120]

You should NEVER run queries in loops. You need to learn how to use JOINs.

 

For example, the looping query can be replaced with this (i.e. no need for the first query)

$query = "SELECT history.account_num, CONCAT(history.prescription,'-',history.dosage) as script
          FROM ci_prescription_history as history
          JOIN patient_info as patient
            ON patient.account_num = history.account_num
          WHERE history.script IN ({$scripts})
            AND patient.dob IN ('1901', '1912', '1940')";

Note; this isn't tested since I don't have your database, but it should be close.

I tired using your query but I cant get it to work. I get this error "Unknown column 'history.script' in 'where clause'". I also tried rewriting it with the subquery but I still cant get it to work. Im not that great with databases so Im pretty sure it's something I am doing.

[Barand]

You can not use aliases in the WHERE clause, so

 

WHERE CONCAT(history.prescription,'-',history.dosage) IN ('$scripts');

[smash120]

You can not use aliases in the WHERE clause, so

 

WHERE CONCAT(history.prescription,'-',history.dosage) IN ('$scripts');

Thanks for that info so here is what I have so far but I am getting an empty result set

SELECT history.account_num, CONCAT(history.prescription,'-',history.dosage) as script, patient.gender
FROM ci_prescription_history as history
JOIN ci_patient_info as patient
ON patient.account_num = history.account_num
WHERE patient.gender="male" 
AND CONCAT(history.prescription,'-',history.dosage) IN ($scripts)
HAVING COUNT(script)=$scripts

If I remove the having count clause I will get a list but I only want the records that have the right count. So in this case the prescriptions would be Flonase-50 mcg/inh and Singulair-5mg so count is 2 and I should get three records but I don't get any.

Edited May 16, 2013 by smash120

[Barand]

What does scripts contain? It is being used as a list of strings and as a count in the query

[smash120]

What does scripts contain? It is being used as a list of strings and as a count in the query

I wrote the query wrong when I added the code here but in my project it is written correctly.  It should read like this

SELECT history.account_num, CONCAT(history.prescription,'-',history.dosage) as script, patient.gender
FROM ci_prescription_history as history
JOIN ci_patient_info as patient
ON patient.account_num = history.account_num
WHERE patient.gender="male" 
AND CONCAT(history.prescription,'-',history.dosage) IN ($scripts)
HAVING COUNT(script)=count($prescriptions[0])

so $scripts is a string and $prescriptions[0] is an array

[Muddy_Funster]

What are the contents of the variables that you are sending to the query at run time?