Batch Action multi sources.

The recommended method of customizing your AppGini-generated application is through hooks. But sometimes you might need to add functionality not accessible through hooks. You can discuss this here.
Post Reply
User avatar
Jay Webb
Veteran Member
Posts: 55
Joined: 2017-08-26 15:27
Contact:

Batch Action multi sources.

Post by Jay Webb » 2019-08-13 04:53

Hello All,

I have a Batch Action that makes a datasheet from a table view that has several lookup fields which display at present there id's. "Red circles"

here is my code;

Code: Select all

<?php
	$curr_dir = dirname(__FILE__);
	include("{$curr_dir}/defaultLang.php");
	include("{$curr_dir}/language.php");
	include("{$curr_dir}/lib.php");
	$table = $_REQUEST['table'];
	$ids = $_REQUEST['ids'];
	$cs_ids = '';
	foreach($ids as $id){
		$cs_ids .= "'" . makeSafe($id) . "',";
	}
	$cs_ids = substr($cs_ids, 0, -1); 
	$res = sql( "select * from marriage " .
				"where id in ({$cs_ids})", $eo);
	while($row = db_fetch_assoc($res)){
		?>
	<link rel="stylesheet" href="table.css">
	<div class="divTable" style="width:100%">
		<div class="divTableBody">
			<div class="divTableRow">
				<div class="divTableCell"> <i><font size="4"><b>Clan MacLellan Marriage Datasheet for &#10148; &nbsp;<u><?php echo $row['forename']; ?>&nbsp;<?php echo $row['surname']; ?></u></b></font></i>   <!--surname is a lookup from table surname, column id.-->
					<br>
					<br> <i><b>Year &#10148; &nbsp;</b><u><?php echo $row['year']; ?></u></i>
					<br> <i><b>Gender &#10148; &nbsp;</b><u><?php echo $row['gender']; ?></u></i>
					<br> <i><b>Surname &#10148; &nbsp;</b><u><?php echo $row['surname']; ?></u></i>   <!--surname is a lookup from table surname, column id-->
					<br> <i><b>First Name &#10148; &nbsp;</b><u><?php echo $row['forename']; ?></u></i>
					<br> <i><b>Spouse Surname &#10148; &nbsp;</b><u><?php echo $row['spouse_surname']; ?></u></i>
					<br> <i><b>Spouse Forename &#10148; &nbsp;</b><u><?php echo $row['spouse_forename']; ?></u></i>
					<br> <i><b>Marriage Date &#10148; &nbsp;</b><u><?php echo $row['marriage_date']; ?></u></i>
					<br> <i><b>2nd Marriage &#10148; &nbsp;</b><u><?php echo $row['marriage_2']; ?></u></i>  <!--2nd Marriage is a lookup from table other_marriages, column id-->
					<br> <i><b>3rd Marriage &#10148; &nbsp;</b><u><?php echo $row['marriage_3']; ?></u></i>   <!--3rd Marriage is a lookup from table other_marriages, column id-->
					<br> <i><b>4th Marriage &#10148; &nbsp;</b><u><?php echo $row['marriage_4']; ?></u></i>   <!--4th Marriage is a lookup from table other_marriages, column id-->
					<br> <i><b>Parish or County &#10148; &nbsp;</b><u><?php echo $row['parish_county']; ?></u></i>
					<br> <i><b>Country &#10148; &nbsp;</b><u><?php echo $row['country']; ?></u></i>
					<br> <i><b>Family File Number &#10148; &nbsp;</b><u><?php echo $row['ff_number']; ?></u></i>   <!--ff_number is a lookup from table ff_number, column id-->
					<br> <i><b>Comments &#10148;</b><div class="trim"><?php echo $row['comments']; ?></div></i>
					<br>
					<br>
					<div style="width: 100%; height: 20px; border-bottom: 1px solid black; text-align: center"> <span style="font-size: 18px; background-color: #F3F5F6; padding: 0 10px;">End of data sheet.</span></div>
					<div class="footer"><img src="hooks/images/CM_ThinkOn.png"></div>
				</div>
			</div>
		</div>
	</div>
	<?php
	}
and the results

Image

problem is several fields are lookups to different tables and are represented by there id's and I need to display the text.

How do I switch to another table pull the id information, switch back to main table, switch to 3rd table pull id information, switch back and so on.

Can it even be done?
What we envision, we make happen.

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

Re: Batch Action multi sources.

Post by jsetzer » 2019-08-13 05:17

Hi,

there are at least two ways to merge data from related tables:
  1. The pure SQL way
    (recommended)
    Change your SQL query and use SQL's LEFT JOIN or INNER JOIN, for example:

    Code: Select all

    // ...
    $sql = "SELECT 
      `marriage`.*
    , COALESCE(`othertable`.`column_name`, 'your_default_value') as 'othertable_column' 
    FROM `marriage`
    LEFT JOIN `othertable` ON `marriage`.`foreign_key` = `othertable`.`primary_key`
    WHERE `marriage`.`id` in ({$cs_ids})";
    $res = sql($sql, $eo);
    // ...
    
    Within your loop you can use...

    Code: Select all

    $row["othertable_column"]
    
    ...to get the data from the other table.

    ----
  2. Fetch data within iteration
    (less efficient but perhaps better to read and understand)
    Within your white-loop (for each marriage-record) fetch the required value for example:

    Code: Select all

    $foreign_key = $row["othertable_column_name"];
    $othertable_column = sqlValue("SELECT `column_name` FROM `othertable` WHERE `othertable`.`primary_key`='{$foreign_key}' LIMIT 1");
    
    For populating your div's you can use...

    Code: Select all

    $othertable_column
    
    ...to get the value.

    Replace variables according to you table names and column names. Repeat for every related table.

    Best Regards,
    Jan

    PS:
    For better understanding of relational databases and joins (NOT: Joints :shock: ) I recommend the following pages:

    Left joins:
    https://www.w3schools.com/sql/sql_join_left.asp

    Inner joins:
    https://www.w3schools.com/sql/sql_join_inner.asp

User avatar
Jay Webb
Veteran Member
Posts: 55
Joined: 2017-08-26 15:27
Contact:

Re: Batch Action multi sources.

Post by Jay Webb » 2019-08-13 16:52

Hi Jan,

Thank you for the assistance, I went with the recommended way with;

Code: Select all

$sql = "SELECT 
  `marriage`.*
, COALESCE(`surname`.`surnames`, 'id') as 'surname' 
FROM `marriage`
LEFT JOIN `surname` ON `marriage`.`surname` = `surname`.`id`
WHERE `marriage`.`id` in ({$cs_ids})";
$res = sql($sql, $eo);
and the results worked,
Image

I then tried adding a second COALESCE statement for the other foreign_key,

Code: Select all

$sql = "SELECT 
  `marriage`.*
, COALESCE(`surname`.`surnames`, 'id') as 'surname' 
FROM `marriage`
LEFT JOIN `surname` ON `marriage`.`surname` = `surname`.`id`
, COALESCE(`other_spouses`.`spouse_forename`,`spouse_surname`, 'id') as 'marriage_2' 
FROM `marriage`
LEFT JOIN `other_spouses` ON `marriage`.`marriage_2` = `other_spouses`.`id`
WHERE `marriage`.`id` in ({$cs_ids})";
$res = sql($sql, $eo);
Well that gave an error, I did some reading but found nothing on combining statements in such a manner, any other suggestions.

Thank you,
Jay
What we envision, we make happen.

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

Re: Batch Action multi sources.

Post by jsetzer » 2019-08-13 17:38

Hi,

let's have an abstract example:
  • Tables `a`, `b`, `c` and `d`:
  • All those tables have a primary key named `id`.
  • Table `a` is the master table
  • The columns a.b_id, a.c_id and a.d_id are modeled as lookups pointing to the tables `b`, `c` and `d`.
chrome_2019-08-13_19-35-50.png
chrome_2019-08-13_19-35-50.png (9.64 KiB) Viewed 100 times

Table "a"
  • id
  • b_id
  • c_id
  • d_id

Table "b"
  • id
  • name
Table "c"
  • id
  • name
Table "d"
  • id
  • name
Now let's have a look at the SQL statement to get all records of `a` and additionally all `name`-values of `b`, `c` and `d`:

Code: Select all

SELECT
    `a`.*,
    `b`.`name` as 'b_name',
    `c`.`name` as 'c_name',
    `d`.`name` as 'd_name'
FROM `a`
    LEFT JOIN `b` ON `a`.`b_id` = `b`.`id`
    LEFT JOIN `c` ON `a`.`c_id` = `c`.`id`
    LEFT JOIN `d` ON `a`.`d_id` = `d`.`id`
WHERE `a`.`id` = 123
To avoid problems with null-values you can use the COALESCE()-function:

Code: Select all

SELECT
    `a`.*,
    COALESCE(`b`.`name`, 'b-default') as 'b_name',
    COALESCE(`c`.`name`, 'c-default') as 'c_name',
    COALESCE(`d`.`name`, 'd-default') as 'd_name'
FROM `a`
    LEFT JOIN `b` ON `a`.`b_id` = `b`.`id`
    LEFT JOIN `c` ON `a`.`c_id` = `c`.`id`
    LEFT JOIN `d` ON `a`.`d_id` = `d`.`id`
WHERE `a`.`id` = 123
I hope I did not make any quote- or comma-mistakes in the code above. I still recommend reading the SQL tutorials concerning JOINS from my previous post. There is so much you can do very effectively with pure SQL.

Best regards,
Jan

User avatar
Jay Webb
Veteran Member
Posts: 55
Joined: 2017-08-26 15:27
Contact:

Re: Batch Action multi sources.

Post by Jay Webb » 2019-08-14 14:49

Hi Jan,

Great explanation, thanks for your help with this, I managed to get it and found limitation to using LEFT JOIN multi times on same table, spent all day reading and
trying figure out how make this work;

