Search result

[objelland]

Hi all

I am trying to manipulate the sarchresult.php page to get the desired search result.

What I want to do is to be able to search the category names from the search page. I believe I have added the possebility to do this by the sql statement underneath, but it gives me no searchresult as I search for a category name.
Can anyone give me a hint about what's wrong here:

//now setup the search query
	$select = $modules->call_hook('searchlistings_select', ''); // Call any module functions
		$sSQL="SELECT " .
"p.id, c.name AS category, p.owner, p.title, p.featured, p.section, p.shortDescription, p.display, " .
"p.description, p.price, p.dateadded, p.expiration, p.pBold, p.pHighlighted, " .
"u.state, u.city, u.country,u.phone " . $select .
"FROM ".PREFIX."products AS p ".$addSQL." " .
"LEFT JOIN ".PREFIX."users AS u " .
"ON p.owner = u.id " .
"LEFT JOIN ".PREFIX."categories AS c " .
"ON p.section = c.id " .
"WHERE p.expiration > NOW() AND p.display = 'Y' ";
$sSQL .= ( empty($arr_childs) ) ? '': " AND p.section IN (". implode(', ', $arr_childs) .")";
$sSQL .= ( empty($_REQUEST['phone']) ) ? '': " AND u.phone LIKE '%".safeAddSlashes($_REQUEST['phone'])."%'";
$sSQL .= ( empty($_REQUEST['adid']) ) ? '': " AND p.id = ".(int)$_REQUEST['adid'];
$sSQL .= ( empty($_REQUEST['state']) ) ? '': " AND u.state LIKE '%".safeAddSlashes($_REQUEST['state'])."%'";
$sSQL .= ( empty($_REQUEST['city']) ) ? '': " AND u.city LIKE '%".safeAddSlashes($_REQUEST['city'])."%'";
$sSQL .= ( empty($_REQUEST['country']) ) ? '': " AND u.country LIKE '%".safeAddSlashes($_REQUEST['country'])."%'";
$sSQL .= ( empty($_REQUEST['owner']) ) ? '': " AND p.owner = ".(int)$_REQUEST['owner'];
$sSQL .= ( empty($_REQUEST['searchtext']) ) ? '': " AND (p.title LIKE '%".safeAddSlashes($_REQUEST['searchtext'])."%' OR p.shortDescription LIKE '%".safeAddSlashes($_REQUEST['searchtext'])."%' OR p.description LIKE '%".safeAddSlashes($_REQUEST['searchtext'])."%' OR c.name LIKE '%".safeAddSlashes($_REQUEST['searchtext'])."%')";
$sSQL .= $modules->call_hook('searchquery_where', ''); // Call any module functions

As you can see I have added the c.name (category name) into the query and joined the query using category.id and produkt.section.
This shoul in theori work, but mayby my sql knowledge is to poor.

Thank you

Regards

Oyvind

[Lhotch]

I dont really understand what it is your trying to accomplish so cant really give a hint as to whats wrong. However, one thing that you may find helpfull when doing query stuff is to test the query your trying to build by using known values at the mysql command line or via phpmyadmin to make sure the query even works.

Once you build a query you know works, then I would insert an echo statement into the script after the query is built so you can see how its being built by the script with your variables. Keep in mind this may make your pages a bit ugly until you get ride of the echo statement but it can be a big help during development.

[objelland]

Hi

Thanks for your reply.
What I am trying to do is this:

I would like to be able to change the checkout process. In stead of seeing the categories listed, I would like to be able to search for them. All my categories will be zip codes. Each category will contain sub categories that are the Street names belonging to the zip code (parent category.). The search result of the category search should be every sub category belonging to a parent category

Oyvind

[Maffo]

Ok, I havent had a chance to look at this properly but what from what I can see you are editing the totally wrong search script.

I wouldnt try editing the search script as you are going to play havoc with the rest of your script. I would simply have a textbox and when submitted it searches and hopefully finds your post code.

Or even I would only have one great big category and simply use thepostcode that the user registered with.

[Greg H]

This is similar to what I am trying to achieve.

I think even better would be for it to search not just the category name but the "keywords" associated with it, as setup when making the category.

So if a user searches for a word, and that word is within either the category/sub category name OR the search keywords associated with it, a link to that category would appear above the usual search results.

I understand that "keywords" as setup when making the category are meta keywords, but it would be a very nice feature for the site search to include these words...somehow.

What I would be trying to achieve would be to make allowances for for sellers who do not place their ad correctly. For example if they do not include words in their title or description that buyers might use in their search, search will still provide links to the categories that contain words set for the category that they placed their listing in.

To clarify, say you have a category with the name "Wheels", a seller places an ad in that category, but does not use the word "wheel" in their ad. A buyer comes along and searches for "wheels" and does not find it.

Sure, they can search or browse the "wheels" category, but in my case I have a huge amount of categories, and selecting a category in search can be cumbersome.

Thanks

[objelland]

Thank you Greg, finally someone that understands what I am trying to say. Have you had any luck with this?
Have you tried any of the developers to get this done?

Regards
Oyvind

[Maffo]

I fully understand what you are trying to do but are trying to place a search function in the checkout page.

All you have to do is have a text input and a submit button.

When the search is entered it searches the category table and pulls out any similar postcodes.

For me though, I dont think I would set it up like that. I would write in a module to deal specifically with postcodes and leave the categories alone incase one day you decide that your products do actually need categorising.

[Greg H]

No, I haven't asked for this to be done, but I think it would improve the efficiency of search an enormous amount.

Thinking about more, IF it could be done using ONLY the "keywords", as setup when making a category, this would provide a huge amount of flexibility to the process. Using category names can be ambiguous, using just the keywords would be very efficient and flexible.
You could include any terms that users may search for looking for a product within any given category.

Ideally, I think it should work like this: When a user searches for a word that is contained within the "keywords" of any category, a link to that category is provided alongside/above/somewhere, with the usual search results showing normally.
Text with these links could say something like "Related Categories" or something like that.

I can see it now "Maffo's Super Search Mod"...come of Maff, get to work man!

I would contribute to the development costs if anyone is interested assuming its possible.

Cheers

[Maffo]

The two of you would need to spell it out exactly what happens, word for word what you want the customer to see and when. Does this apply to searches or to the checkout, things like that.

[Greg H]

Well I was talking about the search function only.

As far as function goes, as I said said in my previous post...

When a user searches for a word that is contained within the "keywords" of any category, a link to that category is provided alongside/above/somewhere, with the usual search results showing normally.
Text with these links could say something like "Related Categories" or something like that.

Cheers