[RESOLVED] Query to an array problem

[micmac]

Hi all,
If i have the following query:

// Get company in relation to type
$strQuery = "select * ";
$strQuery = $strQuery."from pm_company ";
$strQuery = $strQuery."where pm_company_type='client' ";
$strQuery = $strQuery."order by pm_company_name";

$objRS = ExecuteQuery($strQuery, DBSERVER, DBUID, DBPWD, DBDATABASE);

$intNumResults = mysql_num_rows ($objRS);

if ($intNumResults > 0)
{

while ($row = mysql_fetch_object($objRS)) 
{ 
	$company_details['pm_company_id'] = stripslashes($row->pm_company_id);
	$company_details['pm_company_name'] = stripslashes($row->pm_company_name);
	$company_details['pm_company_address_1'] = stripslashes($row->pm_company_address_1);
	$company_details['pm_company_state'] = stripslashes($row->pm_company_state);

	echo $company_details['pm_company_name']."<br/>";

	// Get contact details in relation to company
	$strQuery = "select * ";
	$strQuery = $strQuery."from pm_users ";
	$strQuery = $strQuery."where pm_company_id = '".$company_details['pm_company_id']."' ";
	$strQuery = $strQuery."order by pm_user_id";	

	$objRS2 = ExecuteQuery($strQuery, DBSERVER, DBUID, DBPWD, DBDATABASE);

	$intNumResults2 = mysql_num_rows ($objRS2);

	if ($intNumResults2 > 0)
	{
		while ($row2 = mysql_fetch_object($objRS2)) 
		{
			$contact_user_details['pm_user_id'] = $row2->pm_user_id;
			$contact_user_details['pm_first_name'] = stripslashes($row2->pm_first_name);
			$contact_user_details['pm_last_name'] = stripslashes($row2->pm_last_name);
			$contact_user_details['pm_email'] = stripslashes($row2->pm_email);	

			echo "&nbsp;&nbsp;".$contact_user_details['pm_first_name']."<br/>";

		}			

	} else {
		// No contacts results			
		return $error['null_results'];
	} 

$result[] = array("company_details" => $company_details, "contact_user_details" => $contact_user_details);
}

echo "<pre>";
print_r($result);
echo "</pre>";		
}

how can i get my output to look like this:

Array
(
    [0] => Array
        (
            [company_details] => Array
                (
                    [pm_company_id] => 4
                    [pm_company_name] => Jina Life
                    [pm_company_address_1] => 
                    [pm_company_state] => 
                )

        [0] => Array
            (
                [contact_user_details] => Array
                    (
                        [pm_user_id] => 7
                        [pm_first_name] => Brad
                        [pm_last_name] => Smith
                        [pm_email] => [e-mail]
                    )

            )
        [1] => Array
            (
                [contact_user_details] => Array
                    (
                        [pm_user_id] => 14
                        [pm_first_name] => Michael
                        [pm_last_name] => Jones
                        [pm_email] => [e-mail]
                    )

            )				

    )

[1] => Array
    (
        [company_details] => Array
            (
                [pm_company_id] => 52
                [pm_company_name] => Wineezy
                [pm_company_address_1] => 
                [pm_company_state] => 
            )

        [0] => Array
            (
                [contact_user_details] => Array
                    (
                        [pm_user_id] => 15
                        [pm_first_name] => Duncan
                        [pm_last_name] => Norman
                        [pm_email] => [e-mail]
                    )

            )

    )

)

at the moment i am getting this:

Array
(
    [0] => Array
        (
            [company_details] => Array
                (
                    [pm_company_id] => 4
                    [pm_company_name] => Jina Life
                    [pm_company_address_1] => 
                    [pm_company_state] => 
                )

        [0] => Array
            (
                [contact_user_details] => Array
                    (
                        [pm_user_id] => 7
                        [pm_first_name] => Brad
                        [pm_last_name] => Smith
                        [pm_email] => [e-mail]
                    )

            )				

    )

[1] => Array
    (
        [company_details] => Array
            (
                [pm_company_id] => 52
                [pm_company_name] => Wineezy
                [pm_company_address_1] => 
                [pm_company_state] => 
            )

        [0] => Array
            (
                [contact_user_details] => Array
                    (
                        [pm_user_id] => 15
                        [pm_first_name] => Duncan
                        [pm_last_name] => Norman
                        [pm_email] => [e-mail]
                    )

            )

    )

)

