search results for multiple columns

[jonso100]

Hi

I have a simple search of a 4 col table id | partno | model | description.

My current code looks for the search term of my form in each col and displays the results.

$result=mysql_query("SELECT * FROM table_name WHERE partno LIKE '%term%' OR model like '%$term%' OR description like '%$term%' ORDER BY partno",$connect) or die(mysql_error());

 

If the following two rows in the table were:

 

1 | 1234 | model 3110 | lcd

2 | 5678 | model 2660 | flex

 

at the moment if the search was for "2660 lcd" I'd get no results - how do I get both entries?

I've read that I need to explode the search term or be using fulltext but not sure what that means (newbie).

Does anyone know of what I should be searching google for or any pointers most appreciated.

 

cheers

 

 

[mapleleaf]

Like will return on 2660 and not on 2660 lcd because the % only allows any character after or before the string.

You will need to explode on the space and commas as people may put them in.

Then query for each on the array.

 

[jonso100]

thanks for the info.

I've now got it the code below. For a search of flex 95 this outputs:

select * from parts_in_stock where description like '%flex%' or model like '%flex%' or description like '%95%' or model like '%95%' 

 

What i want is:

select * from parts_in_stock where description like '%flex%' and model like '%95%' or description like '%95%' and model like '%flex%' 

 

