Explode and Implode Strings in MySQL Query

Explode and implode strings in mysql query

Try this:

select concat(last_name," ",first_name) as FullName
from
(
select SUBSTRING_INDEX(SUBSTRING_INDEX(name, ' ', 2), ' ', -1) AS last_name, 
SUBSTRING_INDEX(SUBSTRING_INDEX(name, ' ', 1), ' ', -1) AS first_name
from your_table
) tab

See a sample here

http://sqlfiddle.com/#!2/cd4ee/4

EDIT:

Then a slight modified version will do your work. You can refer the same fiddle for a updated sample as per your need.

select concat(last_name," ",first_name) as FullName
from
(
select right(name,(length(name) - instr(name,' '))) as last_name, 
SUBSTRING_INDEX(SUBSTRING_INDEX(name, ' ', 1), ' ', -1) AS first_name
from tab
) tab1

explode results then implode php

Because every $arr only contains one ingredient, so you're actually implode()ing just one element, echoing it, and then echoing other ingredients not separated by anything, so they show together.

You can add them to an array and then implode them:

$sql = "SELECT items FROM orders";
$result = mysqli_query($connection, $sql);
$array = mysqli_fetch_assoc($result);

$exploded = explode(",", $array['items']);

$ingredients = [];
foreach ($exploded as $row){
    $query = "SELECT food_name FROM foodlist WHERE food_id = $row";
    $res = mysqli_query($connection, $query);
    $arr = mysqli_fetch_assoc($res);

    $ingredients[] = $arr["food_name"];
}

echo implode(", ", $ingredients);

---

PRO TIP, it's a bad idea to have your fields separated by a comma like that in the database. You should normalize that field, have another table called ingredients and a pivot table called order_ingredient containing every ingredient a specific order has, then you can simply JOIN the ingredients by ID and get them all at once, instead of doing N+1 queries which will kill your performance fast.

The structure would look like this:

And the query something like

SELECT ingredient FROM orders o JOIN order_ingredient oi ON oi.order_id = o.id JOIN ingredients i ON oi.ingredient_id = i.id

which will return all your ingredients with just one query.

Imploding an array into a MySQL query

It seems as though you have misunderstood how the IN clause works.

The IN clause will look for what is on the left in the list of values on the right. For example: WHERE id IN (2,3,5) - if id is in that list it will return true. In your case it is the opposite.

Something like this should work for what your after but there are likely to be better alternatives.

$sql = '';
foreach ($keywords AS $keyword)
{
    if ($sql != '')
        $sql .= ' OR ';

    $sql .= "object_title LIKE '%$keyword%'";
}

$query = 'SELECT object_title FROM table WHERE '.$sql;

% is a wildcard.

Just please remember to escape the values first.

Equivalent of explode() to work with strings in MySQL

MYSQL has no explode() like function built in. But you can easily add similar function to your DB and then use it from php queries. That function will look like:

CREATE FUNCTION SPLIT_STRING(str VARCHAR(255), delim VARCHAR(12), pos INT)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(str, delim, pos),
       CHAR_LENGTH(SUBSTRING_INDEX(str, delim, pos-1)) + 1),
       delim, '');

Usage:

SELECT SPLIT_STRING('apple, pear, melon', ',', 1)

The example above will return apple.
I think that it will be impossible to return array in MySQL so you must specify which occurrence to return explicitly in pos. Let me know if you succeed using it.

Explode/Implode List for Mysql WHERE IN ()

As mentioned in the comments, I would loop over your values and add a question mark for every value and then bind the values to these placeholders after you have prepared the statement. Right now you have an sql injection problem.

However, to make sure you get the string you want with the input you seem to be receiving, you could use preg_split() to explode on multiple spaces, tabs or new-line characters.

For example:

$st = trim(" \n fg3456   tg7844  \n  \n de3902  \n ");
$ar = preg_split('#\s+#', $st);
var_dump($ar);
$in = "'".implode("','", $ar)."'";
var_dump($in);

Result:

array(3) {
  [0]=>
  string(6) "fg3456"
  [1]=>
  string(6) "tg7844"
  [2]=>
  string(6) "de3902"
}
string(26) "'fg3456','tg7844','de3902'"

You can use the first part of this to prepare a statement:

$st = trim(" \n fg3456   tg7844  \n  \n de3902  \n ");
$ar = preg_split('#\s+#', $st);
$in = trim(str_repeat('?,', count($ar)), ',');
$sql = "SELECT code, size, price from stock WHERE code IN ($in)";
var_dump($sql);

Result:

string(57) "SELECT code, size, price from stock WHERE code IN (?,?,?)"

By the way, personally I would use PDO as that would make the binding a lot easier.

Array implode to MySql and then retrieve values and explode back to same array not working if commas already existed in string

Instead you can use json_encode and json_decode.

Try this out:

$arr = ["Hello my, name is Steve", "How are you?"]
$data = json_encode($arr); //Insert $data into mysql 
//Then to retrieve values 
$arr2 = json_decode($data); //returns ["Hello my, name is Steve", "How are you?"]

php explode like function in mysql

You can not really "explode" or split on all comma's, but you can split a string on any comma using SUBSTRING_INDEX.

