Jump to content

Mysql Select LIKE


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>'; 

The field tag in the database looks like this:


ID ¦ tags

1 ¦ testtag1, testtag2, testtag3

2 ¦ test. testtag1

3 ¦ testtag2, testtag3



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!





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.

Join the conversation

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

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.