How do I use multiple values from the same column in the WHERE clause?

问题: I currently have a table that holds the region information. The regions are as follows: Statewide - 2 results North - 4 results South - 4 results East - 3 re...

问题:

I currently have a table that holds the region information. The regions are as follows:

  • Statewide - 2 results
  • North - 4 results
  • South - 4 results
  • East - 3 results
  • West - 7 results

Currently, when a user selects Statewide, they get the result from Statewide. But logically it would make more sense if the results were displayed from all the other regions as well because they are a part of the state. Currently, it is only showing 2 results but it should show 20 results

Also, if someone selects East they should see the results from the name of the Region + Statewide. So if someone selects East, the result should be from East + Statewide. It is currently showing 3 Results instead of 5 Results

Can someone please help me with this? Your help will be much appreciated.

I have tried AND, OR, XOR and UNION statements but none of them seems to work.

if (!empty($region_name)) {

            if($region_name !== 'Statewide'){
                $venueArray[] = " AND (REGION = '$region_name' ) XOR ( REGION = '$statewide')";
            }else if($region_name == 'Statewide'){
                $venueArray[] = " AND (REGION = '$region_name' ) XOR ( REGION != '$statewide')";
            } else{
                $venueArray[] = " AND (REGION = '$region_name')";
            }

        }

Lines 1 - 10 /search.php


回答1:

When the region name is Statewide you want all results (so no condition is necessary), otherwise you want the region and the Statewide results, so your if can be simplified to:

if (!empty($region_name) && $region_name !== 'Statewide'){
    $venueArray[] = " AND (REGION = '$region_name' OR REGION = '$statewide')";
}

Note the change in brackets to ensure this condition plays well with other ones in terms of operator precedence.


回答2:

Sorry, I am not much aware about the php, So I am providing you the solution in SQL query, which may help your result.

Declare @region_name varchar(20)
set @region_name = 'North'  /*You can pas any state here*/

Select * 
from tbl_region
Where (
   (@region_name = 'StateWide' And 1=1)   /*it selects all record in the table*/
     And 
   (region_name = @region_name or region_name = 'Statewide') /*it retrieves records for passed region name and statewide*/
  )
  • 发表于 2019-01-20 23:24
  • 阅读 ( 179 )
  • 分类:网络文章

条评论

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

篇文章

作家榜 »

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