AppGini Community Forums

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

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

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

Bootstrap 3 Tables
https://getbootstrap.com/docs/3.4/css/#tables
Bootstrap 3 ListGroup
https://getbootstrap.com/docs/3.4/compo ... list-group

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.

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

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

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

Posted: **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 (24.06 KiB) Viewed 23042 times

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

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

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

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

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

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

Posted: **2020-10-17 16:03**

try

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

Posted: **2020-10-17 17:05**

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

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

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

Posted: **2020-11-12 21:44**

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

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

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

Posted: **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 (38.97 KiB) Viewed 22609 times

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

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

Posted: **2020-11-14 13:16**

Glad it works now.

AppGini Community Forums

combining data from more than one table

combining data from more than one table

Re: combining data from more than one table

Re: combining data from more than one table

Re: combining data from more than one table

Re: combining data from more than one table

Re: combining data from more than one table

Re: combining data from more than one table

Re: combining data from more than one table

Re: combining data from more than one table

Re: combining data from more than one table

Re: combining data from more than one table

Re: combining data from more than one table

Re: combining data from more than one table

Re: combining data from more than one table

Re: combining data from more than one table

Re: combining data from more than one table

Re: combining data from more than one table

Re: combining data from more than one table

Re: combining data from more than one table

Re: combining data from more than one table

Re: combining data from more than one table

Re: combining data from more than one table

Re: combining data from more than one table

Re: combining data from more than one table