[SOLVED] Question about query structure for a "zip codes" query

[arthaeus]

hello all.  i have a question regarding an efficient way to create a query that looks for users based on zip codes.  heres a rundown

 

 

my site will have many users with many searchable fields.  a hypothetical example follows:

 

name :          foo

username:    foo

car_type        ford

beer_type      budweiser

ZIP_CODE      90210

etc..

 

ok now i want to be able to search for users within X miles of a zip code.  for example, if i wanted to search within 100 miles of the zip code 90210, there would be about 75 zip codes to search through.  and further, i want to know who drives a ford and drinks budweiser within 100 miles of 90210.  i was able to put together a working query on another website, but i am no sql expert so i have no idea where to start in terms of optimizing it. 

 

 

NOW FOR THE QUESTION  would you approach it by creating 75 different queries, one for each zip code in which you search that zip code for car_type = 'ford' AND beer_type = 'budweiser' ?  there has to be a more efficient way.  or was i already right?

 

 

Thank you very much

[dbo]

Well, you'll probably have to come up with  way to convert a zip code to latitude and longitude in order to compute distances....

 

After that your other query should be pretty easy to write with a single query. However, you'd have to give more details to give a more accurate answer, and code help isn't really what this forum is for.

[arthaeus]

Well, you'll probably have to come up with  way to convert a zip code to latitude and longitude in order to compute distances....

 

After that your other query should be pretty easy to write with a single query. However, you'd have to give more details to give a more accurate answer, and code help isn't really what this forum is for.

 

hey  thanks for the reply.  i already have the code that will return all of the zips within X miles of a particular zip.

 

a little more info. 

 

the database i have to use for the users is arranged like this.  i didnt design this database so bear with me.  makes more sense to me to put all this information in one row? no?

 

 

so for my hypothetical user, his entries would look as follows

 

 

table user_details

------------------------------------

 

userId            100

trait_name    car

trait_value      ford

 

userId            100

trait_name    beer

trait_value      budweiser

 

userId            100

trait_name    zip_code

trait_value      90210

 

3 rows in the table for 3 traits????  i dont know why they did it this way.

 

 

i just want to know the most efficient method of searching for users who for example: drive a ford, and drink budweiser beer, and live within 100 miles of 90210.  lets say for example that within 100 miles of 90210 we have zip codes

 

90211

90212

90213

90214

...

...

...

 

If you were going to solve this problem would you do :

 

select BEER and CAR where zip = 90211

select BEER and CAR where zip = 90212

select BEER and CAR where zip = 90213

select BEER and CAR where zip = 90214

 

this would give all of the users within these zip codes who drive CAR and drink BEER.

 

 

 

[dbo]

Hrmm. That does make things more complicated.

 

Maybe something like:

 

Give me all users in this set of zip codes (it's assumed you know how to figure out what this set of zip codes is).

 

SELECT userId FROM user_details WHERE trait_name = 'car' AND userId IN (

SELECT userId FROM user_details WHERE trait_name = 'beer' AND userId IN (SELECT userId FROM user_details WHERE zipCode IN (11111, 22222, 33333)))

 

This is of course untested, but theoretically something close to this would work. You start at the inner most set of parenthesis... this gives all the users in the subset of zipcodes..... next you get all the users who like(?) beer and were in the first subquery (inside set of zipcodes), finally you narrow results further by weeding out only userIds who like(?) cars.

[dbo]

I'm afraid that seeing that query might have made arthaeus blow up.

[arthaeus]

I'm afraid that seeing that query might have made arthaeus blow up.

 

 

lol!

 

 

hey, let me post this a different way. 

 

 

CREATE TABLE `elgg_profile_data` (

  `ident` int(10) unsigned NOT NULL auto_increment,

  `owner` int(10) unsigned NOT NULL default '0' COMMENT '-> users.ident',

  `access` varchar(20) collate utf8_unicode_ci NOT NULL default 'PUBLIC',

  `name` varchar(255) collate utf8_unicode_ci NOT NULL default '',

  `value` text collate utf8_unicode_ci NOT NULL,

  PRIMARY KEY  (`ident`),

  KEY `owner` (`owner`,`access`,`name`)

) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=19001 ;

 

 

is it me, or does this table design not make sense?  one row for each attribute.

 

i guess it makes sense if you want to be able to add fields to a profile, but how are you supposed to do a very specific query when the table is designed like this?!?!?!

 

 

if a user had 10 fields in their profile, there would be 10 rows of data for that user!  lots of duplication, and i always understood that data duplication should be avoided. 

 

wouldn't it make more sense to have ONE row with all your data so you could make a query like

 

select * from foo where param1 = 'bar' and param2 = 'foobar' and param3 = 'barfoo'

 

so easy!

 

i have no idea how to query my current table for more than one attribute.

 

is this bad database design??