NewcastleFan Posted April 12, 2013 Share Posted April 12, 2013 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 https://forums.phpfreaks.com/topic/276864-mysql-select-like/ Share on other sites More sharing options...
PravinS Posted April 12, 2013 Share Posted April 12, 2013 Remove "%" from LIKE statement, search only pageid like e.g. "LIKE '$pageid'" in WHERE clause of SELECT query Link to comment https://forums.phpfreaks.com/topic/276864-mysql-select-like/#findComment-1424333 Share on other sites More sharing options...
awjudd Posted April 12, 2013 Share Posted April 12, 2013 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 Link to comment https://forums.phpfreaks.com/topic/276864-mysql-select-like/#findComment-1424334 Share on other sites More sharing options...
NewcastleFan Posted April 12, 2013 Author Share Posted April 12, 2013 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 https://forums.phpfreaks.com/topic/276864-mysql-select-like/#findComment-1424335 Share on other sites More sharing options...
Barand Posted April 12, 2013 Share Posted April 12, 2013 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 Link to comment https://forums.phpfreaks.com/topic/276864-mysql-select-like/#findComment-1424354 Share on other sites More sharing options...
NewcastleFan Posted April 12, 2013 Author Share Posted April 12, 2013 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 https://forums.phpfreaks.com/topic/276864-mysql-select-like/#findComment-1424355 Share on other sites More sharing options...
Barand Posted April 12, 2013 Share Posted April 12, 2013 If it was so much simpler that way, why were you having difficulties? Now (normalized) you can select where tag = 'test' without a problem Link to comment https://forums.phpfreaks.com/topic/276864-mysql-select-like/#findComment-1424371 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.