PDO passing bool values rather than the bind values

Question

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?

Please, if you are going to use a prepared parameterised query use Parameters for ALL the values not just some!! — RiggsFolly, Oct 04 '21 at 11:12

score 0 · Accepted Answer · answered Oct 04 '21 at 11:12

0

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)

answered Oct 04 '21 at 11:12

Mureinik

297,002
52
306
350

Thank you so much Mureinik. This has been driving me crazy! This is all new to me and I'm trying to teach myself. - with The $userid I was entering this directly as it was not coming form the user entered data. I'll pass it as a bind variable too though. – neilt79 Oct 04 '21 at 11:29

PDO passing bool values rather than the bind values

1 Answers1