Logan Voss Logan Voss - 1 month ago 5
MySQL Question

On duplicate key update for multiple values that aren't the primary key

I need to basically do an "insert if not exists else update" type query, and the way everything I've read tells me to go is Insert into...On Duplicate Key Update. The thing is, my primary key is an autoincrement value that I never interact with or keep track of and I can't really dynamically generate it to put into my query.

A typical row would be:

ID| Project_ID | Location | Cost_Center_Category | Name | Number | Year | Q_1 |


1 | 200 | NUH | 1 |asfoi | 1098123|etc.


Basically the uniqueness (not literally) of each row came with the combination of Project_ID, Location, Cost_Center_Category, Name, Number, and year. If those all were identical, then an update to
Q_1
would occur.

UPDATE Labour_Planning
SET $Q = $submit
WHERE Project_ID = $selected_project
AND Year = $selected_year
AND Cost_Center_Category = $CCC
AND Cost_Center_Name = '$CC'
AND Cost_Center_Number = '$CC_Number'
AND Location = '$location';


Yeah, I know, SQL injection and all that, I will make this better. For now, I need to figure out a way to basically insert a row if ANY of the above columns are different. Is that possible with the Insert into....On Duplicate key?

Every example I see uses the primary key in their insert statement, and that's not really possible in this case.

Answer

I didn't want to do this for fear of obnoxious overhead, but considering I won't actually have many updates/inserts at a time, I just went with this.

    $labour_select = "SELECT Project_ID 
                    FROM Labour_Planning 
                    WHERE Project_ID = $selected_project
                    AND Year = $selected_year 
                    AND Cost_Center_Category = $CCC 
                    AND Cost_Center_Name = '$CC' 
                    AND Cost_Center_Number = '$CC_Number'
                    AND Location = '$location';";
    $result = $mysqli->query($labour_select);
    $num_rows = mysqli_num_rows($result);
    if ($num_rows == 0){
        $labour_insert = "INSERT INTO Labour_Planning (Project_ID, Location, Cost_Center_Category, Cost_Center_Name, Cost_Center_Number, Year, $Q) VALUES ($selected_project, '$location', $CCC, '$CC', '$CC_Number', $selected_year, $submit)";
        $insert_result = $mysqli->query($labour_insert);
    }
    else {
        $labour_update = "UPDATE Labour_Planning 
        SET $Q = $submit
        WHERE Project_ID = $selected_project 
        AND Year = $selected_year 
        AND Cost_Center_Category = $CCC 
        AND Cost_Center_Name = '$CC' 
        AND Cost_Center_Number = '$CC_Number'
        AND Location = '$location';";
        $update_result = $mysqli->query($labour_update);
    }

Now to look up prepared statements! I hear not only do they keep you protected from sql injection, it will make things of this nature faster as well! Thanks for all the help!