《php和mysql web开发》笔记——第11章 使用MySQL从Web访问数据库

建立连接：

mysqli::__construct ([ string$host =ini_get("mysqli.default_host") [, string $username= ini_get("mysqli.default_user") [, string $passwd= ini_get("mysqli.default_pw") [, string $dbname= "" [, int$port =ini_get("mysqli.default_port") [, string $socket= ini_get("mysqli.default_socket") ]]]]]] )

 

查询：

mixed mysqli::query ( string$query [, int$resultmode = MYSQLI_STORE_RESULT ] )

 

查询结果：

记录条数：$mysqli->num_rows

读取记录：array mysqli_result::fetch_assoc ( void )

 

断开连接：

void mysqli_result::free ( void )

bool mysqli::close ( void )

但是，这并不是必要的，因为脚本执行完毕的时候它们将被自动关闭。

 

插入，更新用的是也是query函数。可以通过$mysqli->affected_rows来获取影响的记录数。

 

<style>
td,th{
	border:1px solid gray;
}
</style>
<?php
function showBooks(){
	$db = new mysqli('localhost', 'root', '111111', 'books');
	$query = "select * from books";
	if (mysqli_connect_errno()) {
		echo 'Error: Could not connect to database.  Please try again later.';
		exit;
	}
	$result = $db->query($query);
	$numResult = $result->num_rows;
	echo "共".$numResult."条记录<br>";
?>
<table style="border:1px solid gray;" cellspacing="0px">
<tr><th>isbn</th><th>作者</th><th>标题</th><th>价格</th></tr>
<?php
	for($i=0;$i<$numResult;$i++){
		$row = $result->fetch_assoc();
		echo "<tr>";
		echo "<td>".$row['isbn']."</td>";
		echo "<td>".$row['author']."</td>";
		echo "<td>".$row['title']."</td>";
		echo "<td>".$row['price']."</td>";
		echo "</td>";
	}
	$result->free();
?>
</table>
<?php
}
function showBooks2(){
	$db = new mysqli('localhost', 'root', '111111', 'books');
	$query = "select * from books";
	if (mysqli_connect_errno()) {
		echo 'Error: Could not connect to database.  Please try again later.';
		exit;
	}
	$stmt = $db->prepare($query);
	$stmt->execute();
	$stmt->bind_result($isbn,$name,$title,$price);
	$stmt->store_result();
	$numResult = $stmt->affected_rows;
	echo "共".$numResult."条记录<br>";
?>
<table style="border:1px solid gray;" cellspacing="0px">
<tr><th>isbn</th><th>作者</th><th>标题</th><th>价格</th></tr>
<?php
	while($stmt->fetch()){
		echo "<tr>";
		echo "<td>".$isbn."</td>";
		echo "<td>".$name."</td>";
		echo "<td>".$title."</td>";
		echo "<td>".$price."</td>";
		echo "</td>";
	}
	$stmt->close();
?>
</table>
<?php
}

function insert($isbn,$name,$title,$price){
	$db = new mysqli('localhost', 'root', '111111', 'books');
	$query = "insert into books(isbn,author,title,price) values(?,?,?,?)";
	if (mysqli_connect_errno()) {
		echo 'Error: Could not connect to database.  Please try again later.';
		exit;
	}
	$stmt = $db->prepare($query);
	$stmt->bind_param("sssd",$isbn,$name,$title,$price);
	$stmt->execute();
	if($stmt->error)
	{
		echo $stmt->error;
		exit;
	}
	echo "成功插入".$stmt->affected_rows."条数据<br>";
	$stmt->close();
}
showBooks();
insert("2015-10-09 03号","金庸","天龙八部",86.3);
showBooks2();
?>