Issues binding value in pdo mysql

Question

I'm having an issue getting a date bound to a variable in an MySql query I'm trying to make.

I have the following query that works

$sql = 'SELECT `date` FROM `sessions` WHERE `date` LIKE "%2022-07-09%"';

I need the LIKE portion to be dynamic, I am trying to select 7 days in the past from the current date, to do that I am creating the date in a PHP variable.

$lastWeek = date("Y-m-d", strtotime("-7 days"));

I replaced the hardcoded value in the LIKE portion of my query

$sql = 'SELECT `date` FROM `lift_sessions` WHERE `date` LIKE "%:lastweek%"';

And then bound the variable to :lastweek

$stmt->bindValue(':lastweek', $lastWeek);

However when I execute that it doesn't match anything

I've tried making the query a few different ways including adding the % in the PHP variable and removing them from the SQL query. It only seems to work if I hardcode the date into the SQL query. I can't figure out what I'm doing wrong, I feel like it's simple and I just can't see it...

Thanks in advance.

public static function compareWeeklyStats() {

    $current_date = date('F, jS');

    $lastWeek = date("Y-m-d", strtotime("-7 days"));

    //error_log($lastWeek);

    if(isset($_SESSION['user_id'])) { 
        $sql = 'SELECT `date` FROM `sessions` WHERE `date` LIKE "%:lastweek%"';
        $db = static::getDB();
        $stmt = $db->prepare($sql);
        //$stmt->bindValue(':user', $_SESSION['user_id']);
        //$stmt->bindValue(':lastweek', $lastWeek);
        $stmt->bindValue(':lastweek', $lastWeek, PDO::PARAM_STR);
        $stmt->execute();
        error_log(print_r($stmt->fetchAll(PDO::FETCH_ASSOC), true));
        return;
    }

    return; }

Answer 1

I think what you want to do is this?

Create the sql with the placeholder

$sql = 'SELECT `date` FROM `lift_sessions` WHERE `date` LIKE :lastweek';

Create the like variable

$like='%' . $lastWeek . '%';

Then bind the variable containing the wildcard characters

$stmt->bindParam(':lastweek', $like );

Then execute and process recordset

---

A test of the method described above in light of comment that it does not work...

The db schema

mysql> select
    ->      `taskname`,
    ->      `industryname`,
    ->      `country`,
    ->      `orgname`
    ->  from `volopp`;
    
+----------------------+-------------------+----------+-------------------------------+
| taskname             | industryname      | country  | orgname                       |
+----------------------+-------------------+----------+-------------------------------+
| squirrel juggling    | outdoor pursuits  | scotland | Squirrel Jugglers Association |
| Hedgehog Pickling    | food and drink    | england  | Hog Heaven Association        |
| Haggis Hunting       | outdoor pursuits  | scotland | Campbell's Haggis Hunt        |
| Nessie Netting       | outdoor pursuits  | scotland | NessieNets.org.uk             |
| Dwarf Tossing        | outdoor pursuits  | scotland | highlandgames.scot            |
| Stickleback Stuffing | indoor pursuits   | wales    | stickleback.org               |
| squirrel suckling    | historic pastimes | ireland  | weirdness abounds             |
+----------------------+-------------------+----------+-------------------------------+


mysql> describe volopp;
+--------------+---------------------+------+-----+---------+----------------+
| Field        | Type                | Null | Key | Default | Extra          |
+--------------+---------------------+------+-----+---------+----------------+
| id           | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| taskname     | varchar(50)         | NO   | MUL | 0       |                |
| industryname | varchar(50)         | NO   |     | 0       |                |
| description  | text                | YES  |     | NULL    |                |
| country      | varchar(50)         | NO   |     | 0       |                |
| orgname      | varchar(50)         | NO   |     | 0       |                |
| photo        | varchar(50)         | NO   |     | 0       |                |
| status       | tinyint(3) unsigned | NO   | MUL | 0       |                |
+--------------+---------------------+------+-----+---------+----------------+

Then, in PHP:

$var='squirr';

$sql='select 
    `taskname`,
    `industryname`,
    `country`,
    `orgname` 
from `volopp` 
    where `taskname` like :taskname';
    
$like='%' . $var . '%';

$stmt=$db->prepare( $sql );
$stmt->bindParam(':taskname', $like );
$stmt->execute();

printf( '<pre>%s</pre>', print_r( $stmt->fetchAll( PDO::FETCH_ASSOC ), true ) );

Yields the following

Array
(
    [0] => Array
        (
            [taskname] => squirrel juggling
            [industryname] => outdoor pursuits
            [country] => scotland
            [orgname] => Squirrel Jugglers Association
        )

    [1] => Array
        (
            [taskname] => squirrel suckling
            [industryname] => historic pastimes
            [country] => ireland
            [orgname] => weirdness abounds
        )

)

OK - not using a date as per the question but it should work the same