Re: query needed
if it is static then it works fine.but we have lots of codes in a table which should be done similar operation.instead varifying staticly with c1,c2 can we make dynamic. On 8/14/06, Peter Lauri [EMAIL PROTECTED] wrote: SELECT SUM(IF(code='c1', code, IF(code='c2', code, 0))) - SUM(IF(code='c4', code, IF(code='c5', code, 0))) FROM datavalue; -Original Message- From: VenuGopal Papasani [mailto:[EMAIL PROTECTED] Sent: Monday, August 14, 2006 11:26 AM To: mysql@lists.mysql.com Subject: query needed Hi, i got a table datavalue as follows code period value c1 20051 c2 20052 c32006 3 c4 2005 2 c52005 1 now i need a query where some values should be added and some values should be subtracted of certain period.for ex here 2005 now i need (c1+c2-c4-c5) can i do it in a single query .Can any one give me the query plsss regards, venu. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
RE: query needed
Not until we know the logic behind the code and how the calculations should be done. -Original Message- From: VenuGopal Papasani [mailto:[EMAIL PROTECTED] Sent: Monday, August 14, 2006 1:03 PM To: Peter Lauri; mysql@lists.mysql.com Subject: Re: query needed if it is static then it works fine.but we have lots of codes in a table which should be done similar operation.instead varifying staticly with c1,c2 can we make dynamic. On 8/14/06, Peter Lauri [EMAIL PROTECTED] wrote: SELECT SUM(IF(code='c1', code, IF(code='c2', code, 0))) - SUM(IF(code='c4', code, IF(code='c5', code, 0))) FROM datavalue; -Original Message- From: VenuGopal Papasani [mailto:[EMAIL PROTECTED] Sent: Monday, August 14, 2006 11:26 AM To: mysql@lists.mysql.com Subject: query needed Hi, i got a table datavalue as follows code period value c1 20051 c2 20052 c32006 3 c4 2005 2 c52005 1 now i need a query where some values should be added and some values should be subtracted of certain period.for ex here 2005 now i need (c1+c2-c4-c5) can i do it in a single query .Can any one give me the query plsss 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]
Running mysqld on certain interface
How do I set mysqld hears only on eth0? i am using FC linux. -- God Bless Us -- __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to delete /tmp/ibE3FYj2 files created by mySQL
Hi. There's a lot of '/tmp/ibE3FYj2' files inside my Linux server, created by mysqld. Does anybody know how to delete them after being used? Thank you very much. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Replication Binary Logs - How Long to Keep?
On Sat, 2006-08-12 at 08:38 -0400, Warren Crigger wrote: Note that you should not just delete the bin logs. Instead use PURGE MASTER LOGS. See http://dev.mysql.com/doc/refman/4.1/en/purge-master-logs.html hth, mark Sorry, accidently hit Ctrl/Enter :( Anyway, I can't purge with that command: mysql PURGE MASTER LOGS TO 'mysql-bin.023'; ERROR: A purgeable log is in use, will not purge Any ideas? I'm tempted to just delete but would prefer to do this the right way, and for some reason it thinks they are in use :/. I'm showing: mysql show master status; +--+---+--+--+ | File | Position | Binlog_do_db | Binlog_ignore_db | +--+---+--+--+ | repl.024 | 110962544 | | | +--+---+--+--+ 1 row in set (0.00 sec) That File column looks wrong, the name should match your setting for the name of the binary log e.g. 'mysql-bin.023'. 'repl' looks like the name of a relay log, which is what slaves use to update them selves. Can you send the output of SHOW MASTER STATUS; and SHOW SLAVE STATUS; for both boxes? Note if you use \G for the slave on the mysql command line the output is much easy to read. e.g. mysql SHOW SLAVE STATUS\G Also you might find running SHOW PROCESSLIST; on the servers usefull, if the bin log is in use you should be able to see the replication processes using it. cheers, mark -- MARK ADDISON WEB DEVELOPER 200 GRAY'S INN ROAD LONDON WC1X 8XZ UNITED KINGDOM T +44 (0)20 7430 4678 F E [EMAIL PROTECTED] WWW.ITN.CO.UK Please Note: Any views or opinions are solely those of the author and do not necessarily represent those of Independent Television News Limited unless specifically stated. This email and any files attached are confidential and intended solely for the use of the individual or entity to which they are addressed. If you have received this email in error, please notify [EMAIL PROTECTED] Please note that to ensure regulatory compliance and for the protection of our clients and business, we may monitor and read messages sent to and from our systems. Thank You. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Running mysqld on certain interface
On Mon, 2006-08-14 at 00:27 -0700, The Nice Spider wrote: How do I set mysqld hears only on eth0? i am using FC linux. Add a setting for bind_address under the mysqld section in the my.cnf file, set to the IP of eth0 (then restart the mysql server). hth, mark -- MARK ADDISON WEB DEVELOPER 200 GRAY'S INN ROAD LONDON WC1X 8XZ UNITED KINGDOM T +44 (0)20 7430 4678 F E [EMAIL PROTECTED] WWW.ITN.CO.UK Please Note: Any views or opinions are solely those of the author and do not necessarily represent those of Independent Television News Limited unless specifically stated. This email and any files attached are confidential and intended solely for the use of the individual or entity to which they are addressed. If you have received this email in error, please notify [EMAIL PROTECTED] Please note that to ensure regulatory compliance and for the protection of our clients and business, we may monitor and read messages sent to and from our systems. Thank You. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Joining result sets into 1 row
I think what you are looking for is GROUP_CONCAT. Without more info I can't really tell though. http://dev.mysql.com/doc/refman/4.1/en/group-by-functions.html - Original Message - From: Steffan A. Cline [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Sunday, August 13, 2006 6:53 PM Subject: Joining result sets into 1 row I am in a situation where I have say 1 column called attribute I need and the result set is 3 rows. i.e. ROW 1 - Mechanic ROW 2 - Carpenter ROW 3 - Plumber I want to have the rows returned as one row Such as ROW 1 Mechanic, Carpenter, Plumber Something like a literal join would be beautiful such as : ROW 1 Mechanic, Carpenter and Plumber I think the latter is asking for too much but the first would be awesome. Any advice is much appreciated! Thanks Steffan --- T E L 6 0 2 . 5 7 9 . 4 2 3 0 | F A X 6 0 2 . 9 7 1 . 1 6 9 4 Steffan A. Cline [EMAIL PROTECTED] Phoenix, Az http://www.ExecuChoice.net USA AIM : SteffanC ICQ : 57234309 Lasso Partner Alliance Member --- -- 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: Group by with an IF
The problem is your GROUP BY on celec_id. MySQL is doing the grouping and thus only grabbing the first season_week_date value within the grouping. I'm not sure what end result your are looking for. Grouping additionally by season_week_date might be what you are looking for. GROUP BY r.celeb_id, season_week_date - Original Message - From: Steffan A. Cline [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Sunday, August 13, 2006 5:33 AM Subject: Group by with an IF I have the following query: select *, if( season_week_date = 2006-08-16, on, off ) as stat, sum(overall_points) as total_points from rosters r left join celebs c on c.celeb_id = r.celeb_id where season_id=5062 and user_id=1 group by r.celeb_id order by overall_rank, ln, fn; It almost works as expected however the problem I am concerned about is that I never get a stat saying On after it resolves. Removing the sum() and adding group I get the total_points as expected but now the results are always off. Can anyone shed any light on this one? Thanks Steffan --- T E L 6 0 2 . 5 7 9 . 4 2 3 0 | F A X 6 0 2 . 9 7 1 . 1 6 9 4 Steffan A. Cline [EMAIL PROTECTED] Phoenix, Az http://www.ExecuChoice.net USA AIM : SteffanC ICQ : 57234309 Lasso Partner Alliance Member --- -- 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]
Query Question
Hi, Does anyone have a clever way of returning; a requested value with one value less than that value, and one value greater than that value with one query. For example T1 contains ID 1234 1235 1236 1238 select ID from T1 where ID = 1235 and ID1235 and ID 1235 LIMIT 3 (obviously this doesn't work) I would want to return 1234 1235 1236 or; select ID from T1 where ID = 1237 and ID1237 and ID 1237 LIMIT 3 I would want 1236 1238 I would be surprised if there was no way of doing this.but then again, I'm often surprised Thanks Michael -- Michael DePhillips www.star.bnl.gov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Question
Michael, I would think this is what you want. Select ID from T1 where ID BETWEEN (id in question - 1) and (id in question + 1) If you want distinct values, place the distinct keyword in front of ID (i.e. Select DISTINCT ID... This should do it for you. -Dan Hi, Does anyone have a clever way of returning; a requested value with one value less than that value, and one value greater than that value with one query. For example T1 contains ID 1234 1235 1236 1238 select ID from T1 where ID = 1235 and ID1235 and ID 1235 LIMIT 3 (obviously this doesn't work) I would want to return 1234 1235 1236 or; select ID from T1 where ID = 1237 and ID1237 and ID 1237 LIMIT 3 I would want 1236 1238 I would be surprised if there was no way of doing this.but then again, I'm often surprised Thanks Michael -- Michael DePhillips www.star.bnl.gov -- Have a great day, Dan Julson Application Software Engineer API Outsourcing 1355 Mendota Heights Rd. Mendota Heights, MN 55120 651-675-2628 (work) 952-456-2067 (mobile) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Question
Michael DePhillips wrote: Hi, Does anyone have a clever way of returning; a requested value with one value less than that value, and one value greater than that value with one query. For example T1 contains ID 1234 1235 1236 1238 Assuming the id's are consecutive. You want surounding rows returned if the target Id is missing: - select id from T1 as first where T1.id between (1237 -1) and (1237 +1); You want no rows returned if the target Id is missing: - select id from T1 as first , inner join T2 as second on second.id between (first.id -1) and (first.id +1) where first.id = 1237; If you Id's aren't always consecutive, or you need something more smart post again. Nigel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Question
Hi Dan, Thanks for the prompt reply, As I described it yes, you are correct, however, the id may not always be one(1) value away. So the number one needs, somehow, to be replaced with a way to get the next largest value and the previous less than value. Sorry for the lack of precision in my prior post. Regards, Michael Dan Julson wrote: Michael, I would think this is what you want. Select ID from T1 where ID BETWEEN (id in question - 1) and (id in question + 1) If you want distinct values, place the distinct keyword in front of ID (i.e. Select DISTINCT ID... This should do it for you. -Dan Hi, Does anyone have a clever way of returning; a requested value with one value less than that value, and one value greater than that value with one query. For example T1 contains ID 1234 1235 1236 1238 select ID from T1 where ID = 1235 and ID1235 and ID 1235 LIMIT 3 (obviously this doesn't work) I would want to return 1234 1235 1236 or; select ID from T1 where ID = 1237 and ID1237 and ID 1237 LIMIT 3 I would want 1236 1238 I would be surprised if there was no way of doing this.but then again, I'm often surprised Thanks Michael -- Michael DePhillips www.star.bnl.gov -- Michael DePhillips www.star.bnl.gov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
lots of problem with AIX...
Hi, I am trying to setup mysql on AIX 5.3L system with all patches installed (suma update yesterday) I have tried 3 ways and each failed... 1. I have tried the precompiled AIX 64 bit binary. those failed to link with mysql++ (we use 1.7.40) and php (they didn't found libmysqlclient which was there) I assumed this is because i compile 32 bit binary and it cannot link (I think) I am using gcc 4.1.1 to compile php and use php 5.1.4 (I need the latest php for a package). however mysqld started and was running and i was able to log in to the server. 2. Then I tried the 32bit precompiled AIX binaries those crash on runtime. Its worth noticing that during mysql_install_db I see ./bin/mysqld: Out of memory (needed XXX bytes) several times. the error it gives me when it crases is file ./mysql-bin.index not found (error code 13) 3. Last I tried to compile the sources. Configuring this version and make, make install worked fine. This also worked fine with php install and mysql++ however, however, the server crashed with nothing in the log. thoughts? ciao scipio
Re: Query Question
Michael DePhillips wrote: Hi Dan, Thanks for the prompt reply, As I described it yes, you are correct, however, the id may not always be one(1) value away. So the number one needs, somehow, to be replaced with a way to get the next largest value and the previous less than value. Sorry for the lack of precision in my prior post. What you want to do isn't possible in the form you want without subqueries. Which would you prefer: a) A single query returning one row of three columns containing nulls b) A single query using sub queries (MySQL 4.1+ only) returning upto three rows with one column c) multiple queries Nigel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Question
I think this will do it, although it takes three queries. I'm assuming the id values are unique, even if there can be gaps (that's what you might get with an AUTO_INCREMENT field). If the values are not guaranteed to be unique then this may not give what you want (if there are multiple rows with the same value you're looking for, e.g. 3,4,5,5,5,6,9, and you ask for 5, then this would give you 3,5,6, not three fives.) SELECT @id:=5; SELECT * FROM t WHERE id(SELECT MAX(id) FROM t WHERE id@id) ORDER BY id DESC LIMIT 1; SELECT * FROM t WHERE [EMAIL PROTECTED] LIMIT 1; SELECT * FROM t WHERE id(SELECT MIN(id) FROM t WHERE id@id) ORDER BY id ASC LIMIT 1; But as to putting that in one statement... it might be better just to do it as three. Douglas Sims [EMAIL PROTECTED] On Aug 14, 2006, at 9:32 AM, Michael DePhillips wrote: Hi Dan, Thanks for the prompt reply, As I described it yes, you are correct, however, the id may not always be one(1) value away. So the number one needs, somehow, to be replaced with a way to get the next largest value and the previous less than value. Sorry for the lack of precision in my prior post. Regards, Michael Dan Julson wrote: Michael, I would think this is what you want. Select ID from T1 where ID BETWEEN (id in question - 1) and (id in question + 1) If you want distinct values, place the distinct keyword in front of ID (i.e. Select DISTINCT ID... This should do it for you. -Dan Hi, Does anyone have a clever way of returning; a requested value with one value less than that value, and one value greater than that value with one query. For example T1 contains ID 1234 1235 1236 1238 select ID from T1 where ID = 1235 and ID1235 and ID 1235 LIMIT 3 (obviously this doesn't work) I would want to return 1234 1235 1236 or; select ID from T1 where ID = 1237 and ID1237 and ID 1237 LIMIT 3 I would want 1236 1238 I would be surprised if there was no way of doing this.but then again, I'm often surprised Thanks Michael -- Michael DePhillips www.star.bnl.gov -- Michael DePhillips www.star.bnl.gov -- 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: Query Question
Hi Nigel, A and B...please. Perhaps a UDF could achieve my initial request...any ideas. Thanks, Michael nigel wood wrote: Michael DePhillips wrote: Hi Dan, Thanks for the prompt reply, As I described it yes, you are correct, however, the id may not always be one(1) value away. So the number one needs, somehow, to be replaced with a way to get the next largest value and the previous less than value. Sorry for the lack of precision in my prior post. What you want to do isn't possible in the form you want without subqueries. Which would you prefer: a) A single query returning one row of three columns containing nulls b) A single query using sub queries (MySQL 4.1+ only) returning upto three rows with one column c) multiple queries Nigel -- Michael DePhillips www.star.bnl.gov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Question
Here's a single query version of Douglas's solution: select @id:=6; select distinct t.testid, (select max(testid) from t where testid @id) as previousId, (select min(testid) from t where testid @id) as nextId from t where [EMAIL PROTECTED] Donna Douglas Sims [EMAIL PROTECTED] 08/14/2006 10:47 AM To Michael DePhillips [EMAIL PROTECTED] cc Dan Julson [EMAIL PROTECTED], mysql@lists.mysql.com Subject Re: Query Question I think this will do it, although it takes three queries. I'm assuming the id values are unique, even if there can be gaps (that's what you might get with an AUTO_INCREMENT field). If the values are not guaranteed to be unique then this may not give what you want (if there are multiple rows with the same value you're looking for, e.g. 3,4,5,5,5,6,9, and you ask for 5, then this would give you 3,5,6, not three fives.) SELECT @id:=5; SELECT * FROM t WHERE id(SELECT MAX(id) FROM t WHERE id@id) ORDER BY id DESC LIMIT 1; SELECT * FROM t WHERE [EMAIL PROTECTED] LIMIT 1; SELECT * FROM t WHERE id(SELECT MIN(id) FROM t WHERE id@id) ORDER BY id ASC LIMIT 1; But as to putting that in one statement... it might be better just to do it as three. Douglas Sims [EMAIL PROTECTED] On Aug 14, 2006, at 9:32 AM, Michael DePhillips wrote: Hi Dan, Thanks for the prompt reply, As I described it yes, you are correct, however, the id may not always be one(1) value away. So the number one needs, somehow, to be replaced with a way to get the next largest value and the previous less than value. Sorry for the lack of precision in my prior post. Regards, Michael Dan Julson wrote: Michael, I would think this is what you want. Select ID from T1 where ID BETWEEN (id in question - 1) and (id in question + 1) If you want distinct values, place the distinct keyword in front of ID (i.e. Select DISTINCT ID... This should do it for you. -Dan Hi, Does anyone have a clever way of returning; a requested value with one value less than that value, and one value greater than that value with one query. For example T1 contains ID 1234 1235 1236 1238 select ID from T1 where ID = 1235 and ID1235 and ID 1235 LIMIT 3 (obviously this doesn't work) I would want to return 1234 1235 1236 or; select ID from T1 where ID = 1237 and ID1237 and ID 1237 LIMIT 3 I would want 1236 1238 I would be surprised if there was no way of doing this.but then again, I'm often surprised Thanks Michael -- Michael DePhillips www.star.bnl.gov -- Michael DePhillips www.star.bnl.gov -- 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] -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. CONFIDENTIALITY NOTICE:This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or protected health information. Any duplication, dissemination, action taken in reliance upon, or other use of this information by persons or entities other than the intended recipient is prohibited and may violate applicable laws. If this email has been received in error, please notify the sender and delete the information from your system. The views expressed in this email are those of the sender and may not necessarily represent the views of IntelliCare.
comunication between Oracle and MYSQL
Hi All, I need some suggestions from you. I need a comunication between Oracle database with MySQL. In my application there is a situation is arising, where i need to take some data to MySql from a table which is in Oracle database (i am planning to maintain that data in MySQL also). And from MySQL my application will use it. This whole thing should be happen online.That is once some new data was inserted to Oracle table, that should update in MySQL table also. It is totally new situation for me and i am totally confused. Please help me folks. Thanks in advance. regards, Bala Raju Mandala.
Re: comunication between Oracle and MYSQL
--On August 14, 2006 9:11:30 PM +0530 balaraju mandala [EMAIL PROTECTED] wrote: Hi All, I need some suggestions from you. I need a comunication between Oracle database with MySQL. http://www.webmethods.com/ They sell software to do this. Or...well... ActiveSoftware/ActiveWorks did which was bought by them. Disclaimer, I worked at Active as the Unix Admin for the Demonstration center. Someone else might have written some software to do it as well. But you either need a third piece of software from someone else or it needs to be part of your app or you need to write it. The databases will not do it for you natively. In my application there is a situation is arising, where i need to take some data to MySql from a table which is in Oracle database (i am planning to maintain that data in MySQL also). And from MySQL my application will use it. This whole thing should be happen online.That is once some new data was inserted to Oracle table, that should update in MySQL table also. It is totally new situation for me and i am totally confused. Please help me folks. Thanks in advance. regards, Bala Raju Mandala. -- Genius might be described as a supreme capacity for getting its possessors into trouble of all kinds. -- Samuel Butler -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: comunication between Oracle and MYSQL
balaraju mandala wrote: In my application there is a situation is arising, where i need to take some data to MySql from a table which is in Oracle database (i am planning to maintain that data in MySQL also). And from MySQL my application will use it. This whole thing should be happen online.That is once some new data was inserted to Oracle table, that should update in MySQL table also. Can't you just make your app connect directly to Oracle ? You'll drawn in lots of sync issues if you don't, specially if your app updates the database with data provided from Oracle. Take this example: the user updates something, it goes to Oracle, than MySQL. Your system update lots of things out of it. Than, the user realizes he's wrong and update again the record, and some of your updates might not work, other things were deleted that shouldn't according to your business model. I'm not saying you will have this problem, I'm just saying you might consider it before doing the Oracle-MySQL connector. Btw, I don't know any connector, and don't think a trigger on Oracle will work either. You'll probably have to live with a daemon replicating every 10 seconds or so. cheers, --renato -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Question
D'oh. Very good. I wish I'd thought of that. In response to Michael DePhillips' point about the UDF - I believe that in MySQL 5.x UDFs can't query tables. In Oracle, SQL Server, etc. they can and I'm sure they will in the future. Douglas Sims [EMAIL PROTECTED] On Aug 14, 2006, at 10:17 AM, [EMAIL PROTECTED] wrote: Here's a single query version of Douglas's solution: select @id:=6; select distinct t.testid, (select max(testid) from t where testid @id) as previousId, (select min(testid) from t where testid @id) as nextId from t where [EMAIL PROTECTED] Donna Douglas Sims [EMAIL PROTECTED] 08/14/2006 10:47 AM To Michael DePhillips [EMAIL PROTECTED] cc Dan Julson [EMAIL PROTECTED], mysql@lists.mysql.com Subject Re: Query Question I think this will do it, although it takes three queries. I'm assuming the id values are unique, even if there can be gaps (that's what you might get with an AUTO_INCREMENT field). If the values are not guaranteed to be unique then this may not give what you want (if there are multiple rows with the same value you're looking for, e.g. 3,4,5,5,5,6,9, and you ask for 5, then this would give you 3,5,6, not three fives.) SELECT @id:=5; SELECT * FROM t WHERE id(SELECT MAX(id) FROM t WHERE id@id) ORDER BY id DESC LIMIT 1; SELECT * FROM t WHERE [EMAIL PROTECTED] LIMIT 1; SELECT * FROM t WHERE id(SELECT MIN(id) FROM t WHERE id@id) ORDER BY id ASC LIMIT 1; But as to putting that in one statement... it might be better just to do it as three. Douglas Sims [EMAIL PROTECTED] On Aug 14, 2006, at 9:32 AM, Michael DePhillips wrote: Hi Dan, Thanks for the prompt reply, As I described it yes, you are correct, however, the id may not always be one(1) value away. So the number one needs, somehow, to be replaced with a way to get the next largest value and the previous less than value. Sorry for the lack of precision in my prior post. Regards, Michael Dan Julson wrote: Michael, I would think this is what you want. Select ID from T1 where ID BETWEEN (id in question - 1) and (id in question + 1) If you want distinct values, place the distinct keyword in front of ID (i.e. Select DISTINCT ID... This should do it for you. -Dan Hi, Does anyone have a clever way of returning; a requested value with one value less than that value, and one value greater than that value with one query. For example T1 contains ID 1234 1235 1236 1238 select ID from T1 where ID = 1235 and ID1235 and ID 1235 LIMIT 3 (obviously this doesn't work) I would want to return 1234 1235 1236 or; select ID from T1 where ID = 1237 and ID1237 and ID 1237 LIMIT 3 I would want 1236 1238 I would be surprised if there was no way of doing this.but then again, I'm often surprised Thanks Michael -- Michael DePhillips www.star.bnl.gov -- Michael DePhillips www.star.bnl.gov -- 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] -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. CONFIDENTIALITY NOTICE:This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or protected health information. Any duplication, dissemination, action taken in reliance upon, or other use of this information by persons or entities other than the intended recipient is prohibited and may violate applicable laws. If this email has been received in error, please notify the sender and delete the information from your system. The views expressed in this email are those of the sender and may not necessarily represent the views of IntelliCare. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Question
On Monday 14 August 2006 07:08 am, Michael DePhillips wrote: select ID from T1 where ID = 1235 and ID1235 and ID 1235 LIMIT 3 (obviously this doesn't work) I would want to return 1234 1235 1236 mysql select int_value, (int_value + 1) as value2, (int_value - 1) as value3 FROM data_values; +---+++ | int_value | value2 | value3 | +---+++ |11 | 12 | 10 | +---+++ 1 row in set (0.00 sec) You can do like that. -- Chris White PHP Programmer/DBrother Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Joining result sets into 1 row
I want to have the rows returned as one row Such as ROW 1 Mechanic, Carpenter, Plumber You may want to try GROUP_CONCAT(expr) ... http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Joining result sets into 1 row
On Mon, 2006-08-14 at 05:43 -0700, Steffan A. Cline wrote: Here is a better example mysql select listName from listItem limit 3; +-+ | listName| +-+ | PWC | | Small Boats | | Fiberglass | +-+ 3 rows in set (0.02 sec) mysql Rather than return 3 rows I would like the data retruned as mysql select listName from listItem limit 3; +--+ | listName | +--+ | PWC, Small Boats, Fiberglass | +--+ 1 row in set (0.02 sec) Seems like what you want is to concat everything into 1 line separated by Commas? Hmm.. Not very sure how to get that done. Is there anything which is unique about each item in the list? Please Send to the Maillist as you will get more response from more knowledgeable people there. Not sure how your row are. is it really marked as Row1/Row2/Row3? If yes, you can use a case expression. select case when row=row1 then row else null end as mechanic , case when row=row2 then row else null end as carpenter, end -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Incrementing using Max(Field) ?
Does anyone have any ideas of how I can select the max value and insert the next highest value? I want something that would do something like this: Insert into table1 (select max(field1)+1 from table1); This obviously doesn't work. I know if the table was set to auto-increment it wouldn't be an issue, but I am not able to change its schema. Thank you. - William -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Incrementing using Max(Field) ?
On 8/14/06, William DeMasi [EMAIL PROTECTED] wrote: Does anyone have any ideas of how I can select the max value and insert the next highest value? I want something that would do something like this: Insert into table1 (select max(field1)+1 from table1); This obviously doesn't work. I know if the table was set to auto-increment it wouldn't be an issue, but I am not able to change its schema. Thank you. - William with a store procedure, something like: CREATE PROCEDURE InsertNext () BEGIN DECLARE myNext INT; DECLARE cur_for_next CURSOR FOR select coalesce( max(field1),0 ) +1 from table1; OPEN cur_espacio_tanque; FETCH cur_for_next INTO myNext; CLOSE cur_espacio_tanque; INSERT INTO table1 values ( myNext ); END; y you call it call InsertNext (); may be can works -- http://www.obed.org.mx --- blog -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Possiable Trigger Use
Hi All, I was wondering if This is possiable. I have a date column and want to have a trigger perform the following: Look at all of the dates and then if the data is 3 days old. Delete the corresponding tables related to that date, and then fianlly the row that told me what tables are related to that date. Thanks, Jason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: non-text data
On Mon, Aug 14, 2006 at 02:29:41PM +1000, Chris wrote: Dave Shariff Yadallee - System Administrator a.k.a. The Root of the Problem wrote: On Mon, Aug 14, 2006 at 10:42:50AM +1000, Chris wrote: Dave Shariff Yadallee - System Administrator a.k.a. The Root of the Problem wrote: I use SELECT all from * ... and one row is a gif. How do I get that gif to appear as a gif and not text? In your connecting programming language. Mysql doesn't know or care whether it's a gif, pdf, word doc or anything else. Whatever programming language you are using to connect to mysql and fetch the data will be able to convert that binary data and display an image. PHP? http://www.php.net/gd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. All right, so how would one set up a select where rows 1 and 2 are text and row 3 is a blob and for argument sake assume jpg for the moment. -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem installing PERL DBD::mysql
Machine info:
[EMAIL PROTECTED] utils]$ uname -a
Linux snv1 2.6.9-34.0.2.ELsmp #1 SMP Fri Jun 30 10:32:04 EDT 2006 x86_64 x86_64
x86_64 GNU/Linux
[EMAIL PROTECTED] utils]$ perl -v
This is perl, v5.8.8 built for x86_64-linux
[EMAIL PROTECTED] utils]$ more /etc/redhat-release
Red Hat Enterprise Linux WS release 4 (Nahant Update 4)
--
I am having a hard time installing DBD:mysql on 64 bit machine
snv1.ultralight.org
/usr/bin/perl MCPAN -e shell
CPAN install DBD::mysql
Dies with errors...so I decided to do it from the source, and it also
complainshere is the output from the source install:
[EMAIL PROTECTED] DBD-mysql-3.0006]# /usr/bin/perl Makefile.PL I will use the
following settings for compiling and testing:
cflags(mysql_config) = -I/usr/local/include/mysql
embedded (mysql_config) =
libs (mysql_config) = -L/usr/local/lib/mysql -lmysqlclient -lz
-lcrypt -lnsl -lm
mysql_config (guessed ) = mysql_config
nocatchstderr (default ) = 0
nofoundrows (default ) = 0
ssl (guessed ) = 0
testdb(default ) = test
testhost (default ) =
testpassword (default ) =
testsocket(default ) =
testuser (default ) =
To change these settings, see 'perl Makefile.PL --help' and 'perldoc INSTALL'.
Using DBI 1.52 (for perl 5.008008 on x86_64-linux) installed in
/usr/lib/perl5/site_perl/5.8.8/x86_64-linux/auto/DBI/
Writing Makefile for DBD::mysql
[EMAIL PROTECTED] DBD-mysql-3.0006]#
[EMAIL PROTECTED] DBD-mysql-3.0006]# make
cc -c -I/usr/lib/perl5/site_perl/5.8.8/x86_64-linux/auto/DBI
-I/usr/local/include/mysql -DDBD_MYSQL_INSERT_ID_IS_GOO D -g
-fno-strict-aliasing -pipe -Wdeclaration-after-statement -I/usr/local/include
-D_LARGEFILE_SOURCE -D_FILE_OFFSE
T_BITS=64 -I/usr/include/gdbm -O2 -DVERSION=\3.0006\
-DXS_VERSION=\3.0006\ -fpic -I/usr/lib/perl5/5.8.8/x86_64
-linux/CORE dbdimp.c
cc -c -I/usr/lib/perl5/site_perl/5.8.8/x86_64-linux/auto/DBI
-I/usr/local/include/mysql -DDBD_MYSQL_INSERT_ID_IS_GOO D -g
-fno-strict-aliasing -pipe -Wdeclaration-after-statement -I/usr/local/include
-D_LARGEFILE_SOURCE -D_FILE_OFFSE
T_BITS=64 -I/usr/include/gdbm -O2 -DVERSION=\3.0006\
-DXS_VERSION=\3.0006\ -fpic -I/usr/lib/perl5/5.8.8/x86_64
-linux/CORE mysql.c
mysql.xs: In function `XS_DBD__mysql__GetInfo_dbd_mysql_get_info':
mysql.xs:647: warning: ignoring return value of `Perl_newSViv', declared with
attribute warn_unused_result
mysql.xs:650: warning: ignoring return value of `Perl_newSViv', declared with
attribute warn_unused_result
mysql.xs:653: warning: ignoring return value of `Perl_newSVpv', declared with
attribute warn_unused_result Running Mkbootstrap for DBD::mysql () chmod 644
mysql.bs rm -f blib/arch/auto/DBD/mysql/mysql.so
LD_RUN_PATH=/usr/local/lib/mysql /usr/bin/perl myld cc -shared
-L/usr/local/lib dbdimp.o mysql.o -o blib/arch/aut
o/DBD/mysql/mysql.so\
-L/usr/local/lib/mysql -lmysqlclient -lz -lcrypt -lnsl -lm \
/usr/bin/ld: /usr/local/lib/libz.a(compress.o): relocation R_X86_64_32 against
`a local symbol' can not be used when making a shared object; recompile with
-fPIC
/usr/local/lib/libz.a: could not read symbols: Bad value
collect2: ld returned 1 exit status
make: *** [blib/arch/auto/DBD/mysql/mysql.so] Error 1
I have tried using -fPIC, but I think I do not know how to tell it to use that
correctly. I assume it is passed with --cflags on the 'perl Makefile.PL' line,
but I can't get that to work.
Can someone tell me the syntax on the 'perl Makefile.PL' line, or, what the
problem is and how can I get DBD::mysql to load?
By the way, perl Makefile.PL --help gives me..
[EMAIL PROTECTED] DBD-mysql-3.0006]# perl -Makefile.PL --help
Usage: perl [switches] [--] [programfile] [arguments]
-0[octal] specify record separator (\0, if no argument)
-a autosplit mode with -n or -p (splits $_ into @F)
-C[number/list] enables the listed Unicode features
-c check syntax only (runs BEGIN and CHECK blocks)
-d[:debugger] run program under debugger
-D[number/list] set debugging flags (argument is a bit mask or alphabets)
-e program one line of program (several -e's allowed, omit programfile)
-f don't do $sitelib/sitecustomize.pl at startup
-F/pattern/ split() pattern for -a switch (//'s are optional)
-i[extension] edit files in place (makes backup if extension supplied)
-Idirectory specify @INC/#include directory (several -I's allowed)
-l[octal] enable line ending processing, specifies line terminator
-[mM][-]module execute use/no module... before executing program
-n assume while () { ... } loop around program
-p assume loop like -n but print line also, like sed
-P run program through C preprocessor before compilation
-s enable rudimentary parsing for switches after programfile
Re: Problem installing PERL DBD::mysql
Connie,
I had the same error with another program recently, but probably the
fix for yours is the same: try compile zlib with -fPIC by adding
-fPIC to CFLAGS in the Makefile.
Niels Larsen
Logg, Connie A. wrote:
Machine info:
[EMAIL PROTECTED] utils]$ uname -a
Linux snv1 2.6.9-34.0.2.ELsmp #1 SMP Fri Jun 30 10:32:04 EDT 2006 x86_64 x86_64
x86_64 GNU/Linux
[EMAIL PROTECTED] utils]$ perl -v
This is perl, v5.8.8 built for x86_64-linux
[EMAIL PROTECTED] utils]$ more /etc/redhat-release
Red Hat Enterprise Linux WS release 4 (Nahant Update 4)
--
I am having a hard time installing DBD:mysql on 64 bit machine snv1.ultralight.org
/usr/bin/perl MCPAN -e shell
CPAN install DBD::mysql
Dies with errors...so I decided to do it from the source, and it also
complainshere is the output from the source install:
[EMAIL PROTECTED] DBD-mysql-3.0006]# /usr/bin/perl Makefile.PL I will use the
following settings for compiling and testing:
cflags(mysql_config) = -I/usr/local/include/mysql
embedded (mysql_config) =
libs (mysql_config) = -L/usr/local/lib/mysql -lmysqlclient -lz
-lcrypt -lnsl -lm
mysql_config (guessed ) = mysql_config
nocatchstderr (default ) = 0
nofoundrows (default ) = 0
ssl (guessed ) = 0
testdb(default ) = test
testhost (default ) =
testpassword (default ) =
testsocket(default ) =
testuser (default ) =
To change these settings, see 'perl Makefile.PL --help' and 'perldoc INSTALL'.
Using DBI 1.52 (for perl 5.008008 on x86_64-linux) installed in
/usr/lib/perl5/site_perl/5.8.8/x86_64-linux/auto/DBI/
Writing Makefile for DBD::mysql
[EMAIL PROTECTED] DBD-mysql-3.0006]#
[EMAIL PROTECTED] DBD-mysql-3.0006]# make
cc -c -I/usr/lib/perl5/site_perl/5.8.8/x86_64-linux/auto/DBI
-I/usr/local/include/mysql -DDBD_MYSQL_INSERT_ID_IS_GOO D -g
-fno-strict-aliasing -pipe -Wdeclaration-after-statement -I/usr/local/include
-D_LARGEFILE_SOURCE -D_FILE_OFFSE
T_BITS=64 -I/usr/include/gdbm -O2 -DVERSION=\3.0006\ -DXS_VERSION=\3.0006\
-fpic -I/usr/lib/perl5/5.8.8/x86_64
-linux/CORE dbdimp.c
cc -c -I/usr/lib/perl5/site_perl/5.8.8/x86_64-linux/auto/DBI
-I/usr/local/include/mysql -DDBD_MYSQL_INSERT_ID_IS_GOO D -g
-fno-strict-aliasing -pipe -Wdeclaration-after-statement -I/usr/local/include
-D_LARGEFILE_SOURCE -D_FILE_OFFSE
T_BITS=64 -I/usr/include/gdbm -O2 -DVERSION=\3.0006\ -DXS_VERSION=\3.0006\
-fpic -I/usr/lib/perl5/5.8.8/x86_64
-linux/CORE mysql.c
mysql.xs: In function `XS_DBD__mysql__GetInfo_dbd_mysql_get_info':
mysql.xs:647: warning: ignoring return value of `Perl_newSViv', declared with
attribute warn_unused_result
mysql.xs:650: warning: ignoring return value of `Perl_newSViv', declared with
attribute warn_unused_result
mysql.xs:653: warning: ignoring return value of `Perl_newSVpv', declared with attribute
warn_unused_result Running Mkbootstrap for DBD::mysql () chmod 644 mysql.bs rm -f
blib/arch/auto/DBD/mysql/mysql.so LD_RUN_PATH=/usr/local/lib/mysql
/usr/bin/perl myld cc -shared -L/usr/local/lib dbdimp.o mysql.o -o blib/arch/aut
o/DBD/mysql/mysql.so\
-L/usr/local/lib/mysql -lmysqlclient -lz -lcrypt -lnsl -lm \
/usr/bin/ld: /usr/local/lib/libz.a(compress.o): relocation R_X86_64_32 against
`a local symbol' can not be used when making a shared object; recompile with
-fPIC
/usr/local/lib/libz.a: could not read symbols: Bad value
collect2: ld returned 1 exit status
make: *** [blib/arch/auto/DBD/mysql/mysql.so] Error 1
I have tried using -fPIC, but I think I do not know how to tell it to use that
correctly. I assume it is passed with --cflags on the 'perl Makefile.PL' line,
but I can't get that to work.
Can someone tell me the syntax on the 'perl Makefile.PL' line, or, what the
problem is and how can I get DBD::mysql to load?
By the way, perl Makefile.PL --help gives me..
[EMAIL PROTECTED] DBD-mysql-3.0006]# perl -Makefile.PL --help
Usage: perl [switches] [--] [programfile] [arguments]
-0[octal] specify record separator (\0, if no argument)
-a autosplit mode with -n or -p (splits $_ into @F)
-C[number/list] enables the listed Unicode features
-c check syntax only (runs BEGIN and CHECK blocks)
-d[:debugger] run program under debugger
-D[number/list] set debugging flags (argument is a bit mask or alphabets)
-e program one line of program (several -e's allowed, omit programfile)
-f don't do $sitelib/sitecustomize.pl at startup
-F/pattern/ split() pattern for -a switch (//'s are optional)
-i[extension] edit files in place (makes backup if extension supplied)
-Idirectory specify @INC/#include directory (several -I's allowed)
-l[octal] enable line ending processing, specifies line terminator
-[mM][-]module execute use/no module... before executing program
-n assume while () { ... } loop around program
Re: Problem installing PERL DBD::mysql
Niels Larsen wrote: Niels, Do you mean in the Makefile for zlib? Thanks! Patrick Connie, I had the same error with another program recently, but probably the fix for yours is the same: try compile zlib with -fPIC by adding -fPIC to CFLAGS in the Makefile. Niels Larsen Logg, Connie A. wrote: Machine info: [EMAIL PROTECTED] utils]$ uname -a Linux snv1 2.6.9-34.0.2.ELsmp #1 SMP Fri Jun 30 10:32:04 EDT 2006 x86_64 x86_64 x86_64 GNU/Linux [EMAIL PROTECTED] utils]$ perl -v This is perl, v5.8.8 built for x86_64-linux [EMAIL PROTECTED] utils]$ more /etc/redhat-release Red Hat Enterprise Linux WS release 4 (Nahant Update 4) -- I am having a hard time installing DBD:mysql on 64 bit machine snv1.ultralight.org /usr/bin/perl MCPAN -e shell CPAN install DBD::mysql Dies with errors...so I decided to do it from the source, and it also complainshere is the output from the source install: [EMAIL PROTECTED] DBD-mysql-3.0006]# /usr/bin/perl Makefile.PL I will use the following settings for compiling and testing: cflags(mysql_config) = -I/usr/local/include/mysql embedded (mysql_config) = libs (mysql_config) = -L/usr/local/lib/mysql -lmysqlclient -lz -lcrypt -lnsl -lm mysql_config (guessed ) = mysql_config nocatchstderr (default ) = 0 nofoundrows (default ) = 0 ssl (guessed ) = 0 testdb(default ) = test testhost (default ) = testpassword (default ) = testsocket(default ) = testuser (default ) = To change these settings, see 'perl Makefile.PL --help' and 'perldoc INSTALL'. Using DBI 1.52 (for perl 5.008008 on x86_64-linux) installed in /usr/lib/perl5/site_perl/5.8.8/x86_64-linux/auto/DBI/ Writing Makefile for DBD::mysql [EMAIL PROTECTED] DBD-mysql-3.0006]# [EMAIL PROTECTED] DBD-mysql-3.0006]# make cc -c -I/usr/lib/perl5/site_perl/5.8.8/x86_64-linux/auto/DBI -I/usr/local/include/mysql -DDBD_MYSQL_INSERT_ID_IS_GOO D -g -fno-strict-aliasing -pipe -Wdeclaration-after-statement -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSE T_BITS=64 -I/usr/include/gdbm -O2 -DVERSION=\3.0006\ -DXS_VERSION=\3.0006\ -fpic -I/usr/lib/perl5/5.8.8/x86_64 -linux/CORE dbdimp.c cc -c -I/usr/lib/perl5/site_perl/5.8.8/x86_64-linux/auto/DBI -I/usr/local/include/mysql -DDBD_MYSQL_INSERT_ID_IS_GOO D -g -fno-strict-aliasing -pipe -Wdeclaration-after-statement -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSE T_BITS=64 -I/usr/include/gdbm -O2 -DVERSION=\3.0006\ -DXS_VERSION=\3.0006\ -fpic -I/usr/lib/perl5/5.8.8/x86_64 -linux/CORE mysql.c mysql.xs: In function `XS_DBD__mysql__GetInfo_dbd_mysql_get_info': mysql.xs:647: warning: ignoring return value of `Perl_newSViv', declared with attribute warn_unused_result mysql.xs:650: warning: ignoring return value of `Perl_newSViv', declared with attribute warn_unused_result mysql.xs:653: warning: ignoring return value of `Perl_newSVpv', declared with attribute warn_unused_result Running Mkbootstrap for DBD::mysql () chmod 644 mysql.bs rm -f blib/arch/auto/DBD/mysql/mysql.so LD_RUN_PATH=/usr/local/lib/mysql /usr/bin/perl myld cc -shared -L/usr/local/lib dbdimp.o mysql.o -o blib/arch/aut o/DBD/mysql/mysql.so\ -L/usr/local/lib/mysql -lmysqlclient -lz -lcrypt -lnsl -lm \ /usr/bin/ld: /usr/local/lib/libz.a(compress.o): relocation R_X86_64_32 against `a local symbol' can not be used when making a shared object; recompile with -fPIC /usr/local/lib/libz.a: could not read symbols: Bad value collect2: ld returned 1 exit status make: *** [blib/arch/auto/DBD/mysql/mysql.so] Error 1 I have tried using -fPIC, but I think I do not know how to tell it to use that correctly. I assume it is passed with --cflags on the 'perl Makefile.PL' line, but I can't get that to work. Can someone tell me the syntax on the 'perl Makefile.PL' line, or, what the problem is and how can I get DBD::mysql to load? By the way, perl Makefile.PL --help gives me.. [EMAIL PROTECTED] DBD-mysql-3.0006]# perl -Makefile.PL --help Usage: perl [switches] [--] [programfile] [arguments] -0[octal] specify record separator (\0, if no argument) -a autosplit mode with -n or -p (splits $_ into @F) -C[number/list] enables the listed Unicode features -c check syntax only (runs BEGIN and CHECK blocks) -d[:debugger] run program under debugger -D[number/list] set debugging flags (argument is a bit mask or alphabets) -e program one line of program (several -e's allowed, omit programfile) -f don't do $sitelib/sitecustomize.pl at startup -F/pattern/ split() pattern for -a switch (//'s are optional) -i[extension] edit files in place (makes backup if extension supplied) -Idirectory specify @INC/#include directory (several -I's allowed) -l[octal] enable line ending processing, specifies line terminator -[mM][-]module
Re: Problem installing PERL DBD::mysql
Yes .. and had to add it to jpeg-6b (on x86_64) to make that work too. Patrick Galbraith wrote: Niels Larsen wrote: Niels, Do you mean in the Makefile for zlib? Thanks! Patrick Connie, I had the same error with another program recently, but probably the fix for yours is the same: try compile zlib with -fPIC by adding -fPIC to CFLAGS in the Makefile. Niels Larsen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Get a Numeric Zero instead of NULL in SELECT
Asif Lodhi wrote:
Hi,
I have a query:
reformatted
INSERT INTO tmp2 (x)
SELECT ((t3.m * 100) + b.id) AS x2
FROM tmp3 t3
LEFT JOIN (SELECT (MAX(x) - ((MAX(x) div 100) * 100)) + 1 AS pid
FROM tmp2
WHERE (x div 100) = 2147
HAVING (MAX(x) - ((MAX(x) div 100) * 100)) 483648) b
ON t3.m=b.pid
First, the outer SELECT:
SELECT ((t3.m * 100) + b.id) AS x2
Table b has no column `id`, so I expect that should be
SELECT ((t3.m * 100) + b.pid) AS x2
Yes?
Now, the inner SELECT:
SELECT (MAX(x) - ((MAX(x) div 100) * 100)) + 1 AS pid
FROM tmp2
WHERE (x div 100) = 2147
HAVING (((MAX(x)) - ((MAX(x) div 100) * 100)) 483648)
The SELECT clause:
As every row examined has x div 100 = 2147 (the WHERE condition), MAX(x) div
100 must be 2147, so (MAX(x) div 100) * 100) must be 214700.
Therefore,
(MAX(x) - ((MAX(x) div 100) * 100)) + 1
= MAX(x) - 214700 + 1
= MAX(x) - 214699
Thus, we can simplify your select to
SELECT MAX(x) - 214699 AS pid
Next, the WHERE clause:
Because the condition (x div 100) = 2147 compares a function of x to a
constant, no index on column x can be used to find matching rows. Always
compare the unmodified column to a constant or a range, if possible, so that an
index may be used. In this case, (x div 100) = 2147 is equivalent to
x BETWEEN 214700 AND 214799
Written this way, an index on column x could be used to find matching rows.
Finally, the HAVING clause:
Most of the work here has already been done in the SELECT clause, so there's no
need to recompute. That is,
MAX(x) - ((MAX(x) div 100) * 100) 483648
is equivalent to
pid - 1 483648
which, of course, is the same as
pid 483649
Putting it all together yields
SELECT MAX(x) - 214699 AS pid
FROM tmp2
WHERE x BETWEEN 214700 AND 214799
HAVING pid 483649
The table t3 has a single INT field m with a single record and
value: 2147. The table tmp2 has a single INT field x with no
records.
Really? You seem to be doing a lot of work to accomplish the equivalent of
INSERT INTO tmp2 (x) VALUES (214700);
I suppose the intent must be to get a single query that works when tmp2 is empty
and later when tmp2 has rows (as a result of previous INSERTs). How about this?
INSERT INTO tmp2 (x)
SELECT t3.m * 100 + IFNULL(b.pid, 0) AS x2
FROM tmp3 t3
LEFT JOIN ( SELECT MAX(x) - 214699 AS pid
FROM tmp2
WHERE x BETWEEN 214700 AND 214799
HAVING pid 483649)
ON t3.m = b.pid
The above code returns NULL in the 2nd column of the SELECT
What? There is no second column of either select.
that I can't add to or multiply with another number to get the final
value to insert into the table tmp2. I am using INNODB tables on
5.0.22 running on a WINDOWS-XP-SP2. Around 75 Clients connect from
VB6/Windows. I am STARTing TRANSACTIONs and COMMITing them from VB6
client-code. Since I am also using SQL STRICT mode with more stricter
parameters in the MY.INI.
I don't want to use functions as that will impair the query speed.
Do you know of any way that I could use to get a numeric ZERO instead
of a null in the 2ND column of the SELECT?
snip
Again, there is no second column, but you can use IFNULL().
Asif Lodhi wrote in a second message:
Hi,
I am replying to my own post to tell you that now I am using
CASE WHEN {condition} THEN END
construct to check for NULL and returning numeric ZERO. On the face of
it, CASE doesn't seem to be function - it's an operator - isn't?
I'd call it a function, but what's the difference? Why do you believe operators
are faster than functions? In any case, MAX() is a function. Perhaps you
won't mind adding an IFNULL() now that we've removed a MAX().
However, I would now like to ask you whether I can use some kind of an
Oracle-like USE_INDEX optimization hint in MySQL to get it to use a
specific index in the SELECT? I used explain on it and it tells me
that it's using index on all except on the the query I am using as the
2nd table. Is there any way I can speed it up?
Something doesn't make sense here. If tmp2 is empty, what difference does an
index make? How would an index be used to speed up a query on an empty table?
And how could it be slow in the first place?
In any case, as I explained above, use of an index on column x in the inner
query was impossible because the WHERE condition compared a _function of the
column_ (a value calculated from the value of x, whether by a function or use of
an operator) to some value. By rewriting the query to compare the actual value
of the column, use of an index becomes possible.
Any suggestions?
I get the impression you are implementing some sort of serial primary key where
the millions part means something. Have you considered a multi-part primary
key?
In any
Re: Possiable Trigger Use
On Monday 14 August 2006 12:35 pm, [EMAIL PROTECTED] wrote: Look at all of the dates and then if the data is 3 days old. Delete the corresponding tables related to that date, and then fianlly the row that told me what tables are related to that date. I'm kind of curious about the deleting of tables. Are you sure you didn't mean rows? Removing tables like will accept a table name and that's about it, you can't run conditions for it (as far as I know..). -- Chris White PHP Programmer/DBloomers Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: non-text data
All right, so how would one set up a select where rows 1 and 2 are text and row 3 is a blob and for argument sake assume jpg for the moment. You want to select a row based on the content (image) of the jpg? Not possible unless you pass the whole blob into mysql so it can retrieve the record which would be extremely slow and extremely expensive in terms of cpu / memory. As you probably saw in the cross-post to the php-general list, you're much better off just storing the filename in the database and storing the image itself on the filesystem. Then you can easily search for images with filename 'logo.jpg'. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Incrementing using Max(Field) ?
H you're close, very close. What you have actually does work, with a little tweaking - you have an extra set of parens. I'm on 5.0.21, FYI. insert into products (productsid) select max(productsid)+1 from products; Appears to work for both auto-incrementing and non-auto-inc columns. Dan On 8/14/06, William DeMasi [EMAIL PROTECTED] wrote: Does anyone have any ideas of how I can select the max value and insert the next highest value? I want something that would do something like this: Insert into table1 (select max(field1)+1 from table1); This obviously doesn't work. I know if the table was set to auto-increment it wouldn't be an issue, but I am not able to change its schema. Thank you. - William -- 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]
table hangs on a large query
Hello, I have a table (see below) that has 111599 records in it. When i do a query like select * from gallery_object where dir = 'dirname' this query takes a very long time and while the query is going on it locks the entire table so no one else can query it yet a query like select * from gallery_object where parent_id = 1 and dir ='dirname' and obj_type = 1 order by order_idx returns immediately Could someone please enlighten me Thanks CREATE TABLE gallery_object ( id int(3) unsigned NOT NULL auto_increment, obj_type int(10) unsigned default NULL, dir varchar(50) default NULL, path varchar(255) default NULL, parent_id int(10) default NULL, title varchar(100) default NULL, description varchar(255) default NULL, bgcolor varchar(15) default NULL, font varchar(50) default NULL, fontsize varchar(10) default NULL, fontcolor varchar(15) default NULL, rows_per_page int(10) unsigned default NULL, cols_per_page int(10) unsigned default NULL, image varchar(255) default NULL, sized_image varchar(255) default NULL, thumb_image varchar(255) default NULL, frame varchar(255) default NULL, order_idx int(10) unsigned default NULL, date_created timestamp(14) NOT NULL, isNested tinyint(3) unsigned default '0', PRIMARY KEY (id), UNIQUE KEY id (id), KEY id_2 (id), KEY type_idx (obj_type), KEY dir_idx (dir), KEY order_idx (order_idx) ) TYPE=MyISAM; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: table hangs on a large query
Randy Paries wrote: Hello, I have a table (see below) that has 111599 records in it. When i do a query like select * from gallery_object where dir = 'dirname' Use explain to see if it's using the index: explain select * from it could be that for that particular directory you have a lot of data - in which case mysql can't use an index because it covers too much of the table. I think it's roughly 30% that the switch happens (so if the data you're querying covers more than 30% of the table, the index isn't used). Check with something like this: select dir, count(*) as blah from gallery_object group by dir order by blah desc limit 10; it will show you how many records each 'dir' has for the top 10. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: table hangs on a large query
Randy, it's possible your indexes are sadly out of date, or corrupted somehow. Can you post the output of EXPLAIN select * from gallery_object where dir = 'dirname'; as well as of EXPLAIN select * from gallery_object where parent_id = 1 and dir ='dirname' and obj_type = 1 order by order_idx; Also, what does very long time equate to? It may not be related, but this bit of your table-create is interesting - I wonder if you have three indexes on your id column as a result: PRIMARY KEY (id), UNIQUE KEY id (id), KEY id_2 (id), PRIMARY KEY implies UNIQUE, and the id_2 index is redundant to either of the others. Dan On 8/14/06, Randy Paries [EMAIL PROTECTED] wrote: Hello, I have a table (see below) that has 111599 records in it. When i do a query like select * from gallery_object where dir = 'dirname' this query takes a very long time and while the query is going on it locks the entire table so no one else can query it yet a query like select * from gallery_object where parent_id = 1 and dir ='dirname' and obj_type = 1 order by order_idx returns immediately Could someone please enlighten me Thanks CREATE TABLE gallery_object ( id int(3) unsigned NOT NULL auto_increment, obj_type int(10) unsigned default NULL, dir varchar(50) default NULL, path varchar(255) default NULL, parent_id int(10) default NULL, title varchar(100) default NULL, description varchar(255) default NULL, bgcolor varchar(15) default NULL, font varchar(50) default NULL, fontsize varchar(10) default NULL, fontcolor varchar(15) default NULL, rows_per_page int(10) unsigned default NULL, cols_per_page int(10) unsigned default NULL, image varchar(255) default NULL, sized_image varchar(255) default NULL, thumb_image varchar(255) default NULL, frame varchar(255) default NULL, order_idx int(10) unsigned default NULL, date_created timestamp(14) NOT NULL, isNested tinyint(3) unsigned default '0', PRIMARY KEY (id), UNIQUE KEY id (id), KEY id_2 (id), KEY type_idx (obj_type), KEY dir_idx (dir), KEY order_idx (order_idx) ) TYPE=MyISAM; -- 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]
