Wednesday, January 30, 2013

PDO MySQL

    try {
      $this->_handle = new PDO('mysql:host=localhost;dbname='.$db, self::$username, self::$password);
      $this->_handle->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
      $this->_handle->query('SET NAMES UTF8');
    } catch(Exception $e) {
      echo $e->getMessage();
    }
set error mode another way
$dbh = new PDO($dsn, $user, $password, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_WARNING));
set 'set names utf8' another way:
  $db = new PDO('mysql:host=localhost;dbname=something', 'username', 'password', array(
      PDO::ATTR_ERRMODE => PDO::ERRMODE_WARNING,
      PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8")
  );
closing the connection, if you want, not needed
$dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);
// use the connection here

// and now we're done; close it
$dbh = null;
prepared statement, binding with bindParam
    $stmt = $this->db->prepare('SELECT * FROM '.$this->table.' WHERE '.$this->primary_key.' = :id AND domain IN '.$domains);
    $stmt->bindParam(':id', $id);
    $stmt->execute();
    $row = $stmt->fetchAll(PDO::FETCH_ASSOC);

    if(count($row) == 1) {
binding with execute:
            $pre = $this->db->prepare('INSERT INTO '.$this->table.' ('.implode(', ', array_keys($fields)).') VALUES ('.$question_marks.')');
            $pre->execute(array_values($fields));
key-pair fetch:
$items = $this->db->query('SELECT '.$this->primary_key.', '.$this->name_field.' FROM '.$this->table.' WHERE domain IN '.$domains.' ORDER BY '.$this->name_field)->fetchAll(PDO::FETCH_KEY_PAIR);
fetch with query and fetch:
  $packages = $db->query('SELECT * FROM package_translation');
  foreach($packages as $package) {
    echo $package['name'].'
'; } $stmt = $db->query('SELECT * FROM package_translation'); $result = $stmt->setFetchMode(PDO::FETCH_NUM); while ($row = $stmt->fetch()) { print $row[0] . "\t" . $row[1] . "\t" . $row[2] . "\n"; }
affected rows (insert, update, delete):
/* Delete all rows from the FRUIT table */
$del = $dbh->prepare('DELETE FROM fruit');
$del->execute();

/* Return number of rows that were deleted */
print("Return number of rows that were deleted:\n");
$count = $del->rowCount();
print("Deleted $count rows.\n");
count rows:
  $sql = "SELECT COUNT(*) FROM package_translation";
  if ($res = $db->query($sql)) {
  
      /* Check the number of rows that match the SELECT statement */
    if ($res->fetchColumn() > 0) {
  
          /* Issue the real SELECT statement and work with the results */
           $sql = "SELECT * FROM package_translation";
         foreach ($db->query($sql) as $row) {
             print "Name: " .  $row['name'] . "\n";
           }
      }
      /* No rows matched -- do something else */
    else {
        print "No rows matched the query.";
      }
  }
  
  $res = null;
  $conn = null;

SPL Autoloader

class Autoloader {

  public static function autoload($classname) {

    if(file_exists('../lib/'.$classname . '.class.php')) {
      include_once($classname . '.class.php');
    } else {
      include_once('../lib/model/'.$classname . '.class.php');
    }

    if (!class_exists($classname)) {
      throw new MyException("Class '$classname' not found", 7);
    }

  }

}

spl_autoload_register(array('Autoloader', 'autoload'));

Thursday, January 17, 2013

Copy MySQL Database to Another Database

mysqldump -h [server] -u [user] -p[password] db1 | mysql -h [server] -u [user] -p[password] db2