Date Comparison Issue
MYSQL I have a table with a date field. I have a query that needs to output only if NOW()ed3(date field) ed2 is datetime. The issue is this: select * from thesites where ED3NOW() ED3='1999-12-31 00:00:00' the above query will output when ED3 is as above. NOW()='2001-09-15 18:10:44' ??? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Query Question
I'm doing a query like: select Title from inv where Title LIKE '%TOMMY%' OR Content LIKE '%TOMMY%' OR Notes LIKE '%TOMMY%' order by Title What I need is to have the results that match Title LIKE '%TOMMY%' to appear first and then the rest. I have been doing 2 separate queries and filtering the first results from the second in a script. I would like to do this with a single query. Can that be done? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Query Question
Close. Now I need to get the order correct. I need the order like Tommy Tommy Lee Baseball(matched with Tommy in the Content or Notes field) World Series (matched with Tommy in the Content or Notes field) I've been playing with something like: select Title, concat(IF(Title LIKE %TOMMY%, 1, 0),LPAD(90-ASCII(left (Title,1)),2,'0')) as check from inv where (Title LIKE 'tommy%') OR (Content LIKE '%tommy%') OR (Notes LIKE '% tommy%') order by check desc but that doesn't get it close enough. Only compares the frist char in Title. I need a more robost idea... Ravi Raman wrote: hi. select Title, IF(Title LIKE %TOMMY%, 1, 0) as check from inv where... ... order by check desc; hth. -ravi. -Original Message- From: Carl Schrader [mailto:[EMAIL PROTECTED]] Sent: Tuesday, September 04, 2001 10:30 AM To: [EMAIL PROTECTED] Subject: Query Question I'm doing a query like: select Title from inv where Title LIKE '%TOMMY%' OR Content LIKE '%TOMMY%' OR Notes LIKE '%TOMMY%' order by Title What I need is to have the results that match Title LIKE '%TOMMY%' to appear first and then the rest. I have been doing 2 separate queries and filtering the first results from the second in a script. I would like to do this with a single query. Can that be done? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Complex Query Question
This question involves table setup questions as much as it involves a query question. Bare with me, this seems hard to explain. I have a table that includes records with 3 (relevant to this conversation) fields (actually 9..more on that later). Each of those fields have 2 other related fields. i.e.. Field Special 1 has a related field with a Start Date and another field for End date. Field Special 2 has a related field with a Start Date and another field for End date. Field Special 3 has a related field with a Start Date and another field for End date. I will probably need to add up to 3 other fields to keep track of the last shown date. Anyhow, I need to build a query that will return 5-10 Specials. I want to output this very much like an ad banner query would. That is to say, I need to keep track of the last one that was shown and output the next one. What complicates this is that there are UP TO to 3 specials per record. Some of them may not include any specials at all. Some may have 1 or 2. Special 1 should be shown before Special 2. etc. Only 1 Special from each record should be output. i.e.. Special 1 and Special 2 shouldn't be output from record #1. (Actually I would like to have that occur if there are not 5-10 other results.. but I can live without that..) The question: Is there anyway I build a single query to accomplish this? Since this is in the early design mode, I can add/delete any current fields and build it differently as needed. Alternately, I could just randomly output 1 special from each record that has one..I'd prefer the other method though... I just need some advice from some people who are more fluent in sql :) TIA! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Query Question
I'm doing a query like: select Title from inv where Title LIKE '%TOMMY%' OR Content LIKE '%TOMMY%' OR Notes LIKE '%TOMMY%' order by Title What I need is to have the results that match Title LIKE '%TOMMY%' to appear first and then the rest. I have been doing 2 separate queries and filtering the first results from the second in a script. I would like to do this with a single query. Can that be done? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Group By Question
I have a query that is using Group By Title. Is there anyway to make 2 Titles such as League of their own and League of their own### group as the same. I am removing the #'s in the output but I still get 2 results - 1 for each. Ideas? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Results came from
Is there anyway to determine which part of the an sql query where blahblah produced the result? ie. select Title from inv where Title LIKE '%Green' OR Title Like '%Blue%' I need to distinguish if the Title was like Green or Blue.. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Results came from
Eric, I'm using php. The problem is that the where may have 100 or so conditions. I was hoping that mysql had some built in function or variable that would return this for me. I guess I'll have to code it in php.. argh. Eric Fitzgerald wrote: What language are you using to prepare that query? If it's perl, do something like if ($title =~ /Green/) If it's php, do something like if (ereg(Green,$title)) If it's C, cycle the string finding that This is a job for the client truthfully...but if you absolutly MUST do this server side, here is your query: select Title, IF(Title LIKE '%Green','Green','Blue') AS whatmatch - Original Message - From: Carl Schrader [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, May 21, 2001 10:56 AM Subject: Re: Results came from Is there anyway to determine which part of the an sql query where blahblah produced the result? ie. select Title from inv where Title LIKE '%Green' OR Title Like '%Blue%' I need to distinguish if the Title was like Green or Blue.. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Telnet into MySQL
CRT (www.vandyke.com) Tim Thorburn wrote: Hello, Can someone here recommend a good Telnet program (with SSH) that I can use to connect to my servers MySQL database? Preferably something shareware/freeware - but if it must be purchased, I'm not against that. Thank you -Tim - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Speed question
Ok, thanks for the input. I'll work on optimizing the code and look at the indexes. ryc wrote: Changing to C++ is not likely to give you a noticable speed difference because your bottleneck is not the code but the queries. With proper database design you should be able to acheive those results with one query, and it should be fast if given the right indexes. ryan I am searching to speed up an operation I have, which works - it just takes minutes to finish. The current script is written in pike (a C like scripting language). I believe that most of the overhead is the multiple queries. Would using C++ be significantly faster? (I'd have to learn some C before I could do this, so, the speed increase needs to be worth the effort). Let me explain the situation: Table 1 is defined as: MM;varchar(4) Title;varchar(48) Archive;varchar(4) record_num;int(8) unsigned zerofill I am given a list of Titles. With these Title's I need to search Table 1 for the Title and output as follows: Green Mile 10 35mm TRLR 6 16mm BCAM Cast Away 4 35mm 20 BETA In the above example, Green Mile=Title 35mm=MM TRLR=Archive etc. So we have 10 35mm TRLR of Green Mile. Currently I am querying several times to get the result. This is slow because the list could be 50 Titles. Thanks for any advance you can give on this. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Speed question
I am searching to speed up an operation I have, which works - it just takes minutes to finish. The current script is written in pike (a C like scripting language). I believe that most of the overhead is the multiple queries. Would using C++ be significantly faster? (I'd have to learn some C before I could do this, so, the speed increase needs to be worth the effort). Let me explain the situation: Table 1 is defined as: MM;varchar(4) Title;varchar(48) Archive;varchar(4) record_num;int(8) unsigned zerofill I am given a list of Titles. With these Title's I need to search Table 1 for the Title and output as follows: Green Mile 10 35mm TRLR 6 16mm BCAM Cast Away 4 35mm 20 BETA In the above example, Green Mile=Title 35mm=MM TRLR=Archive etc. So we have 10 35mm TRLR of Green Mile. Currently I am querying several times to get the result. This is slow because the list could be 50 Titles. Thanks for any advance you can give on this. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Speed question
No, by multiple queries I mean that I have to querie for every title. The query is: Select if(Year'+y+','1','0') as quest,MM,Archive,record_num from inv where ((REPLACE(Title,'#','')='+t+' or REPLACE(Title,'#','')='+s+' or REPLACE(Title,'#','')='+u+' or REPLACE(Title,'#','')='+v+' or REPLACE(Title,'#','')='+w+') or Mtitle='+mu+' or Mtitle='+mu2+') and ((Year'+(((int)y)-3)+' AND Year'+(((int)y)+3)+') or Year='')+(id-variables-pi==1? AND MM='PAPR' :)+ ORDER BY Archive,MM This is from the code and the variables such as y (+y+) are replaced as it loops through. What I have to do with the results of this is to parse through and count the MM and Archive differences. Needless to say, this code is just inefficient and the reason why I know there has to be a better way. I have played with some other code that uses a querie with a count but I ran into some snags with that as well. Kevin Pratt wrote: Hello Are you querying once for each field? Can you put up a sample of the query that you are doing? If you are querying for each filed to build your result you should compact the query in to one i.e. if you are doing select MM from table1 where record_num = 3 select Title from table1 where record_num = 3 select Archive from table1 where record_num = 3 then you should replace these lines with something like select MM,Title,Archive from table1 where record_num = 3 this will return faster than doing each query separately Kevin - Original Message - From: Carl Schrader [EMAIL PROTECTED] To: Mysql [EMAIL PROTECTED] Sent: Wednesday, May 16, 2001 9:08 AM Subject: Speed question I am searching to speed up an operation I have, which works - it just takes minutes to finish. The current script is written in pike (a C like scripting language). I believe that most of the overhead is the multiple queries. Would using C++ be significantly faster? (I'd have to learn some C before I could do this, so, the speed increase needs to be worth the effort). Let me explain the situation: Table 1 is defined as: MM;varchar(4) Title;varchar(48) Archive;varchar(4) record_num;int(8) unsigned zerofill I am given a list of Titles. With these Title's I need to search Table 1 for the Title and output as follows: Green Mile 10 35mm TRLR 6 16mm BCAM Cast Away 4 35mm 20 BETA In the above example, Green Mile=Title 35mm=MM TRLR=Archive etc. So we have 10 35mm TRLR of Green Mile. Currently I am querying several times to get the result. This is slow because the list could be 50 Titles. Thanks for any advance you can give on this. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Speed question
7 or so. I have indexes on most of the columns being used.. Enrique Vizcarra wrote: 1) How large the data base is it? how many records? 2) Are you using index? I has tested MySql serving with Pentium II 266 with Windows 98 2da. Ed. operating System and with one table consisting of 762000 records. Even before I indexed the database, speed of MySql was great. Of course, after table was indexed MySql speed up. I remember, once time before I had slows queries from MySql (too slow) until I re-install Windows in my work station computer. maybe some systems files was damaged. From: Carl Schrader [EMAIL PROTECTED] To: Mysql [EMAIL PROTECTED] Subject: Speed question Date: Wed, 16 May 2001 08:08:15 -0700 I am searching to speed up an operation I have, which works - it just takes minutes to finish. The current script is written in pike (a C like scripting language). I believe that most of the overhead is the multiple queries. Would using C++ be significantly faster? (I'd have to learn some C before I could do this, so, the speed increase needs to be worth the effort). Let me explain the situation: Table 1 is defined as: MM;varchar(4) Title;varchar(48) Archive;varchar(4) record_num;int(8) unsigned zerofill I am given a list of Titles. With these Title's I need to search Table 1 for the Title and output as follows: Green Mile 10 35mm TRLR 6 16mm BCAM Cast Away 4 35mm 20 BETA In the above example, Green Mile=Title 35mm=MM TRLR=Archive etc. So we have 10 35mm TRLR of Green Mile. Currently I am querying several times to get the result. This is slow because the list could be 50 Titles. Thanks for any advance you can give on this. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Join question
I have 2 tables with a common field of Title. Table 1 defined as: Name;varchar(60) Title;varchar(60) Year;varchar(4) Other;varchar(30) Other2;varchar(30) act_no;int(8) unsigned zerofill Table 2 defined as: MM;varchar(4) Title;varchar(48) Archive;varchar(4) record_num;int(8) unsigned zerofill I need to search Table 1 for an persons name. ie. searching Table 1 for Tom Hanks may return: Green Mile Cast Away and then I need to find out how many individual items for each title are in Table 2. ie return Green Mile 10 35mm 6 16mm Cast Away 4 35mm 20 BETA I have it working by using 2 seperate select calls. I'd like to use join, if possible, to speed this process up. TIA! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
