CODEWITHSUNDEEP
phpmysql
Meo
Meo

Reputation: 241

Wrong result with php

I am running a script to get some posts from a database. Here is the script:

private function getItems()
    {
        $this->dbConnect($detailsTable);
        mysql_select_db(DB_NAME);
        mysql_query('SET NAMES utf8');
        mysql_query('SET CHARACTER SET utf8');

        $result = mysql_query('SELECT * 
        FROM wp_posts, wp_term_relationships,wp_term_taxonomy 
        WHERE wp_posts.post_status = "publish"  
        AND wp_term_relationships.object_id = id
        AND wp_term_taxonomy.taxonomy= "category"
        AND !(wp_term_taxonomy.term_taxonomy_id = 11)
        AND wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
        ORDER BY wp_posts.post_date DESC LIMIT 25', LINK);
        mysql_close(LINK);

        $items = '';
        while($row = @mysql_fetch_array($result))
        {
            $title = UTFayar($row['post_title']);
            $content = UTFayar($row['post_content']);

            $items .= '<item id="'.$row["ID"].'">
                <title><![CDATA['.$title.']]></title>
                <description><![CDATA['. $content .']]></description>
                <pubDate>'.date('D, j M Y H:i:s T', strtotime($row['post_date'])).'</pubDate>
                <category>'.$row['post_category'].'</category>
            </item>';
        }
        $items .= '</channel>
                </rss>';

        return $items;
    }

The problem is that some posts are in 3+ categories. So I get a wrong result, I get same post 3+ times successively. I need that this post even if is at more then one category to be showed in my rss only 1 time.

EIDTED:

Here is right code, if some one will need it:

    private function getItems()
    {
        $this->dbConnect($detailsTable);
        mysql_select_db(DB_NAME);
        mysql_query('SET NAMES utf8');
        mysql_query('SET CHARACTER SET utf8');
        //$result = mysql_query ('SELECT * FROM wp_posts WHERE post_status="publish" and post_category!=17 and post_category!=18 ORDER BY post_date DESC LIMIT 20', LINK);
        $result = mysql_query('SELECT 

ID
, post_title
, post_content
, post_date
, group_concat(DISTINCT post_category ORDER BY post_category DESC SEPARATOR ", " ) as "categories"

FROM wp_posts, wp_term_relationships,wp_term_taxonomy 
        WHERE wp_posts.post_status = "publish"  
        AND wp_term_relationships.object_id = id
        AND wp_term_taxonomy.taxonomy= "category"
        AND !(wp_term_taxonomy.term_taxonomy_id = 11)
        AND wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
        GROUP BY ID, post_title, post_content, post_date ORDER BY wp_posts.post_date DESC LIMIT 25', LINK);
        mysql_close(LINK);
        $items = '';
        while($row = @mysql_fetch_array($result))
        {
            $title = UTFayar($row['post_title']);
            $content = UTFayar($row['post_content']);



            $items .= '<item id="'.$row["ID"].'">
                <title><![CDATA['.$title.']]></title>
                <description><![CDATA['. $content .']]></description>
                <pubDate>'.date('D, j M Y H:i:s T', strtotime($row['post_date'])).'</pubDate>
                <category>'.$row['categories'].'</category>
            </item>';
        }
        $items .= '</channel>
                </rss>';

        return $items;
    }

Upvotes: 0

Views: 269

Answers (1)

Matthewhall58
Matthewhall58

Reputation: 102

The problem is that you need to deal with the categories in some way.... rolling them up and displaying them in a list with commas may be a good way to deal with it.

mysql has a nice function called "GROUP_CONCAT" http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

your query would be 

    SELECT 

    ID
    , post_title
    , post_content
    , post_date
    , group_concat(DISTINCT post_category ORDER BY post_category DESC SEPARATOR ', ' ) as `categories`

    FROM wp_posts, wp_term_relationships,wp_term_taxonomy 
            WHERE wp_posts.post_status = "publish"  
            AND wp_term_relationships.object_id = id
            AND wp_term_taxonomy.taxonomy= "category"
            AND !(wp_term_taxonomy.term_taxonomy_id = 11)
            AND wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
GROUP BY    ID
    , post_title
    , post_content
    , post_date             
ORDER BY wp_posts.post_date DESC

Upvotes: 1

Related Questions