I need to adjust the INNER JOIN below so I can select links within a certain category.
I really want "at category and below", but will settle for in category for starts.
Here's my perfectly working Distance SELECT.
SELECT ".$DB->prefix."Links.*, o.*, ".$DB->prefix."ZCS_Countries.ZCS_Country,
(".$earth_radius." * (2 * ASIN(SQRT(
POWER(SIN(((z.ZCS_Latitude-o.ZCS_Latitude)*0.017453293)/2),2) +
COS(z.ZCS_Latitude*0.017453293) *
COS(o.ZCS_Latitude*0.017453293) *
POWER(SIN(((z.ZCS_Longitude-o.ZCS_Longitude)*0.017453293)/2),2)
)))) AS Distance
FROM ".$DB->prefix."ZipCodes z,
".$DB->prefix."ZipCodes o
INNER JOIN ".$DB->prefix."Links ON o.ZCS_ZipCode = ".$DB->prefix."Links.".$opts->{'links_zipcode_col'}."
INNER JOIN ".$DB->prefix."ZCS_Countries ON o.ZCS_CC = ".$DB->prefix."ZCS_Countries.ZCS_CC
WHERE z.ZCS_ZipCode = ".$query."
GROUP BY Distance SORT_ORDER, Distance having Distance <= ".$dist
;
$code =~ s/SORT_ORDER/$sort_order/;
my $zipdb = $DB->table('ZipCodes') || print $GT::SQL::error;
my $zip_sth = $zipdb->prepare($code) || print "Unable to PREPARE query - ".$GT::SQL::error;
$zip_sth->execute () || print "Unable to EXECUTE query - ".$GT::SQL::error;
I need help with the code colored red. I have simplified the JOIN's by removing the db prefix for easier reading.
SELECT Links.*, o.*, ZCS_Countries.ZCS_Country, CatLinks.CategoryID,
(".$earth_radius." * (2 * ASIN(SQRT(
POWER(SIN(((z.ZCS_Latitude-o.ZCS_Latitude)*0.017453293)/2),2) +
COS(z.ZCS_Latitude*0.017453293) *
COS(o.ZCS_Latitude*0.017453293) *
POWER(SIN(((z.ZCS_Longitude-o.ZCS_Longitude)*0.017453293)/2),2)
)))) AS Distance
FROM ZipCodes z,
ZipCodes o
INNER JOIN Links ON o.ZCS_ZipCode = Links.".$opts->{'links_zipcode_col'}."
INNER JOIN ZCS_Countries ON o.ZCS_CC = ZCS_Countries.ZCS_CC
INNER JOIN CatLinks ON Links.ID = CatLinks.LinkID
WHERE z.ZCS_ZipCode = ".$query." AND CatLinks.CategoryID = ".$catid."
GROUP BY Distance SORT_ORDER, Distance having Distance <= ".$dist
;
I am not sure if I add the AND CatLinks.CategoryID = ".$catid." to the WHERE clause, or if I just do the JOIN so that each link returned in the results has a catid joined to it. Then while looping through results just qualify for catid.
[edit]
I guess I should ask a question...
How do I imitate CatLinks via an INNER JOIN?
Hope it makes sense, also hope someone can help.
Thanks,
Chris
RGB World, Inc. - Software & Web Development.
rgbworld.com
I really want "at category and below", but will settle for in category for starts.
Here's my perfectly working Distance SELECT.
Code:
my $code = " SELECT ".$DB->prefix."Links.*, o.*, ".$DB->prefix."ZCS_Countries.ZCS_Country,
(".$earth_radius." * (2 * ASIN(SQRT(
POWER(SIN(((z.ZCS_Latitude-o.ZCS_Latitude)*0.017453293)/2),2) +
COS(z.ZCS_Latitude*0.017453293) *
COS(o.ZCS_Latitude*0.017453293) *
POWER(SIN(((z.ZCS_Longitude-o.ZCS_Longitude)*0.017453293)/2),2)
)))) AS Distance
FROM ".$DB->prefix."ZipCodes z,
".$DB->prefix."ZipCodes o
INNER JOIN ".$DB->prefix."Links ON o.ZCS_ZipCode = ".$DB->prefix."Links.".$opts->{'links_zipcode_col'}."
INNER JOIN ".$DB->prefix."ZCS_Countries ON o.ZCS_CC = ".$DB->prefix."ZCS_Countries.ZCS_CC
WHERE z.ZCS_ZipCode = ".$query."
GROUP BY Distance SORT_ORDER, Distance having Distance <= ".$dist
;
$code =~ s/SORT_ORDER/$sort_order/;
my $zipdb = $DB->table('ZipCodes') || print $GT::SQL::error;
my $zip_sth = $zipdb->prepare($code) || print "Unable to PREPARE query - ".$GT::SQL::error;
$zip_sth->execute () || print "Unable to EXECUTE query - ".$GT::SQL::error;
I need help with the code colored red. I have simplified the JOIN's by removing the db prefix for easier reading.
Code:
my $code = " SELECT Links.*, o.*, ZCS_Countries.ZCS_Country, CatLinks.CategoryID,
(".$earth_radius." * (2 * ASIN(SQRT(
POWER(SIN(((z.ZCS_Latitude-o.ZCS_Latitude)*0.017453293)/2),2) +
COS(z.ZCS_Latitude*0.017453293) *
COS(o.ZCS_Latitude*0.017453293) *
POWER(SIN(((z.ZCS_Longitude-o.ZCS_Longitude)*0.017453293)/2),2)
)))) AS Distance
FROM ZipCodes z,
ZipCodes o
INNER JOIN Links ON o.ZCS_ZipCode = Links.".$opts->{'links_zipcode_col'}."
INNER JOIN ZCS_Countries ON o.ZCS_CC = ZCS_Countries.ZCS_CC
INNER JOIN CatLinks ON Links.ID = CatLinks.LinkID
WHERE z.ZCS_ZipCode = ".$query." AND CatLinks.CategoryID = ".$catid."
GROUP BY Distance SORT_ORDER, Distance having Distance <= ".$dist
;
I am not sure if I add the AND CatLinks.CategoryID = ".$catid." to the WHERE clause, or if I just do the JOIN so that each link returned in the results has a catid joined to it. Then while looping through results just qualify for catid.
[edit]
I guess I should ask a question...
How do I imitate CatLinks via an INNER JOIN?
Hope it makes sense, also hope someone can help.
Thanks,
Chris
RGB World, Inc. - Software & Web Development.
rgbworld.com