=======================================
PHP MySQL
=======================================

Some quick reference templates.


=======================================
connect db
---------------------------------------

define('MAIN_SERVER',   'localhost');
define('MAIN_USER',     'user');
define('MAIN_PASSWORD', 'pass');
define('MAIN_DB',       'mydb');
$mysqli = new mysqli(MAIN_SERVER, MAIN_USER, MAIN_PASSWORD, MAIN_DB);
if($mysqli->connect_errno) {
  error_log($mysqli->connect_errno.' : '.$mysqli->connect_error.' : '.__FILE__.' : '.__LINE__);
}


=======================================
SELECT: template for general queries
---------------------------------------

$sql = 'SELECT * FROM user WHERE state = ?';
$stmt = $mysqli->prepare($sql);
if($stmt) {
  $stmt->bind_param('i', $state);
  if($stmt->execute()) {
    $result = $stmt->get_result();
    if($result->num_rows > 0) {
      while($row = $result->fetch_assoc()) {
        // save to array
        $user[] = $row;
      }
    } else {
      // no results
    }
  } else {
    // statement fail
    error_log($stmt->errno.' : '.$stmt->error.' : '.__FILE__.' : '.__LINE__);
  }
  $stmt->close();
} else {
  // system fail
  error_log($mysqli->errno.' : '.$mysqli->error.' : '.__FILE__.' : '.__LINE__);
}


=======================================
SELECT: template for static queries
---------------------------------------

$sql = 'SELECT * FROM user WHERE state = 0';
$stmt = $mysqli->query($sql);
if($stmt) {
  if($stmt->num_rows > 0) {
    while($row = $stmt->fetch_assoc()) {
      // save to array
      $user[] = $row;
    }
  } else {
    // no results
  }
  $stmt->close();
} else {
  // system fail
  error_log($mysqli->errno.' : '.$mysqli->error.' : '.__FILE__.' : '.__LINE__);
}


=======================================
SELECT: left join
---------------------------------------

$sql = '
  SELECT
    user.*, user.id AS user_id,
    plan.*, plan.id AS plan_id,
    site.*, site.id AS site_id
  FROM site
  LEFT JOIN plan ON plan.id = site.plan_id AND plan.state = 0
  LEFT JOIN user ON user.id = site.user_id AND user.state = 0
  WHERE site.cost > ? AND site.state = 0
';
$stmt = $mysqli->prepare($sql);
if($stmt) {
  $stmt->bind_param('d', $site_cost);
  if($stmt->execute()) {
    $result = $stmt->get_result();
    if($result->num_rows > 0) {
      while($row = $result->fetch_assoc()) {
        // save to array
        $work[] = $row;
      }
    } else {
      // no results
    }
  } else {
    // statement fail
    error_log($stmt->errno.' : '.$stmt->error.' : '.__FILE__.' : '.__LINE__);
  }
  $stmt->close();
} else {
  // system fail
  error_log($mysqli->errno.' : '.$mysqli->error.' : '.__FILE__.' : '.__LINE__);
}


=======================================
SELECT: bind_result() + fetch() is inconvenient, burdensome, unnecessary, and EVIL
---------------------------------------

###########################  AVOID USING bind_result() + fetch()  ###########################

$sql = 'SELECT                   id,             first,            last FROM user WHERE state = ?';
$stmt = $mysqli->prepare($sql);
if($stmt) {
  $stmt->bind_param('i', $state);
  if($stmt->execute()) {
    $stmt->store_result();
    if($stmt->num_rows > 0) {
      $stmt->bind_result(       $id,            $first,           $last);
      while($stmt->fetch()) {
        // save to array
        $user[] = array('id' => $id, 'first' => $first, 'last' => $last);
      }
    } else {
      // no results
    }
  } else {
    // statement fail
    error_log($stmt->errno.' : '.$stmt->error.' : '.__FILE__.' : '.__LINE__);
  }
  $stmt->close();
} else {
  // system fail
  error_log($mysqli->errno.' : '.$mysqli->error.' : '.__FILE__.' : '.__LINE__);
}


=======================================
INSERT
---------------------------------------

$sql = 'INSERT INTO user (created_ns, first, last, email) VALUES (?, ?, ?, ?)';
$stmt = $mysqli->prepare($sql);
if($stmt) {
  $stmt->bind_param('dsss', $now, $first, $last, $email);
  if($stmt->execute()) {
    if(empty($stmt->insert_id)) {
      // no insert
    }
  } else {
    // statement fail
    error_log($stmt->errno.' : '.$stmt->error.' : '.__FILE__.' : '.__LINE__);
  }
  $stmt->close();
} else {
  // system fail
  error_log($mysqli->errno.' : '.$mysqli->error.' : '.__FILE__.' : '.__LINE__);
}


=======================================
INSERT: no duplicate
---------------------------------------

$sql = '
  INSERT INTO user (created_ns, first, last, email)
  SELECT * FROM (
    SELECT ? AS created_ns, ? AS first, ? AS last, ? AS email
  ) AS tmp
  WHERE NOT EXISTS (SELECT email FROM user WHERE email LIKE ? AND state = 0 LIMIT 1)
  LIMIT 1
';
$stmt = $mysqli->prepare($sql);
if($stmt) {
  $stmt->bind_param('dssss', $now, $first, $last, $email, $email);
  if($stmt->execute()) {
    if(empty($stmt->insert_id)) {
      // no insert
    }
  } else {
    // statement fail
    error_log($stmt->errno.' : '.$stmt->error.' : '.__FILE__.' : '.__LINE__);
  }
  $stmt->close();
} else {
  // system fail
  error_log($mysqli->errno.' : '.$mysqli->error.' : '.__FILE__.' : '.__LINE__);
}


=======================================
UPDATE
---------------------------------------

$sql = 'UPDATE user SET updated_ns = ?, first = ?, last = ?, email = ? WHERE id = ? AND state = 0';
$stmt = $mysqli->prepare($sql);
if($stmt) {
  $stmt->bind_param('dsssi', $now, $first, $last, $email, $user_id);
  if($stmt->execute()) {
    if($stmt->affected_rows == 0) {
      // no update
    }
  } else {
    // statement fail
    error_log($stmt->errno.' : '.$stmt->error.' : '.__FILE__.' : '.__LINE__);
  }
  $stmt->close();
} else {
  // system fail
  error_log($mysqli->errno.' : '.$mysqli->error.' : '.__FILE__.' : '.__LINE__);
}


=======================================
:0)
=======================================