Slight problem with results.

[Dave A]

Hi Charter & Everyone,
I am having a problem with the results that are displayed, when a search is carried out some results come back blank:
I have checked to see if any files in the DB are of zero file length, and have run the PHP Admin cleaner a few times but I can't seem to get rid of these blank results coming through.
Any ideas my friend?

1. [100.00 %]
limit to

2. [95.26 %]
limit to

3. [43.03 %]
limit to

4. [40.82 %]
limit to

5. [40.82 %]
limit to

[Charter]

Does this happen when you do an exact search?

[Dave A]

Hi Charter, yes even an exact search produces a few 100% results with no text or domain details in the results.
And or searches produce even more of those empty results..

[Charter]

Something got out of sync. Maybe info was deleted from the database directly instead of from the admin panel?

Try the following. First create the following table in the same database as the PhpDig tables:

CREATE TABLE blanks (
	blank TEXT DEFAULT '' NOT NULL
);

Then in search_function.php find:

             $query = "SELECT sites.site_url, sites.port, spider.path,spider.file,spider.first_words,sites.site_id,spider.spider_id,spider.last_modified,spider.md5 "
                      ."FROM ".PHPDIG_DB_PREFIX."spider AS spider, ".PHPDIG_DB_PREFIX."sites AS sites "
                      ."WHERE spider.spider_id=$spider_id AND sites.site_id = spider.site_id";
             $result = mysql_query($query,$id_connect);
             $content = mysql_fetch_array($result,MYSQL_ASSOC);

And afterwards add the following:

             if (empty($content['site_url'])) {
                      $blank = addslashes(serialize($content));
                      mysql_query("INSERT INTO blanks (blank) VALUES ('$blank')",$id_connect);
             }

Now do some searches, and then attach to this thread about ten rows from the blanks table so I can have a look-see.

[Dave A]

Hi Charter, I have made the table in the database, copied the code over to the searchfunction.php file

When I do a few searches the results still show blank results but the table in the database would appear to be empty when I go there and look using phpmyadmin
So I am not really sure what is happening the blanks table remains empty?

I would appreciate your help if possible with this.

Heaps of regards
Dave A

[Charter]

Okay, now try the following...

First create the following table in the same database as the PhpDig tables:

CREATE TABLE blanks2 (
	blank TEXT DEFAULT '' NOT NULL
);

Then in search_function.php find:

    $timer->stop('reorder results');

And beforehand add the following:

	if (is_array($final_result) && count($final_result) > 0) {
		arsort($final_result);
		reset($final_result);
		while (list($spider_id,$weight) = each($final_result)) {
			$content_file = $relative_script_path.'/'.TEXT_CONTENT_PATH.$spider_id.'.txt';
			if (!is_file($content_file)) {
				mysql_query("INSERT INTO blanks2 (blank) VALUES ($spider_id)",$id_connect);
				if (isset($final_result[$spider_id])) { unset($final_result[$spider_id]); }
			}
		}
	}

Again do some searches, and then attach to this thread about ten rows from the blanks2 table so I can have a look-see. Note that searches might take longer.

Also note that this may seem like it removes the blanks, but it's a temporary thing. I'll need to see if any rows get inserted into blanks2 to put things back in sync.

[Dave A]

Hi Charter,
I have added the blank2 table and copied the code as you have mentioned into the searchfunction.php file
The results appear to be the same, after about thirty searches both the original blank table and the blank2 table are empty when I use phpmyadmin to have a look at them.
Some of the results are still coming up with blanks that are empty and I didn't notice any change in search speeds.
Perhaps this can help your diagnostics my friend??

All the best
Dave A downunder

[Charter]

Hmm, try the following...

First create the following table in the same database as the PhpDig tables:

CREATE TABLE blanks3 (
	blank TEXT DEFAULT '' NOT NULL
);

Then in search_function.php find:

             $query = "SELECT sites.site_url, sites.port, spider.path,spider.file,spider.first_words,sites.site_id,spider.spider_id,spider.last_modified,spider.md5 "
                      ."FROM ".PHPDIG_DB_PREFIX."spider AS spider, ".PHPDIG_DB_PREFIX."sites AS sites "
                      ."WHERE spider.spider_id=$spider_id AND sites.site_id = spider.site_id";
             $result = mysql_query($query,$id_connect);
             $content = mysql_fetch_array($result,MYSQL_ASSOC);

And afterwards add the following:

             $blank = addslashes(serialize($content));
             mysql_query("INSERT INTO blanks3 (blank) VALUES ('$blank')",$id_connect);

Now do one search (using the and option) on "Zealand Sustainability Database" (without the quotes) and then attach to this thread the content from the blanks3 table.

[Dave A]

Hi Charter I have just done that and when I completed the search, the blank3 table was empty.

[Charter]

Email me FTP access to the PhpDig directory, if you will.

[Dave A]

Hi Charter
just fired the access and password over to you.

[Charter]

Okay, the issue is that (a) the site_id column of the sites table contains IDs that are not in the site_id column of the spider table, (b) the site_id column of the spider table contains IDs that are not in the site_id column of the sites table, or (c) a combination of (a) and (b) so what I did was change the following query:

           $query = "SELECT spider.spider_id,sum(weight) as weight, spider.site_id
           FROM ".PHPDIG_DB_PREFIX."keywords as k,".PHPDIG_DB_PREFIX."engine as engine, ".PHPDIG_DB_PREFIX."spider as spider
           WHERE engine.key_id = k.key_id
           ".$kconds[$n]."
           AND engine.spider_id = spider.spider_id $wheresite $wherepath
           GROUP BY spider.spider_id,spider.site_id ";

To the following query in search_function.php:

           $query = "SELECT spider.spider_id,sum(weight) as weight, spider.site_id
           FROM ".PHPDIG_DB_PREFIX."keywords as k,".PHPDIG_DB_PREFIX."engine as engine, ".PHPDIG_DB_PREFIX."spider as spider,
		".PHPDIG_DB_PREFIX."sites as sites
           WHERE engine.key_id = k.key_id
           ".$kconds[$n]."
		AND sites.site_id = spider.site_id
           AND engine.spider_id = spider.spider_id $wheresite $wherepath
           GROUP BY spider.spider_id,spider.site_id ";

This seems to rid the search results of blanks, but you might want to look at the sites and spider tables to see what IDs are in one table but not the other table. I'm not sure why these tables got out of sync, maybe a dropped database connection, maybe a misdelete, who knows, haven't ever had it happen to me, but now that I know what is out of sync, I might add a new clean routine to a later release to take care of it more thoroughly.

[Dave A]

Hi Charter,
thanks for your advice and help with the problem, it appears to be okay now.

I went into the phpmyadmin and check the sites table which showed an error because of zero length in site. ID part of the table, so I then tried to repair the table which failed to work it out.
In the end I deleted all sit id's with a zero length and the sites table cleaned itself straight away.
So now it's up and running sweet as.
Thanks for your great help and assistance with this, I know it's not a given that you will sort out users problems but in this case I would really like say thanks!
The whole engine is working, I can now clean the index brilliant help.

Thanks for the support..

[Charter]

Glad it is now working. BTW, what do you get from the following query?

SELECT count(*),max(site_id) FROM sites;

The site_id column of the sites table is set as follows:

site_id mediumint(9) NOT NULL auto_increment

Maybe your blanks were because you are out of auto_increment numbers?

Hmm...