I have 3 tables in Joomla 2.5 from which I need to get data and display it.
Now the query works till its 2 tables but as soon as I join a 3rd table it does not work.
This works perfectly fine :
function getData($idData,$idValue) {
$db = JFactory::getDbo();
$sql = $db->getQuery(true);
$sql
->select(array('st.id', 'st.name', 'st.date', 'st.currentstat', 'li.title', 'li.id'))
->from('#__joomla_data AS st')
->where('st.name ='.$idData)
->join('INNER', '#__joomla_cont AS li ON (st.currentstat = li.id) AND (li.title="'.$idValue.'")' )
->order('st.date ASC');
$db->setQuery($sql);
$query_result = $db->loadObjectList();
$date_print = '';
foreach($query_result as $query_results){
$pdate = date("M,D-Y", strtotime($query_results->date));
$date_print .= $pdate .'<br/>';
}
return $date_print;
}
Now I have another table joomla_maincont which has fields id and name. I want to modify the above query a bit and what I finally need is that ID from joomla_maincont is passed for $idData above.
The query I have to retrieve data for joomla_maincont is this
select id, name from #__joomla_maincont where name like '$idData-%'
This will always return only one value as all the names are like ID-Name so using like I can get a single row. Now this tables ID, once retrieved needs to be passed to the above query as $idData as that is the ID for joomla_data ID.
Any help how I can achieve the above? Thank you for your help
$sql
->select(array(‘st.id’, ‘st.name’, ‘st.date’, ‘st.currentstat’, ‘li.title’, ‘li.id’))
->from(‘#__joomla_data AS st’)
->where(‘st.name =’.$idData)
->join(‘INNER’, ‘#__joomla_cont AS li ON (st.currentstat = li.id) AND (li.title="’.$idValue.‘")’ )
->order(‘st.date ASC’);
In joomla you need to write select query in this way only.
Thanks for following up. Let me break the query in a simple query for you
$sql = ‘SELECT st.id as st_id, st.name as st_name, st.date as st_date, st.currentstat as st_currentstat, li.title as li_title
FROM #__joomla_data AS st
LEFT JOIN #__joomla_cont li
ON st.currentstat = li.id
WHERE st_name =’. $idData .’
AND li.title =‘. $idValue .’
ORDER BY st.date’
In regular PHP I write this query and to get the $idData I write a separate query. $idDataMain is a value I pass to get the remaining details. By passing $idDataMain I get the ID from joomla_maincont.
select id, name from #__joomla_maincont where name like ‘$idDataMain-%’
Once I get the data I store it in variable $idData and that completes my above query which then provides me a list of dates for that specific ID. I then print it.
But I want to do this in joomla and run a single join query rather than multiple loops.
okay, the WHERE clause isn’t quite right, because you’re using the alias st_name, and that would generate a syntax error
secondly, you’re specifying a non-null condition for li.title and that means you want an INNER JOIN, not a LEFT JOIN
but now i can at least incorporate the additional table for you –
SELECT st.id as st_id
, st.name as st_name
, st.date as st_date
, st.currentstat as st_currentstat
, li.title as li_title
FROM #__joomla_maincont AS m
INNER
JOIN #__joomla_data AS st
ON st.name = m.id
INNER
JOIN #__joomla_cont AS li
ON li.id = st.currentstat
WHERE m.name LIKE '$idDataMain-%'
AND li.title ='. $idValue .'
ORDER
BY st.date
i think i understood your $idData value coming from the m table, but i’m not sure about the $idValue value, so i just left that part in the WHERE clause
Perfect. Thanks a ton! Works as expected. Only thing I changed a bit is where m.name like '$idDataMain\%’ as if I added $idDatamain it was getting all ids like 1_ 11_ and so on while I needed only 1 ID. I found that in SQL _ represents one character so I used an escape before it and the results are now perfect.
After making this query work I am now trying to group the entries and its returning only 1 row.
Original Query
SELECT st.id as st_id
, st.name as st_name
, st.date as st_date
, st.currentstat as st_currentstat
, li.title as li_title
FROM #__joomla_maincont AS m
INNER
JOIN #__joomla_data AS st
ON st.name = m.id
INNER
JOIN #__joomla_cont AS li
ON li.id = st.currentstat
WHERE m.name LIKE ‘$idDataMain-%’
AND li.title =‘. $idValue .’
ORDER
BY st.date
Modified Query : I have now removed the where and statements and added a group by statement
SELECT st.id as st_id
, st.name as st_name
, st.date as st_date
, st.currentstat as st_currentstat
, li.title as li_title
FROM #__joomla_maincont AS m
INNER
JOIN #__joomla_data AS st
ON st.name = m.id
INNER
JOIN #__joomla_cont AS li
ON li.id = st.currentstat
ORDER BY st.date
GROUP BY m.name
When I try to group by it provides only one result
Name 1 : 11-17-2012 00:00:00
Actually I want
Name 1:
11-17-2012 00:00:00
11-18-2012 00:00:00
11-22-2012 00:00:00
Name 2:
11-21-2012 00:00:00
11-24-2012 00:00:00
11-26-2012 00:00:00
Name 3:
11-07-2012 00:00:00
11-08-2012 00:00:00
11-12-2012 00:00:00
you cannot and should not expect sql to format the results that way
what you should get from your query will look like this –
Name 1 11-17-2012 00:00:00
Name 1 11-18-2012 00:00:00
Name 1 11-22-2012 00:00:00
Name 2 11-21-2012 00:00:00
Name 2 11-24-2012 00:00:00
Name 2 11-26-2012 00:00:00
Name 3 11-07-2012 00:00:00
Name 3 11-08-2012 00:00:00
Name 3 11-12-2012 00:00:00
then you can do the fancy indenting in your application language
Name 1 11-17-2012 00:00:00
Name 1 11-18-2012 00:00:00
Name 1 11-22-2012 00:00:00
Name 2 11-21-2012 00:00:00
Name 2 11-24-2012 00:00:00
Name 2 11-26-2012 00:00:00
Name 3 11-07-2012 00:00:00
Name 3 11-08-2012 00:00:00
Name 3 11-12-2012 00:00:00
but right now I only get 1 row
Name 1 11-17-2012 00:00:00
Modified Query : I have now removed the where and statements and added a group by statement
SELECT st.id as st_id
, st.name as st_name
, st.date as st_date
, st.currentstat as st_currentstat
, li.title as li_title
FROM #__joomla_maincont AS m
INNER
JOIN #__joomla_data AS st
ON st.name = m.id
INNER
JOIN #__joomla_cont AS li
ON li.id = st.currentstat
ORDER BY st.date
GROUP BY m.name
Oh yes I did not think of that! Thanks for highlighting. Each time we use Group By so by default used this for the query when it was not atall needed. Thanks for the help
