2

I am stuck somewhere and need your guidance, basically I am inserting multiple values to single colomn using bit-wise operator OR(|) and my table structure is like enter image description here and I am inserting weekdays like 1 for sunday, 2 from monday, 4 for tuesday, 8 for wednesday, and so on separating by pipe sign(|) like for sunday and monday it will insert like 1|2 and so on but when it inserted to table it is showing like that enter image description here

So now I want to know how will I select and show the exact value that I have inserted and how to update it using php(codeigniter) and mysql?

I have used this query select cast(column_name as varchar) from table_name and it gives the result by sum all the decoded value like 5 for 1|4, 3 for 1|2, but now how can I convert it to the inserted type like 1|2 for 3 and 1|4 for 5 and so on...

Mogsdad
  • 44,709
  • 21
  • 151
  • 275
Vivek Singh
  • 2,453
  • 1
  • 14
  • 27
  • 1
    try `SELECT CAST( week_days AS CHAR ) from table_name` – Chetan Ameta Apr 14 '16 at 06:49
  • have a look at http://stackoverflow.com/questions/1873085/how-to-convert-from-varbinary-to-char-varchar-in-mysql – Chetan Ameta Apr 14 '16 at 06:50
  • 1
    the question is why do you want to store multiple values, separated by `|` in a binary column type? Typically anytime you store separated values, whether by commas (csv), tabs, or even `|`, you should normalize your table, and be adding one-to-many rows instead – Sean Apr 14 '16 at 06:51
  • @Sean the client wants to store the values like this – Vivek Singh Apr 14 '16 at 06:55
  • @Vicky What have you tried? – Ikhlak S. Apr 14 '16 at 07:02
  • @user3284463 i have tried this `select cast(column_name as varchar) from table_name` and it gives the result by sum all the decoded value like 5 for 1|4, 3 for 1|2, but now how can i convert it to like inserted type like 1|2 for 3 and 1|4 for 5 – Vivek Singh Apr 14 '16 at 07:22
  • Client wants to store the values like this? Since when do clients deal with architectural and normalization issues? If the client wants data like that, you can still normalize your data set and deliver a string concatenated by `|`. – Mjh Apr 14 '16 at 07:56

1 Answers1

1
$column&(1<<$day)

will be 0 if that day is not in column and 1<<$day if that day is in column in php.

You don't need to use binary type. Tinyint unsigned is enough. You can insert like this

(1|2)

In php, to convert it to the format you want,

echo '(';
$sep=0;
for($a=1;$a<=$column;$a<<=1)
    if($column&$a){
        if($sep) echo '|';
        else $sep=1;
        echo $a;
    }
echo ')';
v7d8dpo4
  • 1,399
  • 8
  • 9