How do I insert multiple checkbox data into MySQL

[Smurf_UK]

Hi All,

I am new to the site, and also to PHP and MySQL, so please go easy on me for asking what may be an obvious question for many.

I am wanting to store the data from a sign-up form in a MySQL DB, but I cannot get my head around handling an array, and inserting it into a single row.

What I can already do is to create, connect and input "text" and "radio" type data into the table, so you can safely ignore some of the more obvious problems like connectivity etc.

The HTML for the checkbox area of the form in below.

<input name="work_style[]" value="1" type="checkbox">1  
<input name="work_style[]" value="2" type="checkbox">2
<input name="work_style[]" value="3" type="checkbox">3
<input name="work_style[]" value="4" type="checkbox">4
<input name="work_style[]" value="5" type="checkbox">5 
<input name="work_style[]" value="6" type="checkbox">6 
<input name="work_style[]" value="7" type="checkbox">7  
<input name="work_style[]" value="8" type="checkbox">8   
<input name="work_style[]" value="9" type="checkbox">9   
<input name="work_style[]" value="10" type="checkbox">10

I already have code to insert the other form data in to the rows [below], but how would I incorporate adding the array to the database?

$query=mysql_query("insert into plus_signup(userid,password,email,f_name,l_name,work_type) values('$userid','$password','$email','$f_name','$l_name','$work_type')");

I have searched all over the net for an answer to this problem, and I couldn't find anything that really helped, so any advise would be gratefully recieved.

Cheers.

[antwan1986]

Hi Smurf_UK,

The first thing I should note is that when you post an array of html checkboxes, they'll appear as an array of checkboxes when PHP gets a hold of them. So the first thing you could do would be this:

$workStyle = $_POST['work_style'];
  var_dump($workStyle);

What you'll find is that the var_dump will bring back the information that the variable is an array with a certain number of elements. Now you want all of those elements to go into a single string of code, so you can put it in the database. The next step is then to cycle through that array, and add each element to the same string, by concatenating them:

// Setting up a blank variable to be used in the coming loop.
  $allStyles = "";

  // For every checkbox value sent to the form.
  foreach ($workStyle as $style) {
    // Append the string with the current array element, and then add a comma and a space at the end.
    $allStyles .= $style . ", ";
  }

If you were to echo that string out now, you'd see all your values seperated by a comma and a space. The next problem is that the very last entry into the string will also put a comma and a space at the end. That's useless because there's no data coming next so lets get rid of it:

// Delete the last two characters from the string.
  $allStyles = substr($allStyles, 0, -2);

Now you've got all the entries into a handy string seperated by commas and spaces! You can just add this to the database like you did with every other string!

A side note is that when your bringing it back out of the database, you can use a function called explode that will search for ", " (the comma and space) and that will make it back into an array for you to play with.

I hope that helps you; any other questions please don't be afraid to ask!

Anthony

[kanaku]

Cool. (Even if I didn't ask the question)

I always wondered how the check(ed)boxes are passed to the table.

But is it bad practice to create a separate table that holds the checked values for all users (ie an interests table that stores userID and interestID?)

[antwan1986]

But is it bad practice to create a separate table that holds the checked values for all users (ie an interests table that stores userID and interestID?)

Hi kanaku,

It's not bad practice, in fact, in some cases it's better and more efficient to keep seperate SQL tables. That method is one way of doing it, and the method I described is another. I prefer with small information like the example above (which seemed simple words or numbers) to keep it all in one table, and just explode the string when I need to bring it back out!

Anthony

[kkeith29]

why not just use implode

$styles = implode( ',',$_POST['work_style'] );

[Smurf_UK]

Thanks for your reply Anthony, but I cannot get your example to work.

I understand the concept of what you are doing - which is a start - but there are a couple of things I am not sure about.

The $allStyles variable holds the concatenated and delimted data, so therefore I assume that it is this variable that I am passing to my DB. Is that correct?

If that is correct, do I need to have a row in my table with the same name [i.e. allStyles] for the insert code...

$query=mysql_query("insert into <EXAMPLE TABLE>(allStyles) values('$allStyles')");

...or can I pass the $AllStyles data to any row, and if so how? I feel it would be bad coding practice to do that, because it would be easier to call the DB rows the same as the imported variable, for debugging I guess.

Also, the example code above is stored in a second file [signupck.php] which handles the $_POST from the form [signup.php], error checking on the submitted fields, and then if all is okay the SQL insert. So the last question I have for now is in which file [signup.php or signupck.php] does your example code need to go, and where?

Sorry to be a pain, but I really would like to learn and we all have to start somewhere I guess. Maybe I have bitten off more than I can chew, but I am not about to quit yet :)

[antwan1986]

I understand the concept of what you are doing - which is a start - but there are a couple of things I am not sure about.

Understanding the concept is half the battle, the other half is getting the code to work!The $allStyles variable holds the concatenated and delimited data, so therefore I assume that it is this variable that I am passing to my DB. Is that correct?