SELECT SUBSTRING_INDEX('New york,America', ',', 1);
-> New york

Implode array and insert dynamic data to mysql database

Something like this:

$names = empty($_POST['name']) ? [] : $_POST['name'];

foreach($names AS $name){
    if (!empty($name)) {
          $test= '['.implode(", ", (array)$name).']';                    
          print_r($test);
          $sql = "INSERT INTO employee_table (name)
                 VALUES ('$test')";
          if ($conn->query($sql) === true) {
            echo ('ok');
         }              
    }
}

I wanted to repost this comment I made:

its a bad idea to store data as a delimited list when you can make it a related table. In any case I would save it as this ,Alex,Brown,Hello,Hugo, with leading and trailing delimiters, that way when you query it you can do this field LIKE '%,Alex,%'. The difference is if you have foo,some,bar and foo,something,bar and you do field LIKE '%some%' note no , you will find both of those some and something. To query the first and last items like I showed above with , they would need the , around them. You can just use trim($field, ',') to remove them before explode etc

UPDATE

And this one

its unclear the structure of $name is it implode($name[$i]) or impode($name) You use the first one in your code which implies name is [['foo','bar'], [...]] not ['foo','bar', ...] If it's the second your also storing it multiple times which you probably don't want.

So you may be able to do just this:

//$_POST['name'] = ['foo','bar', ...]

//remove the loop

 //we can assign $name in the if condition and save a line or 2
 //the second part, the assignment, will always return true.
if (!empty($_POST['name']) && $name = $_POST['name']) {
      $test= '['.implode(',', (array)$name).']';   //changed mainly this line                 
      print_r($test);
      $sql = "INSERT INTO employee_table (name) VALUES ('$test')";
      if ($conn->query($sql) === true) {
         echo 'ok';
      }              
}

With no loop, because when you loop over the count of names, your inserting the same data each time, up to the number of items in the names variable.

Explaining your code

So with my example data $_POST['name'] = ['foo','bar', ...] and a simplified version of your original code, you would be doing this:

Assuming you meant implode($name) and not implode($name[$i]) in your original code, which is the only sane thing if your data looks like my example data

 //canned example data
$name = ['foo','bar'];

for ($i = 0; $i < count($name); $i++) {
    if ($name[$i] != "") {
        $test= implode(", ", (array)$name);  //changed from $name[$i] 

        //just output this stuff so we can see the results                 
        print_r($test);
        echo "\nINSERT INTO employee_table (name) VALUES ('$test')\n";
    }
}

Outputs:

foo, bar
INSERT INTO employee_table (name) VALUES ('foo, bar')
foo, bar
INSERT INTO employee_table (name) VALUES ('foo, bar')

Sandbox

If should be obvious but if you changed this line $test= implode(", ", (array)$name); to $test= '['.implode(',', (array)$name).']; in the above code the output would be this:

foo, bar
INSERT INTO employee_table (name) VALUES ('[foo,bar]')
foo, bar
INSERT INTO employee_table (name) VALUES ('[foo,bar]')

Which still saves it more then one time. So we need to dump that loop, which basically forces us into the code I put at the top of this update.

Hopefully that all makes sense.

Cheers

UPDATE an imploded multiple selection into database

There are few issues with your code, such as:

• $tags is not an array. See the below statement in your while() loop,

  $tags = $row['tags'];
  

  So you can't use it in foreach loop like that. Use explode() function to split the string and get the tags in an array, like this:

  $tags = explode(",",$row['tags']);
  

  And then use this $tags array in your form, which is explained below.

• Syntax error here,

  $tags = implode(",",$_POST['tags'];
                                    ^ missing closing )
  

• Even you get the tags as an array(as pointed above), you don't have to use that foreach loop either, it will unnecessarily append additional/redundant tags in your <select> element. better use in_array() function to check the tag value is present in $tags array or not and make it selected accordingly

• value attribute is missing from <option> tags.

• Place the SELECT operation below the UPDATE operation, otherwise you'll get old tag values from the SELECT operation even if you update the tags using the form.

So your code should be like this:

if(isset($_POST['update'])){
    $tags = implode(",",$_POST['tags']);
    $query = "UPDATE data SET tags= '$tags' WHERE id = $id";
    mysqli_query($dbc, $query);
}

$query = "SELECT * FROM data WHERE id = $id";
$edit = mysqli_query($dbc, $query);
while($row = mysqli_fetch_assoc($edit)){
    $tags = explode(",",$row['tags']);
}

<form method="post" action="index.php">
    <select id="tags" name="tags[]" multiple="multiple">
        <option value="tag1"<?php if(in_array('tag1', $tags)){ echo ' selected="selected"'; } ?>>tag1</option>
        <option value="tag2"<?php if(in_array('tag2', $tags)){ echo ' selected="selected"'; } ?>>tag2</option>
        <option value="tag3"<?php if(in_array('tag3', $tags)){ echo ' selected="selected"'; } ?>>tag3</option>
        <option value="tag4"<?php if(in_array('tag4', $tags)){ echo ' selected="selected"'; } ?>>tag4</option>
    </select>
    <button type="submit" name="update">Submit</button>
</form>

---

Related Topics