Search Feature

[undecided]

I want to put a search feature into my website that consists of 4 database and shown below

format = table - field1,field2

article - title, contents
writers - name
news - title, contents
category - category

so if someone types 1 or more keywords into the search box, the script will search all the tables,fields as shown before and display the results all on one page.

here is a bit of sql that i did, but it doesnt work.. please help!

$sql = "SELECT * FROM article,writers,news,category WHERE 1";
 
for($i=0;$i<count($_POST['searchfor']);$i++) {
$sql .= "OR `article`.title LIKE '%$_POST['searchfor'][$i]%' ";
$sql .= "OR `article`.contents LIKE '%$_POST['searchfor'][$i]%' ";
$sql .= "OR `news`.title LIKE '%$_POST['searchfor'][$i]%' ";
$sql .= "OR `news`.contents LIKE '%$_POST['searchfor'][$i]%' ";
$sql .= "OR `writers`.name LIKE '%$_POST['searchfor'][$i]%' ";
$sql .= "OR `category`.category LIKE '%$_POST['searchfor'][$i]%' ";

} 

[biggulp]

Enclose the $_POST['searchfor'] with braces, e.g.:

{$_POST['searchfor']}

[Gybbyl]

No, no, no -- I'm assuming that searchFor is your textbox, right? You need to explode that first:

$vars = explode( " ", $_POST['searchFor'] ); 

Put that at the top of your scrpit -- this will break all of the words that are seperated by spaces in the box into an array, and then you can count the array.

$sql = "SELECT * FROM article,writers,news,category WHERE 1";

for( $i = 0; $i < count( $vars ); $i++) {
    $sql .= "OR `article`.title LIKE '%$_POST['searchfor'][$i]%' ";
    $sql .= "OR `article`.contents LIKE '%$_POST['searchfor'][$i]%' ";
    $sql .= "OR `news`.title LIKE '%$_POST['searchfor'][$i]%' ";
    $sql .= "OR `news`.contents LIKE '%$_POST['searchfor'][$i]%' ";
    $sql .= "OR `writers`.name LIKE '%$_POST['searchfor'][$i]%' ";
    $sql .= "OR `category`.category LIKE '%$_POST['searchfor'][$i]%' ";
} 

If I am correct, that should be what you are looking to do.

I think you are assuming that your user is smart enough to enter all of the data into that field in the correct order -- You'd better replan that one. Users can find bugs/leaks that you never thought possible. =]

Alternatively, you can go with my favorite -- The foreach construct:

$sql = "SELECT * FROM article,writers,news,category WHERE 1";

foreach( $vars as $key => $value ) {
    $sql .= "OR `article`.title LIKE '%$_POST['searchfor'][$value]%' ";
    $sql .= "OR `article`.contents LIKE '%$_POST['searchfor'][$value]%' ";
    $sql .= "OR `news`.title LIKE '%$_POST['searchfor'][$value]%' ";
    $sql .= "OR `news`.contents LIKE '%$_POST['searchfor'][$value]%' ";
    $sql .= "OR `writers`.name LIKE '%$_POST['searchfor'][$value]%' ";
    $sql .= "OR `category`.category LIKE '%$_POST['searchfor'][$value]%' ";
} 

[Icheb]

I assume the variable is $_POST['searchfor'][$value] , so try this:

$sql .= "OR article.title LIKE '%". $_POST['searchfor'][$value] ."%' "; 

If you do it like you do, PHP evaluates the content of $_POST['searchfor'] and then puts the content of $value in brackets after the first variables content.
PHP can't know what you want to evaluate, so you have to tell it just that.

[undecided]

Thank you, now the next step is to display the results according to the table its getting the records from,
so for an example of the results page:-
news
___________________________
there are 0 related to news

articles
_______________
found1


how can i organise the results page like this?
thanks

[silan]

One way would be to query each table seperately

[Jeff Lange]

I'd also look at using Fulltext indexes. Using LIKE with a % as the first character forces it to never use indexes, and always search the disk, and it will be quite slow once you get large databases/users.