Yep, that's right. It holds the checkbox array elements all processed into a single string, so that's the one you'll be passing to the database.If that is correct, do I need to have a row in my table with the same name [i.e. allStyles] for the insert code...

$query=mysql_query("insert into <EXAMPLE TABLE>(allStyles) values('$allStyles')");

...or can I pass the $AllStyles data to any row, and if so how? I feel it would be bad coding practice to do that, because it would be easier to call the DB rows the same as the imported variable, for debugging I guess.

You can use the same column name in the table if you wish. I often do that myself. You don't have to do it, but like you said it's handy sometimes when something goes wrong, at least you'll know the variable name to look for.Also, the example code above is stored in a second file [signupck.php] which handles the $_POST from the form [signup.php], error checking on the submitted fields, and then if all is okay the SQL insert. So the last question I have for now is in which file [signup.php or signupck.php] does your example code need to go, and where?

From what you've described, it sounds like [signup.php] is just the actual form, and all the processing is handled in [signupck.php]. In that case you'll need to put that code in [signupck.php]. As for where to put it, I'm not sure your scripts structure, but if you're in doubt of where it should go, just stick it somewhere near the top. When it comes to inserting it into the SQL, make sure it has a table column, and just put it in like you did with all the other columns in your insert query (remember, the variable is just a string now like everything else!)

I've attached a sample PHP script that I made for you to look at, just run it and see if it helps your understanding any!

Any other problems, just let me know!

Anthony

[Smurf_UK]

Understanding the concept is half the battle, the other half is getting the code to work!

Wow, so I already know half of what there is to know :D I wish that was trueYou can use the same column name in the table if you wish.

Sorry I keep calling the columns "rows". I think this is because in phpMyAdmin, when I look at the structure it show the columns in rows. Told you I was new to this!From what you've described, it sounds like [signup.php] is just the actual form, and all the processing is handled in [signupck.php].

Thats correct, the signupck.php file handles the processing. If I put the code at the top of the script, it will be processed regardless of whether or not the data passes the checks. Now I guess that this is bad practice, so I think I should put this after the ELSE statement, which basically says

