Re: Output from select is not what expected
Hi, It seems like you are not "joining" the tables in any way. Say you have: Table1 --- record1_id = 1 --- record2_id = 2 Table2 --- record1_id = 3 --- record2_id = 4 Table3 --- record1_id = 5 --- record2_id = 6 If you do a select all, you get all permutations: 1, 3, 5 1, 3, 6 1, 4, 5 1, 4, 6 2, 3, 5 2, 3, 6 2, 4, 5 2, 4, 6 * records. What you want is to link the tables in some way. For instance: table 1: id int name varchar(100) table 2: id int person int address blob select * from table1, table2 where table1.id = table2.person This will only pull out records in table2, where table2's current record has a value for field "person" equal to the value of "id" in the current record in table1. I suggest you get yourself a good SQL book (any suggestions anyone?) or have a look at some online tutorials: http://www.devshed.com/Server_Side/MySQL/Intro/ has a good overview of MySQL and some examples. http://www.wdvl.com/ is a good web devlopers resource, with tips and examples of different programming languages... Hope this helps... Ben - Original Message - From: "Fates" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, April 18, 2001 2:37 PM Subject: Output from select is not what expected I have three tables in a database called menus and I have added 2 records to each table so 2*3=6 records. Why is it when I do a "select * from table1, table2, table3" I get 8 records back when there are only 2 records in each table? What am I doing wrong? Some of the fields are references if that makes a difference? I want to list the output from all tables together. Using Latest MySQL RedHat Linux RPM -- This email was sent using w3mail. - 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 structure a random query
You could also do something like: select field1, field2 from table order by rand() limit 1; HTH Ben - Original Message - From: "Philip Mak" [EMAIL PROTECTED] To: "Alec Smith" [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, April 18, 2001 11:50 PM Subject: Re: How to structure a random query On Wed, 18 Apr 2001, Alec Smith wrote: I've got a table of X rows, each with a unique ID as determined by auto_increment when the row is inserted into the database. How would I go about doing a SELECT on a row of the database and have MySQL return a row at random? Maybe you can do something with the MySQL RAND() function, which returns a random number between 0.0 and 1.0 (so you'd have to normalize it to between 1 and MAX(ID)). You may not be able to use the grouping function that way, though. MySQL doesn't seem to let you use grouping functions in the WHERE clause. mysql select num from ffml where num=floor(rand()*max(num))+1; ERROR : Invalid use of group function Anyone else have an idea? -Philip Mak ([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: Mysql and Perl DBI
Gordon, What kind of errors? A copy/paste of errors would be useful. Cheers Ben - Original Message - From: Gordon Stewart [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, April 11, 2001 1:29 PM Subject: Mysql and Perl DBI I am running Redhat 7 and mysql and I have tried to install Msql-Mysql DBI module But I get errors when I do a make. Can any one help Gordon - 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: wildcard character in sql grant statement
Hey Scott, Try: GRANT ALL ON DATA.* TO user1@"%" IDENTIFIED BY "password" /or simply/ GRANT ALL ON DATA.* TO user1 IDENTIFIED BY "password" Not specifying a domain defaults to %. HTH Ben -Original Message- From: Scott Meesseman [mailto:[EMAIL PROTECTED]] Sent: 09 April 2001 01:27 To: [EMAIL PROTECTED] Subject: wildcard character in sql grant statement Hello, Is there a new wildcard character in 3.23? I am trying to use the grant statement GRANT ALL ON DATA.* TO user1@% IDENTIFIED BY "password" It doesnt seem to like the % as it returns an sql error Thanks for your time, Scott - 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: Disappearing \
Without being picky, and just to let Jack know, the line: "\\\server\\folder" should be: "server\\folder" Each double slash pair (\\) is replaced in the database with one slash (\). I recommend you take a look at the AddSlashes() and ereg_replace() functions if you're using PHP (http://php.net), or looking at some regular expressions for perl in order to format strings for database insertion. Ben -Original Message- From: John Dean [mailto:[EMAIL PROTECTED]] Sent: 09 April 2001 08:25 To: Jack A. Tinsley Jr.; [EMAIL PROTECTED] Subject: Re: Disappearing "\" Hi On Monday 09 April 2001 05:34, Jack A. Tinsley Jr. wrote: First, things, first - I am a newbie to MySQL but I have it up and running quite well. The only thing I haven't been able to figure out is why I'm losing "\" (back slashes) in my stored data. I have a application I'm considering MySQL for and one table must have a column containing a UNC path. When I insert the value of "\\server\folder", MySQL turns it into "\serverfolder" - very strange. I've set this column up as a varchar. Actually, this behaviour is not strange it is correct, since the '\' character is considered an escape character. If you want to insert the value "\\server\folder" you would need to add extra '\' characters i.e. "\\\server\\folder" - 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 -- Regards John -- MySQL Development Team __ ___ __ __ / |/ /_ __/ __/ __ \/ / John Dean [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\/ Mansfield, England, UK ___/ - 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 update 1 of two identical rows.
delete from db where date="2001-04-09" and CR_amount=300.00
insert into from db (date, DB_amount, CR_amount) values ("2001-04-09", NULL,
300.00);
insert into from db (date, DB_amount, CR_amount) values ("2001-04-09", NULL,
350.00);
would change the 3rd to 350.00 credit.
Unfortunately, there is no easy way of doing this, and if the above is an
example, where the number of duplicates is much higher, then it's going to
be awkward.
you might want to try (on a backup version of the table):
alter table db add id int auto_increment primary key;
This would insert a unique key field, but I'm not sure if it would
pre-populate existing records.
If it works, you can then use the update function where id="xxx";
Hope this helps
Ben
-Original Message-
From: Richard Reina [mailto:[EMAIL PROTECTED]]
Sent: 09 April 2001 10:59
To: [EMAIL PROTECTED]
Subject: How update 1 of two identical rows.
DateDB_amount CR_amount
2001-04-09 NULL300.00
2001-04-09 NULL750.00
2001-04-09 NULL300.00
Anyone know how can I do a query that will update the third record
without updating the first?
-
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
GRANT permissions
I'm confused, and wondering if anyone can help me. I'm running 2 virtual servers, let's say server1.domain.com and server2.domain.com. These virtual servers sit on servers named host1.isp.com and host2.isp.com respectively (I hope I haven't lost anyone yet). I have a perl script, that lists the contents of a table in a database stored on [EMAIL PROTECTED], and when I point the DSN to host=localhost, all works fine. Now I want the app to run on both virtual servers, so I modify the script on server2.domain.com to have DSN=server1.domain.com. I have granted all permissions required to: [EMAIL PROTECTED] [EMAIL PROTECTED] user@"%.domain.com" /and/ user@"%.isp.com" Yet the script doesn't run. It doesn't say cannot connect (I do get that error if I revoke all privs), but I get: DBI-connect(database=DBNAME;host=HOSTNAME;port=143) failed: Lost connection to MySQL server during query at /script/name line 26 Can't call method "prepare" on an undefined value at /script/name line 29. I can connect to the database from server2 using: mysql DBNAME -u USERNAME -h HOSTNAME -p without problems. I guess what I'm asking, to cut a long question short, is if anyone has any idea what this error message means. Lost connection make me think it authenticates, but then disconnects, however if I connect to localhost from server1, it works fine. One last thing, even if I "grant all to user@%" it won't run properly. Hoping someone can help, and thanks in advance... Ben - 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: character set works via web but not at command line?
Ian, I may be way off the mark here, but I've found MySQL usually starts quoting the line from the place at which the error occurred, thus indicating it didn't like the "insert" part. Not being able to see the preceding lines I can only suggest it may not have an active database to write to. When importing a mysqldump, make sure you specify the db name (I'm sorry if this is too simplistic and obvious to you :-) $ mysql dbname -u username -p mysqldump.file provide password If this is not the case, and the contents of your database are not sensitive, I'm willing to take a look and try it on my machine. Hope this helps, Ben -Original Message- From: Ian Corner [mailto:[EMAIL PROTECTED]] Sent: 08 April 2001 22:32 To: [EMAIL PROTECTED] Subject: character set works via web but not at command line? Hi (if needed you can find my environment setup below) I wish to load some data files at the mysql command prompt but seem to be failing due to Swedish characters being incorporated. Here is the error: ERROR 1064: You have an error in your SQL syntax near 'insert into category (categoryid,description,xsectionid) values ( "Hstar sljes"' at line 3 I was suprised as my web interface manages to place and retrieve Swedish characters with no problems. Yet when I review table information at the command prompt the Swedish characters are not visable. Having read many messages and the "Languge" page in the document I still have no answer as to why I can via the web, and why I cant via the data load files? I read a message dated early 2000 that compiling character sets was not going to be need shortly as support for character sets was being developed. Have I missed something? I tried the "default-character-set" tag and I got an error that #10 was not a compiled language? #10 being swe7 in the index file... and I figure meaning Swedish. Whats happening and how do I resolve this. In summary, I need to data load Swedish character files at the command line. Thanks for any help. Regards, Ian mySQL/Java environment - os: w2000 mySQL server: v3.23.36 mySQL client: v3.23.33 IBM JVM 1.3 Web solution: JSP/Beans Web server: Jakarta Tomcat (from Apache project) Web JDBC: mm.mysql-2.0.4 and Poolman _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - 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
