|
Задавайте вопросы, мы ответим
Вы не зашли.
Здравствуйте. есть следующая ошибка
CDbCommand не удалось исполнить SQL-запрос: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND o.price<=
AND o.total_space>= AND' at line 22.
Вот весь запрос:
if (!empty($limit))
$limit = ' LIMIT ' . $limit;
if (!empty($filter['objectType']) && !empty($filter['objectType'][0]))
$object_type_filter = 'AND o.objects_types_id IN (' . implode(',', $filter['objectType']) . ')';
if (!empty($filter['id']))
$object_id_filter = 'AND o.id = ' . $filter['id'];
if (isset($filter['dates']) && !empty($filter['dates'])) {
if ($filter['dates'] == 'today')
$dates_filter = 'AND DATE(o.date_add) = CURDATE()';
else
$dates_filter = 'AND DATE(o.date_add) >= DATE_SUB(CURDATE(), INTERVAL ' . $filter["dates"] . ' DAY)';
}
if (!empty($filter['city']))
$city_id_filter = 'AND o.cities_id = ' . $filter['city'];
if (!empty($filter['region'])) {
$region = implode(',', $filter['region']);
$region_filter = 'AND o.rayon_id IN (' . $region . ')';
}
if (!empty($filter['metro'])) {
$metro = implode(',', $filter['metro']);
$metro_id_filter = 'AND o.subway_station IN (' . $metro . ')';
}
if (!empty($filter['name'])) {
$complex = 'AND o.name = ' . $filter['name'];
}
if (!empty($filter['not_ids'])) {
$not_ids = implode(',', $filter['not_ids']);
$object_not_id_filter = 'AND o.id NOT IN (' . $not_ids . ')';
}
if (!empty($filter['newbuilding']))
$newbuilding_filter = 'AND o.newbuilding = ' . $filter['newbuilding'];
if (!empty($filter['bidding']))
$bidding_filter = 'AND o.bidding = ' . $filter['bidding'];
if (isset($filter['price_from']) && isset($filter['price_to']))
$price_filter = 'AND o.price>=' . $filter['price_from'] . ' AND o.price<=' . $filter['price_to'];
if (!empty($filter['currency']))
$currency_filter = 'AND o.currency LIKE "' . $filter['currency'] . '"';
if (isset($filter['rooms_from']) && isset($filter['rooms_to']))
$rooms_filter = 'AND o.rooms>=' . $filter['rooms_from'] . ' AND o.rooms<=' . $filter['rooms_to'];
if (isset($filter['total_space_from']) && isset($filter['total_space_to']))
$total_space_filter = 'AND o.total_space>=' . $filter['total_space_from'] . ' AND o.total_space<=' . $filter['total_space_to'];
if (isset($filter['etag_from']) && isset($filter['etag_to']))
$etag_filter = 'AND o.etag>=' . $filter['etag_from'] . ' AND o.etag<=' . $filter['etag_to'];
if (isset($filter['mebel']) && $filter['mebel'] != 'all')
$mebel_filter = 'AND o.mebel = ' . $filter['mebel'];
if (isset($filter['utility_bills']) && $filter['utility_bills'] != 'all')
$utility_bills_filter = 'AND o.utility_bills = ' . $filter['utility_bills'];
if (isset($filter['parking']) && $filter['parking'] != 'all')
$parking_filter = 'AND o.parking = ' . $filter['parking'];
if (isset($filter['was_rented']) && $filter['was_rented'] != 'all')
$was_rented_filter = 'AND o.was_rented = ' . $filter['was_rented'];
if (!empty($filter['preferences'])) {
$preferences = implode(',', $filter['preferences']);
$preferences_filter = 'AND opf.preference_id IN (' . $preferences . ')';
}
// $preferences_filter = 'AND opf.preference_id = '.$filter['preferences'];
// $preferences_filter = 'AND o.preferences = '.$filter['preferences'];
if (isset($filter['street']) && $filter['street'] === 'nothing') {
$street_filter = 'AND o.street = 0';
} elseif (!empty($filter['street']))
$street_filter = 'AND o.street = ' . $filter['street'];
if (!empty($filter['address'])) {
$address = addcslashes($filter['address'], '%_'); // escape LIKE's special characters
$address = addcslashes($address, '"'); // escape LIKE's special characters
$address_filter = 'AND (o.address LIKE "%' . $address . '%" OR o.full_name LIKE "%' . $address . '%")';
}
if (isset($filter['radius']) && !empty($filter['radius'])) {
if (isset($filter['radius'][0]) && !empty($filter['radius'][0])) {
$radius_filter = ' AND (';
foreach ($filter['radius'] as $i => $coords) {
if (!empty($coords)) {
if ($i == 0)
$radius_filter .= ' ( 6371 * ACOS( COS( RADIANS( ' . $coords["lat"] . ' ) ) * COS( RADIANS( o.lat ) ) * COS( RADIANS( o.lon ) - RADIANS( ' . $coords["lon"] . ' ) ) + SIN( RADIANS( ' . $coords["lat"] . ' ) ) * SIN( RADIANS( o.lat ) ) ) ) < 1.5';
else
$radius_filter .= 'OR ( 6371 * ACOS( COS( RADIANS( ' . $coords["lat"] . ' ) ) * COS( RADIANS( o.lat ) ) * COS( RADIANS( o.lon ) - RADIANS( ' . $coords["lon"] . ' ) ) + SIN( RADIANS( ' . $coords["lat"] . ' ) ) * SIN( RADIANS( o.lat ) ) ) ) < 1.5';
}
}
$radius_filter .= ' ) ';
}
}
$sql = "SELECT
o.id, o.price, o.currency, ot.name as object_type, o.rooms, o.address, o.lat, o.lon, o.street, o.house, o.name, c.name as city, o.objects_types_id, GROUP_CONCAT(opv.objects_properties_id) as prop, o.full_name
FROM {{objects}} o
LEFT JOIN {{objects_types}} ot ON ot.id = o.objects_types_id
-- LEFT JOIN {{gallery}} g ON g.objects_id = o.id
LEFT JOIN {{cities}} c ON o.cities_id = c.id
LEFT JOIN {{objects_properties_values}} opv ON opv.objects_id = o.id
LEFT JOIN {{objects_pref}} opf ON o.id = opf.object_id
-- LEFT JOIN {{objects_preferences}} op ON op.id = opf.preference_id
-- LEFT JOIN {{cities_regiones}} cr ON o.cities_id = cr.cities_id
-- LEFT JOIN {{cities_metro_stations}} cms ON o.cities_id = cms.cities_id
WHERE
1
$object_id_filter
$complex
$object_type_filter
$city_id_filter
$metro_id_filter
$region_filter
$newbuilding_filter
$bidding_filter
$price_filter
$currency_filter
$total_space_filter
$etag_filter
$rooms_filter
$mebel_filter
$parking_filter
$preferences_filter
$street_filter
$address_filter
$objects_prop_filter
$was_rented_filter
$utility_bills_filter
$radius_filter
$object_not_id_filter
$dates_filter
GROUP BY o.id
-- , opv.objects_properties_id
$limit
ORDER BY o.date_add DESC";
$res = Yii::app()->db->createCommand($sql)->queryAll();
Что не так? помогите пожалуйста.
Неактивен
Распечатайте переменную $sql, которая сформирована перед выполнением запроса.
Неактивен
после o.price<= должно идти значение. Ищите в коде почему $filter['price_to'] пустая.
аналогично и по o.total_space
Неактивен