// $_POST code here
// Error checking and validation code here. If failed $status=="NOTOK"
if($status<>"OK"){ 
echo "<font face='Verdana' size='2' color=red>$msg</font><input type='button' value='Retry' onClick='history.go(-1)'>";
}else{ // if all validations are passed, process the $AllStyles code and insert data into DB

Is that a better way to do it?I've attached a sample PHP script that I made for you to look at, just run it and see if it helps your understanding any!

Cheers for that. I haven't had a chance to look at this yet, but I guess it will help. I will reply again once I have had a chance to play around with it.

[Smurf_UK]

Well I had a look at your script and after a bit of a false start I got it to work, so thanks for that Anthony. I also understand what it is doing, but I wouldn't have known the code to do it myself.

The thing is though, I need to be able to search the string we created, and return the results based on one or more work styles. I am now thinking that I might have been better storing these in seperate rows, or am I missing the point of an array by storing it seperately?

[antwan1986]

Sorry I keep calling the columns "rows". I think this is because in phpMyAdmin, when I look at the structure it show the columns in rows. Told you I was new to this!

Don't worry about it. In university it took me about 6 months to drill which one is which into my head!Thats correct, the signupck.php file handles the processing. If I put the code at the top of the script, it will be processed regardless of whether or not the data passes the checks. Now I guess that this is bad practice, so I think I should put this after the ELSE statement, which basically says

// $_POST code here
// Error checking and validation code here. If failed $status=="NOTOK"
if($status<>"OK"){ 
echo "<font face='Verdana' size='2' color=red>$msg</font><input type='button' value='Retry' onClick='history.go(-1)'>";
}else{ // if all validations are passed, process the $AllStyles code and insert data into DB

Is that a better way to do it?

The way you've described is perfect. Only process it if the validation on all the fields has been passed, and your ready to start inserting stuff into the database. There's no point in processing it if they didn't fill in the form correctly.Well I had a look at your script and after a bit of a false start I got it to work, so thanks for that Anthony. I also understand what it is doing, but I wouldn't have known the code to do it myself.

Yeah I often nail a concept straight away, but putting it into code is my biggest problem too.The thing is though, I need to be able to search the string we created, and return the results based on one or more work styles. I am now thinking that I might have been better storing these in seperate rows, or am I missing the point of an array by storing it seperately?

Ah, I didn't realise that you wanted this to be searchable. I'm not really up to speed on database searches; it's not an area I've investigated (tomorrow for example, is my first time delving into relational databases). I suppose then that each one would need to be stored in separate rows.

Off the top of my head I see you having a styles table, with all the styles put in it. Then a user table for all your username stuff. Then a middle table called user_styles, and in that middle table you would store the users ID, along with each style they put down.. so for example, if the user with the id of 5 used work styles 2, 6 and 7.. then there would be three rows in this user_styles table:

USER ID ... STYLE_ID
====================
.....5 .............2
-------------------------------
.....5 .............6
-------------------------------
.....5 .............7

I hope that makes sense!

Anthony

[Smurf_UK]

Ah, I didn't realise that you wanted this to be searchable. I'm not really up to speed on database searches; it's not an area I've investigated (tomorrow for example, is my first time delving into relational databases). I suppose then that each one would need to be stored in separate rows.

Yep, I need it to be searchable. In fact, I need it to be a lot more than that as the site I am building is a photographic modelling site.

I don't want the site to be just a portfolio of my work, but also a modelling database were other photographers can come and spot new models that they haven't used before. So what I need is for a model to be able to register, change his\her password and profile, retrieve logon details etc, and also for my visitors to be able to search the database to find a model to suit their needs.

So, for instance, a photographer might need a male model, over 6' tall, with blue eyes, so all of the signup data needs to be searchable.

My background is IT, but I was an eCommerce consultant so I specialise in hardware, infrastructure, DR, security etc, rather than design and code. I have played with PHP for some years, but only when I needed an email script on a website [I have been designing simple websites for many years]. But now I have decided to make a site like this, which I know is a big ask for me. But, like most people, I have stolen some code [it is open source :D] and I am now stipping it back, and re-coding it to fit my purpose. But the code was only a simple sign up form, so the script has already come a long way.

I feel the best way to learn something is to do it. So that is what I am doing. I know that if I was a real coder, I would be tackling this completely differently, but I am working in a modular fashion, i.e. I want toknow how to collect the data from the form, and insert it in to a DB, before I worry about how to get the data back out again to provide a search faciility. Otherwise I am trying to write the whole script in one go, and well, it just would end up a right old mess. So, watch out for more stupid questions from me on this forum!

Anyway, thanks for your help Anthony, and good luck with learning to have a relationship with a database. Oh, sorry, I mean relational databases - Doh! :D

Steve

[andymut]

Hi Smurf_UK,

The first thing I should note is that when you post an array of html checkboxes, they'll appear as an array of checkboxes when PHP gets a hold of them. So the first thing you could do would be this:

$workStyle = $_POST['work_style'];
  var_dump($workStyle);

What you'll find is that the var_dump will bring back the information that the variable is an array with a certain number of elements. Now you want all of those elements to go into a single string of code, so you can put it in the database. The next step is then to cycle through that array, and add each element to the same string, by concatenating them:

// Setting up a blank variable to be used in the coming loop.
  $allStyles = "";

  // For every checkbox value sent to the form.
  foreach ($workStyle as $style) {
    // Append the string with the current array element, and then add a comma and a space at the end.
    $allStyles .= $style . ", ";
  }

If you were to echo that string out now, you'd see all your values seperated by a comma and a space. The next problem is that the very last entry into the string will also put a comma and a space at the end. That's useless because there's no data coming next so lets get rid of it:

// Delete the last two characters from the string.
  $allStyles = substr($allStyles, 0, -2);

Now you've got all the entries into a handy string seperated by commas and spaces! You can just add this to the database like you did with every other string!

A side note is that when your bringing it back out of the database, you can use a function called explode that will search for ", " (the comma and space) and that will make it back into an array for you to play with.

I hope that helps you; any other questions please don't be afraid to ask!

Anthony

@antwan,
This seems to work so thank you... now in my case after inserting i am writing another code that will look at the categories that are checked, loop through them and send email to particular categories...or how do I display the data stored using this code into one word/string at a time?

[antwan1986]

@antwan, This seems to work so thank you... now in my case after inserting i am writing another code that will look at the categories that are checked, loop through them and send email to particular categories...or how do I display the data stored using this code into one word/string at a time?

Hi Andymut, do you have any sample code for the community to look at? I'm still trying to grasp exactly what you're trying to do.

Anthony

[pat123]

dear anthony,
i read your attachment regarding to do inserting multiple checkbox..it is kinda cool..but my problem here is..how to loop the checkbox..i have list of name..about 300++....how to declare the check box? and how can i post checked name to my database? i am really need your help or anyone who read this...thank you~

[pat123]

my problem here is..how to loop the checkbox..i have list of name..about 300++....how to declare the check box? and how can i post checked name to my database? i am really need your help or anyone who read this...thank you~

[Morta]

Anthony ...thanks alot...It helped...I wish to you higher progress...

[Morta]

my problem here is..how to loop the checkbox..i have list of name..about 300++....how to declare the check box? and how can i post checked name to my database? i am really need your help or anyone who read this...thank you~

Hi part123 !!It's easy just use loop:-
if those names from DB or File?
in the two cases ,use while loop like this :

<form action="<?php $_SERVER["PHP_SELF"];?>" method="post">
<ol>
<?php 
$q=mysql_query("select name from namelist",$link);
while($names=mysql_fetch_array($q)){
?>
<li><input type="checkbox" name="fields[]" value="<?php echo $names["name"]; ?>"><?php echo $names["name"]; ?></li>
<?php
};
?>
</ol>
</form>