Searching a whole table (all fields)

Is there a way to do this?

[Malle]

Hi all,

after searching the web for over two hours without luck i am reeady to ask the experts :)

I want to search a table by a <input> string. Not only one Column but instead the whole table.

I know the following code doesn't work (or i wouldn't be posting here :) ) but there u will get the idea of what i mean:

 $sql = mysql_query("SELECT * FROM table WHERE * LIKE '%".$_POST['search']."%'"); 

I am not sure if it makes sense to you. I just want to be able to search for a word or just parts of a word. I have a big table and sometimes i am just too

<SELECT NAME="State_of_mind">
<OPTION VALUE="drunk">
<OPTION VALUE="stoned">
<OPTION VALUE="distracted">
<OPTION VALUE="lazy">
</OPTION>....

and then i want to be able to find the row(s) to which the word i was looking for belongs to.
So here is the price question:
Is it possible?

Thx
Malle

[Salsa]

I don't think you can use a wild card like that, Try:

$search = $_POST['search'];
$sql = mysql_query("SELECT * FROM table WHERE col_1 LIKE '%$search%' OR col_2 LIKE '%$search%' OR col_3 LIKE '%$search%' OR col_4 LIKE '%$search%' OR col_5 LIKE '%$search%' OR col_6 LIKE '%$search%'");

Surely your not too drunk OR stoned OR distracted OR lazy to type "OR" a few times.

You can then loop through each column of each result row, calling eregi() if you need to know which column the search word was in.

Also look into FULLTEXT as possibly a more powerful option, but you'd have to index all relevant columns as FULLTEXT to make best use of it, and you'd have pretty cumbersome indexes for your application.

[Malle]

Ah thx for the help :)

i alreeady was afraid that there is no easy way.
I could write a few OR np. As of now that would make 183 OR's however.

I will have a look in the fulltext search as u recomendet.

Thx
Malle

[Salsa]

WOW! That's a wide table!

Here's a lazy man's way:

$result = mysql_list_fields("database_name","table_name"); 
$sql = "SELECT * FROM table_name WHERE "; 
for($i = 0; $i < mysql_num_fields($result); $i++){ 
 $sql .= mysql_field_name($result,$i)." LIKE '%$search%' OR "; 
} 
$sql = substr($sql,0,-4); 
$result = mysql_query($sql);

[Salsa]

PS: Malle, how many rows do you have in that table? I hope it's not too many! In any case, I make no guarantees on the speed of this thing, and if it works for you, I wouldn't expect to find out until tomorrow!

Hopefully someone has a better plan.

I wish you well.

[Malle]

Well first off Thx a ton Salsa...that is a very clever little function.

i havn't tested it thoroughly yet but it does work for me :)

PS: Malle, how many rows do you have in that table? I hope it's not too many!

Well this will depend on the people filling out the form. The rows will increase the more people submit info.

In any case, I make no guarantees on the speed of this thing, and if it works for you, I wouldn't expect to find out until tomorrow!

This searchfunction is just the "last escape" in a row of "normal" search possibilities, meaning i wont use it often hopefully (its for the admin only so my host wont get flooded by the queries hehe). So your function is indeed useful to me.

As of now there are only 5 rows in the table for testing and the speed is quiet ok. I have yet to undergo a stresstest but that has to wait until people start to populate my little database.

For the current i thank you deeply because u solved my little problem quiet well.

Malle