Skip to main content

This site requires you to update your browser. Your browsing experience maybe affected by not having the most up to date version.

Archive

Our old forums are still available as a read-only archive.

Moderators: martimiz, Sean, biapar, Willr, Ingo, simon_w

how to write 'join' for live as well for draft site...


Reply

5 Posts   1353 Views

Avatar
dio5

17 September 2007 at 8:41am Community Member, 501 Posts

In one of my controllers I have:

function TipsByCategory()
   {
      if(isset($_GET['id']))
      {
         $tips = DataObject::get('Tip', "tip_categories.CategoryID = $_GET[id]", '', "LEFT JOIN tip_categories ON tip.ID = tip_categories.TipID");
         return $tips;
      }
   }

But this doesn't seem to be working in the 'live' site. I figured out by the errors and looking in the database that for the 'live' site I had to use

ON tip_live.ID = tip_categories.TipID

instead of

ON tip.ID = tip_categories.TipID

Now I'm probably doing this the wrong way... any ideas?

Avatar
Matt

17 September 2007 at 8:29pm Core Development Team, 84 Posts

I believe you just need to encase the table name in backticks - e.g:

$tips = DataObject::get('Tip', "tip_categories.CategoryID = $_GET[id]", '', "LEFT JOIN tip_categories ON `tip`.ID = tip_categories.TipID");

That should automatically use either tip or tip_live, depending on what version of the site you're looking at (draft/live respectively)

Avatar
dio5

17 September 2007 at 8:34pm Community Member, 501 Posts

No, doesn't seem to do it here on my installation 2.1.0 rc1

Avatar
dio5

17 September 2007 at 8:38pm Community Member, 501 Posts

Wait a minute... when I Uppercase tip and set it in backticks it works...

so :

ON `Tip`.ID = tip_categories.TipID

guess I have to use the name of the class instead of the name of the table.

Avatar
Matt

18 September 2007 at 7:58pm Core Development Team, 84 Posts

Ah, are you using Windows?

There's a problem with MySQL on Windows - by default it doesn't care about the case-sensitivity of table names, but Linux does. This is because Windows itself is a case-insensitive file system, whereas Linux isn't.

If you want to fix that, there's an option you can add to your my.ini file for MySQL, take a look at [url=http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html]the MySQL docs here[/url] which explain it in more depth. If I ever build a Windows server, I use lower_case_table_names = 2, which preserves the original case - e.g. it will create the table 'Tip' instead of 'tip'. This means your code will port fine to Linux later, and you can just copy the database over without issues.