Displaying Data from 2 tables

Cappymoo

I wasn't sure if I should be posting in the newbie forum or here, so I'll try here in hopes that someone can help me out.

I have 2 tables. 3 of the columns have the same rows (id, lastname1, firstname1) all the rest of the columns are different.

I want to display both tables together in one html table.

I can display the results from one table by doing this

<?php

    // connect to the database
    include('connect-db.php');

    // get results from database

$result = mysql_query("SELECT * FROM users ")
or die(mysql_error());

    // display data in table

    echo "<table border='1' cellpadding='10'>";
	echo "<tr><th colspan=6>Membership Database</th></tr>";
    echo "<tr> <th>ID</th> <th>Last Name</th> <th>First Name</th> <th>New Member</th> <th>Returning Member</th><th></th> <th></th></tr>";

    // loop through results of database query, displaying them in the table
    while($row = mysql_fetch_array( $result )) {

            // echo out the contents of each row into a table
            echo "<tr>";
            echo '<td>' . $row['id'] . '</td>';
            echo '<td>' . $row['lastname1'] . '</td>';
			echo '<td>' . $row['firstname1'] . '</td>';
            echo '<td>' . $row['NewMember'] . '</td>';
			echo '<td>' . $row['ReturningMember'] . '</td>';
            echo '<td><a href="edit.php?id=' . $row['id'] . '">Edit/View</a></td>';
            echo '<td><a href="delete.php?id=' . $row['id'] . '">Delete</a></td>';
            echo "</tr>"; 
    } 

    // close table>
    echo "</table>";

But I don't know how to include the 2nd table into the fray of things. I've tried
$result = mysql_query("SELECT * FROM users, youth ")

But that didn't work. It just displays the data from the youth's table if I do that, and it displays it 9 times even though I only have 4 records in the youth table.

Oh and of course the edit and delete links would need to change according to which table it's pulling it from.

Can anyone shed some light?

Bonesnap

The two tables have similar column names, but do they have related data? What I mean is, does one table have a foreign key in the other, or some other kind of connection?

Cappymoo

No, they have no related data, sometimes the lastname and firstname may be the same, but just sometimes.

_uk_stuff

The easiest way would be to union two SQL select statements. So it would look something like ...

Select col1, col2, col3 from table1 union select col1,col2,col3 from table2

Please note that you need to have the number of columns in both statements.

bradgrafelman

[uk]stuff;10989681 wrote:
Please note that you need to have the number of columns in both statements.

...which might not necessarily correspond to SELECT'ing the same number of columns from both tables.

Cappymoo

That worked perfectly, thank you so much. However, is there a way to show a different link to edit/view and delete depending on which record they are selecting? I have different scripts for each table to edit and to delete the records. Or should I try and combine those scripts into one?

Thanks again for helping!!

bradgrafelman

You could add a string to each SELECT statement so that you know which table the row came from, e.g.:

SELECT col1, col2, 'T1' AS tbl_name FROM ...

Cappymoo

Ok, I added the string to the select statement, but I'm still unsure on what to do for the links. Here is my script.

<?php

    // connect to the database
    include('connect-db.php');

    // get results from database
    $result = mysql_query("SELECT id, membertype, lastname1, firstname1, NewMember, ReturningMember, 'T1' AS users FROM users UNION select id, membertype, lastname1, firstname1, ttamembership_iscurrent, date1, 'Y1' AS youth from youth ORDER BY lastname1") 
            or die(mysql_error());  



    // display data in table

    echo "<table border='1' cellpadding='10'>";
echo "<tr><th colspan=8>Membership Database</th></tr>";
    echo "<tr> <th>ID</th> <th>Member Type</th><th>Last Name</th> <th>First Name</th> <th>New Member/Current Member</th> <th>Date Submitted</th><th></th> <th></th></tr>";

    // loop through results of database query, displaying them in the table
    while($row = mysql_fetch_array( $result )) {

            // echo out the contents of each row into a table
            echo "<tr>";
            echo '<td>' . $row['id'] . '</td>';
	echo '<td>' . $row['membertype'] . '</td>';
            echo '<td>' . $row['lastname1'] . '</td>';
	echo '<td>' . $row['firstname1'] . '</td>';
            echo '<td>' . $row['NewMember'] . '</td>';
	echo '<td>' . $row['ReturningMember'] . '</td>';
            echo '<td><a href="edit.php?id=' . $row['id'] . '">Edit/View</a></td>';
            echo '<td><a href="delete.php?id=' . $row['id'] . '">Delete</a></td>';
            echo "</tr>"; 
    } 

    // close table>
    echo "</table>";
?>

If it's the youth table, I need the links to be edityouth.php and deleteyouth.php
If its the users table, I need the links to be edit.php and delete.php

bradgrafelman

For one, the column names are going to be taken from the first SELECT statement, so the 'AS youth' alias in the second SELECT is superfluous and simply ignored.

As for the links, simply add an if() statement (or simply use the ternary operator for brevity) to check if the current row came from 'users' or 'youth' and output the correct link accordingly.

Cappymoo

Ok, you have stumped me again on the if else statement. Here is my code.

<?php
        // connect to the database
        include('connect-db.php');

    // get results from database
    $result = mysql_query("SELECT id, membertype, lastname1, firstname1, NewMember, ReturningMember, 'T1' AS users FROM users UNION select id, membertype, lastname1, firstname1, ttamembership_iscurrent, date1, 'Y1' AS youth from youth ORDER BY lastname1") 
            or die(mysql_error());  



    // display data in table

    echo "<table border='1' cellpadding='10'>";
echo "<tr><th colspan=8>Membership Database</th></tr>";
    echo "<tr> <th>ID</th> <th>Member Type</th><th>Last Name</th> <th>First Name</th> <th>New Member/Current Member</th> <th>Date Submitted</th><th></th> <th></th></tr>";

    // loop through results of database query, displaying them in the table
    while($row = mysql_fetch_array( $result )) {

            // echo out the contents of each row into a table
            echo "<tr>";
            echo '<td>' . $row['id'] . '</td>';
	echo '<td>' . $row['membertype'] . '</td>';
            echo '<td>' . $row['lastname1'] . '</td>';
	echo '<td>' . $row['firstname1'] . '</td>';
            echo '<td>' . $row['NewMember'] . '</td>';
	echo '<td>' . $row['ReturningMember'] . '</td>';

if(!$row['membertype'] == Regular){
echo '<td><a href="edit.php?id=' . $row['id'] . '">Edit/View</a></td>';
                echo '<td><a href="delete.php?id=' . $row['id'] . '">Delete</a></td>';
} else {
echo '<td><a href="editYouth.php?id=' . $row['id'] . '">Edit/View</a></td>';
                echo '<td><a href="deleteYouth.php?id=' . $row['id'] . '">Delete</a></td>';
}


    } 

    // close table>
    echo "</table>";
?>

with this if else statement, it only still shows all of the links as editYouth.php and deleteYouth.php even though I know half of them should be the other. Is my syntax wrong or something?

Cappymoo

Ahhh nevermind, I figured it out

$membertype = $row['membertype'];

if($membertype == "Regular"){
echo '<td><a href="edit.php?id=' . $row['id'] . '">Edit/View</a></td>';
echo '<td><a href="delete.php?id=' . $row['id'] . '">Delete</a></td>';
} else {
echo '<td><a href="editYouth.php?id=' . $row['id'] . '">Edit/View</a></td>';
echo '<td><a href="deleteYouth.php?id=' . $row['id'] . '">Delete</a></td>';
}