:: search problem

[phprock]

I m getting strange problem. I want to search either in All category or in certain category.

When i search in all category it works how ever when search in only certain category, i get this error

ErrorUnknown column 'mysearch words' in 'where clause'

Here is my code .

<?

global $link;

$q=addslashes($_POST["q"]);
$cat=addslashes($_POST["cat"]);

include("header.php");

if(empty($q)){
showerror("02");
exit;
}
echo"$q and $cat";
$search = '';
$the_array = explode(' ', $q);
$i = 0;

foreach( $the_array AS $t ){
if( $i == 0 ){
$search .= " '%$t%' ";
$i = 1;
}else{
$search .= " OR car_title LIKE '%$t%' ";
}
}

$query = "select *";
$query.="FROM  car (where car_title like $search)";


if(!empty($cat) && $cat!='all') {
$query .= "AND car_make like '%$cat%'";
$query .= "AND car_features  like '%$search%'";
}

$result=mysql_query($query,$link) or die("Error".mysql_error());
$found=mysql_num_rows($result);

echo"Found $found";


include("footer.php");
?>

[Weedpacket]

Perhaps $search should be inside quotes in the query?

[phprock]

Thank you for the reply but its still the same.

I have noticed When i search for numbers(ie.121345) it does not give me that error.

So, search in all category is working but search in a particular category is not working ?

When I search for "Toyotoa" in a particular category I get this error

Error :Unknown column 'Toyota' in 'where clause'

Here is my table structure.

CREATE TABLE vehicle (
  vehicle_id int(11) NOT NULL auto_increment,
  vehicle_name varchar(100) NOT NULL default '',
  vehicle_img varchar(100) NOT NULL default '',
  vehicle_description text NOT NULL,
  PRIMARY KEY  (vehicle_id)
) TYPE=MyISAM CHARSET=latin1;

CREATE TABLE make (
  make_id int(11) NOT NULL auto_increment,
  make_vehicle int(11) NOT NULL default '0',
  make_name varchar(100) NOT NULL default '',
  PRIMARY KEY  (make_id)
) TYPE=MyISAM CHARSET=latin1;

#  make_vehicle is Foreign key as vehicle_id from vehicle.

CREATE TABLE car (
  car_id int(11) NOT NULL auto_increment,
  car_title varchar(100) NOT NULL default '',
  car_make int(11) NOT NULL default '0',
  car_model varchar(100) NOT NULL default '',
  car_member int(11) NOT NULL default '0',
  PRIMARY KEY  (car_id)
) TYPE=MyISAM CHARSET=latin1;

#car_make is foreign key -Make_id from Make table  

you can see in the table structure that car_make field is subcateogry of Make table and make is sucategory of Vehicle.

Thank you

[Weedpacket]

Originally posted by phprock
Thank you for the reply but its still the same.

I have noticed When i search for numbers(ie.121345) it does not give me that error.

Which makes it sound like "it's still the same" because it wasn't changed.

$query.="FROM  car where car_title like '$search'";

If the field is a character type, then search terms need to be quoted.

[phprock]

Thank you again but this time even i can't search in "all" category.

Here is my out put when i search for "toyota" in all category .

Search Toyota in Category all
--------------------------------------------------------------------------------
Error :Unknown column 'Toyota' in 'where clause'

And when I search in a perticular category

Search Toyota in Category 3
--------------------------------------------------------------------------------
Error :Unknown column 'Toyota' in 'where clause'

Here is my current code
<?php

require_once("mainfile.php");
global $link;

$q=addslashes($_POST["q"]);
$cat=addslashes($_POST["cat"]);

include("header.php");

if(empty($q)){
showerror("02");
exit;
}
echo"Query : $q and Category :$cat<hr>";
$search ="";
$the_array = explode(' ', $q);
$i = 0;

foreach( $the_array AS $t ){
if( $i == 0 ){
$search .= " '%$t%' ";
$i = 1;
}else{
$search .= " OR car_title LIKE '%$t%' ";
}
}

$query = "select *";
$query.="FROM  car where car_title like '$search'";


if(!empty($cat) && $cat!='all') {
$query .= "AND car_make like '%$cat%'";
$query .= "AND car_features like '%$search%'";
}

$result=mysql_query($query,$link) or die("Error :".mysql_error());
$found=mysql_num_rows($result);

echo"Found $found";

include("footer.php");

?>

[phprock]

My search form

<!--search form start -->
<form action="index.php?file=search" method="post">
<input type="hidden" name="file" value="search">
<table align="center">
  <tr>
    <td>
      <b>Search for</b>
      </td>
    <td><input type="text" size="20" name="q"></td>
    <td><b>in</b></td>
    <td><select  name="cat">
    <option value="all" selected>All Sections</option>
    <option value="1">Cars</option>
    <option value="2">Motorcycles</option>
    <option value="3">Recreational Vehicles</option>
    <option value="4">Sport Utility and Jeeps</option>
     <option value="5">Trucks</option>
     <option value="6">Vans</option>
    </select>
