insert multiple checkbox values into mysql field but add space between each one?

Question

i am allowing my user to insert either one or 4 checkbox values into one field in my database, and later on want to be able to retrieve this data by doing a query for the each of those words, however, when more than one value is inserted into my field it separates these with a comma, which is fine but how do i seperate the words with a space? so i can later on do a search in my query for values containing 'mechanical' or 'non_mechanical' etc.

heres how it is being stored

mechanical,non_mechanical

also would the comma stop me doing a query for all containing the value of 'mechanical' for example as it would have a comma on the end?

html:

<p>mechanical</p>
<input type="checkbox" id="box1" name="scope_type[]" value="mechanical" onClick="showMore(this);">


<p>Non-Mechanical Hire</p>
<input type="checkbox" id="box1" name="scope_type[]" value="non_mechanical" onClick="showMoreMore(this);">


<p>Tools</p>
<input type="checkbox" id="box1" name="scope_type[]" value="tools"  onClick="showMoreMoreMore(this);">

<p>Accessories</p>
<input type="checkbox" id="box1" name="scope_type[]" value="accessories"  onClick="showMoreMoreMoreMore(this);">

mysql:

<?php
session_start();

$db_hostname = 'localhost';
$db_database = 'xxxx'; 
$db_username = 'xxxx';
$db_password = 'xxxx';

$db_server = mysql_connect($db_hostname, $db_username, $db_password)    
        or die("Unable to connect to MySQL: " . mysql_error());

mysql_select_db($db_database)   
or die("Unable to select database: " . mysql_error());

 $scope_type = implode(",",$_POST["scope_type"]);

    $ipaddress = $_SERVER["REMOTE_ADDR"];




$sql="INSERT INTO supplier_scope (user_ip, scope_type)
    VALUES ('$ipaddress', '$scope_type')";  

$result = mysql_query($sql); 

$sql2="UPDATE supplier_session SET form2_completed = 'Yes' WHERE form2_completed = 'No' AND user_IP = '$ipaddress'";

$result2 = mysql_query($sql2); 


 if($result){

    header("Location: index.php?registration=success");

}else {
echo "ERROR";
}
?>

why not using json to store your checkbox values ? – sshet May 12 '14 at 11:24 — sshet, May 12 '14 at 11:24
i've never heard of it and wouldnt know how to use it :( – John Taylor May 12 '14 at 11:25 — John Taylor, May 12 '14 at 11:25
just use in php json_encode($_POST["scope_type"]) – sshet May 12 '14 at 11:26 — sshet, May 12 '14 at 11:26

score 0 · Answer 1 · answered May 12 '14 at 11:25

0

Just use:

$scope_type = implode(" ",$_POST["scope_type"]);

instead of

$scope_type = implode(",",$_POST["scope_type"]);

answered May 12 '14 at 11:25

gbestard

1,177
13
29

score 0 · Answer 2 · edited Sep 14 '18 at 20:18

0

Still you could do a search in table if your values are comma separated in a column.

For that use,

select * 
from TABLENAME 
where FIND_IN_SET('mechanical', COLUMNNAME)

Also storing delimited values in table is not that good. Have a look into this

Is storing a delimited list in a database column really that bad?

edited Sep 14 '18 at 20:18

marc_s

732,580
175
1,330
1,459

answered May 12 '14 at 11:31

shatheesh

633
6
10

D-J · Answer 3 · 2014-05-22T11:59:39.900

0

You can use replace function to use space instead of comma.

edited May 22 '14 at 11:59

answered May 12 '14 at 11:36

D-J

1
1
2

insert multiple checkbox values into mysql field but add space between each one?

3 Answers3