Query Another Table and Add Data From That Table

Discussions related to customizing hooks. Hooks are documented at http://bigprof.com/appgini/help/advanced-topics/hooks/
Post Reply
athreeiron
Posts: 3
Joined: 2015-01-01 08:13

Query Another Table and Add Data From That Table

Post by athreeiron » 2015-01-01 09:06

Hey, I'm new here, and so far the application is really nice. I'm trying to learn the hooks because really without them the application is just a front end to a simple database.

I have a Bills table and a Payments table. There are multiple Payments for each Bill and each of those Payment records has a PaymentDate. What I'm trying to do is on the Bills view page, add either a totally new column, or concatenate it into another existing field in the list, the most recent payment date. So basically, for each time the main Bills view pulls a record, I want to run this other query to get the last Payment made and pull and display the date of that last payment for each Bill in the list. I'm not asking for someone to code the thing for me, but I would like to know what structure to use. Again, I just bought this thing yesterday so I don't know a lot yet, but just the general structure inside the Bills_init hooks section would be greatly appreciated. I've tried to piece together how to do this from the forums and other help on the site, but can't really put it all together in my mind how it should go.

Thanks in advance!

athreeiron
Posts: 3
Joined: 2015-01-01 08:13

Re: Query Another Table and Add Data From That Table

Post by athreeiron » 2015-01-01 13:10

Ok, I've made a little progress. Most of it is working, but what I cannot find for the life of me is how to pull the value of the current ID so I can use it to query the database for relating Payments.

Right now I have sub_ID = '5' and that works, although I can't get proper date formatting to work, yet. How do I replace the 5 with the current BillID in the iteration?

Code: Select all

 function Bills_init(&$options, $memberInfo, &$args){
                $oldFields = $options->QueryFieldsTV;
                foreach($oldFields as $field => $title) {
                if($field == '`Bills`.`BillDueDate`') {
                        $dbcnx = mysqli_connect('localhost', 'root', 'theDung30n', 'Bills');
                        $sub_ID = '5';
                        //
                        //$sub_ID_string = mysqli_real_escape_string($sub_ID);
                        $sql = "SELECT PaymentDate FROM Payments WHERE BillID = '$sub_ID' ORDER BY PaymentDate Desc LIMIT 1";
                        $query = mysqli_query($dbcnx, $sql) or die(mysql_error());
                        exit($sql);
                        while($row = mysqli_fetch_array($query)) {
                                $PaymentDate = $row['PaymentDate'];
                        }
                        //exit($PaymentDate);
                        $modField = "CONCAT($PaymentDate, ' | ', `Bills`.`BillDueDate`, 'horseshit')";
                        //$mod_field = "CONCAT($BillDueDate, $row['PaymentDate'])";
                        $newFields[$modField] = $title;
                        } else {
                                $newFields[$field] = $title;
                        }
                        }
                        $options->QueryFieldsTV = $newFields;

                return TRUE;
        }

athreeiron
Posts: 3
Joined: 2015-01-01 08:13

Re: Query Another Table and Add Data From That Table

Post by athreeiron » 2015-01-01 13:11

The reason the exit($sql) is in there was for when I was trying to test out different things to try and get the BillID to sub_ID, and that allows me to see what it is actually passing.

User avatar
a.gneady
Site Admin
Posts: 1281
Joined: 2012-09-27 14:46
Contact:

Re: Query Another Table and Add Data From That Table

Post by a.gneady » 2015-01-08 22:24

athreeiron wrote:what I cannot find for the life of me is how to pull the value of the current ID so I can use it to query the database for relating Payments
Try $_REQUEST['SelectedID']
:idea: AppGini plugins to add more power to your apps:
  • DataTalk is an innovative AppGini plugin based on ChatGPT that allows you to interact with your AppGini database using natural language questions, without writing any SQL. Check the demo video
  • Mass Update plugin: Update multiple records at once and improve your workflow efficiency.
  • Check our other plugins and get a generous discount of up to 30% when buying 2 or more plugins.

mike4e
Posts: 3
Joined: 2014-12-10 09:39

Re: Query Another Table and Add Data From That Table

Post by mike4e » 2015-01-16 12:32

Hi, have you managed to find a solution? I'm battling with the same issue. Many thanks, Mike

mgodinez
Posts: 7
Joined: 2013-10-30 16:20

Re: Query Another Table and Add Data From That Table

Post by mgodinez » 2023-04-04 14:14

+1 - Same here. Looking for something similar, to list the records in a field, from another field. Example:

Purchased items: Item1
Item2
Item3 .... and so on

The field "Purchased items" will display the items from another table that match the user who had purchased those items, only those items.

Thanks

Post Reply