php使用mysqli连接数据库增删改查

查询

<?php
    //1、连接数据库，返回资源
    $con = mysqli_connect('localhost','root','1234@abcd');

    //2、判断是否连接成功
    if(mysqli_connect_errno()){
        echo '您的链接有错误，错误信息为：'.mysqli_connect_error();
    }
    //3、设置字符集
    mysqli_set_charset($con,'utf8');

    //4、选择数据库
    mysqli_select_db($con,'lamp140');

    //5、准备SQL语句
    $SQL = "select * from user";

    //6、执行SQL语句
    $result = mysqli_query($con,$SQL);//将会返回一个对象

    //7、处理结果集
    //var_dump(mysqli_fetch_row($result)); //取一条
    if($result && mysqli_affected_rows($con)){
		while($rows = mysqli_fetch_assoc($result)){
			$array[] = $rows;
		}
		// $array=mysqli_fetch_all($result); 返回结果下标为012而不是id,name...,
	}else{
		echo '操作失败';
		exit;
	}
    
    echo '总共有'.mysqli_num_rows($result).'条数据';

    //8、关闭资源、连接对象
    // mysqli_free_result($result);//将这个结果集释放掉。除非数量很大否则不建议使用!
    mysqli_close($con);//将连接关闭掉
?>

添加

<?php
    //1、连接数据库
    $con = mysqli_connect('localhost','root','');

    //2、判断是否出错
    if(mysqli_connect_errno()){
        echo mysqli_connect_error();
    }

    //3、设置客户端字符集
    mysqli_set_charset($con,'utf8');

    //4、选择数据库
    mysqli_select_db($con,'test');

    //5、准备SQL语句
    $SQL = "insert into user values(null,'钟小萌','123456','18')";

    //6、执行SQL语句
    $result = mysqli_query($con,$SQL);

    //7、处理结果
    if($result && mysqli_affected_rows($con) > 0){ //获得影响行数
        echo '添加成功!';
        var_dump(mysqli_insert_id($con)); //返回id
    }else{
        echo '插入失败';
    }
    //8、关闭连接
	 mysqli_close($con);
?>

修改

<?php
    //1、连接数据库
    $con = mysqli_connect('localhost','root','');

    //2、判断是否出错
    if(mysqli_connect_errno()){
        echo mysqli_connect_error();
    }

    //3、设置客户端字符集
    mysqli_set_charset($con,'utf8');

    //4、选择数据库
    mysqli_select_db($con,'test');

    //5、准备SQL语句
    $SQL = "update menu set name = 'Mate0' where pid = 2";

    //6、执行SQL语句
    $result = mysqli_query($con,$SQL);

    //7、处理结果
    if($result && mysqli_affected_rows($con) > 0){ //获得影响行数
        var_dump(mysqli_affected_rows($con));
        echo '修改成功!';
    }else{
        echo '修改失败';
    }
    //8、关闭连接
 	 mysqli_close($con);
?>

删除

<?php
    //1、连接数据库
    $con = mysqli_connect('localhost','root','');

    //2、判断是否出错
    if(mysqli_connect_errno()){
        echo mysqli_connect_error();
    }

    //3、设置客户端字符集
    mysqli_set_charset($con,'utf8');

    //4、选择数据库
    mysqli_select_db($con,'test');

    //5、准备SQL语句
    $SQL = "delete from user where id between 2 and 102";

    //6、执行SQL语句
    $result = mysqli_query($con,$SQL);

    //7、处理结果
    if($result && mysqli_affected_rows($con) > 0){ //获得影响行数
        var_dump(mysqli_affected_rows($con));
        echo '删除成功!';
    }else{
        echo '删除失败';
    }
	//8、关闭连接
    mysqli_close($con);
?>