时间戳

首页 > 解决方案 > 如何使用 session_start 更新 MySQL 中的字段并由 id 确定

php - 如何使用 session_start 更新 MySQL 中的字段并由 id 确定

问题描述

我正在尝试为我的用户创建一个空间，他们可以在其中更改其帐户名称等。我进行了很多调查，但找不到安全的东西。现在我只是在努力工作。我用一个简单的 INSERT 插入我的用户：

$query = "INSERT INTO users (Name, Email, Password, metodop, social1, social2, aboutt) VALUES ('$name', '$email', '$passHash', '$metodop', '$social1', '$social2', '$aboutt')";

这是插入的一部分：

$name = $_POST['name'];
$email = $_POST['email'];
$pass = $_POST['password'];

$metodop = $_POST['metodop'];
$social1 = $_POST['social1'];
$social2 = $_POST['social2'];
$aboutt = $_POST['aboutt'];

但我现在想创建一些东西来更新用户可以使用的这些字段，但我不知道如何，因为我不想留下一个用户可以编辑整个数据库的东西。在这里，我正在使用 session_start。我想让登录的用户可以在我的数据表中更新他的数据。这是我想出来的，但它不起作用：

$data = [
    'name' => $name,
    'surname' => $surname,
    'social1' => $social1,
    'id' => $id,
];
$sql = "UPDATE users SET name=:name, surname=:surname, social1=:social1 WHERE id=:id";
$stmt= $pdo->prepare($sql);
$stmt->execute($data);
?>

更新：嗨，我这几天努力工作，我有这个：

用于更新用户数据的 PDO：

<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);
session_start();
?>
<html>
<?php
	/*Incluyendo la conexion y enviando el Arreglo Files a la funcion*/
	include 'pd.conn.php';
	if(isset($_POST['update']))
	{
        
			
			// data sent from form login.html 
			$email = $_POST['email']; 
			
			
			
            
       
        
		$bemail = $_POST['email']; //by get session value in login form

		$sqlgetid = "SELECT id FROM users WHERE Email = '$email'";
		
        
        $q = $pdo->query($sqlgetid);
        $stmt->bindParam(":Email", $email); //this is the line where i get all this errors//
		$email = $bemail;
    
		$q->setFetchMode(PDO::FETCH_ASSOC);
		while ($row = $q->fetch()){
			$verify_id = $row['id']; //user id value set to verify_id variable
		}
		
		$sql = "UPDATE users SET Name=:Name, social1=:social1, social2=:social2, aboutt=:aboutt WHERE id=:id";
		
			if($stmt = $pdo->prepare($sql)){
				// Bind variables to the prepared statement as parameters
				$stmt->bindParam(":Name", $param_Name);
				$stmt->bindParam(":social1", $param_social1);
				$stmt->bindParam(":social2", $param_social2);
				$stmt->bindParam(":aboutt", $param_aboutt);
				$stmt->bindParam(":id", $param_id);
		
				// Set parameters
				$param_Name    = $Name;
				$param_social1 = $social1;
				$param_social2 = $social2;
				$param_aboutt  = $aboutt;
				$param_id      = $verify_id;
		
				// Attempt to execute the prepared statement
				if($stmt->execute()){
					// Records updated successfully. Redirect to landing page
					header("edit-profile.php");
					exit();
				} else{
					echo "Something went wrong. Please try again later.";
				}
            }
             
	}
?>
<div class='alert alert-success mt-4' role='alert'><strong>Welcome!</strong><?php echo $_SESSION['name'] ?? ""; ?></div>
<form method="post">                           	
								<div class="form-group">									
									<input method="post" class="form-control input-lg" name="Nombre" placeholder="Nombre" >        
								</div>							
								<div class="form-group">        
									<input method="post" class="form-control input-lg" name="social1" placeholder="social11" >       
                                </div>	
                                <div class="form-group">        
									<input method="post" class="form-control input-lg" name="social2" placeholder="social22" >       
                                </div>		
                                <div class="form-group">        
									<input method="post" class="form-control input-lg" name="aboutt" placeholder="about" >       
                                </div>		
                                								    
									<button type="submit" name="update" class="btn btn-success btn-block">Update</button>
							</form>
这就是我在数据库中插入新用户的方式。

<div class="container">

	<?php

	include 'conn.php';

	$conn = mysqli_connect($dbhost, $dbuser, $dbpass, $dbname);

	// Check connection
	if (!$conn) {
		die("Connection failed: " . mysqli_connect_error());
	}
	
	// Query to check if the email already exist
	$checkEmail = "SELECT * FROM users WHERE Email = '$_POST[email]' ";
	$checkName = "SELECT * FROM users WHERE Name = '$_POST[name]' ";

	// Variable $result hold the connection data and the query
	$result = $conn-> query($checkEmail);
    $result = $conn-> query($checkName);
	// Variable $count hold the result of the query
	$count = mysqli_num_rows($result);

	// If count == 1 that means the email is already on the database
	if ($count == 1) {
	echo "<div class='alert alert-warning mt-4' role='alert'>
					<p>That email or name is already registred, try to change first the name, if it continues getting error, try with another email.</p>
					<p><a href='login.html'>Please login here</a></p>
				</div>";
	} else {	
	
	/*
	If the email don't exist, the data from the form is sended to the
	database and the account is created
	*/
	$name = $_POST['name'];
	$email = $_POST['email'];
	$pass = $_POST['password'];

	$metodop = $_POST['metodop'];
	$social1 = $_POST['social1'];
	$social2 = $_POST['social2'];
	$aboutt = $_POST['aboutt'];


	// The password_hash() function convert the password in a hash before send it to the database
	$passHash = password_hash($pass, PASSWORD_DEFAULT);
	
	// Query to send Name, Email and Password hash to the database
	$query = "INSERT INTO users (Name, Email, Password, metodop, social1, social2, aboutt) VALUES ('$name', '$email', '$passHash', '$metodop', '$social1', '$social2', '$aboutt')";
	
	
	if (mysqli_query($conn, $query)) {
		echo "<div class='alert alert-success mt-4' role='alert'><h3>Your account has been created.</h3>
		<a class='btn btn-outline-primary' href='login.html' role='button'>Login</a></div>";		
		} else {
			echo "Error: " . $query . "<br>" . mysqli_error($conn);
		}	
	}	
	mysqli_close($conn);
	?>
