I have upgraded to the latest zikula 1.0.2 and created the new search API for a module of mine in a pnsearchapi.php file. When I tested this on my dev server everything went fine. However on my production server I am getting weird errors. Either blank pages or SQL errors such as.
Quote
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') FROM WHERE > '2008-11-06 16:00:06' AND > 0' at line 1
SELECT COUNT(DISTINCT ) FROM WHERE > '2008-11-06 16:00:06' AND > 0
This will only happen with certain search terms. You can test this out by going to
http://www.microbiol…x.php?module=Search
Try searching for bacteria, it works fine
Then do a search for atp, it does not work.
Here is the code for the search function from pnsearchapi.php in the Book Module.
Code
function book_searchapi_search($args)
{
pnModDBInfoLoad('Search');
$pntable = pnDBGetTables();
$artTable = &$pntable['book'];
$artColumns = &$pntable['book_articles'];
$searchTable = $pntable['search_result'];
$searchColumn = $pntable['search_result_column'];
$where = search_construct_where($args,
array($artColumns['title'],
$artColumns['contents']),
null);
$sessionId = session_id();
$sql = "SELECT
$artColumns[art_id] as id,
$artColumns[title] as title,
$artColumns[contents] as text,
$artColumns[chap_id] as chap_id,
$artColumns[book_id] as book_id
FROM $artTable
WHERE $where";
$result = DBUtil::executeSQL($sql);
if (!$result) {
return LogUtil::registerError (_GETFAILED);
}
$insertSql =
"INSERT INTO $searchTable
($searchColumn[title],
$searchColumn[text],
$searchColumn[extra],
$searchColumn[created],
$searchColumn[module],
$searchColumn[session])
VALUES ";
// Process the result set and insert into search result table
for (; !$result->EOF; $result->MoveNext()) {
$item = $result->GetRowAssoc(2);
$chapter = pnModAPIFunc('Book', 'user', 'getchapter', array ('chap_id' => $item['chap_id']));
$book = pnModAPIFunc('Book', 'user', 'get', array ('book_id' => $item['book_id']));
// Security check -
if (SecurityUtil::checkPermission('Book::Chapter', "$book[book_name]::$chapter[chap_name]", ACCESS_READ)) {
$sql = $insertSql . '('
. '\'' . DataUtil::formatForStore($item['title']) . '\', '
. '\'' . DataUtil::formatForStore($item['contents']) . '\', ' //really the whole contents?
. '\'' . DataUtil::formatForStore($item['id']) . '\', '
. '\'' . DataUtil::formatForStore(DateUtil::getDatetime()) . '\', ' //not sure what to put here
. '\'' . 'Book' . '\', '
. '\'' . DataUtil::formatForStore($sessionId) . '\')';
$insertResult = DBUtil::executeSQL($sql);
if (!$insertResult) {
return LogUtil::registerError (_GETFAILED);
}
}
}
return true;
}
{
pnModDBInfoLoad('Search');
$pntable = pnDBGetTables();
$artTable = &$pntable['book'];
$artColumns = &$pntable['book_articles'];
$searchTable = $pntable['search_result'];
$searchColumn = $pntable['search_result_column'];
$where = search_construct_where($args,
array($artColumns['title'],
$artColumns['contents']),
null);
$sessionId = session_id();
$sql = "SELECT
$artColumns[art_id] as id,
$artColumns[title] as title,
$artColumns[contents] as text,
$artColumns[chap_id] as chap_id,
$artColumns[book_id] as book_id
FROM $artTable
WHERE $where";
$result = DBUtil::executeSQL($sql);
if (!$result) {
return LogUtil::registerError (_GETFAILED);
}
$insertSql =
"INSERT INTO $searchTable
($searchColumn[title],
$searchColumn[text],
$searchColumn[extra],
$searchColumn[created],
$searchColumn[module],
$searchColumn[session])
VALUES ";
// Process the result set and insert into search result table
for (; !$result->EOF; $result->MoveNext()) {
$item = $result->GetRowAssoc(2);
$chapter = pnModAPIFunc('Book', 'user', 'getchapter', array ('chap_id' => $item['chap_id']));
$book = pnModAPIFunc('Book', 'user', 'get', array ('book_id' => $item['book_id']));
// Security check -
if (SecurityUtil::checkPermission('Book::Chapter', "$book[book_name]::$chapter[chap_name]", ACCESS_READ)) {
$sql = $insertSql . '('
. '\'' . DataUtil::formatForStore($item['title']) . '\', '
. '\'' . DataUtil::formatForStore($item['contents']) . '\', ' //really the whole contents?
. '\'' . DataUtil::formatForStore($item['id']) . '\', '
. '\'' . DataUtil::formatForStore(DateUtil::getDatetime()) . '\', ' //not sure what to put here
. '\'' . 'Book' . '\', '
. '\'' . DataUtil::formatForStore($sessionId) . '\')';
$insertResult = DBUtil::executeSQL($sql);
if (!$insertResult) {
return LogUtil::registerError (_GETFAILED);
}
}
}
return true;
}
Any and all help is appreciated.
Tim
