[BNM] SQL date_format during LEFT JOINS ??

[Ali]

hi paul,

maybe try selecting l.event_date ASWELL as formatting it as event_date - worth a try?

e.g.

$sql = "SELECT l.id, l.event_date,date_format(l.event_date, '%a %d %M') as event_date,
l.start_time, etc etc

hth

Ali
  ----- Original Message ----- 
  From: delarge 
  To: Brighton New Media 
  Sent: Friday, October 13, 2006 5:06 PM
  Subject: Re: [BNM] SQL date_format during LEFT JOINS ??


  Oooooh hellll! After a good while tinkering - I cannot get this to return
  any values and I call out to the BNM oracle for advice....

  ----------
  $day = 1;
  $daycheck = date("Y-m-d", mktime(0,0,0,date("m"),date("d")+$day,date("Y")));

  $sql = "SELECT l.id, date_format(l.event_date, '%a %d %M') as event_date,
  l.start_time, l.finish_time, l.event, l.category, l.venue, l.price,
  l.notes_short, l.notes_long, c.id, c.name, c. address1, c.address2,
  c.address3, c.address4, c.postcode, c.tel, c.email, c.website FROM listings
  AS l LEFT JOIN listings_clubs AS c ON c.id = c.id WHERE l.venue = c.id AND
  l.event_date = $daycheck ORDER by l.event_date LIMIT $eu, $limit";
  ----------

  I get all results back until I add the 'AND l.event_date = $daycheck'

  $daycheck is getting returned as I would expect: 2006-10-16
  It just will NOT all add up to spit out any event matching $daycheck...

  Large amounts of street cred to anyone can shed any light on what is going
  down...

  PB