- 增删查改
<?php
$dbh = new PDO('mysql:host=localhost;dbname=access_control', 'root', '');
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$dbh->exec('set names utf8');
/*添加*/
//$sql = "INSERT INTO user SET login=:login AND password=:password";
$sql = "INSERT INTO user (login ,password)VALUES (:login, :password)"; $stmt = $dbh->prepare($sql); $stmt->execute(array(':login'=>'kevin2',':password'=>''));
echo $dbh->lastinsertid();
/*修改*/
$sql = "UPDATE user SET password=:password WHERE user_id=:userId";
$stmt = $dbh->prepare($sql);
$stmt->execute(array(':userId'=>'7', ':password'=>'4607e782c4d86fd5364d7e4508bb10d9'));
echo $stmt->rowCount();
/*删除*/
$sql = "DELETE FROM user WHERE login LIKE 'kevin_'"; //kevin%
$stmt = $dbh->prepare($sql);
$stmt->execute();
echo $stmt->rowCount();
/*查询*/
$login = 'kevin%';
$sql = "SELECT * FROM user WHERE login LIKE :login";
$stmt = $dbh->prepare($sql);
$stmt->execute(array(':login'=>$login));
while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
print_r($row);
}
?>
/*查询2*/
$login = 'kevin%';
$sql = "SELECT * FROM user WHERE login LIKE :login";
$stmt = $dbh->prepare($sql);
$stmt->execute(array(':login'=>$login));
$rows = $stmt->fetchall(PDO::FETCH_ASSOC)
foreach ($rows as row)
{ print_r($row); }
- prepare 避免sql injection
- error handling
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
try {
$stmt = $pdo->prepare("SELECT * FROM users where user_id = :xyz");
$stmt->execute(array(":pizza" => $_GET['user_id']));
} catch (Exception $ex ) {
echo("Internal error, please contact support");
error_log("error4.php, SQL error=".$ex->getMessage());
return;
}
$row = $stmt->fetch(PDO::FETCH_ASSOC);
error_log 可在phpinfo中查看
/Applications/MAMP/logs/php_error.log
watch it actively 在命令行中输入 tail -f filename
- html内嵌入php,php里又嵌入了html
#add
<?php
$pdo = new PDO('mysql:host=localhost;port=8889;dbname=misc',
'fred', 'zap');
// See the "errors" folder for details...
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
if ( isset($_POST['name']) && isset($_POST['email'])
&& isset($_POST['password'])) {
$sql = "INSERT INTO users (name, email, password)
VALUES (:name, :email, :password)";
echo("<pre>\n".$sql."\n</pre>\n");
$stmt = $pdo->prepare($sql);
$stmt->execute(array(
':name' => $_POST['name'],
':email' => $_POST['email'],
':password' => $_POST['password']));
}
$stmt = $pdo->query("SELECT name, email, password FROM users");
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>
<html>
<head></head><body><table border="1">
<?php
foreach ( $rows as $row ) {//循环要用php
echo "<tr><td>";//表格又要用html
echo($row['name']);
echo("</td><td>");
echo($row['email']);
echo("</td><td>");
echo($row['password']);
echo("</td></tr>\n");
echo('<form method="post"><input type="hidden" ');//提交表单也要用html
echo('name="user_id" value="'.$row['user_id'].'">'."\n");
echo('<input type="submit" value="Del" name="delete">');
echo("\n</form>\n");
echo("</td></tr>\n");
}
?>
</table>
<p>Add A New User</p>
<form method="post">
<p>Name:
<input type="text" name="name" size="40"></p>
<p>Email:
<input type="text" name="email"></p>
<p>Password:
<input type="password" name="password"></p>
<p><input type="submit" value="Add New"/></p>
</form>
</body>
html里打印php的变量 <?=....?> 等于<?php echo(...);?>
<p>Confirm: Deleting <?= htmlentities($row['name']) ?></p>
<form method="post">
<input type="hidden" name="user_id" value="<?= $row['user_id'] ?>">
<input type="submit" value="Delete" name="delete">
<a href="index.php">Cancel</a>
</form>
- MySQL创建索引有两种语法,即:
用户无法看到索引,它们只能被用来加速搜索/查询。
ALTER TABLE tablename ADD INDEX (column_name);
CREATE INDEX index_name tablename (column_name);
ENGINE=InnoDB DEFAULT CHARSET=utf8
ENGINE=InnoDB使用innodb引擎
DEFAULT CHARSET=utf8 数据库默认编码为utf-8
- 一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY。
FOREIGN KEY 约束用于预防破坏表之间连接的动作。
FOREIGN KEY 约束也能防止非法数据插入外键列,因为它必须是它指向的那个表中的值之一。
FOREIGN KEY (Id_P) REFERENCES Persons(Id_P);// 在 "Orders" 表创建时为 "Id_P" 列创建 FOREIGN KEY,它是Persons表中的Id_P
CONSTRAINT `usertable2_ibfk_1` FOREIGN KEY (`USER_ID`) REFERENCES `usertable` (`ID`) ON DELETE CASCADE
//将子表(有外键的表)中约束名为 ` usertable2_ibfk_1` 的 外键 USER_ID 关联到主表 usertable表的 ID
CASCADE: 从父表删除或更新且自动删除或更新子表中匹配的行。
- 这个函数将返回最后插入的那条记录在表中自增的那个字段的值
$id=$pdo->lastInsertId();