Special MySQL database and Query

[jonathanroy]

Hi,

I am looking for the best solution to my situation:

- I have businesses listed in a MySQL table.
- Each business can bid on up to 10 keywords (different prices for different keywords).

A user inputs a keyword and then I must find a way to query my table and get out the business matching that keyword by order of bid.

Your help would be very appreciated.

Thank you,

Jonathan

[hashinclude]

you'd probably have to use an sql qury such as this:

SELECT business_name FROM business_table WHERE business_name LIKE '%KEYWORD%' ORDER BY bid DESC;

and ofcourse never forget to sanitize user input first

[jonathanroy]

Yes maybe but I need one price (bid) for each keyword so that does not work

[hashinclude]

oh now i understand what you meant by 10 keywords..

assuming you got all submitted keywords into an array called $keywords

foreach($keywords as $keyword)
{
    $list[] = '%' . $keyword . '%'; 
}

$sql = 'SELECT business_name, bid FROM business_table WHERE business_name LIKE ' . implode(' OR LIKE ', $list) . ' ORDER BY bid DESC';

[jonathanroy]

I was more thinking of having keyword1, keyword2, etc and bid1, bid2, etc in my table

EACH keyword has one UNIQUE bid for example:

BUSINESS: Mike & John Attorneys Inc.

keyword1: lawyer
bid1: 2.00

keyword2: attorney
bid2: 1.50

keyword3: law firm
bid3: 3.50

when someone types "lawyer" i want to see a bid of 2.00 and when someone enters "law firm" a bid of 3.50

This will let me rank businesses on the basis of the bid for any keyword.

[hashinclude]

do you mean that every business must have a list of pre-defined keywords?

[jonathanroy]

Exactly. Every business is listed in a MySQL table with predefined keywords and bids.

[hashinclude]

Exactly. Every business is listed in a MySQL table with predefined keywords and bids.

do you mean every business has it's own table? or is it one table which contains all businesses and keywords and bids ?

[jonathanroy]

Ok I didnt think this was so confusing.

There is one table called let's say business where all the businesses are listed with info such as phone number, address, website etc and up to 10 keywords each with a bid.

I want to query that table with a keyword and compare it to keyword 1 to 10 and list the coressponding businesses according to the bid made on the keyword specified by the user/searcher.

[hashinclude]

Ok I didnt think this was so confusing.

There is one table called let's say business where all the businesses are listed with info such as phone number, address, website etc and up to 10 keywords each with a bid.

I want to query that table with a keyword and compare it to keyword 1 to 10 and list the coressponding businesses according to the bid made on the keyword specified by the user/searcher.

it's not that it's confusing, it's just that we can't help you when we don't know how your business table(s) is looks like.

can you please post an example row of that business table?

[jonathanroy]

Ok, it looks like this:

BUSINESS NAME: John & Mike Attorneys
LOGO: johnmikeattorneys.gif
ADDRESS: 123 Maple Street, New York, NY 12345
TELEPHONE: [phone]
WEBSITE: [url]www.johnmikeattorneys.com[/url]
KEYWORD1: LAWYER
BID1: 2.00
KEYWORD2: ATTORNEY
BID2: 3.50
KEYWORD3: LAW FIRM
BID3: 1.50
KEYWORD4: -
BID4: -
KEYWORD5: -
BID5: -
KEYWORD6: -
BID6: -
KEYWORD7: -
BID7: -
KEYWORD8: -
BID8: -
KEYWORD9: -
BID9: -
KEYWORD10: -
BID10: -

This should help.

[hashinclude]

so a user inputs one keyword, and the script returns the businesses matching that keyword ordered by the bid corresponding to that keyword?

[jonathanroy]

Yes.

[hashinclude]

well it can be done ofcourse, but the table layout you have doesn't make it an easy job. an sql query by its self won't accomplish what you want.

you have three options:

1- recreate the table with an appropriate structure (recommended)
2- create a user defined function in your database to handle the keyword input and return the result that you wish directly. (less recommended)
3- run a normal sql query such as

SELECT * FROM business_table WHERE key1='inputkey' OR key2='inputkey' OR key3='inputkey' OR key4='inputkey' OR key5='inputkey' OR key6='inputkey' OR key7='inputkey' OR key8='inputkey' OR key9='inputkey' OR key10='inputkey'

and then use PHP to filter out the key you with with possible bids and then order them as you wish. (least recommended)

all of that while making sure that you store similar keys for each business in the same column. other wise there is no option but to recreate the table.

[jonathanroy]

Yes I see what you mean, I think i will make a separate table called "bids" that looks like this: ID, BUSINESS-ID, KEYWORD, BID Then I will query that table with: SELECT DISTINCT 'business-id' FROM 'bids' WHERE 'keyword'='$inputkey' ORDER BY 'bid' And then for each returned entry I will search the corresponding business. SELECT * FROM 'business' WHERE 'id'='$business-id' LIMIT 0,1 With such a setup I can allow businesses to bid on infinitely many keywords and is more flexible in general.

[hashinclude]

Yes I see what you mean, I think i will make a separate table called "bids" that looks like this: ID, BUSINESS-ID, KEYWORD, BID Then I will query that table with: SELECT DISTINCT 'business-id' FROM 'bids' WHERE 'keyword'='$inputkey' ORDER BY 'bid' And then for each returned entry I will search the corresponding business. SELECT * FROM 'business' WHERE 'id'='$business-id' LIMIT 0,1 With such a setup I can allow businesses to bid on infinitely many keywords and is more flexible in general.

you don't really have to create a separate table

use one table but only use one field for keyword and one field for bid:
then you can insert as many rows as you'd like per one business, for example if a business had two keywords, you would insert two rows, one for each keyword

[jonathanroy]

Yes you are right this is one way to do it but I would get the same business listing over and over for different keywords. If I want to modify the business telephone for example it would be more tedious and globally the table would be more complex.

I think I will keep it the way I proposed for simplicity over runtime.

[hashinclude]

it's your choice in the end :)

[jonathanroy]

And this is how i'm going to end this trend.

Thank you very much hashinclude for your help. Very appreciated. :)

[hashinclude]

you're welcome :)