Почему не срабатывает запрос, выдаёт NULL?

Question

[sharpsss]

Есть вот такой вот итог запроса:

Массив transfers_modify"IT разработка","Менджмент"
Массив transfers: ["IT разработка","Менджмент"]
{"transfers":["IT разработка","Менджмент"],"counts":["2","1"],"colors":["#9c2525","#196fbf"]}

Вот код который выводит всё это дело

<?php
require_once 'connection.php';

try
{
	$select_stmt=$db->prepare('SELECT MAX(result) AS Name, COUNT(result) AS Count FROM `results` GROUP BY (result)');
	$select_stmt->setFetchMode(PDO::FETCH_ASSOC);
	$select_stmt->execute();
	while($record = $select_stmt->fetch()) {
		$transfers[] = $record["Name"];
		$counts[] = $record["Count"];
	}
}
catch(PDOException $e)
{
	echo(getMessage());
}
				$mystring = implode("\",\"", $transfers);
				$transfers_modify = "\"$mystring\"";
				echo "Массив transfers_modify".$transfers_modify."<br> Массив transfers: ".json_encode($transfers,JSON_UNESCAPED_UNICODE)."<br>";
try
{
	$select_stmt=$db->prepare('SELECT j.result_color,pas_project FROM structure_test t JOIN JSON_TABLE(t.structure, "$.exodus[*]" COLUMNS(result_combination varchar(20) PATH "$.result_combination", result_color varchar(20) PATH "$.result_color", result VARCHAR(20) PATH "$.result", result_description VARCHAR(255) PATH "$.result_description")) j WHERE j.result IN ("IT разработка","Менджмент") AND named_project=:pname');
	$select_stmt->execute(array(':pname'=>$pn));
	while($record = $select_stmt->fetch()){
		if($select_stmt->rowCount() > 0){
			if(password_verify($pp, $record["pas_project"])) 
			{
				$colors[] = $record["result_color"];
			}
		}		
	}
} 
catch(PDOException $e)
{
	$status->getMessage();
}


$response = array("transfers"=>$transfers,"counts"=>$counts,"colors"=>$colors);
echo json_encode($response, JSON_UNESCAPED_UNICODE);
?>

Суть: нужно заменить
("IT разработка","Менджмент")
на значения из переменной $transfers_modify в которой по идеи сформирована(из массива $transfers)строка, которая такая же как и в запросе сейчас ("IT разработка","Менджмент")
Собственно , беру заменяю

<?php
require_once 'connection.php';

try
{
	$select_stmt=$db->prepare('SELECT MAX(result) AS Name, COUNT(result) AS Count FROM `results` GROUP BY (result)');
	$select_stmt->setFetchMode(PDO::FETCH_ASSOC);
	$select_stmt->execute();
	while($record = $select_stmt->fetch()) {
		$transfers[] = $record["Name"];
		$counts[] = $record["Count"];
	}
}
catch(PDOException $e)
{
	echo(getMessage());
}
				$mystring = implode("\",\"", $transfers);
				$transfers_modify = "\"$mystring\"";
				echo "Массив transfers_modify".$transfers_modify."<br> Массив transfers: ".json_encode($transfers,JSON_UNESCAPED_UNICODE)."<br>";
try
{
	$select_stmt=$db->prepare('SELECT j.result_color,pas_project FROM structure_test t JOIN JSON_TABLE(t.structure, "$.exodus[*]" COLUMNS(result_combination varchar(20) PATH "$.result_combination", result_color varchar(20) PATH "$.result_color", result VARCHAR(20) PATH "$.result", result_description VARCHAR(255) PATH "$.result_description")) j WHERE j.result IN (:transfers) AND named_project=:pname');
	$select_stmt->execute(array(':pname'=>$pn,':transfers'=>$transfers_modify));
	while($record = $select_stmt->fetch()){
		if($select_stmt->rowCount() > 0){
			if(password_verify($pp, $record["pas_project"])) 
			{
				$colors[] = $record["result_color"];
			}
		}		
	}
} 
catch(PDOException $e)
{
	$status->getMessage();
}


