splitting an array after an SQL query

Question

Hi im totally stuck, could someone help me out?

I have done a straight forward MySQL query from a database to return all products.

I have used the productGroupDesc to tell me which company and category they belong to. A typical entry looks like

Company - Category

So what im trying to do is categorise the products by first finding which company it belongs to and then the category. Then simply list them.

Any ideas?

This is what i have so far:

global $__CMS_CONN__;
$sql = 'SELECT name FROM isproducts';
$arr = array();

$stmt = $__CMS_CONN__->prepare($sql);
$stmt->execute();

$listOfProducts = array();
while ( $row = $stmt->fetchObject() )
{ 
    $row->prodGroupDescList = explode(' - ',  $row->ProductGroupDescription);
    $listOfProducts[] = $row;
}

So from here i need help in creating the lists based on what they have in their productGroupDesc. Hope that makes sense to someone!

Answer 1

Use explode method to split string into array.

Something like this.

$listOfProducts = array();
while ( $row = $stmt->fetchObject() )
{ 
    $row->prodGroupDescList = explode(' - ',  $row->ProductGroupDescription);
    $listOfProducts[] = $row;
}

Now to access the company and categories retrieve array elements:

$company = $row->prodGroupDescList[0]; // Will always be 1st element
$category = $row->prodGroupDescList[1]; // Will always be 2nd element

Answer 2

Is something like what you wanted?

// Define this somewhere appropriate
function addToCategorisedList( $value, $category, array &$list )
{
    if ( !array_key_exists( $category, $list ) ) {
        $list[$category] = array();
    }
    $list[$category][] = $value;
}

// Then change the while loop to something like this
$categories = array();
$companies = array();

while ( $row = $stmt->fetchOject() )
{
    $row->prodGroupDescList = explode( ' - ',  $row->ProductGroupDescription );
    addToCategorisedList( $row, $$row->prodGroupDescList[0], $companies );
    addToCategorisedList( $row, $$row->prodGroupDescList[1], $categories );
}

I haven't tested this code so please let me know/correct it if there are any bugs in it.