NYCPHP Meetup

[nycphp-talk] Still Mysql Selects

Alberto dos Santos acas at sapo.pt
Wed Jun 22 04:12:48 EDT 2005


Ok, I surrender, you guys are top! I'm asking for help.

I have been strugling to have a clean cross reference check code in PHP
using a MySQL database.

I have a table called "CATALOG_READING" with two entries, one is the ID
(CR_ID) and the other a value.
Then I have 16 tables each with 4 rows that have a value from CR_ID, in
order to use the values from the CATALOG_READING table in different
situations.

What I need to check is which values from CR_ID are NOT USED in any of the
other tables, so I can identify and remove it.

Before you ask, CR_ID has 3900 entries and the other tables have 4*700
entries (AVG), so I can't do it by hand... :p

Thanks in advance,



--
Alberto dos Santos
email: acas at sapo.pt
skype: fatflash

 
Salvaguarda: Este email está protegido de vírus e programas maliciosos  

-----Original Message-----
From: talk-bounces at lists.nyphp.org [mailto:talk-bounces at lists.nyphp.org] On
Behalf Of Ophir Prusak
Sent: quarta-feira, 22 de Junho de 2005 4:25
To: NYPHP Talk
Subject: Re: [nycphp-talk] Mysql Selects

not sure why I'm answering this, but this seems you're doing something
in PHP you could/should be doing in the database.

the " IN (zip1, zip2, etc)" solution aint that bad, but what you
really should be doing is using one query with a join or a subselect.

for the join crowd, something like this:

select 
org.* 
from 
org, zips 
where
org.zip = zips.zip
and <zip code sql goes here>

i usually just use a rough zip code distance check using a square box.

ophir

On 6/21/05, harvey <list at harveyk.com> wrote:
> Hello,
> 
> I've got code that queries the 'zipcode' tables in a database and finds
the
> zips that are within X miles of Y zip code. Works fine.
> 
> <?php
>        $z = new zipcode_class;
>                $zips = $z->get_zips_in_range($zipcode,$distance);
>                if (empty($zips))
>                   { echo "Error: " . $z->last_error; }
>                else { foreach ($zips as $key => $value) { echo
"<b>$key</b> is
> <b>$value</b> miles from <b>$zipcode</b>.<br>"; };  }
> ?>
> 
> Now I want to query the 'organization' tables and return all the orgs that
> have one of the zips found above. But I'm not sure how to go about it.
> Something like the following (which I know is incorrect)?
> 
> SELECT * FROM organizations WHERE org_zip IN ($zips)
> 
> Thanks in advance for your help...I'm sure it's some very basic concept
I'm
> missing.
> 
> Harvey
> 
> _______________________________________________
> New York PHP Talk Mailing List
> AMP Technology
> Supporting Apache, MySQL and PHP
> http://lists.nyphp.org/mailman/listinfo/talk
> http://www.nyphp.org
>
_______________________________________________
New York PHP Talk Mailing List
AMP Technology
Supporting Apache, MySQL and PHP
http://lists.nyphp.org/mailman/listinfo/talk
http://www.nyphp.org




More information about the talk mailing list