$response = array("transfers"=>$transfers,"counts"=>$counts,"colors"=>$colors);
echo json_encode($response, JSON_UNESCAPED_UNICODE);
?>

Итог: ловлю NULL

Массив transfers_modify"IT разработка","Менджмент"
Массив transfers: ["IT разработка","Менджмент"]
{"transfers":["IT разработка","Менджмент"],"counts":["2","1"],"colors":null}

Answer 1

[sharpsss]

Проблема была связана с экранированием кавычек

<?php
require_once 'connection.php';


$select_stmt=$db->prepare('SELECT MAX(result) AS Name, COUNT(result) AS Count FROM `results` GROUP BY (result)');
$select_stmt->setFetchMode(PDO::FETCH_ASSOC);
$select_stmt->execute();
while($record = $select_stmt->fetch()) {
	$transfers[] = $record["Name"];
	$counts[] = $record["Count"];
}

$mystring = implode("','", $transfers); 
$transfers_modify = "'$mystring'";

$select_stmt=$db->prepare('SELECT j.result_color,pas_project FROM structure_test t JOIN JSON_TABLE(t.structure, "$.exodus[*]" COLUMNS(result_combination varchar(20) PATH "$.result_combination", result_color varchar(20) PATH "$.result_color", result VARCHAR(20) PATH "$.result", result_description VARCHAR(255) PATH "$.result_description")) j WHERE j.result IN ('.$transfers_modify.') AND named_project=:pname');
$select_stmt->execute(array(':pname'=>$pn));
while($record = $select_stmt->fetch()){
	if($select_stmt->rowCount() > 0){
		if(password_verify($pp, $record["pas_project"])) 
		{
			$colors[] = $record["result_color"];
		}
	}		
}



$response = array("transfers"=>$transfers,"counts"=>$counts,"colors"=>$colors);
echo json_encode($response, JSON_UNESCAPED_UNICODE);
?>

https://www.programulin.ru/zashchita-ot-sql-in-ekc...

Answer 2

[ragq1991]

$mystring = implode("\",\"", $transfers); в этой строке Вы преобразовали значения массива в строку
$transfers_modify = "\"$mystring\""; а здесь в переменную посадили эту строку, соотвественно в строку запроса отправилась строка, а не массив.

после строки

echo "Массив transfers_modify".$transfers_modify."<br> Массив transfers: ".json_encode($transfers,JSON_UNESCAPED_UNICODE)."<br>";

выведите это

echo gettype($transfers_modify);

и если я прав, Вы увидите тип "string"

Comments

[sharpsss]

извиняюсь , решил вопрос решился - проблема с экранированием кавычек была связана, string - да, так и задумано

<?php
require_once 'connection.php';


$select_stmt=$db->prepare('SELECT MAX(result) AS Name, COUNT(result) AS Count FROM `results` GROUP BY (result)');
$select_stmt->setFetchMode(PDO::FETCH_ASSOC);
$select_stmt->execute();
while($record = $select_stmt->fetch()) {
	$transfers[] = $record["Name"];
	$counts[] = $record["Count"];
}

$mystring = implode("','", $transfers); 
$transfers_modify = "'$mystring'";

$select_stmt=$db->prepare('SELECT j.result_color,pas_project FROM structure_test t JOIN JSON_TABLE(t.structure, "$.exodus[*]" COLUMNS(result_combination varchar(20) PATH "$.result_combination", result_color varchar(20) PATH "$.result_color", result VARCHAR(20) PATH "$.result", result_description VARCHAR(255) PATH "$.result_description")) j WHERE j.result IN ('.$transfers_modify.') AND named_project=:pname');
$select_stmt->execute(array(':pname'=>$pn));
while($record = $select_stmt->fetch()){
	if($select_stmt->rowCount() > 0){
		if(password_verify($pp, $record["pas_project"])) 
		{
			$colors[] = $record["result_color"];
		}
	}		
}



$response = array("transfers"=>$transfers,"counts"=>$counts,"colors"=>$colors);
echo json_encode($response, JSON_UNESCAPED_UNICODE);
?>