MySQL CONCAT_WS three columns, echo result in foreach loop after explode()

Question

I have this as result from MySQL (stored as $result)

+-------------------------------------------------------------------------+
| CONCAT_WS('::', p.isMobile, p.contact_phone_id, p.contact_phone_number) |
+-------------------------------------------------------------------------+
| 0::1::123                                                               |
| 1::2::456                                                               |
| 0::3::789                                                               |
| 1::4::987                                                               |
| 0::5::654                                                               |
| 0::6::321                                                               |
| 1::7::123                                                               |
| 1::11::456                                                              |
+-------------------------------------------------------------------------+

Then in a while loop I use explode() for each row like: explode('::', $result). How can I use foreach() to output data like this (first three rows in while iteration as example):

Row 1: The first column is 0, the second column is 1, the third column is 123
Row 2: The first column is 1, the second column is 2, the third column is 456
Row 3: The first column is 0, the second column is 3, the third column is 789

Why are you concatenating your column values together just to split them apart again? — George, Oct 23 '14 at 10:07
Because in my project, there is other columns aswell but for the purpose of this thread I excluded that code. — David, Oct 23 '14 at 10:09

score 0 · Answer 1 · answered Oct 23 '14 at 10:28

There's really no good reason to use a foreach for the output here. It'd be much easier to just explode(), and then output the fields as you want them (note that I constructed an array and used array_shift() over it to simulate fetching rows from a database):

<?php
$data = ['0::1::123', '1::2::456', '0::3::789', '1::4::987', '0::5::654', '0::6::321', '1::7::123', '1::11::456'];

$rownum = 0;
while($row = array_shift($data)) {
    echo "Row $rownum: ";
    $fields = explode('::', $row);
    echo "The first column is {$fields[0]}, the second column is {$fields[1]}, the third column is {$fields[2]}";
    echo "\n";
    ++$rownum;
}

However, if you're really attached to using foreach for the problem, you'll first want to construct yourself an array of "position names" (first, second, third, etc.), and then loop over THAT, and pull the same key from the $row array as you generate the output:

<?php
$data = ['0::1::123', '1::2::456', '0::3::789', '1::4::987', '0::5::654', '0::6::321', '1::7::123', '1::11::456'];

$positions = ['first', 'second', 'third'];

$rownum = 0;
while($row = array_shift($data)) {
    echo "Row $rownum: ";
    $fields = explode('::', $row);
    $output = "";
    foreach($positions as $pos => $posName) {
        $output .= "the {$posName} column is {$fields[$pos]}, ";
    }
    $output = substr($output, 0, -2); // trim that trailing comma

    echo "$output\n";
    ++$rownum;
}

score 0 · Answer 2 · answered Oct 23 '14 at 10:29

0

Try this.

$rowNumber = 1;
foreach($result as $value)
{
    $field = explode('::', $value);
    echo "Row {$rowNumber}: The first column is {$filed[0]}, the second column is {$field[1]}, the third column is {$field[2]}\n";

    $rowNumber++;
}

answered Oct 23 '14 at 10:29

Harley

151
5

MySQL CONCAT_WS three columns, echo result in foreach loop after explode()

2 Answers2