SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    SitePoint Addict
    Join Date
    Oct 2008
    Location
    Virtual World is my location
    Posts
    316
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Question Joomla 2.5 - Multiple Table Join and Retrieve Data

    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 :

    Code:
    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
    Code:
    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
    FreelanceNext.com - Freelance Projects / Jobs & more...
    BargainNext.com - coupons / deals / bargains / offers & more...

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,332
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by jaagare View Post
    This works perfectly fine :

    Code:
    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;
    }
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Oct 2008
    Location
    Virtual World is my location
    Posts
    316
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    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
    FreelanceNext.com - Freelance Projects / Jobs & more...
    BargainNext.com - coupons / deals / bargains / offers & more...

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,332
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    is there any chance that you could print the actual sql that the code produces? you know, the SELECT statement
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    Oct 2008
    Location
    Virtual World is my location
    Posts
    316
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    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.
    FreelanceNext.com - Freelance Projects / Jobs & more...
    BargainNext.com - coupons / deals / bargains / offers & more...

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,332
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by jaagare View Post
    In joomla you need to write select query in this way only.
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict
    Join Date
    Oct 2008
    Location
    Virtual World is my location
    Posts
    316
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    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.
    Last edited by jaagare; Mar 8, 2013 at 08:22. Reason: order by changed from st.name to st.date
    FreelanceNext.com - Freelance Projects / Jobs & more...
    BargainNext.com - coupons / deals / bargains / offers & more...

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,332
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by jaagare View Post
    Let me break the query in a simple query for you
    now we're getting some place

    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 --
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Addict
    Join Date
    Oct 2008
    Location
    Virtual World is my location
    Posts
    316
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    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.
    FreelanceNext.com - Freelance Projects / Jobs & more...
    BargainNext.com - coupons / deals / bargains / offers & more...

  10. #10
    SitePoint Addict
    Join Date
    Oct 2008
    Location
    Virtual World is my location
    Posts
    316
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    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
    FreelanceNext.com - Freelance Projects / Jobs & more...
    BargainNext.com - coupons / deals / bargains / offers & more...

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,332
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by jaagare View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Addict
    Join Date
    Oct 2008
    Location
    Virtual World is my location
    Posts
    316
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    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
    FreelanceNext.com - Freelance Projects / Jobs & more...
    BargainNext.com - coupons / deals / bargains / offers & more...

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,332
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by jaagare View Post
    for my query the other rows are missing
    which query would that be? perhaps you could post the latest one you actually used
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Addict
    Join Date
    Oct 2008
    Location
    Virtual World is my location
    Posts
    316
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    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
    FreelanceNext.com - Freelance Projects / Jobs & more...
    BargainNext.com - coupons / deals / bargains / offers & more...

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,332
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by jaagare View Post
    As posted in my earlier post here is the query
    that query won't run at all, because the ORDER BY clause is not last

    try removing the GROUP BY clause altogether
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Addict
    Join Date
    Oct 2008
    Location
    Virtual World is my location
    Posts
    316
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    that query won't run at all, because the ORDER BY clause is not last

    try removing the GROUP BY clause altogether
    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
    FreelanceNext.com - Freelance Projects / Jobs & more...
    BargainNext.com - coupons / deals / bargains / offers & more...


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •