I have seen many threads on stackoverflow about this concern but I couldn’t achieve that so that made this post again. I am using select2 jQuery plugin where I want to insert Tags in SQL using PHP. Like [Data, Data, Data]
I try to do that which I learn through google, well I am professionalize in this, I am new in PHP.
Please help ‘How I Insert this in DB where show , between two words as above I mentioned in Like‘
My Code Is
if(isset($_POST['submit'])) {
$name = "Your_Name";
$email = "Your_Email";
$stmt = $con->prepare("INSERT INTO `test` (`name`, `email`, `color_name`) VALUES (':name',':email',':color')");
foreach ($data as $row)
{
':name' = $name;
':email' = $email;
':color' = 'MYStatus'; //I want mention here Select2 Tags Data and insert in DB where [, (space)] every two words. like [Green, Red, Blue, Pink]
$stmt->execute();
}
}
<!DOCTYPE html>
<html>
<head>
<link rel="stylesheet" type="text/css" href="http://shashani-humanth.github.io/Notebook-AdminPanel/js/select2/select2.css">
</head>
<body>
<form class="bs-example form-horizontal" id="formid" method="POST" onSubmit="return validate();">
<div class="form-group col-sm-6">
<div>
<input type="hidden" name="selectname[]" id="select2-tags" style="width:260px" value="brown"/>
</div>
</div>
<div class="form-group">
<div class="col-lg-offset-2 col-lg-10">
<button type="submit" name="submit" class="btn btn-sm btn-default" id="submit">Save and Publish</button>
</div>
</div>
</form>
<script src="http://shashani-humanth.github.io/Notebook-AdminPanel/js/jquery.min.js"></script>
<script src="http://shashani-humanth.github.io/Notebook-AdminPanel/js/bootstrap.js"></script>
<script src="http://shashani-humanth.github.io/Notebook-AdminPanel/js/select2/select2.min.js"></script>
<script type="text/javascript" language="javascript">
if ($.fn.select2) {
$("#select2-option").select2();
$("#select2-tags").select2({
tags:["Red", "Green", "Blue", "Pink"],
tokenSeparators: [",", " "]}
);
}
</script>
</body>
</html>
Advertisement
Answer
Your form is likely being sent like this:
Array
(
[selectname] => Array
(
[0] => brown,Green,Blue
)
)
So, since you are wanting it to be formatted as brown, Green, Blue, then you can explode() and implode() or just use a str_replace():
# Exploding, imploding
$tags = implode(", ", explode(",", $_POST['selectname'][0]));
# String replace would be
$tags = str_replace(',', ', ', $_POST['selectname'][0]);
If you are trying to split up that string to insert each tag separately, you would use explode() on the comma, then loop the results of the explode().
I would probably use trim() in there to remove empty space, just incase. Also, if you want to make sure they are all formatted the same, you might want to do ucwords() to make sure each word has an uppercase as the first letter (your default value brown is all lower and the reset have the first letters upper).
If you do method 1, you can apply a ucfirst() and trim() if you use array_map() on the exploded string:
# A full combination of functions
$tags = implode(", ", array_map(function($v){
return ucfirst(trim($v));
}, explode(",", $_POST['selectname'][0])));
would give you the string:
Brown, Green, Blue
EDIT:
Since you are actually storing per row, you can fetch back to an array using a select:
function getColorTags($name, $con)
{
# Query by name, just return the color_name field though
$query = $con->prepare("SELECT `color_name` FROM `test` WHERE `name` = ?");
# Execute the query with bind value
$query->execute([$name]);
# Loop the results
while($result = $query->fetch(PDO::FETCH_ASSOC)) {
# Store the tags
$row[] = $result['color_name'];
}
# Return the tags or an empty array
return (isset($row))? $row : [];
}
To use:
# Fetch the tags by name, make sure to inject the database connection
$tags = getColorTags('Some Name', $con);
# Print out the array
print_r($tags);
EDIT #2
To do it both together on the same page, you would just use json_encode():
<?php
function insertTags($data, $email, $name, $con)
{
$stmt = $con->prepare("INSERT INTO `test` (`name`, `email`, `color_name`) VALUES (?,?,?)");
foreach ($data as $string) {
# Explode and filter the tags from the js
$arr = array_filter(array_map('trim', explode(',', $string)));
# Loop the "selectname"
foreach($arr as $tag) {
# Execute all the rows
$stmt->execute([$name, $email, $tag]);
}
}
}
# This fetches using the email as the primary key
function getColorTags($email, $con)
{
# Query by name, just return the color_name field though
$query = $con->prepare("SELECT `color_name` FROM `test` WHERE `email` = ?");
# Execute the query with bind value
$query->execute([$name]);
# Loop the results
while($result = $query->fetch(PDO::FETCH_ASSOC)) {
# Store the tags
$row[] = $result['color_name'];
}
# Return the tags or an empty array
return (isset($row))? $row : [];
}
# Pull these out so you can use them in general
$name = "Your_Name"; // Assuming this is from the session
$email = "Your_Email"; // Assuming this is from the session
# Here is where you insert
if(isset($_POST['submit'])) {
# This will insert your tags separately on new rows
insertTags($_POST['selectname'], $email, $name, $con);
# This will pull them back from the database (helpful if they already have some in there)
$tags = getColorTags($email, $con);
}
# Here you check if there are tags already generated after submission,
# if not, then pull them.
if(!isset($tags))
$tags = getColorTags($email, $con);
?><!DOCTYPE html>
<html>
<head>
<link rel="stylesheet" type="text/css" href="http://shashani-humanth.github.io/Notebook-AdminPanel/js/select2/select2.css">
</head>
<body>
<form class="bs-example form-horizontal" id="formid" method="POST" onSubmit="return validate();">
<div class="form-group col-sm-6">
<div>
<input type="hidden" name="selectname[]" id="select2-tags" style="width:260px" value="brown"/>
</div>
</div>
<div class="form-group">
<div class="col-lg-offset-2 col-lg-10">
<button type="submit" name="submit" class="btn btn-sm btn-default" id="submit">Save and Publish</button>
</div>
</div>
</form>
<script src="http://shashani-humanth.github.io/Notebook-AdminPanel/js/jquery.min.js"></script>
<script src="http://shashani-humanth.github.io/Notebook-AdminPanel/js/bootstrap.js"></script>
<script src="http://shashani-humanth.github.io/Notebook-AdminPanel/js/select2/select2.min.js"></script>
<script type="text/javascript" language="javascript">
if ($.fn.select2) {
$("#select2-option").select2();
$("#select2-tags").select2({
// Now use this native function to echo the array back to JS
tags: <?php echo json_encode($tags) ?>,
tokenSeparators: [",", " "]}
);
}
</script>
</body>
</html>