MySQL query not shows the category

PHP
1

[B] Hi, I am trying to process this search in a MySQL database of gps tracks by category , my table is that :

CREATE TABLE geo_routes (
id int(11) NOT NULL auto_increment,
name varchar(200) NOT NULL default ‘’,
author varchar(80) NOT NULL default ‘’,
description text NOT NULL,
category varchar(100) NOT NULL default ‘’,
property int(11) NOT NULL default ‘0’,
time int(11) NOT NULL default ‘0’,
downloads int(8) NOT NULL default ‘0’,
visits int(8) NOT NULL default ‘0’,
PRIMARY KEY (id),
FULLTEXT KEY search (name,description)
) ENGINE=MyISAM AUTO_INCREMENT=56 DEFAULT CHARSET=latin1 AUTO_INCREMENT=56 ;

My select form is here –
http://djalmabina.100webspace.net/Tours&Hotels/index.php


<form action="<?php  echo GEO_URL_ROOT.'/category.php';?>" method="POST">
       <select multiple name="category[]" style="width:150px;height:260px;">
       <option value="motor bike">Motor Bike</option>
	<option value="biking">Biking</option>
	<option value="mountain bike">Mountain Bike</option>
        <option value="trekking">Trekking</option>
	<option value="runner">Runner</option> 
	<option value="off road">Off Road</option>
		</select>    <br /> 
         <input type="submit" name="submit" value="Enviar">  
</form>

This is the php script that process the variable “category” :



include_once('overall.php');
include_once('core/geodb.class.php');
include_once('html/route.php');
include_once('html/general.php');
$database = new GeoDB;
$database->geoDB();

if($geoUser) $user = $database->getUser($geoUserName,$geoUserPass);
else $user = false; 

   $cat=$_POST['category'];
   $cat=$database->getRouteByCat($cat);   
    
  if(!empty ($cat)) 
    {
       header("Location: ".GEO_URL_ROOT."/index2.php?s=$cat");  
    }

And that is the php script makes the query into database :


   function getRouteByCat($cat)  
        {
          if(!is_array($cat)) return false;          
          $query = "SELECT * FROM geo_routes";  
              foreach ($cat as $c) { 
          $query.="WHERE category LIKE ".$this->escape($c)." ORDER BY id ASC";       
              }             
                return $this -> query($query);  
	}

However when I run the search and try select the category , for example : “trekking” , the web page -
http://djalmabina.100webspace.net/Tours&Hotels/category.php
return this error :

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 ‘LIKE trekking ORDER BY id ASC’ at line 1

Please  what's  wrong  in  the   MYSQL  query  ?   thanks.

[/B]

2

Thanks for help ! I am trying to send this variable $cat to front-end and select the categories each one from this form : motor bike,biking,mountain bike,trekking, runner,off road however
it displays the categories listing by genre but ALL together.
The form that perform the selection is :

<form action=“<?php echo GEO_URL_ROOT.‘/index.php?s=category’; ?>” method=“POST”>
<select multiple name=“category” style=“width:150px;height:260px;”>
<option value=“motor bike”>Motor Bike</option>
<option value=“biking”>Biking</option>
<option value=“mountain bike”>Mountain Bike</option>
<option value=“trekking”>Trekking</option>
<option value=“runner”>Runner</option>
<option value=“off road”>Off Road</option>
</select> <br /> <br />
<input type=“submit” name=“submit” value=“Submit”>
</form>

And the script to get the query :


<?php 
   elseif(!empty($_GET['s'])) {
        $cat = $_POST['category']; 
        $cat =$database->getRouteByCat($cat);  
        $category=$cat; 
       
	switch($_GET['s']) {
		case 'name':
			$order = 'name ASC';
			$title = 'Tours  and  Hotels';
			break;
              
                case 'category':
                        $order = '$category';
                        $order = 'category DESC';
			$title = 'Category';
			break;  
                 
		case 'top':
			$order = '(downloads*10 + visits) DESC';
			$title = ' Rating';
			break;
		default:
			$order = 'id DESC';
			$title = 'Last Routes';
	}
	 

?>

When i select some option for example : “trekking”, the page shows :
http://trekhotel.com/bina/index.php?s=category

3 

 function getRouteByCat($cat)   {         
    if(!is_array($cat)) return false;                  
    $query = "SELECT * FROM geo_routes";             

    $filters = array();
   
    foreach ($cat as $c) {  
    $filters[] ="category LIKE '&#37;".$this->escape($c)."%'";              
      }   

      $query.=   (empty($filters)?'':' WHERE '.implode(' AND ',$filters)).' ORDER BY id ASC';                     
      return $this -> query($query);      
    }
4

put a space between geo_routes and WHERE

and don’t put WHERE in a foreach loop, because you can only use the WHERE keyword once – after that, additional conditions are appended with ANDs and/or ORs

:slight_smile:

5 

 function getRouteByCat($cat)   {         
    if(!is_array($cat)) return false;                  
    $query = "SELECT * FROM geo_routes";             

    $filters = array();
   
    foreach ($cat as $c) {  
    $filters[] ="category LIKE '&#37;".$this->escape($c)."%' "              
      }   

      $query.=   (empty($filters)?'':' WHERE '.implode('AND',$filters)).' ORDER BY id ASC';                     
      return $this -> query($query);      
    }
6

[B]
OK. I fix the php function below putting the “%” in the query :


 function getRouteByCat($cat)         
 {         
    if(!is_array($cat)) return false;                  
    $query = "SELECT * FROM geo_routes";                

    foreach ($cat as $c) {  
    $query.="WHERE category LIKE '%".$this->escape($c)."%' 
       ORDER BY id ASC";                     
      }                             
         return $this -> query($query);      
    }

However the category.php return same error :

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 ‘LIKE ‘%trekking%’ ORDER BY id ASC’ at line 1

Thanks.
[/B]

7

LIKE trekking is wrong

try LIKE ‘%trekking%’