2 queries --> 1 array

------=_NextPart_000_04B9_01C7365D.16BCD140
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hello,


I need to query two different tables and create one "BIG" array.

I can query both tables, but can't find a way to combine the results in =
one array.
The problem comes in with the second table. It has a weird layout.

The second table only has four fields. They are: contact_id, =
contact_owner, contact_name, and contact_value

a.. The 'contact_id' needs to be linked to 'id' field from the first =
table.
b.. The contact_name is a field I need to use to reference what i'm =
looking for.
example... contact_name HAS values of RANO, PrevSys, =
Supplier, Dynamic
each of them has the same contact_id different =
contact_names with different contact_values


To query the second table I do something like this:

SELECT contact_value FROM egw_addressbook_extra WHERE =
contact_name=3D'Supplier' and contact_id=3D' XXX'

The xxx is the ID from the first table.

-----------------------------------------
Table 1:

ID NAME BUSINESS =
ADDRESS CITY PHONE=20
1 John Smith Smith's Phone Co. =
100 Main St Someplace 555-5555
2 Joe Brown Brown's Shoe Store =
105 Grant St. This City 555-0001




Table 2:

CONTACT_ID CONTACT_NAME =
CONTACT_VALUE =
CONTACT_OWNER
1 PrevSys =
NONE =
4
1 RANO =
100102 =
4 =20
1 Supplier =
TrueValue =
4
1 Dynamic =
192.168.0.0 =
4
2 PrevSys =
OLD POS SYS =
4
2 RANO =
100105 =
4 =20
2 Supplier =
Orgill =
4
2 Dynamic =
192.168.100.0 =
4


I want to query both tables and create a array. The extra fields from =
table two need to have their own field in the array.
I need to call those extra fields in the array by the contact_name =
field...

example.... $myrow[supplier] $myrow[PrepSys] $myrow[name]


I have tried alot of stuff to get this done, but i think i was very off =
task.

Some help/guide is needed.... I'm new!!!!

Thanks...

Matt

=20

------=_NextPart_000_04B9_01C7365D.16BCD140--

Re: 2 queries --> 1 array

------=_NextPart_000_04D0_01C73661.1F610C90
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

I tried a buch of JOINs, but with no luck

The problem comes in with I have to do a different query for each =
contact_name values for every record.

SELECT contact_value FROM egw_addressbook_extra WHERE =
contact_name=3D'Supplier' and contact_id=3D' 1'
SELECT contact_value FROM egw_addressbook_extra WHERE =
contact_name=3D'RANO' and contact_id=3D' 1'
SELECT contact_value FROM egw_addressbook_extra WHERE =
contact_name=3D'PrevSystem' and contact_id=3D' 1'
SELECT contact_value FROM egw_addressbook_extra WHERE =
contact_name=3D'Dynamic' and contact_id=3D' 1'

Then add those values to the array created from query of the first table

Thanks...

Matt
----- Original Message -----=20
From: Natalie Leotta=20
To: Matthew Ferry=20
Sent: Friday, January 12, 2007 3:41 PM
Subject: Re: [PHP-DB] 2 queries --> 1 array


I think this will help, but I'm not sure I understood your question =
correctly.

http://www.w3schools.com/sql/sql_join.asp


On 1/12/07, Matthew Ferry wrote:
Hello,


I need to query two different tables and create one "BIG" array.

I can query both tables, but can't find a way to combine the results =
in one array.
The problem comes in with the second table. It has a weird layout.=20

The second table only has four fields. They are: contact_id, =
contact_owner, contact_name, and contact_value

a.. The 'contact_id' needs to be linked to 'id' field from the =
first table.
b.. The contact_name is a field I need to use to reference what =
i'm looking for.
example... contact_name HAS values of RANO, =
PrevSys, Supplier, Dynamic
each of them has the same contact_id different =
contact_names with different contact_values=20


To query the second table I do something like this:

SELECT contact_value FROM egw_addressbook_extra WHERE =
contact_name=3D'Supplier' and contact_id=3D' XXX'

The xxx is the ID from the first table.=20

-----------------------------------------
Table 1:

ID NAME BUSINESS =
ADDRESS CITY PHONE
1 John Smith Smith's Phone Co. =
100 Main St Someplace 555-5555=20
2 Joe Brown Brown's Shoe Store =
105 Grant St. This City 555-0001




Table 2:

