Problem with selecting post with same tags

Johns3n · August 23, 2010

Hi PHPFreaks.com

I'm currently having some trouble with a mySQL sentence, where i am trying to select other updates (posts in the database) based on the id that is not equal to the post that the user is viewing right now.

It's basically a related updates system, so I don't wan't the update that you viewing right now to be displayed.

Here is the SQL query that i wrote, which unfortunaly still also selects the post that the user is viewing.

$related_data = mysql_query("SELECT title, id FROM " .$db_prefix. "updates WHERE NOT " .$db_prefix. "updates.id = ".$self." AND " .$db_prefix. "updates.tags LIKE '%".$tags_explode[0]."%' OR " .$db_prefix. "updates.tags LIKE '%".$tags_explode[1]."%' OR " .$db_prefix. "updates.tags LIKE '%".$tags_explode[2]."%' OR " .$db_prefix. "updates.tags LIKE '%".$tags_explode[3]."%' OR " .$db_prefix. "updates.tags LIKE '%".$tags_explode[4]."%' ORDER BY id ASC LIMIT 5" );

The $self variable is equal to the id of the post the user is viewing.

Thanks in advance

kickstart · August 23, 2010

Hi

Suspect your issue is operator precedence. The ANDs will be evaluated before the ORs. Use brackets around the ORed statements for force the precedence.

All the best

Keith

Johns3n · August 23, 2010

Sorry I am abit of a noobie at PHP

So you mean my code should look a little like this?

$related_data = mysql_query("SELECT title, id FROM " .$db_prefix. "updates WHERE NOT " .$db_prefix. "updates.id = ".$self." AND (" .$db_prefix. "updates.tags LIKE '%".$tags_explode[0]."%' OR " .$db_prefix. "updates.tags LIKE '%".$tags_explode[1]."%' OR " .$db_prefix. "updates.tags LIKE '%".$tags_explode[2]."%' OR " .$db_prefix. "updates.tags LIKE '%".$tags_explode[3]."%' OR " .$db_prefix. "updates.tags LIKE '%".$tags_explode[4]."%') ORDER BY id ASC LIMIT 5" );

kickstart · August 23, 2010

Hi

Yep, like that.

With operator precedence your original code would have checked for:-

WHERE NOT " .$db_prefix. "updates.id = ".$self."

AND " .$db_prefix. "updates.tags LIKE '%".$tags_explode[0]."%'

so where updates.id was not $self and that updates.tags was like $tags_explode[0].

However it would then check the following lines each in turn:-

OR " .$db_prefix. "updates.tags LIKE '%".$tags_explode[1]."%'

OR " .$db_prefix. "updates.tags LIKE '%".$tags_explode[2]."%'

OR " .$db_prefix. "updates.tags LIKE '%".$tags_explode[3]."%'

OR " .$db_prefix. "updates.tags LIKE '%".$tags_explode[4]."%'

Hence if there was a match on $tags_explode[1] to $tags_explode[4] the record would be returned whether or not updates.id was $self.

Using the brackets forces it to evaluate the bit in the brackets first. So if there is a match on $tags_explode[0] to $tags_explode[4] AND updates.id is not $self

Exactly the same as mixing multiplication and addition in a mathematical equation.

All the best

Keith

Johns3n · August 23, 2010

All the best

Keith

Thanks for the awesome amount of info and it was really enlighting! And i actually understood it! ^^

Unfortunally when I try to put this new code into praksis, the related updates just doesn't find any updates at all now I tried switching around a little the SQL sentence aswell but didn't work out either..

SO if you wan't to maybe take a look over my code? and maybe you can see where it goes wrong? Of course i understand if you better things to do So if no just don't reply

First off.. i made sure that 2 updates in the DB have the exact 5 same tags "Musik Jam Plug Play Scene" all seperated with a space. So these two posts should show up in their related... i run multiple pages from the same PHP file using a $action variable and then a $_GET to determaine which page you end up on... and it's on the $action=update page im having some trouble

