simple search script

sgray

Heyas,

im in the proccess of writing a script that will search my database (name 'db'), table name 'frozenpea') and field name 'content'. Im hoping someone can help me out a bit. My code is as follows:

contents of search.php:

<?
mysql_pconnect("localhost","user","pass");
mysql_select_db("db");
$result = mysql_query ("SELECT * FROM frozenpea WHERE content LIKE '%$search%'");
while($r=mysql_fetch_array($result))
{

$whatevercolumn=$r["content"];
echo "$whatevercolumn";
echo "<br>";
}
else
{
echo "Nothing found";
}
?>

I run the script with the form:

I get the following error message when I use the form,

Parse error: parse error, unexpected T_ELSE in /var/www/frozenpea.net/htdocs/search.php on line 11.

Can anyone see where my problem lies?

thanks in advance,

Stewart

chriskl

You have an else without an if in your PHP. It's a PHP error, not a MySQL error.

Also, use FULLTEXT indexes in MySQL, not LIKE. It will be hundreds of times faster. It's in the manual.

Chris

sgray

im a bit of a newbie, can you please show me where you would use the IF statement, cheers. I think all my Mysql entries are standard text, can I convert the fields to FULLTEXT?

Thanks

MrAlaska

There probably is a better way to do it, but your if statement would look something like this. An empty $result 'might' throw an error which you can suppress with an @ if needed, but you can give this a try:

//untested syntax
if(mysql_num_rows($result)>0) {
	while($r=mysql_fetch_array($result)) { 
		$whatevercolumn=$r["content"]; 
		echo "$whatevercolumn"; 
		echo "<br>";
	}
} 
else { 
	echo "Nothing found"; 
}

The full text reference Chris was referring to is in your SQL query. It is much more efficient to search for a field entry that eqals what is entered instead of searching for a sub portion using the LIKE clause. (i.e. using WHERE content=$search instead of WHERE content LIKE '%$search%')Some applications you need it, though.

sgray

cheers for your input mr alaska. The script no longer returns errors but it doesnt return anything valid either, just a blank page.

you mention 'a better way' way of doing the search, would you be able to show me this way or point me in the direction of a tutorial or something to write it. I dont mind throwing away that script for something better/that works.

MrAlaska

I mentioned that there is probably a better way because I don't trust my judgement when I have been drinking whiskey, but if you are not generating errors then you just might be on the right track.

If it is returning a blank page, it is time to start debugging. Since the page is blank, you must be returning rows to drop into the loop but unable to display them for some reason. One thing you can try is to echo the SQL statement back, which is one reason people often make the sql statement a variable:

$sql="Select ....from....where...";
mysql_query($sql);

Then you can later echo it back while debugging to copy and paste, then run it directly on the database to see what it returns

On possible problem may be in referencing the fields by name, MySql might be set up to reference it by position.
Try changing:
while($r=mysql_fetch_array($result))
to:
while($r=mysql_fetch_array($result, MYSQL_ASSOC))

or referencing the value by its position in the array:
$whatevercolumn=$r[0];
or whatever position it occupies.

Also, if you only want that one field, there is no need to do a 'select *', you only need to ask for the field you want:
$sql="SELECT content FROM frozenpea WHERE content LIKE '%$search%'";

sgray

yipee, thanks a lot. Working nicely now!

one last thing, how would I make it so that it would return the name of the field which contains the 'content' ?. there are 2 fields in my db, 'name' and 'content'. I want the script to search in 'content' but return 'name' so it brings back the names of the sql entries which contains the search.

MrAlaska

Which method worked? I threw a few variations at you!

If you are still using 'select *' you already are pulling back the name, or you can go:
SELECT name, content FROM frozenpea WHERE content LIKE '%$search%'

You would collect it the same way, either by name:
$r[name]
or by position
$r[0] or $r[1]

sgray

how would I get the script to search multiple tables?,

I tried:

$result = mysql_query ("SELECT * FROM frozenpea, lyrics WHERE content LIKE '%$search%'");

but this returned an error. frozenpea and lyrics are the two table names.

Cheers

MrAlaska

Be careful, you are flirting with a cartesian product there!

To do it all in the same select statement you need to have somthing to join them on, such as a common ID in each table. Otherwise you will get every record found in one table joining itself to every record in the other table. (a Cartesian join)

For what you want to do, I believe you need to UNION or UNION ALL the tables, and my version of MySql does not support unions so I can't help with the code. I think the code would look something like, give it a try if you are on MySql 4 or better:

Select from.. where...
UNION ALL
Select from .. where..

There might be an efficient way using temporary tables, I dunno, I am still on the beginning of my learning curve and haven't started playing with temporary tables. At this point, I would probably end up simply making two seperate queries.

MrAlaska

I was mistaken when I took it upon myself to ascertain what Cris was speaking of when he talked about full text searches:
http://www.mysql.com/doc/en/Fulltext_Search.html

Damn, why do people let me get away with these things? I hate having to rat myself out.