RE: Stumped with proper query and display

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



I never saw this email come across - I think it got caught in spam software on 4-1

********************************************************

Well I'm still working on this off and on - still having problems :|

I couldnt get the foreach to work properly..I keep getting:

"Invalid argument supplied for foreach() ".

I've tried lots of different stuff but here's an example:

*****************************************************

$query = "SELECT * FROM cust";
$res = mysql_query($query, $ups_conn) or die(mysql_error());

$query2 = "SELECT * FROM contact";
$res2 = mysql_query($query2, $ups_conn) or die(mysql_error());

while ($row = mysql_fetch_assoc($res)) {  
	while ($row2 = mysql_fetch_assoc($res2)) {
                         $choice_array[] = $row2['choice'];
  //Consolidate your choices into an associative array
foreach($choice_array as $choices) {
	 //Get Unique ID for consolodiation
	$personID= $choices['id'];
	//Save value of fields being consolidated
	$choicesList[$personID]['choice'] .= $choices['choice'].', ';
	//Add Names and Company
	$choicesList[$personID]['Name'] = $row['name'];
	$choicesList[$personID]['company'] = $row['company'];
                }
}
}

Here's what I have been trying:

*****************************************************

while ($row = mysql_fetch_assoc($res)) {  
	while ($row2 = mysql_fetch_assoc($res2)) {
		$choice_array[$row2['id']][] = $row2['choice'];
}
}

print("<pre>");
print_r($choice_array);
print("</pre>");

*****************************************************
Yields this:

Array
(
    [5] => Array
        (
            [0] => send_rep
        )
    [4] => Array
        (
            [0] => 2-3
            [1] => send_rep
        )
)
*****************************************************

so I THINK I am close if I can figure out how to grab the cust table info based on id and iterate through the $choice_array and display it correctly.  I also couldnt figure out how to do only one on query instead of two :(  But I wont sweat the small stuff.

table structure:

CUST TABLE

id: 5
name: jane doe
company: ibm

id: 4
name:  mignon hunter
company: tic

CONTACT TABLE

id: 4 
choice: 2-3

id: 4
choice: send_rep

id: 5
choice: send_rep

*****************************************************

Any ideas or suggestions as always, will be greatly appreciated.  Would love to learn a more elegant way do to this.

Thx
Mignon

>>> Brent Baisley <brent@xxxxxxxxxxxx> 03/26/04 12:13PM >>>
The way I handle queries like this is to use an associative array with 
the ID as the named index key. You then loop through the data 
consolidating the choices and linking the other data based on id. Since 
you are using a named index, it doesn't matter how your data is sorted.

//Consolidate your choices into an associative array
foreach($choiceData as $choices) {
	//Get Unique ID for consolodiation
	$personID= $choices['id'];
	//Save value of fields being consolidated
	$choicesList[$personID]['choice'] .= $choices['choice'].', ';
	//Add Names and Company
	$choicesList[$personID]['Name'] = $names['Name'];
	$choicesList[$personID]['company'] = $names['company'];
}

You should now have an array ($choicesList) containing your 
consolidated data. Each array element will contain and array with three 
items: choices, name, company. The 'choices' will have an extra ', ' at 
the end, but that's easy to get rid of with rtrim().

On Mar 26, 2004, at 10:07 AM, Mignon Hunter wrote:

> Can someone please help me or direct me to some scripts that might get 
> me unstuck, as I've not done this kind of query before.
>
> To simplify:
>
> Table1
> id                     1
> Name               John Doe
> company          IBM
>
> Table 2
> id                    1
> choice             choice #1
> id                    1
> choice              choice #2
>
> So I have 2 records in table 2 that I need to tie in with id # 1 in 
> table 1.
>
> What's the smartest way to do the query ? I have 5 different tables I 
> need to query.  I guess I can get the data I want by selecting all 
> from the 5 tables then parsing through like:
>
> while ($row = mysql_fetch_assoc($res)) {
> foreach Table1.id {
> if Table1.id == Table2.id{
> echo <tr>
> }}}
>
> I could probably manage if I do 5 different queries with 5 different 
> results sets, but that is obviously inelegant and would create more 
> overhead
>
> I need to display as:
>
> John Doe           IBM         Choice #1, Choice #2
>
> Am I even close ?  Need some help with the logic...
>
> Thx for any guidance
>
> mignon
>
> -- 
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php 
>
>
>
-- 
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux