Jump to content

Mysql Select LIKE


NewcastleFan

Recommended Posts

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by Barand
Link to comment
Share on other sites

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 :(

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.