-3

I want to add data to a table with user accounts that contains a column of type SET

roles set('user','admin') NOT NULL

In PHP the value is stored in an indexed array of strings.

I am binding the parameter with

$st = $conn->prepare($q);
...
// $pv is the value which causes the problem.
$r = array('user', 'admin');
// Nothing of these worked
// 1. Attempt, pass as the original array hoping PDO will take care
$pv = $r;
// 2. Convert to string with separator in various variations.
$pv = '\''.implode(',', $r).'\'';
// 2b.
$pv = '(\''.implode(',', $r).'\')';
// 2c.
$pv = '(\''.implode('\',\'', $r).'\')';
// 2d.
$pv = '\''.implode('\',\'', $r).'\'';

$st->bindParam(':roles', $pv);

I also tried $conn->setAttribute(\PDO::ATTR_EMULATE_PREPARES, FALSE);

Executing the query always returns

"01000",1265,"Data truncated for column 'roles' at row 1"
Rakete
  • 3
  • 2
  • The lack of actual code in this question makes it very hard to guess what the problem is. What I do know is that column 'roles' only accepts the value 'user' or 'admin' and you're clearly not setting one of those. – KIKO Software Sep 02 '21 at 10:00
  • https://dev.mysql.com/doc/refman/8.0/en/set.html clearly shows that values must be inserted as a comma-separated string, does it not? – CBroe Sep 02 '21 at 10:01
  • 1
    So where is `$pv` given a value? – KIKO Software Sep 02 '21 at 10:05
  • Does this answer your question? [Data truncated for column?](https://stackoverflow.com/questions/18089240/data-truncated-for-column) – Angel Deykov Sep 02 '21 at 10:08
  • @KIKOSoftware the column accepts any subset of 'user', 'admin'. Either of those or both of them. It is not an enum which would only accept one of those values. The values are set properly on the array. – Rakete Sep 02 '21 at 10:10
  • $pv is an array. Will add – Rakete Sep 02 '21 at 10:11
  • @CBroe that's exactly what I tried (like stated). Pass as comma separated string with/without quotes and also with/without parenthesis. – Rakete Sep 02 '21 at 10:13
  • Don't give us this vague "as stated", _show_ what you actually tried. Including what the query you are preparing looks like. – CBroe Sep 02 '21 at 10:19
  • You can't insert an array as a parameter value. You need to turn it into a string. Although, storing comma-separated strings containing multiple values in a single field is a design flaw in a relational database anyway. This should likely be stored in secondary table with a foreign key back to the user accounts table. Please ensure you actually understand relational database design, data normalisation, foreign key relationships etc before trying to design a database, otherwise you will just end up making all kinds of mistakes which cause big problems and are hard to correct later. – ADyson Sep 02 '21 at 10:22
  • P.S. `Pass as comma separated string with/without quotes and also with/without parenthesis`. You need to show us what you _actually did_ for this, because you saying you did it doesn't prove anything - if you still got an error then clearly you didn't do it quite right. (But as per my comment above, you should not be inserting a comma separated string anyway. Redesign your schema in a sensible way instead.) – ADyson Sep 02 '21 at 10:23
  • I added the examples. Sorry, was definitely too short in the initial post. PS: I disagree about SET columns being a design flaw but that's a different discussion. – Rakete Sep 02 '21 at 10:28
  • 1
    Why are you trying to add quotes or braces? If you wanted to insert the value `test` into a text column, then you would not put extra quotes or braces around it either, would you? The value you need to insert is `user,admin`. – CBroe Sep 02 '21 at 10:38
  • 1
    @CBroe Thanks. Well, stupid me, this was the only variant I did not try. Neither outer quotes, nor inner quotes not parenthesis. So, finally what worked was: implode(',', $user->Roles) – Rakete Sep 02 '21 at 10:44
  • 1
    @Rakete if you think storing multiple values in one field is ok, then please have a look at this to see what problems you are introducing to your application by doing that: https://stackoverflow.com/a/3653574/5947043 – ADyson Sep 02 '21 at 16:00
  • 1
    @ADyson I am not sure if we are talking about the same thing. I am not storing a delimited list of values in a text column which would be indeed a bad idea. SET columns are made for exactly that purpose and in a certain context it is the right column type. I don‘t use them often but sometimes since they are quite efficient. For instance, when I don‘t need an index on that column and the value is bound to a single property which is of type set (or an equivalent). If you want to see it from a normalization standpoint, a set value can be seen as an atomic value. – Rakete Sep 02 '21 at 19:31

1 Answers1

0

Solution is:

$r = array('user', 'admin');    
$pv = implode(',', $r);
$st->bindParam(':roles', $pv);
Rakete
  • 3
  • 2