老司机求带:php中的数据库操作。? 400 报错-问答-阿里云开发者社区-阿里云

开发者社区> 问答> 正文

老司机求带:php中的数据库操作。? 400 报错

爱吃鱼的程序员 2020-06-01 11:21:12 78

老司机求带:php中的数据库操作。? 400 报错

小弟刚自学了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);
}

SQL 数据库连接 PHP 数据库
分享到
取消 提交回答
全部回答(1)
  • 爱吃鱼的程序员
    2020-06-01 11:21:14

    操作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',
      ),
    )


    ######谢谢!看了一会,看懂一点了。###### composer.json
    "illuminate/database" : "*"

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

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

    翻开手册看下pdo

    ######谢谢,我正在学习。######下面是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";
    0 0
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

推荐文章
相似问题
推荐课程