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   1429 Views

Avatar
dio5

Community Member, 501 Posts

17 September 2007 at 8:41am

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

Core Development Team, 84 Posts

17 September 2007 at 8:29pm

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

Community Member, 501 Posts

17 September 2007 at 8:34pm

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

Avatar
dio5

Community Member, 501 Posts

17 September 2007 at 8:38pm

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

Core Development Team, 84 Posts

18 September 2007 at 7:58pm

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.