Displaying output from one table based on the info of another

Discussion in 'Programming' started by Warwick Lyons, Mar 29, 2013.

  1. #1
    This has been driving me crazy!!!
    I am creating a small plugin with two tables"
    table1
    id
    first_name
    family_name
    date_of_birth
    family_id
    post_id father_id
    mother_id
    sex

    and
    table2
    id
    person_id
    spouse_id
    date_of_marriage
    marriage_id

    My question is this
    How do i display a table of spouses?
    ie grab the first_name, family_name for spouse one and the first_name, familyname of spouse2 from table1 and the date_of_marriage from table2 based on the person_id and spouse_id of table2.
    I can get the table to display one spouse member but not the other:
    I would like the output table to have the following headings:
    Spouse 1 Marriage Date Spouse 2
    First Name Family Name Date of Marriage First Name Family Name
    The code I have tried is:
    'include ('tablename.php'); // This contains the names of the tables $result = $wpdb->get_results( "SELECT $table_name.first_name, $table_name.family_name, $table_name.sex, $table_name.post_id, $table_name2.date_of_marriage, $table_name2.person_id, $table_name2.spouse_id, $table_name2.marriage_id FROM $table_name JOIN $table_name2 ON $table_name.id=$table_name2.person_id ORDER BY '$table_name2.id ASC'" ); $spouse_id = $wpdb->get_var( "SELECT spouse_id FROM $table_name2 WHERE person_id = $id" ); $marriage_id = $wpdb->get_var( "SELECT marriage_id FROM $table_name2 WHERE person_id = id and spouse_id = $spouse_id" ); ?> <th class="menu_heading"> First Name</th> <th class="menu_heading"> Family Name</th> <th class="menu_heading small"> Edit Wife</th> <th class="menu_heading"> Marriage</th> <th class="menu_heading small"> Edit Date</th> <th class="menu_heading small"> Delete</th> <tr> <?php // Loop through the results to display the obtained information foreach ($result as $result){ ?> <tr class="menu_list"> <td ><?php echo $result->first_name;?></td> <td ><?php echo $result->family_name;?></td> <td ><?php echo $result->marriage_date;?></td> </tr> <?php } > </tr> </table> <?php } ?>'
    Any Assistance is appreciated
    Thanks
     
    Warwick Lyons, Mar 29, 2013 IP