Warning: Please do not give out any FTP or ssh credentials to anyone, unless you trust them completely. Giving out login details is dangerous.

If the asker does not get an answer then they have 10 days to request a refund.

       	$sql="SELECT SQL_CALC_FOUND_ROWS  wp_1_posts.*, wp_8_posts.* 

       	FROM wp_1_posts, wp_8_posts, wp_8_postmeta  

       	WHERE 1=1  AND wp_1_posts.post_type = 'post' AND wp_1_posts.post_status = 'publish' 

       	AND wp_8_posts.post_type = 'post' AND wp_8_posts.post_status = 'publish' 

       	AND wp_8_postmeta.meta_key = '_show_on_main' AND wp_8_postmeta.meta_value = 1

       	ORDER BY wp_1_posts.post_date, wp_8_posts.post_date DESC LIMIT 0, 25";

Eric Kittell | 01/27/11 at 2:58pm Edit

Previous versions of this question: 01/27/11 at 3:31pm | 01/27/11 at 3:31pm

(2) Possible Answers Submitted...

See a chronological view of answers?

Warning: Please do not give out any FTP or ssh credentials to anyone, unless you trust them completely. Giving out login details is dangerous.

• 
  Last edited:
  01/27/11
  3:11pm
  Ashfame says:

  You can carry out normal queries in a multisite setup across sites like this:
  
  

  <?php
  
  global $switched;
  
  switch_to_blog(7);
  
  get_posts($args); // it will act as if it was run in standalone wordpress setup whose blog ID is 7
  
  restore_current_blog(); // and this returns you to the current blog you are on
  
  ?>
  
  

  
  
  So this way you can just switch to the blog, query and then switch to yet another, query and display them as you want.

  Previous versions of this answer: 01/27/11 at 3:11pm

  • 01/27/11 3:18pm
    
    Eric Kittell says:

    yeah I know of that BUT then I'd have to do 2 different queries and sort through the results with PHP to get them ordered by date, this site is slow enough as it is...

  • 01/27/11 3:19pm
    
    Ashfame says:

    Use get_posts to retrieve all the posts. Code link - http://codex.wordpress.org/Function_Reference/get_posts
    
    

    <?php $args = array(
    
        'numberposts'     => -1, // this will return all posts
    
        'meta_key'        => '_show_on_main',
    
        'meta_value'      => 1 ); ?> 

    
    
    So the code should be like:
    
    <?php
    global $switched;
    
    switch_to_blog(1);
    $args = array(
    'numberposts' => -1, // this will return all posts
    'meta_key' => '_show_on_main',
    'meta_value' => 1 );
    get_posts($args);
    
    switch_to_blog(8);
    $args = array(
    'numberposts' => -1, // this will return all posts
    'meta_key' => '_show_on_main',
    'meta_value' => 1 );
    get_posts($args);
    
    restore_current_blog(); // and this returns you to the current blog you are on
    ?>

  • 01/27/11 3:21pm
    
    Ashfame says:

    That's what WordPress APIs offer, and sorting is not really gonna slow you down.

  • 01/27/11 3:26pm
    
    Eric Kittell says:

    I'm asking for a custom sql query, this is not a question about the Worpdress API

  • 01/27/11 3:27pm
    
    Ashfame says:

    If there's a way to do this with query vars instead of custom sql that would be great, but I doubt it.

    
    I believe you were willing to accept WP APIs

  • 01/27/11 3:34pm
    
    Eric Kittell says:

    sorry I meant if there was a GOOD way to do it with the API, I'm sure there is not, I don't think dealing with an array of thousands of posts is a good way to do this...

• 
  Last edited:
  01/27/11
  7:35pm
  John Cotton says:

  You could vary where you do the post_type/status WHERE clause, but this should give
  you it:
  
  

  
  
  
  
  
  
         	SELECT SQL_CALC_FOUND_ROWS * FROM (
  
  
  
             SELECT * FROM wp_1_posts WHERE post_type = 'post' AND post_status = 'publish'
  
  
  
             UNION ALL
  
  
  
             SELECT p.* FROM wp_8_posts p INNER JOIN wp_8_postmeta pm ON pm.post_id = p.id WHERE post_type = 'post' AND post_status = 'publish' AND pm.meta_key = '_show_on_main' AND pm.meta_value = 1
  
      ) T1
  
         	ORDER BY post_date DESC LIMIT 0, 25;
  
  
  
  

  • 01/27/11 5:14pm
    
    Eric Kittell says:

    hey that looks good, but it's not returning any results, just to test it I setup simpler queries and both of these return the expected results:
    
    

    SELECT SQL_CALC_FOUND_ROWS * FROM wp_1_posts WHERE post_type = 'post' AND post_status = 'publish' ORDER BY post_date DESC LIMIT 0, 25;

    
    
    

    SELECT SQL_CALC_FOUND_ROWS * FROM wp_8_posts p INNER JOIN wp_8_postmeta pm ON pm.post_id = p.id WHERE post_type = 'post' AND post_status = 'publish' AND pm.meta_key = '_show_on_main' AND pm.meta_value = 1
    
            ORDER BY post_date DESC LIMIT 0, 25;

  • 01/27/11 6:10pm
    
    John Cotton says:

    Hi Eric
    
    It think what's happening is that there's an error (hence no results) since you can't UNION those two queries - they don't return the same number of columns.
    
    You need to have it exactly as I entered it:
    
    SELECT p.*
    
    from the second one otherwise you're getting all the postmeta columns and that throws out the union.
    
    If you tell me what you want to get back (which columns) I can rewrite it so that it works.
    
    JC
    

  • 01/27/11 6:11pm
    
    John Cotton says:

    PS Assuming that you're using $wpdb->get_results with this, you could
    
    

    echo $wpdb->last_error;

    
    
    just after the query to see what's happening.
    
    

  • 01/27/11 7:34pm
    
    Eric Kittell says:

    ok there was an error because somebody added an extra column to the wp_1_posts table!
    
    it works now, thanks for helping me debug it!

This question has expired.

Current status of this question: Completed

Warning: Please do not give out any FTP or ssh credentials to anyone, unless you trust them completely. Giving out login details is dangerous.

If the asker does not get an answer then they have 10 days to request a refund.