如何在PHP中修改或替换包含CQL(Cassandra DataStax PHP DRIVER)"LIKE"和"bindValue"的SQL查询？

Question

查找如何在CQL查询中执行LIKE(使用以下Cassandra PHP驱动程序：https://github.com/datastax/php-driver)替换我的以下SQL代码：

$con->execute("CREATE TABLE IF NOT EXISTS images(siteUrl varchar PRIMARY KEY, imageUrl varchar, alt varchar, title varchar, description varchar, 
keywords varchar, textFromWebPage varchar, site_lang varchar, width_of_image float, height_of_image float, image_type varchar, 
image_extension varchar, image_attribute varchar, clicks bigint, broken int, centroidScore float, graphBasedScore float, scrapeScore float, 
centroidWeightedScore float, created_date timestamp) WITH caching='ALL';");

$con->execute("CREATE CUSTOM INDEX images_prefix ON images(siteUrl, alt, title, keywords, description, textFromWebPage) USING 'org.apache.cassandra.index.sasi.SASIIndex'");


$query = $this->con->prepare("SELECT * 
FROM images 
WHERE (title LIKE :term 
OR alt LIKE :term 
OR siteUrl LIKE :term 
OR keywords LIKE :term 
OR description LIKE :term
OR textFromWebPage LIKE :term
AND broken=0 ORDER BY clicks DESC LIMIT :fromLimit, :pageSize");

$searchTerm = "%". $term . "%";
$query->bindValue(":term", $searchTerm);
$query->bindValue(":fromLimit", $fromLimit, PDO::PARAM_INT);
$query->bindValue(":pageSize", $pageSize, PDO::PARAM_INT);
$query->execute();

$resultsHtml = "<div class='imageResults'>";

$count = 0;

while($row = $query->fetch(PDO::FETCH_ASSOC)) {
...
...
}

我在以下DataStax文档链接中看到了这一点：https://docs.datastax.com/en/dse/5.1/cql/cql/cql_using/useSASIIndex.html，它需要执行CREATE CUSTOM INDEX以在相关表上创建前缀，并指定必须在其上进行LIKE请求的列。但在我的情况下，我需要将LIKE查询应用于images表的多列

那么，我该如何修改上面的代码，使其正确适应DataStax的CASSANDRA PHP驱动程序，因为我知道最初我试图用CQL替换的是SQL，并且它首先包含bindValue

请帮帮我，因为这已经让我头疼了好几个小时了。

Answer 1

这些行不需要引号，字符串类型的PARAM绑定就足够了(见下文)：

OR strcmp(soundex(title), soundex(:shorterm)) = 0
OR strcmp(soundex(alt), soundex(:shorterm)) = 0)
OR strcmp(soundex(siteUrl), soundex(:shorterm)) = 0
OR strcmp(soundex(keywords), soundex(:shorterm)) = 0
OR strcmp(soundex(description), soundex(:shorterm)) = 0
OR strcmp(soundex(description), soundex(:shorterm)) = 0

您已经将params定义为字符串。

$query->bindValue(":term", $searchTerm, PDO::PARAM_STR);
$query->bindValue(":shorterm", $term, PDO::PARAM_STR);