combining data from more than one table

If you're a new user of AppGini, feel free to ask general usage questions, or look for answers here.
Post Reply
graham
Posts: 28
Joined: 2020-09-29 12:30

combining data from more than one table

Post by graham » 2020-10-08 14:39

Hello. I have a number of tables that hold details about different activities - meetings, events etc. Each activity in each table has a date. What I want to do is to show a schedule of all the activities from all tables on one screen - another table or report perhaps? It might seem obvious to put all the activities in one table but I don't want to do that because each activity table has it's own look up fields (allowing people to add comments about a particular activity in a 'comments' table or an 'actions' table) so it would make one main table very messy. If anyone has any thoughts that would be really appreciated. I'm thinking that this is going to be an ongoing requirement - to be able to draw out and combine records from two tables or more (that are of the same field type). Thanks in advance.

onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 578
Joined: 2019-05-21 22:42
Contact:

Re: combining data from more than one table

Post by onoehring » 2020-10-08 16:23

Hi,

I think you should try to create a view (let's say we name it v_all_my_tables) in your database which collects all data that you want to display. Once that is finished, go to AG and create a "pseudo" table that has the same name as the view: v_all_my_tables. Generate your app and AG will generate a display for your view - done :-)

So, does this work for you?
Olaf
Some postings I was involved, you might find useful:
Backup your database (viewtopic.php?f=4&t=3341); Improve security (viewtopic.php?f=4&t=3168); Field Permissions (viewtopic.php?f=4&t=3308); Custom (error) message (viewtopic.php?f=7&t=1740&p=10871#p10906); Audit Log (viewtopic.php?f=4&t=1369&p=10407); Two Factor Authentication (viewtopic.php?f=7&t=3306&p=11478); Add 2nd SAVE CHANGES button (viewtopic.php?f=2&t=3242&p=11104); Place a search on details view (viewtopic.php?f=2&t=3479&p=12484#p12484); Column-Value-Based-Permissions (viewtopic.php?f=4&t=3498)

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 747
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: combining data from more than one table

Post by jsetzer » 2020-10-08 17:18

An alternative to an additional view is a custom page (https://bigprof.com/appgini/help/advanc ... cess-pages), on which the information can be collected from database using the built-in sql-command and displayed, for example as a table or list.
Tip:
From my experience it is very helpful if all relevant tables have a few column names in common like id, subject, description, created_by, created_on. This makes it quite generic to fetch relevant data from a string-array of tablenames using a foreach loop.
Kind regards,
<js />

graham
Posts: 28
Joined: 2020-09-29 12:30

Re: combining data from more than one table

Post by graham » 2020-10-09 09:51

Thanks Olaf & JS
I'd like to get both your methods working so have I have some tools to work with.

"I think you should try to create a view (let's say we name it v_all_my_tables) in your database which collects all data that you want to display."
So I've gone to phpMyAdmin and worked out how the principle of creating a view. (As you can tell, I'm pretty new to this!) But, I'm having trouble writing the SQL statement. What I want to say is "SELECT date, field2 FROM meetings AND date, field2 FROM events AND date, field2 from conferences" and so on. I've tried a lot of angles from various references but statement always errors. I appreciate it's not strictly an AppGini question but any quick thoughts on a suitable SQL statement? By the way, the tables are not related.

As suggested by JS I've got a custom page working but it looks like it needs the same SQL statement as Olaf's solution and that's where I'm stuck at the moment. Any thoughts much appreciated. Thanks

graham
Posts: 28
Joined: 2020-09-29 12:30

Re: combining data from more than one table

Post by graham » 2020-10-09 10:20

Also .. I was thinking ... if I can display all the events from all the tables (and display by date order) the other thing is that I would like each event to be linked to be automatically linked to the the appropriate detail view. Olaf, would that happen automatically with your version of the solution and JS - I'm thinking that it would not with yours? Thanks.

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 747
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: combining data from more than one table

Post by jsetzer » 2020-10-09 10:43

For sure you can create table-rows (or list-items) as you like and, for example, put a link to the detail view into the first cell of each row:

not tested code

Code: Select all

<?php

  // get your search results using SQL
  // $rows = 
?>
<table class="table table-bordered table-striped table-hover">
<!-- use PHP and iterate through all your tables and search results. For example variable $row -->
<?php foreach($rows as $row) { ?>

<tr>
  <th><a href="<?=$row["tableName"]?>_view.php?SelectedID=<?=$row["id"]?>"><?=$row["subject"]?></a></th>
  <!-- more columns here -->
</tr>

<?php } ?>
<!-- end of iteration -->
</table>
Kind regards,
<js />

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 747
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: combining data from more than one table

Post by jsetzer » 2020-10-09 10:45

Example screenshot (Bootstrap 3 list-group-items) "Recently Viewed Records" (per user) showing a list of records from different tables. Each list-view-item is linked to the detail view:
chrome_J31oVO6zQO.png
chrome_J31oVO6zQO.png (24.06 KiB) Viewed 275 times
Kind regards,
<js />

graham
Posts: 28
Joined: 2020-09-29 12:30

Re: combining data from more than one table

Post by graham » 2020-10-13 08:34

onoehring wrote:
2020-10-08 16:23
Hi,

I think you should try to create a view (let's say we name it v_all_my_tables) in your database which collects all data that you want to display. Once that is finished, go to AG and create a "pseudo" table that has the same name as the view: v_all_my_tables. Generate your app and AG will generate a display for your view - done :-)

So, does this work for you?
Olaf
I created a view in phpMyAdmin:

CREATE VIEW full_schedule AS

SELECT
`ID`,
`date`,
`title`
FROM meetings

UNION ALL

SELECT
`ID`,
`date`,
`title`
FROM study_visits

and the "pseudo" table in AppGini - 'full_schedule' with fields ID, date and title. It worked - I now get my combined data in the familiar AppGini TV/DV. But the solution is not complete because when I see the list what I want to do is to click and go to the DV of the original listing. In my 'full_schedule' view all I have is the date and the title of, say, a meeting. How can I connect from 'full_schedule' to the actual DV of the record in the 'meetings' table?

Any pointers much appreciated. Thanks

pböttcher
AppGini Super Hero
AppGini Super Hero
Posts: 1037
Joined: 2018-04-01 10:12

Re: combining data from more than one table

Post by pböttcher » 2020-10-13 16:29

Hi,

if your ID make it possible to identify from which table the record comes, you can use that logic, or
if you add the origin table to the record you could retrieve that information together with the selected id and redirect in the hooks/VIEW.php view_dv function directly to your desired page.

graham
Posts: 28
Joined: 2020-09-29 12:30

Re: combining data from more than one table

Post by graham » 2020-10-16 14:27

pböttcher wrote:
2020-10-13 16:29
Hi,

if your ID make it possible to identify from which table the record comes, you can use that logic, or
if you add the origin table to the record you could retrieve that information together with the selected id and redirect in the hooks/VIEW.php view_dv function directly to your desired page.
Thank you pböttcher . The truth is I don't understand enough to understand your comment. I have confidence that it will work, and I need to achieve this, but could you just give a little more explanation if you can so I have a better idea what to do next for both solutions. Much appreciated if you can. Thanks.

pböttcher
AppGini Super Hero
AppGini Super Hero
Posts: 1037
Joined: 2018-04-01 10:12

Re: combining data from more than one table

Post by pböttcher » 2020-10-16 19:07

Hi,

if you use

CREATE VIEW full_schedule AS

SELECT
`ID`,
`date`,
`title`
FROM meetings

UNION ALL

SELECT
`ID`,
`date`,
`title`
FROM study_visits

and have in meeting e.g. ID=1, date=16.10.2020 and title = meeting
and in study_visits e.g. ID=1, data=16.10.2020 and title = study.

You will have in the view

ID | date | title
1 | 16.10.2020 | meeting
1 | 16.10.2020 | study

so it will be (almost) impossible to say to which table the first record belongs and to which the second one.

now if you add e.g. an unique identifier you can use this one to identify the origin.

CREATE VIEW full_schedule AS

SELECT
`ID`,
'meeting' as origin,
`date`,
`title`
FROM meetings

UNION ALL

SELECT
`ID`,
,study' as origin,
`date`,
`title`
FROM study_visits

will give you e.g.

ID | origin | date | title
1 | meeting | 16.10.2020 | meeting
1 | study | 16.10.2020 | study

So now you can use the origin. to identify the origin

in the full_schedule_dv function you can try

Code: Select all

$origin=sqlvalue('SELECT origin from full_schedule where id=".$selectedID);
redirect($origin."_view.php?SelectedID=".$selectedID);
Code is not tested and may have syntax errors.

graham
Posts: 28
Joined: 2020-09-29 12:30

Re: combining data from more than one table

Post by graham » 2020-10-16 19:24

Thank you. I can't study tonight but look forward to trying to make it work tomorrow. I think this will be a really useful feature for the community if we can make it happen. Thank you again.

graham
Posts: 28
Joined: 2020-09-29 12:30

Re: combining data from more than one table

Post by graham » 2020-10-17 14:53

So I added a new field in meetings - 'origin' and set it to default to 'meeting' and same for study_visits 'study' and added them to the SQL for the View. That's working. Then in full_schedule,php I located function full_schedule_dv($selectedID, $memberInfo, &$html, &$args) {
}
but not sure now implement your code...
function full_schedule_dv($selectedID, $memberInfo, &$html, &$args) {
$origin=sqlvalue('SELECT origin from full_schedule where id=".$selectedID);
redirect($origin."_view.php?SelectedID=".$selectedID);
}
isn't accepted?

pböttcher
AppGini Super Hero
AppGini Super Hero
Posts: 1037
Joined: 2018-04-01 10:12

Re: combining data from more than one table

Post by pböttcher » 2020-10-17 16:03

try

$origin=sqlvalue("SELECT origin from full_schedule where id=".$selectedID);
redirect($origin."_view.php?SelectedID=".$selectedID);

graham
Posts: 28
Joined: 2020-09-29 12:30

Re: combining data from more than one table

Post by graham » 2020-10-17 17:05

Fantastic .. that solved it, thank you very much. Much appreciated.

Post Reply