Newbie: How to get/combine data from 2 tables

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
gopanthers
Forum Newbie
Posts: 9
Joined: Fri Nov 05, 2010 5:00 pm

Newbie: How to get/combine data from 2 tables

Post by 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.

Code: Select all

<?

$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 currently I am able to print out user_login, user_email, and user_url from ever row (members), ordered by user_login. Without changing anything about the database itself, how do I modify my php code above at the top so that I can do something like this?... print out user_login, user_email, user_url, last_name (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!
maas
Forum Newbie
Posts: 3
Joined: Fri Nov 05, 2010 9:59 pm

Re: Newbie: How to get/combine data from 2 tables

Post by maas »

Try this, if I understood the whole 'meta key, meta value' thing then this should be what you are looking for.

Code: Select all

$query = "SELECT user_login.wp_users, user_email.wp_users, user_url.wp_users, last_name.wp_users, meta_value.wp_usermeta FROM wp_users, wp_usermeta WHERE ID.wp_users = user_id.wp_usermeta AND meta_key.wp_usermeta ='date_joined' ORDER BY last_name.wp_users";
zaloobe
Forum Newbie
Posts: 2
Joined: Sat Nov 06, 2010 3:40 am

Re: Newbie: How to get/combine data from 2 tables

Post by zaloobe »

Sorry to post in this thread as well, it's not my intention to hijack it, but it handles the same wordpress database issue.

What I'm trying to do is the following:

1. insert data into wp_posts (post_content) (this works, see code below)
2. get the auto_increment ID from the row I just inserted
3. insert other data into wp_postmeta with the same ID into post_id

Code: Select all

if ($handle->processed) {
			
// Insert info in DB
$command="INSERT INTO wp_posts(post_content) VALUES('$newname.jpg')";
$result = mysql_query($command, $connection);

if($result) {

// Show processed thumb if writing to DB is ok
echo "<div class=\"photo\">\n";
echo "<a class=\"greybox\" href=\"http://site.com/wallpapers/$dirname/$newname.jpg\"><img class=\"thumbnail\" src=\"http://site.com/wallpapers/$dirname/$newname.jpg\" alt=\"Photo\" title=\"Thumbnail\" /></a>\n";
echo "<div class=\"imageInfo\">\n";
echo "<a class=\"actionEdit greybox\" href=\"http://site.com/admin/test.php?file=$newname.jpg\" title=\"Edit\">Edit</a>\n";
echo "<a class=\"actionDelete\" href=\"#\" title=\"Delete\">Delete</a>\n";
echo "</div>\n";
echo "</div>\n";

// If writing to DB doesn't work
} else {

echo "error writing to DB";

}
				
// If convert doesn't go well, throw an error	
} else {

echo "Resizing Error";

}
Thanks for any help or tips :)
zaloobe
Forum Newbie
Posts: 2
Joined: Sat Nov 06, 2010 3:40 am

Re: Newbie: How to get/combine data from 2 tables

Post by zaloobe »

Ok, i can answer my own question, so I'll post it here for people who look for the same:

Code: Select all

if ($handle->processed) {
                       
// Insert info in DB
$command="INSERT INTO wp_posts(post_content) VALUES('$newname.jpg')";
$result = mysql_query($command, $connection);
$lastItemID = mysql_insert_id();  //gets the latest auto_increment ID from wp_posts
$command2="INSERT INTO wp_postmeta(post_id, meta_key, meta_value) VALUES('$lastItemID', 'imagefile', '$newname.jpg')";
$result = mysql_query($command2, $connection);

if($result) {
gopanthers
Forum Newbie
Posts: 9
Joined: Fri Nov 05, 2010 5:00 pm

Re: Newbie: How to get/combine data from 2 tables

Post by gopanthers »

maas wrote:Try this, if I understood the whole 'meta key, meta value' thing then this should be what you are looking for.

Code: Select all

$query = "SELECT user_login.wp_users, user_email.wp_users, user_url.wp_users, last_name.wp_users, meta_value.wp_usermeta FROM wp_users, wp_usermeta WHERE ID.wp_users = user_id.wp_usermeta AND meta_key.wp_usermeta ='date_joined' ORDER BY last_name.wp_users";
Thanks maas. I tried it but now it says no records found. Here's the code I have now, using all fields and table names that really do exist, and simplified too (like I didn't even try ORDER BY this time).

Code: Select all

<?

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

$query = "SELECT user_login.wp_users, user_email.wp_users, user_url.wp_users, last_name.usermeta FROM wp_users, wp_usermeta WHERE (ID.wp_users = user_id.wp_usermeta)";

$result = mysql_query ($query);

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

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

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

print "<tr>\n";  
    print "<td>".$row['last_name.wp_usermeta']."</td>\n"; 
    print "<td>".$row['user_login.wp_users']."</td>\n"; 
    print "<td>".$row['user_email.wp_users']."</td>\n"; 
    print "<td>".$row['user_url.wp_users']."</td>\n"; 
    print "</tr>\n";
}
print "</table>\n"; 

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

