MySQL多条件类型

Question

I'm creating a CMS from scratch. I'm looking for a way to set a value in my database following a specific condition.

The context here is I want to save records for the deleted or edited comments that would have been reported by the community. Then I wanna view those logs/records but I have trouble defining wheter or not those values are deleted or edited. ( this is important to view the logs, obviously )

Here is the code i've done so far to insert the logs.

// Insert logs moderation
public function insertLogs($idCommentaire){
    $sql ="INSERT INTO logs(com_id, com_date, com_author, com_content, post_id) 
    SELECT com_id, com_date, com_author, com_content, post_id FROM 
    comments WHERE com_id = ?";
    $this->executeRequest($sql, array($idCommentaire));
}

Now I would like to set it up if it's modified or deleted, depending on which method I call this SQL, here is an example for the deletion :

$this->admin->insertLogs($idCommentaire);
$this->admin->suppressCom($idCommentaire);

I've created a new column ENUM from MySql ("deleted" "modified") but can't figure out how can I update this Logs table with the datas on it.

Here is the SQL I'm thinking about :

UPDATE logs SET type =("modified"OR"deleted") WHERE com_id = 70;

Note that it's not a good coding, just what I want to do in my mind.

I'm talking about plain MySQL here, if it's possible to combine it all in one request. Otherwise I would set up 1 more request, 1 for each, but I don't know if it's really clean to do so.

What's your advices and thoughts about it ? Thanks you all.

Answer 1

Your logic isn't too bad. However, I'll note a couple of things.

1. TYPE is a reserved word in MySQL and probably most RDBMSs. Pick another name for your column that indicates if a post has been modified or deleted.

2. There is at least one more state for a post; it's approved, or published, or 'okay', or whatever you'd like to call it. So if you create an ENUM field called SomethingOtherThanTypeThatStillMeansType, or foo or post_status or whatever, include fields for all potential states your data may have, including deleted, modified, posted, not_yet_posted, edited, or whatever you think the system may support at the time of some future feature update.

3. You might consider using an INT type for your status field. I'm thinking that might be a tad faster than an ENUM.