Joomla 2.5 - Multiple Table Join and Retrieve Data


#1

Greetings,

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


#2

is there any chance you could display the actual sql that this code produces?

because i can't work with perl or whatever that code is


#3

This code produces all the dates where joomla_data field : name = idData and joomla_cont field : title = idValue

Based on that it prints dates like
11-17-2012 00:00:00
11-18-2012 00:00:00
11-22-2012 00:00:00


#4

is there any chance that you could print the actual sql that the code produces? you know, the SELECT statement


#5

its already there in the query I have posted.

$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.


#6

that may very well be...

... but that is not the sql that gets passed to mysql, i can assure you

can't you do an echo or something similar prior to executing the query?


#7

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.


#8

now we're getting some place smile

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


#9

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.


#10

Greetings

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

Any help?
Thanks


#11

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


#12

Yes I understand that the results would be like

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

for my query the other rows are missing


#13

which query would that be? perhaps you could post the latest one you actually used


#14

As posted in my earlier post here is the query

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


#15

that query won't run at all, because the ORDER BY clause is not last

try removing the GROUP BY clause altogether


#16

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


#17