[SOLVED] Updating an Enum column with checkboxes

[Copilot 🤖]

Hello, I've posted this question at StackOverflow http://tinyurl.com/lg6rb2 before.

 

Basically, I need a way to append a certain identifier to my content so that I can fetch it under different conditions. I'll mention the whole problem here again:

 

The web-site is going to be divided into a couple of sections e.g. example.com/france/, example.com/usa/ and example.com/international/ etc. All three areas are connected to one backend where the end-user has to have the ability to check off certain articles for either all or certain countries. It was suggested to me to use ENUM types. So I've made a field called 'country' in my db with the values enum('us', 'uk', 'fr', 'intl').

 

My checkboxes are as follows:

 

<input name="country_us" type="checkbox" value="us" <?php if ($_POST['country_us']!="") { echo $checked; } ?> >USA

<input name="country_uk" type="checkbox" value="uk" <?php if ($_POST['country_uk']!="") { echo $checked; } ?> >UK

<input name="country_fr" type="checkbox" value="fr" <?php if ($_POST['country_fr']!="") { echo $checked; } ?> >France

<input name="country_intl" type="checkbox" value="intl" <?php if ($_POST['country_intl']!="") { echo $checked; } ?> >International

 

I'm lost at the INSERT part -- as to how to talk to that field and set each one accordingly if it is checked or not...

 

Next part would be the actual content. How would I set this 'condition' to true?

 

<?php

$sql  = "SELECT id, title, country ";

$sql .= "FROM my_table ";

$result = mysql_query($sql);

while($row = mysql_fetch_assoc($result)) {

$id = $row['id'];

...

?>

 

Thank You for reading this far. I appreciate your help!

[Copilot 🤖]

Okay I tried $sql .= "(country) VALUES('$country_us'),('$country_uk'),('$country_fr'),('$country_intl')"; in my INSERT statement but I got "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(country) VALUES('usa'),(''),(''),('')' at line 1"

 

Please help me! =(

[Copilot 🤖]

Well that was embarrassing Turns out it's simply SET country = '$country_us' which works fine but I'm not sure how to add the rest...? country = '$country_us, $country_uk, $country_fr, $country_intl' is wrong and the same column can't be repeated twice or else I would have done it separately. I'm thissss close to it so please help.

 

Thanks!

[Copilot 🤖]

Got it! Enum only holds one value where as SET holds multiple values. Now that I can add to the the database -- I have an issue with pulling content from it. For example,

 

I can do $sql  = "SELECT id, title, content ";

$sql .= "FROM table_name ";

$sql .= "WHERE country='us'"; works fine but if the country is "us,uk" it wouldn't work. This should be easy, please help!

[Copilot 🤖]

A kind soul at StackOverflow said to use the FIND_IN_SET function. So:

 

$sql .= "WHERE FIND_IN_SET('us', country)";

 

All resolved. Thank you for your help everyone, haha!