AppGini Community Forums

Posted: **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!

Posted: **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;
        }

Posted: **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.

Posted: **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']

Posted: **2015-01-16 12:32**

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

Posted: **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

AppGini Community Forums

Query Another Table and Add Data From That Table

Query Another Table and Add Data From That Table

Re: Query Another Table and Add Data From That Table

Re: Query Another Table and Add Data From That Table

Re: Query Another Table and Add Data From That Table

Re: Query Another Table and Add Data From That Table

Re: Query Another Table and Add Data From That Table