Searching function help pls

[MarioPro]

Hello,

I'm trying to get a search system to work but I'm getting some odd values. What I need is to query my DB (MySql) for example the following keywords:

"Glass"

but I have "Glass", "Hourglass" and "Glass (material)"

How can I search for just Glass, or just Glass (material) or just Hourglass since I can already search for all of them.

Thanks in advance for your time and help

[rlemon]

you can refine your search to be case sensitve,

also, maybe try returing all applicable results (Glass, hourglass and Glass (material),
then trim out all characters other than glass and compare to see if you've returned the exact string.

[MarioPro]

Hi

Thanks for your reply.

Well, I don't know no more where to go. It seems that I'm loosing control and I think that this might be a series of errors.

I tryied this approach for text but it doesn't result very good.

I have the follwoing tables:

categories; keywords; concepts; styles; -> these are relational tables, this is, tables that have only the image_ID and category_ID etc.

What I'm tring to do is search for multiple entries comming from user's input in checkboxes. Well, now I found that I have to make it run on a sequence, like:

=> search the table CATEGORIES for the cat_ID '5' AND '45' etc.
=> in the results I'l have a first set of image ID's
=> I then have to search on table CONCEPTS for concept_ID's entered for each image_ID comming from the above results
=> this will give me a (perhaps) shorter number of results
=> from these results I will have to do the other searchs untill I have run on all tables and get the final filtered results.

I'm lost! :confused: :confused:

[Velox Letum]

SELECT * FROM table WHERE MATCH (material) AGAINST ('+Glass' IN BOOLEAN MODE);

This'll return such things as: Polished Glass, Glass, Broken Glass, Glass on fire, but not things like Glassware.

Check out the MySQL.com page on Boolean Full-Text Searches (http://dev.mysql.com/doc/refman/5.0/en/fulltext-boolean.html) and see what else you can do, but make sure to add a FULLTEXT index on the column you plan on using, or it could get really really slow.

[rlemon]

here is a great open source example of a search engine built in php.

http://www.cs.ioc.ee/~ando/sphider/

the software is called sphider and works almost exactly as you describe.

i suggest you take a look at their SQL and Table setup to better understand your own project.

I can fully understand how it can sometimes feel overwhelming when developing a large project alone. I have recently 'burnt out' on a project and have moved over to another.. so don't feel bad - happens to all of us.

[MarioPro]

Thanks both,
I had this thread as no immediate notice :confused: and just now seen the answers.

Velox -> That would be great if my server's MySql was already updated but the guys told me that just next year they will update to version 5 so I don't have the IN BOLEAN MODE :o
Even though I have prepared tables for that already.

rlemon -> At first sight this might be close but not as much. I'm downloading the code to see if I can drink some water over there.


I think that I'm almost close but I'm doing something that I'm getting little "trash" at the end.

I noticed that I had to add a "trash bag" to the 2nd query in oder to keep the non conformity ID's out of the rest of the query but... something is wrong over here.

Here's what I have for the first two tables (in this example: categories and keywords):



<?
// for table Catgeories where I searched for "Travel" and "Transportation"
foreach($catID as $k=>$v){
$query="SELECT * FROM photo_categories_rel WHERE cat_ID='$v' GROUP BY photo_ID ";
$result=mysql_query($query) or die("Can't select cats: ".mysql_error());
$rows1=mysql_num_rows($result);
if($rows1!==0){
while($fields=mysql_fetch_array($result)){
$found[]=$fields["photo_ID"];
}
}
}
// At this point I got the right values, this is, only images that meet the conditions

// troubleshooting only
$found=array_unique($found);
foreach($found as $key=>$value){
echo $key. " - ".$value."<br>";
}
echo $keyID."<br>";


// Now comes the problem
// I searched the Keywords table for images that met the imageID's from the above query (for example 5)
// on a list of 3 selected keywords.
// This should have gave me a total of 3 images (meeting the All categories AND All the keywords), but
// I got 6 images and not 3 because 3 of them met at least on of the keywords, and shouldn't as all final results
// should met ALL the conditions.
if(is_array($keyID)){
foreach($found as $k=>$v){
foreach($keyID as $key=>$value){
$query="SELECT * FROM photo_keywords_rel WHERE ";
if((is_array($found2)) && (in_array($v, $found2))){
$query.=" photo_ID='0' ";
} else {
$query.=" (photo_ID='$v' AND keyword_ID='$value') ";
$query.=" GROUP BY photo_ID ";
}
$result=mysql_query($query) or die("Can't select cats: ".mysql_error());
$rows=mysql_num_rows($result);
if($rows!==0){
while($fields=mysql_fetch_array($result)){
$found2[]=$fields["photo_ID"];
}
}
}
}
}
?>



Am I doing something wrong?? :confused: Thanks for any help

[Velox Letum]

You can use the query (with IN BOOLEAN MODE) on MySQL 4.x+, which means 4.0, 4.1, 5.0, 5.1, etc., it does not require MySQL 5.

[MarioPro]

Yes, but the server has 3.23.57 version :( so no bolean function present.

[Velox Letum]

3.23.57? Archaic! They really should upgrade.

[MarioPro]

Yeap! :rolleyes:

By the way, have seen my code above? Is it to much to ask you some help just to see if I'm reaaly near the solution or just messing more than I should: Thanks again.

Here's from where the inputs come from:
http://www.spsuicidologia.pt/search.gif

[rlemon]

I have to ask what the practical application of that search is?

because looking at that form it might be easier to take a different approach.

[MarioPro]

Hi rlemon,

Well, it is intended to search images on specific conditions. Images are uploaded through a backoffice page in which are set to which categories (one or more), belong, which keywords they have (one or more), which styles etc. Everytime an image is uploaded or edited it is possible to add new categories, keywords, styles, etc. if needed, so the reference tables for are being constantly updated with new items. For example, an image from Hawai that has an hawaian women: I already have the categories Hawai, Travel, Polynesia (to which it will belong, and I also have the keyword Women, but not "Polynesian Women" so it is added to the Keywords table and when image is submited the image_keyword_relation table will have the id's for the features that most describe that image. Now I might have 10 images of the same subject and for example one of them will include a Boat. When seaching for images on category "Hawai", "Travel", "Polynesia", that contain the keywords "Women", "Polynesian Women", the one that includes the Boat should not appear in the results.

Hope this helps, and thanks again.

[MarioPro]

Well, the example above was just illustrative since the application is to function in a specific theme, this is, a non-profit mental health organization. I gave this example because it was the one it best approaches the intended use.

Any hep here? Thanks.