</div>

conn.php 是与数据库的简单连接，但与 pdo 连接不同，我收到此错误，$stm 中的未定义变量，在上传系统中，因为这里不起作用的是上传， insert 效果很好，但不在 pdo 中。但是如果我在 pdo 系统中进行上传会更好。在我不太明白的事情之后，为什么我会收到第二个错误，未捕获的错误：在 null 上调用成员函数 bindParam()。在同一行中，它说的地方。$stmt->bindParam(":Email", $email); 但在上传时我使用这个连接：

 <?php
     define('DB_SERVER', 'xxx.0.0.x');
     define('DB_USERNAME', 'root');
     define('DB_PASSWORD', '');
     define('DB_NAME', 'phplogin');

     /* Attempt to connect to MySQL database */

     try {
        $pdo = new PDO("mysql:host=" . DB_SERVER . ";dbname=" . DB_NAME, DB_USERNAME, DB_PASSWORD);
        // Set the PDO error mode to exception
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
     }

     catch(PDOException $e) {   
        die("ERROR: Could not connect. " . $e->getMessage());
     }

?>

那是一样的。非常感谢您的帮助，您已经赢得了我的选票，但是如果我们一起解决这个问题，那将是惊人的，您会怎么做？

标签： phppdo

解决方案

您可以使用安全的PDO插入功能，

$sql = "INSERT INTO users (Name, Email, Password, metodop, social1, social2, aboutt) VALUES (:Name, :Email, :Password, :metodop, :social1, :social2, :aboutt)";

if($stmt = $pdo->prepare($sql)){
        // Bind variables to the prepared statement as parameters

        $stmt->bindParam(":Name", $param_Name, PDO::PARAM_STR);
        $stmt->bindParam(":Email", $param_Email, PDO::PARAM_STR);
        $stmt->bindParam(":Password", $param_Password, PDO::PARAM_STR);
        $stmt->bindParam(":metodop", $param_metodop, PDO::PARAM_STR);
        $stmt->bindParam(":social1", $param_social1, PDO::PARAM_STR);
        $stmt->bindParam(":social2", $param_social2, PDO::PARAM_STR);
        $stmt->bindParam(":aboutt", $param_aboutt, PDO::PARAM_STR);

        // Set parameters
        $param_Name     = $Name;
        $param_Email    = $Email;
        $param_Password = password_hash($Password, PASSWORD_DEFAULT); // Creates a password hash
        $param_metodop  = $metodop;
        $param_social1  = $social1;
        $param_social2  = $social2;
        $param_aboutt   = $aboutt;

} else {
            echo "Something went wrong. Please try again later.";
}

然后您需要在用户登录时创建一个会话变量。因此，确定唯一用户并授予该用户更改其姓名的权限。

更新功能

session_start(); //session start
$bemail = $_SESSION['useremail']; //by get session value in login form

$sqlgetid = "SELECT id FROM users WHERE Email=:Email";

$q = $pdo->query($sqlgetid);
$stmt->bindParam(":Email", $param_Email);
$param_Email = $bemail;

$q->setFetchMode(PDO::FETCH_ASSOC);
while ($row = $q->fetch()){
    $verify_id = $row['id']; //user id value set to verify_id variable
}

$sql = "UPDATE users SET Name=:Name, social1=:social1, social2=:social2, aboutt=:aboutt WHERE id=:id";

    if($stmt = $pdo->prepare($sql)){
        // Bind variables to the prepared statement as parameters
        $stmt->bindParam(":Name", $param_Name);
        $stmt->bindParam(":social1", $param_social1);
        $stmt->bindParam(":social2", $param_social2);
        $stmt->bindParam(":aboutt", $param_aboutt);
        $stmt->bindParam(":id", $param_id);

        // Set parameters
        $param_Name    = $Name;
        $param_social1 = $social1;
        $param_social2 = $social2;
        $param_aboutt  = $aboutt;
        $param_id      = $verify_id;

        // Attempt to execute the prepared statement
        if($stmt->execute()){
            // Records updated successfully. Redirect to landing page
            header("location: redirect_page_link_here");
            exit();
        } else{
            echo "Something went wrong. Please try again later.";
        }
    }

使用PDO（PHP 数据对象）连接数据库

<?php
     define('DB_SERVER', 'xxx.x.x.x');
     define('DB_USERNAME', 'root');
     define('DB_PASSWORD', '');
     define('DB_NAME', 'phplogin');

     /* Attempt to connect to MySQL database */

     try {
        $pdo = new PDO("mysql:host=" . DB_SERVER . ";dbname=" . DB_NAME, DB_USERNAME, DB_PASSWORD);
        // Set the PDO error mode to exception
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
     }

     catch(PDOException $e) {   
        die("ERROR: Could not connect. " . $e->getMessage());
     }

?>

推荐阅读