Mysql Select LIKE

[NewcastleFan]

Hey guys, not sure if anyone will be able to help with this but I have a small issue with a select filter I'm trying to use.

 

I'm trying to select posts with the correct tag's to display a list of the posts in the page. I've got it working to a certain extent with the code below, however I have this small issue:

 

If I click the link that says "testtag1" it will filter the posts and only show the posts with testtag1.

 

But If I have a shortened link say the tag is called just "test" it will bring up anything with test in it, so posts with testtag1, testtag2, test will appear.

 

is there a way to refine the LIKE filter in mysql to stop this?

	if (!$_GET['tagseourl']) {
		$pageid = '';
	} else {
		$pageid = $_GET['tagseourl'];
	}	
			
		$sqlCommand = "SELECT blogid, blogtitle, content, blogtime, category, blogseourl, tag, author FROM blog WHERE tag LIKE '%$pageid%'";
		$query = mysqli_query($myConnection, $sqlCommand) or die (mysqli_error());
	$blogDisplay = '';
			while ($row = mysqli_fetch_array($query)) {
			$blogid = $row["blogid"];
			$blogtitle = $row["blogtitle"];
			$content = $row["content"];
			$blogtime = $row["blogtime"];
			$category = $row["category"];
			$blogseourl = $row["blogseourl"];
			$tag = $row["tag"];
			$author = $row["author"];
			$contentshort =  substr($content, 0, 250);
			
	$blogDisplay .= '<h1><a href="/blog/'. $blogseourl .'"> ' . $blogtitle . ' </a></h1> ' . $contentshort . '... <a href="/blog/'. $blogseourl .'">Read More...</a><br /><br /> ' . $author . ' posted on ' . $blogtime . ' |	 Category: ' . $category . ' |	 Tags: ' . $tag . ' | <a href="/blog/'. $blogseourl .'#disqus_thread"></a>'; 
			
			}
	mysqli_free_result($query);

The field tag in the database looks like this:

 

ID ¦ tags

1 ¦ testtag1, testtag2, testtag3

2 ¦ test. testtag1

3 ¦ testtag2, testtag3

 

etc.

[PravinS]

Remove "%" from LIKE statement, search only pageid like e.g. "LIKE '$pageid'" in WHERE clause of SELECT query

[awjudd]

Remove "%" from LIKE statement, search only pageid like e.g. "LIKE '$pageid'" in WHERE clause of SELECT query

 

If you are going to do that, then you should change the LIKE to an = operator.

 

~awjudd

[NewcastleFan]

Remove "%" from LIKE statement, search only pageid like e.g. "LIKE '$pageid'" in WHERE clause of SELECT query

 

That would work if I only had one tag for each post correct? The tag column has the option of multiple values e.g; "testtag1, testtag2". So if I only used the "LIKE '$pageid'" it will only filter items if they are the exact match so I couldn't have a tag page for testtag1 and a separate page for testtag2 it would have to be a page for both testtag1 and testtag2.

[Barand]

Normalize, normalize, normalize!

 

http://forums.phpfreaks.com/topic/273634-best-way-to-set-up-tables-when-multiple-values/?do=findComment&comment=1408360

 

 

The normalised version would look like

id| tag
--|----------
1 | testtag1
1 | testtag2
1 | testtag3
2 | test
2 | testtag1
3 | testtag2
3 | testtag3

Now you can index the tag column, use "=" instead of LIKE and thus speed up your queries

Edited April 12, 2013 by Barand

[NewcastleFan]

Was hoping I wouldn't have to create another table

 

So I need another table like:

 

BlogID ¦ Tag

1 ¦ testtag1

1 ¦ testtag2

1 ¦ test

2 ¦ testtag2

2 ¦ test

2 ¦ testtag1

 

To then re-write everything to pull from both tables? I thought it would of been a lot simpler to just use one table and the implode and explode functions

[Barand]

If it was so much simpler that way, why were you having difficulties?

 

Now (normalized) you can select where tag = 'test' without a problem

Edited April 12, 2013 by Barand