Erro ao buscar dados não cadastrado

[lerf 0]

Estou com um erro que ao digitar alguma palavra no campo BUSCA que não esteja cadastrada no BD, ao invés de retornar a mensagem que não encontrou nada, ele me retorna esse erro:

 

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 '), 2) + POW(69.1 * ( - longitude) * COS(latitude / 57.3), 2' at line 2 SQL=SELECT a.*,latitude, longitude, SQRT( POW(69.1 * (latitude - ), 2) + POW(69.1 * ( - longitude) * COS(latitude / 57.3), 2)) AS distance FROM vp67k_jdbusiness_listings as a INNER JOIN vp67k_jdbusiness_listings_categories as b ON b.listing_id = a.id INNER JOIN vp67k_jdbusiness_categories as c ON c.id = b.category_id LEFT JOIN vp67k_jdbusiness_members AS d ON a.created_by = d.user WHERE ((d.end_date >= NOW() && d.state = 1) || a.is_admin = 1) AND a.state = 1 GROUP BY a.id HAVING distance < 1000000000 ORDER BY distance ASC,create_date desc

 

 

Código abaixo:

$search_term = $input->get('search_term');
        $search_category = $input->get('search_category');
        $search_zip = $input->get('search_zip');
        $search_city = $input->get('search_city');
        $search_state = $input->get('search_state');
        $search_city_state = $input->get('search_city_state');

        $search_distance = $input->get('search_distance');
        if($search_distance == 0){
            $search_distance = 1000000000;
        } else {
            $search_distance = $input->get('search_distance');
        }
        
        // Default Zipcode
        if(empty($search_zip) || !isset($search_zip)){
            $search_zip = '01913';
        }

        // Get Coords
        $coords = $this->getCoords($search_zip);
        // Get Latitude
        $lat = $coords['lat'];
        // Get Longitude
        $lng = $coords['lng'];

        $db = JFactory::getDBO();

        $query = $db->getQuery(true);
        $query->select("a.*,latitude, longitude, SQRT(
                    POW(69.1 * (latitude - $lat), 2) +
                    POW(69.1 * ($lng - longitude) * COS(latitude / 57.3), 2)) AS distance");
        $query->from("#__jdbusiness_listings as a");

        $query->innerjoin('#__jdbusiness_listings_categories as b ON b.listing_id = a.id');
        $query->innerjoin('#__jdbusiness_categories as c ON c.id = b.category_id');

        $query->group('a.id');

        // Filter by search term
        if (!empty($search_term)) {
            $search_term = $db->Quote('%' . $db->escape($search_term, true) . '%');
            $query->where('( a.company_name LIKE '.$search_term.'  OR  a.company_description LIKE '.$search_term.' )');
        }

         // Filter by search category
        if (!empty($search_category)) {
             $query->where('c.id = '.$search_category.' OR c.parent_category = '.$search_category.'');
        }

        // Bring in members table
        $query->leftJoin('#__jdbusiness_members AS d ON a.created_by = d.user');

        // Check System Type
        if($componentParams->get('system_type','free') == 'membership'){
            // Get only listings with valid members
             $query->where('((d.end_date >= NOW() && d.state = 1) || a.is_admin = 1)');
            // Member must be active
            //$query->where('c.state = 1');
        }

        // Only get published
        $query->where ( 'a.state = 1' );

        $query->having("distance < $search_distance");

        $query->order('distance ASC');

        //Custom Sorting
        $sort_by = $app->input->get('sort_by');
        $sort_order = $app->input->get('sort_order');
        if(isset($sort_by)){
            //Filter Order
            $query->order($sort_by.' '.$sort_order);
        } else {
            $query->order($componentParams->get('listing_order','create_date').' '.$componentParams->get('sorting','DESC'));
        }

        $db->setQuery($query);

[lokaodomau 61]

Notou que o valor das variáveis $lat e $lng não foram devidamente tratados?

SELECT
  a.*,
  latitude,
  longitude,
  SQRT( POW(69.1 * (latitude - ??), 2) + POW(69.1 * (?? - longitude) * COS(latitude / 57.3), 2)) AS distance
FROM vp67k_jdbusiness_listings as a
INNER JOIN vp67k_jdbusiness_listings_categories as b
  ON b.listing_id = a.id
INNER JOIN vp67k_jdbusiness_categories as c
  ON c.id = b.category_id
LEFT JOIN vp67k_jdbusiness_members AS d
  ON a.created_by = d.user
WHERE
  ( (d.end_date >= NOW() && d.state = 1) || a.is_admin = 1)
  AND a.state = 1
GROUP BY a.id
HAVING distance < 1000000000
ORDER BY
  distance ASC,
  create_date desc

[lerf 0]

Por favor, o moderador pderia excluir os tópicos duplicados, não sei oq aconteceu, mas postei ontem a noite e tava dando erro e, fiquei tentando e, acabou validando tudo. Obrigado.

 

 

lokaodomau, obrigado, vou ver verificar.

Consegui resolver o erro.

 

Na parte do código:

$query = $db->getQuery(true);
        $query->select("a.*,latitude, longitude, SQRT(
                    POW(69.1 * (latitude - $lat), 2) +
                    POW(69.1 * ($lng - longitude) * COS(latitude / 57.3), 2)) AS distance");
        $query->from("#__jdbusiness_listings as a");

a variável $lat esta lado errado.

 

ao invés de: POW(69.1 * (latitude - $lat), 2)

 

seria: POW(69.1 * ($lat - latitude), 2)

 

Mas desta forma, mesmo fazendo uma pesquisa com uma palavra que não esta no BD, sempre volta um resultado.

[lokaodomau 61]

Que bom que conseguiu resolveu, como você resolveu sozinho, o ideal é que marque sua resposta (#3) como resolvido! :D

[giesta 29]

Topicos duplicados removidos

[lerf 0]

Resolvi em partes,rsrsrsr.

 

Obrigado giesta.