CONTACT_ID CONTACT_NAME =
CONTACT_VALUE =
CONTACT_OWNER=20
1 PrevSys =
NONE =
4
1 RANO =
100102 =
4=20
1 Supplier =
TrueValue =
4
1 Dynamic =
192.168.0.0 =
4
2 PrevSys =
OLD POS SYS =
4=20
2 RANO =
100105 =
4
2 Supplier =
Orgill =
4=20
2 Dynamic =
192.168.100.0 =
4


I want to query both tables and create a array. The extra fields =
from table two need to have their own field in the array.
I need to call those extra fields in the array by the contact_name =
field...

example.... $myrow[supplier] $myrow[PrepSys] $myrow[name]=20


I have tried alot of stuff to get this done, but i think i was very =
off task.

Some help/guide is needed.... I'm new!!!!

Thanks...

Matt







------=_NextPart_000_04D0_01C73661.1F610C90--

Re: 2 queries --> 1 array

Not sure if this is exactly what you're looking for, Matt, but I think it is.

$query = "SELECT t1.NAME, t1.BUSINESS, t1.ADDRESS, t1.CITY, t1.PHONE, t2.CONTACT_NAME, t2.CONTACT_VALUE FROM Table1 as t1, Table2 as t2 WHERE t1.ID = t2.CONTACT_ID"
$result = mysql_query($query);

while ($row = mysql_fetch_assoc($result)) {
// to make it easier to read, I'll assign the values to variables for this demonstration
$name = $row['NAME'];
$business = $row['BUSINESS'];
$address = $row['ADDRESS'];
$city = $row['CITY'];
$phone = $row['PHONE'];
$contactname = $row['CONTACT_NAME'];
$contactvalue = $row['CONTACT_VALUE'];

$bigarray[$name][$contactname] = $contactvalue;
}


$JoeBrownPrevsys = $bigarray['Joe Brown']['Prevsys'];
?>


If that's not right, please re-describe the problem and let's see what we can figure out.

If you have questions about what/how/why/etc... feel free to ask.

-TG

= = = Original message = = =

Hello,


I need to query two different tables and create one "BIG" array.

I can query both tables, but can't find a way to combine the results in one array.
The problem comes in with the second table. It has a weird layout.

The second table only has four fields. They are: contact_id, contact_owner, contact_name, and contact_value

a.. The 'contact_id' needs to be linked to 'id' field from the first table.
b.. The contact_name is a field I need to use to reference what i'm looking for.
example... contact_name HAS values of RANO, PrevSys, Supplier, Dynamic
each of them has the same contact_id different contact_names with different contact_values


To query the second table I do something like this:

SELECT contact_value FROM egw_addressbook_extra WHERE contact_name='Supplier' and contact_id=' XXX'

The xxx is the ID from the first table.

-----------------------------------------
Table 1:

ID NAME BUSINESS ADDRESS CITY PHONE
1 John Smith Smith's Phone Co. 100 Main St Someplace 555-5555
2 Joe Brown Brown's Shoe Store 105 Grant St. This City 555-0001




Table 2:

CONTACT_ID CONTACT_NAME CONTACT_VALUE CONTACT_OWNER
1 PrevSys NONE 4
1 RANO 100102 4
1 Supplier TrueValue 4
1 Dynamic 192.168.0.0 4
2 PrevSys OLD POS SYS 4
2 RANO 100105 4
2 Supplier Orgill 4
2 Dynamic 192.168.100.0 4


I want to query both tables and create a array. The extra fields from table two need to have their own field in the array.
I need to call those extra fields in the array by the contact_name field...

example.... $myrow[supplier] $myrow[PrepSys] $myrow[name]


I have tried alot of stuff to get this done, but i think i was very off task.

Some help/guide is needed.... I'm new!!!!

Thanks...

Matt


___________________________________________________________
Sent by ePrompter, the premier email notification software.
Free download at http://www.ePrompter.com.

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

Re: 2 queries --> 1 array

yeah..that is what i'm trying to do, but

I need to query WHERE contact_name='Supplier' and bring back the value for
contact_value
query WHERE contact_name='RANO' and bring back the value for
contact_value
query WHERE contact_name='Dynamic' and bring back the value
for contact_value
query WHERE contact_name='PrevSys' and bring back the value
for contact_value
Which are all different rows in the second table....
with the same ID field
thats how they are linked to the first table

thanks...

Matt

----- Original Message -----
From:
To:
Cc:
Sent: Friday, January 12, 2007 4:26 PM
Subject: Re: [PHP-DB] 2 queries --> 1 array


