How to return data from 2 tables to one table using a select field

Question

My tables:

dzialki
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| id           | int(11)     | NO   | PRI | NULL    | auto_increment |
| nazwa        | varchar(50) | NO   |     | NULL    |                |
| powierzchnia | varchar(50) | NO   |     | NULL    |                |
| state        | tinyint(1)  | NO   |     | 0       |                |
+--------------+-------------+------+-----+---------+----------------+
    

and

dane
+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| id           | int(11)      | NO   | PRI | NULL    | auto_increment |
| stawka       | decimal(9,2) | NO   |     | NULL    |                |
| partycypacja | decimal(9,2) | NO   |     | NULL    |                |
| skladka      | decimal(9,2) | NO   |     | NULL    |                |
| woda         | decimal(9,2) | NO   |     | NULL    |                |
| smieci       | decimal(9,2) | NO   |     | NULL    |                |
| energia      | decimal(9,2) | NO   |     | NULL    |                |
| inwestycyjna | decimal(9,2) | NO   |     | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+

---

Sorry for the English, I use google translator. I built a component for J4 based on 2 tables (as CRUD) on https://www.component-creator.com. I am trying to make a module based on Ajax examples and it is not working out. On my site https://rod-lubocza.pl/blog/finanse is what I mean in html + js. I am not familiar with php, js and ajax. I write code based on examples found on the web.

As for the module, I have this code in the tmpl/default.php file:

<?php
/**
 * @package     J4.Module
 * @subpackage  mod_kalkulator
 *
 * @copyright   Copyright (C) 2022 ROD Lubocza Wszystkie prawa zastrzeżone.
 * @license     GNU/GPLv3 http://www.gnu.org/licenses/gpl-3.0.html
 */

defined('_JEXEC') or die;
use Joomla\CMS\Language\Text;
use Joomla\CMS\HTML\HTMLHelper;
use Joomla\CMS\Factory;

$db = Factory::getDbo();
$query = $db->getQuery(true);
$query->select($db->quoteName(array('id', 'nazwa', 'powierzchnia', 'status')))
    ->from($db->quoteName('#__kalk_dzialki'))
    ->where($db->quoteName('status') . ' = ' . $db->quote(1))
    ->order($db->quoteName('id') . ' ASC');
$db->setQuery($query);
//echo $db->replacePrefix((string) $query);
//$results = $db->loadAssocList();
$results = $db->loadObjectList();
?>

<div class="kalkulator">
    <div id="kalk" class="mb-3">
        <form action="" method="post">
            <select id="select" class="form-select" aria-label="Default select" autocomplete="off"> 
                <option value="" hidden selected="selected">Wybierz działkę</option>
                <?php foreach ($results as $row){ ?>
                <option value="<?php echo $row->id; ?>"><?php echo $row->nazwa; ?></option>   
                <?php }?>
            </select>
        </form>
    </div>
    <table class="table">
        <tbody>
            <tr class="table-success"><td class="col-6 fw-bold">Numer działki</td><td class="col-3"></td><td class="col-3 text-center fw-bold"><div id="kalnazwa"></div></td></tr>
            <tr><td>Powierzchnia:</td><td class="col-3 text-center">m2</td><td class="col-3 text-end"><div id="kalpowierzchnia"></div></td></tr>
            <tr><td class="col-6">Stawka opłaty ogrodowej</td><td class="col-3 text-center">0.69 za m2</td><td class="col-3 text-end"><div id="kalstawka"></div></td></tr>
            <tr><td class="col-6">Partycypacja</td><td class="col-3 text-center">0.09 za m2</td><td class="col-3 text-end"><div id="kalpartycypacja"></div></td></tr>
            <tr><td class="col-6">Składka członkowska</td><td class="col-3 text-center">od działki</td><td class="col-3 text-end"><div id="kalskladka"></div></td></tr>
            <tr><td class="col-6">Opłata za wodę</td><td class="col-3 text-center">od działki</td><td class="col-3 text-end"><div id="kalwoda"></div></td></tr>
            <tr><td class="col-6">Opłata za śmieci</td><td class="col-3 text-center">od działki</td><td class="col-3 text-end"><div id="kalsmieci"></div></td></tr>
            <tr><td class="col-6">Opłata energetyczna</td><td class="col-3 text-center">od działki</td><td class="col-3 text-end"><div id="kalenergia"></div></td></tr>
            <tr><td class="col-6">Opłata inwestycyjna</td><td class="col-3 text-center">od działki</td><td class="col-3 text-end"><div id="kalinwestycyjna"></div></td></tr>
            <tr class="table-success"><td class="col-6 fw-bold">Do zapłaty</td><td class="col-3 text-center"></td><td class="col-3 text-end fw-bold"><div id="kaldozaplaty"></div></td></tr>
        </tbody>            
    </table>   
</div>

I want to return data from 2 tables mysql to a table html using the select field. Can I ask for help?

Answer 1

First, you need to work out how entries in the 'dzialki' table are related to entries in the 'dane' table. Is it a one-to-one or a one-to-many relationship?

If it is one-to-one, that will be more straightforward to get working. You will need to add a new column to the dzialki table which will contain the relevant id from the dane table. Then, you will need to alter your SQL query to include a left join to the dane table to include the relevant data from 'dane' in your results. We can help you with the syntax for the query, but the first step is for you to confirm if one-to-one is correct for your context.

If you are confused by the above, please let me know and I will try to explain more clearly.