问题:
I have a query
SELECT GROUP_CONCAT(batch_days SEPARATOR ", ") FROM `batch_list` WHERE `batch_venue_id` = '1' GROUP BY `batch_venue_id`.
This is the result array I am g...
可以将文章内容翻译成中文,广告屏蔽插件会导致该功能失效:
问题:
I have a query
SELECT GROUP_CONCAT(batch_days SEPARATOR ", ") FROM `batch_list` WHERE `batch_venue_id` = '1' GROUP BY `batch_venue_id`.
This is the result array I am getting it.
Array ( [0] => stdClass Object ( [GROUP_CONCAT(batch_days SEPARATOR ", ")] => 1,2,3,4,7 ) ).
Now What I am doing is. I am passing the 1,7 to check this is available or not in the result if found then display the message.
$days='1,7';
$result12 =$this->db->select('GROUP_CONCAT(batch_days SEPARATOR ",")')
->from('batch_list')
->where('batch_venue_id',$venue_id)
//->where('days',$days)
->group_by('batch_venue_id')
->get()
->result();
So I am getting the result [GROUP_CONCAT(batch_days SEPARATOR ", ")] => 1,2,3,4,7 ) now I have to check the $days available or not in the result. Would you help me out how to check this?
回答1:
You can try with explode()
and array_intersect()
,
<?php
$day_str = '1,7';
$result_str = '1,2,3,4,7';
$result = explode(',',$result_str);
$days = explode(',',$day_str);
if(count(array_intersect($result, $days)) > 0){
echo "$day_str are in $result_str";
}else{
echo "$day_str are not in $result_str";
}
?>
Suggestion: You can use an alias when selecting columns from table
SELECT GROUP_CONCAT(batch_days SEPARATOR ", ") as result FROM `batch_list` WHERE `batch_venue_id` = '1' GROUP BY `batch_venue_id`
DEMO: https://3v4l.org/MG5kt
回答2:
Ideally you maybe want to do this in pure MySQL code.
If you want you can with this MySQL code.
Query
SELECT
GROUP_CONCAT(batch_days SEPARATOR ", ")
, MAX(
CASE
# match this with the batch_days IN (1, 7) part in the CROSS JOIN list length
WHEN values_exist_check.batch_list_count = 2
THEN 1
ELSE 0
END
) AS values_exists
FROM batch_list
CROSS JOIN (
SELECT
COUNT(*) AS batch_list_count
FROM
batch_list
WHERE
batch_venue_id = 1
AND
batch_days IN (1, 7)
) AS values_exist_check
WHERE
batch_venue_id = 1
GROUP BY
batch_venue_id
Result
| GROUP_CONCAT(batch_days SEPARATOR ", ") | values_exists |
| --------------------------------------- | ------------- |
| 1, 2, 3, 4, 5, 6, 7 | 1 |
see demo
For the missing 1 and 7 values this is the result
Result
| GROUP_CONCAT(batch_days SEPARATOR ", ") | values_exists |
| --------------------------------------- | ------------- |
| 2, 3, 4, 5, 6 | 0 |
see demo
Edited
After a review i was realizing that mine last query was to much off a workaround to be confirm with the ANSI SQL GROUP BY standard.
The query below
SELECT
table.column
, (SELECT 1) # literal SQL statement/expression
FROM
table
GROUP BY
table.colum
is confirm with the ANSI SQL GROUP BY standard (MySQL forces this with sql_mode ONLY_FULL_GROUP_BY) because (SELECT 1)
is a literal SQL statement/expression and not depending from the GROUP BY resultset
Meaning the query can be optimized without using a CROSS JOIN
Query
SELECT
GROUP_CONCAT(batch_days SEPARATOR ", ")
, (
CASE
WHEN (
SELECT
COUNT(*) AS batch_list_count
FROM
batch_list
WHERE
batch_venue_id = 1
AND
batch_days IN (1, 7)
# match this with the batch_days IN (1, 7) above
) = 2
THEN 1
ELSE 0
END
)
FROM batch_list
WHERE
batch_venue_id = 1
GROUP BY
batch_venue_id
see demo
Edited because off comment:
Thanks for the answer, I am trying like 1,2,3,4,7 checking with 1,5 if anyone is available then it' should display the message.
From the question i got the impression the the string 1,5
fully needed to be found within 1,2,3,4,7
/ But cleary this is not not what you want/need.
This query will give the correct results.
Query
SELECT
GROUP_CONCAT(batch_days SEPARATOR ", ")
, (
CASE
WHEN (
SELECT
COUNT(*) AS batch_list_count
FROM
batch_list
WHERE
batch_venue_id = 1
AND
batch_days IN (1, 7)
) >= 1
THEN 1
ELSE 0
END
) AS value_exists
FROM batch_list
WHERE
batch_venue_id = 1
GROUP BY
batch_venue_id
Result
| GROUP_CONCAT(batch_days SEPARATOR ", ") | value_exists |
| --------------------------------------- | ------------ |
| 1, 2, 3, 4, 6, 7 | 1 |
see demo
PHP should display the message, it's didn't program in the codeigniter framework for some time now. So mine code could be wrong.
But i believe your code needs to be something like
$result12 =$this->db->select('GROUP_CONCAT(batch_days SEPARATOR ",") AS list')
# not sure if a second select() chain like this was allowed or not, i can't remember annymore.
->select('
(
CASE
WHEN (
SELECT
COUNT(*) AS batch_list_count
FROM
batch_list
WHERE
batch_venue_id = 1
AND
batch_days IN (1, 5)
) >= 1
THEN 1
ELSE 0
END
) AS value_exists
')
->from('batch_list')
->where('batch_venue_id',$venue_id)
//->where('days',$days)
->group_by('batch_venue_id')
->get()
->result();
if( is_array($result12) ) {
if (isset($result12[0]) && isset($result12[0]->value_exists) && (((int)$result12[0]->value_exists) == 1)
echo "<message>"; // display available message
else
echo "<message>"; // display non available message
}
回答3:
You should use ->result_array();
instead of ->result();
because it would treated as normal array not array of objects.
Than you can use in_array()
,array_search methods etc to find if the elements are in the array or not
Or you can use find_in_set
method to check values are there or not.
If you are using result method then you need to fetch it as object as of sql query
SELECT GROUP_CONCAT(batch_days SEPARATOR ", ") as test FROM `batch_list` WHERE `batch_venue_id` = '1' GROUP BY `batch_venue_id`.
Using PHP the variable string you can fetch as $result12[0]->test;
or using foreach loop
$ar_match=[1,7]
foreach($result12 as $val)
{
$ar[]=$val;
}
now $ar is single dimensional array You can use array_intersect(). Checking to see if one array's elements are in another array in PHP