Filtering Results on Category ID Issue

PHP
1

Hi’

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

For example: http://roller.rollertestingserver.co.uk/blog/categories/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?


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

I feel as if I am falling into a trap here, but why not use " = " instead of " like "?


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

// 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)

4

Because the blog_categories is comma separated so it may have more then one value such as (1,7,6)

5

So a blog post can be related to more then one category.

6

In that case use “… IN (1,7,6)”, you pasted an example of how to do that in your original post.