Dear team,
I am trying to create a feature for my website to provide auto searches for users. How this will work is that user will have options to specify that they need in their prospective partners. these get stored in the Comprofiler table, i have created a query that fetches the prospective matches for each profile and sends them an email, till here it is fine , but i want to incorporate a couple more things to make this more meaningful.
1) The result of the query should show only those profiles which are not already a connection for that user. till now the query is mostly running from comprofiler table. but to incorporate this feature i am trying to apply a join in the query but i am still not able to bring the resulkt set down to only profiles which are non connections. I am trying to join the table t4qcm_comprofiler_members.
2) the result of the result of the query should also respect the users privacy settings such that if he has chosen to hide his photo it should be hidden in the email too. But i am unable to find the privacy details in the database, can you please point me to the right table or suggest a way out( i remember that privacy simply hides the HTML output,so maybe no table for privacy exists?)
Query :-
Code:
$gender ="Male";
$stmt = $connect->prepare("
SELECT users.email,user_c.id AS user_id, user_c.firstname
AS user_firstname, partner_c.id AS partner_id, partner_c.firstname
AS partner_firstname
,partner_c.lastname
,partner_c.cb_religion
,partner_c.cb_functionalarea
,partner_c.avatar
,partner_c.cb_dateofbirth
FROM t4qcm_comprofiler AS user_c
JOIN t4qcm_comprofiler AS partner_c
ON user_c.cb_complexion2 = partner_c.cb_complexion
AND user_c.cb_religion2 = partner_c.cb_religion
AND user_c.cb_smoking2 = partner_c.cb_smoking
AND user_c.cb_drinking2 = partner_c.cb_drinking
AND user_c.cb_eatinghabits2 = partner_c.cb_eatinghabits
AND user_c.cb_height2 = partner_c.cb_height
AND user_c.cb_weight2 = partner_c.cb_weight
AND user_c.cb_maritalstatus2 = partner_c.cb_maritalstatus
AND partner_c.cb_gender != user_c.cb_gender
AND partner_c.cb_gender = '$gender'
INNER JOIN t4qcm_comprofiler_members AS members
ON user_c.id = members.referenceid
AND partner_c.id != members.memberid
INNER JOIN t4qcm_users AS users ON user_c.id = users.id
LIMIT 0 , 30");
$stmt->bind_param("s",$gender);
$stmt->execute();
$stmt->bind_result($useremail,$userid,$userfirstname,$partnerid,$partnerfirstname,$partnerlastname,$partnerreligion,$partnerfunctionalarea,$partneravatar,$partnerdob);
$stmt->store_result();
krileon
