检查包含逗号分隔的字符串单词的 SQL 列。

huangapple go评论62阅读模式
英文:

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.

huangapple
  • 本文由 发表于 2023年2月24日 02:46:51
  • 转载请务必保留本文链接:https://go.coder-hub.com/75549082.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定