Data not being submited to SQL Database using MySQ

问题:

Here's my original post: Why is data I upload getting renamed, and corresponding data added to different rows?

I was able to edit the code a little bit (using the solution I was given) so that the image that was submitted to the server via the insert form had the same name as the file I uploaded.

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.

But everytime I sent data, the image and the other data would be inserted into the database on 2 SEPERATE rows. I have no idea why. I also tried modifying my code so that it used mysqli instead of mysql and nothing works anymore. No errors but no data is sent into the database.

Here's my new php code:

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

The only time that data is inserted into the database is when I use the old mysql_query code. But my database says it supports the mysqli extension.

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

Here's a snippet of the my current PHP code (which is basically the code you posted in your solution) with the GetImageExtension function added:

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'];

回答1:

You might need to check the variable names and adjust it to your liking. Use prepared statement to prevent sql injection.

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();
}

回答2:

But everytime I sent data, the image and the other data would be inserted into the database on 2 SEPERATE rows. I have no idea why.

Why would you expect it to land in the same row? You perform two different insert queries. If you do want to use two queries, the second one would have to be an update of the previously inserted row. But obviously, that's not the preferred way, just use one query.

Combine your if (!empty($_FILES["uploadedimage"]["name"])) and if(isset($_POST['submit'])) and then use something like this, where you insert the URL at the same time into the same row as all the other values:

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 (?,?,?,?,?,?,?,?)

Security

Please note that your code is extremely unsecure. $imagename is user controlled, so your first query is open to SQL injection. The values in your second query are obviously user controlled, that too is vulnerable. SQL injection can take place in all sorts of queries, including on inserts. It makes it possibly to leak data, DOS you, and possibly execute code or change data. Use prepared statements to protect against SQL injection. It's simple to use and results in nice code, there is no reason not to use it.

Note also that $_FILES["uploadedimage"]["type"] is user controlled as well and independent of the actual file type or extension. You should not trust it when deciding on the extension of the image on your server (if you do, an attacker could eg upload a PHP script).