老司机求带:php中的数据库操作。

超戈戈 发布于 2016/04/04 14:22
阅读 422
收藏 1
PHP

MySQL连接为什么挂死了?别踩坑!>>>

小弟刚自学了html基础和php语言基础,照着视频教程敲了以下代码,但是完全云里雾里啊。有木有好心的大哥能给解析一下。

//这个能理解,连接数据库

function connect()
{
    $link = mysql_connect(DB_HOST, DB_USER, DB_PWD) or die("数据库连接失败Error" . mysql_errno() . ":" . mysql_error());
    mysql_set_charset(DB_CHARSET);
    mysql_select_db(DB_DBNAME) or die("制定数据库打开失败");
    return $link;
}


//insert
function insert($table, $array)
{
    $key = join(",", array_keys($array));
    $vals = "'" . join("','", array_values($array)) . "";
    $sql = "insert {$table}($key) values({$vals})";
    mysql_query($sql);
    return mysql_insert_id();
}


//update imooc_admin set username='dave' where id=1


function update($table, $array, $where = null)
{
    foreach ($array as $key => $val) {
        if ($str == null) {
            $sep = "";
        } else {
            $sep = ",";
        }


    }
    $str .= $sep . $key . "='" . $val . "'";
    $sql = "update {$table} set {$str}" . ($where == null ? null : "where" . $where);
    mysql_query($sql);
    return mysql_affected_rows();
}


function delete($table, $where)
{
    $where = $where == null ? null : "where" . $where;
    $sql = "delete from {$table} {$where}";
    mysql_query($sql);
    return mysql_affected_rows();
}


function fetchOne($sql, $result_type = MYSQL_ASSOC)
{
    $result = mysql_query($sql);
    $row = mysql_fetch_array($result, $result_type);
    return $row;
}


function fetchAll($sql, $result_type = MYSQL_ASSOC)
{
    $result = mysql_query($sql);
    while (@$row = mysql_fetch_array($result, $result_type)) {
        $rows[] = $row;
    }
    return $rows;
}


function getResultNum($sql)
{
    $result = mysql_query($sql);
    return mysql_num_rows($result);
}

加载中
0
eechen
eechen
操作MySQL官方已经不建议使用mysql这个扩展了,这个扩展在PHP7中已经被移除了,请使用mysqli或者pdo_mysql进行替代,比如下面使用mysqli扩展操作MySQL实现CRUD增查改删(在PHP5.4和PHP7上测试通过):
<?php
function db() {
	global $app;
	static $db; //1个请求内多个函数共用1个连接.
	if ($db) {
		return $db;
	} else {
		$db = @new mysqli(
			$app['db_host'], 
			$app['db_user'], 
			$app['db_pass'], 
			$app['db_name'], 
			$app['db_port']
		);
	}
	if ($db->connect_errno) {
		echo $db->connect_error;
		exit();
	}
	$db->set_charset('utf8');
	return $db;
}

function insert($title = '', $content = '') {
	global $app;
	$db = db();
	$stmt = $db->prepare('INSERT INTO posts (post_title, post_content) VALUES(?, ?)');
	$stmt->bind_param('ss', $title, $content);
	$stmt->execute();
	return ($stmt->affected_rows !== 0) ? 
		array(true,  'insert_id' => $stmt->insert_id) : 
		array(false, 'insert_id' => $stmt->insert_id);
}

function select($id = '') {
	global $app;
	$db = db();
	if (!empty($id)) {
		return $db->query('SELECT * FROM posts WHERE id = '.intval($id))->fetch_all(MYSQLI_ASSOC);
	} else {
		return $db->query('SELECT * FROM posts')->fetch_all(MYSQLI_ASSOC);
	}
}

function select_v2($id = '') {
	global $app;
	$db = db();
	if (!empty($id)) {
		$stmt = $db->prepare('SELECT * FROM posts WHERE id = ?');
		$stmt->bind_param('i', $id);
	} else {
		$stmt = $db->prepare('SELECT * FROM posts');
	}
	$stmt->execute();
	//get_result和fetch_all需要mysqlnd支持,PHP从5.4开始内置mysqlnd.
	return $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
}

