problems selecting certain records from a database.

[natchattack]

I wasnt sure whether or not to put this in the database section but i think the problem is in my php code so i thought it would be best posted here.

I intend to use this as a simple search script. I want to compare what the user enters to the 'searchkey' field in the db and display the relevant records.

So if the user types in "mars saturn" my page will display from the db, records 1,3,5,6 and 7. Because somewhere in the searchkeys are those words.

The problem i have is that regardless of what is typed in, my page just automatically displays every record. Not very good as a search feature :p

My database table:

Table name: items

| ID | price | description |        searchkey        |  
------------------------------------------------------   
| 1  |  2.99 |  some_data  | mars saturn jupiter     |
| 2  |  4.99 |  some_data  | earth pluto             |
| 3  |  5.99 |  some_data  | venus earth mars neptune|
| 4  |  2.49 |  some_data  | uranus neptune mercury  |
| 5  |  9.99 |  some_data  | saturn                  |
| 6  |  0.49 |  some_data  | earth mars              |
| 7  |  5.67 |  some_data  | mars pluto              |
------------------------------------------------------

My php code:

//THERE IS A FORM ON ANOTHER PAGE WITH TEXTBOX NAMED "search",
//THAT COMES TO THIS PAGE.


$search = $_post['search'];
							
$words = EXPLODE(" ",$search);
 
FOR($i=0; $i<count($words); $i++)
{
  $query_string .= "LIKE '%".$words[$i]."%' OR ";
}
 
$query_string = SUBSTR($query_string, 0 ,STRLEN($query_string)-4);
							
$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ($sql_connect_error);
mysql_select_db($dbname);

							
$sql = "SELECT * FROM items WHERE searchkey ".$query_string." ";

My code for displaying the db records is spot on and works fine with no errors. Its just getting it to pick the right ones in the first place.

Hopefully its something simple, but i am still relatively inexperienced with combining php/sql.

Thanks in advance.

[natchattack]

Hmm my database example didnt display too good, i shall edit it now.

[mschroeder]

Hopefully this illustrates something that might get you on your way.
I also applied some corrections to your code in general.
Tried to comment as much as possible.

//THERE IS A FORM ON ANOTHER PAGE WITH TEXTBOX NAMED "search",
//THAT COMES TO THIS PAGE.

//Global variables should be CAPITALIZED functions should not unless they are capitalized.
$search = $_POST['search'];

//Get words from search string		
$words = explode(' ',$search);

//Drop any empty search terms and trim remaining ones to avoid any extra whitespace
$words = array_map('trim', array_filter($words));

//Format the search terms into a string surrounded by single quotes
//e.g. 'word1 word2 word3'
$search_terms = sprintf("'%s'", implode(' ', $words) );

//Connect to the database
//Should REALLY be using mysqli not mysql functions
$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ($sql_connect_error);
mysql_select_db($dbname);
						
//Do a Boolean full-text search on the searchkey column
//searchkey MUST have an index to work IN BOOLEAN MODE
//@see http://dev.mysql.com/doc/refman/5.1/en/fulltext-boolean.html
$sql = 'SELECT * FROM items WHERE MATCH(\'searchkey\') AGAINST ('.$search_terms.' IN BOOLEAN MODE)';

[natchattack]

That worked just as i had hoped it would. I was close but yet so far off at the same time. Many thanks for helping me out on this one, its saved me a lot of stress!

It dont like searching 3 lettered words but thats a fun relaxing problem for me to have a crack at next time i have a spare half hour.