php sql

最新推荐文章于 2025-04-27 16:22:34 发布

原创最新推荐文章于 2025-04-27 16:22:34 发布 · 310 阅读

0 ·

CC 4.0 BY-SA版权

php javascript 专栏收录该内容

11 篇文章

订阅专栏

增删查改


<?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