RE: How to access an array variables in a single query
Thanks. I might actually adapt our code, in my infinite free time.
Regards,
Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
> -Original Message-
> From: Brent Baisley [mailto:[EMAIL PROTECTED]
> Sent: Monday, January 08, 2007 10:13 AM
> To: Jerry Schwartz; 'VenuGopal Papasani'; 'ViSolve DB Team'
> Cc: mysql@lists.mysql.com; 'bharath kumar'
> Subject: Re: How to access an array variables in a single query
>
> If this is in PHP, just use implode to create the string. The
> there is no need to strip trailing commas and no loops.
> $instring= implode('","', $a);
>
> As for the query, where are you getting your "1000" values
> from? You can do joins on queries if your values are the
> result of a some
> complex query.
>
>
> - Original Message -
> From: "Jerry Schwartz" <[EMAIL PROTECTED]>
> To: "'VenuGopal Papasani'" <[EMAIL PROTECTED]>;
> "'ViSolve DB Team'" <[EMAIL PROTECTED]>
> Cc: ; "'bharath kumar'" <[EMAIL PROTECTED]>
> Sent: Monday, January 08, 2007 9:38 AM
> Subject: RE: How to access an array variables in a single query
>
>
> > Within the limits of an overall query, the IN clause can be
> arbitrarily
> > long. You'll use a loop in your program to construct a
> string such as
> > $instring .= "'$a[$i]',"
> > and then remove the unneeded trailing comma.
> >
> > Our code here does that with very large lists that are the
> result of a
> > query. I don't like it, because it might fail mysteriously
> if the query gets
> > too long, but I inherited it and haven't thought of a good
> way to rewrite
> > it.
> >
> > Regards,
> >
> > Jerry Schwartz
> > Global Information Incorporated
> > 195 Farmington Ave.
> > Farmington, CT 06032
> >
> > 860.674.8796 / FAX: 860.674.8341
> >
> >
> >> -Original Message-
> >> From: VenuGopal Papasani [mailto:[EMAIL PROTECTED]
> >> Sent: Saturday, January 06, 2007 4:22 AM
> >> To: ViSolve DB Team
> >> Cc: mysql@lists.mysql.com; bharath kumar
> >> Subject: Re: How to access an array variables in a single query
> >>
> >> but that is not static for 3 variables what if array contains
> >> 1000 elements
> >> in cannot be used in such cases
> >>
> >> Thanks and Regards,
> >> venu
> >>
> >> On 1/6/07, ViSolve DB Team <[EMAIL PROTECTED]> wrote:
> >> >
> >> > Hi,
> >> >
> >> > IN operator will do.
> >> >
> >> > For Instance.
> >> >
> >> > SELECT * FROM TABLE1 WHERE id IN ('i1','i2','i3');
> >> >
> >> >
> >> > Thanks
> >> > ViSolve DB Team.
> >> >
> >> > - Original Message -
> >> > From: "VenuGopal Papasani" <[EMAIL PROTECTED]>
> >> > To:
> >> > Cc: "bharath kumar" <[EMAIL PROTECTED]>
> >> > Sent: Saturday, January 06, 2007 2:07 PM
> >> > Subject: How to access an array variables in a single query
> >> >
> >> >
> >> > > Dear all,
> >> > > I have got an array variable say a[]={"i1","i2",i3""}
> >> now i need to
> >> > get
> >> > > the values from the database that matches i1,i2,i3 for example
> >> > >
> >> > > select * from tablename where
> i1=a[1] and
> >> > > simillaryly to a[2] and a[3] but my list is too big one
> >> more option i
> >> > can
> >> > > write it through loop but it takes time.Is there any
> >> possibility of
> >> > > writing
> >> > > a single query to retrieve all that data.
> >> > >
> >> > > Awaiting your reply
> >> > >
> >> > > Thanks and Regards,
> >> > > venu
> >> > >
> >> > > --
> >> > > ,
> >> > > ?? ???.
> >> > >
> >> >
> >> >
> >>
> >>
> >> --
> >> ,
> >> ?? ???.
> >>
> >
> >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
> >
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to access an array variables in a single query
If this is in PHP, just use implode to create the string. The there is no need
to strip trailing commas and no loops.
$instring= implode('","', $a);
As for the query, where are you getting your "1000" values from? You can do joins on queries if your values are the result of a some
complex query.
- Original Message -
From: "Jerry Schwartz" <[EMAIL PROTECTED]>
To: "'VenuGopal Papasani'" <[EMAIL PROTECTED]>; "'ViSolve DB Team'" <[EMAIL
PROTECTED]>
Cc: ; "'bharath kumar'" <[EMAIL PROTECTED]>
Sent: Monday, January 08, 2007 9:38 AM
Subject: RE: How to access an array variables in a single query
Within the limits of an overall query, the IN clause can be arbitrarily
long. You'll use a loop in your program to construct a string such as
$instring .= "'$a[$i]',"
and then remove the unneeded trailing comma.
Our code here does that with very large lists that are the result of a
query. I don't like it, because it might fail mysteriously if the query gets
too long, but I inherited it and haven't thought of a good way to rewrite
it.
Regards,
Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
-Original Message-
From: VenuGopal Papasani [mailto:[EMAIL PROTECTED]
Sent: Saturday, January 06, 2007 4:22 AM
To: ViSolve DB Team
Cc: mysql@lists.mysql.com; bharath kumar
Subject: Re: How to access an array variables in a single query
but that is not static for 3 variables what if array contains
1000 elements
in cannot be used in such cases
Thanks and Regards,
venu
On 1/6/07, ViSolve DB Team <[EMAIL PROTECTED]> wrote:
>
> Hi,
>
> IN operator will do.
>
> For Instance.
>
> SELECT * FROM TABLE1 WHERE id IN ('i1','i2','i3');
>
>
> Thanks
> ViSolve DB Team.
>
> - Original Message -
> From: "VenuGopal Papasani" <[EMAIL PROTECTED]>
> To:
> Cc: "bharath kumar" <[EMAIL PROTECTED]>
> Sent: Saturday, January 06, 2007 2:07 PM
> Subject: How to access an array variables in a single query
>
>
> > Dear all,
> > I have got an array variable say a[]={"i1","i2",i3""}
now i need to
> get
> > the values from the database that matches i1,i2,i3 for example
> >
> > select * from tablename where i1=a[1] and
> > simillaryly to a[2] and a[3] but my list is too big one
more option i
> can
> > write it through loop but it takes time.Is there any
possibility of
> > writing
> > a single query to retrieve all that data.
> >
> > Awaiting your reply
> >
> > Thanks and Regards,
> > venu
> >
> > --
> > ,
> > ?? ???.
> >
>
>
--
,
?? ???.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to access an array variables in a single query
Within the limits of an overall query, the IN clause can be arbitrarily
long. You'll use a loop in your program to construct a string such as
$instring .= "'$a[$i]',"
and then remove the unneeded trailing comma.
Our code here does that with very large lists that are the result of a
query. I don't like it, because it might fail mysteriously if the query gets
too long, but I inherited it and haven't thought of a good way to rewrite
it.
Regards,
Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
> -Original Message-
> From: VenuGopal Papasani [mailto:[EMAIL PROTECTED]
> Sent: Saturday, January 06, 2007 4:22 AM
> To: ViSolve DB Team
> Cc: mysql@lists.mysql.com; bharath kumar
> Subject: Re: How to access an array variables in a single query
>
> but that is not static for 3 variables what if array contains
> 1000 elements
> in cannot be used in such cases
>
> Thanks and Regards,
> venu
>
> On 1/6/07, ViSolve DB Team <[EMAIL PROTECTED]> wrote:
> >
> > Hi,
> >
> > IN operator will do.
> >
> > For Instance.
> >
> > SELECT * FROM TABLE1 WHERE id IN ('i1','i2','i3');
> >
> >
> > Thanks
> > ViSolve DB Team.
> >
> > - Original Message -----
> > From: "VenuGopal Papasani" <[EMAIL PROTECTED]>
> > To:
> > Cc: "bharath kumar" <[EMAIL PROTECTED]>
> > Sent: Saturday, January 06, 2007 2:07 PM
> > Subject: How to access an array variables in a single query
> >
> >
> > > Dear all,
> > > I have got an array variable say a[]={"i1","i2",i3""}
> now i need to
> > get
> > > the values from the database that matches i1,i2,i3 for example
> > >
> > > select * from tablename where i1=a[1] and
> > > simillaryly to a[2] and a[3] but my list is too big one
> more option i
> > can
> > > write it through loop but it takes time.Is there any
> possibility of
> > > writing
> > > a single query to retrieve all that data.
> > >
> > > Awaiting your reply
> > >
> > > Thanks and Regards,
> > > venu
> > >
> > > --
> > > ,
> > > ?? ???.
> > >
> >
> >
>
>
> --
> ,
> ?? ???.
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to access an array variables in a single query
In the last episode (Jan 06), VenuGopal Papasani said:
> On 1/6/07, ViSolve DB Team <[EMAIL PROTECTED]> wrote:
> >> I have got an array variable say a[]={"i1","i2",i3""} now i need
> >> to get the values from the database that matches i1,i2,i3 for
> >> example
> >>
> >> select * from tablename where i1=a[1] and
> >>
> >> simillaryly to a[2] and a[3] but my list is too big one more
> >> option i can write it through loop but it takes time.Is there any
> >> possibility of writing a single query to retrieve all that data.
> >
> >IN operator will do.
> >
> >For Instance.
> >
> >SELECT * FROM TABLE1 WHERE id IN ('i1','i2','i3');
> >From: "VenuGopal Papasani" <[EMAIL PROTECTED]>
>
> but that is not static for 3 variables what if array contains 1000
> elements in cannot be used in such cases
It actually does work, as long as your total query length is less than
max_allowed_packet (defaults to 1048576 bytes). An IN clause with 1000
elements and 10 characters per element would only be 13000 bytes.
--
Dan Nelson
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to access an array variables in a single query
Hi,
If the values to be matched against were from a table, why can't subquery be
used.
Like, SELECT * FROM TABLE1 WHERE id IN (select id from table2);
Otherwise if numeric constants, then AFAIK procedure -loops would do.
Thanks
ViSolve DB Team.
- Original Message -
From: "VenuGopal Papasani" <[EMAIL PROTECTED]>
To: "ViSolve DB Team" <[EMAIL PROTECTED]>
Cc: ; "bharath kumar" <[EMAIL PROTECTED]>
Sent: Saturday, January 06, 2007 2:52 PM
Subject: Re: How to access an array variables in a single query
but that is not static for 3 variables what if array contains 1000
elements
in cannot be used in such cases
Thanks and Regards,
venu
On 1/6/07, ViSolve DB Team <[EMAIL PROTECTED]> wrote:
Hi,
IN operator will do.
For Instance.
SELECT * FROM TABLE1 WHERE id IN ('i1','i2','i3');
Thanks
ViSolve DB Team.
- Original Message -
From: "VenuGopal Papasani" <[EMAIL PROTECTED]>
To:
Cc: "bharath kumar" <[EMAIL PROTECTED]>
Sent: Saturday, January 06, 2007 2:07 PM
Subject: How to access an array variables in a single query
> Dear all,
> I have got an array variable say a[]={"i1","i2",i3""} now i need to
get
> the values from the database that matches i1,i2,i3 for example
>
> select * from tablename where i1=a[1] and
> simillaryly to a[2] and a[3] but my list is too big one more option i
can
> write it through loop but it takes time.Is there any possibility of
> writing
> a single query to retrieve all that data.
>
> Awaiting your reply
>
> Thanks and Regards,
> venu
>
> --
> ధన్యవాదములతో,
> వేణుగోపాల్ పాపసాని.
>
--
ధన్యవాదములతో,
వేణుగోపాల్ పాపసాని.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to access an array variables in a single query
but that is not static for 3 variables what if array contains 1000 elements
in cannot be used in such cases
Thanks and Regards,
venu
On 1/6/07, ViSolve DB Team <[EMAIL PROTECTED]> wrote:
Hi,
IN operator will do.
For Instance.
SELECT * FROM TABLE1 WHERE id IN ('i1','i2','i3');
Thanks
ViSolve DB Team.
- Original Message -
From: "VenuGopal Papasani" <[EMAIL PROTECTED]>
To:
Cc: "bharath kumar" <[EMAIL PROTECTED]>
Sent: Saturday, January 06, 2007 2:07 PM
Subject: How to access an array variables in a single query
> Dear all,
> I have got an array variable say a[]={"i1","i2",i3""} now i need to
get
> the values from the database that matches i1,i2,i3 for example
>
> select * from tablename where i1=a[1] and
> simillaryly to a[2] and a[3] but my list is too big one more option i
can
> write it through loop but it takes time.Is there any possibility of
> writing
> a single query to retrieve all that data.
>
> Awaiting your reply
>
> Thanks and Regards,
> venu
>
> --
> ధన్యవాదములతో,
> వేణుగోపాల్ పాపసాని.
>
--
ధన్యవాదములతో,
వేణుగోపాల్ పాపసాని.
Re: How to access an array variables in a single query
Hi,
IN operator will do.
For Instance.
SELECT * FROM TABLE1 WHERE id IN ('i1','i2','i3');
Thanks
ViSolve DB Team.
- Original Message -
From: "VenuGopal Papasani" <[EMAIL PROTECTED]>
To:
Cc: "bharath kumar" <[EMAIL PROTECTED]>
Sent: Saturday, January 06, 2007 2:07 PM
Subject: How to access an array variables in a single query
Dear all,
I have got an array variable say a[]={"i1","i2",i3""} now i need to get
the values from the database that matches i1,i2,i3 for example
select * from tablename where i1=a[1] and
simillaryly to a[2] and a[3] but my list is too big one more option i can
write it through loop but it takes time.Is there any possibility of
writing
a single query to retrieve all that data.
Awaiting your reply
Thanks and Regards,
venu
--
ధన్యవాదములతో,
వేణుగోపాల్ పాపసాని.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to access an array variables in a single query
Dear all,
I have got an array variable say a[]={"i1","i2",i3""} now i need to get
the values from the database that matches i1,i2,i3 for example
select * from tablename where i1=a[1] and
simillaryly to a[2] and a[3] but my list is too big one more option i can
write it through loop but it takes time.Is there any possibility of writing
a single query to retrieve all that data.
Awaiting your reply
Thanks and Regards,
venu
--
ధన్యవాదములతో,
వేణుగోపాల్ పాపసాని.
