How to check data available or not in the results?

问题: 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

  • 发表于 2019-02-21 00:27
  • 阅读 ( 189 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除