1084

custom queries for show view

Comments for “custom queries for show view”
 

Posted by KeepCalm on Monday 18th July 2022 at 20:57 GMT

I wish to replace an integer id field with a lookup string value from another table in the Modules->Clients->show.php file.
I have created a function with an SQL query to get the related value, but I cannot call the function in the show.php file.
I keep getting a "function is undefined" error while trying to call the function.
Any pointers will be appreciated, Thanks.
Trongate is Awesome!

KeepCalm

User Level: Guest

Date Joined: 15/07/2022

Posted by Myhiideas on Monday 18th July 2022 at 21:07 GMT

Hello KeepCalm,
Posting your function and you call to the function will help.
Use
code and /code tags with your code inbetween and [ ] around the word code and /code
Look at this from the docs:
https://trongate.io/docs_m/information/calling-another-module-from-a-view

 



Dan

This comment was edited by Myhiideas on Tuesday 19th July 2022 at 18:25 GMT

Myhiideas

User Level: Guest

Date Joined: 1/11/2021

Posted by KeepCalm on Monday 18th July 2022 at 21:19 GMT

Thanks djnordeen, here is the function
        function getlocation() {
            $sql = 'SELECT
             clients.location_id,
             locations.location 
            FROM
             clients
            INNER JOIN
             locations
            ON
             clients.location_id = locations.id
            WHERE clients.id = '.segment(3);
            $result=$this->model->query($sql,'string');
           // json($result); testing displays correctly
            return $result;
    }

I tried to call the function in the show.php file :
 
                div class="row"
                    div Location ID
                 < ?=  getlocation() ? >


Ok This issue has been solved. I deleted the function and added the SQL directly to the show() function.

This comment was edited by KeepCalm on Monday 18th July 2022 at 23:17 GMT

KeepCalm

User Level: Guest

Date Joined: 15/07/2022

Posted by DaFa on Wednesday 20th July 2022 at 06:55 GMT

Hi KeepCalm,

Yes, Trongate is Awesome!

Just following up on your previous post. It's good that you have moved the SQL to the controller, but you didn't have to delete the method getlocation(), you could have just added an underscore to make it inaccessible from the URL, like _getlocation() and more importantly you should be more careful with the query() method when passing from the URL, as it's a real threat of SQL injection.
    public function query($sql, $return_type = false) {

        //WARNING: very high risk of SQL injection - use with caution!
        $data = [];

        if ($this->debug == true) {
            $query_to_execute = $this->show_query($sql, $data);
        }

        $this->prepare_and_execute($sql, $data);

        if (($return_type == 'object') || ($return_type == 'array')) {

            if ($return_type == 'object') {
                $query = $this->stmt->fetchAll(PDO::FETCH_OBJ);
            } else {
                $query = $this->stmt->fetchAll(PDO::FETCH_ASSOC);
            }

            return $query;
        }
    }


A more safe method would be to use query_bind().

Let me know if you need help with that?

This comment was edited by DaFa on Friday 22nd July 2022 at 13:24 GMT

Founding Member

DaFa

User Level: Founding Member

Date Joined: 30/11/2018

Posted by KeepCalm on Friday 22nd July 2022 at 23:51 GMT

Thank you Dafa for following up.
After reading the documentation, I decided to use Trongate's database methods like get() and get_where()
where suitable, instead of my previous direct sql calls to the database.

Where I am struggling however, is in getting dependent dropdowns to work.
I suspect that I may have to use javascript to listen for a change event from the first dropdown and then call a function to
populate the second dropdown, but javascript and php are new to me (I am a former VBA developer).

My project heavily relies on dependent dropdowns (building a Task logging, assignment and reporting app for a company intranet), so any tips would be welcome.

Cheers!

KeepCalm

User Level: Guest

Date Joined: 15/07/2022

Posted by DaFa on Sunday 24th July 2022 at 09:34 GMT

Hi KeepCalm,

I'm a fellow VBA coder and have created some pretty heavy apps in MS Access for local government and other clients. I don't do much of that now, as my focus has shifted solely to web apps. However, I still have some clients that use my projects with a MS Access front end for desktop and a web app for mobile that connects to the same db via an ODBC connection.

As for your linked dropdowns, yes JavaScript is the solution. There are many tutorials on YT and the net that solve this for you.

Here is an example on W3S > https://www.w3schools.com/howto/howto_js_cascading_dropdown.asp

If you need help, let me know :)

This comment was edited by DaFa on Sunday 24th July 2022 at 09:37 GMT

Founding Member

DaFa

User Level: Founding Member

Date Joined: 30/11/2018

Posted by KeepCalm on Wednesday 27th July 2022 at 01:29 GMT

Hi Simon, I eventually got my dynamic dropdowns to work quite well using ajax/jquery,
The performance is good but I am wondering if the same could be done using plain javascript instead without the overhead of jquery.
Otherwise, I must say that Trongate almost makes my project easy, as a total web/php noob, I have made more progress in less than two weeks of using Trongate than I did trying to get the same project done in WPF over two months.
The only thing that I am completely stumped on is how to handle multiple instances of sub forms, but i think that is more so due to my lack of html experience rather than Trongate's limits.
I really love this framework and I wished I had discovered it sooner!

Cheers!

This comment was edited by KeepCalm on Wednesday 27th July 2022 at 03:01 GMT

KeepCalm

User Level: Guest

Date Joined: 15/07/2022

×