hi, i need help to add a where statement to the query, i need to know which field name in the db i have to insert a value this is the code:
the property is called categoria, if i add this code it gives me error could you help me to fix it???
if(count($prop)){
foreach ($prop as $pr) {
if($pr=="ristorante" || $pr=="discoteca" || $pr=="cinema" || $pr=="centro benessere" || $pr=="sport e hobbies"){
$where[] = $id . '.`' . "categoria" . '` LIKE ' . $this->_db->Quote('%' . $pr . '%');
}
}
}
the search module works with one property or another i need it to find 2 properties at the same time, could you help me if it works this way, the website is ok please help me!!!! thank you for your help
function buildSimpleQuery()
{
static $query;
if (empty($query)) {
$templateHelper = AFactory::getTemplateHelper();
/* @var $templateHelper ATemplateHelper */
$closed="chiuso";
$cap = abs($this->_lists['required_capacity']); // capacity should be positive integer
if ($cap < 1)
$cap = 1; // capacity has to be at lest 1
$query = 'SELECT `sbj`.`id`, `sbj`.`title`, `sbj`.`alias`, `sbj`.`template`, `sbj`.`introtext`, `sbj`.`params`, COUNT(`kid`.`id`) AS `children` ';
if ($this->_lists['date_from'] || $this->_lists['date_to']) {
// search for reservations what cover date range and sum of their capacity make subject full in this period
$query .= ', (`rsv`.`state` = ' . RESERVATION_ACTIVE . ' AND (`sbj`.`total_capacity` - SUM(`itm`.`capacity`)) < ' . $cap . ') AS `isEngaged` '; // it will be test in parent select
}
$query .= 'FROM `#__booking_subject` AS `sbj` ';
$query .= 'LEFT JOIN `#__booking_subject` AS `kid` ON `kid`.`parent` = `sbj`.`id` '; // indentify parent - search if object has children
if ($this->_lists['date_from'] || $this->_lists['date_to']) {
if ($this->_lists['date_type'] == 'date') { // hardly to full day reservation format
$this->_lists['date_from'] = date('Y-m-d 23:59:00', strtotime($this->_lists['date_from']));
$this->_lists['date_to'] = date('Y-m-d 00:00:00', strtotime($this->_lists['date_to']));
}
// search for reservations what cover date range and sum of their capacity make subject full in this period
$query .= ' LEFT JOIN `#__booking_reservation_items` AS `itm` ON `itm`.`subject` = `sbj`.`id` ';
if ($this->_lists['date_from'])
$query .= ' AND `itm`.`from` <= ' . $this->_db->quote($this->jdate2save($this->_lists['date_to'], ADATE_FORMAT_MYSQL_DATETIME, true));
if ($this->_lists['date_to'])
$query .= ' AND `itm`.`to` >= ' . $this->_db->quote($this->jdate2save($this->_lists['date_from'], ADATE_FORMAT_MYSQL_DATETIME, true));
$query .= ' LEFT JOIN `#__booking_reservation` AS `rsv` ON `rsv`.`id` = `itm`.`reservation_id` ';
$query .= ' AND `rsv`.`state` = ' . RESERVATION_ACTIVE . ' '; // only active reservations
}
if ($this->_lists['price_from'] || $this->_lists['price_to']) // search for required price range
$query .= 'LEFT JOIN `#__booking_price` AS `pre` ON `pre`.`subject` = `sbj`.`id` ';
if (!empty($this->_lists['properties'])) {
// filter by template properties
foreach ($this->_lists['properties'] as $templateId => $properties) {
$template = $templateHelper->getTemplateById($templateId);
/* @var $template ATemplate */
$id = '`tmpl' . $templateId . '`'; // alias of template database table
foreach ($properties as $name => $param) { // check if properties has value to filter
$value = JString::trim($param['value']);
if ($value){
$prop[] =$value;
if ($param['type'] == 'text' || $param['type'] == 'textarea'){ // fulltext search
$where[] = $id . '.`' . $name . '` LIKE ' . $this->_db->Quote('%' . $value . '%');
$query2 ="";
}elseif ( $value=="tutti") {
$where[] = $id . '.`' . $name . '` NOT LIKE ' . $this->_db->Quote('%' . $closed . '%');
//$query2 = ' , '.$id . '.`' . $name .' DESC ';
//$query2 = ' , '.$id . '.`' . $name .'` DESC ';
$query2 = ' ORDER BY '.$id . '.`' . $name .'` DESC ';
} else {// search by key
$where[] = $id . '.`' . $name . '` = ' . $this->_db->Quote($value);
$query2 ="";
}
}
}
if (!empty($where)) // there is params to filter - join with template
$query .= ' LEFT JOIN `' . $template->getDBTableName() . '` AS ' . $id . ' ON ' . $id . '.`id` = `sbj`.`id` ';
}
}
/*
if(count($prop)){
foreach ($prop as $pr) {
if($pr=="ristorante" || $pr=="discoteca" || $pr=="cinema" || $pr=="centro benessere" || $pr=="sport e hobbies"){
$where[] = $id . '.`' . "categoria" . '` LIKE ' . $this->_db->Quote('%' . $pr . '%');
}
}
}
*/
$where[] = '`sbj`.`state` = ' . SUBJECT_STATE_PUBLISHED; // always only published items
$where[] = '`sbj`.`access` IN (' . implode(',', $this->_lists['access']) . ')'; // logged user ACL
if (!is_null($this->_lists['parent'])) // search in parent branch
$where[] = '`sbj`.`parent` = ' . $this->_lists['parent'];
else // search in all bookable objects - ignore no-bookable parents
$where[] = '`kid`.`id` IS NULL'; // hasn't children
$gmt0 = $this->_db->quote(AModel::getNow());
$null = $this->_db->quote(AModel::getNullDate());
$where[] = '(`sbj`.`publish_up` <= ' . $gmt0 . ' OR `sbj`.`publish_up` = ' . $null . ')';
$where[] = '(`sbj`.`publish_down` >= ' . $gmt0 . ' OR `sbj`.`publish_down` = ' . $null . ')'; // always only published items
if ($this->_lists['template_area']) // search in specific template
$where[] = '`sbj`.`template` = ' . abs($this->_lists['template_area']); // template shoud be positive integer
if ($this->_lists['price_from'] || $this->_lists['price_to']) {
// search for price covered required price range
if ($this->_lists['price_from'])
$where[] = '`pre`.`value` >= ' . $this->_db->quote($this->_lists['price_from']);
if ($this->_lists['price_to'])
$where[] = '`pre`.`value` <= ' . $this->_db->quote($this->_lists['price_to']);
// search for price covered required date range
if ($this->_lists['date_from']) {
$where[] = '`pre`.`date_up` <= ' . $this->_db->quote($this->jdate2save($this->_lists['date_to'], ADATE_FORMAT_MYSQL_DATE, true));
$where[] = '(`pre`.`time_up` <= ' . $this->_db->quote($this->jdate2save($this->_lists['date_to'], ADATE_FORMAT_MYSQL_TIME, true)) . ' OR `pre`.`time_up` = "00:00:00")'; // time up only in hourly reservation types
}
if ($this->_lists['date_to']) {
$where[] = '`pre`.`date_down` >= ' . $this->_db->quote($this->jdate2save($this->_lists['date_from'], ADATE_FORMAT_MYSQL_DATE, true));
$where[] = '(`pre`.`time_down` >= ' . $this->_db->quote($this->jdate2save($this->_lists['date_from'], ADATE_FORMAT_MYSQL_TIME, true)) . ' OR `pre`.`time_down` = "00:00:00")';
}
}
if ($this->_lists['required_capacity'])
$where[] = '`sbj`.`total_capacity` >= ' . $cap; // subject has to have required capacity, at lest 1
$query .= ' WHERE ' . implode(' AND ', $where);
$query .= ' GROUP BY `sbj`.`id` '; // prevent for duplicities provided by joins
if($query2 !=""){
$query .= $query2;
}else{
$query .= ' ORDER BY `sbj`.`ordering` ASC ';
}
//$query .= $query2;
if ($this->_lists['date_from'] || $this->_lists['date_to']) // parent select to test if subject is not engaged with reservations
$query = 'SELECT * FROM (' . $query . ') AS `s` WHERE `isEngaged` = 0 OR `isEngaged` IS NULL';
}
return $query;
}
