Jump to content

Display Links Based on Search Results


itsinmyhead

Recommended Posts

So, here's what I'm trying to do.  We've got a MySQL database with two tables in it - the first table is 'classifieds' with the fields 'category' and 'description'.  The next table is 'links_rea' with the fields 'name', 'address', 'web'.

 

People are able to search our classified ads.  What I would like to do, though, is to display related links to their search results.  So, if someone searches for "house," I would like to return those search results - plus links that match the category that the ad is placed in (if the ad has a category of "Real Estate," I'd like to return Real Estate ads.  If the ad has a category of "Automotive," I'd like to return Automotive ads.  etc.)

 

I can't figure out a way to do this.  Is there a way that I can crawl the results of my search to find the specific categories, and then return the links that correspond to whatever categories are found?

 

Or something else I should be trying to do, perhaps?

 

Thanks!

Link to comment
Share on other sites

What tables should I have?

 

Right now, I'm only working with my main classifieds table (which works fine as far as searching/viewing ads goes) and my Real Estate links table.  I figure there's not much point in putting all of my links into tables until I can get one working.

 

And right now, I don't have much.  I'm at a total loss.  I've been playing with strpos and preg_match, but I can't make any real headway with them.

 

What I've been able to accomplish with preg_match is this:

- If you click the link for "Real Estate", the Real Estate ads will be displayed as well the Real Estate links.

- If you search "real estate", the ads with that term will be displayed as well as the Real Estate links

 

However, I can only get the links displayed with those exact matchings of the term "real estate."  If you search for "house" or "rent" or anything that brings up an ad with the category of "Real Estate", I get no results from my links.

Link to comment
Share on other sites

I'm searching the "classifieds" table.  Here's how I do that:

 

$query1 = "select * from classifieds where Category like \"%$trimmed%\" or Description like \"%$trimmed%\"
  order by Category";
$numresults=mysql_query($query1);
$numrows=mysql_num_rows($numresults);

 

So, if I run preg_match to find a match in "query1", it's only going to find a match based on the search term used.  This isn't what I want to accomplish.  I want the links displayed to relate to the results, not the search term - so if there are results in the Real Estate, Autmotive and Pets categories, it will return the links that correspond to each of those sections.

Link to comment
Share on other sites

I'm still very confused why you're confused... don't you get back the category as one of the fields?

 

Yes, I do - in the "Classifieds" table.

 

What I want to do, though, is to have the data from my "Links" table match and display that.  So, if I search for a house for rent and come up with things in "Real Estate", it will display "Real Estate" categorized links.

 

I can do an "if category1 = category2" or "select * where category1 = category2" but these will indefinitely return results whose categories match instead of basing it on the results of the search that has been run.

Link to comment
Share on other sites

Related like this:

 

"Classifieds" Table:

ID

Category

Description

 

"Links" Table:

ID

Category

Name

Address

Web

 

The ID numbers aren't linkable, since they auto increment - plus the IDs for classifieds change week to week, for the most part, when the week's new data is imported.

 

Category would match up.  However, like I mentioned before, if I just do a join where I select matching categories, it would always display those categories.

Link to comment
Share on other sites

IDs should *ALWAYS* be linkable... why wouldn't they be?

 

There are a few hundred ads going in each week.  Let's say there are 75 real estate ads and the first one is item number 40 in the entire block of ads.

 

If there are only ten links for real estate advertisers in the links table, the IDs (which auto increment) wouldn't match up.

 

And, say they did, I still can't find a way to display matching items based only on search results and not on the entire dataset of the database & tables.

Link to comment
Share on other sites

I doubt it - you seem to be the only consistent poster replying to these, haha.

 

Let me try to explain again...

 

We have a table for classifieds in our database.  You can search the table for any term so you can find ads you want to see.

 

We have a table for links supplied by our advertisers.  These can be real estate groups, auto dealers, restaurants, etc.  These aren't searchable, they are just for display.

 

What we would like to do is have a user search the ads for an item - i.e., a house.  The results will then come back to our user.  Then, run another process where the actual results are searched - if there are results in the category "Real Estate" then we would like to display the links from the links table that also fit that category.

 

I know how to call for items from the classifieds & links table that match categories - but I want to crawl the results, not just pull data straight from the tables.

Link to comment
Share on other sites

Maybe we're getting closer... could you elaborate with examples?

 

I'm not sure what kind of examples you want, since I haven't found anything that works for me yet.  Do you mean examples on other sites?  If that's the case, then the easiest example I can think of is Google's ads - where they crawl your page's content and supply related ads.  That's in the same realm, basically, of what I'm wanting to do.

Link to comment
Share on other sites

Ahh, okay.  From the Classifieds table, for instance:

 

ID: 1

Category: Real Estate

Description: 1 BR Apartment For Rent...

 

ID: 2

Category: Real Estate

Description: Small home for sale...

 

ID: 3

Category: Automotive

Description: 1999 Buick...

 

In the Links table:

 

ID: 1

Category: Real Estate

Name: #1 Realty

Link: http://www.number1.com

 

ID: 2

Category: Automotive

Name: Advanced Auto

Link: http://www.advancedauto.com

 

 

Now, as far the Classifieds table goes, I have my search working fine (as  seen here: http://www.adkpennysaver.com/search.php).

 

What I would like to do is to have a user run a search.  The results will come up, and if the categories from the Classifieds table match a category from the Links table, it will display those results from the Links table (in addition to displaying the results from the search).

 

So, if you searched real estate ads, you would come up with the first 2 results in the Classifieds table, plus the link provided in the Links table.

 

Does that make any sense?

Link to comment
Share on other sites

I don't remember seeing a suggestion for a UNION, but I did look that up before.  I could never get it to work properly - so I was either doing it wrong or it wasn't the method I needed for this particular task.

 

How would I go about that?

Maybe I didn't.... basically, you take the "easy" query, and UNION it to the second "harder" query, where by you run a compact version of the "easy" query, just enough to get the category, and use it to pull the ads.

Link to comment
Share on other sites

Maybe I didn't.... basically, you take the "easy" query, and UNION it to the second "harder" query, where by you run a compact version of the "easy" query, just enough to get the category, and use it to pull the ads.

 

I think I know what you're saying, but I'm not sure of how to put it into action.  The thing is, I can understand how to do this if I were just displaying the information static on the page - find the matches across the two tables and display the results.

 

However, a user can search any number of things and return any number of results in several different categories at a time.  So, I'm unsure of how to pull the categories from the results and then match those to the information in my Links table.

Link to comment
Share on other sites

OK, lets start off by saying I'm going to call the classified ads 'articles' for the sake of differentiating them from 'ads' that are provided by advertisers (similar to google ads).

 

If you want to 'crawl' the articles then I'd do it before inserting it into the classified table. Here's what I'd do...

 

Create a keyword column in classified table to store crawled keywords

 

[submission of article]

When article is submitted, crawl for keywords

Insert article and keywords into classified table

 

[Retrieval of article]

User searches for articles

Recordset returned into php

Query links table where category IN (comma seperated keywords list from all above articles)

Loop through recordset to display ads

 

Regards

Rich

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.