英文:
Check sql column that contain a comma separated string with words
问题
我尝试使用FIND_IN_SET,LIKE %word%和其他方法,但没有成功..
好的,现在我有一个具有字段field_name的表,其中有多个结果..
插入了像这样的值:
互联网
安全
家具等等...
我尝试检查我的字符串是否包含在上面的列中传递的单词..
我的字符串是:"互联网,安全"
,我必须检查这个字符串是否通过上面的field_name
列中的值。
这是我的代码:
function check_osobenosti($value,$id_field) {
global $dbh;
//执行
$trim_v = trim($value);
$query2 = $dbh->query("SELECT `field_name` FROM wpls_sw_field_lang WHERE FIND_IN_SET('" . $trim_v . "',`field_name`) > 0 AND `field_id`='" . (int)$id_field . "'");
if($query2->rowCount() > 0) {
return 1;
} else {
return '';
}
}
在$value
中,我传递了上面的字符串,即:
"互联网,安全"
我希望检查这个字符串是否包含在上面的字段中之一..
我不知道该如何做,因为我是一个新手。
英文:
I try with FIND_IN_SET, LIKE %word% and other ways but without success..
Ok, now i have a table with column field_name and there is multiple results in..
There is inserted values like:
Internet
Security
Furnished and so on...
I try to check that my string is contain a words that pass in this columns above..
My string is: "Internet, Security"
and i must check that this string is passing field_name
column from above values.
Here is my code:
function check_osobenosti($value,$id_field) {
global $dbh;
//proceed
$trim_v = trim($value);
$query2 = $dbh->query("SELECT `field_name` FROM wpls_sw_field_lang WHERE FIND_IN_SET('".$trim_v."',`field_name`) > 0 AND `field_id`='".(int)$id_field."'");
if($query2->rowCount() > 0) {
return 1;
} else {
return '';
}
}
in $value
im passing the string from above, which is:
"Internet, Security"
and i want to check that this string is contain in one of above fields..
I dont know how to do it, because im a newbie.
答案1
得分: 0
我会使用IN()
并将查询参数化:
function check_osobenosti(string $value, int $id_field) {
global $dbh;
// 将$value拆分为数组以在执行准备好的语句时传递
// 使用','而不是','来拆分,以便它可以处理两种情况
$toBind = explode(',', $value);
$toBind = array_map('trim', $toBind);
// 构建'?'的占位符字符串,用于IN()
$inPlaceholders = rtrim(str_repeat('?,', count($toBind)), ',');
// 将$id_field添加到要传递给execute的数组中
$toBind[] = $id_field;
// 准备SQL语句
$query = $dbh->prepare("SELECT COUNT(*) FROM wpls_sw_field_lang WHERE `field_name` IN ($inPlaceholders) AND `field_id` = ?");
// 执行准备好的语句
$query->execute($toBind);
if($query->fetchColumn() > 0) {
return 1;
} else {
return '';
}
}
我已将查询更改为COUNT(*)
,因为您没有使用返回的数据,而是计算返回的记录数。您不应该在SELECT语句中使用PDOStatement::rowCount():
对于大多数数据库,PDOStatement::rowCount()不会返回受SELECT语句影响的行数。相反,使用PDO::query()发出带有与预定的SELECT语句相同的谓词的SELECT COUNT(*)语句,然后使用PDOStatement::fetchColumn()来检索匹配行数。
英文:
I would use IN()
and parameterize the query:
function check_osobenosti(string $value, int $id_field) {
global $dbh;
// split $value into an array to pass when executing the prepared statement
// splitting on ',' instead of ', ' so that it can handle either
$toBind = explode(',', $value);
$toBind = array_map('trim', $toBind);
// build string of '?' placeholders for IN()
$inPlaceholders = rtrim(str_repeat('?,', count($toBind)), ',');
// add the $id_field to the array to be passed to execute
$toBind[] = $id_field;
// prepare the SQL statement
$query = $dbh->prepare("SELECT COUNT(*) FROM wpls_sw_field_lang WHERE `field_name` IN ($inPlaceholders) AND `field_id` = ?");
// execute the prepared statement
$query->execute($toBind);
if($query->fetchColumn() > 0) {
return 1;
} else {
return '';
}
}
I have changed the query to a COUNT(*)
as you were not using the returned data, instead of counting the returned records. You should not use PDOStatement::rowCount() with SELECT statements:
> For most databases, PDOStatement::rowCount() does not return the number of rows affected by a SELECT statement. Instead, use PDO::query() to issue a SELECT COUNT(*) statement with the same predicates as your intended SELECT statement, then use PDOStatement::fetchColumn() to retrieve the number of matching rows.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论