hi there,
help anyone..i have problem in my query..i need to query with multiple values.
for example..in where patientid could be one or more values like this where element = 'element a' or 'element b' or 'element c'...the values are dependent on what the user inputs in his search list..
heres the code im working on now...
public function GetReport($_POST)
{
extract($_POST);
$condition_patient = '';
$condition_doctor = '';
$condition_clinic = '';
$datefrom = $dispensedfromyear.'/'.$dispensedfrommonth.'/'.$dispensedfromday;
$dateto = $dispensedtoyear.'/'.$dispensedtomonth.'/'.$dispensedtoday;
if($patient == 1)
{
if($patient_search == 2)
{
$condition_patient = 'AND u.lastname between :namefrom and :nameto';
}
if($patient_search == 3)
{
$condition_patient = 'AND o.patient_id IN(:patient)';
}
}
if($patient == 2)
{
$condition_patient = 'AND exp.state=:state
AND exp.city=:city
AND exp.postcode=:postcode';
}
if($doctor_search == 2)
{
$condition_doctor = 'AND u2.lastname between :namefrom and :nameto';
}
if($doctor_search == 3)
{
$condition_doctor = 'AND o.doctor_id =:doctor';
}
if($clinic == 2)
{
if($clinic_search == 2)
{
$condition_clinic = 'AND c.clinic between :namefrom and :nameto';
}
if($clinic_search == 3)
{
$condition_clinic = 'AND o.clinic_id =:clinic';
}
}
try
{
$stmt = $this->db->prepare("SELECT o.id, o.price, ot.order_type,
u.id as patientid, exp.telephone as patienttel,
exp.address as patientst,
exp.city as patientcity, exp.state as patientstate,
exp.postcode as patientpostcode,
exp.pathology as patientpathology,u2.id as doctorid,
concat( u.firstname, u.lastname ) AS patientname,
concat( u2.firstname, u2.lastname ) AS docname,
exd.practice as doctype,exd.address as docst,
exd.city as doctorcity,exd.state as doctorstate,
exd.postcode as doctorpostcode,
exd.prescriber_number as docpresc,
c.clinic AS clinicname,
ins.delivery_date AS datedeliver,
o.script_date as prescdate,
i.description as drugdesc,
i.id as ingid,
i.name as ingname,
i.type as ingtype,
i.abbreviation as ingabrev,
i.unit_of_measurement as ingunit,
i.measurement_tolerance as ingmeasurement,
i.min_warehouse_stock as ingstock,
i.min_lab_stock as inglabstock
FROM tbl_order AS o
LEFT JOIN tbl_user AS u ON u.id = o.patient_id
LEFT JOIN tbl_user_extended_patient as exp on exp.user_id=u.id
LEFT JOIN tbl_order_type AS ot ON ot.id = o.order_type
LEFT JOIN tbl_user AS u2 ON u2.id = o.doctor_id
LEFT JOIN tbl_user_extended_doctor as exd on exd.user_id=u2.id
LEFT JOIN tbl_clinic AS c ON c.id = o.clinic_id
LEFT JOIN tbl_order_medication_definition AS md ON md.order_id = o.id
LEFT JOIN tbl_order_medication_definition_ingredient AS mdi ON mdi.order_medication_definition_id = md.id
LEFT JOIN tbl_ingredient AS i ON mdi.ingredient_id = i.id
LEFT JOIN tbl_ingredient_stock AS ins ON ins.ingredient_id = i.id
LEFT JOIN tbl_ingredient_stock_manufacturer AS ism ON ins.ingredient_stock_manufacturer_id = ism.id
WHERE o.order_type =:script
AND o.script_date between :datefrom and :dateto
$condition_patient
$condition_doctor
$condition_clinic
ORDER BY patientname asc");
$stmt->bindParam(":script", $script_type);
$stmt->bindParam(":datefrom", $datefrom);
$stmt->bindParam(":dateto", $dateto);
if($patient == 2)
{
$stmt->bindParam(":state", $State);
$stmt->bindParam(":city", $City);
$stmt->bindParam(":postcode", $Postcode);
}
if($patient_search == 2 )
{
$stmt->bindParam(":namefrom", $patient_name_from);
$stmt->bindParam(":nameto", $patient_name_to);
}
if($patient_search == 3 )
{
$stmt->bindParam(":patient", $patientlistvalue);
}
if($doctor_search == 2 )
{
$stmt->bindParam(":namefrom", $doctor_name_from);
$stmt->bindParam(":nameto", $doctor_name_to);
}
if($doctor_search == 3)
{
$stmt->bindParam(":doctor", $doctorlistvalue);
}
if($clinic_search == 2 )
{
$stmt->bindParam(":namefrom", $clinic_name_from);
$stmt->bindParam(":nameto", $clinic_name_to);
}
if($clinic_search == 3)
{
$stmt->bindParam(":clinic", $cliniclistvalue);
}
$stmt->execute();
return $stmt;
}
catch(PDOException $e)
{
return $e->getMessage();
}
}thanks for any help =)