Get From Foreigner (utility function)

Last updated October 10th, 2018 at 08:05 am

In your PHP/MySQL applications, have you ever had to get values from a table based on the value of a foreign key in another table?

Of course you have! Let me put it this way:

Let’s suppose…

Suppose you have a database of students and their families. Further suppose each student in the “students” table is associated with one and only one family in the “families” table and that each student resides with his or her family (unlike the real world). Your mission: Get the mailing address of any given student.

Table Structures

students Table Structure (abridged):

id (PK)
student_first_name
student_last_name
family_id (FK)

families Table Structure (abridged):

id (PK)
family_first_name
family_last_name
address1
address2
city
state
postal_code

Method 1:

I could use this SQL code, using a basic join (note that in PHP, “some_value” will appear as ‘$some_value’ and will be passed by the calling script) :

SELECT address1, address2, city, state, postal_code
FROM families, students
where students.id = 'some_value' 
and family_id = families.id

Note that because there are no ambiguous columns, I didn’t have to use table specifiers. But let’s add them now, to make better documented code:

With table specifiers added:

SELECT families.address1, families.address2, 
families.city, families.state, families.postal_code
FROM families, students
where students.id = 'some_value' 
and students.family_id = families.id

Using table aliases is a handy alternative. To whit:

SELECT t2.address1, t2.address2, 
t2.city, t2.state, t2.postal_code
FROM families as t2, students as t1
where t1.id = 'some_value' 
and t1.family_id = t2.id

Method 2: Subquery

This query will render the same result as each of those listed in method 1:

SELECT address1, address2, city, state, postal_code
FROM families
where id = (
select family_id from students 
where id = 'some_value')

The advantage of the subquery method (IMHO) is that it’s easier to use if we wanted to create a reusable function that could perform similar queries for many different sets of database tables with similar structures.

Reusable Code in Function get_from_foreigner()

When faced with the task of writing a function to do pretty much what the above SQL code does, I realized that this task (getting values from a “parent” table — “parent” in the generic sense) is something I do a lot. Instead of hard-coding the specific table and column values for the “students” and “families” table, I decided to make a function I could reuse any time.

WordPress developers will recognize some thefts here.

• Lines 14-24: This is the classic WordPress technique of doing an array_merge of default values and those passed as arguments.
• Since most of my database tables use “id” as the primary key, I made these the defaults in lines 18 and 20.
• The meat of the function is in lines 32 and 33. It’s pretty much exactly what I prototyped above in the Subquery method.
• Depending on whether the $get argument was a single column or an array, the conditional code in lines 39-43 returns what I need.

/**
 * Get value (or array of values) from a foreign table
 * Example: 
 *   Get last_name, first_name from families table using the family_id (FK)  of the student table.
 *     students: id (PK); family_id (FK)
 *     families: id (PK)
 *     students.family_id = families.id
 * @param array $args  See below
 * Quite kewl
 * 2012-03-21
 */
if ( !function_exists('get_from_foreigner') ) {
	function get_from_foreigner($args='') {
		$defaults = array(
			'get' => '', 		// Col or Cols to get (can be array)
			't1' => '', 		// Table 1: The one in which 'find' is the primary key value (e.g., students)
			't2' => '', 		// Table 2: Foreign table (e.g., families)
			't1pk' => 'id', 	// Table 1 Primary Key col name
			't1fk' => '', 		// Table 1 Foreign Key col name
			't2pk' => 'id', 	// Table 2 Primary Key col name
			'find' => '', 		// The value to find in Primary Key col of Table 1
		);
		$r = wp_parse_args($args, $defaults);
		extract($r);
		
		if ( is_array($get) ) {
			$get_cols = implode(', ', $get);
		} else {
			$get_cols = $get;
		}

		
		$sql = "SELECT $get_cols FROM $t2 
		where $t2pk = (SELECT $t1fk from $t1 where $t1pk = '$find')";

		$result = mysql_query($sql);
		if ( !mysql_num_rows($result) ) return false;

		$row = mysql_fetch_assoc($result);
		if ( is_array($get) ) {
			return $row;
		} else {
			return $row[$get];
		}	
	}
}

The Page Code:

Finally, in the HTML page where I want to display the mailing address, I can use code like the following (assuming, of course, that ‘$student_id’ has already been set):

<?php
	$addy_arr = get_from_foreigner(
		array(
		'get' => array('address1', 'address2', 'city', 'state', 'postal_code'), 
		't1' => 'students', 
		't2' => 'families', 
		't1fk' => 'family_id', 
		'find' => $student_id)
	);
?>
<p>Student's Address:</p>

<p>
<?php echo $addy_arr['address1'];?><br>
<?php echo $addy_arr['address2'];?><br>
<?php echo $addy_arr['city'];?>, <?php echo $addy_arr['state'];?> <?php echo $addy_arr['postal_code'];?>
</p>

Comments?

I welcome your comments!