combining data from more than one table
combining data from more than one table
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.
Re: combining data from more than one table
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 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:
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
Re: combining data from more than one table
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.
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.
- Bootstrap 3 Tables
https://getbootstrap.com/docs/3.4/css/#tables - Bootstrap 3 ListGroup
https://getbootstrap.com/docs/3.4/compo ... list-group
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
AppGini 24.10 Revision 1579 + all AppGini Helper tools
<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 readabilityAppGini 24.10 Revision 1579 + all AppGini Helper tools
Re: combining data from more than one table
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
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
Re: combining data from more than one table
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.
Re: combining data from more than one table
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
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
AppGini 24.10 Revision 1579 + all AppGini Helper tools
<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 readabilityAppGini 24.10 Revision 1579 + all AppGini Helper tools
Re: combining data from more than one table
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:
Kind regards,
<js />
My AppGini Blog:
https://appgini.bizzworxx.de/blog
You can help us helping you:
Please always put code fragments inside
AppGini 24.10 Revision 1579 + all AppGini Helper tools
<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 readabilityAppGini 24.10 Revision 1579 + all AppGini Helper tools
Re: combining data from more than one table
I created a view in phpMyAdmin:onoehring wrote: ↑2020-10-08 16:23Hi,
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
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
Re: combining data from more than one table
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.
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.
Re: combining data from more than one table
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 wrote: ↑2020-10-13 16:29Hi,
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.
Re: combining data from more than one table
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 is not tested and may have syntax errors.
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);
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.
Re: combining data from more than one table
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.
Re: combining data from more than one table
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?
}
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?
Re: combining data from more than one table
try
$origin=sqlvalue("SELECT origin from full_schedule where id=".$selectedID);
redirect($origin."_view.php?SelectedID=".$selectedID);
$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.
Re: combining data from more than one table
Fantastic .. that solved it, thank you very much. Much appreciated.
Re: combining data from more than one table
$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
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
Re: combining data from more than one table
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
now you can use
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
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.
Re: combining data from more than one table
Thank you pböttcher. I can't access right now but will be first job tomorrow. Thank you.
Re: combining data from more than one table
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.)
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.)
Re: combining data from more than one table
change
to
concat('meetings',":",`id`) AS `pk`, // <- here meetings is a string, not a field
Code: Select all
concat(`pdt`.`meetings`,":",`id`) AS `pk`,
Code: Select all
concat('meetings',":",`id`) AS `pk`,
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.
Re: combining data from more than one table
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=
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=
Re: combining data from more than one table
Hi,
ok, I think I missed it.
Can you try
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.
Re: combining data from more than one table
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.
Re: combining data from more than one table
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.