How to create a LIKE Between query?

Question

So I have issue with searching through wp_comments table. This is some custom meta created by the custom plugin. This is what needs to be searched for https://prnt.sc/a2IXbLNVKs11

$q = "SELECT comment_id, meta_value 
        FROM wp_commentmeta 
        WHERE meta_key = 'course_completion' 
        AND meta_value LIKE '" . $course\['id'\]. "%," . date('Y-m-d', strtotime('-24 months')) . "%'";

Except that I need to implement in it Between two dates.

For example:

Between date('Y-m-d', strtotime('-24 months')) 
    and date('Y-m-d', strtotime('-2 months'))

I have tried subquery, nested query, and everything I could think about, but no luck, no results for searching between two dates.

Can you help? Thank you

Did you know there is a `WHERE date BETWEEN oneDate AND anotherDate` syntax — RiggsFolly, Apr 05 '22 at 13:55
$course\['id'\] is because I have an array of different courses and Ids. — Coding Panda, Apr 05 '22 at 14:01
Youwill have to jump through some hoops then. First with some string manipulation functions remove the courseid then pick up the date part and do a string to date conversion on that and then use the result in the `date BETWEEN 'a' AND 'b'` — RiggsFolly, Apr 05 '22 at 14:02
I don't want to remove course[id] if possible. I am searching for course[id] and between dates. Would be possible only to search LIKE between without course[id] ? Do you have example for this? — Coding Panda, Apr 05 '22 at 14:04
Yes I get that, but the only way to search on a `date` between 2 other dates is to make the `date` in your column (which is currently not a date but a string that looks like a date) into a date — RiggsFolly, Apr 05 '22 at 14:06
So are there more than one `course_id` and `Date` in that column? — RiggsFolly, Apr 05 '22 at 14:07
ok, for converting a date I have this in query date('Y-m-d'). As for course_id (stored in array), there is 6 different ones and the Date can vary from 2018 to today. Also, I don't want to edit the column in the DB but only search through it. — Coding Panda, Apr 05 '22 at 14:13
I am not suggesting editing the column. If you want to search the date part of that column you will have to write code to pick out the string which is the date and then convert it to a DATE data type so that it will work with the BETWEEN. All this you have to do as part of the query, never actually changing the data on the datebase. ___Aint comma seperated lists in a single table column a great idea___ — RiggsFolly, Apr 05 '22 at 14:21
Thank you very much, so : 1. _get string that looks like a date and covert it_ ? - > how to do this? How to select after first delimeter all values and convert them to DATE data type? 2 Not sure how to proceed with this after conversion. _Aint comma seperated lists in a single table column a great idea_ yeah, I have no idea who created this field to looks like this, total mess. Thank you for your advice! — Coding Panda, Apr 05 '22 at 14:47
Ok, show me a representative example of the complete contents of this `meta_value` cell, in fact make it a worst case example — RiggsFolly, Apr 05 '22 at 14:50
For this meta_key value, the worst-case scenario would be this `30418,2018-01-31 01:50:35,83.33` but if you need more https://prnt.sc/pvrNQDFMqxC1 — Coding Panda, Apr 05 '22 at 15:46

RiggsFolly · Accepted Answer · 2022-04-06T10:23:14.837

1

A little look through the MySQL manual for String Functions that might help me and refresh my memory on the str_to_date() function and the use of MySQL's own date manipulations using CURDATE() and the INTERVAL and Bob's your Uncle.

$q = "SELECT comment_id, meta_value 
      FROM wp_commentmeta 
      WHERE meta_key = 'course_completion' 
      AND str_to_date( substr(meta_value, locate(',', meta_value)+1, 10), '%Y-%m-%d') 
            BETWEEN DATE_SUB(CURDATE() - INTERVAL -24 month)
            AND DATE_SUB(CURDATE() - INTERVAL -2 month)";

edited Apr 06 '22 at 10:23

answered Apr 05 '22 at 16:21

RiggsFolly

93,638
21
103
149

Nice one, I've never used str_to_date before in mysql. If I was going to use this data often I would use str_to_date in a job that creates another table with comment_id, course_id and date. Then you could index course and date for faster search. – Patrick Apr 06 '22 at 07:55
"CURDATE() - INTERVAL -24 month", didn't work for me though, could be mysql versions. But "BETWEEN DATE_SUB(CURDATE(), INTERVAL 24 month) AND DATE_SUB(CURDATE(), INTERVAL 2 month)" worked – Patrick Apr 06 '22 at 08:07
1

Thank you very much both! **"BETWEEN DATE_SUB(CURDATE(), INTERVAL 24 month) AND DATE_SUB(CURDATE(), INTERVAL 2 month)"** works for me as well! @RiggsFolly – Coding Panda Apr 06 '22 at 09:49

How to create a LIKE Between query?

1 Answers1