</td>
<td>
<input type="submit" value="Search!"></td>
  </tr>
</table>
</form>
<!--search form end -->

[Weedpacket]

Have you looked at the query you're building?
$result=mysql_query($query,$link) or die("Error in query: ".$query);

[phprock]

That was a good tenique to see the actual query. Thanks for the tips.

But the problem is still there

When i search for Toyota in categorry 1

  Search Toyota in Category 1
--------------------------------------------------------------------------------
Error in query: select * FROM car where car_title like '%Toyota%' AND car_make like '%1%' AND car_features like '% '%Toyota%' %' 

When i search for "Toyota Hilux surf ssrx 1993" in category 1(car)

   Search Toyota Hilux surf ssrx 1993 in Category 1
--------------------------------------------------------------------------------
Error in query: select * FROM car where car_title like '%Toyota%' OR car_title LIKE '%Hilux%' OR car_title LIKE '%surf%' OR car_title LIKE '%ssrx%' OR car_title LIKE '%1993%' AND car_make like '%1%' AND car_features like '% '%Toyota%' OR car_title LIKE '%Hilux%' OR car_title LIKE '%surf%' OR car_title LIKE '%ssrx%' OR car_title LIKE '%1993%' %' 

[phprock]

The following code working with no errors however, it shows the same result when i search in category 1 and category 2.

in category there is 13 matches found which is right ..but there is no records in category 2. but still shows 13 records when i search in empty category (ie.category 2).

$search ="";
$the_array = explode(' ', $q);
$i = 0;

foreach( $the_array AS $t ){
if( $i == 0 ){
$search .= " '%$t%' ";
$i = 1;
}else{
$search .= " OR car_title LIKE '%$t%' ";
}
}

$query = "select *  ";
$query.="FROM  car where car_title like $search";
$query .= "AND car_features like $search";


if(!empty($cat) && $cat!='all') {
$query .= " AND car_make like $cat";

}


$result=mysql_query($query,$link) or die("Error in query: ".$query);
$found=mysql_num_rows($result);
echo"Found $found";

[leatherback]

Hi,

Looks like your code it geting too cluttered to see your own typos? ;-)

Anywaytry this (Which I always prefer):
Add brackets around the statements, and newlines too;

Your query would look like:

Select * from TABLE where (
(car_name like '%$term%') or
(car_make like '%$term%') 
// -- so on --
)

That would pick up misquotations much easier.
In your case, hae a look at the last part of the query:

AND car_features like '% '%Toyota%' %

[phprock]

Thank you for the reply..

All I want to search in all category or in a particular category.

why my query

if(!empty($cat) && $cat!='all') {
$query .= " AND car_make like '$cat'";

}

so, if searach in category 1 or cateogry 2 i get the same result ,even though category is empty.

[bunner_bob]

Am I wrong, or does this:
(snip)

...AND car_features like '% '%Toyota%' OR ...

have an extra single quote/percent sign in it (taken from your DIE statement).

This one too:
(snip)

AND car_features like '% '%Toyota%' %'

The extra single quotes are closing the preceding quotes, so it thinks 'Toyota' is a column name, not search criteria.

I think it's caused by this line of code:

$search .= " '%$t%' ";

Just a guess - I'm no expert on this.

• Bob

[phprock]

Ok guys i don't see any more error . I can search car_title in all category or in a particular category... but

global $link;

$q=addslashes($_POST["q"]);
$cat=addslashes($_POST["cat"]);

include("header.php");

if(empty($q)){
showerror("02");
exit;
}
echo"Search  $q in Category $cat<hr>";
$search ="";
$the_array = explode(' ', $q);
$i = 0;

foreach( $the_array AS $t ){
if( $i == 0 ){
$search .= " '%$t%' ";
$i = 1;
}else{
$search .= "AND car_title LIKE '%$t%' ";
}
}

$query = "select *  ";
$query.="FROM  car where car_title like $search";

if(!empty($cat) && $cat!='all') {
$query .= "AND car_make IN(SELECT make_id FROM make WHERE make_vehicle =$cat)";

}

$result=mysql_query($query,$link) or die("Error in query: ".$query);
$found=mysql_num_rows($result);
echo"Query $query";
echo"Found $found";

but if i
I changed

$search .= "AND car_title LIKE '%$t%'

to

$search .= "OR car_title LIKE '%$t%'

The following condition is ignored

$query .= "AND car_make IN(SELECT make_id FROM make WHERE make_vehicle =$cat)";

And also I want to search car_features field , so if i add

$query.="AND car_features like '$search'";

I get no records..