elseif($action == "update")
{
// Get update id from URL to see which update we are viewing
$updateid = mysql_real_escape_string($_GET['updateid']);

// Make a SQL call to get the data to the update that needs viewing
$update_data = mysql_query("SELECT * FROM " .$db_prefix. "updates WHERE " .$db_prefix. "updates.id = ".$updateid."" );

// Run while loop for all found results (should always just be ONE result never the less)
while($update = mysql_fetch_array($update_data))
{
	echo "<body>
    		<div id='wrapper'>
			<div id='header'>
        		</div>";

	echo "<div id='update'>
	<h2>" .$update['title']. "</h2>
	<p>" .$update['content']. "</p>
	<div class='related'><b>Relaterede updates:</b><br /><br />";

	// Explode tags in the update that er being viewed at the space between the words
	$tags = $update['tags'];
	$tags_explode = explode(" ", $tags);

	// Variable needed to determaine which update the user is already viewing so it doesn't show up in the related updates.				
	$self = "".$update['id']."";

	// Make SQL query to get the related updates based on the exploded tags
	$related_data = mysql_query("SELECT title, id FROM " .$db_prefix. "updates WHERE NOT " .$db_prefix. "updates.id = ".$self." AND " .$db_prefix. "updates.tags LIKE '%".$tags_explode[0]."%' (OR " .$db_prefix. "updates.tags LIKE '%".$tags_explode[1]."%' OR " .$db_prefix. "updates.tags LIKE '%".$tags_explode[2]."%' OR " .$db_prefix. "updates.tags LIKE '%".$tags_explode[3]."%' OR " .$db_prefix. "updates.tags LIKE '%".$tags_explode[4]."%') ORDER BY id ASC LIMIT 5" );

	// Run while loop to output all found data
	while($related = mysql_fetch_array($related_data))
	{
		echo "
		<p style='margin-bottom:5px;'><a href='view.php?action=update&updateid=" .$related['id']. "'>" .$related['title']. "</a></p>
		";
	}

	echo "
	</div>
	</div>";
}
}

As you can see i explode() the tags in the db at the space and then line each of the 5 tags up to find matching results..

If you have any further questions about my code.. please let me know

And thanks in advance

kickstart · August 23, 2010

Hi

:cry: . Think I have spotted it and probably my fault for not spotting it with your last post