> Not sure if this is exactly what you're looking for, Matt, but I think it
is.
>
> > $query = "SELECT t1.NAME, t1.BUSINESS, t1.ADDRESS, t1.CITY, t1.PHONE,
t2.CONTACT_NAME, t2.CONTACT_VALUE FROM Table1 as t1, Table2 as t2 WHERE
t1.ID = t2.CONTACT_ID"
> $result = mysql_query($query);
>
> while ($row = mysql_fetch_assoc($result)) {
> // to make it easier to read, I'll assign the values to variables for
this demonstration
> $name = $row['NAME'];
> $business = $row['BUSINESS'];
> $address = $row['ADDRESS'];
> $city = $row['CITY'];
> $phone = $row['PHONE'];
> $contactname = $row['CONTACT_NAME'];
> $contactvalue = $row['CONTACT_VALUE'];
>
> $bigarray[$name][$contactname] = $contactvalue;
> }
>
>
> $JoeBrownPrevsys = $bigarray['Joe Brown']['Prevsys'];
> ?>
>
>
> If that's not right, please re-describe the problem and let's see what we
can figure out.
>
> If you have questions about what/how/why/etc... feel free to ask.
>
> -TG
>
> = = = Original message = = =
>
> Hello,
>
>
> I need to query two different tables and create one "BIG" array.
>
> I can query both tables, but can't find a way to combine the results in
one array.
> The problem comes in with the second table. It has a weird layout.
>
> The second table only has four fields. They are: contact_id,
contact_owner, contact_name, and contact_value
>
> a.. The 'contact_id' needs to be linked to 'id' field from the first
table.
> b.. The contact_name is a field I need to use to reference what i'm
looking for.
> example... contact_name HAS values of RANO, PrevSys,
Supplier, Dynamic
> each of them has the same contact_id different
contact_names with different contact_values
>
>
> To query the second table I do something like this:
>
> SELECT contact_value FROM egw_addressbook_extra WHERE
contact_name='Supplier' and contact_id=' XXX'
>
> The xxx is the ID from the first table.
>
> -----------------------------------------
> Table 1:
>
> ID NAME BUSINESS
ADDRESS CITY PHONE
> 1 John Smith Smith's Phone Co. 100
Main St Someplace 555-5555
> 2 Joe Brown Brown's Shoe Store 105
Grant St. This City 555-0001
>
>
>
>
> Table 2:
>
> CONTACT_ID CONTACT_NAME
CONTACT_VALUE CONTACT_OWNER
> 1 PrevSys
NONE 4
> 1 RANO
100102 4
> 1 Supplier
TrueValue 4
> 1 Dynamic
192.168.0.0 4
> 2 PrevSys
OLD POS SYS 4
> 2 RANO
100105 4
> 2 Supplier
Orgill 4
> 2 Dynamic
192.168.100.0 4
>
>
> I want to query both tables and create a array. The extra fields from
table two need to have their own field in the array.
> I need to call those extra fields in the array by the contact_name
field...
>
> example.... $myrow[supplier] $myrow[PrepSys] $myrow[name]
>
>
> I have tried alot of stuff to get this done, but i think i was very off
task.
>
> Some help/guide is needed.... I'm new!!!!
>
> Thanks...
>
> Matt
>
>
> ___________________________________________________________
> Sent by ePrompter, the premier email notification software.
> Free download at http://www.ePrompter.com.
>
>

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

Re: 2 queries --> 1 array

query1 = SELECT * FROM egw_addressbook

query2 = SELECT contact_id, contact_value FROM egw_addressbook_extra WHERE
contact_name='Supplier'
query3 = SELECT contact_id, contact_value FROM egw_addressbook_extra WHERE
contact_name='RANO'
query4 = SELECT contact_id, contact_value FROM egw_addressbook_extra WHERE
contact_name='PrevSys'
query5 = SELECT contact_id, contact_value FROM egw_addressbook_extra WHERE
contact_name='Dynamic'


Then some type of array merge....

array1 = query1 + query2 + query3 + query4 + query5

query2 to query5 needs locked to query1 with: WHERE
egw_addressbook.id=egw_addressbook_extra.contact_id


Thanks...

Matt
----- Original Message -----
From:
To:
Cc:
Sent: Friday, January 12, 2007 4:26 PM
Subject: Re: [PHP-DB] 2 queries --> 1 array


