Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    Regular Coder
    Join Date
    Jun 2007
    Posts
    310
    Thanks
    86
    Thanked 3 Times in 3 Posts

    Related articles help please?

    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?
    PHP Code:

    <?

    $story_id 
    $_GET['story_id']; 

    $query "SELECT DISTINCT story_id, headline, story FROM stories  
    WHERE (headline LIKE '%$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

  • #2
    Senior Coder
    Join Date
    Mar 2003
    Location
    Atlanta
    Posts
    1,037
    Thanks
    14
    Thanked 30 Times in 28 Posts

    Several Things Wrong

    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.
    Most of my questions/posts are fairly straightforward and simple. I post long verbose messages in an attempt to be thorough.

  • #3
    Regular Coder
    Join Date
    Jun 2007
    Posts
    310
    Thanks
    86
    Thanked 3 Times in 3 Posts
    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
    Last edited by stfc_boy; 02-20-2008 at 09:53 PM.

  • #4
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by stfc_boy View Post
    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
    Last edited by oesxyl; 02-20-2008 at 10:37 PM.

  • #5
    Regular Coder
    Join Date
    Jun 2007
    Posts
    310
    Thanks
    86
    Thanked 3 Times in 3 Posts
    Sorry my bad...

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

    so maybe this makes more sense:

    Code:
    $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
    Last edited by stfc_boy; 02-20-2008 at 11:10 PM.

  • #6
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by stfc_boy View Post
    PHP Code:
    $story_id $_GET['story_id'];  

    $query "SELECT DISTINCT story_id, headline, story, related FROM stories   
    WHERE (headline LIKE '%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:

    PHP Code:
    '%".$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
    Last edited by oesxyl; 02-20-2008 at 11:26 PM.

  • #7
    Senior Coder
    Join Date
    Mar 2003
    Location
    Atlanta
    Posts
    1,037
    Thanks
    14
    Thanked 30 Times in 28 Posts
    Quote Originally Posted by oesxyl View Post
    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.
    Most of my questions/posts are fairly straightforward and simple. I post long verbose messages in an attempt to be thorough.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •