Page 1 of 1

MySql Right Join to hook.

Posted: 2018-05-17 22:13
by Jay Webb
Trying to to input data from tbl1 to empty tbl2. My php comes from successful query in phpmyadmin, now would like to add as hook so on page load, data is retrieved from tbl1 and sent to tbl2.

I tried this code, it run with blank screen, not sure how to proceed

In; ../hooks/FF_Reference_Check.php

Code: Select all

	function FF_Reference_Check_init(&$options, $memberInfo, &$args){

		$sql = "SELECT ID, FamilyFile, LastName, FirstName, Birth, BirthDate, BirthPlace, Father, Mother, SpouseName\n"

		    . "FROM FF_Reference_Check\n"

		    . "RIGHT JOIN MacLellanGenealogy USING (ID, FamilyFile, LastName, FirstName, Birth, BirthDate, BirthPlace, Father, Mother)\n"

		    . "WHERE FamilyFile IS NOT NULL\n"

		    . "\n"

		    . "ORDER BY case when `FamilyFile` is null then 1 else 0 end, `FamilyFile`";
			}
		return $query;
		}

Re: MySql Right Join to hook.

Posted: 2018-05-18 05:54
by pbottcher
Hi,

In your code you create the SQL query and put it into a string. This would return all records where your WHERE clause is matched.

Now you can add

Code: Select all

$res = sqlvalue("INSERT INTO TBL2 (FamilyFile, LastName, FirstName, Birth, BirthDate, BirthPlace, Father, Mother, SpouseName) ".$query);

Re: MySql Right Join to hook.

Posted: 2018-05-20 23:02
by Jay Webb
Hello: Pascal
I'v tried a dozen different ways and I can't seem to get it working.
Did a bunch of reading and came up with this,

Code: Select all

	function FF_Reference_Check_init(&$options, $memberInfo, &$args){

		$options=$query

			$query = mysql_query("SELECT ID, FamilyFile, LastName, FirstName, Birth, BirthDate, BirthPlace, Father, Mother FROM `MacLellanGenealogy`

					WHERE ID='ID', FamilyFile='FamilyFile', LastName='LastName', FirstName='FirstName', Birth='Birth', BirthDate='BirthDate', BirthPlace='BirthPlace', Father='Father', Mother='Mother' ".$query),

					$res = sqlvalue("INSERT INTO 'FF_Reference_Check' ('ID', 'FamilyFile', 'LastName', 'FirstName', 'Birth', 'BirthDate', 'BirthPlace', 'Father', 'Mother') ".$query);

						mysql_query($query);

						return TRUE;

				}
But it didn't work ether, I'm missing something.

Re: MySql Right Join to hook.

Posted: 2018-05-21 08:09
by pbottcher
Hi Jay, just to clarify
are you trying to put in TBL2 all data from TBL1 with the WHERE clause in the SQL as in your 1'st post, or some other matching data (like in your second post).

For the 1'st you can use this after your string assignment to $sql.

Code: Select all

$res = sqlvalue("INSERT INTO TBL2 (ID, FamilyFile, LastName, FirstName, Birth, BirthDate, BirthPlace, Father, Mother, SpouseName) ".$query);

sorry, I missed the "ID" in my first reply.

If you try to get other specific data, please explain what you try to acheive. (Maybe you can add a screenshot).