> Not sure if this is exactly what you're looking for, Matt, but I think it
is.
>
> > $query = "SELECT t1.NAME, t1.BUSINESS, t1.ADDRESS, t1.CITY, t1.PHONE,
t2.CONTACT_NAME, t2.CONTACT_VALUE FROM Table1 as t1, Table2 as t2 WHERE
t1.ID = t2.CONTACT_ID"
> $result = mysql_query($query);
>
> while ($row = mysql_fetch_assoc($result)) {
> // to make it easier to read, I'll assign the values to variables for
this demonstration
> $name = $row['NAME'];
> $business = $row['BUSINESS'];
> $address = $row['ADDRESS'];
> $city = $row['CITY'];
> $phone = $row['PHONE'];
> $contactname = $row['CONTACT_NAME'];
> $contactvalue = $row['CONTACT_VALUE'];
>
> $bigarray[$name][$contactname] = $contactvalue;
> }
>
>
> $JoeBrownPrevsys = $bigarray['Joe Brown']['Prevsys'];
> ?>
>
>
> If that's not right, please re-describe the problem and let's see what we
can figure out.
>
> If you have questions about what/how/why/etc... feel free to ask.
>
> -TG
>
> = = = Original message = = =
>
> Hello,
>
>
> I need to query two different tables and create one "BIG" array.
>
> I can query both tables, but can't find a way to combine the results in
one array.
> The problem comes in with the second table. It has a weird layout.
>
> The second table only has four fields. They are: contact_id,
contact_owner, contact_name, and contact_value
>
> a.. The 'contact_id' needs to be linked to 'id' field from the first
table.
> b.. The contact_name is a field I need to use to reference what i'm
looking for.
> example... contact_name HAS values of RANO, PrevSys,
Supplier, Dynamic
> each of them has the same contact_id different
contact_names with different contact_values
>
>
> To query the second table I do something like this:
>
> SELECT contact_value FROM egw_addressbook_extra WHERE
contact_name='Supplier' and contact_id=' XXX'
>
> The xxx is the ID from the first table.
>
> -----------------------------------------
> Table 1:
>
> ID NAME BUSINESS
ADDRESS CITY PHONE
> 1 John Smith Smith's Phone Co. 100
Main St Someplace 555-5555
> 2 Joe Brown Brown's Shoe Store 105
Grant St. This City 555-0001
>
>
>
>
> Table 2:
>
> CONTACT_ID CONTACT_NAME
CONTACT_VALUE CONTACT_OWNER
> 1 PrevSys
NONE 4
> 1 RANO
100102 4
> 1 Supplier
TrueValue 4
> 1 Dynamic
192.168.0.0 4
> 2 PrevSys
OLD POS SYS 4
> 2 RANO
100105 4
> 2 Supplier
Orgill 4
> 2 Dynamic
192.168.100.0 4
>
>
> I want to query both tables and create a array. The extra fields from
table two need to have their own field in the array.
> I need to call those extra fields in the array by the contact_name
field...
>
> example.... $myrow[supplier] $myrow[PrepSys] $myrow[name]
>
>
> I have tried alot of stuff to get this done, but i think i was very off
task.
>
> Some help/guide is needed.... I'm new!!!!
>
> Thanks...
>
> Matt
>
>
> ___________________________________________________________
> Sent by ePrompter, the premier email notification software.
> Free download at http://www.ePrompter.com.
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

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

Re: 2 queries --> 1 array

Ok, in your original message you said something about using the name as dominant key (or whatever you want to call it). All the SQL does is gather all the data together in an associated fashion. You can use the array you build to organize the data. I'm still not 100% sure what you're doing, so I can't say this is the best way, but let's look at the exampe I gave and change it to hopefully suit your last description:

$name = $row['NAME'];
$business = $row['BUSINESS'];
$address = $row['ADDRESS'];
$city = $row['CITY'];
$phone = $row['PHONE'];
$contactname = $row['CONTACT_NAME'];
$contactvalue = $row['CONTACT_VALUE'];

// $bigarray[$name][$contactname] = $contactvalue;

$bigarray[$contactname][$name] = $contactvalue;

foreach ($bigarray['Supplier'] as $name => $value) {
echo "Supplier: $name has CONTACT_VALUE = $value
\n";
}

There may be an elegant way to do what you want in SQL too, I'm just not fully groking the situation I think.

-TG

