Zoom Search Engine FAQ - Indexing databases

Q. How can I use Zoom to index my site stored in a database (eg. SQL, Access, etc.)?

In most cases, all you need to index a database driven website, is to select "Spider mode" in Zoom Indexer, point it at your website, and click "Start indexing". This is because spider mode will download and scan each webpage as if it was a typical human visitor, browsing your website one page at a time.

Provided that all your database content is made accessible via web pages (i.e. there is a URL to represent each record in your database), Zoom will successfully index the resultant pages (as displayed to your users), regardless of the database backend you are using (MySQL, Postgres, MS Access, Oracle, etc.).

However, if there are portions of your database which can not be accessed via your existing webpages and you want this data indexed, then you need to make this data accessible to a browser (i.e. via a URL) before Zoom can be used to index this content. Normally, this would involve the creation of a script which connects to the database and display the content in HTML. Since every database has a different structure (tables, key fields, field types, etc.), a custom script is usually required.

You may also need to create a list of URLs to refer to these records in your database. For example, you may have a database of cooking recipe. Each of these recipes are a record in the database, and are accessed via a PHP webpage which displays the recipe selected such as the following:

http://mysite.com/recipe.php?id=12
http://mysite.com/recipe.php?name=Pavalova http://mysite.com/recipe.php?name=Guacamole
...
etc.

Here you would need to produce a list of all such URLs, and either put them in a HTML page as hypertext links, and allow the spider to crawl this page, or alternatively, add each of these URLs as "additional start points" by clicking on the "More" button in the indexer.

You can also create a dynamically generated list (e.g. "List all recipes"), which will simply go through your database by the appropriate record and field names, and produce a list of valid HTML links such as the one above, to all the records of your site. Make this index accessible from your website, and you will be able to index the SQL portions of your site.

This latter option also has the benefit of increasing the accessibility of your website to Internet-wide search engine spiders such as Google, thereby increasing the prominence of your site on the web.

Example script to allow indexing of database records

If you have determined that the database data you wish to index is not currently accessible from any existing dynamically generated webpage (as described above), you should consider creating server-side scripts for the purpose of allowing Zoom to index them. These can be very simple scripts, and we have provided some examples below.

While this obviously requires familiarity with a server-side scripting language and an understanding of SQL queries and your database structure, these skill requirements are necessary and unavoidable when implementing a meaningful search function for any database. If you are not familiar with the backend of your site, you may want to consult the original developer or the database administrator.

The following is an example PHP script which makes a MySQL database accessible for a spider indexer. The same principles apply for other server-side scripting languages and databases. Note that every database would have different table names, fields and data types, so you will need to adapt this to your own situation for use.

<?php
$TABLENAME = "Books";

$db_connect = mysql_connect("mydbserver", "mylogin", "mypassword");
if ($db_connect == FALSE)
  die("Could not connect to database server");

$mysql_query = "SELECT * FROM $TABLENAME";
$mysql_ret = mysql_query($mysql_query, $db_connect);
if ($mysql_ret != FALSE)
{
  $count = 1;
  while ($res = mysql_fetch_object($mysql_ret) )
  {
    print("<a href=\"details.php?ID=$res->BookID\">$count</a><br>");
    $count++;
  }
}
?>

The above is "listing.php" which connects to a MySQL server named "mydbserver" and creates hypertext links to a "details.php" script, with the "BookID" (primary key for the record in the "Books" table) as a parameter.

The following is "details.php" which will take this ID parameter and retrieve the corresponding data record from the database and display all the fields on the page.

<?php
$TABLENAME = "Books";

$db_connect = mysql_connect("mydbserver", "mylogin", "mypassword");
if ($db_connect == FALSE)
  die("Could not connect to database server");

$ID = "";
if (isset($_GET['ID']))
  $ID = $_GET['ID'];

$mysql_query = "SELECT * FROM $TABLENAME WHERE BookID='$ID'";
$mysql_ret = mysql_query($mysql_query, $db_connect);
if ($res = mysql_fetch_object($mysql_ret))
{
  print("Book ID: $res->BookID<br>");
  print("Author: $res->Author<br>");
  print("ISBN: $res->ISBN<br>");
  print("Title: $res->Title<br>");
}
?>

This page simply displays the fields from the database record. You should change your SQL query accordingly if your data is spanned across multiple tables and require joining by key fields.

Once you have these scripts up and running (and hosted on your web server), you should be able to test them by simply accessing the "listing.php" page from your web browser, and clicking on each link. This should display the data for each record.

You can then index the data by providing the URL to the live "listing.php" page as the Start Spider URL in Zoom. If you have other pages to index besides this data, you may want to add it as an additional start point instead, by clicking on "More" and selecting "Add".

If you wish to redirect these indexed pages (so that the search results do not point at "details.php"), you could utilize the "Rewrite links" feature (on the "Indexing Options" tab of the Configuration window).

Return to the Zoom Search Engine Support page