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: mysql@lists.mysql.com
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: mysql@lists.mysql.com
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
--
ధన్యవాదములతో,
వేణుగోపాల్ పాపసాని.
--
ధన్యవాదములతో,
వేణుగోపాల్ పాపసాని.
Indexing issue in slave !!
Hi, I am facing a peculiar problem.When i execute a query on slave server it doesn't use indexes that have been created and hence read all the records present in that table.The same query when execute on Master yields proper results.The table structure , table types are same.What could be wrong? regards Himanshu Raina -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Selecting avg()...
This is a continuation of something I started with a few weeks ago. So, here's the previous information again: table: data ++---+--+-+---++ | Field | Type | Null | Key | Default | Extra | ++---+--+-+---++ | uid| mediumint(8) unsigned | NO | PRI | NULL | auto_increment | | temp_f | float(4,1)| YES | | NULL || | temp_c | float(4,1)| YES | | NULL || | windchill | float(4,1)| YES | | NULL || | dewpoint | float(4,1)| YES | | NULL || | time | timestamp | NO | | CURRENT_TIMESTAMP || ++---+--+-+---++ This table gets information every minute. And for a '24 hour' graph, I perform the following select: select day(time) as the_day, hour(time) as the_hour, avg(temp_f) as avg_temp_f, avg(temp_c) as avg_temp_f, avg(windchill) as avg_windchill, avg(dewpoint) as avg_dewpoint from data where time now() - interval 24 hour group by the_day, the_hour; And this returns the data I need. However, I'd like a finer grain select, if possible. For example, instead of it averaging each 60 minute period, I'd like an average per 15 minute. So that I get 4 data points returned per hour, instead of just one. Possible? -- A -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: On Duplicate Key Update question
Hi, From your query, understood that you want to retain old qty and new qty; result in another field. Try with, INSERT INTO TABLE1 (id,newqty) values (6,300) ON DUPLICATE KEY UPDATE totqty=oldqty+newqty, oldqty=newqty; Thanks, ViSolve DB Team - Original Message - From: Ed Reed [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Saturday, January 06, 2007 4:10 AM Subject: On Duplicate Key Update question I use On Duplicate Key Update a lot and I usually use it like this, Insert Into tablename (myID, Qty) Values (1034,15), (6,13), (5001,1), (145,20) On Duplicate Key Update Qty=Values(Qty); This works very well but now I'd like to do something a little different. I'd like to have a query like this but instead of replacing the value of the previous Qty I'd like it to take the old Qty and the new Qty and store the sum of the two values in the Qty field. Is this possible and can anyone tell me how? Thanks -- 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: mysql@lists.mysql.com; 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: mysql@lists.mysql.com
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: Indexing issue in slave !!
In the last episode (Jan 06), Himanshu Raina said: I am facing a peculiar problem.When i execute a query on slave server it doesn't use indexes that have been created and hence read all the records present in that table.The same query when execute on Master yields proper results.The table structure , table types are same.What could be wrong? If you compare the EXPLAIN SELECT .. outputs for the query on both servers, what's different? -- 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: Selecting avg()...
In the last episode (Jan 06), Ashley M. Kirchner said: This is a continuation of something I started with a few weeks ago. So, here's the previous information again: ++---+--+-+---++ | Field | Type | Null | Key | Default | Extra | ++---+--+-+---++ | uid| mediumint(8) unsigned | NO | PRI | NULL | auto_increment| | temp_f | float(4,1)| YES | | NULL | | | temp_c | float(4,1)| YES | | NULL | | | windchill | float(4,1)| YES | | NULL | | | dewpoint | float(4,1)| YES | | NULL | | | time | timestamp | NO | | CURRENT_TIMESTAMP | | ++---+--+-+---++ This table gets information every minute. And for a '24 hour' graph, I perform the following select: select day(time) as the_day, hour(time) as the_hour, avg(temp_f) as avg_temp_f, avg(temp_c) as avg_temp_f, avg(windchill) as avg_windchill, avg(dewpoint) as avg_dewpoint from data where time now() - interval 24 hour group by the_day, the_hour; And this returns the data I need. However, I'd like a finer grain select, if possible. For example, instead of it averaging each 60 minute period, I'd like an average per 15 minute. So that I get 4 data points returned per hour, instead of just one. select from_unixtime(floor(unix_timestamp(time)/900)*900) as range_start, avg(temp_f) as avg_temp_f, avg(temp_c) as avg_temp_f, avg(windchill) as avg_windchill, avg(dewpoint) as avg_dewpoint from data where time now() - interval 24 hour group by floor(unix_timestamp(time)/900) should do the trick. Actually a TIME_GROUP(timestamp, interval) function would be really handy for stuff like this: GROUP BY TIME_GROUP(time, interval 15 minute). -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