> $name = $row['NAME'];
> $business = $row['BUSINESS'];
> $address = $row['ADDRESS'];
> $city = $row['CITY'];
> $phone = $row['PHONE'];
> $contactname = $row['CONTACT_NAME'];
> $contactvalue = $row['CONTACT_VALUE'];
>
> $bigarray[$name][$contactname] = $contactvalue;


= = = Original message = = =

yeah..that is what i'm trying to do, but

I need to query WHERE contact_name='Supplier' and bring back the value for
contact_value
query WHERE contact_name='RANO' and bring back the value for
contact_value
query WHERE contact_name='Dynamic' and bring back the value
for contact_value
query WHERE contact_name='PrevSys' and bring back the value
for contact_value
Which are all different rows in the second table....
with the same ID field
thats how they are linked to the first table

thanks...

Matt

----- Original Message -----
From:
To:
Cc:
Sent: Friday, January 12, 2007 4:26 PM
Subject: Re: [PHP-DB] 2 queries --> 1 array


> Not sure if this is exactly what you're looking for, Matt, but I think it
is.
>
> > $query = "SELECT t1.NAME, t1.BUSINESS, t1.ADDRESS, t1.CITY, t1.PHONE,
t2.CONTACT_NAME, t2.CONTACT_VALUE FROM Table1 as t1, Table2 as t2 WHERE
t1.ID = t2.CONTACT_ID"
> $result = mysql_query($query);
>
> while ($row = mysql_fetch_assoc($result))
> // to make it easier to read, I'll assign the values to variables for
this demonstration
> $name = $row['NAME'];
> $business = $row['BUSINESS'];
> $address = $row['ADDRESS'];
> $city = $row['CITY'];
> $phone = $row['PHONE'];
> $contactname = $row['CONTACT_NAME'];
> $contactvalue = $row['CONTACT_VALUE'];
>
> $bigarray[$name][$contactname] = $contactvalue;
>
>
>
> $JoeBrownPrevsys = $bigarray['Joe Brown']['Prevsys'];
> ?>
>
>
> If that's not right, please re-describe the problem and let's see what we
can figure out.
>
> If you have questions about what/how/why/etc... feel free to ask.
>
> -TG
>
> = = = Original message = = =
>
> Hello,
>
>
> I need to query two different tables and create one "BIG" array.
>
> I can query both tables, but can't find a way to combine the results in
one array.
> The problem comes in with the second table. It has a weird layout.
>
> The second table only has four fields. They are: contact_id,
contact_owner, contact_name, and contact_value
>
> a.. The 'contact_id' needs to be linked to 'id' field from the first
table.
> b.. The contact_name is a field I need to use to reference what i'm
looking for.
> example... contact_name HAS values of RANO, PrevSys,
Supplier, Dynamic
> each of them has the same contact_id different
contact_names with different contact_values
>
>
> To query the second table I do something like this:
>
> SELECT contact_value FROM egw_addressbook_extra WHERE
contact_name='Supplier' and contact_id=' XXX'
>
> The xxx is the ID from the first table.
>
> -----------------------------------------
> Table 1:
>
> ID NAME BUSINESS
ADDRESS CITY PHONE
> 1 John Smith Smith's Phone Co. 100
Main St Someplace 555-5555
> 2 Joe Brown Brown's Shoe Store 105
Grant St. This City 555-0001
>
>
>
>
> Table 2:
>
> CONTACT_ID CONTACT_NAME
CONTACT_VALUE CONTACT_OWNER
> 1 PrevSys
NONE 4
> 1 RANO
100102 4
> 1 Supplier
TrueValue 4
> 1 Dynamic
192.168.0.0 4
> 2 PrevSys
OLD POS SYS 4
> 2 RANO
100105 4
> 2 Supplier
Orgill 4
> 2 Dynamic
192.168.100.0 4
>
>
> I want to query both tables and create a array. The extra fields from
table two need to have their own field in the array.
> I need to call those extra fields in the array by the contact_name
field...
>
> example.... $myrow[supplier] $myrow[PrepSys] $myrow[name]
>
>
> I have tried alot of stuff to get this done, but i think i was very off
task.
>
> Some help/guide is needed.... I'm new!!!!
>
> Thanks...
>
> Matt


___________________________________________________________
Sent by ePrompter, the premier email notification software.
Free download at http://www.ePrompter.com.

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

Re: 2 queries --> 1 array

------=_NextPart_000_052C_01C73697.1BBFE170
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Here is a copy of what table two looks like

http://www.retailautomation.biz/table2.bmp

Matt

------=_NextPart_000_052C_01C73697.1BBFE170--