在pdo mysql中发布绑定值

Question

我遇到了一个问题，在MySql查询中绑定到一个变量。

我有下面的查询工作

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

我需要LIKE部分是动态的，我试图从当前日期选择过去的7天，要做到这一点，我正在PHP变量中创建日期。

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

我在查询的LIKE部分替换了硬编码的值

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

然后将变量绑定到:lastweek

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

然而，当我执行它时，它没有匹配任何

我尝试了几种不同的查询方式，包括在PHP变量中添加%并从SQL查询中删除它们。它似乎只工作，如果我硬编码的日期到SQL查询。我不知道我做错了什么，我觉得它很简单，我只是看不到它…

提前感谢。

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

我想你要做的是这个?

用占位符 创建sql

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

创建like变量

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

然后绑定包含通配符

的变量

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

然后执行并处理记录集

---

根据上面描述的方法不起作用的注释对其进行测试…

数据库模式

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       |                |
+--------------+---------------------+------+-----+---------+----------------+

然后，在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 ) );

产生以下

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 -没有按照问题使用日期，但它应该工作相同