SitePoint Sponsor

User Tag List

Results 1 to 18 of 18
  1. #1
    Non-Member
    Join Date
    Feb 2005
    Posts
    737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Query returns no results

    In my Content Management system when I add a story to the database I have two fields to link related stories to so I can enter a search team in those two fields (named term_one and term_two) and then ask the front-end website templated page (where the info in the CMS is echoed out to) to perform a like search on the rest of my stories in the table:

    PHP Code:
    <?
    $query 
    "SELECT DISTINCT story_id FROM cms_stories WHERE (headline LIKE '%$term_one%' OR headline LIKE '%$term_two%' OR body_text LIKE '%$term_one%' OR body_text LIKE '%$term_two%') AND story_id != $story_id"$result mysql_query($query) OR die(mysql_error()); 

    if (
    mysql_num_rows($result) <= 0) { 
    echo (
    "<DIV ALIGN=\"CENTER\">Sorry, there are no related stories</div>"); 

    else {
    // If there is a result!!; 
    if (mysql_num_rows($result) > 0) {  
    }
    }
    while(
    $rows mysql_fetch_assoc($result))   { 
    echo 
    " - <a href='story.php?story_id={$row[story_id]}'>{$row[headline]}</a><p></p>"

    ?>
    Now I need each of the results returned to be distinct and also Not return a result that is also the the current story_id

    Now after some help from Dan, I came up with the above, which I thought would work and return results, but it outputs nothing no error message or no message with no results returned.

    Now Ive played around with it and if I change my query to select * and only return one search result I get the thing to work:

    PHP Code:
    <?
    $SQL 
    "SELECT * FROM cms_stories WHERE (headline LIKE '%$term_one%' 
    OR headline LIKE '%
    $term_two%' OR body_text LIKE '%$term_one%' OR 
    body_text LIKE '%
    $term_two%') AND story_id != $story_id"
    $result mysql_query($SQL) OR die(mysql_error()); 
    $row mysql_fetch_array($result); 
    echo 
    "<a href='story.php?story_id={$row[story_id]}'>{$row[headline]}</a>"
    ?>
    But this does not really help me as the results returned are not distinct and as I say it only returns one result.

    So can anyone please advise on why my first query is not working?

    Many Thanks

    Chris

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    run your first query outside of php -- you will see that it does work

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Non-Member
    Join Date
    Feb 2005
    Posts
    737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks,

    Have done in Mysql and the query runs, but returns no results like it does in php?

    Thanks

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    returns no results? you mean, no rows at all?

    would you mind showing the exact query that you tried?

    and then try it again without the WHERE clause and see if that returns anything
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Non-Member
    Join Date
    Feb 2005
    Posts
    737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yep, course - no probs! Thanks

    Code:
    SELECT DISTINCT story_id FROM cms_stories WHERE (headline LIKE 'news' OR headline LIKE 'sport' OR body_text LIKE 'news' OR body_text LIKE 'sport') AND story_id != 1;
    Without the WHERE:

    Code:
    SELECT DISTINCT story_id FROM cms_stories;
    Returns all my story_id rows:

    story_id
    7
    9
    10
    11
    12
    13
    14
    15
    etc...

    Thanks

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you might wish to try your LIKE expressions with some wildcards

    e.g. instead of LIKE 'news' try LIKE '%news%' etc.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    Non-Member
    Join Date
    Feb 2005
    Posts
    737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks,

    Tried that and it now is returning results. So why is PHP not having it then?

    Thanks

    Chris

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    do you think it might have anything to do with the fact that you are returning only the story_id and no other columns?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    Non-Member
    Join Date
    Feb 2005
    Posts
    737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Thanks. I don't think it's that as i've tried also outputting the headline in php and I just this time get the could not find any stories message.

    PHP Code:
    $query "SELECT DISTINCT story_id, headline FROM cms_stories
    WHERE (headline LIKE '%news%' OR headline LIKE '%sport%' OR body_text LIKE '%news%' OR body_text LIKE '%sport%')
    AND story_id !=1; 
    Many Thanks

  10. #10
    Non-Member
    Join Date
    Feb 2005
    Posts
    737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry hate to be a pain, but can anyone else offer any tips please?

    Thanks

  11. #11
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,807
    Mentioned
    158 Post(s)
    Tagged
    3 Thread(s)
    Give us the php script you are using after the query please Chris

    Spike
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....

  12. #12
    SitePoint Evangelist Waffles's Avatar
    Join Date
    Nov 2005
    Posts
    435
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This may just be a typo in the forums but that $query doesn't end correctly - there is no "...I would imagine you simple forgot to add it in the forums?

  13. #13
    Non-Member
    Join Date
    Feb 2005
    Posts
    737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Guys,

    Here's why whole query.

    PHP Code:
    <?
    $query 
    "SELECT DISTINCT story_id, headline FROM cms_stories
    WHERE (headline LIKE '%news%' OR headline LIKE '%sport%' OR body_text LIKE '%news%' OR body_text LIKE '%sport%')
    AND story_id !=1;
    $result = mysql_query($query) OR die(mysql_error())"

    if (
    mysql_num_rows($result) <= 0) { 
    echo (
    "<DIV ALIGN=\"CENTER\">Sorry, there are no related stories</div>"); 

    else {
    // If there is a result!!; 
    if (mysql_num_rows($result) > 0) {  
    }
    }
    while(
    $rows mysql_fetch_assoc($result))   { 
    echo 
    " - <a href='story.php?story_id={$row[story_id]}'>{$row[headline]}</a><p></p>"

    ?>
    And I know there should be results returns when I run it. I've run the query in PHP MyAdmin and it does return results, so I thinki it may be something in my query?

    Many, Many Thanks

    Chris

  14. #14
    SitePoint Evangelist Waffles's Avatar
    Join Date
    Nov 2005
    Posts
    435
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Waffles
    This may just be a typo in the forums but that $query doesn't end correctly - there is no "...I would imagine you simple forgot to add it in the forums?
    said so..you missed out a "..

    PHP Code:
    <?
    $query 
    "SELECT DISTINCT story_id, headline FROM cms_stories
    WHERE (headline LIKE '%news%' OR headline LIKE '%sport%' OR body_text LIKE '%news%' OR body_text LIKE '%sport%')
    AND story_id !=1"
    ;

    $result mysql_query($query) OR die(mysql_error());

    if (
    mysql_num_rows($result) <= 0) {

        echo (
    "<DIV ALIGN=\"CENTER\">Sorry, there are no related   
        stories</div>"
    );

    } else {

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

    }

    // you didn't need half of the if/elses...and you had the while out side of the if/else brackets anyway so it would have run regardless..



    and on my edit indenting my code, I noticed that it would never return any data because you are using $rows to return data, and $row[] to echo it...

    i have fixed the code now

  15. #15
    SitePoint Evangelist Waffles's Avatar
    Join Date
    Nov 2005
    Posts
    435
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    and sorry to double post but omg how did none of us spot that in the first example, when the code was fine?!

  16. #16
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,807
    Mentioned
    158 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by 7724
    And I know there should be results returns when I run it. I've run the query in PHP MyAdmin and it does return results, so I thinki it may be something in my query?
    If a script runs in your phpMyAdmin it's not the query!

    Quote Originally Posted by Waffles
    and sorry to double post but omg how did none of us spot that in the first example, when the code was fine?!
    Beer
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....

  17. #17
    Non-Member
    Join Date
    Feb 2005
    Posts
    737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry, did not mean query. It's too hot, my brain's not in gear!

    Works a treat now - thanks Guys!

  18. #18
    SitePoint Evangelist Waffles's Avatar
    Join Date
    Nov 2005
    Posts
    435
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by spikeZ
    Beer
    and it was stupidly hot these past few days I suppose


    glad ya got it working, in the end


Bookmarks

Posting Permissions

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