function update($id, $title = '', $content = '') {
	global $app;
	$db = db();
	$db->autocommit(false);
	//$db->begin_transaction(); //PHP从5.5开始才有这个函数,之前版本用autocommit即可.
	$stmt = $db->prepare('UPDATE posts SET post_title = ?, post_content = ? WHERE id = ?');
	$stmt->bind_param('ssi', $title, $content, $id);
	$stmt->execute();
	$db->commit();
	//UPDATE时,如果更新的内容跟原来的内容一样,affected_rows也会返回0.
	return ($stmt->affected_rows !== 0) ? true : false;
}

function delete($id) {
	global $app;
	$db = db();
	$db->query('DELETE FROM posts WHERE id = '.intval($id));
	return ($db->affected_rows !== 0) ? true : false;
}

function delete_v2($id) {
	global $app;
	$db = db();
	$stmt = $db->prepare('DELETE FROM posts WHERE id = ?');
	$stmt->bind_param('i', $id);
	$stmt->execute();
	return ($stmt->affected_rows !== 0) ? true : false;
}

header('Content-Type: text/plain; charset=utf-8');

$app = array(
	'db_host' => '127.0.0.1',
	'db_user' => 'root',
	'db_pass' => 'xxxx',
	'db_name' => 'test',
	'db_port' => 3306
);

$table = "CREATE TABLE IF NOT EXISTS posts (
	id           int(10) unsigned NOT NULL AUTO_INCREMENT,
	post_title   varchar(255)     NOT NULL DEFAULT '',
	post_content text             NOT NULL DEFAULT '',
	PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;";

//db()->query('DROP TABLE IF EXISTS posts;') or exit();
db()->query($table) or exit();

echo "var_export(insert('标题1', '内容1'));\n";
var_export(insert('标题1', '内容1'));
echo "\n\n";

echo "var_export(insert('标题2', '内容2'));\n";
var_export(insert('标题2', '内容2'));
echo "\n\n";

echo "var_export(select());\n";
var_export(select());
echo "\n\n";

echo "var_export(update(2, '标题2_更新','内容2_更新'));\n";
var_export(update(2, '标题2_更新','内容2_更新'));
echo "\n\n";

echo "var_export(select(2));\n";
var_export(select(2));
echo "\n\n";

echo "var_export(delete(2));\n";
var_export(delete(2));
echo "\n\n";

echo "var_export(select());\n";
var_export(select());
echo "\n\n";
输出:
var_export(insert('标题1', '内容1'));
array (
  0 => true,
  'insert_id' => 1,
)

var_export(insert('标题2', '内容2'));
array (
  0 => true,
  'insert_id' => 2,
)

var_export(select());
array (
  0 =>
  array (
    'id' => '1',
    'post_title' => '标题1',
    'post_content' => '内容1',
  ),
  1 =>
  array (
    'id' => '2',
    'post_title' => '标题2',
    'post_content' => '内容2',
  ),
)

var_export(update(2, '标题2_更新','内容2_更新'));
true

var_export(select(2));
array (
  0 =>
  array (
    'id' => '2',
    'post_title' => '标题2_更新',
    'post_content' => '内容2_更新',
  ),
)

var_export(delete(2));
true

var_export(select());
array (
  0 =>
  array (
    'id' => '1',
    'post_title' => '标题1',
    'post_content' => '内容1',
  ),
)
超戈戈
谢谢!看了一会,看懂一点了。
0
Null--Null
Null--Null
composer.json
"illuminate/database" : "*"

超戈戈
这个还不太了解。。。我再百度研究一下。
0
Tuesday
Tuesday
视频教程过期了,, 换一个.. 
超戈戈
您是指视频教程太旧了吗?
0
朱__朱
朱__朱

别被视频毁了,现在是什么年代了,还用mysql函数,用mysqli的我都鄙视。

翻开手册看下pdo

超戈戈
谢谢,我正在学习。
0
eechen
eechen
下面是PDO版本:
<?php
function db() {
	global $app;
	static $db;
	if ($db) {
		return $db;
	} else {
		try {
			$dsn = "mysql:dbname=$app[db_name];host=$app[db_host];port=$app[db_port];charset=utf8";
			$db = new PDO($dsn, $app['db_user'], $app['db_pass'], array(
				PDO::ATTR_PERSISTENT => false,
				PDO::ATTR_EMULATE_PREPARES => false,
				PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'
			));
		} catch (PDOException $e) {
			echo $e->getMessage();
			exit();
		}
	}
	return $db;
}

function insert($title = '', $content = '') {
	global $app;
	$db = db();
	$stmt = $db->prepare('INSERT INTO posts (post_title, post_content) VALUES(?, ?)');
	$stmt->bindParam(1, $title,   PDO::PARAM_STR);
	$stmt->bindParam(2, $content, PDO::PARAM_STR);
	$stmt->execute();
	return ($stmt->rowCount() !== 0) ? 
		array(true,  'lastInsertId' => $db->lastInsertId()) : 
		array(false, 'lastInsertId' => $db->lastInsertId());
}

function select($id = '') {
	global $app;
	$db = db();
	if (!empty($id)) {
		return $db->query('SELECT * FROM posts WHERE id = '.intval($id))->fetchAll(PDO::FETCH_ASSOC);
	} else {
		return $db->query('SELECT * FROM posts')->fetchAll(PDO::FETCH_ASSOC);
	}
}

function select_v2($id = '') {
	global $app;
	$db = db();
	if (!empty($id)) {
		$stmt = $db->prepare('SELECT * FROM posts WHERE id = ?');
		$stmt->bindParam(1, $id, PDO::PARAM_INT);
	} else {
		$stmt = $db->prepare('SELECT * FROM posts');
	}
	$stmt->execute();
	return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function update($id, $title = '', $content = '') {
	global $app;
	$db = db();
	$db->setAttribute(PDO::ATTR_AUTOCOMMIT, false); 
	$db->beginTransaction();
	$stmt = $db->prepare('UPDATE posts SET post_title = ?, post_content = ? WHERE id = ?');
	$stmt->bindParam(1, $title,   PDO::PARAM_STR);
	$stmt->bindParam(2, $content, PDO::PARAM_STR);
	$stmt->bindParam(3, $id,      PDO::PARAM_INT);
	$stmt->execute();
	$db->commit();
	return ($stmt->rowCount() !== 0) ? true : false;
}

function delete($id) {
	global $app;
	$db = db();
	return ($db->query('DELETE FROM posts WHERE id = '.intval($id))->rowCount() !== 0) ? true : false;
}

function delete_v2($id) {
	global $app;
	$db = db();
	$stmt = $db->prepare('DELETE FROM posts WHERE id = ?');
	$stmt->bindParam(1, $id, PDO::PARAM_INT);
	$stmt->execute();
	return ($stmt->rowCount() !== 0) ? true : false;
}

header('Content-Type: text/plain; charset=utf-8');

$app = array(
	'db_host' => '127.0.0.1',
	'db_user' => 'root',
	'db_pass' => 'xxxx',
	'db_name' => 'test',
	'db_port' => 3306
);

$table = "CREATE TABLE IF NOT EXISTS posts (
	id           int(10) unsigned NOT NULL AUTO_INCREMENT,
	post_title   varchar(255)     NOT NULL DEFAULT '',
	post_content text             NOT NULL DEFAULT '',
	PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;";

//db()->query('DROP TABLE IF EXISTS posts;') or exit();
db()->query($table) or exit();

echo "var_export(insert('标题1', '内容1'));\n";
var_export(insert('标题1', '内容1'));
echo "\n\n";

echo "var_export(insert('标题2', '内容2'));\n";
var_export(insert('标题2', '内容2'));
echo "\n\n";

echo "var_export(select());\n";
var_export(select_v2());
echo "\n\n";

echo "var_export(update(2, '标题2_更新','内容2_更新'));\n";
var_export(update(2, '标题2_更新','内容2_更新'));
echo "\n\n";

echo "var_export(select(2));\n";
var_export(select_v2(2));
echo "\n\n";

echo "var_export(delete(2));\n";
var_export(delete_v2(2));
echo "\n\n";

echo "var_export(select());\n";
var_export(select_v2());
echo "\n\n";
返回顶部
顶部