SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Evangelist
    Join Date
    Oct 2005
    Posts
    405
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Filtering Results on Category ID Issue

    Hi'

    I'm having an issue filtering my posts returned for a blog.

    For example: http://roller.rollertestingserver.co.../applications/

    Shows all the post related assigned to applications which has a category id which is '1'.

    It is also on this page pulling through an article which has a category id ='13' & '14' & '15'

    From the SQL query below I know that I am using LIKE in the query and this is the reason why however I'm unsure how to make it unique so it pulls back only LIKE '1' and not '13', '14 etc

    Any tips?

    Code PHP:
    public function getBlogPostsByCategoryId($id) {
    		$db = db::getInstance();
    		if ($result = $db->query("SELECT * FROM `blogs` WHERE `blog_show_live` = 1 AND `blog_categories` LIKE'%".$db->escape($id)."%'")) {
    			if ($rows = $db->getrows($result)) {
    				foreach ($rows as &$row) { 
    					$row['blog_categories_names'] = $this->getCategoryNamesFromId($row['blog_categories']);
    					$row['blog_tags_names'] = $this->getTagNamesFromId($row['blog_tags']); 	
    				} 
     
    					return $rows;
     
    			}
    		}
    	}
     
    public function getCategoryNamesFromId($categories) {
    		$db = db::getInstance();
    		//for each entry check and pull back name and output into array
    		$list = explode(",",substr($categories,0));
    		foreach($list as $item) {
    			if($result = $db->query("SELECT category_name, category_slug FROM categories WHERE category_id IN ($categories)")) {
    				$res = array(); 
                    while ($row = $db->getrow($result))  
                            $res[] = $row; 
                    return $res;
            	}	
    		}
    	}
     
    	public function getTagNamesFromId($tags) {
    		$db = db::getInstance();
    		//for each entry check and pull back name and output into array
    		$list = explode(",",substr($tags,0));
    		foreach($list as $item) {
    			if($result = $db->query("SELECT tag_name, tag_slug FROM tags WHERE tag_id IN ($tags)")) {
    				$res = array(); 
                    while ($row = $db->getrow($result))  
                            $res[] = $row; 
                    return $res;
            	}	
    		}
    	}

  2. #2
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    I feel as if I am falling into a trap here, but why not use " = " instead of " like "?

    PHP Code:
    $result $db->query("SELECT * FROM `blogs` WHERE `blog_show_live` = 1 AND `blog_categories` = ".$db->escape($id)

    // SELECT * FROM `blogs` WHERE `blog_show_live` = 1 AND `blog_categories` = 1 

  3. #3
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,033
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Code SQL:
    SELECT
        *
    FROM
        blogs
    WHERE
        blog_show_live = 1
    AND
        blog_categories = 1

    Do you really need all fields from the blogs table, if not list just the fields you need in the SELECT clause.

    Instead of sending the query direct to your query function, build up their query in a variable and then send the variable to your query function

    PHP Code:
    // example query
    $sql="
        SELECT
              this
            , that
            , something_else
        FROM
        da_table
    WHERE
        id = 
    $id
    "
    ;

    $result $db->query($sql);

    // Debug Query:
    echo "<p>The query which was sent was:</p><p>$sql</p>"
    It makes it easier to debug the query (obviously you should still escape and sanitize any variable that has originated from any sort of user input)
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  4. #4
    SitePoint Evangelist
    Join Date
    Oct 2005
    Posts
    405
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Cups View Post
    I feel as if I am falling into a trap here, but why not use " = " instead of " like "?

    PHP Code:
    $result $db->query("SELECT * FROM `blogs` WHERE `blog_show_live` = 1 AND `blog_categories` = ".$db->escape($id)

    // SELECT * FROM `blogs` WHERE `blog_show_live` = 1 AND `blog_categories` = 1 
    Because the blog_categories is comma separated so it may have more then one value such as (1,7,6)

  5. #5
    SitePoint Evangelist
    Join Date
    Oct 2005
    Posts
    405
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So a blog post can be related to more then one category.

  6. #6
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    In that case use "... IN (1,7,6)", you pasted an example of how to do that in your original post.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •