• Skip to main content
  • Skip to primary sidebar
  • Skip to footer

nSiteful Web Builders

Building a Better Web - One Site at a Time.

  • Home
  • About
    • Testimonials
    • Resources
  • Web Sites
  • Online Marketing
  • WordPress Support
    • Customized WordPress Training
    • 60-for-60 Sessions
  • Web Applications
  • Blog
    • Archive Listing Minimalistic
    • Blog Articles Grouped by Category
    • Case Studies
    • General
    • Portfolio
    • Reviews
    • Snippets
    • Techniques
  • Contact Jeff
    • Purchase Retainer Consulting Hours
    • About Retainer Consulting Hours

By Jeff Cohan, March 22, 2012

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!

Related Posts

  1. Why, When, and How to use sprintf and printf
  2. Diagnostic PHP: Get All Included Files
  3. Diagnostic PHP: Get All User Constants
  4. Diagnostic PHP: Get All User Functions
  5. Sort Multidimensional Arrays with PHP array_multisort
  • Choose the best match.

Written by Jeff Cohan · Categorized: Snippets · Tagged: MySQL, php

  • Choose the best match.

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

mailchimp signup

Subscribe to get notified when new articles are published. Unsubscribe any time. No spam. I promise. Check out my newsletter archives.

social

Twitter Facebook LinkedIn

Recent Articles

  • Use Case for Custom Post Type: “In The News” March 10, 2023
  • Create a Custom Shortcode to Display a MemberPress Membership Price ANYWHERE on Your Website February 5, 2023
  • Avoid Direct Styling; Use CSS Instead September 21, 2022
  • Blog Tags: What They Are (and What They’re Not) August 5, 2022
  • How to Create a Simple Custom Events Plugin May 24, 2022

Filter By Category/Tag

Categories

  • Case Studies (7)
  • General (61)
  • Portfolio (5)
  • Reviews (12)
  • Snippets (16)
  • Techniques (38)

Popular Tags

Advanced Custom Fields Blogging Child Themes Content Marketing CSS CSS Grid Customer Service Custom Fields Custom Post Types Diagnostics Facebook FooGallery Genesis Gutenberg HTML Images iPhone Libra Live Chat Marketing Media MemberPress MemberPress Courses mu-plugins MySQL Photo Gallery php Pinterest Plugins Post Formats Pricing Project Management Security SEO Seth Godin Shortcodes Social Networking Surveys Taxonomies Trello Twitter Video Web design Web forms WordPress

siteground wp hosting

Web Hosting

wp101

EasyWordPresstutorialvideosforbeginners.
MemberPress CTA

Footer

Background

Web Sites | WordPress Support | Web Applications.

Formally trained in liberal arts and education (I have a B.A. in Government from Harvard and studied Secondary Education at Rutgers Graduate School), I have honed my skills in the communication arts and sciences as a teacher, trainer, instructional designer, writer, photographer, calligrapher, helpdesk manager, database programmer, and multimedia developer.

(I've also been a group counselor, waiter, bartender, bicycle messenger boy, computer salesman, carpenter's helper, financial analyst, and school board president.)

Tech

Systems since 1983.
Web sites since 1994.
PHP since 2001.
WordPress since 2007.

Contact

770-772-5134
Email Jeff
Send Money
All Ways

Copyright 2023, nSiteful Web Builders, Inc.

 

Subscribe

Pardon the interruption. I know popups can be annoying. But I’d love to have you as a subscriber.

Sign up to be notified when new articles are published. Unsubscribe any time.

* indicates required

Powered by MailChimp

×