0

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 &#039;is_logout&#039; doesn&#039;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()&#039;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.

  • 1
    This is just a word of advice. The PHP module is evil. There's even (still) talk about removing it from core. I'd definitely suggest spend a few minutes learning about the block hooks and putting this in a module instead. – Chapabu Aug 5 '13 at 9:50
  • I missed that part of the question. @Chapabu is right. – Letharion Aug 5 '13 at 9:52
0

I could be wrong, but looking at the database code, there is not manual locking going on, so most likely you have a contrib module that causes the above error.

Regardless, you'll need to debug the lock_acquire function to find out which lock is being attempted, and work back from there to understand why the lock is needed.

  • Hi, instead of putting it in the block, I transfer it on my theme under html.tpl.php with the same code. Is what I am doing correct? – jaypabs Aug 5 '13 at 9:51
  • Is the code/functionality related to your theme? – Letharion Aug 5 '13 at 9:53
  • @jaypabs There shouldn't be any logic in XYZ.tpl.php files, this should be reserved for modules and preprocess functions :) – Chapabu Aug 5 '13 at 9:54
  • @Letharion the code will simple record the URL accessed. – jaypabs Aug 5 '13 at 9:55
  • 1
    @jaypabs Yes. Templates should only be used for HTML output, anything that requires any logic should be dealt with in a preprocess function in either template.php or a custom module. From the looks of it, you would be better off with a custom module. I'd suggest breaking down what you are actually trying to do (without all the DB code) and posting that as a question. E.g. "I'm trying to track page views, how would I go about it." – Chapabu Aug 5 '13 at 10:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.