Why i cannot use REPLACE.How do I UPDATE a row in a table or INSERT it if it doesn't exist?

Question

I want to UPDATE a row in a table or INSERT it if it doesn't exist?

I have already read solution from this link. How do I UPDATE a row in a table or INSERT it if it doesn't exist?

So, i used replace but it did not work. It only added new row into the table but did not update anything.

this is my structure

<?php

define('ROOTPATH', __DIR__);
$output = [];
$output['result'] = [];
$output['image_path'] = [];
$applicationName = (isset($_POST) && array_key_exists('applicationName', $_POST)) ? $_POST['applicationName'] : 'applicationName';

if (empty($applicationName)) {
    $output['result'][] = 'missing application name';
}
else if (is_array($_FILES) && array_key_exists('image', $_FILES) && array_key_exists('logo', $_FILES))
{

    $upload_dir  = '/upload_dir/';
    $upload_path = ROOTPATH . $upload_dir;

    $applicationName = $_POST['applicationName'];

    $sql_field_list  = ['applicationName'];
    $sql_value_list  = [$applicationName];

    foreach ( $_FILES as $key => $upload) {

        if($key != 'image' && $key != 'logo')
        {
            $output['result'][] = $key . ' is invalid image';
        }
        else
        {

            if ($upload['error'] == UPLOAD_ERR_OK &&
                preg_match('#^image\/(png|jpg|jpeg|gif)$#', strtolower($upload['type'])) && //ensure mime-type is image
                preg_match('#.(png|jpg|jpeg|gif)$#', strtolower($upload['name'])) ) //ensure name ends in trusted extension
            {
                $parts     = explode('/', $upload['tmp_name']);
                $tmpName   = array_pop($parts);
                $fieldname = ($key == 'image') ? 'bgBNPage' : 'logo';
                $filename  = $applicationName . '_' . $fieldname . '.' . pathinfo($upload["name"], PATHINFO_EXTENSION);

                if (move_uploaded_file($upload["tmp_name"], $upload_path . $filename))
                {

                    $sql_field_list[] = $fieldname;
                    $sql_value_list[] = $upload_dir . $filename;

                    $output['image_path'][$key] = $upload_dir . $filename;
                }
                else
                {
                    $output['result'][] = $key . ' upload fail';
                }

            }
            else
            {
                $output['result'][] = $key . ' error while upload';
            }
        }

    }

    //after upload complete insert pic data into database

    $con = mysqli_connect("localhost", "root", "root", "museum");

    if (!$con) {
       echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }

    $fields = implode(', ', $sql_field_list);
    $values = implode("', '", $sql_value_list);
    $sql = "REPLACE INTO general (" . $fields . ") VALUES ('" . $values . "');";

    if (!mysqli_query($con, $sql)) {
        die('Error: ' . mysqli_error($con));
    }

    mysqli_close($con);


} else {

    $output['result'][] = 'no file selected';
}

    header('Content-type: application/json');
    echo json_encode($output);
    echo json_encode('finish');

?>

Can i use

if(logo or bgBNPage is enpty)
{
insert into database
}
else{
Update database
}

please tell me the correct syntax.

What's the actual SQL query that you're executing which "doesn't work"? If your SQL query isn't doing what you expect, you should at least take a look at what that query is. — David, Feb 22 '17 at 12:51
You have to set the primary key in your $sql_field_list and $sql_value_list. If not, replace is not able to "identify" the specific row. You will find an explanation here: http://stackoverflow.com/questions/3003244/does-replace-into-have-a-where-clause — Oliver, Feb 22 '17 at 13:00

score 0 · Answer 1 · answered Feb 22 '17 at 13:03

0

I'm guessing username is the field where, if it's a duplicate, you want to update. So, if username is a unique key, you can do something like:

insert into general ([fields]) values ([values])
on duplicate username update
[whatever]

answered Feb 22 '17 at 13:03

alanlittle

460
2
12

score 0 · Answer 2 · answered Feb 22 '17 at 15:20

I found the solution. I use if else condition to proove it. This is my code result

    //after upload complete insert pic data into database
    $con = mysqli_connect("localhost", "root", "root", "museum");
    $sql = "SELECT logo,bgBNPage FROM general "; 
    $result = mysqli_query($con, $sql); 


    if (!$con) {
       echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }

    $fields = implode(', ', $sql_field_list);
    $values = implode("', '", $sql_value_list);

    if(mysqli_num_rows($result) > 0) 
    {
        $str_array = [];
        for($i =0; $i < count($sql_field_list); $i++) 
        {
            $str_array[] = $sql_field_list[$i] . "='" .  $sql_value_list[$i] ."'";
        }

        $sql = 'UPDATE general SET ' . implode(',', $str_array);
        //$sql = "UPDATE general SET (" . $fields . ") = ('" . $values . "');";
    }
    else 
    {
        $sql = "INSERT INTO general (" . $fields . ") VALUES ('" . $values . "');";
    }


    if (!mysqli_query($con, $sql)) {
        die('Error: ' . mysqli_error($con));
    }

    mysqli_close($con);

Your solution is not atomic. What happens if the record exists, but a different update or a delete occurs between the time of your `SELECT` and the time of your `UPDATE`? You'll either overwrite the other update, or get an error if the record has been deleted. (Also, you might want to check your connection before submitting the query.) — alanlittle, Feb 22 '17 at 15:37

Why i cannot use REPLACE.How do I UPDATE a row in a table or INSERT it if it doesn't exist?

2 Answers2