Mysql Searching in PHP

meghatronic · 21 Oct 2006 at 12:18

Why can't I get the folowing search form to work?

Contents of search.php:

'field' Field (name="field") - contains the fields from the db in drop down list

'what' field (how to search):

Code:

<select name="what">
<option selected="" value="%term%">Contains</option>
<option value="term">Equals</option>
<option value="term%">Starts with</option>
<option value="%term">Ends with</option>
</select>

'text' field - (name="find") what to search for

Contents of searchprocess.php (called as form action from above):

Code:

<?
$find = $_POST['find'];
$what = $_POST['what'];
$field = $_POST['field'];

if ($find == "") 
{ 
echo "<p>You forgot to enter a search term"; 
exit; 
} 

mysql_connect("localhost",$user,$password);
@mysql_select_db($database) or die("Unable to select database");

$find = strtoupper($find); 
$find = strip_tags($find); 
$find = trim ($find); 

$query="SELECT * FROM 'data' WHERE $field LIKE'%$find%'";


$data =mysql_query($query);
$num=mysql_numrows($data);


while($result = mysql_fetch_array( $data )) 
{ 
echo $result['field 1'];
echo $result['field 2'];  
\\etc......
}


if ($num == 0) 
{ 
echo "Sorry, but we can not find an entry to match your query<br><br>"; 
} 

echo "<b>Searched For:</b> " .$find; 

?>

Output I get:

Code:

Warning: mysql_numrows(): supplied argument is not a valid MySQL result resource....

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource....
Sorry, but we can not find an entry to match your query

Searched For: blah

Any ideas?

edit - I am guessing the problem is with "$query="SELECT * FROM 'data' WHERE $field LIKE'%$find%'";"......?

Augmented · 21 Oct 2006 at 13:00

jdickerson said:
edit - I am guessing the problem is with "$query="SELECT * FROM 'data' WHERE $field LIKE'%$find%'";"......?

Looks like it. There needs to be a space after LIKE:

Code:

$query="SELECT * FROM 'data' WHERE $field LIKE '%$find%'";

If something's not working with a MySQL query, always try dumping the error out to screen or file, e.g.

Code:

$data = mysql_query($query) or die(mysql_error());

Ensure you add some proper precautionary measures, and sanitise those variables you're using in the query (mysql_real_escape_string(), for example), if you're putting that on a live production site.

meghatronic · 21 Oct 2006 at 13:12

Augmented said:
Looks like it. There needs to be a space after LIKE:

Code:

$query="SELECT * FROM 'data' WHERE $field LIKE '%$find%'";

Didn't change anything, unfortunately.

Ensure you add some proper precautionary measures, and sanitise those variables you're using in the query (mysql_real_escape_string(), for example), if you're putting that on a live production site.

It isn't going live - BUT I might as well do things a bit better.... could you explain this a bit more later perhaps?

Code:
If something's not working with a MySQL query, always try dumping the error out to screen or file, e.g.

Code:

$data = mysql_query($query) or die(mysql_error());

Errot report says:

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 ''data' WHERE Keyword LIKE '%AS%'' at line 1.

I'm concerned about the fact it is ''data' rather than 'data' and '%AS%'' rather than '%AS'..... (AS was what I typed in the search box) is that wrong? seems it...

meghatronic · 21 Oct 2006 at 13:15

I've changed it to:

$query="SELECT * FROM data WHERE '$field' LIKE '%$find%'";

But now it won't 'find' anything...

EDIT - I think I know why... my columns are called 'HIV-1_Prot_Acc'... would this create a problem? As when I use $query="SELECT * FROM data WHERE $field LIKE '%$find%'"; I get "Unknown column 'HIV' in 'where clause'"

meghatronic · 21 Oct 2006 at 13:38

Managed to get it to work - a little.

Using:

$query="SELECT * FROM data WHERE upper('$field') LIKE '%$find%'";

But it still won't work fully.. i.e. can't 'search'... only if I put "1" in the field...

i.e. one field contains value "11676".. if 'search for 1 it will display it.. if i search for 11676 or 11 etc.. it won't :S

meghatronic · 21 Oct 2006 at 14:23

I think it is to do with the column names...

If I pass

Code:

$data = mysql_query("SELECT * FROM data WHERE upper($field) LIKE'%$find%'") or die(mysql_error());

it doesn't work for those fields where the column is xxx-1 etc... e.g.
The column is "HIV-1_TaxID", *** error is "Unknown column 'HIV' in 'where clause'"

Code:

$data = mysql_query("SELECT * FROM data WHERE upper('$field') LIKE'%$find%'") or die(mysql_error());

Makes it work - but not search properly...

meghatronic · 21 Oct 2006 at 14:52

Sorted it by just renaming the fields and recoding everything else...

1) For future reference, why won't php/mysql handle "-"s and "_"s?

2) Also, why can't I get tabs to work:???

Code:

echo "<b>Searched for:</b> \"" .$find. "\"\t<b>Results found:</b> <i>" .mysql_num_rows($data). "</i><br><br>";

3) I am searching by field, however, how can I search for "all" fields at once - as an option?

Augmented · 21 Oct 2006 at 15:27

jdickerson said:
1) For future reference, why won't php/mysql handle "-"s and "_"s?

Should be okay, as long as the table or fieldname is `quoted` and \'escaped\' correctly.

2) Also, why can't I get tabs to work:???

Extraneous whitespace [multiple spaces, tabs etc.] is automatically collapsed to single space when rendered, unless you specifically tell the user-agent to preserve it. You can do this with the <pre> element or using the CSS white-space: pre; property.

3) I am searching by field, however, how can I search for "all" fields at once - as an option?

Youy can use boolean operators to extend your WHERE clause, like an OR:

Code:

SELECT * FROM data WHERE (field1 LIKE '%$find%') or (field2 LIKE '%$find%');

meghatronic · 21 Oct 2006 at 17:20

Bingo bingo and bingo

It is fun this php malarky....