[BNM] SQL hell

[delarge]

Holy moly Andy G... looks like that was it....

I was looking at JOINS LEFT & RIGHT yesterday but was certain I had got this
kind of select statement working before without using them...

However, I now see that these JOINS hold to the key to success...

This part:

AS dc ON d.category = dc.cat_id

this provides the relationship that I was missing, right?

thanks a million!

PB


On 7/4/06, Andy Gale <a.gale at epcdirect.co.uk> wrote:
>
> Paul,
>
> I guess this is what you want to be doing...
>
> SELECT d.id, d.category, d.image1, d.image2, d.title, d.text,
> d.phone, d.email, d.web, dc.cat_id, dc.category
> FROM directory AS d
> LEFT JOIN directory_categories AS dc ON d.category = dc.cat_id
> WHERE dc.cat_id = $category
> ORDER by d.id
> LIMIT $eu, $limit
>
> Best regards,
>
> Andy Gale
> EPC Direct Limited
>
>
> > -----Original Message-----
> > From: delarge [mailto:delargerock at gmail.com]
> > Sent: 04 July 2006 15:48
> > To: Brighton New Media
> > Subject: [BNM] SQL hell
> >
> > Yo all
> >
> > This is starting to drive me loopy...
> >
> > Can anyone see what is wrong with this sql statement?
> >
> > $sql = "SELECT d.id, d.category, d.image1, d.image2, d.title, d.text,
> > d.phone, d.email, d.web, dc.cat_id, dc.category FROM directory d,
> > directory_categories dc WHERE dc.cat_id = $category ORDER by d.id LIMIT
> > $eu,
> > $limit";
> >
> >
> > i get no errors, instead it selects everything - seems to be ignoring
> the
> > WHERE clause
> >
> > $category is being passed via a query string and that's getting through
> > OK...
> >
> > any ideas?
> >
> > --
> > Paul Burgess
> >
> > http://delarge.co.uk
> > http://streetstickers.co.uk
> > http://iampaulburgess.co.uk
> > --
> >
> > BNM info/subscription/archives: http://www.brightonnewmedia.org/
> >
> > BNM archive search: http://www.roddis.org/bnm/search.php
> > BNM Del.icio.us tag: http://del.icio.us/tag/bnm/
> > BNM Flickr group: http://www.flickr.com/groups/bnm/
> >
> > BNM powered by http://www.screen-play.net/
>
>
> --
>
> BNM info/subscription/archives: http://www.brightonnewmedia.org/
>
> BNM archive search: http://www.roddis.org/bnm/search.php
> BNM Del.icio.us tag: http://del.icio.us/tag/bnm/
> BNM Flickr group: http://www.flickr.com/groups/bnm/
>
> BNM powered by http://www.screen-play.net/
>



-- 
Paul Burgess

http://delarge.co.uk
http://streetstickers.co.uk
http://iampaulburgess.co.uk