PHP查询内部联接表 [英] php Query INNER join tables

问题描述

在我的Joomla组件中，我有两个表:

In my Joomla component I have two table:

表1:#__ com_units

Table1: #__com_units

 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

表1:#__ com_reservations

Table1: #__com_reservations

 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

我希望查询以"mood = Rented"和"confirmation = YES"按钮显示单位但按钮不起作用的情况:

<?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>';
    }
?>

推荐答案

 SELECT * FROM `#__com_reservations` A 
    JOIN `#__com_units` B
    ON A.unit = B.unit_number 
    AND A.building = B.building 
    AND A.floor = B.floor 
    WHERE A.confirmation = 'Yes' 
    AND B.mood = 'Available'

即使我不熟悉joomla，也希望此mysql查询会有所帮助.

Even though i'm not familiar with joomla hope this mysql query will be helpful.

这篇关于PHP查询内部联接表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！