mysql - 不同的查询不使用 AND 运算符给出任何结果
问题描述
这是我试图执行的查询，但我没有得到任何结果，如果您需要更多信息来帮助解决这个问题，请在下面的评论中提问，我将不胜感激。谢谢。
SELECT DISTINCT *
FROM `users`
WHERE ((`height` BETWEEN "139" AND "170"
AND `language` = "english")
AND (`active` = "1"
AND `gender` IN (4525, 4526)
AND DATEDIFF(CURDATE(), `birthday`) / 365 >= "22"
AND DATEDIFF(CURDATE(), `birthday`) / 365 <= "55"
AND `country` = "IT")
AND `id` NOT IN (SELECT `block_userid`
FROM `blocks`
WHERE `user_id` = 1)
AND `id` NOT IN (SELECT `like_userid`
FROM `likes`
WHERE `user_id` = 1)
AND `id` NOT IN (SELECT `user_id`
FROM `likes`
WHERE `like_userid` = 1)
AND `id` <> "1" )
ORDER BY
`xlikes_created_at` DESC, `xvisits_created_at` DESC,
`xmatches_created_at` DESC, `is_pro` DESC
LIMIT 12 OFFSET 0;
我尝试了 OR 运算符，但我当然不是在寻找 OR，我希望所有条件都为真，然后想要结果。
function GetSearchResults($user_id, $limit, $offset, $country = true) {
$where_or = array();
$where_and = array();
$u = auth();
// main query
$query = 'SELECT DISTINCT * FROM `users`';
// Filters
$where = ' WHERE ( ';
// must be verified
$where_and[] = '`active` = "1"';
//$where_and[] = '`privacy_show_profile_match_profiles` = "1"';
//********** public search params *****************//
// check gender from post or from session
$genders = null;
if( isset($_SESSION['_gender']) && $_SESSION['_gender'] !== '') {
$genders = Secure( $_SESSION['_gender'] );
}
if( isset($_POST['_gender']) && $_POST['_gender'] !== '') {
$_SESSION[ '_gender' ] = $_POST['_gender'];
$genders = Secure( $_POST['_gender'] );
}
if( $genders == null || $genders == 'male' || $genders == 'female' ) {
$genders = '4525, 4526';
}
if( is_array($genders)) {
$genders = @implode( ',' , $genders );
}
if( strpos( $genders, ',' ) === false ) {
$where_and[] = '`gender` = "'. $genders .'"';
} else {
$where_and[] = '`gender` IN ('. $genders .')';
}
// check age from post or from session
if(isset($_POST['_age_from']) && !empty($_POST['_age_from']) && isset($_POST['_age_to']) && !empty($_POST['_age_to']) ) {
$where_and[] = 'DATEDIFF(CURDATE(), `birthday`)/365 >= "'. Secure($_POST['_age_from']) .'" AND DATEDIFF(CURDATE(), `birthday`)/365 <= "'. Secure($_POST['_age_to']) . '"';
} else {
if(isset( $_SESSION['_age_from'] ) && isset( $_SESSION['_age_to'] )) {
$where_and[] = 'DATEDIFF(CURDATE(), `birthday`)/365 >= "'. Secure($_SESSION['_age_from']) .'" AND DATEDIFF(CURDATE(), `birthday`)/365 <= "'. Secure($_SESSION['_age_to']) . '"';
} else {
$where_and[] = 'DATEDIFF(CURDATE(), `birthday`)/365 >= "20" AND DATEDIFF(CURDATE(), `birthday`)/365 <= "55"';
}
}
$query_country = '';
if( $u->show_me_to == '' ) {
if ((isset($_POST['_lat']) && !empty($_POST['_lat']) && isset($_POST['_lng']) && !empty($_POST['_lng']))
||
(isset($_SESSION['_lat']) && !empty($_SESSION['_lat']) && isset($_SESSION['_lng']) && !empty($_SESSION['_lng']))
) {
$lat = 0;
$lng = 0;
$located = 7;
if(isset( $_SESSION['_lat'] ) ) $lat = Secure($_SESSION['_lat']);
if(isset( $_POST['_lat'] ) ) $lat = Secure($_POST['_lat']);
if( isset( $_SESSION['_lng'] ) ) $lng = Secure($_SESSION['_lng']);
if( isset( $_POST['_lng'] ) ) $lng = Secure($_POST['_lng']);
if( isset( $_SESSION['_located'] ) ) $located = Secure($_SESSION['_located']);
if( isset( $_POST['_located'] ) ) $located = Secure($_POST['_located']);
$distance = 'ROUND( ( 6371 * acos(cos(radians(' . $lat . ')) * cos(radians(`lat`)) * cos(radians(`lng`) - radians(' . $lng . ')) + sin(radians(' . $lat . ')) * sin(radians(`lat`)))) ,1) ';
$where_and[] = $distance . ' <= ' . $located;
}
} else {
if($country == true) {
$query_country = ' OR `country` = "' . $u->show_me_to . '"';
$where_and[] = '`country` = "' . $u->show_me_to . '"';
}
}
//******************* Looks Filters ************************//
if( isset($_POST['_height_from']) && !empty($_POST['_height_from']) && isset($_POST['_height_to']) && !empty($_POST['_height_to']) ) {
$where_or[] = '`height` BETWEEN "'. Secure($_POST['_height_from']) .'" AND "'. Secure($_POST['_height_to']) .'"';
}
if( isset($_POST['_body']) && !empty($_POST['_body']) ) {
if( strpos( Secure( $_POST['_body'] ), ',' ) === false ) {
$where_or[] = '`body` = "'. Secure($_POST['_body']) . '"';
} else {
$where_or[] = '`body` IN ('. Secure($_POST['_body']) .')';
}
}
//******************* Background Filter ********************//
if( isset($_POST['_language']) && !empty($_POST['_language']) ) {
$where_or[] = '`language` = "'. Secure($_POST['_language']) .'"';
}
if( isset($_POST['_ethnicity']) && !empty($_POST['_ethnicity']) ) {
if( strpos( Secure( $_POST['_ethnicity'] ), ',' ) === false ) {
$where_or[] = '`ethnicity` = "'. Secure($_POST['_ethnicity']) . '"';
} else {
$where_or[] = '`ethnicity` IN ('. Secure($_POST['_ethnicity']) .')';
}
}
if( isset($_POST['_religion']) && !empty($_POST['_religion']) ) {
if( strpos( Secure( $_POST['_religion'] ), ',' ) === false ) {
$where_or[] = '`religion` = "'. Secure($_POST['_religion']) . '"';
} else {
$where_or[] = '`religion` IN ('. Secure($_POST['_religion']) .')';
}
}
//******************* LifeStyle filter *********************//
if( isset($_POST['_relationship']) && !empty($_POST['_relationship']) ) {
if( strpos( Secure( $_POST['_relationship'] ), ',' ) === false ) {
$where_or[] = '`relationship` = "'. Secure($_POST['_relationship']) .'"';
} else {
$where_or[] = '`relationship` IN ('. Secure($_POST['_relationship']) .')';
}
}
if( isset($_POST['_smoke']) && !empty($_POST['_smoke']) ) {
if( strpos( Secure( $_POST['_smoke'] ), ',' ) === false ) {
$where_or[] = '`smoke` = "'. Secure($_POST['_smoke']) . '"';
} else {
$where_or[] = '`smoke` IN ('. Secure($_POST['_smoke']) .')';
}
}
if( isset($_POST['_drink']) && !empty($_POST['_drink']) ) {
if( strpos( Secure( $_POST['_drink'] ), ',' ) === false ) {
$where_or[] = '`drink` = "'. Secure($_POST['_drink']) . '"';
} else {
$where_or[] = '`drink` IN ('. Secure($_POST['_drink']) .')';
}
}
//******************* More Filter **************************//
if( isset($_POST['_interest']) && !empty($_POST['_interest']) ) {
$where_or[] = '`interest` like "%'. Secure($_POST['_interest']) .'%"';
}
if( isset($_POST['_education']) && !empty($_POST['_education']) ) {
if( strpos( Secure( $_POST['_education'] ), ',' ) === false ) {
$where_or[] = '`education` = "'. Secure($_POST['_education']) . '"';
} else {
$where_or[] = '`education` IN ('. Secure($_POST['_education']) .')';
}
}
if( isset($_POST['_pets']) && !empty($_POST['_pets']) ) {
if( strpos( Secure( $_POST['_pets'] ), ',' ) === false ) {
$where_or[] = '`pets` = "'. Secure($_POST['_pets']) .'"';
} else {
$where_or[] = '`pets` IN ('. Secure($_POST['_pets']) .')';
}
}
if( !empty($where_or) ){
$where = $where . '('. implode($where_or, ' AND ') . ') ';
}
if( !empty($where_and) ){
if( !empty($where_or) ) {
$where = $where . ' AND (' . implode($where_and, ' AND ') . ')';
}else{
$where = $where . ' (' . implode($where_and, ' AND ') . ')';
}
}
if( isset( $_REQUEST['access_token'] ) ) {
$uid = GetUserFromSessionID(Secure($_REQUEST['access_token']));
$u->id = $uid;
}
if( isset( $u->id ) ) {
// to exclude blocked users
$notin = ' OR `id` NOT IN (SELECT `block_userid` FROM `blocks` WHERE `user_id` = ' . $u->id . ') ';
// to exclude liked and disliked users users
$notin .= ' OR `id` NOT IN (SELECT `like_userid` FROM `likes` WHERE `user_id` = ' . $u->id . ') ';
$notin .= ' OR `id` NOT IN (SELECT `user_id` FROM `likes` WHERE `like_userid` = ' . $u->id . ') ';
$notin .= ' OR `id` <> "' . $u->id . '" ';
}
$custom_sql = [];
if(isset($_POST['custom_profile_data'])){
$count = 100;
for($i = 0 ; $i <= $count ; $i++ ){
if(isset($_POST['fid_' . $i])){
if(!empty($_POST['fid_' . $i])){
$custom_sql[] = ' id IN (SELECT `user_id` FROM `userfields` WHERE `fid_' . $i .'` = "'.Secure($_POST['fid_' . $i]) . '") ';
}
}
}
}
$custom_sql_text = '';
if(!empty($custom_sql)){
$custom_sql_text .= ' AND ( ';
$custom_sql_text .= implode(' OR ', $custom_sql);
$custom_sql_text .= ' ) ';
}
if( $limit == 0 ) {
$limit = 20;
}
$orderBy = ' ORDER BY ';
$orderBy .= '`xlikes_created_at` DESC';
$orderBy .= ',`xvisits_created_at` DESC';
$orderBy .= ',`xmatches_created_at` DESC';
$orderBy .= ',`is_pro` DESC';
$query = $query . ' ' . $where . $notin . ') ' . $custom_sql_text . $query_country . $orderBy . ' LIMIT '.$limit.' OFFSET '.$offset.';';
return $query;
}
解决方案
为什么需要不同的？通常用户是唯一的
SELECT DISTINCT * FROM `users`
不要将数值保存为字符串
`height` BETWEEN "139" AND "170"
你有重复的性别条件：我不想听起来不敏感，但这些是很多性别。
`gender` IN (4525,4526,1275,1277,1278,1279,1280,1281,1282,1283)
您有 2 个日期差异，而第二个没有比较器。Datediff 也返回一个数值而不是一个字符串
DATEDIFF(CURDATE(), `birthday`)/365 >= "24"
DATEDIFF(CURDATE(), `birthday`)/365 "16"
现在，如果缺少的运算符 onDATEDIFF不能解决问题。直接进入数据库删除所有条件并开始一一添加，直到找到哪个原因返回 0 行。
推荐阅读
- typescript - 2021 年为 npm 浏览器库和 TypeScript 配置 babel 的最佳方式？
- node.js - 我无法在 Windows10 上使用 node-v14.16.0-x64 环境和 VSCodeUserSetup-x64-1.54.3 安装 EnMap
- php - 如何在 Codeigniter 4 中将数据插入到 2 个表中
- python - ValueError：无法将字符串转换为浮点数：Python 3D 图中的“2019-01-01 00:00:00”
- arrays - 在颤动中使用带有名称图像日期的数组列表的列表视图
- connection-pooling - 在 Heroku PostgreSQL 中使用 connect-pg-simple 尝试在没有 SSL 的情况下进行连接
- reactjs - 如何在前端存储socket.IO id？
- sql - 此表达式键入不正确，或者它太复杂而无法计算。SQL 访问
- typescript - 是否可以将 Set 的值用作类型或接口中的允许值？
- sql - 检查特定整数是偶数