Robert Granlund
asked on
Wordpress Select Syntax
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:
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?
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";
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
"
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?
ASKER
@dan, it pulled the user_id but not the first_name last_name.
Any further ideas?
I tried this:
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));
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
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
ASKER
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();
}
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
@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();
}
ASKER
It was not exactly what I needed but it pointed me in the correct direction. Thanks you!
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)
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)
Open in new window
HTH,Dan