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
Veteran Member
Posts: 84
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.

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
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

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
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 />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

graham
Veteran Member
Posts: 84
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
Veteran Member
Posts: 84
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: 1807
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 />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
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 22949 times
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

graham
Veteran Member
Posts: 84
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

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: combining data from more than one table

Post by pbottcher » 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.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

graham
Veteran Member
Posts: 84
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.

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: combining data from more than one table

Post by pbottcher » 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.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

graham
Veteran Member
Posts: 84
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
Veteran Member
Posts: 84
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?

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: combining data from more than one table

Post by pbottcher » 2020-10-17 16:03

try

$origin=sqlvalue("SELECT origin from full_schedule where id=".$selectedID);
redirect($origin."_view.php?SelectedID=".$selectedID);
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

graham
Veteran Member
Posts: 84
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.

graham
Veteran Member
Posts: 84
Joined: 2020-09-29 12:30

Re: combining data from more than one table

Post by graham » 2020-11-11 10:02

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

I've just discovered it doesn't work:

ID Origin
2 meetings
2 conferences

When there is more than one records with the same ID, it always goes to the table of the first record.

For example, both of the above are taken to meetings_view.php?SelectedID=2
If I force the redirect, it works - i.e. redirect("conferences_view.php?SelectedID=".$selectedID);

I though it was working before - but I didn't test enough records!

I've spent a few hours on it and can't work it out - any thoughts would be much appreciated. Thanks

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: combining data from more than one table

Post by pbottcher » 2020-11-12 21:34

HI,

sorry for the missunderstanding. I thought you have a unique key which will not be duplicated between the two tables.

So create you view table as
pk -> primary key varchar(40)
date -> like in the original table
title -> like in the original table


So in you case, create the view somehow like

Code: Select all

CREATE VIEW `new_schedule`
 AS select concat('meetings',":",`id`)  AS `pk`, `date` AS `date`, `title` AS `title` from `meetings` union select concat('conferences',":",`id`)  AS `pk`, `date` AS `date`, `title` AS `title` from conferences
now you can use

Code: Select all

$res=sqlvalue("SELECT pk from full_schedule where id=".$selectedID);
$origin=explode(":",$res);
redirect($origin[0]."_view.php?SelectedID=".$origin[1]);
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

graham
Veteran Member
Posts: 84
Joined: 2020-09-29 12:30

Re: combining data from more than one table

Post by graham » 2020-11-12 21:44

Thank you pböttcher. I can't access right now but will be first job tomorrow. Thank you.

graham
Veteran Member
Posts: 84
Joined: 2020-09-29 12:30

Re: combining data from more than one table

Post by graham » 2020-11-13 13:13

After several more hours, and lots of experimenting, I can't get it working.
So if we stick with the proposed solution, the first hurdle is that phpMyAdmin won't allow the creation of the View:
CREATE VIEW `schedule_2` AS
SELECT
concat(`pdt`.`meetings`,":",`id`) AS `pk`,
`pdt`.`meetings`.`country` AS `country`,
`pdt`.`meetings`.`origin` AS `origin`,
`pdt`.`meetings`.`date` AS `date`,
`pdt`.`meetings`.`title` AS `title`,
`pdt`.`meetings`.`category` AS `category`
FROM
`pdt`.`meetings`
(and the the rest of the UNION ALLs)

MySQL said: Documentation
#1054 - Unknown column 'pdt.meetings' in 'field list'
(If I remove the concat line, or use the same format as the other lines, it's fine.)

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: combining data from more than one table

Post by pbottcher » 2020-11-13 15:35

change

Code: Select all

concat(`pdt`.`meetings`,":",`id`) AS `pk`,
to

Code: Select all

concat('meetings',":",`id`) AS `pk`,
concat('meetings',":",`id`) AS `pk`, // <- here meetings is a string, not a field
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

graham
Veteran Member
Posts: 84
Joined: 2020-09-29 12:30

Re: combining data from more than one table

Post by graham » 2020-11-13 17:09

Hmmm. Ahh. It does seems obvious now!
And I have my View (see attached).
But still no results:
hooks/schedule2.php =
function schedule_2_dv($selectedID, $memberInfo, &$html, &$args) {
$res=sqlvalue("SELECT pk from schedule_2 where id=".$selectedID);
$origin=explode(":",$res);
redirect($origin[0]."_view.php?SelectedID=".$origin[1]);
}
gives: pdt/_view.php?SelectedID=
pk.png
pk.png (38.97 KiB) Viewed 22516 times

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: combining data from more than one table

Post by pbottcher » 2020-11-13 19:34

Hi,

ok, I think I missed it.

Can you try

Code: Select all

function schedule_2_dv($selectedID, $memberInfo, &$html, &$args) {
$origin=explode(":",$selectedID);
redirect($origin[0]."_view.php?SelectedID=".$origin[1]);
}
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

graham
Veteran Member
Posts: 84
Joined: 2020-09-29 12:30

Re: combining data from more than one table

Post by graham » 2020-11-14 13:14

Success! Thank you very much. I think this is a really useful facility - create a pseudo table via a view, combine data from different tables and then linking to the correct dv. Thanks again.

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: combining data from more than one table

Post by pbottcher » 2020-11-14 13:16

Glad it works now.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

Post Reply