Problem in getting the search results

**mayflower** · Dec 6, 2004 09:17

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

**swdev** · Dec 6, 2004 09:34

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

**Infizi** · Dec 6, 2004 09:46

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";

}

**Infizi** · Dec 6, 2004 09:49

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

**mayflower** · Dec 6, 2004 10:28

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

**swdev** · Dec 6, 2004 10:32

try mysql_num_rows, not mysql_count_rows

**KDesigns** · Dec 6, 2004 10:55

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()?

**mayflower** · Dec 6, 2004 10:58

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

**KDesigns** · Dec 6, 2004 11:12

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']

**mayflower** · Dec 6, 2004 11:19

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

**swdev** · Dec 6, 2004 11:27

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?

**mayflower** · Dec 6, 2004 11:46

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

**KDesigns** · Dec 6, 2004 12:13

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.

**mayflower** · Dec 6, 2004 13:19

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

**KDesigns** · Dec 6, 2004 13:43

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);

**mayflower** · Dec 6, 2004 14:07

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

**KDesigns** · Dec 6, 2004 14:20

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);

**mayflower** · Dec 6, 2004 15:10

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

**KDesigns** · Dec 6, 2004 23:32

Just glad it's working!

**Infizi** · Dec 7, 2004 03:01

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!

**mayflower** · Dec 7, 2004 12:52

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

User Tag List

Thread: Problem in getting the search results

Thread Tools

Display

Problem in getting the search results

Bookmarks

Bookmarks

Posting Permissions