WordPress.org

1. imjscn
   Member
   Posted 1 year ago #

   Hello, I'm dying for a tip, please help!
   I want to run a query on postmeta table and get 3 custom field value for each post. but my code got the post_id only.
   

   $query = <<<QUERY
   SELECT      project_id.post_id
   FROM        $wpdb->postmeta project_id
   LEFT JOIN  $wpdb->postmeta height
               on height.post_id = project_id.post_id
               and height.meta_key = 'height'
   LEFT JOIN  $wpdb->postmeta width
               on width.post_id = project_id.post_id
               and width.meta_key = 'width'
   WHERE       project_id.meta_key = 'project_id'
   ORDER BY    project_id.meta_value+(0) ASC
   QUERY;

   If I remove the LEFT JOIN, the code still get the post_id. So, obviously my left join is wrong. Please help!

2. s_ha_dum (was apljdi)
   Member
   Posted 1 year ago #

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

3. imjscn
   Member
   Posted 1 year ago #

   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!

4. GRAQ
   Member
   Posted 1 year ago #

   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

5. s_ha_dum (was apljdi)
   Member
   Posted 1 year ago #

   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.

6. imjscn
   Member
   Posted 1 year ago #

   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.

7. imjscn
   Member
   Posted 1 year ago #

   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!

8. imjscn
   Member
   Posted 12 months ago #

   @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;

9. s_ha_dum (was apljdi)
   Member
   Posted 12 months ago #

   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'.

10. imjscn
    Member
    Posted 12 months ago #

    I see.
    Thanks for helping! I will try to see if I can apply this code on postmeta table or not.

11. imjscn
    Member
    Posted 11 months ago #

    @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!

12. s_ha_dum (was apljdi)
    Member
    Posted 11 months ago #

    $data[] = ...

13. imjscn
    Member
    Posted 11 months ago #

    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.

14. imjscn
    Member
    Posted 11 months ago #

    @apljdi,

    Complete! $data[] = (object)Array(....);

    Thanks so much for your helping!

Topic Closed

This topic has been closed to new replies.