Code: Select all

$sql = "SELECT 
  `marriage`.*,
COALESCE(`surname`.`surnames`, 'id') as 'surname1',
COALESCE(`other_spouses`.`spouse_forename`, 'id') as 'marriage_2',
COALESCE(`other_spouses`.`spouse_surname`, 'id') as 'marriage_2a',
COALESCE(`other_spouses`.`spouse_forename`, 'id') as 'marriage_3',
COALESCE(`other_spouses`.`spouse_surname`, 'id') as 'marriage_3a',
COALESCE(`other_spouses`.`spouse_forename`, 'id') as 'marriage_4',
COALESCE(`other_spouses`.`spouse_surname`, 'id') as 'marriage_4a',
COALESCE(`ff_numbers`.`ff_code`, 'id') as 'ff_number`'
FROM `marriage`
LEFT JOIN `surname` ON `marriage`.`surname` = `surname`.`id`
LEFT JOIN `other_spouses` ON `marriage`.`marriage_2` = `other_spouses`.`id`
LEFT JOIN `ff_numbers` ON `marriage`.`ff_number` = `ff_numbers`.`id`
WHERE `marriage`.`id` in ({$cs_ids})";
in the data-sheet there are 2nd 3rd and 4th possible marriages.
problem i found is although the;

Code: Select all

LEFT JOIN `other_spouses` ON `marriage`.`marriage_2` = `other_spouses`.`id`
would display the
spouse_forename and spouse_surname simple by renaming row name to 'marriage_2a'.

it can't be used a second or third time, as in;

Code: Select all

LEFT JOIN `other_spouses` ON `marriage`.`marriage_3` = `other_spouses`.`id`
it errors us not unique.

Hate not be able to figure this out, but I know when I'm stumped.
What we envision, we make happen.

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

Re: Batch Action multi sources.

Post by jsetzer » 2019-08-14 16:23

Yes, you can use join the same table multiple times but then you have to use alias names ("as"):

See code below:
I'm using the same table 'b' in three joins. Each time I'm kind of "renaming" table "b" to "b1", "b2", "b3" using 'as' (alias). Afterwards you can use the table by it's alias name as if it was something completely different - but isn't:

Code: Select all

SELECT
    `a`.*,
    `b1`.`name` as 'b1_name',
    `b2`.`name` as 'b2_name',
    `b3`.`name` as 'b3_name',

FROM `a`
    LEFT JOIN `b` as 'b1' ON `a`.`b_id` = `b1`.`id`
    LEFT JOIN `b` as 'b2' ON `a`.`c_id` = `b2`.`id`
    LEFT JOIN `b` as 'b3' ON `a`.`d_id` = `b3`.`id`
WHERE `a`.`id` = 123
As recommended by Olaf and me in one of your earlier posts:
It would be easier to use group_concat-SQL-function on a normalized model than to use multiple joins with aliases on your non-normalized model ;) That's my personal opinion after 25 years of designing relational databases and developing applications and BI-systems.

Best,
Jan

User avatar
Jay Webb
Veteran Member
Posts: 55
Joined: 2017-08-26 15:27
Contact:

Re: Batch Action multi sources.

Post by Jay Webb » 2019-08-14 19:07

Hi Jan;

I don't want to sound ungrateful for the help or condescending, but I don't know what normalized model is, I'm doing all this as a favor for my Clan, I'm not a student or a programmer, I'm not doing it for a living, i'm a 62 year old guy who got was a bunch of data from 15+ people is various formats, I spent 4 1/2 months compiling into files that was intuitive. Although your suggestion have led to a little progress I've also spent countless hours with none, trying to figure out your formulas, showing what I had done, what went wrong and what I was trying to accomplish, for me the simplest solution would be to post, try this code like this.
I bought this program because it seamed to be the easiest and there were forums to ask for help. I'm donating the time, the domain, the host site and the money spent, I don't have years to get this done, now if you would like to do a commission free rework, I could send all the data I have, but knowing that's not going to happen, I'm trying to do the best I can with what I got, with what little I know, little being the key word.

With much gratitude,
Jay
What we envision, we make happen.

onoehring
Veteran Member
Posts: 230
Joined: 2019-05-21 22:42
Contact:

Re: Batch Action multi sources.

Post by onoehring » 2019-08-17 12:07

Hi Jay,

I suggest you take a look at https://www.activedbsoft.com/overview-querytool.html which is a great tool to create SQL that shows the things you want.
I agree with Jan, that working on a normalized model is much easier and .... most of the time ... worth recreating the database from scratch (this is my opinion after several years of database development). Maybe take a look at https://www.youtube.com/results?search_ ... +normalize . Probably you can find a video which helps to understand normalization. And really: We do not want to bully someone, but a database system (MySQL) brings so many advantages for the user once a database/table is normalized, that several things one would need to code by hand can be done with a single (database) command.
PS: 3rd normal form is probably enough for you and that's what's worth going for.

Just trying to help
Olaf

Post Reply