Prepared grant statement?
Hi list: I don't know if this the right forum to ask the following questions: Will MySQL production version 5.0 support grant in prepared statements? The yet part is encouraging in ERROR 1295 (HY000) at line 17: This command is not supported in the prepared statement protocol yet Will prepared statements in stored procedures be supported? (I read that it is disabled right now) Regards, Adolfo __ Renovamos el Correo Yahoo! Nuevos servicios, más seguridad http://correo.yahoo.es -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Weirdness (or bug) with DROP TABLE
I am using Mandrake 10, MySQL 4.1.5 from RPM downloaded from dev.mysql.com. Look at this session: $ mysql -u root -p permarn Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 to server version: 4.1.5-gamma-standard Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql select * from tblinstructivos; Empty set (0.00 sec) mysql drop table tblinstructivos; ERROR 1051 (42S02): Unknown table 'tblinstructivos' mysql select * from `tblinstructivos`; Empty set (0.00 sec) mysql drop table `tblinstructivos`; ERROR 1051 (42S02): Unknown table 'tblinstructivos' mysql = Why I can not drop this particular table? No problem with any other table. Every table in this database is InnoDB. I started mysql with the --user=root flag. Adolfo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Autoincrement : how does it work / how to reset it
On Fri, 2003-01-31 at 10:48, Robert Mena wrote: Hi, I have been using autoincrement fields for some time but was wondering how does it work in some special situations. Ex. suppose I have an autoincrement field called num and the last one has value of 10. I delete the last on and insert a new one. Which value will it have ? 10 or 11 11 I read that if I issue a query delete * from table and delete from table I have diferent results (in regards to the auto increment field). Is this correct ? Try both. I don't know what you mean. Sorry. Thanks. PS. plese CC me directly since for some reason the messages from the list are taking a while to get in my mailbox. Done! -- __ / \\ @ __ __@ Adolfo Bello [EMAIL PROTECTED] / // // /\ / \\ // \ // Bello Ingenieria S.A, ICQ: 65910258 / \\ // / \\ / // // / //cel: +58 416 609-6213 /___// // / _/ \__\\ //__/ // fax: +58 212 952-6797 www.bisapi.com //pager: www.tun-tun.com (# 609-6213) - 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: sql query using select and row functions
On Tue, 2003-01-28 at 12:26, Christopher Lyon wrote: I am trying to do an sql query and am trying to select the last x rows from the database. I see the limit function but that seems like that is from the first row down. I want to start from the last row to the first row. So, selecting the last 5 rows for instance? Can this be done? SELECT * FROM blah-blah ORDER BY whatever DESC LIMIT 0,5 -- __ / \\ @ __ __@ Adolfo Bello [EMAIL PROTECTED] / // // /\ / \\ // \ // Bello Ingenieria S.A, ICQ: 65910258 / \\ // / \\ / // // / //cel: +58 416 609-6213 /___// // / _/ \__\\ //__/ // fax: +58 212 952-6797 www.bisapi.com //pager: www.tun-tun.com (# 609-6213) - 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: mysql.sock is missing
On Mon, 2003-01-27 at 11:23, Pedro Leite wrote: Hi, I'm getting the same error message. mysql here is in /etc/rc.d/init.d, don't ask me why, I'm new to this things. Any more help would be much appreciated. TIA That's the directory where it should be. You can also start with service mysql start Adolfo sql, query - 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: problem
On Sun, 2003-01-26 at 15:58, Beogradjanin wrote: Zdravo mysql, I tried to install MySQL mysql-3.23.55-win on WinXp Platform. I did installed it but I can't start MySQL...??? Can You Help me??? What happens when you try Startup-AdminTools-Services-mysql-start? -- __ / \\ @ __ __@ Adolfo Bello [EMAIL PROTECTED] / // // /\ / \\ // \ // Bello Ingenieria S.A, ICQ: 65910258 / \\ // / \\ / // // / //cel: +58 416 609-6213 /___// // / _/ \__\\ //__/ // fax: +58 212 952-6797 www.bisapi.com //pager: www.tun-tun.com (# 609-6213) - 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: Need nulls in my join
On Fri, 2003-01-24 at 20:27, Hoffman, Geoffrey wrote: I have an SQL query that LEFT JOINs four different tables to a main table, based on several foreign keys. It's returning only stories in a section that have photos, but I need it to return all the stories in a section whether it has a photo or not. I think the problem is in the WHERE evtphoto.phtusage = 1 -- if there's no photo, then there's no phtusage. Should I split this up into two queries, to get all the story IDs for a section/day and then get the photos for the stories in a 2nd query? Or do I need a different join type? Or do I put the WHERE clauses in a different order? Here's the query: SELECT evtstorysection.secid, evtstory.styid, evtstory.styheadline, evtstory.stysummary, evtstory.styintro, evtstory.stypubdate, evtphoto.phturl, evtphoto.phtcaption, evtstoryorder.stoorder, evtlayoutsum.lytstring FROM evtstory LEFT JOIN evtlayoutsumON evtstory.slytid = evtlayoutsum.lytid LEFT JOIN evtstorysection ON evtstory.styid = evtstorysection.styid LEFT JOIN evtstoryorder ON evtstory.styid = evtstoryorder.styid LEFT JOIN evtphotoON evtstory.styid = evtphoto.styid LEFT JOIN evtphoto nophoto ON evtstory.styid = nophoto.styid WHERE evtphoto.phtusage = 1 AND Replace above line with: (evtphoto.phtusage = 1 OR nophoto.phtusage IS NULL) AND evtstorysection.secid = 3 AND evtstoryorder.secid = 3 AND evtstoryorder.stodate = '2003-01-24' AND evtstory.stypubdate = '2003-01-24' AND evtstory.styexpdate '2003-01-24' ORDER BY evtstoryorder.stoorder __ / \\ @ __ __@ Adolfo Bello [EMAIL PROTECTED] / // // /\ / \\ // \ // Bello Ingenieria S.A, ICQ: 65910258 / \\ // / \\ / // // / //cel: +58 416 609-6213 /___// // / _/ \__\\ //__/ // fax: +58 212 952-6797 www.bisapi.com //pager: www.tun-tun.com (# 609-6213) - 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: join from this subselect
On Tue, 2003-01-21 at 15:51, Josh L Bernardini wrote: I can't come up with the join syntax to mimic this subselect query to list people not attending an event (*epeople.eid == event.id): select people.id as pid, concat(lastname, , , firstname) as name from people where people.id not in (select epeople.pid from epeople left join people on epeople.pid=people.id where epeople.eid=2); Thought it would be: select epeople.pid, concat(lastname, , , firstname) as name from epeople left join people on epeople.pid=people.id where epeople.eid=2 and people.id is null; but I get an empty set. In this query you're querying events with no attendants (people.id IS NULL) and with event Id 2. Did event 2 have at least an attendant? If so, then the result must be empty. I guess you will need a TEMP table. HTH -- __ / \\ @ __ __@ Adolfo Bello [EMAIL PROTECTED] / // // /\ / \\ // \ // Bello Ingenieria S.A, ICQ: 65910258 / \\ // / \\ / // // / //cel: +58 416 609-6213 /___// // / _/ \__\\ //__/ // fax: +58 212 952-6797 www.bisapi.com //pager: www.tun-tun.com (609-6213) - 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: Re:SQL Count Query
On Sun, 2003-01-19 at 10:44, Paul DuBois wrote: At 7:18 + 1/19/03, Nasser Ossareh wrote: a row gets inserted into this table whenever a visitor uses a certain tool on the site based on this approach your table does not have any entries for the days that no one uses the certain tool on the site. As a result your query can not pull out records which don't exist. You could catter for this programmatically by a perl script. The script could do this: That would work, but it's not necessary. He should use a LEFT JOIN, which will produce a row in the output for the left table, whether or not any right table rows match it. The syntax is in the MySQL manual. Well, the date is inserted into the master table only after a hit to the tool. Then, if in a date the tool is not called/used, then that date will not be in the master table. In this case a LEFT JOIN won't help. __ / \\ @ ____@ Adolfo Bello [EMAIL PROTECTED] / // // /\ / \\ // \ // Bello Ingenieria S.A. / ICQ: 65910258 / \\ // / \\/ // // / //cel: +58 416 609-6213 /___ / _/\__\\//__/ // fax: +58 212 952-6797 www.bisapi.com //pager: www.tun-tun.com (# 609-6213) - 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: mysql
On Sun, 2003-01-19 at 14:48, M A wrote: HI i am new to mysql.. i have just installed the source code under /user/local/mysql but the commnads don't work unless i have ./ infront of the command for example: mysqladmin won't work unless its written ./mysqladmin. please let me know if i am doing something wrong No. This is the standard Linux/Unix way of invoking a command from in the working directory. Adolfo - 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: Re:Re: Substraction
On Sun, 2003-01-19 at 19:09, Nasser Ossareh wrote: Isn't there a typo somewhere... Shouldn't you expect 5 instead of -5 (15 - id where id = 10)?? Nevermind, let's assume that there is a typo somewhere and the correct expected value is -5... however, in the domain of unsigned arithmetics -5 is precisely 18446744073709551611... don't you believe me: look at this: 18446744073709551611 + 5 = 18446744073709551616 which is 2 to power 64 (2^64). Clearly in the architecture that you are using an unsigned int is a 64 bits integer... if you set all the bits to 1 you will get the largest unsigned int in your system and that is 18446744073709551615 which is (2^64)-1. if you increment it by one... all the bits switch to 0. Hence within your architecture 2^64 = 0 and -5 is represented correctly by 18446744073709551611 (which is 2^64 - 5). Nasser sql, smallint, ... Beautifully explained. Adolfo - 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: Host not allowed to connect to server - Help!
Sorry for what is most likely a starter question! I am a newbie and need some help. I have a mysql DB on server rhserver.mydomain.com running RH7.2 I installed mysqlcc on my Win2K PC to access that DB. No matter how I try to access the DB from within mysqlcc, I get, Host win2kpc.mydomain.com not allowed to connect to server. I am able to connect to the DB as root and a regular user using passwords while logged into the RH server. How do I tell the mysql DB to allow access from win2kpc.mydomain.com? Thanks. Mark Have you granted permissions to [EMAIL PROTECTED]? sql, query __ / \\ @ ____@ Adolfo Bello [EMAIL PROTECTED] / // // /\ / \\ // \ // Bello Ingenieria S.A, Presidente / \\ // / \\/ // // / //cel: +58 416 609-6213 /___ / _/\__\\//__/ // fax: +58 212 952-6797 www.bisapi.com //pager: www.tun-tun.com (# 609-6213) - 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: Will PhpMyAdmin run on Linux-Apache-MySQL box ?
Will PhpMyAdmin run on a Linux/Apache/MySQL server? Sure, without any problem. If so which version should I download? (.php files) - Download phpMyAdmin-2.3.3pl1-php.tar.bz2 - Download phpMyAdmin-2.3.3pl1-php.tar.gz I downloaded this one ^^^ - Download phpMyAdmin-2.3.3pl1-php.zip (.php3 files) - Download phpMyAdmin-2.3.3pl1-php3.tar.bz2 - Download phpMyAdmin-2.3.3pl1-php3.tar.gz - Download phpMyAdmin-2.3.3pl1-php3.zip (Yes, I'm a Linux newbie.) Finally, is it a fairly easy install for a Linux newbie? I use with Apache and it is faily easy. Just remember the virtual host stuff and modify you /etc/hosts if you are going to use it locally. thanks for any help. Will HTH sql, query __ / \\ @ ____@ Adolfo Bello [EMAIL PROTECTED] / // // /\ / \\ // \ // Bello Ingenieria S.A, Presidente / \\ // / \\/ // // / //cel: +58 416 609-6213 /___ / _/\__\\//__/ // fax: +58 212 952-6797 www.bisapi.com //pager: www.tun-tun.com (# 609-6213) - 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: A little problem with SELECT
SELECT a.* FROM company a, employee b WHERE a.id=b.cid AND b.name='joe' OR b.name='bill'; however, this would return any companies that has ONLY one Bill or one Joe .. I only want companies that have BOTH. It also returns one row with the company per name it found, so you can imagine I got confused when it found 2000 rows when my list of companies is 50 rows =) Basically, SELECT DISTINCT . will give you just one row per company found. what I'd like to do is find the company who has both bill and joe I tried: SELECT a.* FROM company a, employee b WHERE a.id=b.cid AND b.name='joe' AND b.name='bill'; Try: SELECT DISTINCT a.* FROM company a, employee b, employee c WHERE a.id=b.cid AND a.id=c.cid WHERE b.name='joe' AND c.name='bill' But I realized this is all wrong cause the same cell cannot be both Joe and Bill =) mysql query smallint HTH __ / \\ @ ____@ Adolfo Bello [EMAIL PROTECTED] / // // /\ / \\ // \ // Bello Ingenieria S.A, Presidente / \\ // / \\/ // // / //cel: +58 416 609-6213 /___ / _/\__\\//__/ // fax: +58 212 952-6797 www.bisapi.com //pager: www.tun-tun.com (# 609-6213) - 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: Re:Re: Password problem
you can do this: mysql set password for newsletter@localhost=PASSWORD(testPass); or mysql update user Set Password=Password(testPass) - where user=newsletter; Don't forget the flush privileges thing after the update command. __ / \\ @ ____@ Adolfo Bello [EMAIL PROTECTED] / // // /\ / \\ // \ // Bello Ingenieria S.A, Presidente / \\ // / \\/ // // / //cel: +58 416 609-6213 /___ / _/\__\\//__/ // fax: +58 212 952-6797 www.bisapi.com //pager: www.tun-tun.com (# 609-6213) - 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: Foreign keys
Does MySQL allow using foreign keys or not? It does. Try it by yourself with version 4.0.8 +. Adolfo - 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: InnoDB table, NOT NULL question
I have an InnoDB table that looks like the following.
CREATE TABLE `stuff` (
`stuff_id` int(11) NOT NULL auto_increment,
`somevalue1` varchar(35) NOT NULL,
`somevalue2` varchar(35) NOT NULL,
PRIMARY KEY (`stuff_id`)
) TYPE=InnoDB COMMENT='stuff table';
I run the following insert statement.
INSERT INTO stuff ( somevalue2 ) VALUES ('blah')
Now the field somevalue1 is empty. Doesn't this violate the NOT NULL
constraint on the field?
Well, by empty you probably mean , which is not null, is it?
This is wierd. If you define a field as not null is because you want the
user to enter some data, for example, First Name and Last Name. Yes, I
know one should check it at the application level but, isn't MySQL being
a little too flexible with this?
__
/ \\ @ ____@ Adolfo Bello [EMAIL PROTECTED]
/ // // /\ / \\ // \ // Bello Ingenieria S.A, Presidente
/ \\ // / \\/ // // / //cel: +58 416 609-6213
/___ / _/\__\\//__/ // fax: +58 212 952-6797
www.bisapi.com //pager: www.tun-tun.com (# 609-6213)
-
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: innofb foreign keys problem
then i ask me: why mysql needs explicit creation instead of create itself what it needs? manually creating the index seems to be a big complication (for big databases) and a waste of time. don't you think so? it's a bug or a wanted feature? why? I agree with you 100%. A foreign key,as its name implies, should create automatically a key. I would it is a wanted feature. __ / \\ @ ____@ Adolfo Bello [EMAIL PROTECTED] / // // /\ / \\ // \ // Bello Ingenieria S.A, Presidente / \\ // / \\/ // // / //cel: +58 416 609-6213 /___ / _/\__\\//__/ // fax: +58 212 952-6797 www.bisapi.com //pager: www.tun-tun.com (# 609-6213) - 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: relations between tables
As far as I know and how I understand it, the relationships are basically all in your head... You just have to coordinate it in your queries and other functions This is not true. Fot type innodb tables, you can define REFERENCES using ON DELETE (CASCADE|RESTRICT) and/or ON UPDATE CASCADE Adolfo - 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: sql delete question
I have two tables whose structures are below. - Table1 - table1id int not null auto_increment data varchar(30) - Table2 - table2id int not null auto_increment table1id int not null data varchar(30) These two tables are connected to each other with the table1id column. I need to delete rows in table1 which have no corresponding table1id values in table2 and also i need to delete rows in table2 which have no corresponding table1id values in table1. Any comments? Can you try a multi table DELETE? I haven't done it but I would be a shot at something like (adapted from MySQL Manual) DELETE t1 FROM t1,t2 WHERE t1.id=t2.id AND t2.id IS NULL Adolfo - 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: WHERE clouse
On Wed, 2003-01-08 at 15:30, Dejan Milenkovic wrote: Hi, Is there a difference in speed between these two querys: SELECT whatever, somethin_else FROM table1 WHERE 1 AND ( table1.whatever='value' OR ) SELECT whatever, somethin_else FROM table1 WHERE table1.whatever='value' OR ... Dare to say NO :-) Adolfo - 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: Multiple SQL files
I have multiple SQL files that create different tables. Is there a way for me to create a single SQL file that will call these other files? Since I am using phpMyAdmin, I am assuming that call this file from phpMyAdmin?! Since phpMyAdmin is a HTTP interfase, I don't think it is possible to do this. The server (Apache, IIS, etc) would have to know the location of the files in the client machine, which is not possible in a stateless connection. What I do is to have a script (bat for DOS or bash for Linux) making the calls to the sql scripts. Something like: mysql -h yourhost -u root -pqwerty script1.sql ... mysql -h yourhost -u root -pqwerty scriptN.sql HTH Adolfo - 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: auto increment question
It sounds to me that a trigger would help, but mysql doesn't has them yet. Adolfo -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED]] Sent: Friday, January 03, 2003 8:04 PM To: Kevin; [EMAIL PROTECTED] Subject: Re: auto increment question At 15:28 -0800 1/3/03, Kevin wrote: Hello, I have a table with an ID column and a viewed column. I would like the viewed column to increment by one each time the row is updated. Stats === ID views is this possible? Sure. Since you're updating the row anyway, set the column value to one more than its current value. :-) No, it won't happen automatically, which I would guess is what you're really asking. -k sql, query - 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: auto increment question
It sounds to me that a trigger would help, but mysql doesn't has them yet. Adolfo -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED]] Sent: Friday, January 03, 2003 8:04 PM To: Kevin; [EMAIL PROTECTED] Subject: Re: auto increment question At 15:28 -0800 1/3/03, Kevin wrote: Hello, I have a table with an ID column and a viewed column. I would like the viewed column to increment by one each time the row is updated. Stats === ID views is this possible? Sure. Since you're updating the row anyway, set the column value to one more than its current value. :-) No, it won't happen automatically, which I would guess is what you're really asking. -k sql, query - 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: a problem with sum()
By looking at your query and table description, I guess you can try something like: SELECT cus.`Code` AS `Customer Code`, cus.`Name` AS `Customer`,SUM((1-bil.direction)*bil.Total) AS Loan, SUM(bil.direction*bil.Total) AS Credit FROM customer AS cus, bill AS bil WHERE bil.`Customer` = cus.`ID` GROUP BY bil.`Customer` ORDER BY cus.`Name` Adolfo On Wed, 2003-01-01 at 14:41, Aziz Durmaz wrote: Hi, I have got 2 tables one of the is customers the other is bill. In the customers table i store my customers informations. In the bill table i store my billings. if i take a bill bill.direction=0, if i give a bill bill.direction=1. And now i must take a report Like This.. Customer Code Customerloan credit total - 10021 Bob 2515$ 500$2015$ 10022 Mark530$ 600$-70$ 10023 Fred7500$ 0 7500$ .. and its goes on... Bill Table --- CREATE TABLE `bill` ( `ID` int(11) NOT NULL auto_increment, `direction` tinyint(4) NOT NULL default '0', `BillNo` varchar(16) NOT NULL default '', `BillDate` date default '-00-00', `Customer` int(11) default '0', `Total` float(10,2) default '0.00' PRIMARY KEY (`ID`), UNIQUE KEY `ID` (`ID`), UNIQUE KEY `BillNo` (`BillNo`), KEY `BillDate` (`BillDate`), KEY `Customer` (`Customer`) ) TYPE=MyISAM; Customer Table --- CREATE TABLE `customer` ( `ID` int(10) unsigned NOT NULL auto_increment, `Code` varchar(32) NOT NULL default '0', `Name` varchar(80) default NULL, PRIMARY KEY (`ID`), UNIQUE KEY `Code` (`Code`), KEY `Name` (`Name`) ) TYPE=MyISAM; use a query like -- SELECT cus.`Code` AS `Customer Code`, cus.`Name` AS `Customer`, IF (bil.`direction`=0,SUM(bil.`Total`), NULL ) AS Loan, IF (bil.`direction`=1,SUM(bil.`Total`), NULL ) AS Credit FROM customer AS cus, bill AS bil WHERE bil.`Customer` = cus.`ID` GROUP BY bil.`Customer` ORDER BY cus.`Name` But it gives wrong results. My query is to add all Totals, its doesnt look if direction is 1 or 0. The out put like this Customer Code Customerloan credit 10021 Bob 3015$ 0 10022 Mark0 1030$ 10023 Fred7500$ 0 .. I must send only one query ! Is there any body can help me??? Aziz Durmaz - 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: Storing a SHA1 checksum
I would try storing the SHA1() result as a hexadecimal string. Adolfo -Original Message- From: Benjamin Pflugmann [mailto:[EMAIL PROTECTED]] Sent: Sunday, December 29, 2002 7:35 AM To: Philip Mak Cc: [EMAIL PROTECTED] Subject: Re: Storing a SHA1 checksum On Sun 2002-12-29 at 05:28:57 -0500, [EMAIL PROTECTED] wrote: sql, table I'm storing a SHA1 checksum as varchar(20) binary in my application. After running a test, it seems MySQL will strip trailing spaces from a varchar column, even if it is binary! Yes, the BINARY keyword only influences how comparisons are done (mainly case-sensivity, but also umlauts, etc...). Stripping space from VARCHAR is a known deficiency: http://www.mysql.com/doc/en/Bugs.html It also mentions, that the TEXT/BLOB types are save from it. That means if the last character of my SHA1 checksum happens to be a space, MySQL will corrupt it. What should I do? It seems I can: 1. Use blob instead of varchar. Problem: blob type is slower. Is that really a problem? Did you measure it? If so, I would be intersted in the results. Advantage: Other application programmers do not need to be aware of the hack. After MySQL is fixed, the source doesn't contain redundant code. 2. Make my application pad the checksum out to 20 spaces. Problem: Increases my code complexity a bit. Advantage: Doesn't affect performance (noticeably). The DBA doesn't need to be aware of the hack. 3. Wait for MySQL to fix the strip trailing spaces bug. Problem: That doesn't provide an immediate solution. 4. Append a non-space at the end, and ignore it on retrieval Problem: Same as 2. Although 2. looks like the prettier solution, 4. makes easier to spot the problem, if the additional handling is forgotten in new code. Well, what you should do? It depends on what you need. It's a trade-off and no one except you can answer what your priorities are. If, for example, you have many applications / programmers who access this stuff, 1. is least intrusive. OTOH, if it is used only in one place, perhaps in a well-encapsulated object, 2. is the least intrusive change. And someone (that includes yourself in 1 year) looking at your SQL dump wouldn't know why you have chosen a BLOB, while you can have a neat comment in the source about it. Since any of the solutions involves only minor changes, I would not bother to waste time on the decision. Simply go with one and rewrite if it really turns out to become a problem later (which I don't believe). HTH, Benjamin. -- [EMAIL PROTECTED] - 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: SELECT query
-Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED]] Sent: Saturday, December 28, 2002 6:44 PM To: Gloria L. McMillan; [EMAIL PROTECTED] Subject: Re: SELECT query At 14:38 -0700 12/28/02, Gloria L. McMillan wrote: RE: MySQL SELECT and COUNT or SUM Hi, all! I think this SELECT command does almost what I need. SELECT CW03survey.Q6, CW03survey.Q7, CW03survey.Q8 FROM CW03survey WHERE CW03survey.Q3 = '1' = But how can I get it to also run a count of the total of each column? Q6, Q7...? Try: SELECT CW03survey.Q6, CW03survey.Q7, CW03survey.Q8 FROM CW03survey WHERE CW03survey.Q3 = '1' UNION SELECT SUM(CW03survey.Q6), SUM(CW03survey.Q7), SUM(CW03survey.Q8) FROM CW03survey WHERE CW03survey.Q3 = '1' - 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: Starting of MySQL with redhat 8
service mysql start Search the list for more information Adolfo On Tue, 2002-12-24 at 09:07, Amitié Plus wrote: Hello, I'm a beginner with mysql and i installed it with linux. I'm getting this message when i tried to be connected : Error 2002 : can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111). Can you help me by finding how to be connected to the database ? - 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: Counting results using 2 tables.
On Tue, 2002-12-24 at 12:11, [EMAIL PROTECTED] wrote: I wish to get a count of records contained in 1 table as compared to the contents of a different table. What I have is a list of realtors in 1 table (agents.name) The listings are contained in a different table (listings) and each can be identified by their agent by listings.agent. Here's the query I've tried using alias and COUNT but it doesn't seem to work. In fact it's telling me that the table (listings) doesn't even exist when I know it does. SELECT name, COUNT(*) as cnt from agents where agents.name = listings.agent; SELECT name,COUNT(listings.*) as cnt from agents LEFT OUTER JOIN listings ON agents.name=listings.agent GROUP BY name; IMO, joining by name is a real bad idea. What I need to accomplish is to generate a report where it will list out each agent in agents followed by the number of listings posted by them in listings even if it is zero. TIA, Ed - 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: Help with database Design
-Original Message- From: Ricardo Fitzgerald [mailto:[EMAIL PROTECTED]] Sent: Sunday, December 22, 2002 11:22 AM To: [EMAIL PROTECTED] Subject: Help with database Design create table recipes( rid int not null primary key, rname ) ; create table ingredient( iid int not null, rid int not null, # references recipe.rid ridorder int not null, # order of this ingredient in recipe PRIMARY KEY(iid,rid,ridorder), iname ...) ; HTH, Adolfo Hi, I'm having problems trying to figure which is the best way to solve a design : I'm designing a delivery program with php and mysql, and I'm having troubles with some tables, my problem is with the recipes, the recipes are divided into two tables one with a recipe id and description the other detailing the ingredients, of each particular recipe, that's where my problem starts because each ingredient is not unique and even it appears in many recipes, and another thing is every time and item is ordered their correspondent recipe ingredients are added , ie. pizza has a code 11, recipe has code 01 then all the ingredients in the recipe table have different ingredient code each, and are related by the recipe code 01, then each separate item must be counted, and added to the ingredients general daily totals, I mean pizza uses 2 tomatoes then the general total will show two tomatoes and each other ingredient. There are also some combined recipes : Pizza with a special sauce, then this item has to query two parts of the same table, say code 12 is Pizza with a combined sauce of mushrooms and milk, and pineapples, so each ingredient must be added to the general total. I need help with the design because I don't want to have database anomalies. Thank you all, Merry Xmas Rick __ Omni ICQ#: 37031810 Current ICQ status: + More ways to contact me __ - 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: How long is my piece of string?
Create 2 tables with identical columns definition, one with index and the other without. Start adding records to the two tables and querying them. This way you'll find out the number of recors for which the performance is about the same. After that, you can decide wich way to go. Adolfo -Original Message- From: Mark Matthews [mailto:[EMAIL PROTECTED]] Sent: Sunday, December 22, 2002 9:44 AM To: Iain Lang Cc: Alan McDonald; [EMAIL PROTECTED] Subject: Re: How long is my piece of string? -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Iain Lang wrote: . But that is my question! *When* do they provide a performance difference? Obviously my (fairly simple) question (...and beyond what number of records might indices provide faster extraction/presentation?... ) has not been understood. Can anyone else help, please? At 11:22 22/12/02 +1100, Alan McDonald wrote: You need indexes as soon as (or rather just before) they provide a performance difference. From http://www.mysql.com/doc/en/Where_optimisations.html : Each table index is queried, and the best index that spans fewer than 30% of the rows is used. If no such index can be found, a quick table scan is used.. So if you can't create an index that will have values that will cause MySQL to look at less that 30% of the rows, it will not be used. For example if you have a column that contains the value 'foo' 90% of the time, queries looking for 'foo' in that column will not use an index. Indexes almost _always_ slow down data _modification_. You can tell when/if they speed-up data retrieval by using the 'EXPLAIN' command in MySQL, see: http://www.mysql.com/doc/en/EXPLAIN.html -Mark -BEGIN PGP SIGNATURE- Version: GnuPG v1.1.90 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE+BcGPtvXNTca6JD8RAjAmAKC/OlCFFx7iLZN57AMIRO5cV4wfyACgrLjU OHViqL0GR0Hf4lLoS50uTXU= =veUT -END PGP SIGNATURE- - 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: mysql.sock
service mysql start -Original Message- From: bruno peracchio [mailto:[EMAIL PROTECTED]] Sent: Sunday, December 22, 2002 12:41 PM To: mysql-list Subject: mysql.sock Hi how can I do to restore mysql.sock? Thank in advance - 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: Help with database Design
You're right. I have already posted a 3 tables solution: recipes, ingredients and recipeingredients. Adolfo On Sun, 2002-12-22 at 14:10, Jeff Snoxell wrote: I'm designing a delivery program with php and mysql, and I'm having troubles with some tables, my problem is with the recipes, the recipes are divided into two tables one with a recipe id and description the other detailing the ingredients, of each particular recipe, that's where my problem starts because each ingredient is not unique and even it appears in many recipes, and another thing is every time and item is ordered their correspondent recipe ingredients are added , ie. pizza has a code 11, recipe has code 01 then all the ingredients in the recipe table have different ingredient code each, and are related by the recipe code 01, then each separate item must be counted, and added to the ingredients general daily totals, I mean pizza uses 2 tomatoes then the general total will show two tomatoes and each other ingredient. There are also some combined recipes : Pizza with a special sauce, then this item has to query two parts of the same table, say code 12 is Pizza with a combined sauce of mushrooms and milk, and pineapples, so each ingredient must be added to the general total. I need help with the design because I don't want to have database anomalies. -Original Message- From: Ricardo Fitzgerald [mailto:[EMAIL PROTECTED]] Sent: Sunday, December 22, 2002 11:22 AM To: [EMAIL PROTECTED] Subject: Help with database Design create table recipes( rid int not null primary key, rname ) ; create table ingredient( iid int not null, rid int not null, # references recipe.rid ridorder int not null, # order of this ingredient in recipe PRIMARY KEY(iid,rid,ridorder), iname ...) ; Your solution means a repetition of ingredients in the ingredients table. Then, if the unit cost of an ingredient changed you'd have to change every occurance of the ingredient... and where would you store info like the source manufacturer, tel and address etc etc. How about: table meals( m_id primary, m_name, etc etc ) table ingredients( i_id primary, i_name, i_price, etc etc ) table recipes( r_id primary, m_id, #(the meal ident) referencess meals, there will be multiples of these ) OR a really nasty way to do it with 2 tables.. table meals( m_id primary, m_name, m_ingredients, # a comma separated list of ingredient ID's in a text field ) table ingredients( as above ) so then you can read in a list of ingredient id's for each recipe, then 'look up' the ingredients. Jeff Snoxell Aetherweb Ltd - 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 -- Adolfo Bello [EMAIL PROTECTED] - 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: i need help
Is mysql service started? Check with: ps -ax mysql | grep -i mysql If it does't appear on tje list, start the service with: service mysql start Adolfo P.S. I have mysql.sock in /var/lib/mysql which is the default. Check also basedir in your /etc/my.cnf. -Original Message- From: Rich Dodge [mailto:[EMAIL PROTECTED]] Sent: Saturday, December 21, 2002 11:10 AM To: [EMAIL PROTECTED] Subject: i need help I keep getting this message what can i do to correct this problem ? Warning: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) in /web/repository/sites/www/inc/generate-page.php on line 4 - 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: Help with Joins
SEELECT t1.* FROM ticket t1,customer t2 WHERE t1.customerid=t2.customer.id AND t2.email='[EMAIL PROTECTED]' On Sat, 2002-12-21 at 16:52, Jim Hankins wrote: Greetings, I'm very new to Mysql and am trying to work with my first multi-table database project. Here are my two table descriptions. I'm trying to get the correct syntax for a sql query to do the following. Display Ticket.Ticketnum,Ticket.Priority,Ticket.Title,Ticket.Status. I assume the customer does not know the CustomerID but only their email address (actually I'm parsing this with an email processor and passing the senderemail to it. Anyway, I know what Customer.Email is. The common field is CustomerID between the two tables. Can someone help me out? +---+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-++ | CustomerID| int(5) | | PRI | NULL| auto_increment | | Email | varchar(40) | | | || | FirstName | varchar(40) | | | || | LastName | varchar(40) | | | || | Title | varchar(40) | | | || | Company | varchar(40) | | | || | Address1 | varchar(40) | | | || | Address2 | varchar(40) | | | || | City | varchar(40) | | | || | State | varchar(40) | | | || | Zip | int(5) | | | 0 || | phonedaytime | varchar(10) | YES | | NULL|| | phoneevening | varchar(10) | YES | | NULL|| | phonecellular | varchar(10) | YES | | NULL|| | phonepager| varchar(40) | YES | | NULL|| | domain| varchar(40) | | | || | username | varchar(20) | | | || | passwd| varchar(15) | | | || | ContractNum | int(10) | | | 0 || mysql Describe Ticket; ++-+--+-+-++ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-++ | TicketNum | int(10) | | PRI | NULL| auto_increment | | CustomerID | int(5) | | | 0 || | Priority | int(1) | | | 0 || | Status | varchar(30) | | | || | Title | varchar(60) | | | || | ProblemSum | longtext| YES | | NULL|| | SupportID | int(5) | YES | | 0 || - 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 -- Adolfo Bello [EMAIL PROTECTED] - 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: MySQL Command to edit a single record
The following query adds NAME and ADDRESS (where the last name is wrongly placed) and set ADDRESS to an empty string. UPDATE tablename SET FullName=CONCAT(FullName,Address),Address='' WHERE ID=the register id Adolfo On Sat, 2002-12-21 at 17:07, Jim Hankins wrote: Jeff, Are you looking for an update table syntax? Example Update tablename Set FullName=Their Full Name where someotherfield=someother value; Careful with this one. As if you omit the where clause it will update every record! -Original Message- From: Jeffrey Ellis [mailto:[EMAIL PROTECTED]] Sent: Saturday, December 21, 2002 3:52 PM To: [EMAIL PROTECTED] Subject: MySQL Command to edit a single record Hi-- I have just begun using MySQL...I am using a program called MySQL to manage an online database. I can see the entire table I need and all the records in that table for the db I¹m working with. I have a field called fullname which is supposed to receive the full name of the person entering the information. But sometimes, folks are entering their first name only in this field and then entering their last name in the address field by mistake. I would like to be able to send a command to the database to be able manually edit those records to add the last name back into the fullname field. Is there a way to do this? All My Best, Jeffrey Ellis - 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 -- Adolfo Bello [EMAIL PROTECTED] - 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: MySQL Command to edit a single record
CONCAT(FullName,' ',Address) regarding the lack of an ID field, create a new table with all the fields from the original table plus an ID field, auto_numeric, and insert the registers from the old table to the new one. Then, use the UPDATE on the new table. Adolfo On Sat, 2002-12-21 at 17:35, Jeffrey Ellis wrote: Hi, Adolfo-- Thank you! But I have two problems here...First, I (whoops!) don't have an id field. In the program I use, you can only set a key field when you first create the database. I would love to have an id field, but don't know how to create one now. :( The second is that the command will -- I think -- place the last name right up against the first name with no space before. Is there a way to do that so it looks like Foo Bar? All My Best, Jeffrey on 12/21/02 1:23 PM, Adolfo Bello at [EMAIL PROTECTED] wrote: The following query adds NAME and ADDRESS (where the last name is wrongly placed) and set ADDRESS to an empty string. UPDATE tablename SET FullName=CONCAT(FullName,Address),Address='' WHERE ID=the register id Adolfo On Sat, 2002-12-21 at 17:07, Jim Hankins wrote: Jeff, Are you looking for an update table syntax? Example Update tablename Set FullName=Their Full Name where someotherfield=someother value; Careful with this one. As if you omit the where clause it will update every record! -Original Message- From: Jeffrey Ellis [mailto:[EMAIL PROTECTED]] Sent: Saturday, December 21, 2002 3:52 PM To: [EMAIL PROTECTED] Subject: MySQL Command to edit a single record Hi-- I have just begun using MySQL...I am using a program called MySQL to manage an online database. I can see the entire table I need and all the records in that table for the db I¹m working with. I have a field called fullname which is supposed to receive the full name of the person entering the information. But sometimes, folks are entering their first name only in this field and then entering their last name in the address field by mistake. I would like to be able to send a command to the database to be able manually edit those records to add the last name back into the fullname field. Is there a way to do this? All My Best, Jeffrey Ellis - 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 -- Adolfo Bello [EMAIL PROTECTED] - 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: need index date help
Just a wild guess. If you are only using TO_DAYS(somedate) for the queries, why don't you create the column 'adate' as INT and index it? When you need the real DATE, use the FROM_DAYS() function. Just a wild guess that I think could improve your queries speed. Adolfo On Sat, 2002-12-21 at 17:50, John Hinton wrote: I keep getting weird return messages from the list.. not sure if this made it through.. I am running into a system wall here. I have at the moment about 2600 rows of data totaling 650K. I expect this to grow at a rate of about an additional 1200-1500 rows per week. I am using PHP to format the returns into webspace. I have a field named 'adate' which is a mysql 14 character timestamp (yes, I need HHMMSS data for other stuff). I am creating an array based on a distinct return from the database. I then am in turn looping through that array of about 25 entries, (which will remain at about 25 with time) and running each through 10 queries all based on date. The queries are really only two, with the exception of choosing separate intervals of time to return, one having distinct fields parsed, the other all rows parsed. The following are the two snippets of code which get repeated five more times with only the time interval changed. $table, $user_net are PHP variables and $page[$i] is the array of 25 entries. SELECT TO_DAYS(adate), mask FROM $table WHERE mask NOT LIKE '$user_net' AND page LIKE '$page[$i]' AND TO_DAYS(adate) = TO_DAYS(NOW()) - 6 SELECT adate, mask FROM $table WHERE mask NOT LIKE '$user_net' AND page = '$page[$i]' AND TO_DAYS(adate) = TO_DAYS(NOW()) - 6 All I need is the count from each query. So, these ten queries are being run 25 times on 2600 rows of data and it is taking about 4-6 seconds. I plan to collect data up to a limit of about 70,000 rows. If I can expect the query time to grow linearly, it would take about 2 minutes to generate this data. I need to get that down to maybe 15 seconds fingers crossed or as little as possible. I have indexed 'adate', but don't think the index really works within the functions? Maybe I'm stuck thinking inside of a box here? Perhaps there is one blindingly great solution which I have not considered. This is the first time I have ever created anything that really taxed a system... therefore I am new at thinking in many of these terms. Perhaps I should be rolling the data off into a temp file or something and running the results using PHP? I really don't know what direction to take, but I do see what appears to be a lot of repeating work, with only little changes in time chunks. Should I perhaps create a 'date' field, grabbing only MMDD and working from there? What am I not thinking about here? Any suggestions are very much welcome. -- Adolfo Bello [EMAIL PROTECTED] - 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: MySQL Command to edit a single record
Look in the manual the ALTER TABLE command. I don't remember the syntax but it can be done. Adolfo On Sat, 2002-12-21 at 18:56, Jeffrey Ellis wrote: Hi, Adolfo-- Thank you again! However, the problem is, the table is linked to a file on the web. If I create a new table with a different name the link wont work and no data will be entered. Is there a way to rename a table once you create it? Then, I could delete the old table after I create the new one, and then rename the new one to the old name so it will work with the website. All My Best, Jeffrey on 12/21/02 2:02 PM, Adolfo Bello at [EMAIL PROTECTED] wrote: CONCAT(FullName,' ',Address) regarding the lack of an ID field, create a new table with all the fields from the original table plus an ID field, auto_numeric, and insert the registers from the old table to the new one. Then, use the UPDATE on the new table. Adolfo On Sat, 2002-12-21 at 17:35, Jeffrey Ellis wrote: Hi, Adolfo-- Thank you! But I have two problems here...First, I (whoops!) don't have an id field. In the program I use, you can only set a key field when you first create the database. I would love to have an id field, but don't know how to create one now. :( The second is that the command will -- I think -- place the last name right up against the first name with no space before. Is there a way to do that so it looks like Foo Bar? All My Best, Jeffrey on 12/21/02 1:23 PM, Adolfo Bello at [EMAIL PROTECTED] wrote: The following query adds NAME and ADDRESS (where the last name is wrongly placed) and set ADDRESS to an empty string. UPDATE tablename SET FullName=CONCAT(FullName,Address),Address='' WHERE ID=the register id Adolfo On Sat, 2002-12-21 at 17:07, Jim Hankins wrote: Jeff, Are you looking for an update table syntax? Example Update tablename Set FullName=Their Full Name where someotherfield=someother value; Careful with this one. As if you omit the where clause it will update every record! -Original Message- From: Jeffrey Ellis [mailto:[EMAIL PROTECTED]] Sent: Saturday, December 21, 2002 3:52 PM To: [EMAIL PROTECTED] Subject: MySQL Command to edit a single record Hi-- I have just begun using MySQL...I am using a program called MySQL to manage an online database. I can see the entire table I need and all the records in that table for the db I¹m working with. I have a field called fullname which is supposed to receive the full name of the person entering the information. But sometimes, folks are entering their first name only in this field and then entering their last name in the address field by mistake. I would like to be able to send a command to the database to be able manually edit those records to add the last name back into the fullname field. Is there a way to do this? All My Best, Jeffrey Ellis - 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 -- Adolfo Bello [EMAIL PROTECTED] - 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: Help w/ Join Query
SELECT * FROM t1,t2,t3 WHERE t1.img_id=t2.img_id AND t2.caption_id=t3.caption_id Adolfo -Original Message- From: mike [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 19, 2002 8:15 PM To: [EMAIL PROTECTED] Subject: Help w/ Join Query Hello, I working on a small my project to learn more about joins. I have three tables here is what they look like CREATE TABLE `album` ( `album_id` smallint(3) NOT NULL auto_increment, `name` text NOT NULL, `description` text NOT NULL, `img_id` smallint(5) NOT NULL default '0', PRIMARY KEY (`photo_album_id`) ) TYPE=MyISAM; CREATE TABLE `images` ( `img_id` smallint(3) unsigned NOT NULL auto_increment, `img_path` varchar(60) default NULL, `img_caption_id` smallint(5) NOT NULL default '0', PRIMARY KEY (`img_id`) ) TYPE=MyISAM; CREATE TABLE `img_caption` ( `caption` text NOT NULL, `img_caption_id` smallint(5) NOT NULL auto_increment, PRIMARY KEY (`img_caption_id`) ) TYPE=MyISAM; Is it possible write a join query that would join the album table and the images table and then join the images table with the img_caption table?. I would like to select name and description from album , img_path from images, and caption from img_caption for any paticluar album row. Thanks in advance for the help, Mike - 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: CREATE TABLE issue?
Missing TYPE=INNODB in create table statement.
MySQL parses references for type MyISAM but doesn't use them.
Adolfo
-Original Message-
From: Jeff Snoxell [mailto:[EMAIL PROTECTED]]
Sent: Friday, December 20, 2002 1:14 PM
To: [EMAIL PROTECTED]
Subject: CREATE TABLE issue?
Hi again :)
I found the following mysql stuff in the MySQL manual in a section
explaining how to do relationships.
CREATE TABLE persons (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
name CHAR(60) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE shirts (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
owner SMALLINT UNSIGNED NOT NULL REFERENCES persons,
PRIMARY KEY (id)
);
What's the point of the REFERENCES persons bit at the end of the
definition of table Shirts? It doesn't seem to do anything.
Jeff
-
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: help me, please
select * from your_table order by no desc limit 0,1 -Original Message- From: mustakim abas [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 19, 2002 12:07 PM To: [EMAIL PROTECTED] Subject: help me, please Hello, i am new in mysql. I got a problem. I try write my C program with mysql API.I have 5 field in table.No,Name,Phone,Date,Time. How can i take one row where the No is bigger? Thanks for help. __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.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 - 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: MySQL/InnoDB-4.0.6 is released
There is not link to download the Windows version. Any date for the Windows version to be published? sql, query - 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: Excluding records?
/ (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 -- Adolfo Bello [EMAIL PROTECTED] - 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: JOIN based query problem (little help needed)
Hi Gigi: I got a 2 steps solution to your problem: 1.- Create a TEMPORARY TABLE with CREATE TEMPORARY TABLE anyname SELECT t1.id,t1.description,SUM(t2.quantities) AS Purchases,t3.quantities FROM products t1 INNER JOIN purchases t2 ON t1.id=t2.id INNER JOIN sellings t3 ON t1.id=t3.id GROUP BY t1.id.t1.description,t3.quantities; 2.- SELECT FROM anyname id,description,Purchases, SUM(quantities) AS Sellings, (Purchases-SUM(quantities)) AS Inventory GROUP BY id,description,Purchases; Adolfo -Original Message- From: Gigi Di Leo [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 17, 2002 11:53 AM To: [EMAIL PROTECTED] Subject: JOIN based query problem (little help needed) Hello list. Please help me to refine a JOIN based query which I am not able to correct. This the scenario (simplified). Three tables: Products - | id | description | - | 01 | bread| | 02 | milk | | 03 | coffee | - Purchases - | prod_id | quantities | - | 01| 10 | | 02| 5 | | 03| 3 | - Sellings - | prod_id | quantities | - | 01| 3 | | 01| 1 | | 02| 1 | | 02| 1 | - This the query which I could figure out: SELECT products.*, SUM(purchases.quantity) AS purchases, SUM(sellings.quantity) AS sellings, SUM(purchases.quantity)-SUM(sellings.quantity) AS inventory FROM products LEFT JOIN purchases ON products.id=purchases.prod_id LEFT JOIN sellings ON products.id=sellings.prod_id GROUP BY products.id ORDER BY products.id The query should return: - | prod_id | description | purchases | sellings | inventory | - |01 | bread|10 | 4 | 6 | |02 | milk |5 | 2 | 3 | |03 | coffee |3 | 0 | 3 | - This is what the query actually returns: - | prod_id | description | purchases | sellings | inventory | - |01 | bread|20 | 4 |16 | |02 | milk |10 | 2 | 8 | |03 | coffee |3 | 0 | 3 | - Thank you very much for your help. Gigi Here is the database dump if you wish to reproduce the scenario: # phpMyAdmin MySQL-Dump # version 2.3.2 # http://www.phpmyadmin.net/ (download page) # # Host: localhost # Generato il: 17 Dic, 2002 at 04:45 PM # Versione MySQL: 3.23.53 # Versione PHP: 4.2.3 # Database : `inventory` # # # Struttura della tabella `products` # CREATE TABLE products ( id int(11) NOT NULL auto_increment, description varchar(64) NOT NULL default '', PRIMARY KEY (id), KEY id (id) ) TYPE=MyISAM; # # Dump dei dati per la tabella `products` # INSERT INTO products VALUES (1, 'bread'); INSERT INTO products VALUES (2, 'milk'); INSERT INTO products VALUES (3, 'coffee'); # # # Struttura della tabella `purchases` # CREATE TABLE purchases ( prod_id int(11) NOT NULL default '0', quantity int(11) NOT NULL default '0', KEY prod_id (prod_id) ) TYPE=MyISAM; # # Dump dei dati per la tabella `purchases` # INSERT INTO purchases VALUES (1, 10); INSERT INTO purchases VALUES (2, 5); INSERT INTO purchases VALUES (3, 3); # # # Struttura della tabella `sellings` # CREATE TABLE sellings ( prod_id int(11) NOT NULL default '0', quantity int(11) NOT NULL default '0', KEY prod_id (prod_id) ) TYPE=MyISAM; # # Dump dei dati per la tabella `sellings` # INSERT INTO sellings VALUES (1, 3); INSERT INTO sellings VALUES (1, 1); INSERT INTO sellings VALUES (2, 1); INSERT INTO sellings VALUES (2, 1); - 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
RE: Inventory Query
Try this: SELECT t1.id AS prod_id, t1.description AS Description,SUM(t2.quantities) AS Purchases, SUM(t3.quantities) AS Sellings, (Purchases-Sellings) AS Inventory FROM Products t1 INNER JOIN Purchases t2 ON t1.id=t2.prod_id INNER JOIN Sellings t3 ON t1.id=t3.prod_id GROUP BY t1.id,t1.description -Original Message- From: Gigi Di Leo [mailto:[EMAIL PROTECTED]] Sent: Monday, December 16, 2002 12:03 PM To: '[EMAIL PROTECTED]' Subject: Inventory Query Hello list. Could you please suggest me a one-line query to solve a problem of inventory ? Three table: Products - | id | description | - | 01 | bread| | 02 | milk | | 03 | coffee | - Purchases - | prod_id | quantities | - | 01| 10 | | 02| 5 | | 03| 3 | - Sellings - | prod_id | quantities | - | 01| 3 | | 01| 1 | | 02| 1 | | 02| 1 | - The query should return - | prod_id | description | purchases | sellings | inventory | - |01 | bread|10 | 4 | 6 | |02 | milk |5 | 2 | 3 | |03 | coffee |3 | 0 | 3 | - Thank you very much for your help. Gigi - 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: Need Help phpMyAdmin !!
I guess your question is more about configuring IIS (or PWS) than about MySQL. Anyway, you have to create a web site in IIS pointing to your phpMyAdmin directory and define in it the default document (index.html. default.php or whatever) Adolfo -Original Message- From: Rachid Abdelkhalak [mailto:[EMAIL PROTECTED]] Sent: Monday, December 16, 2002 1:28 PM To: [EMAIL PROTECTED] Subject: Need Help phpMyAdmin !! Hello every one At the first, I'm Sorry for my bad english. I want to install phpMyAdmin for managing My MySql databases, I Downloaded ths zip file frome here: http://sourceforge.net/project/showfiles.php? group_id=23067 the file name is phpMyAdmin-2.3.3pl1-php3.zip I unzip this file on my Mysql Default folder: C:\inetpub\Mysql but I dont know how to make after for display the MySQL Admin HTML page: http://localhost/phpmyadmin/index.php Some one have a proposition for me. I am just a newbbie in MySQL and PHP development. Thanks. -- |-Rachid Abdelkhalak |-Account Manager |-MTDS S.A. |-tel +212.3.767.4861 |-fax +212.3.767.4863 |-14, rue 16 novembre |-Rabat, Kingdom of MoroccO - 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: How to xfr Windows MySQL db to Linux ?
I have done that for months on Windows2000/XP. A MySQL database accessed by a PHP application which runs under Apache. Without any problem, I have now that working environment under Mandrake 9. I regenerated the db under Linux with a dump from the Windows db. Adolfo On Mon, 2002-12-16 at 19:30, tmb wrote: Will MySQL/Windows db files work on a Linux box? I'm guessing they will, but then someone questioned me. I'm creating a MySQL/PHP db prototype on a Windows box running Apache... but it will end up on a Linux/Apache server some day. Thanks for any help. tmb __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.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 -- Adolfo Bello [EMAIL PROTECTED] - 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: a simple query
SELECT a.Name,b.Name,c.Name FROM your_table a LEFT OUTER JOIN your_table b ON a.Code=b.ParentFemale LEFT OUTER JOIN your_table c ON a.Code=c.ParentMale -Original Message- From: Fam. Tarniceru [mailto:[EMAIL PROTECTED]] Sent: Friday, December 13, 2002 3:39 PM To: MySQL Mailing List Subject: a simple query Hi, I have a table like this: --- | Code | Name | ParentFemale | ParentMale | --- |1 | Child| 2 | 3 | --- |2 | Mather | 0 | 0 | --- |3 | Father | 0 | 0 | --- and I need a query to obtain this table: -- | Name | ParentFemale | ParentMale | -- | Child | Mather | Father| -- and if in I have the next values in table 1 --- | Code | Name | ParentFemale | ParentMale | --- |1 | Child| 0 | 0 | --- |2 | Mather | 0 | 0 | --- |3 | Father | 0 | 0 | --- I need the next values for table 2: -- | Name | ParentFemale | ParentMale | -- | Child | Null| Null | -- Can you help me, please? Sincerely, Adrian --- Xnet scaneaza automat toate mesajele impotriva virusilor folosind RAV AntiVirus. Xnet automatically scans all messages for viruses using RAV AntiVirus. Nota: RAV AntiVirus poate sa nu detecteze toti virusii noi sau toate variantele lor. Va rugam sa luati in considerare ca exista un risc de fiecare data cand deschideti fisiere atasate si ca MobiFon nu este responsabila pentru nici un prejudiciu cauzat de virusi. Disclaimer: RAV AntiVirus may not be able to detect all new viruses and variants. Please be aware that there is a risk involved whenever opening e-mail attachments to your computer and that MobiFon is not responsible for any damages caused by viruses. - 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: Nested MySQL Statements
Not until version 4.1 -Original Message- From: Peter Vertes [mailto:[EMAIL PROTECTED]] Sent: Friday, December 13, 2002 8:43 AM To: MySQL Help List (E-mail) Subject: Nested MySQL Statements Hello All, Is it possible to do nested select statements with MySQL ? Basically I'm trying to display the last 5 rows that got inserted into a table. My SQL query looks like this: select * from tablename limit ((select count(*) from tablename) - 5), -1; In theory it works for me :) but MySQL complains. Is it possible to do nested queries with MySQL ? Does anyone have a better way of displaying the last x amount of rows inserted into a table ? Thanks in advance... -Pete Peter Vertes Beast Financial Systems 404 Fifth Avenue New York, NY 10018 - 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: BEGINNER: in mysql, better to do 1 table for one relationship, even with a one to one relation ?
Could you post your create table statement? It should be something like: CREATE TABLE relation_person_car( personID int 11 NOT NULL, carID int 11 NOT NULL, PRIMARY KEY (personID,carID) ); -Original Message- From: Alliax [mailto:[EMAIL PROTECTED]] Sent: Saturday, December 14, 2002 9:16 AM To: Adolfo Bello; [EMAIL PROTECTED] Subject: RE: BEGINNER: in mysql, better to do 1 table for one relationship, even with a one to one relation ? Hello, I've tried your way, but my SQL reports error, that I have specified more than ONE primary key, so I can't do the primary key on both fields. Is that a mySQL 3.X limitation ? My tables are ISAM. Following your advice, I've deleted the relation_person_carID, so only cardID and personID are left i nthe table, with no primary key. Is it a good idea, and is it safe, not to have a primary key ? Cheers, Damien COLA -Message d'origine- 3) table relation_person_car personID int 11 carID int 11 PRIMARY KEY (personID,carID) something like : table relation_person_car relation_personID primary int 11 autoincrement personID int 11 carID int 11 - 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: BEGINNER: in mysql, better to do 1 table for one relationship, even with a one to one relation ?
Using the ALTER statement: Step 1.- DROP the primary key Step 2.- ADD the new primary key Adolfo -Original Message- From: Alliax [mailto:[EMAIL PROTECTED]] Sent: Saturday, December 14, 2002 9:54 AM To: Adolfo Bello; [EMAIL PROTECTED] Subject: RE: BEGINNER: in mysql, better to do 1 table for one relationship, even with a one to one relation ? Hello, you're right, creating the table with 2 primary keys works fine. What I was doing is try to convert my personID and carID fields into primary, and it says: Error MySQL : Invalid SQL: ALTER TABLE `varmalinalliax`.`tgl_rel_rest_card` CHANGE `restID` `restID` INT (11) DEFAULT '0' NOT NULL , CHANGE `cardID` `cardID` INT (11) DEFAULT '0' NOT NULL , ADD PRIMARY KEY(`restID`), ADD PRIMARY KEY(`cardID`) Error My SQL number: 1068 (Multiple primary key defined) What simple query could I make to transform the fields in primary ? I don't have access to the mysql server via command line, I use a php package (eSKUeL, it's like phpMyAdmin) Cheers, Damien COLA -Message d'origine- Could you post your create table statement? It should be something like: CREATE TABLE relation_person_car( personID int 11 NOT NULL, carID int 11 NOT NULL, PRIMARY KEY (personID,carID) ); - 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: Root user password changing
After loging in mysql as root, enter the command:
SET PASSWORD FOR root=password('the_new_password');
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]
Sent: Saturday, December 14, 2002 5:07 PM
To: [EMAIL PROTECTED]
Subject: Root user password changing
how exactly do I change the Root user's password for mysql?
like this? :
mysqladmin -u root -p'newpasswd' ? (without a space
between -p and
'newpasswd' ?)
and, if that is correct, I guess I'd then be presented with a prompt
for the old passwd, right?
Then, the new one would take effect?
TR
-
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: newbie - linking queries with dynamic where conditions
You´re missing the GROUP BY clause select a.domain, a.transport, sum(c.recipient_count) sum(c.recipient_size) from transport as a, acl as b, recipientstats as c where a.id=b.transportId and b.adminId='1' and c.recipient_email like '%a.domain' GROUP BY a.domain,a.transport; -Original Message- From: news [mailto:[EMAIL PROTECTED]] On Behalf Of Max Clark Sent: Thursday, December 12, 2002 5:30 PM To: [EMAIL PROTECTED] Subject: Re: newbie - linking queries with dynamic where conditions So I am trying to accomplish something like this: select a.domain, a.transport, sum(c.recipient_count) sum(c.recipient_size) from transport as a, acl as b, recipientstats as c where a.id=b.transportId and b.adminId='1' and c.recipient_email like '%a.domain'; But I know I am missing something because of the error. Can anyone point me in the right direction? Thanks in advance, Max Max Clark [EMAIL PROTECTED] wrote in message ataq2r$ev1$[EMAIL PROTECTED]">news:ataq2r$ev1$[EMAIL PROTECTED]... Hi- I am trying to write a sql query that will output (domain, transport, sum(count), sum(size)) from multiple tables for many records. When the domain field is dynamic based on the adminId passed to the query, how do I execute the second query at the same time? Thanks in advance, Max select a.domain, a.transport from transport as a, acl as b where a.id=b.transportId and b.adminId='1'; select sum(count), sum(size) from stats where email like '%a.domain'; - 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
RE: BEGINNER: in mysql, better to do 1 table for one relationship, even with a one to one relation ?
I would really prefer a little variation of the third way you mention: 1) table person personID primary int 11 autoincrement 2) table car carID primary int 11 autoincrement 3) table relation_person_car personID int 11 carID int 11 PRIMARY KEY (personID,carID) Adolfo -Original Message- From: Alliax [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 12, 2002 8:35 PM To: [EMAIL PROTECTED] Subject: BEGINNER: in mysql, better to do 1 table for one relationship, even with a one to one relation ? The way I've always done it, and I wonder if I am not doing lots of things wrong: i have 2 tables, one person and one address for a one to one relation, the way I do it: table person personID primary int 11 autoincrement addressID int 11 table address addressID primary int 11 autoincrement if it's a one ot many relationship i do it like that: table person personID primary int 11 autoincrement table car addressID primary int 11 autoincrement personID int 11 NOW, reading this great mailinglist, I've come to believe the right way to do it that works in both relationship types : table person personID primary int 11 autoincrement table car carID primary int 11 autoincrement table relation_person_car relation_personID primary int 11 autoincrement personID int 11 carID int 11 I understand it's closer to the real way to represent relations in database design, but I wonder what else can be done with mySQL to simplify the work. for example, should I make something special with the type of fields personID and addressID in table relation_person_car ? another example, it would be easier to name all the primary key fields 'id' instead of '*nameOfTheTable*ID', but what would be the backdraws when coding queries and server scripts ? Any critics on my beginner way of handling relations is most welcome. Cheers, Damien COLA Cordialement, __ Alliax ~CV : http://LingoParadise.com/cv.php Un site pour Toulon : http://www.ToulonParadise.com Un site pour Renaud : http://www.rfaucilhon.com Un site pour Director : http://www.LingoParadise.com Un site pour Harmonica: http://www.LingoParadise.com/mp3 -Message d'origine- De : Michael T. Babcock [mailto:[EMAIL PROTECTED]] Envoyé : lundi 9 décembre 2002 15:15 À : [EMAIL PROTECTED] Objet : Re: QUICK: What is the optimal way to store opening times ? On Fri, Dec 06, 2002 at 05:54:53PM +0100, Alliax wrote: I have one question : if I go the RestTimes route, that is having a row per day and so 7 row per restaurants : can I, in one SQL request, know if THAT restaurant is open or close now ? I now there are NOW() function in SQL and probably many others, but I am not at ease to use them since I have pretty basic SQL skills. This should be a personal work assignment for you, but try: SELECT * FROM RestTimes WHERE RestID = ... AND OpenTime now() and CloseTime now(); FWIW, you'll have to do a calculation in there such that OpenTime is midnight today + seconds from day offset. Its not difficult; find some calendaring code for examples. -- Michael T. Babcock CTO, FibreSpeed Ltd. (Hosting, Security, Consultation, Database, etc) http://www.fibrespeed.net/~mbabcock/ - 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
RE: Field type conversion question
I would suggest having EmpTable (PRIMARY KEY=empID) EventsTable (PRIMARY KEY=eventID) AttendantsTable (PRIMARY KEY=[empID,eventID]) From there you can build queries to find persons who attended an event or which events a person went to. Adolfo -Original Message- From: Brad Harriger [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 12, 2002 10:00 AM To: [EMAIL PROTECTED] Subject: Field type conversion question I need to create a table that will store employees that attended certain events. I have two tables set up for this purpose. One (EmpTable) stores employee demographic information. The other (Events) stores information about the events (date, time, location, etc.) In EmpTable, the employee name is stored in fields FirstName and LastName. I would like to have a field in Events that will store multiple names with both fields from EmpTable concatenated into one string (i.e. FirstName LastName) What is the most efficient way to do this in MySQL? Thanks, Brad This e-mail and any files transmitted with it are confidential and are intended solely for the use of the individual or entity to whom they are addressed. This communication may contain material protected by legal privileges or statutory protections. If you are not the intended recipient or the individual responsible for delivering the e-mail to the intended recipient, please be advised that you have received this e-mail in error and that any use, dissemination, forwarding, printing, or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please immediately notify us and delete the original message. Upon request, we will reimburse you for reasonable costs incurred in notifying us. - 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: Displaying output from MySQL
SELECT * FROM your_table WHERE your_number_field REGEXP \. -Original Message- From: Beauford.2003 [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 12, 2002 3:26 AM To: [EMAIL PROTECTED] Subject: Displaying output from MySQL Hi, Not sure if this is a PHP of a MySQL question, so I am sending it to both groups. Basically I have a list of numbers with two decimal places in the MySQL database, but I only want to display some of them with the decimal points. i.e. 70 (not 70.00) 87 51.5 46.75 12 29 5.5 -1 45 I know it's probably a weird request, but any thoughts on how one would do this either through PHP or MySQL. 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 - 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: Select Where !=
SELECT n.uid, n.name from names_tables n LEFT OUTER JOIN exclude ON n.uid = exclude.n_uid WHERE exclude.n_uid IS NULL -Original Message- From: Michelle de Beer [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 11, 2002 3:22 PM To: mysql list Subject: Select Where != I have two tables. One with names and one for excluding certain names. Exclude-table contains the uid for the name excluded. If I want to see which names has been excluded, this query does the job: Select n.uid, n.name from names_tables n, exclude WHERE n.uid = exclude.n_uid But if I want to select all names, but leave out the ones that are in the exclude-table, I thought this would do it, but no. Select n.uid, n.name from names_tables n, exclude WHERE n.uid != exclude.n_uid It has something to do with the != thingy... Any thoughts? // Michelle sql, query __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.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 - 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: Select and Sort?
SELECT ... WHERE Category 'others' UNION SELECT ... WHERE Category='others' -Original Message- From: Tariq Murtaza [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 10, 2002 7:12 AM To: Fred van Engen Cc: [EMAIL PROTECTED] Subject: Re: Select and Sort? Thanks for help. here is another solution for that... SELECT if (Category = 'others', '999', 0) as priority, ID, Category FROM TableName ORDER BY priority, Category Tariq Fred van Engen wrote: On Tue, Dec 10, 2002 at 03:32:13PM +0500, Tariq Murtaza wrote: I want to achieve recordset like below (sorting category by Name and including 'Others' at the end), I want to do that by single query ;), any idea??? ID Category 6 biochemistry 62 Commerce 95 chemistry 2 Engg 87 Ecommerce 7 math 45 physics 1 Software Engg 5 Others Try something like this: SELECT id, category from mytable order by concat(if(id=5,'B','A'),category); Only problem is that it won't optimize, so use this on small tables only. Regards, Fred. - 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: Where does MySQL store db files?
1 - On a Linux/Apache box, when you create a MySQL db with tables, etc, Where does MySQL store the files associated with the db? Generally, /var/lib/mysql. Check your my.cnf 2 - On subsiquent db's, does MySQL store those files in the same directory or each db in a seperate folder? Depends on table type. For MyISAM you will have a subdirectory for each database. For InnoDB a common file (usually, ibdata in /var/lib/mysql) plus a subdirectory where the *.frm are stored. thanks - tmb __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.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 - 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: help on NOT EXISTS SQL query
SELECT * FROM table1 t1 LEFT OUTER JOIN table2 t2 ON t1.field1=t2.field2 WHERE t2.field2 IS NULL -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Murad Nayal Sent: Monday, December 09, 2002 11:38 AM To: MySQL List Subject: help on NOT EXISTS SQL query Hello all, I need to run query like (in mysql ver.4): select * from table1 as t1 where not exists (select NULL from table2 as t2 where t1.field1 = t2.field1) I know you can emulate an 'exists' subquery with a join. but I just can't think of a way to emulate a 'not exists' without a subquery. probably due to my limited sql experience. any hints? thanks a lot Murad BTW: when do you think mysql 4.1 would be stable enough for robust use (not necessarily mission critical). - 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: May someone try this script under Mandrake 9 and Mysql 4.0.5
Thanks a lot. I´ll stick to 3.23.53 until 4.0.6 appears. Adolfo -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED]] Sent: Sunday, December 08, 2002 4:21 PM To: [EMAIL PROTECTED] Subject: Re: May someone try this script under Mandrake 9 and Mysql 4.0.5 Adolfo, - Original Message - From: Adolfo Bello [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Sunday, December 08, 2002 12:47 AM Subject: May someone try this script under Mandrake 9 and Mysql 4.0.5 I have used a database creation script for months under 3.23.53a. Now, I want to upgrade to version 4.0.5 and the script doesn't work. The first table is created but mysql complains with: Error 1005 at line 10 Can't create table ./dbdir/tblbasestados.frm (errno:150) = = = SCRIPT TO TRY = = = CREATE TABLE tblbasregiones( intregion INTEGER PRIMARY KEY, strregion VARCHAR(30) NOT NULL ) TYPE=InnoDB ; CREATE TABLE tblbasestados( // this is line 10 intestado INTEGER PRIMARY KEY, strestado VARCHAR(50) NOT NULL, intregion INTEGER NOT NULL, INDEX(intregion), FOREIGN KEY (intregion) REFERENCES tblbasregiones(intregion) ON DELETE RESTRICT ON UPDATE CASCADE ) TYPE=InnoDB ; = = = END OF SCRIPT = = = I just can't spot what's wrong and want to know if there is something wrong in my installation. InnoDB does not support ON UPDATE CASCADE yet, though the support may be available already in 4.0.6. 3.23 just ignores the ON UPDATE clause, but 4.0.5 gives an error 150 from it. Thanks in advance, Adolfo Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB sql query - 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: select question
Use the CONCAT function Adolfo -Original Message- From: tag [mailto:[EMAIL PROTECTED]] Sent: Friday, December 06, 2002 3:57 AM To: [EMAIL PROTECTED] Subject: select question HI, I need to do a select query that can do the following: select * from table where col1 like hex(somestring); My problem is HOW do I get the % in there??? The Mysql Server is 4.0.4 and the table has a blob field with hex stored in it Thanks Tonino - 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: Drop, Create. . .help
drop table if exists your_table; create your_table (...); -Original Message- From: Peter Abilla [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 04, 2002 1:12 PM To: [EMAIL PROTECTED] Subject: Drop, Create. . .help I want to do the following: 1-If table x exists, then drop. 2-then, create table x again. Explanation: Table x in (1) has different date parameters than table x in (2). They are called the same name because there is a function that uses that table. Basically, I want to do something like: DROP TABLE IF EXISTS ExistingDocID CREATE TABLE ExistingDocID SELECT distinct OrderingPhysicianID FROM vu_StudyInformation WHEREStartDate Between '1999-11-25' and '2002-05-01' ORDER BY OrderingPhysicianID asc Any ideas? sql, query - 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
May someone try this script under Mandrake 9 and Mysql 4.0.5
I have used a database creation script for months under 3.23.53a. Now, I want to upgrade to version 4.0.5 and the script doesn't work. The first table is created but mysql complains with: Error 1005 at line 10 Can't create table ./dbdir/tblbasestados.frm (errno:150) = = = SCRIPT TO TRY = = = CREATE TABLE tblbasregiones( intregion INTEGER PRIMARY KEY, strregion VARCHAR(30) NOT NULL ) TYPE=InnoDB ; CREATE TABLE tblbasestados( // this is line 10 intestado INTEGER PRIMARY KEY, strestado VARCHAR(50) NOT NULL, intregion INTEGER NOT NULL, INDEX(intregion), FOREIGN KEY (intregion) REFERENCES tblbasregiones(intregion) ON DELETE RESTRICT ON UPDATE CASCADE ) TYPE=InnoDB ; = = = END OF SCRIPT = = = I just can't spot what's wrong and want to know if there is something wrong in my installation. Thanks in advance, Adolfo - 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: No my.cnf - why?
Taake a look a t /usr/share/mysql (or /usr/share/doc/mysql). You will find some my.cnf templates in there. Adolfo -Original Message- From: Vidiot [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 04, 2002 2:40 AM To: MySQL mail list Subject: No my.cnf - why? I installed the Linux RPMs for the latest version of MySQL. But, no my.cnf. How do I create one? I can't find anything in the 800+ page manual on how to create said file. I can't get any of the programs to work either. The mysql daemon starts, the firewall has a hole cut in it for my IP to get through port 3306, but it doesn't help: mrvideo.ZROOT 6 /usr/bin/mysqlshow /usr/bin/mysqlshow: Access denied for user: 'root@localhost' (Using password: NO) mrvideo.ZROOT 3 /usr/bin/mysqladmin -u root -h localhost password xxx /usr/bin/mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user: 'root@localhost' (Using password: NO)' mrvideo.ZROOT 7 /usr/bin/mysqladmin -u root -h mrvideo password vidiot90210 /usr/bin/mysqladmin: connect to server at 'mrvideo' failed error: 'Host 'mrvideo.vidiot.com' is not allowed to connect to this MySQL server' I previously posted the following query, which hasn't resulted in any responses, so I'll try again... Date: Sun, 1 Dec 2002 02:31:59 -0600 (CST) I found the following RPMs: perl-DBD-MySQL-1.2215-1.i386.rpm perl-DBI-1.14-10.i386.rpm But they aren't good enough: rpm -ivh MySQL*3.23.53a-1.i386.rpm perl-DBD-MySQL-1.2215-1.i386.rpm perl-DBI-1. 14-10.i386.rpm error: failed dependencies: MySQL-DBI-perl-bin is needed by MySQL-bench-3.23.53a-1 Just what the hell is the person looking for that packaged the bench RPM? It would be nice if the MySQL page that has the Linux RPMs would list the dependencies and where to get the RPMs that it needs. But, nope, nada. Until I can find that particular RPM, the bench RPM will be left off the list. I'm attempting to install the following Linux RPMs: MySQL-3.23.53a-1.i386.rpmMySQL-client-3.23.53a-1.i386.rpm MySQL-Max-3.23.53a-1.i386.rpmMySQL-devel-3.23.53a-1.i386.rpm MySQL-bench-3.23.53a-1.i386.rpm MySQL-shared-3.23.53a-1.i386.rpm When I do so I get the following error: rpm -ivh MySQL*3.23.53a-1.i386.rpm error: failed dependencies: MySQL-DBI-perl-bin is needed by MySQL-bench-3.23.53a-1 The only Perl DBI module on the MySQL website is the DBI-1.18.tar.gz tarball. Where is said RPM located? Thanks for any pointers to the location. MB -- e-mail: [EMAIL PROTECTED] /~\ The ASCII \ / Ribbon Campaign X Against Visit - URL: http://www.vidiot.com// \ HTML Email - 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: Some Basic Questions I can't Find Answers to
I will assume that your hosting service supports MySQL, which most of them do. Also, most of hosting services allow you to administer your database using phpMyAdmin, in which you can create and drop databases and tables, run SQL scripts, etc. I will also recommend you to install Apache in your Windows machine and make the whole testing of your site in there before uploading the site to your host site. Adolfo -Original Message- From: Linda Carter [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 04, 2002 11:18 AM To: [EMAIL PROTECTED] Subject: Some Basic Questions I can't Find Answers to I've got two MySQL books that I've just purchased in order to help me utilize the MySQL on my web host's server. I've been searching them both, but I still don't have answers to some very basic questions. Okay, the MySQL database I want to run will be on my web host. Since I don't have the server physically in my possession, is the working method to install MySQL to my machine, develop the routines there, then upload them to my server? I've been going on that assumption and have attempted to install to my Windows 2000 pro system. Still fumbling with that. Should I be trying to install the version most like the one on my host's server? He's on LInux Apache. Will that cause a problem? I don't see anything in either of these books on uploading, which makes me wonder if they're assuming I have access to the actual server. Of course I do, in a limited way, but I don't see any explanations on how to access the MySQL on my virtual machine over at the web host's. What am I missing here? Linda Carter - 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: MySQL
En el mio tampoco, pero todo funciona OK. Mandrake 9 y MySQL 4.0.5 Adolfo -Original Message- From: Fernando Grijalba [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 04, 2002 11:21 AM To: MySQL Help Subject: RE: MySQL En que sistema lo instalaste? Yo hice la instalacion en Red Hat 8.0 y lo instalo en /usr/local/mysql(version infor) y luego tuve que crear un link (/usr/local/mysql) que apuntara a ese directorio. JFernando ** sql ** -Original Message- From: Vicente Valero [mailto:[EMAIL PROTECTED]] Sent: December 4, 2002 07:27 To: MySQL List Subject: MySQL Hola, He instalado MySQL 3.23.53a y en numerosos documentos veo referencias a '/usr/local/lib/mysql' cuando dicho directorio en mi sistema no existe, así como a la libreria libmysqlclient.so He hecho algo mal en la instalación? Puedo crear dicho directorio consiguiendo los archivos de algun lugar? Gracias Vicente --- Hello, I have installed MySQL 3.23.53a and I have seen in a lot of places references to '/usr/local/lib/mysql' directory, and this doesn't exist in my system. Also I have seen references to 'libmysqlclient.so' in this directory. Have I made something wrong during the installation? Can I create these directory and copy its files from somewhere? Thank you Vicente __ _ Yahoo! Messenger Nueva versión: Webcam, voz, y mucho más ¡Gratis! Descárgalo ya desde http://messenger.yahoo.es - 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
RE: Table setup question
Let's assume you want the entire albums list which the song Day and Night by Billie Holiday is in. SELECT t1.title,t1.artist,t3.name FROM sings t1 INNER JOIN reference t2 ON t1.ids=t2.ids INNER JOIN album t3 ON t2.ida=t3.ida WHERE t1.title='Day and Night' AND t1.artist='Billie Holiday' (or WHERE t1.ids=1 if you know the id of the song) Relationship is like: [songs] [album] \\// [reference] (this later one contains the list of songs of each album) This way you treat songs and album separately and link them through another table so you don't have any limit on the number of albums a song can belong to. If you know the id of a song all you have to do is Adolfo -Original Message- From: Beauford.2003 [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 05, 2002 3:42 PM To: Adolfo Bello Cc: [EMAIL PROTECTED] Subject: Re: Table setup question Adolfo, Thanks for the info, but can you elaborate on it, 'cause basically I am just not getting the concept. No way no how can I get these joins to work. mysql describe songs; (this contains an ID field and the title of the mysql song and the artist.) ++-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-+---+ | ids| int(11) | | PRI | 0 | | | title | varchar(55) | YES | | NULL| | | artist | varchar(30) | YES | | NULL| | ++-+--+-+-+---+ 3 rows in set (0.00 sec) mysql describe album; (this contains an ID field and the name of the mysql album the above songs came from.) +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | ida | int(11) | | PRI | 0 | | | name | varchar(35) | YES | | NULL| | +---+-+--+-+-+---+ 2 rows in set (0.00 sec) mysql describe reference; (this contains the ID's that corrspond to the above two tables.) +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | ida | int(11) | | PRI | 0 | | | ids | int(11) | | PRI | 0 | | +---+-+--+-+-+---+ 2 rows in set (0.00 sec) This table is in this format: The left column corresponds to the ID of the ablum and the right field correspond to the ID of the song. From the example below, you can see that song #1 appears on Albums 2, 3, 6, and 16. SongAlbum 12 13 16 115 27 221 223 So with all this information, how would I perform a select that would show the all the albums a particular song were on. (from the example above what would I need to do to show the album name and song title for song ID # 1 above). Sorry if this is long, I just wanted to make sure there is enough info. TIA Beauford - Original Message - From: Adolfo Bello [EMAIL PROTECTED] To: 'Beauford.2003' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, December 02, 2002 7:33 AM Subject: RE: Table setup question Try something like CREATE TABLE album( ida int primary key , title varchar(n) not null ) ; CREATE TABLE songs( ids intprimary key, song varchar(m) not null ) ; CREATE TABLE albumsongs( ida int not null, ids int not null, primary key(ida,ids), foreign key(ida) references album(ida), foreign key(ids) references songs(ids)); Querying for albums which have a songs is something like: SELECT title,song FROM album INNER JOIN albumsongs ON ... INNER JOIN songs ON ... WHERE song='your song name'; -Original Message- From: Beauford.2003 [mailto:[EMAIL PROTECTED]] Sent: Sunday, December 01, 2002 9:59 PM To: [EMAIL PROTECTED] Subject: Table setup question Hi, I have a database of albums by a group and I want to be able to search on this table to find out what songs are duplicated on what albums (there are 36+ albums). The problem is how do I set up the database. Example: Album1 has 3 songs. 1.song, 2.song, 3.song, 4.song Album2 has 4 songs. 4.song, 5.song, 6.song, 3.song Album3 has 4 songs. 7.song, 8.song, 1.song, 3.song So 3.song appears on all 3 albums. Currently I have it set up with two tables as shown below, but I am thinking there has to be a better way to do this than to duplicate the name of the song three, four, or five times in the table. Table AlbumName AlbumID == Album1 1 Album2 2 Album3 3 Table SongTitle SongID === == 3.song 1 3.song 2 3
RE: Select and count duplicates
SELECT headline, count(*) AS HITS from your_table GROUP BY headline -Original Message- From: Michelle de Beer [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 03, 2002 8:27 AM To: mysql list Subject: Select and count duplicates I have a column containing headlines. In this column there are some duplicates, like this: --- monsters headline1 monsters halloween monsters halloween ... -- How can I get a result like this instead? HEADLINE | HITS --- monsters | 3 halloween | 2 headline1 | 1 --- Any thoughts? // Michelle Sql query __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.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 - 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: join count and 0
select oi.id,oi.name, count(o.id) from orderid oi LEFT OUTER JOIN orders o ON o.order_id = oi.id group by oi.id,oi.name ; -Original Message- From: Charles Verge [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 03, 2002 11:53 AM To: [EMAIL PROTECTED] Subject: join count and 0 I am wanting to see if there is a way to do a join but have count(id) show up as 0 when there is no records in the 2nd table. With out having to 2 selects. for example. orderid = has persons name and contact info select oi.id,oi.name, count(o.id) from orderid oi, orders o where o.order_id = oi.id group by oi.id; you have say 100 records in orderid , and only 50 of them have related orders in orders for those that don't have matching orders I would want count(o.id) to show 0 rather then not being listed. Any way to do this in a single sql statment ? Charles - 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: MySQL: Select Not In Table
SELECT Table1.ID FROM Table1 LEFT OUTER JOIN Table2 ON Table1.ID=Table2.ID WHERE Table2.ID IS NULL; -Original Message- From: Henning Sittler [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 03, 2002 10:16 AM To: 'mysql users' Subject: MySQL: Select Not In Table Just wondering if anyone knows of a work around for selecting rows in Table1 that have an ID column value which is not found in the ID column of Table2. I have seen a workaround for selecting similar rows IN both tables, but I want NOT IN. This would be a work around to the sql subselect query in the following statement: SELECT Table1.ID FROM Table1 WHERE Table1.ID NOT IN (SELECT Table2.ID FROM Table2); Thanks for any help! - 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: Trying2insert Date2table
Missing quotes. It should read: sNewCallBackSQLINSERT INTO CallBacks (LearnerID,CallDate,CallTime) VALUES (2113 , '01/01/2002' , '0:00') -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 03, 2002 9:41 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Trying2insert Date2table Hi, I am trying to insert the following value to a Date/Time field in SQLServer7 table. Here is the Line values Im tring to execute sNewCallBackSQLINSERT INTO CallBacks (LearnerID,CallDate,CallTime) VALUES (2113 , 01/01/2002 , 0:00) Here is the statement: sNewCallBackSQL = INSERT INTO CallBacks (LearnerID,CallDate,CallTime) VALUES ( ExLearnerID , sCallBackDate , sCallBackTime ) - 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: MySQL: Select HELP!
SELECT * FROM processo_arquivos ORDER BY DATE DESC LIMIT 0,10 -Original Message- From: Felipe Moreno - MAILING LISTS [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 03, 2002 1:42 PM To: [EMAIL PROTECTED] Subject: MySQL: Select HELP! Importance: High Hi List Users, I want to know if anyone has any idea on how can I do the SQL command below to archive a result. I have one table called processo_arquivos that have a filed called DATE and another FIELD called COD (primary key). I want to select the last TEN (10) dates from the Database, but only the last TEN. How Can I do this? Any ideia? I tried the sql bellow o archive this, but I was unable to do it. I just want to do this with ONLY one select, not with two. Thanks for any idea. Regards, Felipe - 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
Script works under 3.23.53a, doesn't under 4.0.5
I have used a database creation script for months under 3.23.53a. Now, I want to upgrade to version 4.0.5 and the script doesn't work. The first table is created but mysql complains with: Error 1005 at line 10 Can't create table ./dbdir/tblbasestados.frm (errno:150) CREATE TABLE tblbasregiones( intregion INTEGER PRIMARY KEY, strregion VARCHAR(30) NOT NULL ) TYPE=InnoDB ; CREATE TABLE tblbasestados( // this is line 10 intestado INTEGER PRIMARY KEY, strestado VARCHAR(50) NOT NULL, intregion INTEGER NOT NULL, INDEX(intregion), FOREIGN KEY (intregion) REFERENCES tblbasregiones(intregion) ON DELETE RESTRICT ON UPDATE CASCADE ) TYPE=InnoDB ; MySQL-MAX running under Mandrake 9.0 I just can't spot what's wrong. Any help will be truly appreciated. Adolfo - 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: Newbie Question
Php is widely used for web development, and works nicely with MySQL. Fuerthermore, it is supported by most hosting services. I recommend you to give it a try Adolfo -Original Message- From: Colaluca, Brian [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 03, 2002 2:25 PM To: [EMAIL PROTECTED] Subject: Newbie Question Hello there. I recently got a wild hair to set up an amateur web site that will allow a group of friends to log and comment on bottles of wine. Having never done web development before, I set out to first research what relational database I would use that would satisfy my need for maintaining three related tables: an objective category of wines (1999 XYZZY Vineyard Cab), a subjective category of wines (It sucked), and a user listing. I found that MySQL fits perfectly (my budget, that is), plus I wanted something to run off of my Linux box. Where to go from here is where I'm stuck now. I am tossed between going towards a scripting language like PHP or PERL, and embedding that directly in my HTML, or possibly going the Java route. Does XML have the capability of directly querying the database? I'm curious to hear what methodologies have worked best for users of MySQL, keeping in mind a tight budget. Is the J2EE architecture worth looking more heavily into, or is that somewhat overkill for what I'm trying to achieve? One thing is certain, I would like to use either Mandrake or Red Hat for my OS, and of course prefer to use Apache as my web server. Other than that, I am open to suggestions or comments. bjc- :== Brian J. Colaluca - Software Engineer :== DRS Technologies - ESG === [EMAIL PROTECTED] === phone: (301) 921-8107 - 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: mysql.sock error
Check if mysql is started by entering ps -ax | grep -i mysql If not started try: service mysql start Adolfo -Original Message- From: Jeff Hollingshead [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 03, 2002 5:17 PM To: [EMAIL PROTECTED] Subject: mysql.sock error at first when trying to run mysql it did this: [root@shadow mysql]# bin/safe_mysqld --user=mysql [1] 3124 [root@shadow mysql]# Starting mysqld daemon with databases from /var/lib/mysql 021121 02:27:42 mysqld ended [1]+ Donebin/safe_mysqld --user=mysql then, i stopped mysql and gave the command again and it seemed to work: [root@shadow mysql]# bin/safe_mysqld --user=mysql [1] 5456 [root@shadow mysql]# Starting mysqld daemon with databases from /var/lib/mysql however, when trying to connect to mysql it does this still: Enter password: /usr/local/mysql/bin/mysqladmin: connect to server at 'localhost' failed error: 'Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)' Check that mysqld is running and that the socket: '/tmp/mysql.sock' exists! any help appreciated - 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: relational is relational is relational, but ...
MySQL supports using InnoDB tables. CREATE TABLE customers ( num INT PRIMARY KEY, Name VARCHAR(50) NOT NULL); CREATE TABLE sales( Product VARCHAR(15) NOT NULL, Price DOUBLE NOT NULL, Cust INT NOT NULL, # PRIMARY KEY definition goes here, INDEX (Cust), FOREIGN KEY(Cust) REFERENCES customers(num) ON DELETE RESTRICT ON UPDATE CASCADE); Now if the num changes in customer, MySQL automatically updates Cust on SALES. Adolfo -Original Message- From: David T-G [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 03, 2002 5:36 PM To: mysql users Subject: relational is relational is relational, but ... ++ ++ | SALES | | CUSTOMERS | +-+---+--+ +--+-+ | product | price | cust | | num | name| +-+---+--+ +--+-+ | TXLblue | 14.99 | 1136 | | 1136 | richard roe | +-+---+--+ +--+-+ | CMMblck | 11.50 | 2408 | | 2408 | jane doe| +-+---+--+ +--+-+ | SMMblue | 22.75 | 2408 | | 8421 | bob smith | +-+---+--+ +--+-+ where SALES.cust gets filled with the same value as CUSTOMERS.num -- but should the customer number somehow change we're now out of sync, whereas in a truly relational database the fields in the SALES.cust column would *be* the fields in the CUSTOMERS.num column rather than simply having the same value. Am I lost, out of my tree, confused, or bang on? And how (perhaps InnoDB tables) does MySQL support this if I'm not any of the first three? TIA HAND mysql query, :-D - -- David T-G * There is too much animal courage in (play) [EMAIL PROTECTED] * society and not sufficient moral courage. (work) [EMAIL PROTECTED] -- Mary Baker Eddy, Science and Health http://www.justpickone.org/davidtg/Shpx gur Pbzzhavpngvbaf Qrprapl Npg! -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (FreeBSD) iD8DBQE97SO3Gb7uCXufRwARAooDAKDI6dhugjyejLAEwC5Sjl6Pjqz/nACcDoj0 vUcEuhNZHvTYh+KjGz05/9Q= =hZrX -END PGP SIGNATURE- - 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: Converting Access to MySQL
SELECT @myvar := 1 ; -Original Message- From: Gerald Norman [mailto:[EMAIL PROTECTED]] Sent: Sunday, December 01, 2002 10:37 PM To: [EMAIL PROTECTED] Subject: Converting Access to MySQL I have an application that currently is using an Access database and am in the process of converting over to MySQL. I downloaded the ODBC driver and have been using ADO and have had no problems at all up to this point. I have a number of parameterized SQL statements that worked fine in Access, but aren't working at all with MySQL. I know that MySQL supports variables in SQL statements using the @ prefix. I've tried setting that as the prefix for the parameters but it doesn't work. I keep getting a 'unspecified error' when the SQL statement executes. Am I missing something or does MySQL not support parameters that can be set at runtime? Thanks in advance for any assistance. - 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: Table setup question
Try something like CREATE TABLE album( ida int primary key , title varchar(n) not null ) ; CREATE TABLE songs( ids intprimary key, song varchar(m) not null ) ; CREATE TABLE albumsongs( ida int not null, ids int not null, primary key(ida,ids), foreign key(ida) references album(ida), foreign key(ids) references songs(ids)); Querying for albums which have a songs is something like: SELECT title,song FROM album INNER JOIN albumsongs ON ... INNER JOIN songs ON ... WHERE song='your song name'; -Original Message- From: Beauford.2003 [mailto:[EMAIL PROTECTED]] Sent: Sunday, December 01, 2002 9:59 PM To: [EMAIL PROTECTED] Subject: Table setup question Hi, I have a database of albums by a group and I want to be able to search on this table to find out what songs are duplicated on what albums (there are 36+ albums). The problem is how do I set up the database. Example: Album1 has 3 songs. 1.song, 2.song, 3.song, 4.song Album2 has 4 songs. 4.song, 5.song, 6.song, 3.song Album3 has 4 songs. 7.song, 8.song, 1.song, 3.song So 3.song appears on all 3 albums. Currently I have it set up with two tables as shown below, but I am thinking there has to be a better way to do this than to duplicate the name of the song three, four, or five times in the table. Table AlbumName AlbumID == Album1 1 Album2 2 Album3 3 Table SongTitle SongID === == 3.song 1 3.song 2 3.song 3 7.song 3 etc. etc. So basically my search is - SELECT Album, Song FROM AlbumName, SongTitle WHERE AlbumName.ID=SongTitle.ID; Given the setup above, is there a way that I can put in the SongTitle.ID field that song appears on more than one album. Maybe something like: SongID === == 3.song 1, 2, 3 But then what would my search be. Sorry for the length of this, but I am learning MySQL and trying to get a handle on all of it. My way works, but I'm sure there has to be a better way. Any thoughts are appreciated. TIA, Beauford - 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: Table setup question
The other difference is that it works (BTW, a huge benefit) Adolfo -Original Message- From: Beauford.2003 [mailto:[EMAIL PROTECTED]] Sent: Monday, December 02, 2002 11:24 AM To: DL Neil; [EMAIL PROTECTED] Subject: Re: Table setup question DL, OK, I get it now. I thought there would need to be duplication in the AlbumSonglist DB. Just one other question though. What is the difference/benefits of doing it this way, than the way I have it. Currently I use two tables - the songlist table includes 2 id fields (one that corresponds with the album title, and one that is used for the song title). The difference between what you have said and what I have is that I have duplicated the actual song titles instead of a pointer to the song title This way I have eliminated one table. Thanks again, Beauford - Original Message - From: DL Neil [EMAIL PROTECTED] To: Beauford.2003 [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, December 02, 2002 10:41 AM Subject: Re: Table setup question Beauford, [please reply to the list - there are always others 'lurking' who will learn from the discussion (and use of the archives is a wonderful dream...)] You are correct, there will be duplication in the AlbumSong table - but not within one field, only within one column. Album will consist of a unique ID (IDA) and each Title will, one assumes be unique (unless two groups use the same name - I guess that must be possible). Similarly Songlist will also consist of a unique ID (IDS) and once again, one assumes that any repetition of title will be two different songs that share the same name. These two ID 'uniqueness' rules must apply for the next to work, ie you will most likely define them as PRIMARY KEY columns. The 'translation' table, breaks the 'unique' 'rule', So if song Q apears on albums A, C, and E as you ask, then we will see: AlbumSong IDAIDS 11 21 31 Note that while 1 repeats in AlbumSong, it does not repeat (a) in Songlist, nor in (b) one row/field of AlbumSong, eg IDA IDS 1,2,31 THE ABOVE IS TOTALLY WRONG!!! In the case of AlbumSong the table will not be able to have a PRIMARY KEY column(s), but each of the individual columns should probably be INDEXed for read-speed. Be aware that AlbumSong contains no real 'data' from an end-user perspective. It is a table made up purely of 'links' or 'keys' or 'IDs' to the other two tables. The Album and Songlist tables do all the 'data' work, AlbumSong does all the 'heavy lifting' to relate Album's data to Songlist's, and/or vice-versa. Apologies if this was not clear, first time round, =dn DL, OK, that helps a lot, but I am still confused. I am still struggling with how you can not have duplication in at least one of the tables. A diagram here may help. So if song Q apears on albums A, C, and E - I don't quite understand how AlbumSong is going to be able to know which songs are duplicated without duplication of ID's. (There are 500 songs and only 36 albums). Thanks AlbumSonglistAlbumSong IDATITLEIDSSongIDAIDS 1A 1Q?? 2B2R?? 3C3S?? 4D4T?? 5E 5V ?? ? ? ? ? ? ? - Original Message - From: DL Neil [EMAIL PROTECTED] To: Beauford.2003 [EMAIL PROTECTED]; Sent: Monday, December 02, 2002 4:32 AM Subject: Re: Table setup question Hi Beauford, You are on the right track. Yes you should remove the songs to a separate table. If you merely duplicate the first example (below) in two tables you have created a one-to-many relationship between the Album table and the Songs table - one album has many songs on it. Your query code applies. However the Songs table still has duplication in it, eg 3.song appears thrice, and we can't have that! The problem is, if you cut down the Songs table entries so that 3.song appears only once, how do you link to multiple Albums? So
RE: WHERE IN SYNTAX
Isn't this sort of impossible?
You are asking for something like a set which contains as an element
the set itself
WHERE fooId IN (fooId)
Adolfo
-Original Message-
From: Peter Abilla [mailto:[EMAIL PROTECTED]]
Sent: Monday, December 02, 2002 1:43 PM
To: [EMAIL PROTECTED]
Subject: sql:WHERE IN SYNTAX
Question about SQL WHERE IN syntax:
I know that something like this is fine to do:
(1)
SELECT foo
FROM bar
WHERE fooID in ('1','2')
In the example above, the parenthesis includes the itemized
fooID's. But, I want to do a similar thing where instead of
the actual fooID's in the WHERE IN clause, I reference a
column that contains the fooID's, like:
(2)
SELECT foo
FROM bar
WHERE fooID in (bar.fooID)
In this example, I have the table 'bar' and the column 'fooID'.
Is (2) possible? If not, what other strategies are there?
Please help.
PSA
-
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: Multiple Inserts and updates
INSERT INTO your_table(fields_list) VALUES (record_1),(record_2),...,(record_n); -Original Message- From: Ed Reed [mailto:[EMAIL PROTECTED]] Sent: Saturday, November 30, 2002 3:47 PM To: [EMAIL PROTECTED] Subject: Multiple Inserts and updates Can anyone give me an example of how to insert and/or update multiple records in the same table simultaneously? I have a table with three fields; ID, PartNum, Qty. The user enters PartNum and Qty data in an unbound grid UI. When the user hits Save I'd like to build a single SQL statement from the data in the grid and have that one statement update or insert the rows in the table as necessary. I don't wanna have to build a separate SQL statement for each row of the grid (which is the only way I know how at this moment). Any help would be appreciated. - 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: Select: JOIN magic?
SELECT t1.name AS Nomi, t2.name AS Propo FROM award t0 INNER JOIN user t1 ON t0.nominee=t1.uid INNER JOIN user t2 ON t0.proposer=t2.uid -Original Message- From: Michael Carter [mailto:[EMAIL PROTECTED]] Sent: Saturday, November 30, 2002 11:51 AM To: [EMAIL PROTECTED] Subject: Select: JOIN magic? Hello. I'm trying to select and display from a couple tables I have. I have an awards table, which has info on an award a user has earned. I've also a user table which lists all the users. What I'm trying to do is display information about an award, but can't find the right SELECT syntax to make it work the way I want. The award table has two associations to the user table: the nominee and the proposer: What I want: Award | Nominee | Proposer | Awarded on | 1 | Joe Shmoe | Fred Smith | 2002-11-29 | What I get: Award | Nominee | Proposer | Awarded on | 1 | Joe Shmoe | 3 | 2002-11-29 | The (simplified) tables look like this: +---+ +-+ | award | | user| +---+ +-+ | uid (int) | | uid (int PRI) | | nominee (int) | | name (varchar) | | proposer (int)| +-+ | awarded (Date)| +---+ I've been doing the following to get the basic info: SELECT * FROM award LEFT JOIN user ON award.nominee=user.uid; This gets me the info for the nominee, including name and everything else. I'd like to spare making a whole other query to the DB to get the name for the Proposer, but I can't think of a way to make it work! Any help would be appreciated. :) Regardez, Michael Carter Pilot/Programmer - 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: script
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Monday, November 25, 2002 9:51 AM To: Mysql maillist Subject: script Hi All. I have written a SQL script that should retrieve all the rows from a table that match a value given as parameter by the user. The script is something like: SELECT TEST_NAME AS NAME, TEST_DATE FROM TEST WHERE TEST_NAME=NAME; where NAME is the parameter the user should give. Is NAME both the parameter and the column alias? Your query looks to me like SELECT something FROM atable WHERE something=something Adolfo - 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: /tmp/mysql.sock question (newbie)
I am also a newbie so don't misunderstand my question. Is there any performance or functionality reason to change mysql.sock location? Adolfo On Mon, 2002-11-11 at 14:37, Black, Kelly W [PCS] wrote: You can do this in /etc/my.cnf ~K Black -Original Message- From: [EMAIL PROTECTED] [mailto:CZachary;wiley.com] Sent: Monday, November 11, 2002 10:05 AM To: [EMAIL PROTECTED] Subject: /tmp/mysql.sock question (newbie) Hello all, I would like to change where the /tmp/mysql.sock file gets created. How to I get it to create the file in another location? Thanks. - 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
MySQL doesn't start after booting
Every time I start or restart the PC, the MySQL service doesn't come up, so we have to start it manually with the command service mysql start. I am a really newbie to Linux (2 weeks using Mandrake 9). Using drakconf I checked that the service was scheduled to start at boot time, which it is. By the way, when I try to start MySQL using drakconf, this (drakconf) freezes up (well, sort of) but the service is started. Everything that I have tested works fine but the bootup annoyance. I am using MySQL-Max-3.23.53a-1.i686 on a 512Mb PC. Any help will be truly appreciated. Adolfo. - 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: starting Mysql on linux
Make sure the service is started. If no, enter the command: console service mysql start Adolfo On Sun, 2002-11-10 at 16:44, Stick Dragon wrote: i have installed redhat 8 and slackware linux and been trying to get mysql to run. when i get to the console and type 'mysql' i get, error 2002; cant connect to local mysql server through socket '/var/lib/mysql/mysql.sock' i have tried to run mysqld through the console but says it cant find the command. i'm not really linux or mysql savy. any help on getting it to run would be great. _ STOP MORE SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail - 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 -- ___ // \\ @ ___ ___@ // // // /\ / \\ // \ // Adolfo Bello [EMAIL PROTECTED] // \\ // / \\ / // // / //Bello Ingenieria S.A, Presidente //___// // / _/ \___\\ //___/ // cel: +58 416 609-6213 // fax: +58 212 952-6797 http://www.bisapi.com // sms: www.tun-tun.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
