Displaying output from one table based on the info of another

It’s been a long day, so I’m providing the following SQL, untested and off the top of my head, but I think you’re probably looking for something like this:

SELECT
	spouse1_info.first_name AS spouse1_first_name,
	spouse1_info.family_name AS spouse1_family_name,
	marriage_info.date_of_marriage,
	spouse2_info.first_name AS spouse2_first_name,
	spouse2_info.family_name AS spouse2_family_name
FROM table2 AS marriage_info
INNER JOIN table1 AS spouse1_info
ON marriage_info.persion_id = spouse1_info.id
INNER JOIN table1 AS spouse2_info
ON marriage_info.spouse_id = spouse2_info.id

However, if your marriage info table has a record for each spouse, then you’re going to see the relationship show up twice in your results, like
Bob, Jones, 1988-01-02, Sally, Jones
Sally, Jones, 1988-01-02, Bob, Jones

If that’s a problem, then you should consider just storing the marriage_id in table1 for each person and get rid of the person_id and spouse_id fields in table2. In fact, that makes more sense than the method above.

Also, make sure you’re cleaning the values in those variables before using them in your SQL queries. As your code stands right now, you’re quite vulnerable to SQL injection attacks.

Thanks

Will give it ago.

I thought about adding the marriage_id to the person table but thought multiple marriages may cause a problem.

Will let you know how it turns out

Thanks Again

Warwick

First, analyze your desired output:

I want a table of data where the data consists of
a.(data)
joining.(data)
b.(data)

WHERE a.data and b.data come from either side of the join.

i.e. I don’t care if person a and person b come from left side or right side of join

SELECT DISTINCT
    a.first_name AS spouse1_first_name,
    a.family_name AS spouse1_family_name,
    j.date_of_marriage,
    j.marriage_id,
    b.first_name AS spouse2_first_name,
    b.family_name AS spouse2_family_name
FROM table2 j
  INNER JOIN table1 a
    ON a.id = j.person_id OR a.id = j.spouse_id
  INNER JOIN table2 b
    ON b.id = j.person_id OR b.id = j.spouse_id

Hi,

I am having difficulties getting it to work. My interpretation is as follows

Am I correct in assuming that the code I use is

Where $tablename is the person table (a)and $tablename2 is the marriage info table (b and j)

`SELECT DISTINCT
$table_name.first_name AS spouse1_first_name,
$table_name.family_name AS spouse1_family_name, $table_name2.date_of_marriage,
$table_name.id,
$table_name.first_name AS spouse2_first_name,
$table_name.family_name AS spouse2_family_name
FROM $table_name
INNER JOIN $table_name ON $table_name.id=$table_name2.person_id OR $table_name.id=$table_name2.spouse_id
INNER JOIN $table_name2 ON $table_name.id=$table_name2.person_id OR $table_name.id=$table_name2.spouse_id `

Thanks in advance
Warwick

You’re pretty close.

First, I’d encourage you to use more descriptive variable names rather than generic ones like $table_name and $table_name2. It’s easy to get confused with non-descriptive names. Imagine what would happen if you had five or six tables to join.

I believe c_cav and I were both referring to the “person table” as “$table_name” and the “marriage table” as “table_name2”.

With c_cav’s SQL, the substitution should be as simple as:

SELECT DISTINCT
    a.first_name AS spouse1_first_name,
    a.family_name AS spouse1_family_name,
    j.date_of_marriage,
    j.marriage_id,
    b.first_name AS spouse2_first_name,
    b.family_name AS spouse2_family_name
FROM $table_name2 j
  INNER JOIN $table_name a
    ON a.id = j.person_id OR a.id = j.spouse_id
  INNER JOIN $table_name2 b
    ON b.id = j.person_id OR b.id = j.spouse_id

Notice how only three substitutions for $table_name and $table_name2 were required: one in the FROM clause and two in the JOIN clauses. This is possible because we’re using the table aliases ‘a’, ‘b’, and ‘j’, so you only need to define the alias once, then you can use the aliases everywhere else in the SQL. You can use aliases on tables or columns. The “AS” keyword is optional, but I prefer it for readability.

On that note, I think c_cav arbitrarily chose ‘a’, ‘b’, and ‘j’ for example purposes. It works, but again, the letters ‘a’, ‘b’, and ‘j’ are no more descriptive than “table_name” and “table_name2”. In your actual code, you’d probably want to use something more descriptive like ‘p’ for person and ‘m’ for marriage… or just ‘person’ and ‘marriage’. Whatever is best for readability.

When you want to get the value of spouse 1’s first name, you’ll use the column name “spouse1_first_name”, not “first_name” or “a.first_name”.

Hope that helps.

Thank You.

You guys are Jets. Worked like a charm.

Just wonder if I could push the friendship that one more step. It was mentioned about “cleaning the values” prior to output. What method would you recommend?

I run the input through sanitize_text_field and a check input function:

function check_input($data, $problem=”)
{
$data = strip_tags($data);
$data = trim($data);
$data = stripslashes($data);
$data = htmlspecialchars($data);

if ($problem && strlen($data) == 0)
{
die($problem);
}

but unsure what is the best method for output.

Thanks Heaps

Warwick

Glad we could help. With regards to output, in the examples you’ve provided so far you should be fine displaying the data exactly as it appears in the database (unless you need to reformat it for some reason).

Generally, you’d concern yourself with validating input before you save it to the database. In some cases a person might try to embed some malicious code (i.e., JavaScript) into a comment or post. This rogue input might pass your SQL injection filter but would be harmful if you output it to the browser of an unsuspecting visitor. So you’d need to come up with a filter or validation check that does more that just look for SQL injection attacks.

However it’s difficult (or impossible) to detect every type of exploit you might encounter. So, in some cases it doesn’t hurt to use some kind of output filtering (like htmlspecialchars or strip_tags) before displaying strings of user-submitted data. But things like dates, numbers, etc. should be fine as-is.