How to get data from 2 tables (newbie)

gopanthers

I know just barely enough to have created this day-one basic code to access my single-table database and print out the data I want about my membership directory, sorted in a specific order.

<?

$db = mysql_connect("localhost", "USERNAME", "PASSWORD");
mysql_select_db("DATABASE",$db);

$query = "SELECT * FROM wp_users ORDER BY user_login";
$result = mysql_query ($query);

if (@mysql_num_rows($result)) 
{ 
print "<table border=\"1\" >\n"; 

print "<tr> 
	<td>User Name</td>
	<td>Email</td>
	<td>URL</td>
</tr>\n"; 

while($row = mysql_fetch_array($result)) { 

print "<tr>\n";  

    print "<td>".$row['user_login']."</td>\n"; 

print "<td>".$row['user_email']."</td>\n"; 

print "<td>".$row['user_url']."</td>\n"; 

print "</tr>\n";
}
print "</table>\n"; 

} else { echo "<p>Sorry, no records were found!</p>"; }

?>

This is just about as much as I know how to do so far, but it's worked for me for years because all my data was always in one table... just like a simple spreadsheet.

But now my situation has changed and our membership data is in two different tables and I haven't got the first clue as to how to modify my code to get the data out of both tables and combine them (properly) to output what I need. Can anybody help, please?

The first table that I was already accessing is "spreadsheet-like" in that every member has their own row. Table #1 is called "wp_users" and contains fields such as ID, user_login, user_login, user_email, and user_url.

But Table #2 is called wp_usermeta. (If this all sounds familiar, these are tables from a WordPress installation.) It only has 4 fields total but contains a heck of a lot more info. The 4 fields are:

umeta_id (a unique ID for this table)
user_id (this value refers to the value in the ID field from the 1st table, so this value tells you who the following data if for)
meta_key (this is the name of the data entered, like last_name, membership_number, or date_joined)
meta_value (all the entered data, like Smith, Main Street, or 2010

So without changing anything about the database at all, how do I modify my php code above so that I can do something like this?

Previously:
print out user_login, user_email, and user_url from ever row (members), ordered by user_login

Wanted:
print out user_login, user_email, user_url, last_name (from table2), membership_number (from table2), and date_joined (from table2) from every row (members from table 1 I guess?), ordered by last_name (from table2 instead of user_login from table 1 in my original example)

Hopefully that makes some sense. It's probably easier to do the code than for me to describe it to you. Can anybody please show me how to modify my code from above to display the extra data from table 2 too?

Thanks so so much!

ixalmida

There are a couple of ways to do this.

First, what is the table relationship? (If you need help with table relationships, read this article.)

Here's a typical query for a one-to-one relationship:

SELECT students.student_id, students.student_name, preferences.forum_alias
FROM students JOIN preferences ON students.student_id = preferences.student_id;

-- OR, the following equivalent --

SELECT student_id, student_name,
  (SELECT forum_alias FROM preferences WHERE student_id = students.student_id) AS forum_alias
FROM students

You can use this first query to make a join in a one-to-many relationship as well. HOWEVER, be aware that you are going to pull a duplicate copy of each record in the students table for each extra record in the preferences table. So if "Bob Smith" has multiple forum names (more than one row) in the preferences table, you're going to get a duplicate student ID and name for each forum name.

So the rows you retrieve might look like this:

student_id  |  student_name  |  forum_alias
------------+----------------+-------------
1001        |  Bob Smith     |  Mickey Mouse
1001        |  Bob Smith     |  Donald Duck

To process this without displaying Bob Smith multiple times, you'll need to detect changes to the student_id in your code:

$old_id = 0;
while($row = mysql_fetch_assoc($result))
  {
  if($row['student_id'] != $old_id)
    echo $row['student_id']."  --  ".$row['student_name']."\n";
  echo "  Alias: ".$row['forum_alias']."\n";
  $old_id = $row['student_id'];
  }

The above output would look something like this:

1001  --  Bob Smith
  Alias:  Mickey Mouse
  Alias:  Donald Duck

Depending on the size of the Table B, it may simply be better to skip joining tables and run a second query, like so:

while($row = mysql_fetch_assoc($result))
  {
  echo $row['student_id']."  --  ".$row['student_name']."\n";
  $result2 = mysql_query("SELECT forum_alias FROM preferences WHERE student_id = ".$row['student_id']);
  while($alias_row = mysql_fetch_assoc($result2))
     echo "  Alias:  ".$alias_row['forum_alias']."\n";
  }

Not sure if this answers your question...