Well I'm still working on this off and on - still having problemos :|
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