$related_data = mysql_query("

SELECT title, id FROM " .$db_prefix. "updates

WHERE NOT " .$db_prefix. "updates.id = ".$self."

AND (" .$db_prefix. "updates.tags LIKE '%".$tags_explode[0]."%'

OR " .$db_prefix. "updates.tags LIKE '%".$tags_explode[1]."%'

OR " .$db_prefix. "updates.tags LIKE '%".$tags_explode[2]."%'

OR " .$db_prefix. "updates.tags LIKE '%".$tags_explode[3]."%'

OR " .$db_prefix. "updates.tags LIKE '%".$tags_explode[4]."%')

ORDER BY id ASC LIMIT 5" );

The bracket needs to be just after the AND rather than after the first OR.

If that doesn't fix it can you export the table definitions and a couple of lines of data from them.

All the best

Keith

Johns3n · August 23, 2010

Structure dump for table

Rowname Datatype Nullvalue Standardvalue

id int(11) No

title varchar(250) No

content text No

end_date datetime No 0000-00-00 00:00:00

tags varchar(250) No

is_active varchar(20) No

Data dump for table

3 | ARGHHH! | <p>gsdgdsgdsgsdgsdgd</p> | 2010-09-16 02:00:00 | Musik Jam Plug Play Scene | Ja

4 | What? | <p>Testing testing!</p> | 2010-11-08 14:00:00 | Musik Jam Plug Play Scene | Ja

Is this what you mean mate? Because I actually spotted that little error myself before you replied so i'm still running blank here But i really appreciate that you are taking your time to help me!

kickstart · August 23, 2010

Hi

Tried the SQL on its own and it appears to work. Put in one id and it finds the other.

Used you php code and it also worked (once I put the bracket in the right place), so not sure what the issue is.

However looking at your code I would suggest that you do not use a column with tags (although I appreciate that you might be modifying an existing system and not be in a position to change this). Separate the tags off onto a different table, with one row per tag per row on the updates table. You can then use JOINs to find the matching rows which will be FAR more efficient than using LIKE.

Set up a table as follows

-- phpMyAdmin SQL Dump
-- version 3.2.0.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Aug 23, 2010 at 02:50 PM
-- Server version: 5.1.36
-- PHP Version: 5.3.0

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `test`
--

-- --------------------------------------------------------

--
-- Table structure for table `updates_tags`
--

CREATE TABLE IF NOT EXISTS `updates_tags` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `updatesId` int(11) NOT NULL,
  `Tag` varchar(20) NOT NULL,
  PRIMARY KEY (`Id`),
  KEY `updatesId` (`updatesId`),
  KEY `Tag` (`Tag`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

--
-- Dumping data for table `updates_tags`
--

INSERT INTO `updates_tags` (`Id`, `updatesId`, `Tag`) VALUES
(1, 3, 'Musik'),
(2, 3, 'Jam'),
(3, 3, 'Plug'),
(4, 3, 'Play'),
(5, 3, 'Scene'),
(6, 4, 'Musik'),
(7, 4, 'Jam'),
(8, 4, 'Plug'),
(9, 4, 'Play'),
(10, 4, 'Scene');

As you can see, one row per tag for each row on updates.

Then try your page like this:-

elseif($action == "update")
{
// Get update id from URL to see which update we are viewing
$updateid = mysql_real_escape_string($_GET['updateid']);

echo "<body>
	<div id='wrapper'>
		<div id='header'>
		</div>";

$related_data = mysql_query("SELECT DISTINCT d.content, d.title, d.id
FROM updates a INNER JOIN updates_tags b ON a.id = b.updatesId
INNER JOIN updates_tags c ON b.Tag = c.Tag INNER JOIN updates d ON c.updatesId = d.id
WHERE a.Id = 3
AND a.id != d.id ORDER BY d.id ASC LIMIT 5" ) or die(mysql_error());
if($related = mysql_fetch_array($related_data))
{
	echo "<div id='update'>
	<h2>" .$related['title']. "</h2>
	<p>" .$related['content']. "</p>
	<div class='related'><b>Relaterede updates:</b><br /><br />";
	echo "
	<p style='margin-bottom:5px;'><a href='MatchDb.php?action=update&updateid=" .$related['id']. "'>" .$related['title']. "</a></p>
	";
	while($related = mysql_fetch_array($related_data))
	{
		echo "
		<p style='margin-bottom:5px;'><a href='MatchDb.php?action=update&updateid=" .$related['id']. "'>" .$related['title']. "</a></p>
		";
	}
	echo "
	</div>
	</div>";
}
}

This saves you reading the record from updates, exploding it and extracting the tags. Also means you don't really have to worry about tags having spaces in them or the total number of tags getting too long for an update.

If could be improved further if you want. On the table I have added rather than storing the actual tags you could just store the id field for a table listing each tag. This would be more efficient, plus would make it easy for you to have a list to select tags for an update.

All the best

Keith

Johns3n · August 23, 2010

Actually i'm writing a new system from stratch so it is possible to change this

I'll mark this as solved for now mate and give your code a try! Thanks again for all the help

Sign In

Problem with selecting post with same tags

Recommended Posts

Johns3n

Link to comment

Share on other sites

kickstart

Link to comment

Share on other sites

Johns3n

Link to comment

Share on other sites

kickstart

Link to comment

Share on other sites

Johns3n

Link to comment

Share on other sites

kickstart

Link to comment

Share on other sites

Johns3n

Link to comment

Share on other sites

kickstart

Link to comment

Share on other sites

Johns3n

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information