I have created a Block with PHP as text format.
What I want is to insert record into separate database using db_insert function.
Here's my code:
<?php
$tracker = array(
'database' => 'tracker',
'username' => 'tracker',
'password' => 'PASSWORD',
'host' => 'localhost',
'driver' => 'mysql',
);
Database::addConnectionInfo('tracker', 'default', $tracker);
db_set_active('tracker');
// This code requires the code found in "Record Visit Pre-requisite in Block". This will compute the total page load.
$time = microtime();
$time = explode(' ', $time);
$time = $time[1] + $time[0];
$finish = $time;
$total_time = round(($finish - $start), 4);
$t = $total_time;
// $url = $_GET['url'];
$url = "http://$_SERVER[HTTP_HOST]$_SERVER[REQUEST_URI]";
$current_ip = $_SERVER['REMOTE_ADDR'];
$user_agent = $_SERVER['HTTP_USER_AGENT'];
$date = date("Y-m-d");
$time = date("h:i:s");
function is_bot() {
$botlist = array("Teoma", "alexa", "froogle", "Gigabot", "inktomi",
"looksmart", "URL_Spider_SQL", "Firefly", "NationalDirectory",
"Ask Jeeves", "TECNOSEEK", "InfoSeek", "WebFindBot", "girafabot",
"crawler", "www.galaxy.com", "Googlebot", "Scooter", "Slurp",
"msnbot", "appie", "FAST", "WebBug", "Spade", "ZyBorg", "rabaz",
"Baiduspider", "Feedfetcher-Google", "TechnoratiSnoop", "Rankivabot",
"Mediapartners-Google", "Sogou web spider", "WebAlta Crawler","TweetmemeBot",
"Butterfly","Twitturls","Me.dium","Twiceler");
foreach($botlist as $bot) {
if(strpos($_SERVER['HTTP_USER_AGENT'], $bot) !== false)
return true;
}
return false;
}
if (is_bot())
$isbot = 1;
}
else {
$isbot = 0;
}
db_insert('ap_visits')
->fields(array(
'id' => NULL,
'length' => $t,
'url' => '$url',
'current_ip' => '$current_ip',
'user_agent' => '$user_agent',
'date_start' => date("Y-m-d H:i:s"),
'is_bot' => $isbot,
))
->execute();
db_set_active();
?>
But this returns the following error:
PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42S02]: Base table or view not found: 1146 Table 'c1tracker.semaphore' doesn't exist' in /var/www/clients/client1/web2/web/includes/database/database.inc:2168
Stack trace:
#0 /var/www/clients/client1/web2/web/includes/database/database.inc(2168): PDOStatement->execute(Array)
#1 /var/www/clients/client1/web2/web/includes/database/database.inc(680): DatabaseStatementBase->execute(Array, Array)
#2 /var/www/clients/client1/web2/web/includes/database/database.inc(2347): DatabaseConnection->query('SELECT expire, ...', Array, Array)
#3 /var/www/clients/client1/web2/web/includes/lock.inc(167): db_query('SELECT expire, ...', Array)
#4 /var/www/clients/client1/web2/web/includes/lock.inc(146): lock_may_be_available('schema:runtime:...')
#5 /var/www/clients/client1/web2/web/includes/bootstrap.inc(420): lock_acquire('schema:runtime:...')
#6 /var/www/clients/client1/web2/web/includes/bootstrap.inc(442): DrupalCacheArray->set(Array)
#7 [internal function]: DrupalCache in /var/www/clients/client1/web2/web/includes/database/database.inc on line 2168
PDOException: SQLSTATE[HY000]: General error: 1364 Field 'is_logout' doesn't have a default value: INSERT INTO {ap_visits} (id, length, url, current_ip, user_agent, date_start, is_bot) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4, :db_insert_placeholder_5, :db_insert_placeholder_6); Array
(
[:db_insert_placeholder_0] =>
[:db_insert_placeholder_1] => 1375693838.8169
[:db_insert_placeholder_2] => $url
[:db_insert_placeholder_3] => $current_ip
[:db_insert_placeholder_4] => $user_agent
[:db_insert_placeholder_5] => 2013-08-05 17:10:38
[:db_insert_placeholder_6] => 0
)
in eval() (line 61 of /var/www/clients/client1/web2/web/modules/php/php.module(80) : eval()'d code).
It seems that the code is trying to access 'semaphore' table but it is using the external database that I have called 'c1tracker'. Please refer to line # 1 above. It says:
SQLSTATE[42S02]: Base table or view not found: 1146 Table 'c1tracker.semaphore' doesn't exist' in /var/www/clients/client1/web2/web/includes/database/database.inc:216
As you can see I have already switch to the external database as follows:
Database::addConnectionInfo('tracker', 'default', $tracker);
db_set_active('tracker');
Anyone know how to fix this?
BTW, I'm using drupal 7.