mysqlpdosql-updatemysql-insert-id

PDO passing bool values rather than the bind values


I'm trying to add an entry into a table if it doesnt exist of update it if it does exist.

$sql = "INSERT INTO company 
                (userid, companyName, registeredAddress, registeredPostcode) 
        VALUES ($userid, companyName = :companyName, registeredAddress = :registeredAddress, registeredPostcode = :registeredPostcode) 
        ON DUPLICATE KEY UPDATE 
            companyName = :companyName,
            registeredAddress = :registeredAddress, 
            registeredPostcode = :registeredPostcode;";  

$stmt = $this->connect()->prepare($sql);
$stmt->execute(['companyName' => $companyname, 
                'registeredAddress' => $registeredaddress,          
                'registeredPostcode' => $registeredpostcode]);
    
    

If the userid already exists then the update works perfectly.

If there is no userid it creates a row with teh user id but then places 0 or 1 in the other fields. 0 if there is a value and 1 if no value is passed.

Any idea where I'm going wrong?


Solution

  • The syntax looks wrong. In the insert's values clause you just need to pass the values, not = expressions:

    $sql = "INSERT INTO company (userid, companyName, registeredAddress, registeredPostcode) VALUES ($userid, :companyName, :registeredAddress, :registeredPostcode) ON DUPLICATE KEY UPDATE companyName = :companyName,registeredAddress = :registeredAddress, registeredPostcode = :registeredPostcode;";  
    

    Side note: $userid should probably be passed as a bind variable too (it wasn't in the original code)