You are only asking for project_id.post_id so you are only going to get that post_id. You need to ask for all the fields you want returned.
SELECT project_id.post_id, height.height, width.width....
Thread Starter
imjscn
(@imjscn)
ok,just now I changed the first 2 lines to:
SELECT project_id.post_id, height.height, width.width
FROM $wpdb->postmeta project_id,$wpdb->postmeta height,$wpdb->postmeta width
var_dump returns array(0) { }
(if don’t add height and width table in FROM line, the same 0 return )
Please give further instruction!
Check the error response, you don’t need modify line two as you have done. You can already SELECT height.* and width.* from your LEFT JOINs
Yes. GRAC is correct. You don’t need to list your tables like that in the ‘FROM’ clause. The joins are enough to bring those tables into the query, and you could end up with naming conflicts if you try to do both.
Also, you should make sure that you are using valid table and column names. I guessed at those values.
It might help to echo your MYSQL errors, if you aren’t already.
Thread Starter
imjscn
(@imjscn)
I put the 2 lines in template file, right below the line I echo the query result, it prints nothing.
<?php $wpdb->show_errors(); ?>
<?php $wpdb->print_error(); ?>
I changed the 2nd line of the query to FROM $wpdb->postmeta project_id, still no error messages and returns project_id, This line might be the problem, coz it’s the project_id, not contains height and width info.
Here’s how they look like in postmeta table:
-------------------------------------
post_id | meta_key | meta_value |
-------------------------------------
1 | project_id | 1 |
-------------------------------------
1 | height | 50 |
-------------------------------------
1 | width | 100 |
-------------------------------------
2 | project_id | 2 |
-------------------------------------
2 | height | 50 |
-------------------------------------
2 | width | 100 |
--------------------------------------
Edit:
Now I changed the 1st line to
SELECT project_id.post_id, project_id.meta_value,project_id.meta_key
got this:
array(3) {
[0]=>
object(stdClass)#101 (3) {
["post_id"]=>
string(3) "1"
["meta_value"]=>
string(3) "1"
["meta_key"]=>
string(7) "project_id"
}
[1]=>{...}
[2]=>{...}
}
Seems, the “FROM $wpdb->postmeta project_id ” is wrong.
But, “FROM $wpdb->postmeta” deosn’t give any result.
Thread Starter
imjscn
(@imjscn)
New finding- if I change the first 2 lines to:
SELECT project_id.post_id, height.meta_value,width.meta_value
FROM $wpdb->postmeta project_id
The output will show:
array(3) {
[0]=>
object(stdClass)#101 (3) {
["post_id"]=>
string(3) "1"
["meta_value"]=>
string(3) "50"
}
[1]=>{...}
[2]=>{...}
}
After serveral test, I see it can only output 3 items in one object (post_id, meta_key, meta_value)
It takes whatever value (height,width…)I specific in SELECT line, but each object can only have one post_id, one meta_key and one meta_value.
So, now the problem is a bit clear– my code makes the query result structured the same as postmeta table, to get one id with all related height/width…, I need another structure.
Please help!
Thread Starter
imjscn
(@imjscn)
@apljdi,
Could you help check this code? It gets the correct result but from multi tables, I don’t understand what is pd, pa, and u. / p. , hope there’s a way I can borrow this code to apply on postmeta table.
$query = <<<QUERY
SELECT DISTINCT(u.ID),
u.user_email,
u.user_nicename,
u.display_name,
pd.value
FROM $wpdb->users u
LEFT
JOIN {$bp->profile->table_name_data} pd
ON u.ID = pd.user_id
LEFT
JOIN (
SELECT DISTINCT( p.post_author ) ID
FROM $wpdb->posts p
WHERE
p.post_type = 'ep_reg'
AND p.post_parent = $postid
$for_admin
) pa
ON u.ID = pa.ID
WHERE
u.user_status = 0
AND pa.ID IS NULL
AND pd.field_id = 1
ORDER BY pd.value ASC
QUERY;
pd, pa, and u. / p.
Those are table ‘aliases’. Look at this part of the code:
FROM $wpdb->users u
That is the same as, and can also be written as:
FROM $wpdb->users AS u
Now, within the query you can refer to the wp_users as ‘u’.
Thread Starter
imjscn
(@imjscn)
I see.
Thanks for helping! I will try to see if I can apply this code on postmeta table or not.
Thread Starter
imjscn
(@imjscn)
@apljdi
Seems there’s no way to query colums as keys in postmeta table. I decide to move on in other ways.
Somedays ago, I learned get_post_meta from you. Now I do it this way:
function mp_all_ids( ){
global $wpdb, $post;
$query = <<<QUERY
SELECT project_id.post_id
FROM $wpdb->postmeta project_id
WHERE project_id.meta_key = 'project_id'
ORDER BY project_id.meta_value+(0) ASC
QUERY;
$postids = $wpdb->get_col( $query );
foreach ($postids as $id) {
$meta=get_post_custom($id);
$data = Array(
'ID' => $meta['project_id'][0],
'height' => $meta['height'][0],
'width' => $meta['width'][0]
);
}
return apply_filters( 'mp_all_ids', $data );
}
From var_dump($data), this function get only one result(because of Foreach) like this:
array(3) {
["ID"]=>
string(3) "463"
["height"]=>
string(16) "100"
["width"]=>
string(32) "50"
}
How can I get all projects with data like this:
array(2) {
[0]=>
object(stdClass)#1 (3) {
["ID"]=>
string(1) "2"
["height"]=>
string(3) "100"
["width"]=>
string(2) "50"
}
[1]=>
object(stdClass)#2 (3) {
["ID"]=>
string(1) "1"
["height"]=>
string(3) "200"
["width"]=>
string(3) "100"
}
}
Please help!
Thread Starter
imjscn
(@imjscn)
Great! Got all projects by $data[] = …
Still need one more tip to complete-
Now, each project is an array, how to get it as object(stdClass){…} ?
The array comes from this:
$data[] = Array(...);
If I change to $data[] = object(…), then , the “=>” is wrong.
Thread Starter
imjscn
(@imjscn)
@apljdi,
Complete! $data[] = (object)Array(….);
Thanks so much for your helping!
