php - 使用 MySQLi 和 PHP 未将数据提交到 SQL 数据库

Question

这是我的原始帖子:Why is data I upload getting renamed, and corresponding data added to different rows?

我能够稍微编辑一下代码(使用我得到的解决方案)，以便通过插入表单提交到服务器的图像与我上传的文件具有相同的名称。

Example: I upload turtle.jpg into the form and click Insert. The file "turtle.jpg" would be written into the database where it is located at on the server (images/turtle.jpg). And then a success message would pop up.

但每次我发送数据时，图像和其他数据都会以 2 个单独的行插入到数据库中。我不知道为什么。我还尝试修改我的代码，以便它使用 mysqli 而不是 mysql 并且不再起作用。没有错误，但没有数据发送到数据库。

这是我的新 PHP 代码:

error_reporting(E_ALL);
ini_set('display_errors', 1);

// Create connection
$conn = new mysqli('$host', '$user', '$pass', '$databasename');

// Check connection
if (mysqli_connect_error()) {
    die("Database connection failed: " . mysqli_connect_error());
}

if (!empty($_FILES["uploadedimage"]["name"])) {

	$file_name=$_FILES["uploadedimage"]["name"];
	$temp_name=$_FILES["uploadedimage"]["tmp_name"];
	$imgtype=$_FILES["uploadedimage"]["type"];
	$ext= GetImageExtension($imgtype);
	$imagename= $_FILES['uploadedimage']['name'];
        $target_path = "images/".$imagename;
        
        $result = $mysqli->query("INSERT INTO charts ( charts_URL ) VALUES ('".$target_path."')");
        or die(mysqli_error($mysqli));
        
} else {

        echo "<p> It is not working </p>";

    }


if(isset($_POST['submit'])){ // Fetching variables of the form which travels in URL
$date = $_POST['date'];
$retrace = $_POST['retrace'];
$start_of_swing_trade = $_POST['start_of_swing_trade'];
$end_of_swing_trade = $_POST['end_of_swing_trade'];
$bull_flag = $_POST['bull_flag'];
$bear_flag = $_POST['bear_flag'];
$ema_crossover = $_POST['ema_crossover'];
$trading_instrument = $_POST['trading_instrument'];
if($date !=''||$trading_instrument !=''){
//Insert Query of SQL
$sql = "INSERT into charts (charts_date, charts_retrace, charts_start_of_swing_trade, charts_end_of_swing_trade, charts_bullflag, charts_bearflag, charts_ema_crossover, charts_trading_instrument) VALUES ('$date', '$retrace', '$start_of_swing_trade', '$end_of_swing_trade', '$bull_flag', '$bear_flag', '$ema_crossover', '$trading_instrument')";

if (mysqli_query($conn, $sql)) {

    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}
}
mysqli_close($conn); // Closing Connection with Server

唯一一次将数据插入数据库是在我使用旧的 mysql_query 代码时。但是我的数据库说它支持 mysqli 扩展。

Database server
Server: Localhost via UNIX socket
Server type: MySQL
Server version: 5.5.35-cll-lve - MySQL Community Server (GPL)
Protocol version: 10
User: cpses_msLpFymSYl@localhost
Server charset: UTF-8 Unicode (utf8)

Web Server
cpsrvd 11.48.1.2
Database client version: libmysql - 5.1.73
PHP extension: mysqli Documentation

phpmyadmin
Version information: 4.0.10.7, latest stable version: 4.4.2

这是我当前的 PHP 代码片段(基本上是您在解决方案中发布的代码)，其中添加了 GetImageExtension 函数:

if(isset($_POST['submit'])){

    $conn = new mysqli($host, $user, $pass, $databasename);
    // Check connection can be established
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }
    
        function GetImageExtension($imagetype)
    {
       if(empty($imagetype)) return false;
       switch($imagetype)
       {
           case 'image/bmp': return '.bmp';
           case 'image/gif': return '.gif';
           case 'image/jpeg': return '.jpg';
           case 'image/png': return '.png';
           default: return false;
       }
     }

    $target_path = '';
    if (!empty($_FILES["uploadedimage"]["name"])) {
        $file_name=$_FILES["uploadedimage"]["name"];
        $temp_name=$_FILES["uploadedimage"]["tmp_name"];
        $imgtype=$_FILES["uploadedimage"]["type"];
        $ext= GetImageExtension($imgtype);
        $imagename= $_FILES['uploadedimage']['name'];
        $target_path = "images/".$imagename;

    $date = $_POST['date'];
    $retrace = $_POST['retrace'];
    $start_of_swing_trade = $_POST['start_of_swing_trade'];
    $end_of_swing_trade = $_POST['end_of_swing_trade'];
    $bull_flag = $_POST['bull_flag'];
    $bear_flag = $_POST['bear_flag'];
    $ema_crossover = $_POST['ema_crossover'];
    $trading_instrument = $_POST['trading_instrument'];

Answer 1

您可能需要检查变量名称并根据自己的喜好进行调整。使用预处理语句来防止sql注入(inject)。

if(isset($_POST['submit'])){

    $conn = new mysqli($servername, $username, $password, $dbname);
    // Check connection can be established
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }
    $target_path = '';
    if (!empty($_FILES["uploadedimage"]["name"])) {
        $file_name=$_FILES["uploadedimage"]["name"];
        $temp_name=$_FILES["uploadedimage"]["tmp_name"];
        $imgtype=$_FILES["uploadedimage"]["type"];
        $ext= GetImageExtension($imgtype);
        $imagename= $_FILES['uploadedimage']['name'];
        $target_path = "images/".$imagename;
    }

    $date = $_POST['date'];
    $retrace = $_POST['retrace'];
    $start_of_swing_trade = $_POST['start_of_swing_trade'];
    $end_of_swing_trade = $_POST['end_of_swing_trade'];
    $bull_flag = $_POST['bull_flag'];
    $bear_flag = $_POST['bear_flag'];
    $ema_crossover = $_POST['ema_crossover'];
    $trading_instrument = $_POST['trading_instrument'];

    if($date !=''||$trading_instrument !=''){

        $sql = "INSERT into charts (charts_URL, charts_date, charts_retrace, charts_start_of_swing_trade, charts_end_of_swing_trade, charts_bullflag, charts_bearflag, charts_ema_crossover, charts_trading_instrument) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
        // s = string, i = integer, d = double, b = blob
        //preparing statement
        $stmt = $conn->prepare($sql);
        if(!$stmt){ exit("prepare failed");}
        //binding param
        $bind = $stmt->bind_param('sssssssss',$target_path, $date, $retrace, $start_of_swing_trade, $end_of_swing_trade, $bull_flag, $bear_flag, $ema_crossover, $trading_instrument);
        if(!$bind){ exit("bind failed");}
        //will return 0 if fail
        if($stmt->execute() != 0){

            echo "New record created successfully";
        }else{ echo "Failed to insert new record";}

    }
//close connection
$conn->close();
}