?> 
And here's a visual representation of what kind of data I have in these two tables if that helps.

wp_users
+-----+-----------------+------------------------+-------------------------+
| ID | user_login | user_email | user_url |
+-----+-----------------+------------------------+-------------------------+
| 1 | user1A | user1A@email.com | http://user1.com |
+-----+-----------------+------------------------+-------------------------+
| 2 | user2B | user2B@email.com | |
+-----+-----------------+------------------------+-------------------------+
| 3 | user3C | user1A@email.com | http://google.com |
+-----+-----------------+------------------------+-------------------------+
| 4 | user4D | user1A@email.com | |
+-----+-----------------+------------------------+-------------------------+

wp_usermeta
+-------------+------------+--------------------------+-------------------------+
| umeta_id | user_id | meta_key | meta_value |
+-------------+------------+--------------------------+-------------------------+
| 1 | 1 | last_name | Smith |
+-------------+------------+--------------------------+-------------------------+
| 2 | 1 | first_name | John |
+-------------+------------+--------------------------+-------------------------+
| 3 | 1 | date_joined | 20100309 |
+-------------+------------+--------------------------+-------------------------+
| 4 | 1 | membership_number | 123 |
+-------------+------------+--------------------------+-------------------------+
| 5 | 2 | last_name | Robinson |
+-------------+------------+--------------------------+-------------------------+
| 6 | 2 | first_name | Will |
+-------------+------------+--------------------------+-------------------------+
| 7 | 2 | date_joined | |
+-------------+------------+--------------------------+-------------------------+
| 8 | 4 | last_name | Davis |
+-------------+------------+--------------------------+-------------------------+

Again, the code above shows me "no results found". Even if there were no data in table 2, I should still get a list of all users. All I want to do is tack on extra data about each user to the end of each user's row.
maas
Forum Newbie
Posts: 3
Joined: Fri Nov 05, 2010 9:59 pm

Re: Newbie: How to get/combine data from 2 tables

Post by maas »

sorry, in a hurry I posted the table name and the column name round the wrong way, it should be:

Code: Select all

$query = "SELECT wp_users.user_login, wp_users.user_email, wp_users.user_url, usermeta.last_name FROM wp_users, wp_usermeta WHERE (wp_users.ID = wp_usermeta.user_id)";
but also in your code you have specified "usermeta.last_name" the table is wp_usermeta and last_name is not a column, it is a value in metakey, so if you want the last name you need to do

Code: Select all

$query = "SELECT wp_users.user_login, wp_users.user_email, wp_users.user_url, wp_usermeta.meta_value FROM wp_users, wp_usermeta WHERE (wp_users.ID = wp_usermeta.user_id AND wp_usermeta.metakey = 'last_name')";
gopanthers
Forum Newbie
Posts: 9
Joined: Fri Nov 05, 2010 5:00 pm

Re: Newbie: How to get/combine data from 2 tables

Post by gopanthers »

Thanks again but it didn't work for me. I feel like I'm so close. Everything you wrote makes sense to me as far as what's happening except what the part after WHERE is actually doing. But perhaps this helps. Since I'm more comfortable with graphics, I drew out what is going on with everything, or at least what I want to happen.

Image

I'm not sure if this is a "join" or which direction it would go if it was, but I hope this image helps. Basically, every single row from the first table (wp_users) is a user, but most of the data for each user is stored seemingly scattered in the second table (wp_usermeta). I don't know how this works but it seems like we're printing out every row from table1 but then trying to attach all the random data stored in table2 to the correct lines from table1. ??

And like you pointed out before, you were right that I incorrectly referred to last_name as a field of table2 when last_name (like all the stored data) is actually a value within the meta_key field... and then the value for "last name" is really stored next to it in the next field. Seems like that's what makes this whole thing tricky since I was trying to treat it like a field when it's really not.

Anyway, does this graphic help make more sense? The data above is made up but all the table names and table fields are correct and how I'm trying to print out one long line of data for each user like the black text at the bottom of the graphic.
Post Reply