Page 1 of 1

Newbie: How to get/combine data from 2 tables

Posted: Fri Nov 05, 2010 5:13 pm
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!

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

Posted: Fri Nov 05, 2010 10:02 pm
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";

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

Posted: Sat Nov 06, 2010 3:53 am
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 :)

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

Posted: Sat Nov 06, 2010 4:22 am
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) {

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

Posted: Sat Nov 06, 2010 8:33 pm
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.

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

Posted: Sun Nov 07, 2010 7:16 am
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')";

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

Posted: Mon Nov 08, 2010 11:49 pm
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.