Solved

Wordpress Select Syntax

Posted on 2014-04-11
9
414 Views
Last Modified: 2014-04-12
I have the following wordpress table data.
The columns are named as follows:
Meta_id
user_id
meta_key
meta_value
15       2       first_name       Robert
16       2       last_name       Granlund
17       2       nickname       rgranlund
18       2       description       
19       2       rich_editing       true
20       2       comment_shortcuts       false
21       2       admin_color       fresh
22       2       use_ssl       0
23       2       show_admin_bar_front       true
24       2       wp_capabilities       a:1:{s:13:"administrator";b:1;}
25       2       wp_user_level       10

I want to write this but I'm not sure how:
$sql = "SELECT meta_value WHERE meta_key ='first_name', 
meta_value WHERE meta_key ='last_name', 
meta_value WHERE meta_key ='wp_user_level', 
FROM wp_usermeta
GROUP BY user_id
"

Open in new window


Right now there is only one user_id "2"  But if there was 100 user id I want it to return all 100, grouped by the user_id.  Any hints?
0
Comment
Question by:rgranlund
  • 4
  • 4
9 Comments
 
LVL 35

Expert Comment

by:Dan Craciun
ID: 39994951
Try this:
SELECT user_id, meta_key, meta_value FROM wp_usermeta
WHERE meta_key IN ('first_name', 'last_name', 'wp_user_level')

Open in new window

HTH,
Dan
0
 
LVL 7

Author Comment

by:rgranlund
ID: 39995218
@dan, it pulled the user_id but not the first_name last_name.

Any further ideas?

I tried this:
				$sql_b = 	"SELECT user_id, meta_key, meta_value
							FROM wp_usermeta
							WHERE meta_key IN('first_name' AS first_name AND 'last_name' AS last_name)
							GROUP BY user_id";
				
				$pdos_b = $pdo->prepare($sql_b, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));

Open in new window

0
 
LVL 35

Expert Comment

by:Dan Craciun
ID: 39995224
You can't do AS in the IN clause. It makes no sense.

And you don't need the GROUP BY.

The expected result of my SELECT is:
2       first_name            Robert
2       last_name             Granlund
2       wp_user_level    10
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Author Comment

by:rgranlund
ID: 39995237
Here is the rest of it and keep in mind that there will be multiple users :

				$sql_b = 	"SELECT user_id, meta_key, meta_value FROM wp_usermeta
WHERE meta_key IN ('first_name', 'last_name', 'wp_user_level')";
				
				$pdos_b = $pdo->prepare($sql_b, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
			
			try {
    			$pdos_b->execute();
	//  GET RESULTS
				$bg = '#eeeeee'; 

				while($row_b = $pdos_b->fetch()):
				$bg = ($bg=='#eeeeee' ? '#ffffff' : '#eeeeee');

					echo '<tr bgcolor="' . $bg . '">
						<td align="left"><a href="delete_user.php?id=' . $row_b->user_id . '"><img src="'.$path.'img/buttons/delete.png"/></a></td>
						<td align="left"><a href="edit_user.php?id=' . $row_b->user_id . '"><img src="'.$path.'img/buttons/edit-icon.png"/></a></td>
						<td align="left">' . $row_b->last_name . '</td>
						<td align="left">' . $row_b->first_name . '</td>
						<td align="left">' . $row_b->wp_user_level . '</td>
					</tr>';
			 	endwhile;	
			} 
			catch(PDOException $a) {
   				echo 'ERROR: ' . $a->getMessage();
			}

Open in new window

0
 
LVL 35

Accepted Solution

by:
Dan Craciun earned 500 total points
ID: 39995325
You will get 3 rows for every user with my original query. For what you want you need a pivot (to transform data from rows into columns).

Try this:
SELECT `user_id`, MAX(`first_name`) AS `first_name`, MAX(`last_name`) AS `last_name`, MAX(`wp_user_level`) AS `wp_user_level` FROM (
SELECT `user_id`, 
	CASE WHEN `meta_key` = 'first_name' THEN `meta_value` END AS `first_name`,
	CASE WHEN `meta_key` = 'last_name' THEN `meta_value` END AS `last_name`,
	CASE WHEN `meta_key` = 'wp_user_level' THEN `meta_value` END AS `wp_user_level`
FROM wp_usermeta) pivot
GROUP BY `user_id`

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39995454
no points pl

Assuming it's MySQL then Dan's query above looks great.

Just one tiny tip, he has used an alias "pivot" and while this is not (currently) a reserved word in MySQL it is in other SQL dialects - and might become one in MySQL one day.

So I'd replace the word "pivot" with something else, "sq" (for sub query) for example.
0
 
LVL 7

Author Comment

by:rgranlund
ID: 39996278
@dan & Paul, The following works.  The above did not however, your examples got me going in the correct direction, thank you!

$sql_b = "SELECT
    u1.ID,
    u1.user_email,
    u1.user_registered,
    m1.meta_value AS first_name,
    m2.meta_value AS last_name,
    m3.meta_value AS wp_user_level
FROM wp_users u1
JOIN wp_usermeta m1 ON (m1.user_id = u1.ID AND m1.meta_key = 'first_name')
JOIN wp_usermeta m2 ON (m2.user_id = u1.ID AND m2.meta_key = 'last_name')
JOIN wp_usermeta m3 ON (m3.user_id = u1.ID AND m3.meta_key = 'wp_user_level')";

	
		$pdos_b = $pdo->prepare($sql_b, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
			
		try {
    		$pdos_b->execute();
	//  GET RESULTS
			$bg = '#eeeeee'; 

			while($row_b = $pdos_b->fetch()):
				
				$bg = ($bg=='#eeeeee' ? '#ffffff' : '#eeeeee');
				echo '<tr bgcolor="' . $bg . '">
					<td align="left"><a href="delete_user.php?id=' . $row_b->user_id . '"><img src="'.$path.'img/buttons/delete.png"/></a></td>
					<td align="left"><a href="edit_user.php?id=' . $row_b->user_id . '"><img src="'.$path.'img/buttons/edit-icon.png"/></a></td>
					<td align="left">' . $row_b->last_name . '</td>
					<td align="left">' . $row_b->first_name . '</td>
					<td align="left">' . $row_b->wp_user_level . '</td>
				</tr>';
			 endwhile;	
		} 
		catch(PDOException $a) {
   			echo 'ERROR: ' . $a->getMessage();
		}

Open in new window

0
 
LVL 7

Author Closing Comment

by:rgranlund
ID: 39996279
It was not exactly what I needed but it pointed me in the correct direction.  Thanks you!
0
 
LVL 35

Expert Comment

by:Dan Craciun
ID: 39996436
Glad I could help!

I personally tested my query on a Wordpress site and it's working.

However, your approach using JOIN's is faster than a CASE.
Tested it on a 10 user db and the results are:
1. my query: Showing rows 0 - 9 (10 total, Query took 0.0083 sec)
2. your query: Showing rows 0 - 9 (10 total, Query took 0.0018 sec)
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
The purpose of this video is to demonstrate how to Import and export files in WordPress. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Click on Too…
The purpose of this video is to demonstrate how to set up basic WordPress SEO. This will be demonstrated using a Windows 8 PC. The plugin used will be WordPress SEO by Yoast. Go to your WordPress login page. This will look like the following: myw…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question