itsinmyhead Posted January 28, 2009 Share Posted January 28, 2009 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! Quote Link to comment https://forums.phpfreaks.com/topic/142851-display-links-based-on-search-results/ Share on other sites More sharing options...
fenway Posted January 29, 2009 Share Posted January 29, 2009 what do you have so far? you seem to be missing some tables... Quote Link to comment https://forums.phpfreaks.com/topic/142851-display-links-based-on-search-results/#findComment-749387 Share on other sites More sharing options...
itsinmyhead Posted January 29, 2009 Author Share Posted January 29, 2009 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. Quote Link to comment https://forums.phpfreaks.com/topic/142851-display-links-based-on-search-results/#findComment-749609 Share on other sites More sharing options...
fenway Posted January 29, 2009 Share Posted January 29, 2009 Are you searching one of the fields? How? Quote Link to comment https://forums.phpfreaks.com/topic/142851-display-links-based-on-search-results/#findComment-749755 Share on other sites More sharing options...
itsinmyhead Posted January 29, 2009 Author Share Posted January 29, 2009 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. Quote Link to comment https://forums.phpfreaks.com/topic/142851-display-links-based-on-search-results/#findComment-749822 Share on other sites More sharing options...
fenway Posted February 2, 2009 Share Posted February 2, 2009 So you don't want the actual results of the search? Quote Link to comment https://forums.phpfreaks.com/topic/142851-display-links-based-on-search-results/#findComment-752436 Share on other sites More sharing options...
itsinmyhead Posted February 2, 2009 Author Share Posted February 2, 2009 So you don't want the actual results of the search? No, I've already got the search results coming up (http://www.adkpennysaver.com/search.php). What I want now is to be able to show related links based on the results of that search. Quote Link to comment https://forums.phpfreaks.com/topic/142851-display-links-based-on-search-results/#findComment-752547 Share on other sites More sharing options...
itsinmyhead Posted February 3, 2009 Author Share Posted February 3, 2009 Just wanted to give this a little bump to see if anyone may have any ideas. Quote Link to comment https://forums.phpfreaks.com/topic/142851-display-links-based-on-search-results/#findComment-753880 Share on other sites More sharing options...
fenway Posted February 6, 2009 Share Posted February 6, 2009 I'm still very confused why you're confused... don't you get back the category as one of the fields? Quote Link to comment https://forums.phpfreaks.com/topic/142851-display-links-based-on-search-results/#findComment-755791 Share on other sites More sharing options...
itsinmyhead Posted February 6, 2009 Author Share Posted February 6, 2009 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. Quote Link to comment https://forums.phpfreaks.com/topic/142851-display-links-based-on-search-results/#findComment-756183 Share on other sites More sharing options...
fenway Posted February 8, 2009 Share Posted February 8, 2009 what's in the links table? how is the related to the categories? Quote Link to comment https://forums.phpfreaks.com/topic/142851-display-links-based-on-search-results/#findComment-757368 Share on other sites More sharing options...
itsinmyhead Posted February 9, 2009 Author Share Posted February 9, 2009 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. Quote Link to comment https://forums.phpfreaks.com/topic/142851-display-links-based-on-search-results/#findComment-758172 Share on other sites More sharing options...
fenway Posted February 15, 2009 Share Posted February 15, 2009 IDs should *ALWAYS* be linkable... why wouldn't they be? Quote Link to comment https://forums.phpfreaks.com/topic/142851-display-links-based-on-search-results/#findComment-762674 Share on other sites More sharing options...
itsinmyhead Posted February 17, 2009 Author Share Posted February 17, 2009 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. Quote Link to comment https://forums.phpfreaks.com/topic/142851-display-links-based-on-search-results/#findComment-764272 Share on other sites More sharing options...
fenway Posted February 17, 2009 Share Posted February 17, 2009 Sorry, I give up... I really haven't followed this from the outset. Maybe someone else will be able to understand. Quote Link to comment https://forums.phpfreaks.com/topic/142851-display-links-based-on-search-results/#findComment-764286 Share on other sites More sharing options...
itsinmyhead Posted February 17, 2009 Author Share Posted February 17, 2009 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. Quote Link to comment https://forums.phpfreaks.com/topic/142851-display-links-based-on-search-results/#findComment-764450 Share on other sites More sharing options...
fenway Posted February 18, 2009 Share Posted February 18, 2009 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. Maybe we're getting closer... could you elaborate with examples? Quote Link to comment https://forums.phpfreaks.com/topic/142851-display-links-based-on-search-results/#findComment-765009 Share on other sites More sharing options...
itsinmyhead Posted February 18, 2009 Author Share Posted February 18, 2009 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. Quote Link to comment https://forums.phpfreaks.com/topic/142851-display-links-based-on-search-results/#findComment-765435 Share on other sites More sharing options...
fenway Posted February 23, 2009 Share Posted February 23, 2009 No, I want specific examples in your case... sample records from both tables, what output you are trying to achieve with them, how they relate, etc. Quote Link to comment https://forums.phpfreaks.com/topic/142851-display-links-based-on-search-results/#findComment-769033 Share on other sites More sharing options...
itsinmyhead Posted February 23, 2009 Author Share Posted February 23, 2009 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? Quote Link to comment https://forums.phpfreaks.com/topic/142851-display-links-based-on-search-results/#findComment-769297 Share on other sites More sharing options...
fenway Posted February 24, 2009 Share Posted February 24, 2009 Yes, that does make sense. So why not simply UNION the two result sets? I feel like I mentioned that before... Quote Link to comment https://forums.phpfreaks.com/topic/142851-display-links-based-on-search-results/#findComment-770161 Share on other sites More sharing options...
itsinmyhead Posted February 24, 2009 Author Share Posted February 24, 2009 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? Quote Link to comment https://forums.phpfreaks.com/topic/142851-display-links-based-on-search-results/#findComment-770218 Share on other sites More sharing options...
fenway Posted February 25, 2009 Share Posted February 25, 2009 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. Quote Link to comment https://forums.phpfreaks.com/topic/142851-display-links-based-on-search-results/#findComment-770817 Share on other sites More sharing options...
itsinmyhead Posted February 25, 2009 Author Share Posted February 25, 2009 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. Quote Link to comment https://forums.phpfreaks.com/topic/142851-display-links-based-on-search-results/#findComment-771059 Share on other sites More sharing options...
HuggieBear Posted February 25, 2009 Share Posted February 25, 2009 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 Quote Link to comment https://forums.phpfreaks.com/topic/142851-display-links-based-on-search-results/#findComment-771109 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.