Search code examples
phpsqlmergerows

Separate merged SQL rows with a comma


I'm having a bit of trouble getting my retrieved values from an SQL query into the correct format.

I've managed to join multiple rows into the one value, however I am not sure how to make it separate each of the values with a comma. Essentially I need all the ID's of a product to be retrieved as, for example, if the database had values of '5,6,9,1' '1,3,4' and '2,1' I want it to throw a comma in between each like -> '5,6,9,1,1,3,4,2,1' instead is doing something more like -> '5,6,911,3,42,1' which is what it is doing at the moment.

The code I'm using is below. Any help would be greatly appreciated.

$hist = "SELECT ORDITEMS FROM cust_orderc WHERE ORDDATE > 
to_date('".$olddate."','dd/mm/yyyy')";


$histitem = OCIParse($db, $hist);
OCIExecute($histitem);

while($row = oci_fetch_array($histitem)){
    $pastitem .= $row['ORDITEMS'];  
}

echo "$pastitem";

Solution

  • You can do same in oracle using LISTAGG

    $hist = "SELECT LISTAGG(ORDITEMS) as ORDITEMS FROM cust_orderc WHERE ORDDATE > to_date('".$olddate."','dd/mm/yyyy')";
    

    Edit OR PHP way

    $pastitem = '';
    while($row = oci_fetch_array($histitem)){
        $pastitem .= $row['ORDITEMS'] . ',';  
    }
    $pastitem = trim($pastitem, ",");
    echo $pastitem;