Does anyone have any pointers?

					$todo=$_POST['todo'];
				if(isset($todo) and $todo=="search"){
				$search_text=$_POST['search_text'];
				$type=$_POST['type'];

				$search_text=ltrim($search_text);
				$search_text=rtrim($search_text);

					if($type<>"any"){
				$query="select * from parts_in_stock where description='$search_text'";
						}else{
				$kt=split(" ",$search_text);//Breaking the string to array of words
				// Now let us generate the sql 
							while(list($key,$val)=each($kt)){
				if($val<>" " and strlen($val) > 0){$q .= " description like '%$val%' or model like '%$val%' or ";}

							}// end of while
				$q=substr($q,0,(strLen($q)-3));
				// this will remove the last or from the string. 
				$query="select * from parts_in_stock where $q ";

 

 

[mapleleaf]

select * from parts_in_stock where (description like '%flex%' and model like '%95%') or (description like '%95%' and model like '%flex%')

is I think what you need

 

 

[aeroswat]

What you need to do is explode your search string into an array with a space being the delimiter like this

$searchstring = "This is a search";
$arr = array();
$arr = explode(' ', $searchstring);

 

Next you will need to insert them into an IN keyword for each field so maybe something like this

 

$fields = array('partno','Model', 'description');
$qry = SELECT * FROM parts_in_stock WHERE";
foreach($fields as $f) {
   $qry .= " " . $f . " IN (";
   foreach($arr as $a) {
      $qry .="'" . $a . "',";
   }
   $qry = substr($qry,0,-1);
   $qry .= ") OR"
}
$qry = substr($qry,0,-3);
$qry .= "ORDER BY partno";

 

This hasn't been tested but it should create a qry that looks something like this

SELECT * FROM parts_in_stock WHERE partno IN ('this', 'is', 'a', 'search') OR Model IN ('this', 'is', 'a', 'search') OR description IN ('this', 'is', 'a', 'search')

 

[aeroswat]

My previous solution will only pull up results where the field matches exactly one of those things. If you need 2660 to match the string model 2660 then you are going to need to use the LIKE keyword in the loop instead. Let me know if you need that.

[aeroswat]

If you need it the other way then you will have to change it to something like this but the query is going to be ugly as fug

 

$fields = array('partno','Model', 'description');
$qry = SELECT * FROM parts_in_stock WHERE";
foreach($fields as $f) {
   foreach($arr as $a) {
      $qry .= " " . $f . " LIKE '%" . $a . "%'";
   }
   $qry .= " OR"
}
$qry = substr($qry,0,-2);
$qry .= "ORDER BY partno";

 

and of course don't forget to do the actual database query

$result = mysql_query($qry);

[jonso100]

thanks aeroswat, but I can't get your code to work. I'm now not sure what I've asked for is what I really want!

For example, if I had the following in my table:

 

partno  |  model  |  description

 

123          6500        flex

234          6500        cover

345          N80          cover

 

what i want to happen is:

 

if search for "cover", just the entry 2 and 3 are returned.

if search for "6500", just the entry 1 and 2 are returned.

if search for "6500 cover" just entry 2 is returned.

 

At present if I search "6500 cover" I get all three. In fact I don't mind that, but I want entry 2 to be top of the list.

 

Any help, pointers to tutorials etc much appreciated.

 

 

[aeroswat]

thanks aeroswat, but I can't get your code to work. I'm now not sure what I've asked for is what I really want!

For example, if I had the following in my table:

 

partno  |  model  |  description

 

123          6500        flex

234          6500        cover

345          N80          cover

 

what i want to happen is:

 

if search for "cover", just the entry 2 and 3 are returned.

if search for "6500", just the entry 1 and 2 are returned.

if search for "6500 cover" just entry 2 is returned.

 

At present if I search "6500 cover" I get all three. In fact I don't mind that, but I want entry 2 to be top of the list.

 

Any help, pointers to tutorials etc much appreciated.

 

Getting it to display all 3 with entry 2 at the top of the list will be difficult because it will require you to load your results into an array and do extra work on it before you get it organized the way you want it to. Getting it to display only entry 2 would require you to just change the logic a little bit

 

$fields = array('partno','Model', 'description');
$qry = SELECT * FROM parts_in_stock WHERE";
foreach($fields as $f) {
   $qry .= " ("
   foreach($arr as $a) {
      $qry .= " " . $f . " LIKE '%" . $a . "%' OR";
   }
   $qry = substr($qry,0,-2);
   $qry .= ") AND"
}
$qry = substr($qry,0,-2);
$qry .= "ORDER BY partno";

[jonso100]

thanks for the quick reply.

I'm getting an "unexpected T_FOREACH" on this line:

foreach($arr as $a) {

 

my code is:

</php
				$searchstring = $_GET['term'];
				$arr = array();
				$arr = explode(' ', $searchstring);

				$fields = array('partno','model', 'description');
				$qry = "SELECT * FROM parts_in_stock WHERE";
				foreach($fields as $f) {
				   $qry .= " ("
				   foreach($arr as $a) {
				      $qry .= " " . $f . " LIKE '%" . $a . "%' OR";
				   }
				   $qry = substr($qry,0,-2);
				   $qry .= ") AND"
				}
				$qry = substr($qry,0,-2);
				$qry .= "ORDER BY partno";


				$nt=mysql_query($qry);
				echo mysql_error();
				while($myrow=@mysql_fetch_array($nt))
				{
?>
some formatting
<?php echo $myrow['partno'] ?>
<?php echo $myrow['model'] ?>
<?php echo $myrow['description'] ?>
<?php
}
?>

 

 

[aeroswat]

You are missing some semi-colons after the $qry concatenations. There are 2 of them inside your foreach that are missing them.

[jonso100]

fantastic! got it working and when I search for "6500 cover" I get:

 

SELECT * FROM parts_in_stock WHERE ( model LIKE '%6500%' OR model LIKE '%cover%') AND ( description LIKE '%6500%' OR description LIKE '%cover%') ORDER BY partno

 

BUT (isn't there always!) if i search for just "6500" I get:

 

SELECT * FROM parts_in_stock WHERE ( model LIKE '%cover%') AND ( description LIKE '%cover%') ORDER BY partno

 

So on one word searches it needs to be OR rather than AND - am I missing something?

[aeroswat]

To fix that you can change this line

 

$qry .= ") AND";

 

to something like this

 

$qry .= (count($arr) > 1 ? ") AND" : ") OR");

[jonso100]

that's great! one more question:

 

I took out partno from the $fields and everything works. If I search for '6500 cover' in my real db I get 10 results and the query is this:

 

SELECT * FROM parts_in_stock WHERE ( model LIKE '%6500%' OR model LIKE '%cover%') AND ( description LIKE '%6500%' OR description LIKE '%cover%') ORDER BY partno

 

if I reinsert the partno into $fields I get no results and the query is this:

SELECT * FROM parts_in_stock WHERE ( partno LIKE '%6500%' OR partno LIKE '%cover%') AND ( model LIKE '%6500%' OR model LIKE '%cover%') AND ( description LIKE '%6500%' OR description LIKE '%cover%') ORDER BY partno

 

I can't work out why that would make it fail - if the partno has neither term surely it would be ignored?

 

[aeroswat]

Shit gotta think about that for a second. I forgot about that. The problem is none of your records have a partno that matches either of those so we have to figure our way around that obstacle