You will notice i can't get the following row result:

            [1] => Array
                (
                    [contact_user_details] => Array
                        (
                            [pm_user_id] => 14
                            [pm_first_name] => Michael
                            [pm_last_name] => Jones
                            [pm_email] => [e-mail]
                        )

            )

If i echo the results it all works fine, i just can't seem to loop through the second query.
Any ideas?

Cheers

[bogu]

I dont know that your first query works like u expect, u need to use concat this two parts:

// Get company in relation to type
$strQuery = "select * ";
$strQuery = $strQuery."from pm_company ";
$strQuery = $strQuery."where pm_company_type='client' ";
$strQuery = $strQuery."order by pm_company_name";

/* ... */

// Get contact details in relation to company
$strQuery = "select * ";
$strQuery = $strQuery."from pm_users ";
$strQuery = $strQuery."where pm_company_id = '".$company_details['pm_company_id']."' ";
$strQuery = $strQuery."order by pm_user_id";

like this:

// Get company in relation to type
$strQuery = "select * ";
$strQuery.= $strQuery."from pm_company ";
$strQuery.= $strQuery."where pm_company_type='client' ";
$strQuery.= $strQuery."order by pm_company_name";

/* ... */

// Get contact details in relation to company
$strQuery = "select * ";
$strQuery.= $strQuery."from pm_users ";
$strQuery.= $strQuery."where pm_company_id = '".$company_details['pm_company_id']."' ";
$strQuery.= $strQuery."order by pm_user_id";

[micmac]

Thanks for the reply.
The query works fine as is. When i echo the results i get the correct output, i just can't replicate it via print_r($result);

[bogu]

Yes is working I didnt read it corectly:

I thought this:

$strQuery = "select * "; 
$strQuery = $strQuery."from pm_company "; 

is this:

$strQuery = "select * "; 
$strQuery = "from pm_company "; 

and I was suggesting, this:

$strQuery = "select * "; 
$strQuery.= "from pm_company "; 

my bad, sry ...

Back to your output:

// Get company in relation to type
$strQuery = "select * ";
$strQuery.= "from pm_company ";
$strQuery.= "where pm_company_type='client' ";
$strQuery.= "order by pm_company_name";

$objRS = ExecuteQuery($strQuery, DBSERVER, DBUID, DBPWD, DBDATABASE);

$intNumResults = mysql_num_rows ($objRS);

if ($intNumResults > 0)
{
	$i = 0;
	while ($row = mysql_fetch_object($objRS)) 
	{ 
		$company_details[$i]['company_details']['pm_company_id'] = stripslashes($row->pm_company_id);
		$company_details[$i]['company_details']['pm_company_name'] = stripslashes($row->pm_company_name);
		$company_details[$i]['company_details']['pm_company_address_1'] = stripslashes($row->pm_company_address_1);
		$company_details[$i]['company_details']['pm_company_state'] = stripslashes($row->pm_company_state);

	// Get contact details in relation to company
	$strQuery = "select * ";
	$strQuery.= "from pm_users ";
	$strQuery.= "where pm_company_id = '".$company_details['pm_company_id']."' ";
	$strQuery.= "order by pm_user_id";	

	$objRS2 = ExecuteQuery($strQuery, DBSERVER, DBUID, DBPWD, DBDATABASE);

	$intNumResults2 = mysql_num_rows ($objRS2);

	if ($intNumResults2 > 0)
	{
		$j = 0;
		while ($row2 = mysql_fetch_object($objRS2)) 
		{
			$company_details[$i][$j]['contact_user_details']['pm_user_id'] = $row2->pm_user_id;
			$company_details[$i][$j]['contact_user_details']['pm_first_name'] = stripslashes($row2->pm_first_name);
			$company_details[$i][$j]['contact_user_details']['pm_last_name'] = stripslashes($row2->pm_last_name);
			$company_details[$i][$j]['contact_user_details']['pm_email'] = stripslashes($row2->pm_email);	
			$j++;	
		}			
	} 
	$i++;
}

echo "<pre>";
print_r($company_details);
echo "</pre>";		
}

dont know if its bug free, I writed here on textarea ...

From your desire output I dont know why is this index {'contact_user_details'} need it because user details already have 0,1,2 index and when u parse your array u can tell like this:

if ($key == 'company_details') {/* company details*/}
else {/* users details*/}

[micmac]

Thanks bogu...that definitely got it working 🙂
For anyone else that is interested the only addition was to the following:

$strQuery.= "where pm_company_id = '".$company_details['pm_company_id']."' ";

should be:

$strQuery.= "where pm_company_id = '".$company_details[$i]['company_details']['pm_company_id']."' ";

Cheers