...

View Full Version : Related articles help please?



stfc_boy
02-20-2008, 07:28 PM
Hi Guys,

I was wondering if anyone could help me. In my database I have a field named related which holds keywords. So for one entry in the database I have the keywords:

park and ride, floods, flood, flooding,

So I want to then run a query whereby I check all the other entries in the database where these terms appear in either the headline column or the story column.

But I have a couple of questions:

Would the below query I created deal with commas. Will it look for the term park and ride, floods, flood, flooding,

Rather than..

park and ride then floods

Etc…

If there is nothing entered in the keywords column how can I default it to say Sorry, there are no related stories?



<?

$story_id = $_GET['story_id'];

$query = "SELECT DISTINCT story_id, headline, story FROM stories
WHERE (headline LIKE '&#37;$related%' OR story LIKE '%$related%')
AND story_id != $story_id AND unix_timestamp(published) <= unix_timestamp(NOW()) ORDER BY unix_timestamp(published) asc LIMIT 5";
$result = mysql_query($query) OR die(mysql_error());
$the_results = mysql_num_rows($result);
if ($the_results == 0) {
echo ("Sorry, there are no related stories");

} else {

while($row = mysql_fetch_assoc($result)) {
echo "<a href='story.php?story_id={$row[story_id]}'>".$row[headline]."</a><br />";
}

}

?>


Thanks

Chris

StupidRalph
02-20-2008, 10:41 PM
You have several keywords in one field which means your database isn't normalized.

Read this quick article to understand better.
http://guelphdad.wefixtech.co.uk/sqlhelp/lists.shtml

By using the % wildcard at the beginning of your search term you eliminate the ability to use the index which will slow your query down drastically. If your database was normalized you will not have to use the wildcard operator (again see article).

Barring any sanitization routine done globably on $_GET you are subjecting yourself to a possible security risk.

From the looks of your setup it appears that you should be using a date field in your DB not timestamp.

We can't see where you set $related in your code so we can't really answer that.

stfc_boy
02-20-2008, 10:49 PM
Thanks,

This isn't my database. I'm just trying to query out of it. I understand normalization as I studied it at Uni and the floors of this database i'm working with.

Sorry if I was not clear but related is a term already saved in the database as my example shows.

Anyone else help. As you can see I think i'm nearly there, but not quite?

Thanks

oesxyl
02-20-2008, 11:34 PM
Thanks,

This isn't my database. I'm just trying to query out of it. I understand normalization as I studied it at Uni and the floors of this database i'm working with.

Sorry if I was not clear but related is a term already saved in the database as my example shows.

Anyone else help. As you can see I think i'm nearly there, but not quite?

Thanks

I presume the code is yours, so as StupidRalph say, "We can't see where you set $related in your code so we can't really answer that."
That is about your variable $related not about db field.

Along with what StupidRalph allready say, I don't think that '&#37;$related%' work because I don't think is expanded with his value in your query.
you can test this by echoing the query.

best regards

stfc_boy
02-21-2008, 12:04 AM
Sorry my bad...

I don't mean the variable related I mean the values in the column related:

so maybe this makes more sense:



$story_id = $_GET['story_id'];

$query = "SELECT DISTINCT story_id, headline, story, related FROM stories
WHERE (headline LIKE '&#37;related%' OR story LIKE '%related%')
AND story_id != $story_id AND unix_timestamp(published) <= unix_timestamp(NOW()) ORDER BY unix_timestamp(published) asc LIMIT 5";
$result = mysql_query($query) OR die(mysql_error());
$the_results = mysql_num_rows($result);
if ($the_results == 0) {
echo ("Sorry, there are no related stories");

} else {

while($row = mysql_fetch_assoc($result)) {
echo "<a href='story.php?story_id={$row[story_id]}'>'.$row[headline].'</a><br />";
}

}


I just typed it wrong!

But my problem remains the same.

Say the values from story_id=2 in the database column for related are:

dog,cat,rat,

and the values from story_id=3 in the database column for related are:

ship,yard,boat,

It outputs the same set of links despite the keywords being different.

So any ideas on how to split up the values in the related column for each story_id that are seperated by a comma and sadly I can't change the database to put it in 3NF? :-(

Thanks

oesxyl
02-21-2008, 12:21 AM
$story_id = $_GET['story_id'];

$query = "SELECT DISTINCT story_id, headline, story, related FROM stories
WHERE (headline LIKE '&#37;related%' OR story LIKE '%related%')
AND story_id != $story_id AND unix_timestamp(published) <= unix_timestamp(NOW())
ORDER BY unix_timestamp(published) asc LIMIT 5";


It outputs the same set of links despite the keywords being different.

- what keywords? I don't see any other variable then $story_id.
- about 'related', do you mean search the word 'related' inside the field headline and story? such as "my stuff related your stuff". Or related is the keyords I miss? In this case you must use something like:



'%".$related."%'


in query.



So any ideas on how to split up the values in the related column for each story_id that are seperated by a comma

that was what StupidRalph say, best way is to normalize the table, else you can use a php explode, split or a regex for each row, but this is a pain.


and sadly I can't change the database to put it in 3NF? :-(
Thanks
you can replicate? or create some view?

best regards

StupidRalph
02-21-2008, 02:10 AM
I presume the code is yours, so as StupidRalph say, "We can't see where you set $related in your code so we can't really answer that."
That is about your variable $related not about db field.

Along with what StupidRalph allready say, I don't think that '%$related%' work because I don't think is expanded with his value in your query.
you can test this by echoing the query.

best regards

This is precisely what I meant. :)



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum