SitePoint Sponsor

User Tag List

Results 1 to 21 of 21
  1. #1
    SitePoint Enthusiast
    Join Date
    Mar 2004
    Location
    USA
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Problem in getting the search results

    Hi:

    I am getting this error when I do the search of the site :

    Search results

    Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in ./search.php on line 38
    query executed in 0.000 seconds.

    in my html page I have this part calling the search.php file

    HTML Code:
    .....
    
    <TD COLSPAN=3 ROWSPAN=2>
    <form method='post' action='search.php'>
    <input type="image"  SRC="images/Search-button.gif" value="Search" ALT ="Search" \
    Name="Search"</TD>
    
    <TD COLSPAN=5 ROWSPAN=2>
    <input type="text" size="15" name='keyword'></form></TD>
    
    ........
    Following is my search.php file:

    PHP Code:

    <?
    /*                                                                                   
     * search.php                                                                        
     *                                                                                   
     * Script for searching a datbase populated with keywords by the                     
     * load-db.php-script.                                                               
     */
    print "<html><head><title>My Search Engine</title></head><body>\n";
    if( 
    $_POST['keyword'] )
    {
      
    /* Connect to the database: */
      
    mysql_connect("localhost","..","..")
        or die(
    "ERROR: Could not connect to database!");
      
    mysql_select_db("db");
      
    /* Get timestamp before executing the query: */
      
    $start_time getmicrotime();
      
    /* Execute the query that performs the actual search in the DB: */
      
    $result mysql_query(" SELECT                                                     
                                    p.page_url AS url,                                   
                                    COUNT(*) AS occurrences                              
                                FROM                                                     
                                    page p,                                              
                                    word w,                                              
                                    occurrence o                                         
                                WHERE                                                    
                                    p.page_id = o.page_id AND                            
                                    w.word_id = o.word_id AND                            
                                    w.word_word = \""
    .$_POST['keyword']."\"              
                                GROUP BY                                                 
                                    p.page_id                                            
                                ORDER BY                                                 
                                    occurrences DESC                                     
                                LIMIT "
    .$_POST['results'] );
      
    /* Get timestamp when the query is finished: */
      
    $end_time getmicrotime();
      
    /* Present the search-results: */
      
    print "<h2>Search results for '".$_POST['keyword']."':</h2>\n";
      for( 
    $i 1$row mysql_fetch_array($result); $i++ )
        {
          print 
    "$i. <a href='".$row['url']."'>".$row['url']."</a>\n";
          print 
    "(occurrences: ".$row['occurrences'].")<br><br>\n";
        }
      
    /* Present how long it took the execute the query: */
      
    print "query executed in ".(substr($end_time-$start_time,0,5))." seconds.";
    }


    print 
    "</body></html>\n";
    /* Simple function for retrieving the currenct timestamp in microseconds: */
    function getmicrotime()
    {
      list(
    $usec$sec) = explode(" ",microtime());
      return ((float)
    $usec + (float)$sec);
    }
    ?>
    I couldn't figure out what is going wrong. Any help is greatly appreciated.
    mayflower

  2. #2
    SitePoint Wizard swdev's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    1,053
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Place the query into a variable like this

    PHP Code:
        $sql "SELECT p.page_url as url ..."
    etc.

    Then, before you run the query, echo it out, like this

    PHP Code:
        echo $sql;
        
    $result mysql_query($sql) or die('Error Running Query' $sql ' due to ' mysql_error()); 
    This should then show you exactly what query you are running, and if there are any SQL errors

    Hope this helps

  3. #3
    Obsessive designer Infizi's Avatar
    Join Date
    May 2004
    Location
    North Pole
    Posts
    450
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    wrong!

    the query isnt returning any results. your code dosent allow for no results..

    you need an if staments.

    try this
    PHP Code:
    if(mysql_count_rows($result) > 0){
    for( 
    $i 1$row mysql_fetch_array($result); $i++ ) 
        { 
          print 
    "$i. <a href='".$row['url']."'>".$row['url']."</a>\n"
          print 
    "(occurrences: ".$row['occurrences'].")<br><br>\n"
        } 
    }else{
        print 
    "No results found";


  4. #4
    Obsessive designer Infizi's Avatar
    Join Date
    May 2004
    Location
    North Pole
    Posts
    450
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    also, PHP wont see the line spaces, it cuts the query up into sections only allowing one space, so even if you leave 100 lines between SELECT and field, it wont matter.

    As long as your whole SQL query is between quote marks with nothing else between it (except for echoing vars etc) you fine

  5. #5
    SitePoint Enthusiast
    Join Date
    Mar 2004
    Location
    USA
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the replies but when I tried that it gives this error:

    Search results

    Fatal error: Call to undefined function: mysql_count_rows() in ./search.php on line 39

    mayflower

  6. #6
    SitePoint Wizard swdev's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    1,053
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    try mysql_num_rows, not mysql_count_rows

  7. #7
    Are You There? KDesigns's Avatar
    Join Date
    Oct 2003
    Location
    Your Monitor
    Posts
    1,147
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    mysql_num_rows isn't going to help him out here. If I'm correct the result of an empty query would simply not display any data.

    When you see an error like you have it usually points to a problem in the database connection, selection of database, or query. Is your db name correct in the mysql_select_db()?
    ChooseDaily.com - Follow on Twitter
    Top Resources for Web Designers and Developers Every Day!

  8. #8
    SitePoint Enthusiast
    Join Date
    Mar 2004
    Location
    USA
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you, but still I am not getting the results, I am getting the same old error :

    Search results

    Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in ./search.php on line 25
    No results found queryexecuted in 0.000 seconds.

    I changed the following part as suggested by Infizi and swdev :

    PHP Code:
    .....

     if (
    mysql_num_rows($result) > 0)
    {

      for( 
    $i 1$row mysql_fetch_array($result); $i++ )
        {
          print 
    "$i. <a href='".$row['url']."'>".$row['url']."</a>\n";
          print 
    "(occurrences: ".$row['occurrences'].")<br><br>\n";
        }
    }
      else {

        print 
    "No results found<br>\n";
      }
    .... 

    When I try this with the form part included in the search.php file itself, I mean not calling from a html file it is working fine. So I don't know what is going wrong and where

    mayflower

  9. #9
    Are You There? KDesigns's Avatar
    Join Date
    Oct 2003
    Location
    Your Monitor
    Posts
    1,147
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try adding an id value to your form field as well.
    Code:
    <input type='text' name='keyword' id='keyword' size='15'>
    Also, try setting and echoing out the value sent to your search.php page
    PHP Code:
    $keyword $_POST['keyword'];
    echo 
    "Keyword = $keyword"
    Also, do you know what version of PHP you have? If it is an older version you will need to use $HTTP_POST_VARS['keyword'] rather than just $_POST['keyword']
    ChooseDaily.com - Follow on Twitter
    Top Resources for Web Designers and Developers Every Day!

  10. #10
    SitePoint Enthusiast
    Join Date
    Mar 2004
    Location
    USA
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks KDesigns, I was trying to do the changes as suggested by you but then some other problem is coming: I am encountering this problem when I try to save my file:

    IO error writing ../../index.html: No space left on device

    What causes this and how can I fix this.

    mayflower

  11. #11
    SitePoint Wizard swdev's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    1,053
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sounds like you have run out of disk space. Try deleting some files (ones that you no longer need )

    Did you try my suggestion in Post #2 - If yes, what was the query? Did it look corect? Post it here so we can look at it.

    Also, were there any error messages returned from the mysq_query function call?

  12. #12
    SitePoint Enthusiast
    Join Date
    Mar 2004
    Location
    USA
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes swdev, I did try your suggestion. Again it was giving error saying that there is a syntax error at some line in the select statement. But since the same file works fine when I include the form part in the PHP file , I have a feeling that it would work if I include $keyword = $_POST['keyword']; in my php file as suggested by KDesign. But I am having problem updating the file.

    This is happening when ever I run this search file many times as part of my testing. I don't have that many files in the directory to run out of space. Thank you all for the replies
    mayflower

  13. #13
    Are You There? KDesigns's Avatar
    Join Date
    Oct 2003
    Location
    Your Monitor
    Posts
    1,147
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How much storage space do you have on your server?? It really sounds like you've ran out of storage space. Do you possibly have a directory with old files or an abundance of other, non-essential, files? I can't see any other reason for the error.
    ChooseDaily.com - Follow on Twitter
    Top Resources for Web Designers and Developers Every Day!

  14. #14
    SitePoint Enthusiast
    Join Date
    Mar 2004
    Location
    USA
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I still am getting the same error after adding
    $keyword = $_POST['keyword'];
    echo "Keyword = $keyword";
    It is passing the variable alright but I don't know what is going wrong. This is what it is saying:

    Keyword = art

    Search results for 'art':

    Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in ./search.php on line 44
    No results found
    query executed in 0.000 seconds.

    mayflower

  15. #15
    Are You There? KDesigns's Avatar
    Join Date
    Oct 2003
    Location
    Your Monitor
    Posts
    1,147
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Are you sure the database name is correct in the mysql_select_db()?

    You also may trying to place your SELECT and actual query call in separate var's, ie:
    PHP Code:
    $sql "SELECT ..... "
    $query mysql_query($sql); 
    ChooseDaily.com - Follow on Twitter
    Top Resources for Web Designers and Developers Every Day!

  16. #16
    SitePoint Enthusiast
    Join Date
    Mar 2004
    Location
    USA
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, the database name is correct and I tried putting it in different variables (That is what swdev suggested to do) still I am not getting it. As I mentioned earlier it works fine if I try it with the form part in the search.php file itself. This is the changed PHP file :

    PHP Code:

    <?
    /*                                                                                   
     * search.php                                                                        
     *                                                                                   
     * Script for searching a datbase populated with keywords by the                     
     * load-db.php-script.                                                               
     */

    print "<html><head><title>My Search Engine</title></head><body>\n";
    if( 
    $_POST['keyword'] )
    {
      
    /* Connect to the database: */
      
    $db = @mysql_connect("localhost","..l","..")
        or die(
    "ERROR: Could not connect to database!");
      @
    mysql_select_db(".."$db);

     
    /* Get timestamp before executing the query: */
      
    $start_time getmicrotime();
      
    /* Execute the query that performs the actual search in the DB: */
      
    $sql " SELECT                                                                    
                                    p.page_url AS url,                                   
                                    COUNT(*) AS occurrences                              
                                FROM                                                     
                                    page p,                                              
                                    word w,                                              
                                    occurrence o                                         
                                WHERE                                                    
                                    p.page_id = o.page_id AND                            
                                    w.word_id = o.word_id AND                            
                                    w.word_word = \""
    .$_POST['keyword']."\"              
                                GROUP BY                                                 
                                    p.page_id                                            
                                ORDER BY                                                 
                                    occurrences DESC                                     
                                LIMIT "
    .$_POST['results'];

      
    $result mysql_query($sql$db) or die('Error Running Query' $sql ' due to ' \
    mysql_error());


      
    /* Get timestamp when the query is finished: */
      
    $end_time getmicrotime();

      
    /* Present the search-results: */
     
    if (mysql_num_rows($result) > 0)
      {
       for( 
    $i 1$row = @mysql_fetch_array($result); $i++ )
        {
          print 
    "$i. <a href='".$row['url']."'>".$row['url']."</a>\n";
          print 
    "(occurrences: ".$row['occurrences'].")<br><br>\n";
        }
      }
      else
        {
          print 
    "No results found <br>\n";
        }

      
    /* Present how long it took the execute the query: */
      
    print "query executed in ".(substr($end_time-$start_time,0,5))." seconds.";
    }


    print 
    "</body></html>\n";
    /* Simple function for retrieving the currenct timestamp in microseconds: */
    function getmicrotime()
    {
      list(
    $usec$sec) = explode(" ",microtime());
      return ((float)
    $usec + (float)$sec);
    }
    ?>

    And this is the error now I am getting when I call this from a html file.

    Error Running Query SELECT p.page_url AS url, COUNT(*) AS occurrences FROM page p, word w, occurrence o WHERE p.page_id = o.page_id AND w.word_id = o.word_id AND w.word_word = "art" GROUP BY p.page_id ORDER BY occurrences DESC LIMIT due to You have an error in your SQL syntax near '' at line 16

  17. #17
    Are You There? KDesigns's Avatar
    Join Date
    Oct 2003
    Location
    Your Monitor
    Posts
    1,147
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Whenever a page includes PHP coding it should have a .php file extension.

    Also... your SQL Syntax Error is most likely the double quotes around the word 'art'. Make those double quotes single quotes and it should do the trick. I know you are using a variable to populate that keyword, but still place just single quotes around that part of the query.

    As long as you have the keyword set in a variable as I mentioned above (ie. $keyword = $_POST['keyword']; ) you can do your query like this:

    PHP Code:
    $keyword $_POST['keyword'];
    $results $_POST['results'];

    $sql "SELECT p.page_url AS url, COUNT(*) AS occurrences FROM page p, word w, occurrence o WHERE p.page_id = o.page_id AND w.word_id = o.word_id AND w.word_word = '$keyword' GROUP BY p.page_id ORDER BY occurrences DESC LIMIT '$results'";

    $query mysql_query($sql); 
    ChooseDaily.com - Follow on Twitter
    Top Resources for Web Designers and Developers Every Day!

  18. #18
    SitePoint Enthusiast
    Join Date
    Mar 2004
    Location
    USA
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you all, now it is working. I changed the code as follows:

    PHP Code:

    <?
    /*                                                                                                                        
     * search.php                                                                                                             
     *                                                                                                                        
     * Script for searching a datbase populated with keywords by the                                                          
     * load-db.php-script.                                                                                                    
     */

    print "<html><head><title>My Search Engine</title></head><body>\n";
    if( 
    $_POST['keyword'] )
    {
      
    /* Connect to the database: */
      
    $db = @mysql_connect("localhost","--","--")
        or die(
    "ERROR: Could not connect to database!");
      @
    mysql_select_db("--"$db);

     
    /* Get timestamp before executing the query: */
      
    $start_time getmicrotime();
      
    /* Execute the query that performs the actual search in the DB: */
      
    $sql " SELECT p.page_url AS url, COUNT(*) AS occurrences FROM page p, word w, occurrence o WHERE p.page_id = o.page_i\
    d AND w.word_id = o.word_id AND w.word_word = \""
    .$_POST['keyword']."\" GROUP BY p.page_id ORDER BY occurrences DESC";


      
    $result mysql_query($sql$db) or die('Error Running Query' $sql ' because ' mysql_error());


      
    /* Get timestamp when the query is finished: */
      
    $end_time getmicrotime();

      
    /* Present the search-results: */

      
    print "<h2>Search results for '".$_POST['keyword']."':</h2>\n";

      if (
    mysql_num_rows($result) > 0)
      {
       for( 
    $i 1$row = @mysql_fetch_array($result); $i++ )
        {
          print 
    "$i. <a href='".$row['url']."'>".$row['url']."</a>\n";
          print 
    "(occurrences: ".$row['occurrences'].")<br><br>\n";
        }
      }
      else
        {
          print 
    "No results found <br>\n";
        }

      
    /* Present how long it took the execute the query: */
      
    print "query executed in ".(substr($end_time-$start_time,0,5))." seconds.";
    }


    print 
    "</body></html>\n";
    /* Simple function for retrieving the currenct timestamp in microseconds: */
    function getmicrotime()
    {
      list(
    $usec$sec) = explode(" ",microtime());
      return ((float)
    $usec + (float)$sec);
    }
    ?>
    I removed the line LIMIT ".$_POST['results'] from the select clause. So now it is working. Thank you all for your replies. It really helped me a lot in figuring out the problem.
    mayflower

  19. #19
    Are You There? KDesigns's Avatar
    Join Date
    Oct 2003
    Location
    Your Monitor
    Posts
    1,147
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just glad it's working!
    ChooseDaily.com - Follow on Twitter
    Top Resources for Web Designers and Developers Every Day!

  20. #20
    Obsessive designer Infizi's Avatar
    Join Date
    May 2004
    Location
    North Pole
    Posts
    450
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by mayflower
    I removed the line LIMIT ".$_POST['results'] from the select clause. So now it is working.
    was the limit variable sent? and does it have a default?

    make sure you have something like
    PHP Code:
    if($_POST['results'] =< 0$_POST['results'] = 10
    10 should be your minimum search results to allow, just makes sure no one put some odd number in there!

  21. #21
    SitePoint Enthusiast
    Join Date
    Mar 2004
    Location
    USA
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Actually the limit value 'results' was the variable I was using when I was using the search.php file just by itself, I mean without calling it from the html file. So then I had the form part in the same file. So the variable 'results' was for the number of results the user enters. And I forgot to remove that part from the code when I was calling the PHP file from a html file. That is why I was getting the errors. But thanks everybody for all your help.

    mayflower


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
  •