Question - dealer locator by zipcode/brand

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Question - dealer locator by zipcode/brand

Author

Topic

borriej
Starting Member

4 Posts

Posted - 2010-07-15 : 06:37:10

Hello,

Im new to SQL and I've got a php script to find the nearest dealer/shop from the database.
First you have to fill in your zipcode, you can then click a checkbox to filter your search by the brands dealers sell.

When you click Search it goes trough a php script with the following SQL:

$query2 = "SELECT * FROM table_zipcodes AS p, Dealer AS d WHERE d.zipcode=p.zipcode AND p.nz BETWEEN $NZm AND $NZp AND p.ow BETWEEN $OWm AND $OWp AND d.products LIKE '%". $_GET['brand1a'] . "%' AND d.products LIKE '%". $_GET['brand2a'] . "%'";

This works, but when you click both checkboxes only the dealers will be found that sell both brands. And I want it to display the dealers that sell brand1a or brand2a or brand1a & brand2a.

How do i adjust this SQL rule to accomplish this?

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-07-15 : 06:59:58

$query2 = "SELECT * FROM table_zipcodes AS p, Dealer AS d WHERE d.zipcode=p.zipcode AND p.nz BETWEEN $NZm AND $NZp AND p.ow BETWEEN $OWm AND $OWp AND (d.products LIKE '%". $_GET['brand1a'] . "%' OR d.products LIKE '%". $_GET['brand2a'] . "%')";

Yuo should go o back and revisit your maths notes "order of evaluation of symbols when evaluating formulae"

borriej
Starting Member

4 Posts

Posted - 2010-07-15 : 07:10:05

hi Andrew,

When i use your code the following happends:

- When I don't use the checkboxes and search on my zipcode it shows 6 dealers -> works good.

- When I click the upper checkbox (to filter on brand1a)it still shows 6 dealers at my zipcode which sell brand1a but also brand2a

- When i filter on brand2a it still shows 6 dealers which offer brand1a and brand2a

- When i click both checkboxes and let is search on dealers that sell brand1a and brand2a it shows 6 dealers

Some how it always shows 6 dealers at my zipcode and doesn't use the filter system, what to do?

borriej
Starting Member

4 Posts

Posted - 2010-07-15 : 07:21:47

oh btw the database table looks like this:

Table: Dealer
collumn: products

if the dealers sells brand1a the content is:
namebrand1a

if the dealers sells brand2a the content is:
namebrand2a

if the dealers sells brand1a & brand2a the content is:
namebrand1a, namebrand2a

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-07-15 : 08:51:02

i can't tell about how your checkboxes work/don't work...i don't use php.
all i can do is work with the SQL side.

can you read the FAQ's re supplying sample input data and matching expected results.
I can't figure out how your data looks....what you have given is open to some vagueness in understanding.

borriej
Starting Member

4 Posts

Posted - 2010-07-15 : 09:01:38

ok thx, I did solve it in an easy way by using php conditions. First the php will check if the checkbox is clicked, than it will execute the matching SQL query.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources