Update a BANS search query sitewide with phpMyAdmin

September 18, 2007

Hi All -

Let me preface this tip with a warning that you should be comfortable moving around in your hosting package as well as the phpMyAdmin tool for modifying your database. I am also listing the directions for Hostgator only, as that is the most common hosting being used for BANS. If you are on a different host, I am sure they have phpMyAdmin somewhere, you will just need to check with them on the location of your database management tool.If you are not comfortable in either of these areas this may end up confusing you more than helping. Which is actually where all of us started at one point in time! I should also say that if you have already optimized your store - DONT DO THIS!! It will overwrite any changes you have made to the query field in your database. This method works well for a BRAND NEW BANS store that you have not yet optimized.The problem I had:

Too many categories/Subcategories for my Niche.

It was going to take me weeks to go through each category and subcategory making the query specific changes to assure the visitors ONLY saw relevant auctions to the term I was targeting. I chose a Niche that was found in a total of 17 categories - I know, not much of a Niche so to speak, but ALL 17 categories has products very specific to this, so it was fitting.The solution that worked for me:Execute a raw sql statement in phpMyAdmin and change the query field to the same common keyword for ALL categories.

How it was done: (Link to a short flash movie)
Before you begin, make a backup of your MySQL Database! (Link to short movie on how to do so)

1) Log into your cPanel account.
2) Click on the MySQL Database link.
3) Scroll to the bottom and click on the phpMyAdmin link What you are looking at is a tool used to make changes to your database in a somewhat user friendly format. **WARNING** If you are unsure about doing this, now is a good time to click the X in the upper right of the screen!! Making changes to your database **May** leave it useless and you will have to rebuild the site from scratch.Note: If you are using a shared account (Like hostgator baby croc) you will need to choose the database you wish to change from the dropdown menu on the left side of the screen. Make SURE you choose the same database for the site you wish to change.

4) Click on the “categories” link on the left side of the page.
5) On the right side frame - click on the BOLD SQL link at the top. It is to the right of “Structure” and left of “Search“.

5) In the “Run SQL Query” text box, type:

UPDATE categories SET query = ’search_term’

Note: replace search_term with your niche search term.
Note: the single quote around your search_term is required. In my case, the site was about Dale Earnhardt collectibles and there were ALOT of categories that had items that fit the niche. My goal was to simply narrow down all the various categories into showing ONLY his collectibles, so I used the word ‘earnhardt’. This updated the entire database to use the query term earnhardt in every category, without having to go through each category one by one and do it manually!Glad to help if questions arise…Mark

Popularity: 8% [?]

If you enjoyed this post, make sure you subscribe to my RSS feed!

Posted by Mark | Filed Under Blog, Build a Niche Store MODS

Comments

10 Responses to “Update a BANS search query sitewide with phpMyAdmin”

Christine on October 3rd, 2007 2:09 pm

This is a great idea, unfortunately it’s not working for me. I’m getting an error message. I followed your instructions and clicked go and this is the error I’m getting:

#1054 - Unknown column ‘â€™testâ€™’ in ‘field list’

note: I’m using version 1.3

I’m I missing a step?

Thanks
Mark on October 3rd, 2007 2:55 pm

Hi Christine -

Are you 100% sure you are in the correct db, and working in the correct table?

It actually looks like MySql is not understanding what you are typing in, did you use a copy/paste function, or type it directly?

UPDATE categories SET query = ’searchtermhere’

Mark
Christine on October 4th, 2007 2:40 pm

Hi,
yes, I’m in the correct db. By table do you mean categories?
I clicked on the “categories” link on the left side of the page and then clicked on the BOLD SQL link at the top. Then I copied and pasted exactly what you have:
UPDATE categories SET query = ’searchtermhere’ and clicked GO…
am I supposed to put a checkmark on something?
Mark on October 4th, 2007 3:10 pm

Hi Christine -

You are definitely in the right area and it sounds like you have done everything correct to this point.

Try typing the text into the query box versus copy/pasting. Your term should contain single quotes around it, just like in your last post.

You should then press GO

Note: What search term are you using? Is it a single word or multiple words? Any special characters such as hyphens or commas?

Thanks,

Mark
Rochelle on October 15th, 2007 8:30 am

I think I might know what the problem is for Christine. I just tried this and got the same error. I had to change the ‘ ‘ to ” “. In other words, don’t use a punctuation mark, use quotation marks around your search term.

Mark - do you know if there is a way for me to get search results that are JUST for my niches? I found out yesterday that I am getting search results in each store that have nothing to do with my store’s niche. For example, if you go to http://unicorns.anichestore.com and search for ‘anvil’ it will show results, even though this has nothing to do with unicorns.

I think the reason for this is because this store is built around the phrase ‘unicorn’ instead of a category. To build each category I used category ‘0′ and entered keywords. There has to be a way to limit the search results to unicorns (in this store’s instance) and I was hoping this mod would do it but it didn’t.

Rochelle
Mark on October 15th, 2007 8:47 am

Hi Rochelle -

I am actually working on a way to parse search strings and use the main niche keyword as a portion thereof.

I should have something figured out before too long…
Mark
Graham on November 14th, 2007 5:47 pm

Probably well out of my depth here, but giving it a go.

First, the links to your videos give a 404 error so I’ve jumped in without doing a backup. It is a new store so fingers crossed :-)

I want to put two words in the SQL query (iphone ipod)
Do I just leave a space between or use _ for the space?

I notice in the Ebay categories that there is not one for Iphones. It is in with other phones that i don’t want to show in the results. Do you know of a way to only bring up Iphone auction items?

Thanks,

Graham
Mike on November 22nd, 2007 9:31 am

a space, but both terms will have to be present for an item to show, and I don’t know if that’s what you want.
Mike on November 22nd, 2007 11:02 am

To do what you want, instead of doing a global “update” on all the categories, you will have to do each phone category separately, or you can do a query like this to update all the categories that have phone in their name (% is a wildcard in the database, just like * is in windows):

UPDATE categories SET query=’iphone’ WHERE cat_name LIKE ‘%phone%’
Russ on December 6th, 2007 8:14 pm

Hey Graham I had to laugh out loud when I read your post.The same happened to me but I kept on moving forward and pushed “GO” and it worked for me .

Thanks Mark