CODEWITHSUNDEEP
phpmysqlsetaddition
Mattia Frusca
Mattia Frusca

Reputation: 45

How to add a possble value to a MySQL SET type in php, without know the current values

Hi everybody and sorry for my english.

I have the column "example" that is a SET type. I have to make a php page where you can add values to that column.

First of all I need to know what is just in "example", to prevent the adding of an existing value by a control. Second of all I need to add the new value.

Here's what I had thinked to do.

//I just made the connection to the db in PDO or MySQLi
$newValue=$_POST['value']; //I take the value to add in the possible values from a form
//Now I have to "extract" all the possible values. Can't think how.

//I think I can store the values into an array
$result=$sql->fetch();  //$sql is the query to extract all the possible values from "example"

//So now i can do a control with a foreach
foreach($result as $control){
     if ($newValue == $control){
          //error message, break the foreach loop
     }
}
//Now, if the code arrives here there isn't erros, so the "$newValue" is different from any other values stored in "example", so I need to add it as a possible value
$sql=$conn->query("ALTER TABLE 'TableName' CHANGE 'example' 'example' SET('$result', '$newValue')"); //<- where $result is the all existing possible values of "example"

In PDO or MySQLi, it's indifferent

Thanks for the help

Upvotes: 1

Views: 375

Answers (2)

spencer7593
spencer7593

Reputation: 108400

We can get the column definition with a query from information_schema.columns

Assuming the table is in the current database (and assuming we are cognizant of lower_case_table_names setting in choosing to use mixed case for table names)

 SELECT c.column_type 
   FROM information_schema.columns c
  WHERE c.table_schema = DATABASE()
  WHERE c.table_name   = 'TableName'
    AND c.column_name  = 'example'

Beware of the limit on the number of elements allowed in a SET definition.

Remove the closing paren from the end, and append ',newval').

Personally, I don't much care for the idea of running an ALTER TABLE as part of the application code. Doing that is going to do an implicit commit in a transaction, and also require an exclusive table / metadata lock while the operation is performed.

Upvotes: 1

Darker
Darker

Reputation: 83

If you need a SET type - you should know what values you add. Otherwise, simply use VARCHAR type.

Upvotes: 0

Related Questions