php Query INNER join tables

[allogos]

In my Joomla component I have two table:

Table1: #__com_units

Code: Select all

Id | Building | Floor |unit_number | posx | posy | mood
 -------------------------------------------------------
  1 | 01       | 01    | 001A       | 100 | 200   |Rented
  2 | 01       | 01    | 002A       | 101 | 202   |Available
  3 | 01       | 01    | 003A       | 102 | 204   |Available
  4 | 01       | 01    | 004A       | 103 | 206   |Available
  5 | 01       | 01    | 005A       | 104 | 208   |Rented
  6 | 01       | 01    | 006A       | 103 | 206   |Available
  7 | 01       | 01    | 007A       | 104 | 208   |Rented
  8 | 01       | 01    | 008A       | 103 | 206   |Rented
  9 | 01       | 01    | 009A       | 104 | 208   |Rented
 10 | 01       | 01    | 010A       | 103 | 206   |Available

Table1: #__com_reservations

Code: Select all

 Id | Building | Floor |unit    | confirmation
 --------------------------------------------------
  1 | 01       | 01    | 002A   | NO
  2 | 01       | 01    | 003A   | YES
  3 | 01       | 01    | 004A   | NO
  4 | 01       | 01    | 006A   | NO
  5 | 01       | 01    | 010A   | YES

i want to get query to show units as buttons which "mood=Rented" and "confirmation=YES" but its not working:

Code: Select all

<?php
    $db = JFactory::getDbo();
    $query = $db->getQuery(true);
    $jinput = JFactory::getApplication()->input;

    $query->select($db->quoteName(array(
        'b.unit_number', 
        'a.confirmation',
        'b.posx',
        'b.id', 
        'a.unit', 
        'b.posy', 
        'b.mood'
    )));

    $query->from($db->quoteName('#__com_reservations','a' ))
              ->join('INNER', $db->quoteName('#__com_units', 'b') . ' ON (' . $db->quoteName('b.unit_number') . ' = ' . $db->quoteName('a.unit') . ')');

    $query->where($db->quoteName('b.floor')." = ".$db->quoteName('a.floor'),'AND')
          ->where($db->quoteName('b.building')." = ".$db->quoteName('a.building'),'AND')
          ->where($db->quoteName('a.confirmation')." = ".$db->quote('YES'));

    $db->setQuery($query);

    $results = $db->loadObjectList();

    foreach ($results as $result) {
        echo '<button class=" ' . $result->mood . ' ' . $result->posx . ' ' . $result->posy . '" value=' . $result->id . ' disabled> ' . $result->Unit_number  . '</button>';
    }
?>

[sovainfo]

You only select 003A and 010A because they have confirmation is 'YES'. Both of them have mood: Available.

Might be helpful if you mention what you got!

[allogos]

I'm not getting any error, but getting blank result!
for single table i have similar query and working very well but here something wrong with inner join statement.

[allogos]

I found something, if i use join LEFT result is showing but the variable from com_units is not getting, then i tried join OUTER and im getting Error: 1064...

[sovainfo]

It should be an INNER JOIN, but the LEFT JOIN produced results with no values for the right table! That means unit and unit_number don't have the same content!

[allogos]

Thank you for your guide, i found my problem, Again i used INNER JOIN and quoteName('b.id'). This worked for me.

[allogos]

It should be an INNER JOIN, but the LEFT JOIN produced results with no values for the right table! That means unit and unit_number don't have the same content!

What if i want list of all units from table #__com_units which mood=available but exclude units from #__com_reservation confirmation=yes

[sovainfo]

Use SELECT #__com_units INNER JOIN #__com_reservation ON ....
WHERE mood = 'Available' AND confirmation != 'Yes'