基于$ get值的mysql join

Question

I have 2 tables.

contacts and complaints.

contacts has 3 columns:

contact_id, contact_name, score

complaints has 4 columns:

complaint_id, contact_id, score_deduction, complaint_type

I am able to obtain value $_GET['complaint_id'] from previous page.

What i am trying to do is delete the record with that particular complaint_id and get the value of complaints.score_deduction and add it back to contacts.score.

I have spent hours and cant seem to figure it out.

Please help.

if (isset($_GET['complaint_id'])) {
record_set('complaints',"SELECT * FROM add_complaints WHERE complaint_id = ".$_GET['complaint_id']."");
$contact_id = $row_complaints['contact_id'];
record_set('contact',"SELECT * FROM contacts WHERE contact_id = $contact_id");
$score = $row_contact['contact_score'];
echo $score;

}

Answer 1

You can just combine that into a single query.

SELECT * FROM add_complaints AS ac
INNER JOIN contacts AS c on ac.contact_id = c.contact_id
WHERE ac.complaint_id = ?

I am not familiar with whatever you are using to make the DB queries. But I assume you would know how to get the contact id, current score, and score deduction values. And make the update query and delete queries from there.

IF you wanted to get more fancy and just update the contact record in place without needing to select anything you could also do:

UPDATE add_complaints AS ac
INNER JOIN contacts AS c on ac.contact_id = c.contact_id
SET c.score = c.score - ac.score_deduction
WHERE ac.complaint_id = ?

Then just delete the complaint

DELETE FROM add_complaints WHERE complaint_id = ?