[Tricky Query]

The ids can be any length from 1 digit to 4 digits, currently.

 

I didn't mean to put those sunglassed-smilies, it's supposed to be ( 8 )...

[Tricky Query]

OH, btw, the zipData table looks like this:

 

zipcode varchar(5)

lon varchar(

lat varchar(

[Tricky Query]

Another question for you..

 

The phpZipLocator uses this php function to grab all zipcodes within x mile radius of a provided zipcode:

 

function inradius($zip,$radius)
    {
    	$i = "";
        global $db_zip;
        $query="SELECT * FROM zipData WHERE zipcode='$zip'";
        $db_zip->query($query);

        if($db_zip->affected_rows()<>0) {
            $db_zip->next_record();
            $lat=$db_zip->f("lat");
            $lon=$db_zip->f("lon");
            $query="SELECT zipcode FROM zipData WHERE (POW((69.1*(lon-\"$lon\")*cos($lat/57.3)),\"2\")+POW((69.1*(lat-\"$lat\")),\"2\"))<($radius*$radius) ";
            $db_zip->query($query);
            if($db_zip->affected_rows()<>0) {
                while($db_zip->next_record()) {
                    $zipArray[$i]=$db_zip->f("zipcode");
                    $i++;
                }
            }
        }else{
            return "Zip Code not found";
        }
     return $zipArray;
    } // end func

 

Since we're starting with meet_theatres.zip,

can we join the zipData table (to get the lat/lon),

and return a count of members (member.zipcode) that are in that result?

 

 

[Tricky Query]

Oh.. sorry.. meant to say YES, the mockup worked great.

I just tried your new query and it's awesome!

Thanks again, Wildbug.

 

.. can I contact you for freelance work sometime?

[Tricky Query]

hi wildbug.. thanks for the afterschool help with this one.

 

would 3362

 

be found in 3362|2878|2881|2883|2885

 

be found in 2878|2881|3362|2883|2885

 

be found in 2878|2881|2885|2883|3362

 

 

likewise, what happens if we search 62, would it

 

 

be found in 3362|2878|2881|2883|2885

 

be found in 2878|2881|62|2883|2885

 

 

.. I know I know.. i'm seriously regretting my choice to pipe-separate like that.

it was just really easy at the time. I'm sure I wouldn't be in this boat if i had done the table design like you recommend.

 

 

[Tricky Query]

Could be that I'm translating the tables, columns that I used for this post, wrong with the actual columns/table names.. Sorry, Wildbug, only did that cuz I didn't want to confuse ppl

 

here's the real stuff:

 

Table: meet_theatres

cols: id, zip

 

(id is a unique identifier, zip is the zipcode for that theater record)

 

Table: meet_profile

cols: id, theatres

 

(id is a unique identifier, theatres looks like this "3362|2878|2881|2883|2885", where 3362 is an id from meet_theatres)

 

Table: member

cols: id, zipcode

 

(id, again unique identifier, zipcode is the member's personal zipcode, like your people table)

 

this is the translation of your query:

 

SELECT
meet_theatres.id,
meet_theatres.zip,
SUM(meet_profile.theatres REGEXP CONCAT('meet_theatres.id',meet_theatres.id,'[[:>:]]')) AS zips
FROM
meet_theatres LEFT JOIN(SELECT DISTINCT zipcode FROM member) AS pz ON meet_theatres.zip=pz.zipcode,
meet_profile
GROUP BY meet_theatres.id;

[Tricky Query]

k.. finally got the update figured out.

I'm running

MySQL 4.1.11-Debian_4sarge7-log

now and getting 0 for all zips...

[Tricky Query]

running MySQL 4.0.24_Debian-10sarge1-log

i suppose I oughtta start looking for documentation about upgrading MySQL.

[Tricky Query]

.. hmm.. well those individual queries

(mysql> select * from theaters; select *,COUNT(*) from people GROUP BY zipcode; select * from trips;)

works without problem.. but when I run the combined query

(SELECT

theaters.id,

theaters.zipcode,

SUM(trips.theaterlist REGEXP CONCAT('theaters.id',theaters.id,'[[:>:]]')) AS zips

FROM

theaters LEFT JOIN

(SELECT DISTINCT zipcode FROM people) AS pz ON theaters.zipcode=pz.zipcode,

trips

GROUP BY theaters.id;)

I get an error from mysql:

 

 

#1064 - You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT DISTINCT zipcode FROM people) AS pz ON theaters.zipcode

[Tricky Query]

SWEET.. gonna give it a try right now.

Thank you sooo much for your genius.

I'll post a reply when I run the query.

[Tricky Query]

No.. it's a separate unique identifier that I'm using for TRIPS.

My primary key (auto-incr).

[Tricky Query]

Hi.. wondering if this is possible.. Here are my tables:

 

THEATERS has ID and ZIPCODE

PEOPLE has ZIPCODE

TRIPS has ID and THEATERLIST (looks like this: "THEATERS.ID1|THEATERS.ID2|THEATRES.ID10")

 

I would like to query a record set to get

 

ALL VALUES in THEATERS (including ID and ZIPCODE)

PLUS

COUNT of PEOPLE in that ZIPCODE

PLUS

COUNT of TRIPS where the THEATER ID appears in my TRIPS.THEATERLIST, pipe-separated string...

 

 

 

I'll eventually use that zip-code range finder, so that I can use a

COUNT of PEOPLE where their PEOPLE.ZIPCODE in (10001, 10002...) <-- meaning a 25mi radius of THEATERS.ZIPCODE.

 

 

Can somebody help me concoct this query?

[Timezone by Client IP?]

Hmm... not a bad idea.

Populate a hidden text field with current client time.

Parse with PHP to get timezone by stamp...

 

You're a smart guy.

[Timezone by Client IP?]

Rats.. thought you might say that.

Localtime isn't communicated through server/session obj is it..

[Timezone by Client IP?]

Can somebody recommend a good way to convert a HOST_ADDR to a TIMEZONE?

[[SOLVED] IP -> Country?]

Brilliant. Thanks redarrow.

[[SOLVED] IP -> Country?]

Can anybody suggest a good way to determine which country an IP hails from?

More specifically (and ethnocentrically), if an IP hails from the US, return true?

Thanks,

benny

[Contact Forms and ABUSE]

I'll try changing email to myemail, message to mymessage (or something like that) to see if that resolves the issue.

Thanks for the links too, you guys. I really appreciate your brilliance.

Benny

[How can I send email to AOL via PHP?]

Excellent question.

I'm dying to know the solution too.

[Contact Forms and ABUSE]

All fields are validated, and succeed.

[Contact Forms and ABUSE]

Hi All,

 

I've written a bunch of contact forms in the past, and one in particular has been getting a ton of abuse. The fields are:

NAME

EMAIL

PHONE

SUBJECT

MESSAGE

and a few other more specific questions.. The form is submitted via a javascript function (.submit()).

 

Can any one recommend any tricks to limit this type of abuse?

 

Thanks,

Benny

[AOL doesn't like OSCommerce]

Hi Jesirose.. I've tried that and it's still never arrives.

[AOL doesn't like OSCommerce]

I'm getting the confirmations fine on other accounts (incl gmail), just not AOL.

[AOL doesn't like OSCommerce]

I've seen tons of solutions to this problem, but none that apparently work.

OSCommerce mails order confirmations (and new account confirmations) just fine to any recip except for AOL email addresses. Anybody come across this -- or have a real solution?

 

Thx,

Benny

[One user only!]

the_oliver, i like this plan.

a. when logging in, check if there is a session open corresponding to the last session id recorded for that user.
b. if there isn't a session open, log the new session id and allow login
c. if there IS a session open that corresponds to that user's previous session id, then show error

how do you search the server for sessions by id?

thx
benny