sql change for page map

  • Resolved jcc5018

    (@jcc5018)


    11 years, 5 months ago

    I am looking to edit a page map function for my theme and I thought I had an idea how to make it work but it is not working so I could use some assistance.

    The line I believe I need to change is

    $pageslist = $wpdb->get_results(“SELECT ID, post_title FROM $wpdb->posts WHERE post_status = ‘publish’ AND post_type = ‘page’ ORDER BY post_title “);

    I need it to also list post from my writing category (id 35)
    So I ran a query in phpmyadmin in an attempt to do this, but I am getting syntax errors and Im not really sure what to fix. My guess would be the nested Select statement is not correct. It’s been a while since Ive learned sql so I forget some of the ways to handle things.

    SELECT ID, post_title FROM lis_posts WHERE post_status = ‘publish’ AND post_type = ‘page’ OR { post_type = ‘post’ AND {SELECT parent FROM ’lis_term_taxonomy’ WHERE ’parent’ = ’35’ } } ORDER BY post_title

    I suppose when I get the proper syntax, ill have to set it up in the them format with the $wpdb-> parts instead of the LIS_ prefix?

Viewing 1 replies (of 1 total)
  • Thread Starter jcc5018

    (@jcc5018)

    IT took a while but I figured it out.

    I had some issue where a few of my pages were displaying multiple times due to different ID’s. I’m not exactly sure what caused this.

    And I’m not sure if I really have the JOIN’s connected correctly as I just copied them from one of the help pages and altered a bit to get what I want. I had to add DISTINCT to remove the duplicates. 

    $pageslist = $wpdb->get_results("SELECT DISTINCT ID, post_title
FROM $wpdb->posts
LEFT JOIN $wpdb->term_relationships ON ( $wpdb->posts.ID = $wpdb->term_relationships.object_id )
LEFT JOIN $wpdb->term_taxonomy ON ( $wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id )
LEFT JOIN $wpdb->terms ON ( $wpdb->term_taxonomy.term_id = $wpdb->terms.term_id )
WHERE $wpdb->posts.post_type = 'page'
AND $wpdb->posts.post_status = 'publish'
OR $wpdb->posts.post_type = 'post'
AND $wpdb->term_taxonomy.parent = '35'

ORDER BY post_title
 ");

    I’m going leave this unresolved for a bit to see if anyone has a suggestion to get the same result with a little more simplified code. I think the current way it is set up may end up displaying any unpublished post. Though this probably wont be a big issue since I usually post my stuff right away, I’d like to have it set right just in case.

Viewing 1 replies (of 1 total)
  • The topic ‘sql change for page map’ is closed to new replies.

Tags