php mysql

mysqldemo.md

php set mysql demo

• process-oriented model

$host = 'localhost';
$user = 'root';
$pass = '';
$data_base = 'test';

mysql_connect($host, $user, $pass);
mysql_select_db($data_base);

if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
  	$data = array(
		'status' => 100,
		'desc' => 'wrong method'
	);
	echo json_encode($data);
  	exit();
}
if (!array_key_exists('action', $_POST)) {
	$data = array(
		'status' => 100,
		'desc' => 'none action'
	);
	echo json_encode($data);
	exit();
}
foreach ($_POST as $key => $value) {
	$$key = $value;
}
switch ($action) {
  case 'hecheng':
    mysql_query("INSERT INTO `testtable`
			(
				`create_time`,
				`owner_id`,
			) VALUES (
				NOW(),
				$owner_id
			)");
		if (mysql_affected_rows()) {
			$insert_id = mysql_insert_id();
			$data = array(
				'status'=>200,
				'desc'=>'success',
				'data'=> array(
					'id'=>$insert_id
				)
			);
			echo json_encode($data);
		} else {
			$data = array(
				'status'=>100,
				'desc'=>'操作失败'
			);
			echo json_encode($data);
		}
		echo json_encode($data);
    break;
}

/**
 * 字符串参数安全转义
 *
 * param {string} $text 字符串参数
 */
function safeParam($text) {
	return preg_replace('/([\\\\\'])/', '\\\\$1', $text);
}

• object-oriented model

$host = 'localhost';
$user = 'root';
$pass = '';
$data_base = 'test';

$mysqli = new mysqli($host, $user, $pass, $data_base);
if (mysqli_connect_errno()) {
	printf("Connect failed: %s\n", mysqli_connect_error());
	exit();
}

// insert 
$stmt = $mysqli->prepare(
			"
			INSERT INTO `testtable`
				(
					`create_time`,
					`owner_id`,
					`nickname`
				)
				VALUES
				(
					NOW(),
					?,
					?
				)
			");
		$stmt->bind_param('sssii', intval($owner_id), $nickname);
		$stmt->execute();
	  $meta = $stmt->result_metadata();
    $insert_id = 'wrong';
    if (!$meta) {
      $insert_id = $stmt->insert_id;
    }
    $data = array(
			'status'=>200,
			'desc'=>'success',
			'data'=> array(
				'id'=>$insert_id
			)
		);
		echo json_encode($data);
		$stmt->close();
		
	// query
	
	if ($stmt = $mysqli->prepare("SELECT * FROM `testtable` WHERE id=$id")) {
	    $stmt->execute();

	    $meta = $stmt->result_metadata();
	    while ($field = $meta->fetch_field())
	    {
	        $params[] = &$row[$field->name];
	    }

	    call_user_func_array(array($stmt, 'bind_result'), $params);

	    while ($stmt->fetch()) {
	        foreach($row as $key => $val)
	        {
	            $c[$key] = $val;
	        }
	        $result[] = $c;
	    }
	    //print_r($result);
	    $stmt->close();
		}

		echo json_encode($result);