Just starting out, this should be a simple one but I haven't been able to find a good source, especially with the 1.5 to 1.6/1.7/2.5 changeover.
Building a component and among other issues keep running into syntax issues.
For example, here's one way I'm building a query: [TYPE 1]
$query->SELECT('u.id as UserID
, u.name AS Name
, uppi.profile_value AS Hair
, uppi2.profile_value AS Height
');
$query->FROM (' #__users AS u');
$query->LEFTJOIN (' #__user_profiles AS uppi ON u.id = uppi.user_id AND uppi.ordering = 1 ');
$query->LEFTJOIN (' #__user_profiles AS uppi2 ON u.id = uppi2.user_id AND uppi2.ordering = 2 ');
$query->GROUPBY (' u.id
, u.name
, uppi.profile_value
');
Another way: [TYPE 2]
$query->SELECT('u.id as UserID
, u.name AS Name
, uppi.profile_value AS Hair
, uppi2.profile_value AS Height
')
->FROM (' #__users AS u')
->LEFTJOIN (' #__user_profiles AS uppi ON u.id = uppi.user_id AND uppi.ordering = 1 ')
->LEFTJOIN (' #__user_profiles AS uppi2 ON u.id = uppi2.user_id AND uppi2.ordering = 2 ')
->GROUPBY (' u.id
, u.name
, uppi.profile_value
');
Confusing part about both of those is that mysql calls like "LEFTJOIN" are one word instead of two. So then if I want to use INSERT…ON DUPLICATE KEY UPDATE
I'm totally lost.
Here's a third: [TYPE 3]
$query->SELECT('u.id as UserID
, u.name AS Name
, uppi.profile_value AS Hair
, uppi2.profile_value AS Height
');
$query->FROM (' #__users AS u');
$query->JOIN ('LEFT', ' #__user_profiles AS uppi ON u.id = uppi.user_id AND uppi.ordering = 1 ');
$query->JOIN ('LEFT', ' #__user_profiles AS uppi2 ON u.id = uppi2.user_id AND uppi2.ordering = 2 ');
$query->GROUPBY (' u.id
, u.name
, uppi.profile_value
');
$query->ORDER ('u.name ASC');
This is another way I've seen queries built but can't get to work [EDITED - working now, thanks @cppl!] [Type 4]
$query= "SELECT `u`.`id` as UserID
, `u`.`name` AS Name
, `uppi`.`profile_value` AS Hair
, `uppi2`.`profile_value` AS Height
FROM `#__users` AS u
LEFT JOIN `#__user_profiles` AS uppi ON `u`.`id` = `uppi`.`user_id` AND `uppi`.`ordering` = 1
LEFT JOIN `#__user_profiles` AS uppi2 ON `u`.`id` = `uppi2`.`user_id` AND `uppi2`.`ordering` = 2
";
I've gone through the Joomla MVC tutorial and Lynda...neither one really addresses this beyond "here's some code - PLOP."
References:
--//www.theartofjoomla.com/home/9-developer/135-database-upgrades-in-joomla-16.html
--//stackoverflow.com/questions/8467858/joomla-1-7-db-query-does-not-work-when-query-has-an-ampersand
--developer.joomla.org/standards/ch03s10.html
--forum.joomla.org/viewtopic.php?p=2506722
--forum.joomla.org/viewtopic.php?p=1200668
--docs.joomla.org/API16:JDatabaseQuery
--//fsadventures.com/2011/01/some-basic-joomla-database-call-functions/
--//www.sourcecodester.com/php/3863/updating-multiple-rows-mysql-using-php.html
--//stackoverflow.com/questions/7047471/mysql-query-syntax-error
Questions:
1) Are there any differences between these?
2) Is there a reason to use one method over another?
3) Is there a good source for learning different ways of building these? I've searched for hours and haven't found anything comprehensive.
Thanks!
$query
object has functions like select()
,where()
,join()
, etc...->leftJoin()
is a PHP call to the JDatabaseQuery function called leftJoin()
and therefore must be one word ie. it can't be two.leftJoin()
actually just calls join('LEFT',$conditions)
so it's a readability implementation more than a functional difference.You seem to have found all the online sources for Joomla! documentation, apart from the Joomla! Google Groups (CMS Dev, General Dev and Platform Dev).
The only other source is Andrew Eddies "Learn the Art of Joomla!". I haven't used Andrew's stuff myself but I've heard lots of good things about it a the last Joomla! Day I attended.
Edit
Based on the error I would say it's because you have the identifier u.id
encased as one identifier. Try something like:
SELECT `u`.`id` AS "UserID" FROM `#__users` AS `u`