Re: Query Help
Jason Chan wrote: Jason Chan wrote: I have a student Table and a SubjectGrade table Create Table Student( StudentID INT NOT NULL, StudentName VARCHAR(30) ) Create Table SubjectGrade( StudentID INT NOT NULL, Subject VARCHAR(30) NOT NULL, Grade CHAR(1) ) let's say have following record in SubjectGrade 1MathsA 1PhysB 1ChemA 2MathsA 2ChemA 3BioC 3ChemA I want to find out students who have got A in both Maths and Chem How the SQL look like? select s.StudentID , s.StudentName from Student as s , SubjectGrade as sj where s.studentID = sj.studentID and sj.Grade = 'A' and (sj.Subject = 'Maths' or sj.Subject = 'Chem') I think your query will return student 3 as well My apologies. Misunderstood the question... -- Arno Coetzee Developer Flash Media Group Office : 2712 342 7595 Mobile : 2782 693 6180 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: cannot drop database
Try DROP database `ÃáãÃáÃ`; Gary Huntress wrote: I need to drop a database named ÃáãÃáà using the mysql client. I'm getting you have an error in your sql syntax for the command DROP database ÃáãÃáÃ; I'm sure this is a character set issue. How can I drop this database? Regards, Gary H. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: cannot drop database
I need to drop a database named ÃáãÃáà using the mysql client. I'm getting you have an error in your sql syntax for the command DROP database ÃáãÃáÃ; I'm sure this is a character set issue. How can I drop this database? What about using backticks around it: drop database `yourname` With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question on Join
Manoj wrote: Dear All, I am trying to join two tables say a b. Both tables have a set of dates. I want to join the tables in such a fashion that I retrieve all dates from table A. If table b has corresponding data (for that date) then it will display it or else will display null. I am pretty sure that I can solve this using left outer join but am not able to get it working hence any help would be appreciated! Try something like this: SELECT A.date,B.date FROM A LEFT JOIN B ON B.date=A.date -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to include a dynamic function result in a view?
I have a table with date values: ++ | theDate| ++ | 2005-08-15 | | 2005-08-16 | | 2005-08-14 | ++ I can execute this select statement on it: select * from tDay where theDate date_sub( now(), interval 1 day); And I can create a view from it: create view dateTest as select * from tDay where theDate date_sub( now(), interval 1 day); On creating the view, the date_sub function is replaced by its result, though, so that the view does not generate dynamic results: show create table dateTest; CREATE ALGORITHM=UNDEFINED VIEW `kurse`.`dateTest` AS select sql_no_cache `kurse`.`tDay`.`theDate` AS `theDate` from `kurse`.`tDay` where (`kurse`.`tDay`.`theDate` 20050814) How can I avoid this replacement and make this view a truly dynamic one? s.m. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: cannot drop database
Hi Gary, If you are running unix (or variants thereof), you can go to the data directory and remove it at the operating system level if the mysql client can't do it. Not sure about windows though but I would think the same thing would apply. If you do that and then do a show databases, it should be gone. Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: Gary Huntress [mailto:[EMAIL PROTECTED] Sent: Monday, 15 August 2005 9:09 AM To: mysql@lists.mysql.com Subject: cannot drop database I need to drop a database named ÃáãÃáà using the mysql client. I'm getting you have an error in your sql syntax for the command DROP database ÃáãÃáÃ; I'm sure this is a character set issue. How can I drop this database? Regards, Gary H. -- 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]
install as a non-root in /xyz directory on Solaris
Hello, I tried to google and search on mysql.com but couldn't find anything on installing as a non root and in /xyz directory on Solaris. Perhaps I miss it? I have downloaded mysql-standard-4.1.13-sun-solaris2.8-sparc-64bit.tar.gz. The instruction there is simply for a root user and in a stanard directory. Thanks for your help. Isarin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: spatial extensions - SRID
Andras Kende [EMAIL PROTECTED] wrote on 08/13/2005 10:32:07 PM: Hello, I have a html page with 70+ form fields some like 40 fields are only used for entering quantity numbers… Don’t want to do Mysql table with 70 fields… Is it a good idea to put this 50 fields of the form fields into a single text mysql field? Somehow process it with php before, put inside of some kind of xml structure? Thanks, Andras Kende http://www.kende.com You ask, Is it a good idea to put this 50 fields of the form fields into a single text mysql field?. IMHO, the answer is No. To do so is to defeat the purpose of using a database in the first place. You might as well be saving your data into a text file. In proper database design, every single logical item of information should be contained within a single row with the elements of that item of information all residing within their own columns in that row. To merge several fields of information into one larger field actually _removes_ useful information from your data and makes it harder to work with. It is quite common to have several dozen fields defined within a single table. More than seventy columns is definitely NOT the widest table I have ever seen (I have seen network monitoring tables that were hundreds of columns wide, for example). If you need help modelling your data into an efficient database schema, talk to us (the list) about it and I am sure we can help. You describe what you want to store and we can offer our opinions on how to store it. The more details you provide, the better we can help. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
French Characters, Still no answer
I am still having trouble with french characters if anyone has ANY ideas, please help. We have installed the newest version of MySql and cannot get it to play nice with French characters. Our older version worked fine. The problem may (or may not) be that when we put the dump into the new database(yes its default charset is Utf8) the default character set for the table is Utf8 but some fields are like this: 'Story' longtext character set latin1 NOT NULL We tried linking our tomcat to the old database on the other server through this tomcat and everything works fine but when we link the tomcat back to the new database, it will not play nice with french characters. (they come out as outlined squares etc) Any ideas would be greatly appreciated James
Change columm Name
All, How do we change column name in the tables where there are constraint as PK, FK,...? V/R Nguyen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: French Characters, Still no answer
Still no answer, perhaps, but ther'es still no question. Per my earlier response... What version of MySQL is the old version you refer to, what version is the new version you refer to? With that information someone here is more likely to be able to tell you something useful... without that you're not likely to get much of a response. Best Regards, Bruce On Aug 15, 2005, at 7:59 AM, James Sherwood wrote: I am still having trouble with french characters if anyone has ANY ideas, please help. We have installed the newest version of MySql and cannot get it to play nice with French characters. Our older version worked fine. The problem may (or may not) be that when we put the dump into the new database(yes its default charset is Utf8) the default character set for the table is Utf8 but some fields are like this: 'Story' longtext character set latin1 NOT NULL We tried linking our tomcat to the old database on the other server through this tomcat and everything works fine but when we link the tomcat back to the new database, it will not play nice with french characters. (they come out as outlined squares etc) Any ideas would be greatly appreciated James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: French Characters, Still no answer
The older version is 4.xx.xx im not sure how to tell. New version is 4.1.12 Thank you, James - Original Message - From: Bruce Dembecki [EMAIL PROTECTED] To: James Sherwood [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Monday, August 15, 2005 2:50 PM Subject: Re: French Characters, Still no answer Still no answer, perhaps, but ther'es still no question. Per my earlier response... What version of MySQL is the old version you refer to, what version is the new version you refer to? With that information someone here is more likely to be able to tell you something useful... without that you're not likely to get much of a response. Best Regards, Bruce On Aug 15, 2005, at 7:59 AM, James Sherwood wrote: I am still having trouble with french characters if anyone has ANY ideas, please help. We have installed the newest version of MySql and cannot get it to play nice with French characters. Our older version worked fine. The problem may (or may not) be that when we put the dump into the new database(yes its default charset is Utf8) the default character set for the table is Utf8 but some fields are like this: 'Story' longtext character set latin1 NOT NULL We tried linking our tomcat to the old database on the other server through this tomcat and everything works fine but when we link the tomcat back to the new database, it will not play nice with french characters. (they come out as outlined squares etc) Any ideas would be greatly appreciated James -- 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: French Characters, Still no answer
Hello. You've already got a good answer: http://lists.mysql.com/mysql/187794 Subscribe to mysql list or use web interface. Why are you mixing latin1 with utf8 in the same column? You can check if something is wrong with your connection variables using the following statement: show variables like '%char%'; Use mysql command line client or mysql-query-browser (or other client software which correctly handles UTF8 data) to check if import of your data was performed successfully. In such a way you can localize the problem, and find whether it is related to Tomcat or wrong data in MySQL. I am still having trouble with french characters if anyone has ANY ideas, please help. We have installed the newest version of MySql and cannot get it to play nice with French characters. Our older version worked fine. The problem may (or may not) be that when we put the dump into the new database(yes its default charset is Utf8) the default character set for the table is Utf8 but some fields are like this: 'Story' longtext character set latin1 NOT NULL We tried linking our tomcat to the old database on the other server through this tomcat and everything works fine but when we link the tomcat back to the new database, it will not play nice with french characters. (they come out as outlined squares etc) Any ideas would be greatly appreciated James James Sherwood [EMAIL PROTECTED] wrote: -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to include a dynamic function result in a view?
Hello. What version of MySQL do you use? On my 5.0.11 I have a correct result: mysql show create table dateTest\G; *** 1. row *** View: dateTest Create View: CREATE ALGORITHM=UNDEFINED VIEW `test`.`dateTest` AS select sql_no_cache `test`.`tDay`.`theDate` AS `theDate`,`test`.`tDay`.`a` AS `a` from `test`.`tDay` where (`test`.`tDay`.`theDate` (now() - interval 1 day)) [EMAIL PROTECTED] wrote: I have a table with date values: ++ | theDate| ++ | 2005-08-15 | | 2005-08-16 | | 2005-08-14 | ++ I can execute this select statement on it: select * from tDay where theDate date_sub( now(), interval 1 day); And I can create a view from it: create view dateTest as select * from tDay where theDate date_sub( now(), interval 1 day); On creating the view, the date_sub function is replaced by its result, though, so that the view does not generate dynamic results: show create table dateTest; CREATE ALGORITHM=UNDEFINED VIEW `kurse`.`dateTest` AS select sql_no_cache `kurse`.`tDay`.`theDate` AS `theDate` from `kurse`.`tDay` where (`kurse`.`tDay`.`theDate` 20050814) How can I avoid this replacement and make this view a truly dynamic one? s.m. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Post-Installation Setup Problems: error[2002 1604]
Hello.
I'm getting the following problems:
Login Problem:
See:
http://dev.mysql.com/doc/mysql/en/can-not-connect-to-server.html
Problems unencrypting password:
OLD_PASSWORD() is available as of MySQL 4.1. 4.0.20 is a very old
version. I strongly recommend you to upgrade to the latest release
(4.1.13 now).
Tim Johnson [EMAIL PROTECTED] wrote:
Hello:
OS: Linux Slackware 10.0
Ver: 4.0.20
Network Status: Closed to outside world
After installation:
I'm getting the following problems:
Login Problem:
mysql --host=localhost --user=root --password=**
ERROR 2002: Can't connect to local MySQL server through socket
'/var/lib/mysql/mysql.sock' (13)
What works:
mysql -u tim -p
Enter password: * # works
---
Problems unencrypting password:
SET PASSWORD FOR 'tim'@'localhost' = OLD_PASSWORD('**')
ERROR 1064: You have an error in your SQL syntax. Check the manual that
corresponds to your MySQL server version for the right syntax to use
near
'OLD_PASSWORD('marion')' at line 1
## Note: am unclear what is wrong with syntax
## Host table dump below:
SELECT Host, User FROM mysql.user;
+---+--+
| Host | User |
+---+--+
| linus | |
| linus | root |
| linus | tim |
| linus.johnson.com | tim |
| localhost | |
| localhost | root |
| localhost | tim |
+---+--+
Any advice is appreciated:
Am working from the installed docs.
Thanks
tj
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Change columm Name
Hello. May be set FOREIGN_KEY_CHECKS=0, alter the definitions of your tables, and set FOREIGN_KEY_CHECKS=1. See: http://dev.mysql.com/doc/mysql/en/innodb-foreign-key-constraints.html Nguyen, Phong [EMAIL PROTECTED] wrote: All, How do we change column name in the tables where there are constraint as PK, FK,...? V/R Nguyen -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: install as a non-root in /xyz directory on Solaris
Hello. I have used a shell on a Solaris box, and successfully ran MySQL Server under my non-root account. I didn't have any problems installing it, except that I was unable to start it automatically on boot (I didn't have permission to write to system startup files). Specify your configuration settings, say, in /your/home/my.cnf. Then start MySQL Server using mysqld_safe --defaults-file=/your/home/my.cnf. Here is the part of my configuration file for MySQL on Linux, however, in my opinion, it is quite suitable for Solaris. [client] port= 3734 socket = /home/gleb/mysqls/tmp/mysql.sock.gleb.t default_character_set=utf8 character-sets-dir= /home/gleb/mysqls/mysql-debug-5.0.11-beta-linux-i686/share/mysql/cha rsets [mysqld] log_slow_queries=/home/gleb/mysqls/logs/mysql.log-slow.s long_query_time=1 set-variable=user=gleb default_character_set=utf8 port= 3734 socket = /home/gleb/mysqls/tmp/mysql.sock.gleb.t pid-file= /home/gleb/mysqls/tmp/mysql.pid.gleb.t log = /home/gleb/mysqls/logs/mysql.log.t log-bin = /home/gleb/mysqls/logs/log_t/log_t log-error = /home/gleb/mysqls/logs/mysql.log-error.t datadir =/home/gleb/mysqls/mysql-debug-5.0.11-beta-linux-i686/data key_buffer = 16K max_allowed_packet = 50M table_cache = 4 sort_buffer_size = 64K read_buffer_size = 256K read_rnd_buffer_size = 256K net_buffer_length = 2K thread_stack = 64K wait_timeout=1000 net_read_timeout = slave_net_timeout = 2555 interactive_timeout=50 server-id = 1 H S [EMAIL PROTECTED] wrote: Hello, I tried to google and search on mysql.com but couldn't find anything on installing as a non root and in /xyz directory on Solaris. =20 Perhaps I miss it? I have downloaded=20 mysql-standard-4.1.13-sun-solaris2.8-sparc-64bit.tar.gz. The instruction there is simply for a root user and in a stanard directory. Thanks for your help. Isarin -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
character encoding
hello... i'm having some troubles storing data in mysql and thought you can help, here is my problem: i'm capturing raw audio/video data and want to store it into mysql. in my c++ program i create the query to do this, i use sprintf to create this query using %s format for the printing. using PTRACE, i print the query and the data is ok. but, when i connect to the database, the data is wrong. i know this because i create a wav file at the same time that the data is inserted in the database; i can play this wav file, it is ok. the data in the wav file and the data in the database does not match! i've been reading about the character set/collate in mysql, but don´t know which one should i use. does anybody know? thanks for any reply... best regards, karima
Native XML Support
Does MySQL 5 provide native XML support? ie, can I have a stored procedure return an XML string instead of a recordset? Can I pass in an XML string/doc and have the DB update relational tables based on it? Thanks. Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: character encoding
Karima Velasquez wrote: i'm capturing raw audio/video data and want to store it into mysql. in my c++ program i create the query to do this, i use sprintf to create this query using %s format for the printing. Um, you are aware that C strings (which sprintf uses) are null-terminated, and that nulls will be _everywhere_ in raw video and audio data, right? You need to dig up some sample code on using BLOB columns. That will show you how to insert raw binary data into a table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: character encoding
i know about the null terminating character, but i don't think this is the problem. comparing with the wav file, i notice that there are some characters changed, e.g. rigth data: 82 wrong data: rigth data: ^ wrong data: so, i don't think that this is the main issue that i'm facing here... any toughts? On 8/15/05, Warren Young [EMAIL PROTECTED] wrote: Karima Velasquez wrote: i'm capturing raw audio/video data and want to store it into mysql. in my c++ program i create the query to do this, i use sprintf to create this query using %s format for the printing. Um, you are aware that C strings (which sprintf uses) are null-terminated, and that nulls will be _everywhere_ in raw video and audio data, right? You need to dig up some sample code on using BLOB columns. That will show you how to insert raw binary data into a table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Native XML Support
Does MySQL 5 provide native XML support? ie, can I have a stored procedure return an XML string instead of a recordset? Can I pass in an XML string/doc and have the DB update relational tables based on it? native xml support, now, that's probably the funniest thing I've heard all day :-) What would that actually be? With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
unix timestamp
i have this query: SELECT COUNT(*) AS score FROM downloads WHERE date_add(dateline, interval 1 hour) = now() GROUP BY filename ORDER BY score DESC unfortunately for other reasons i had to change `dateline` to unix timestamp so this query is no longer able to run as intended. can anyone help with a work around? btw, i am using php to run queries if that helps find a solution. -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.9/72 - Release Date: 8/14/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Post-Installation Setup Problems: error[2002 1604]
* Gleb Paharenko [EMAIL PROTECTED] [050815 10:59]: Hello. I'm getting the following problems: Login Problem: See: http://dev.mysql.com/doc/mysql/en/can-not-connect-to-server.html That's going to be really helpful. Thanks. Problems unencrypting password: OLD_PASSWORD() is available as of MySQL 4.1. 4.0.20 is a very old version. I strongly recommend you to upgrade to the latest release (4.1.13 now). grin it get worse . most of my commercial work is running on servers with ver 3.23.X (sun and RH servers). I enjoy a great relationship with my domain hoster (who is also a programmer who uses mysql a lot), but he has been very cautious about upgrading any of his servers, being concerned about code breaking, etc. What argument might be provided to him to upgrade and what caveats might be cited? Thanks very much for the pointer to the docs. regards tim -- Tim Johnson [EMAIL PROTECTED] http://www.alaska-internet-solutions.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: unix timestamp
Sebastian [EMAIL PROTECTED] wrote on 08/15/2005 03:51:05 PM: i have this query: SELECT COUNT(*) AS score FROM downloads WHERE date_add(dateline, interval 1 hour) = now() GROUP BY filename ORDER BY score DESC unfortunately for other reasons i had to change `dateline` to unix timestamp so this query is no longer able to run as intended. can anyone help with a work around? btw, i am using php to run queries if that helps find a solution. -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.9/72 - Release Date: 8/14/2005 One possible solution permutation... SELECT count(*) FROM downloads where dateline UNIX_TIMESTAMP(now()) - 3600; Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: unix timestamp
Well, you could use the FROM_UNIXTIME() function to convert it into a datetime MySQL understands. SELECT COUNT(*) AS score FROM downloads WHERE date_add(FROM_UNIXTIME(dateline), interval 1 hour) = now() GROUP BY filename ORDER BY score DESC But, considering what you're doing, it would probably be better if you just skipped all the MySQL date functions and just used UNIX_TIMESTAMP() instead. SELECT COUNT(*) AS score FROM downloads WHERE UNIX_TIMESTAMP() - dateline = 3600 GROUP BY filename ORDER BY score DESC I haven't tested these, but you should be looking at the manul anyway. Chris FROM_UNIXTIME(): http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html#id2724743 UNIX_TIMESTAMP(): http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html#id2726862 Sebastian wrote: i have this query: SELECT COUNT(*) AS score FROM downloads WHERE date_add(dateline, interval 1 hour) = now() GROUP BY filename ORDER BY score DESC unfortunately for other reasons i had to change `dateline` to unix timestamp so this query is no longer able to run as intended. can anyone help with a work around? btw, i am using php to run queries if that helps find a solution. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Native XML Support
Martijn Tonies wrote: Does MySQL 5 provide native XML support? ie, can I have a stored procedure return an XML string instead of a recordset? Can I pass in an XML string/doc and have the DB update relational tables based on it? native xml support, now, that's probably the funniest thing I've heard all day :-) What would that actually be? something like this I suppose http://mysql.r18.ru/~bar/myxml/XMLXpathSupportInMySQL.pdf With regards, Martijn Tonies -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Native XML Support
Scott Klarenbach [EMAIL PROTECTED] writes: Does MySQL 5 provide native XML support? ie, can I have a stored procedure return an XML string instead of a recordset? Can I pass in an XML string/doc and have the DB update relational tables based on it? MyXML is supposed to help with this sort of thing: http://tangent.org/index.pl?lastnode_id=478node_id=388 though I haven't used it. As far as I know, MySQL doesn't support XML as a native datatype, and doesn't support indexing XML documents directly; you have to treat them as BLOBs or TEXT fields, and extract and index the various fields yourself. Probably what you want to do is implement this at the application layer: convert an XML document into one or more SQL queries, and convert SQL rows into XML documents. The other option is using a database other than MySQL which has native XML support. ScottG. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: unix timestamp
Sebastian [EMAIL PROTECTED] writes: i have this query: SELECT COUNT(*) AS score FROM downloads WHERE date_add(dateline, interval 1 hour) = now() GROUP BY filename ORDER BY score DESC unfortunately for other reasons i had to change `dateline` to unix timestamp so this query is no longer able to run as intended. can anyone help with a work around? Sure, 1 hour is just 3600 seconds, which is how Unix timestamps are measured: SELECT COUNT(*) AS score FROM downloads WHERE dateline + 3600 = UNIX_TIMESTAMP() GROUP BY filename ORDER BY score DESC ScottG. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: unix timestamp
Scott Gifford wrote: SELECT COUNT(*) AS score FROM downloads WHERE dateline + 3600 = UNIX_TIMESTAMP() GROUP BY filename ORDER BY score DESC It would be better with WHERE dateline = UNIX_TIMESTAMP() - 3600 so that it can use an index on dateline. -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: unix timestamp
Keith Ivey [EMAIL PROTECTED] writes: Scott Gifford wrote: SELECT COUNT(*) AS score FROM downloads WHERE dateline + 3600 = UNIX_TIMESTAMP() GROUP BY filename ORDER BY score DESC It would be better with WHERE dateline = UNIX_TIMESTAMP() - 3600 so that it can use an index on dateline. You're right; I have too much faith in the optimizer. :-) Scott. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: unix timestamp
Sebastian wrote: i have this query: SELECT COUNT(*) AS score FROM downloads WHERE date_add(dateline, interval 1 hour) = now() GROUP BY filename ORDER BY score DESC unfortunately for other reasons i had to change `dateline` to unix timestamp so this query is no longer able to run as intended. can anyone help with a work around? btw, i am using php to run queries if that helps find a solution. If the database server and the webserver are not on the same box you probably should use the same source for the timestamps. Translated in plain english the insert query is created on the downloads server and don't use MySQL function to insert the date you should use php time() function to retrieve your data. $sql= SELECT COUNT(*) AS score FROM downloads WHERE dateline = . (time() - 3600) . GROUP BY filename ORDER BY score DESC ; Also in a ntp syncronized network a difference of one or two second is not unusual. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: character encoding
Karima Velasquez wrote: i know about the null terminating character, but i don't think this is the problem. It might not be your immediate problem, but you will run into it eventually. rigth data: 82 wrong data: rigth data: ^ wrong data: That should only happen if your column is set as a non-binary type, which is a bad idea, for the reasons I've pointed out already. Character set conversions do not affect binary columns. See: http://dev.mysql.com/doc/mysql/en/blob.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Native XML Support
Does MySQL 5 provide native XML support? ie, can I have a stored procedure return an XML string instead of a recordset? Can I pass in an XML string/doc and have the DB update relational tables based on it? native xml support, now, that's probably the funniest thing I've heard all day :-) What would that actually be? something like this I suppose http://mysql.r18.ru/~bar/myxml/XMLXpathSupportInMySQL.pdf Nice document. But, IMO, if you need to query elements in your XML, better normalize the data structures and put it in tables... That's what a database is supposed to be: tables with columns and rows. Use XML for what is was intended. With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: character encoding
thanks for answering... i already know about these datatype, actually i'm using longblob as datatype; which is why i don't really understand what's going on!!! regarding on your previous message, do you know about any sample code on using BLOB columns using c++ to create querys??? best regards, karima On 8/15/05, Warren Young [EMAIL PROTECTED] wrote: Karima Velasquez wrote: i know about the null terminating character, but i don't think this is the problem. It might not be your immediate problem, but you will run into it eventually. rigth data: 82 wrong data: rigth data: ^ wrong data: That should only happen if your column is set as a non-binary type, which is a bad idea, for the reasons I've pointed out already. Character set conversions do not affect binary columns. See: http://dev.mysql.com/doc/mysql/en/blob.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
multiple JOINs / GROUP BY (beginnrer) question
Hello. I am trying to obtain a list of products and related information from three tables using JOINs and GROUP BY. my tables look like this: products: id: int name varchar ratings: user_id int product_id int rating int wishlists: user_id int product_id int stars int And I want the result to look like this: product_id | product_name | my_rating | avg_rating | total_ratings | my_stars | avg_stars | total_stars | total_star_users Tables 'ratings' and 'wishlists' do not necessarily contain entries for each user_id; also a user can rate a product, but not have it in his wishlist and vice-versa (this is the cause of my problem). My question is if it is possible to do this in mysql by using a single query, without using temporary tables. I tried to use something like this: SELECT p.id AS product_id, p.name AS product_name, r1.rating AS my_rating, AVG(r2.rating) AS avg_rating, COUNT(DISTINCT(r2.user_id)) AS total_ratings, w1.stars AS my_stars, AVG(w2.stars) AS avg_stars, SUM(w2.stars) AS total_stars, COUNT(DISTINCT(w2.stars)) AS total_star_users FROM products p LEFT JOIN ratings r1 ON r1.product_id=p.id AND r1.user_id=current_user_id LEFT JOIN ratings r2 ON r2.product_id=p.id LEFT JOIN wishlists w1 ON w1.product_id=p.id AND w1.user_id=current_user_id LEFT JOIN wishlists w2 ON w2.product_id=p.id GROUP BY product_id ORDER BY product_name; The problem is the field 'total_stars' which shows incorrectly, as it SUMs more data than necessary in some cases. Can anyone help? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: character encoding
Karima Velasquez wrote: do you know about any sample code on using BLOB columns using c++ to create querys??? C++, eh? I happen to be the MySQL++ maintainer. Two of its example programs, cgi_image and load_file, deal with BLOBs. http://tangentsoft.net/mysql++/ Notice the automatic escaping, and the use of C++ strings, both of which render the null issue irrelevant. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: French Characters, Still no answer
Im not sure if my experience will be of any help but here it is: I used a database with utf8 as default charset (you can easily set this with mysql administrator) and the columns that I needed to store Macedonian cyrilic characters were varchar. Some letters were stored as ?. When I changed the column type to varbinary I was able to store/retreive all the letters properly. However if you change the type of the columns you cannot use the mysql adminisators backup sql queries to restore a database that contains utf8 characters (at least I failed). Using a PHP script will help. For those that use mySQL with some script language using base64 encoding/decoding funtions for non latin1 letters is the safest way (however you cannot strictly predict the lenght of the encoded string based on the lenght of the plaintext). best regards --- Ace Dimitrievski, research assistant, Faculty of Electrical Engineering Skopje --- [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: The older version is 4.xx.xx im not sure how to tell. New version is 4.1.12 Thank you, James - Original Message - From: Bruce Dembecki [EMAIL PROTECTED] To: James Sherwood [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Monday, August 15, 2005 2:50 PM Subject: Re: French Characters, Still no answer Still no answer, perhaps, but ther'es still no question. Per my earlier response... What version of MySQL is the old version you refer to, what version is the new version you refer to? With that information someone here is more likely to be able to tell you something useful... without that you're not likely to get much of a response. Best Regards, Bruce On Aug 15, 2005, at 7:59 AM, James Sherwood wrote: I am still having trouble with french characters if anyone has ANY ideas, please help. We have installed the newest version of MySql and cannot get it to play nice with French characters. Our older version worked fine. The problem may (or may not) be that when we put the dump into the new database(yes its default charset is Utf8) the default character set for the table is Utf8 but some fields are like this: 'Story' longtext character set latin1 NOT NULL We tried linking our tomcat to the old database on the other server through this tomcat and everything works fine but when we link the tomcat back to the new database, it will not play nice with french characters. (they come out as outlined squares etc) Any ideas would be greatly appreciated James -- 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] __ 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]
[Way OFF] Amazing picture of Helios Flight 522
http://www.briandunning.com/helios.shtml Sorry this is WAY OFF TOPIC, but it's a pretty darn scary picture. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
4.0 - 4.1 lose timestamp
Yes theres all this talk about timestamp returning different results in 4.1 vs. 4.0 but i took the MYI and MYD and frm files from a winnt running 4.0.14 and transferred to a 4.1.11-Debian_4-log and all the timestamp fields are NULL it seems all the other data is fine. Any ideas? i suppose i may just have to dump/load instead of copy files. --- Hunter Peress [EMAIL PROTECTED] Web Programer The Santa Fe New Mexican, Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How can I receive a a TRUE or FALSE result?
How can I receive a TRUE or FALSE result? Example: SELECT `name`, `description` FROM `table1`; I don't want to show the description cause they're big. I only want to show the name and a result of TRUE or FALSE. TRUE if description is not empty or not null, FALSE if empty or null. I'm using MySQL 4.0. Thanks. :-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can I receive a a TRUE or FALSE result?
Misa wrote: How can I receive a TRUE or FALSE result? Example: SELECT `name`, `description` FROM `table1`; I don't want to show the description cause they're big. I only want to show the name and a result of TRUE or FALSE. TRUE if description is not empty or not null, FALSE if empty or null. I'm using MySQL 4.0. Thanks. :-) select name, not(isnull(description)) from table1; This will only work if the field is actually NULL, which is different from having an empty string ( '' ) in it. If you instead have empty strings, you'd have to do something like: select name, if(length(description),1,0) from table1; Otherwise if you want the *string* TRUE or FALSE in the results, you'd replace the 1 and 0 with 'TRUE' and 'FALSE'. Or for the original example, you'd have to add an if() function around the not() function and put your TRUE and FALSE strings in there. Dan -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Multiple my.cnf files
Does anyone have any experience using multiple my.cnf files on a single box? If so, how is the my.cnf specified during startup and shutdown? I need multiple my.cnf files to test ibbackup software because it does not currently support using mysqld_multi. CL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Why can't I revoke usage from user?
Hello everyone: Why can't I revoke usage from user? mysql show grants for ''@'172.20.16.110'; +--+ | Grants for @172.20.16.110| +--+ | GRANT USAGE ON *.* TO ''@'172.20.16.110' | +--+ 1 row in set (0.00 sec) mysql revoke usage on *.* from ''@'172.20.16.110'; Query OK, 0 rows affected (0.00 sec) mysql show grants for ''@'172.20.16.110'; +--+ | Grants for @172.20.16.110| +--+ | GRANT USAGE ON *.* TO ''@'172.20.16.110' | +--+ 1 row in set (0.00 sec) Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to arrange my table in another direction
My table is: type price car1000 bike 100 I want the result: car bike 1000100 I don't know how to get that result. - DO YOU YAHOO!? 雅虎邮箱超强增值服务－2G超大空间、pop3收信、无限量邮件提醒
