Feeds a database with the content of a bibtex file parsed with bibtexbrowser

feeding-mysql-database-with-bibtexbrowser.php

<?php 
/* Feeds a database with the content of a bibtex file parsed with bibtexbrowser
 * See: http://www.monperrus.net/martin/feeding-mysql-database-with-bibtexbrowser
 * Author: Martin Monperrus, Xiaodong Qi
 * Last Modification Date: Oct 2015
 * Creation Date: Feb 2012
 */

// if exists, should contain define('DB_PASSWORD', 'dssizyrekzbqsf');, etc.
@include('conf.local.php');

/** MySQL database username */
@define('DB_USER', 'root');

/** MySQL database password */
@define('DB_PASSWORD', 'dssizyrekzbqsf');

/** MySQL hostname */
@define('DB_HOST', 'localhost');

/** The name of the database */
@define('DB_NAME', 'bibliography');

/** The name of the table */
@define('BIBTEX_TABLE', 'bibliography');


/** returns a BibDatabase object created from the content of $bibtex_file */
function init_bibtexbrowser($bibtex_file) {
  $_GET['bib'] = $bibtex_file;
  $_GET['library'] = 1;
  include('bibtexbrowser.php');
  setDB();
  $database = $_GET[Q_DB]; 
  return $database;
}

/** returns the list of fields used in the BibDatabase object $bibdb */
function get_field_list($bibdb) {
  $entries = $bibdb->bibdb;
  $result = array();
  foreach($entries as $entry) {
    foreach($entry->getFields() as $k => $v) {
      @$result[$k]++;
    }
  }
  return array_keys($result);
}

/** converts a Bibtex field name into a valid MySQL column name */
function convert_column_name($field) {
  return str_replace('-','_',$field);
}

/** sets the schema of the mysql DB based on $field_list and BIBTEX_TABLE */
function init_db($field_list) {
  $mysqli = new mysqli(DB_HOST, DB_USER , DB_PASSWORD, DB_NAME);
  if (mysqli_connect_errno()) {
      printf("Could not connect: %s\n", mysqli_connect_error());
      exit();
  }
  
  // introspection
  $query = 'show tables;';
  $result = $mysqli->query($query) or die('Query failed: ' . mysqli_error($mysqli).' '.$query);
  $found = false;
  while ($line = mysqli_fetch_row($result)) { 
    if ($line[0] === BIBTEX_TABLE) {
      $found = true;
    }
  }
  
  // we create the table if it does not exist
  if (!$found) {
    $query = 'CREATE TABLE '.BIBTEX_TABLE.' (bibtexkey VARCHAR(255), PRIMARY KEY (bibtexkey)) ENGINE = MyISAM DEFAULT CHARSET=UTF8;';
    $result = $mysqli->query($query) or die('Query failed: ' . mysqli_error($mysqli).' '.$query);
  }

  // introspection 2
  $query = 'show columns from '.BIBTEX_TABLE.';';
  $result = $mysqli->query($query) or die('Query failed: ' . mysqli_error($mysqli).' '.$query);
  $columns = array();
  while ($line = mysqli_fetch_row($result)) { 
    $columns[] = $line[0];
  }
  
  // altering table to add missing columns
  foreach($field_list as $rfield) {
    // some fields require special naming
    $field = convert_column_name($rfield);
    if (!in_array($field,$columns) && strtolower($field)!='key') {
       // altering the table
       $query = 'alter table '.BIBTEX_TABLE.' add `'.$field.'` TEXT NULL;';   
       $result = $mysqli->query($query) or die('Query failed: ' . mysqli_error($mysqli).' '.$query);    
    }
  }
}

/** adds escape and quotes around an HTML string; the string is also converted to UTF-8 */
function create_mysql_string_from_bibtexbrowser_value($f) {
  return "'".mysqli_real_escape_string(dbconnect(),html_entity_decode($f,ENT_NOQUOTES,'UTF-8'))."'";
}

/** connected function. */
function dbconnect(){ 
  $hostname_PrintData = DB_HOST;
  $database_PrintData = DB_NAME;
  $username_PrintData = DB_USER;
  $password_PrintData = DB_PASSWORD;
  $PrintData = mysqli_connect($hostname_PrintData, $username_PrintData, $password_PrintData, $database_PrintData) or trigger_error(mysqli_error(),E_USER_ERROR); 
  return $PrintData;
}

/** feeds a MySQL database using the content of the BibDatabase object $bibdb.
 *
 * The MySQL schema is usually created using function init_db
 */
function feed_database($bibtex_db) {

  $mysqli = new mysqli(DB_HOST, DB_USER , DB_PASSWORD, DB_NAME);
  if (mysqli_connect_errno()) {
      printf("Could not connect: %s\n", mysqli_connect_error());
      exit();
  }

  //print_r($bibtex_db->bibdb);
  
  foreach($bibtex_db->bibdb as $key=>$entry) {
    // do we have an entry ?
    $query = 'select * from '.BIBTEX_TABLE.' where bibtexkey=\''.$entry->getKey().'\';';
    //echo $query;   
    $result = $mysqli->query($query) or die('Query failed: ' . mysqli_error($mysqli).' '.$query);    
    //print_r($result);
    //if (mysqli_fetch_assoc($result) !==FALSE) {
    if (mysqli_num_rows($result) > 0) {
      // updating the entry
      $fields = $entry->fields;
      $updates = array ();
      foreach ($fields as $k=>$v) { 
        if ($k!='key') {
          $column = convert_column_name($k);
          $updates[] = $column.'='.create_mysql_string_from_bibtexbrowser_value($v);
        }
      }
      echo 'updating '.$entry->getKey().'...';
      $query = "update ".BIBTEX_TABLE." set ".implode(",",$updates)." where bibtexkey='".$entry->getKey()."';";
      //echo $query;      
      $result = mysqli_query($mysqli,$query);
      if( $result ) { 
          echo "Record has been updated successfully;<br/>";
      } else {
            die("Query failed:".$mysqli->error.$query."<br>"); 
      } //or die('Query failed: ' . $mysqli->error.' '.$query);  
      //$result->close();
    } else {
      // no such key
      $fields = $entry->fields;
      $keys = array ();
      foreach (array_keys($fields) as $f) { 
        if ($f!='key') {$keys[] = convert_column_name($f);}
        else {$keys[] = 'bibtexkey';}
      }

      $values = array ();
      foreach (array_values($fields) as $f) { 
        $values[] = create_mysql_string_from_bibtexbrowser_value($f);
      }
      
      echo 'adding '.$entry->getKey().'<br/>';
      $query = 'insert into  '.BIBTEX_TABLE.'('.implode(',',$keys).')       values ('.implode(',',$values).');';
      //echo $query;   
      $result = $mysqli->query($query) or die('Query failed: ' . mysqli_error($mysqli).' '.$query);    
      //$result->close();
    }
  
  } // end foreach
  mysqli_close($mysqli);
} // end function 



$bibtex_db = init_bibtexbrowser('/var/www/bibtexbrowser/test/input/all.bib');
$field_list = get_field_list($bibtex_db);
init_db($field_list);
feed_database($bibtex_db);

?>

I have converted mysql_* functions to mysqli_* functions, because the MySQL_* functions have been depreciated since MySQL v5.5.0. However, this code (maybe even the original one?) seems not to be able to create any data into the MySQL database... Did I overlooked anything?

Replaced line 134, now it works from my preliminary test!