0

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?

5
  • Thank you for separating this new question from your previous question. I don't see where $stawka is declared.
    – mickmackusa
    Apr 18 at 8:45
  • Sorry about the <table><div>, I tested different options. Apr 18 at 11:36
  • Ultimately, I wanted to have $stawka = powierzchnia*stawka >> This is it? Apr 18 at 11:49
  • I can't say, to be honest. I am not sure that I am the right person to offer support on this one. I can also say that the non-English in this post adds to my difficulty in understanding where you are stuck.
    – mickmackusa
    Apr 19 at 2:59
  • I started working on my module based on this php script phpzag.com/ajax-drop-down-selection-data-load-with-php-mysql and got stuck on downloading data after selecting an employee from the dropdown list. Apr 19 at 4:21

1 Answer 1

0

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.

4
  • Hello, I have an inquiry from @pe7er : $query = $db->getQuery(true) ->select($db->quoteName(['nazwa', 'powierzchnia', 'state', 'stawka', 'partycypacja', 'skladka', 'woda', 'smieci', 'energia', 'inwestycyjna'])) ->from($db->quoteName('#__dzialki', 'dzialki')) ->leftJoin( $db->quoteName('#__dane', 'dane') . ' ON ' . $db->quoteName('dane.id') . ' = RETRIEVED DATA FROM DROPDOWN ) ->where($db->quoteName('dzialki.state') . ' = 1') ->order('dzialki.id ASC'); I wanted to use them in the get.php file Apr 19 at 13:32
  • In the 'dzialki' table they do not change, in the 'dane' table I change them once a year. All data in the 'dane' table is the same for all records in the 'dzialki' table. The difference is that I multiply 'dzialki.powierzchnia' with 'dane.stawka' or 'data.partycypacja'. Apr 19 at 13:44
  • A small correction to the query: $query = $db->getQuery(true) ->select($db->quoteName(['nazwa', 'powierzchnia', 'state', 'stawka', 'partycypacja', 'skladka', 'woda', 'smieci', 'energia', 'inwestycyjna'])) ->from($db->quoteName('#__dzialki', 'dzialki')) ->leftJoin( $db->quoteName('#__dane', 'dane') . ' ON ' . $db->quoteName('dzialki.id') . ' = RETRIEVED DATA FROM DROPDOWN ) ->where($db->quoteName('dzialki.state') . ' = 1') ->order('dzialki.id ASC'); Apr 19 at 16:28
  • Hello @Grant Hood. One more thing, my table module is only supposed to retrieve data. Apr 20 at 6:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.