phpmyadmin Question posted in
The official documentation can be found here.

How Can I Retrieve hass or comma separated value from database using codeigniter? – Phpmyadmin

I am Involve a CodeIgniter Project.In this project another developer insert cat_id in the database include # separated like #1#. when insert two cat_id the value store in database like #1##2#.

Now I want to retrieve the cat_id and join it to the another table.

Tags:

3

Answers


  1. 0

    Ok, I’ve made a mistake in my first attempt (find bellow), this hopfully this should work:

    $text = "#1##2##10000";
$ids = array_filter(explode('#', $text));

// output
array(3) { [1]=> string(1) "1" [3]=> string(1) "2" [5]=> string(5) "10000" }

    Bonus if you want to reset the array keys to start from 0 again:

    $ids = array_values(array_filter(explode('#', $text)));

// output
array(3) { [0]=> string(1) "1" [1]=> string(1) "2" [2]=> string(5) "10000" }

    Old incorrect answer:

    $text = "#1##2#";

$ids = [];
for ($i=0; $i < strlen($text); $i++) { 
    if ($text[$i] != '#') {
        $ids[] = $text[$i];
    }
}

var_dump($ids);
// produced result
array(2) { [0]=> string(1) "1" [1]=> string(1) "2" }
    Login or Signup to reply.
  2. 0

    I want to retrieve 1 and 2 form #1##2# . I don’t change the database
    or insert code

    I strongly suggest you into changing your PHP code and MySQL tables into supporting normalisation.
    Because the needed query to fetch (“parse”) a cat_id number from #1##2# is a total nightmare and will not perform well.
    The query looks more or less like this without the extra join to a other table.

    SELECT 
  DISTINCT
   REVERSE(
      REVERSE(
     SUBSTRING_INDEX(SUBSTRING_INDEX(<table>.<column>, '##', rows.row), '##', -1) 
   ) >> 1 ) AS cat_id
FROM (
  SELECT 
   @row := @row + 1 AS ROW
  FROM (
    SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
  ) row1
  CROSS JOIN (
    SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
  ) row2  
  CROSS JOIN (
    SELECT @row := 0 
  ) init_user_params
)
 ROWS
CROSS JOIN 
 <table>

    But then i feel the next question coming.

    How can i convert that SQL into codeignitor’s code?

    So now a PHP example to parse out cat_ids

    PHP code 

    <?php
  $text = "#1##2##10000";

  $cat_ids = explode("##", trim($text, '#'));

  var_dump($cat_ids);
?>

    Result

    array(3) {
  [0]=>
  string(1) "1"
  [1]=>
  string(1) "2"
  [2]=>
  string(5) "10000"
}
    Login or Signup to reply.
  3. 0

    You can try this,

    $str = "#1##2#";
preg_match_all('|d+|',$str,$matches);
print_r($matches);

    Here is working demo.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search