CODEWITHSUNDEEP
phpmysql
ZYWIEC
ZYWIEC

Reputation: 75

Generate date for selected comments

I will try to describe briefly the problem, namely: I have a page where there are about 1000 profiles. Each profile has its own comments with different dates.

What I would like to do is write a code that would take from each profile 3 last comments, but only the ones whose last comment was no later than 2016 and add them + 6 months, and skips those profiles that have comments from 2017.

I made sample code to add those 6 months:

UPDATE `sktest`.`wp_comment`
SET `time` = DATE_ADD(`time`, INTERVAL 6 MONTH)
WHERE date(`wp_comment`.`time`) BETWEEN "2015-05-01" AND "2016-06-01"

Unfortunately, I have no idea how to get only those profiles with 3 last comments that have a date before 2017. Does anyone have any suggestions? Ideas?

Comments I keep in the wp_comment table, which looks like this: wp_comment table

The column "whom" is ID of the user profile (in wp_users).

EDIT: Ok I did this and seems working. I'm getting 3 last comments from all profiles:

$infor=$wpdb->get_results('SELECT a.*, count(*) as row_number FROM wp_comment a JOIN wp_comment b ON a.whom = b.whom AND a.id <= b.id GROUP BY a.whom, a.id ORDER BY a.time DESC');

    foreach ($infor as $info_resr) {        
        $id             = $info_resr->id;
        $active         = $info_resr->whom;
        $time       = $info_resr->time;
        $row_num        = $info_resr->row_number;
        }
       if ($row_num>3) {
           continue;
       }
        echo ''.$active.', '.$time.', '.$row_num.' <br>';   
    }

Now, how can I skip those profiles that already have comments from 2017?

Upvotes: 1

Views: 52

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133380

Use date( ) for wp_comment.time

  UPDATE `sktest`.`wp_comment` 
  SET `time` = DATE_ADD(`time`, INTERVAL 6 MONTH) 
  WHERE date(`wp_comment`.`time`)  BETWEEN '2015-05-01' AND '2016-06-01'

Upvotes: 3

Related Questions