Re: Beginner question
Am 02.01.2012 16:33, schrieb Biz-comm: Thanks for any assistance. Web page that needs a sort of all records with a specific state, set to show, and if it exists in one of 4 categories. Using this: SELECT * FROM listings WHERE listing_state = 'DC' AND listings.listing_show ='y' AND listings.cat1 = 23 OR listings.cat2 = 23 OR listings.cat3 = 23 Gives up 2 records in the state of DC, set to show, and are listed in cat1. However, it also gives up 2 records in the state of VA, set to show, but are listed in cat2 (not in 1). Any assistance most appreciated. basic math how should mysql know that the OR-clauses are meant not standalone? SELECT * FROM listings WHERE listing_state = 'DC' AND listings.listing_show ='y' AND (listings.cat1 = 23 OR listings.cat2 = 23 OR listings.cat3 = 23) signature.asc Description: OpenPGP digital signature
Re: Beginner question
Hi Patrice I would try some brackets. Something like this should work SELECT * FROM listings WHERE listing_state = 'DC' AND listings.listing_show ='y' AND ( listings.cat1 = 23 OR listings.cat2 = 23 OR listings.cat3 = 23 ) Mike - Original Message - From: Biz-comm b...@biz-comm.com To: mysql@lists.mysql.com Sent: Monday, January 02, 2012 10:33 AM Subject: Beginner question Thanks for any assistance. Web page that needs a sort of all records with a specific state, set to show, and if it exists in one of 4 categories. Using this: SELECT * FROM listings WHERE listing_state = 'DC' AND listings.listing_show ='y' AND listings.cat1 = 23 OR listings.cat2 = 23 OR listings.cat3 = 23 Gives up 2 records in the state of DC, set to show, and are listed in cat1. However, it also gives up 2 records in the state of VA, set to show, but are listed in cat2 (not in 1). Any assistance most appreciated. Regards, Patrice Olivier-Wilson 828-628-0500 http://Biz-comm.com b...@biz-comm.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Beginner question
Perfect. Thanks so much. On Jan 2, 2012, at 10:40 AM, Mike OK wrote: Hi Patrice I would try some brackets. Something like this should work SELECT * FROM listings WHERE listing_state = 'DC' AND listings.listing_show ='y' AND ( listings.cat1 = 23 OR listings.cat2 = 23 OR listings.cat3 = 23 ) Mike Regards, Patrice Olivier-Wilson 828-628-0500 http://Biz-comm.com b...@biz-comm.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Beginner question
Hey, welcome to the lists, Be mindful that your query is using 2 tables and 'SELECT *'. On Tue, Oct 11, 2011 at 4:11 PM, Biz-comm b...@biz-comm.com wrote: I am trying to write a query for a web page that shows a list of users in a particular group. There are 3 tables: pm1_users that uses UserID pm1_groupsubscriptions that uses UserID and GroupID pm1_mailingroups that uses GroupID So I want to show all the users that belong to a specific mailingroup SELECT * FROM pm1_groupsubscriptions, pm1_users WHERE GroupID = 10 (10 = one of the mailingroups) That isn't enough to get there. That shows all uses. Thanks for any assistance. Regards, Patrice Olivier-Wilson 828-628-0500 http://Biz-comm.com b...@biz-comm.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=eroomy...@gmail.com
Re: Beginner question
- Original Message - From: Andrew Moore eroomy...@gmail.com Be mindful that your query is using 2 tables and 'SELECT *'. Which probably means not so much to someone who doesn't even know what a join is :-) Have a look at http://www.w3schools.com/sql/sql_join.asp . -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Beginner question
Thanks for the pointer. Digging out reference books to learn how to do a join. :-) On Oct 11, 2011, at 11:23 AM, Johan De Meersman wrote: Which probably means not so much to someone who doesn't even know what a join is :-) Have a look at http://www.w3schools.com/sql/sql_join.asp . Regards, Patrice Olivier-Wilson 828-628-0500 http://Biz-comm.com b...@biz-comm.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mySQL beginner question
Having duplicates is not a problem. As long as you don't have a UNIQUE index on it. Something else is happening in your code that is putting out the HTML I would guess. P.S. It should be SELECT * FROM jspCart_products; (your table, not your database) B Wiley Snyder wrote: Hello, hope this is the right list I created a table with the following code CREATE TABLE jspCart_products ( ProductID int primary key, CategoryID int, ModelNumber varChar(75), ModelName varChar(250), ProductImage varchar(250), UnitCost decimal(9,2), Description BLOB, ); The CategoryID has duplicate entrys. When I use SELECT * FROM mydatabase I see the categories just fine but when they are sent to an html page they are null values. Would that be a screw-up in my code or is it becuase I need to specify when I initially build the table like above that it uses duplicates? i hope that makes sense and thanks for replys in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mySQL beginner question
- Original Message - From: B Wiley Snyder [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, August 26, 2004 1:56 AM Subject: mySQL beginner question Hello, hope this is the right list I created a table with the following code CREATE TABLE jspCart_products ( ProductID int primary key, CategoryID int, ModelNumber varChar(75), ModelName varChar(250), ProductImage varchar(250), UnitCost decimal(9,2), Description BLOB, ); The CategoryID has duplicate entrys. When I use SELECT * FROM mydatabase I see the categories just fine but when they are sent to an html page they are null values. Would that be a screw-up in my code or is it becuase I need to specify when I initially build the table like above that it uses duplicates? i hope that makes sense and thanks for replys in advance. It's not clear to me what you are saying when you say that CategoryID has duplicate entries. Do you mean that you have this in your table: ProductIDCategoryIDModelNumberModelNameProductImage UnitCostDescription 11 B200Starburst blahblahblah50.00x'...' 21 C240Stonehenge foofoofoofoo30.00x'...' i.e. products that have different product numbers have the same category ID? If so, is that what you want in your table? It seems perfectly reasonable to me to have this situation in your table. When you say that the CategoryID is appearing as a null in your HTML reports, do you mean that they are not null in the tables? If that is the case, then it is almost certainly something in your code isn't handling the values in the CategoryID column correctly. You may want to post the code that reads the rows of the tables and formats it for the HTML page if you can't find the problem yourself. To be absolutely sure that the nulls being displayed by the program are bogus, run this query from your command line: select ProductID, CategoryID from yourtable where CategoryID is null If this query produces an empty result, you can be dead certain that your program is turning non-null CategoryIDs into nulls. If this query produces a non-empty result, check the HTML report and see if the rows containing null CategoryIDs match exactly the rows that contain nulls in the command line query; if they do, your application code is working perfectly and your table data has nulls in the CategoryID. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mySQL beginner question
Hello, hope this is the right list I created a table with the following code CREATE TABLE jspCart_products ( ProductID int primary key, CategoryID int, ModelNumber varChar(75), ModelName varChar(250), ProductImage varchar(250), UnitCost decimal(9,2), Description BLOB, ); The CategoryID has duplicate entrys. When I use SELECT * FROM mydatabase I see the categories just fine but when they are sent to an html page they are null values. Would that be a screw-up in my code or is it becuase I need to specify when I initially build the table like above that it uses duplicates? i hope that makes sense and thanks for replys in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Another beginner question
Marty Ray [EMAIL PROTECTED] wrote: Brace yourself for another beginner question... When I try to use the SHOW command (logged in as root) , I get an error: mysql show databases; ERROR: No query specified What version of MySQL do you use? What OS? There is a bug on Mac OS X in MySQL 4.1.1: http://bugs.mysql.com/bug.php?id=2058 -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Another beginner question
Brace yourself for another beginner question... When I try to use the SHOW command (logged in as root) , I get an error: mysql show databases; ERROR: No query specified Any suggestions? Thanks! Marty Ray -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
INNODB beginner question
Hi -- I'm still relatively new to MySql. I'm using InnoDB tables and foreign keys. When I set up the databases, I recall assigning a few large fixed-size files to the InnoDB engine for data (and I think) logs. I've been adding data to my databases daily and wish to know how full they are, and what is the process for extending the disk space allotted to them, should they need it. I do need to deal with the larger issue of rolling historic data out of the database, summarizing it, and stashing it in longer-term storage, but that's an problem for later. Right now I'm just curious how full are the DBs and when will it run out of space. Thanks for any pointers or advice -- -TO __ Do you Yahoo!? Yahoo! Finance: Get your refund fast by filing online. http://taxes.yahoo.com/filing.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INNODB beginner question
Tofu Optimist wrote: When I set up the databases, I recall assigning a few large fixed-size files to the InnoDB engine for data (and I think) logs. I've been adding data to my databases daily and wish to know how full they are, Use this command: show table status from database_name like 'table_name'; where database_name.table_name is an InnoDB table. The last column of the output will show the InnoDB free space. and what is the process for extending the disk space allotted to them, should they need it. You could make the last table space file auto extending or shut the server down and add more table space files. You can't make them smaller easily. The details are here: http://www.innodb.com/ibman.php#Adding.and.removing Good luck, Ware -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Beginner question - Preventing Duplicate Entries
You could make the column a unique key...that would prevent duplicates from being entered. Then if you want to be able to try inserting duplicates (like if you don't want the query to fail on duplicate attempts), you could do INSERT IGNORE INTO myTable ... Check http://www.mysql.com/doc/en/CREATE_TABLE.html and http://www.mysql.com/doc/en/ALTER_TABLE.html for information about keys. -Brian McCain - Original Message - From: Wileynet [EMAIL PROTECTED] To: 'mysql users' [EMAIL PROTECTED] Sent: Wednesday, March 26, 2003 3:03 PM Subject: Beginner question - Preventing Duplicate Entries Is there a sql statement that would not allow the same entry twice. Something like INSERT into myTable s WHERE s != Value(?). I don't know if that makes sense but I thought I would give it a shot. Basically I want to know if it is possible and if so can you point me to a webpage or give me an example if you can? Thanks in advance - Wiley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Beginner question - Preventing Duplicate Entries
Is there a sql statement that would not allow the same entry twice. Something like INSERT into myTable s WHERE s != Value(?). I dont know if that makes sense but I thought I would give it a shot. Basically I want to know if it is possible and if so can you point me to a webpage or give me an example if you can? Thanks in advance - Wiley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Beginner question - Preventing Duplicate Entries
If you wanted to use MyISAM tables and peform an initial select to determine whether you should insert, you could lock the table you would be selecting/insert from. 'LOCK TABLES table_name WRITE' Don't forget to unlock the table when you are done 'UNLOCK TABLES', otherwise you will likely have deadlocks. ~Wynne -Original Message- From: Wileynet [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 26, 2003 2:04 PM To: 'mysql users' Subject: Beginner question - Preventing Duplicate Entries Is there a sql statement that would not allow the same entry twice. Something like INSERT into myTable s WHERE s != Value(?). I don't know if that makes sense but I thought I would give it a shot. Basically I want to know if it is possible and if so can you point me to a webpage or give me an example if you can? Thanks in advance - Wiley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Beginner question - Preventing Duplicate Entries
Add a UNIQUE INDEX to the table you are inserting to. You will then get an error if you try to insert a second time with the same values. Alternativly you could use perform a select and then an insert within the same transaction, determining whether something exists in the db before inserting. This of course would require transactions supported by InnoDB tables and not supported by MyISAM. ~Wynne -Original Message- From: Wileynet [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 26, 2003 2:04 PM To: 'mysql users' Subject: Beginner question - Preventing Duplicate Entries Is there a sql statement that would not allow the same entry twice. Something like INSERT into myTable s WHERE s != Value(?). I don't know if that makes sense but I thought I would give it a shot. Basically I want to know if it is possible and if so can you point me to a webpage or give me an example if you can? Thanks in advance - Wiley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
BEGINNER QUESTION.
I have looked everywhere online, books. I simply would like to DELETE a FIELD from the command line. I have a database called hitcounter. Inside hitcounter I have one table with 3 fields. I've tried... DELETE FROM info FIELD count; ALTER info DELETE count; DROP count; Can someone please tell me how to do this, OR do you have to DROP the database and start over -thank you in advance Wiley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: BEGINNER QUESTION.
USE hitcounter; ALTER TABLE info DROP count; Hope this helps Roger -Original Message- From: Wileynet [mailto:[EMAIL PROTECTED] Sent: Monday, March 24, 2003 8:27 PM To: [EMAIL PROTECTED] Subject: BEGINNER QUESTION. I have looked everywhere online, books. I simply would like to DELETE a FIELD from the command line. I have a database called hitcounter. Inside hitcounter I have one table with 3 fields. I've tried... DELETE FROM info FIELD count; ALTER info DELETE count; DROP count; Can someone please tell me how to do this, OR do you have to DROP the database and start over -thank you in advance Wiley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: BEGINNER QUESTION.
You were so close... ALTER info DROP count; -Brian McCain - Original Message - From: Wileynet [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, March 24, 2003 5:26 PM Subject: BEGINNER QUESTION. I have looked everywhere online, books. I simply would like to DELETE a FIELD from the command line. I have a database called hitcounter. Inside hitcounter I have one table with 3 fields. I've tried... DELETE FROM info FIELD count; ALTER info DELETE count; DROP count; Can someone please tell me how to do this, OR do you have to DROP the database and start over -thank you in advance Wiley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: BEGINNER QUESTION.
ALTER TABLE table_name DROP col_name http://www.mysql.com/doc/en/ALTER_TABLE.html So... mysql use hitcounter; mysql ALTER TABLE info DROP count; Assuming info is the table name and count is the column you'd like to drop. -Original Message- From: Wileynet [mailto:[EMAIL PROTECTED] Sent: Monday, March 24, 2003 5:27 PM To: [EMAIL PROTECTED] Subject: BEGINNER QUESTION. I have looked everywhere online, books. I simply would like to DELETE a FIELD from the command line. I have a database called hitcounter. Inside hitcounter I have one table with 3 fields. I've tried... DELETE FROM info FIELD count; ALTER info DELETE count; DROP count; Can someone please tell me how to do this, OR do you have to DROP the database and start over -thank you in advance Wiley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: beginner question: how many queries via PHP are...
At 08:33 PM 2/16/2002 , you wrote: Hello I am new to the subject. I am experimenting in mysql via PHP with a nice book (PHP and MySQL Web development). My question is how many queries to mysql, made via PHP, should considered ok for efficiency. I know it has much to do with the size of databases, but I would like to get an idea. Thanks. Nikolas Nikolas, One thing you may want to implement is adding a LIMIT clause to your queries to prevent users from returning too many rows. Mine would be set to 100 and in a lot of cases, fewer than that. The queries will run much faster. Brent - 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
beginner question: how many queries via PHP are...
Hello I am new to the subject. I am experimenting in mysql via PHP with a nice book (PHP and MySQL Web development). My question is how many queries to mysql, made via PHP, should considered ok for efficiency. I know it has much to do with the size of databases, but I would like to get an idea. Thanks. Nikolas - 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 question: how many queries via PHP are...
I am new to the subject. I am experimenting in mysql via PHP with a nice book (PHP and MySQL Web development). My question is how many queries to mysql, made via PHP, should considered ok for efficiency. I know it has much to do with the size of databases, but I would like to get an idea. Thanks. Unfortunately there really is no set number one could come up withit all depends on what you need, the intended load of the server, speed of the queries, optimizations of both MySQL and the OS, the system you're using and a plethora of other variablesbasically remember it's not size but speed that determines efficiency...so if it takes 2 seconds to complete 5 queries you'd be better off doing that than a single query for the same information that thats 5 seconds to complete =) Sincerely, Craig Vincent - 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 question
Assuming that you have a table called 'test' where there are three columns 'a', 'b' and 'c'. Now you have 'a'+'b' as unique but neither only 'a' nor 'b'. To specify that, define a and b both combined as primary key for the table, as in the following statement: CREATE TABLE `test` ( `a` TINYINT NOT NULL, `b` TINYINT NOT NULL, `c` TINYINT NOT NULL, PRIMARY KEY (`a`, `b`) ); This would achieve exactly what you want, With regards, Kundan On 11/28/01 10:18 PM, Etienne Marcotte [EMAIL PROTECTED] wrote: When creating a table, can we specify that the combinaison of two fields needs to be unique but each of those two fields may have duplicates, just not both the same? Can we have a required attribute on one of two fields? I have phone_line_french and phone_line_english, i require one of those two to be filled (or both). I can check that in my script, just asking if we can do it in mySQL Regards, Etienne - 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
Beginner question
When creating a table, can we specify that the combinaison of two fields needs to be unique but each of those two fields may have duplicates, just not both the same? Can we have a required attribute on one of two fields? I have phone_line_french and phone_line_english, i require one of those two to be filled (or both). I can check that in my script, just asking if we can do it in mySQL Regards, Etienne -- Etienne Marcotte Specifications Management - Quality Control Imperial Tobacco Ltd. - Montreal (Qc) Canada 514.932.6161 x.4001 - 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
Beginner question - getting last inserted ID
Hello! I am working on my first MySQL client. I have a number of tables, each containing a AUTO_INCREMENT PRIMARY KEY column. The client is built in Java and using a JDBC-bridge to connect to the database. When I do an insert in one of these tables the primary key column gets a new ID. How do I get this ID? I guess I cannot use SELECT MAX(id) FROM Table since old, deleted id's are reused for new rows. Please, help me! Regards, Anna - 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 question - getting last inserted ID
Anna Åhnberg writes: When I do an insert in one of these tables the primary key column gets a new ID. How do I get this ID? I guess I cannot use SELECT MAX(id) FROM Table since old, deleted id's are reused for new rows. Please, help me! I shall help you help yourself, for from that you will benefit in the long run, as will I. The manual has all the necessary information: http://www.mysql.com/doc/C/R/CREATE_TABLE.html http://www.mysql.com/doc/e/x/example-AUTO_INCREMENT.html The unofficial FAQ has a chapter on AUTO_INCREMENT: http://www.bitbybit.dk/mysqlfaq/faq.html#ch6_0_0 What you're looking for is probably LAST_INSERT_ID(), but I'll let you find it yourself. Oops, now I told you. :-P //C -- Carl Troein - Círdan / Istari-PixelMagic - UIN 16353280 [EMAIL PROTECTED] | http://pixelmagic.dyndns.org/~cirdan/ Amiga user since '89, and damned proud of it too. - 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 question - getting last inserted ID
The manual has all the necessary information: http://www.mysql.com/doc/C/R/CREATE_TABLE.html http://www.mysql.com/doc/e/x/example-AUTO_INCREMENT.html The unofficial FAQ has a chapter on AUTO_INCREMENT: http://www.bitbybit.dk/mysqlfaq/faq.html#ch6_0_0 What you're looking for is probably LAST_INSERT_ID(), but I'll let you find it yourself. Oops, now I told you. :-P //C His language might also have a function for it. - 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 question - getting last inserted ID
Anna Åhnberg writes: Thanks, I actually already found the chapters but now I also now how to use the function too! Let me quote from the manual: LAST_INSERT_ID([expr]) Returns the last automatically generated value that was inserted into an AUTO_INCREMENT column. mysql select LAST_INSERT_ID(); - 195 Thus you'd follow these steps: 1) INSERT into the table, and leave out the auto column or supply a value of 0 or NULL 2) Verify that the query succeeded 3) SELECT LAST_INSERT_ID() 4) Get the result of the SELECT. On success, the SELECT will return one row with one value in it. That is the number you're interested in knowing. If you were using MySQL's C API there is a function that returns the ID without the need for steps 3-4, but since you are communicating over JDBC I don't think there's a simpler solution available. The query in step 3 is extremely fast, so it doesn't really matter, except that it's a bit more work to make an extra query. //C -- Carl Troein - Círdan / Istari-PixelMagic - UIN 16353280 [EMAIL PROTECTED] | http://pixelmagic.dyndns.org/~cirdan/ Amiga user since '89, and damned proud of it too. - 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 question - getting last inserted ID
If you are using the Mark Matthews JDBC driver (MM.MySQL), there's another way to get the last insert id: instead of using a generic Statement object to execute the INSERT command, use an org.gjt.mm.mysql.Statement object. After executing an INSERT, the method getLastInsertID() will return the value of LAST_INSERT_ID. This value is sent to the client along with other status info after the INSERT command is executed, so the method does not require another call to the server. --Greg Johnson -Original Message- From: Carl Troein [mailto:[EMAIL PROTECTED]] Sent: Friday, November 09, 2001 10:41 AM To: [EMAIL PROTECTED] Subject:Re: Beginner question - getting last inserted ID Anna Åhnberg writes: Thanks, I actually already found the chapters but now I also now how to use the function too! Let me quote from the manual: LAST_INSERT_ID([expr]) Returns the last automatically generated value that was inserted into an AUTO_INCREMENT column. mysql select LAST_INSERT_ID(); - 195 Thus you'd follow these steps: 1) INSERT into the table, and leave out the auto column or supply a value of 0 or NULL 2) Verify that the query succeeded 3) SELECT LAST_INSERT_ID() 4) Get the result of the SELECT. On success, the SELECT will return one row with one value in it. That is the number you're interested in knowing. If you were using MySQL's C API there is a function that returns the ID without the need for steps 3-4, but since you are communicating over JDBC I don't think there's a simpler solution available. The query in step 3 is extremely fast, so it doesn't really matter, except that it's a bit more work to make an extra query. //C -- Carl Troein - Círdan / Istari-PixelMagic - UIN 16353280 [EMAIL PROTECTED] | http://pixelmagic.dyndns.org/~cirdan/ Amiga user since '89, and damned proud of it too. - 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 question - getting last inserted ID
Anna Åhnberg writes:
Thanks, I actually already found the chapters but now I also now how to
use the function too!
Let me quote from the manual:
LAST_INSERT_ID([expr])
Returns the last automatically generated value that was
inserted into an AUTO_INCREMENT column.
mysql select LAST_INSERT_ID();
- 195
Thus you'd follow these steps:
1) INSERT into the table, and leave out the auto column or supply
a value of 0 or NULL
2) Verify that the query succeeded
3) SELECT LAST_INSERT_ID()
4) Get the result of the SELECT. On success, the SELECT will
return one row with one value in it. That is the number you're
interested in knowing.
If you were using MySQL's C API there is a function that returns
the ID without the need for steps 3-4, but since you are
communicating over JDBC I don't think there's a simpler solution
available. The query in step 3 is extremely fast, so it doesn't
really matter, except that it's a bit more work to make an extra
query.
You can access the fast mode from JDBC if you are using the mm.mysql driver:
if (stmt instanceof org.gjt.mm.mysql.Statement) // Fast fetch of clipID when using
MySQL
return (int) ((org.gjt.mm.mysql.Statement) auxStmt).getLastInsertID () ;
else // slower, but portable, way of getting clipID
{
ResultSet rs = stmt.executeQuery (SELECT LAST_INSERT_ID ()) ;
rs.next () ; //to first (only) row
int res = rs.getInt (1) ;
rs.close () ;
return res ;
}
-
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 question - getting last inserted ID
On Fri, 9 Nov 2001, Carl Troein wrote: Date: Fri, 09 Nov 2001 15:41:21 GMT From: Carl Troein [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: Beginner question - getting last inserted ID Anna Åhnberg writes: Thanks, I actually already found the chapters but now I also now how to use the function too! Let me quote from the manual: LAST_INSERT_ID([expr]) Returns the last automatically generated value that was inserted into an AUTO_INCREMENT column. mysql select LAST_INSERT_ID(); - 195 Thus you'd follow these steps: 1) INSERT into the table, and leave out the auto column or supply a value of 0 or NULL 2) Verify that the query succeeded 3) SELECT LAST_INSERT_ID() 4) Get the result of the SELECT. On success, the SELECT will return one row with one value in it. That is the number you're interested in knowing. If you are using the mmysql jdbc driver you can directly access it. use import org.gjt.mm.mysql.Statement; instead of java.sql.Statement; Then after the insert long id = stmt.getLastInsertID(); Note: this is the only method to get a bigint unsigned index back accordign to the mm.mysql doc's . See source forge for details. If you were using MySQL's C API there is a function that returns the ID without the need for steps 3-4, but since you are communicating over JDBC I don't think there's a simpler solution available. The query in step 3 is extremely fast, so it doesn't really matter, except that it's a bit more work to make an extra query. //C -- Carl Troein - Círdan / Istari-PixelMagic - UIN 16353280 [EMAIL PROTECTED] | http://pixelmagic.dyndns.org/~cirdan/ Amiga user since '89, and damned proud of it too. - 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 Sincerely, William Mussatto, Senior Systems Engineer CyberStrategies, Inc ph. 909-920-9154 ext. 27 - 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 Question
- Original Message - From: jim barchuk [EMAIL PROTECTED] With luck you got the book at a reputable dealer who'll allow you to exchange it for Paul Dubois _MySQL_ New Riders ISBN0-7357-0921-1. Please spend a few extra $ and save yourself many many hours of confusion and frustration. Does anybody know if any MySQL books have been released that is as solid as the Paul Dubois book but on a more advanced level? My copy is pretty dog-eared and has been an invaluable resource to learning MySQL but would like to learn more advanced database design and use that the book only was able to cover briefly do to size. Thanks Joel - 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 question
Hello Marshall, Saturday, June 09, 2001, 9:05:18 AM, you wrote: MB I'm a new user, working through the tutorial provided in the MySQL documentation (chapter 9). I'm trying to load the data from the pet.txt file into the pet table using the specified command: MB mysql load data local infile pet.txt into table pet; MB I get the following: MB ERROR: MB File 'pet.txt' not found (Errcode: 2) MB I assumed that the text file was in the wrong directory, and reviewed the section describing the load data local command. Just to be safe, I put copies of the text file in several locations, MB including the menagerie directory (which should be the only one required), the bin directory, and the mysql directory. I still get the error message. MB What might I be doing wrong? MB Also, is there a FAQ that might hold the answer to newbie questions like this? MB Thanks for the help. Did you try load data local infile ...fullpath/pet.txt into table pet;? -- Best regards, Olexandrmailto:[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: beginner question
At 1:05 AM -0500 6/9/01, Marshall Bohlin wrote: I'm a new user, working through the tutorial provided in the MySQL documentation (chapter 9). I'm trying to load the data from the pet.txt file into the pet table using the specified command: mysql load data local infile pet.txt into table pet; I get the following: ERROR: File 'pet.txt' not found (Errcode: 2) I assumed that the text file was in the wrong directory, and reviewed the section describing the load data local command. Just to be safe, I put copies of the text file in several locations, including the menagerie directory (which should be the only one required), the bin directory, and the mysql directory. I still get the error message. The file should be in the same directory where you're running mysql. Otherwise, you'll need to specify the pathname to the file. For example, if it's in the /tmp directory, your statement would be: mysql load data local infile /tmp/pet.txt into table pet; What might I be doing wrong? Also, is there a FAQ that might hold the answer to newbie questions like this? Thanks for the help. -- Paul DuBois, [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
beginner question
I'm a new user, working through the tutorial provided in the MySQL documentation (chapter 9). I'm trying to load the data from the pet.txt file into the pet table using the specified command: mysql load data local infile pet.txt into table pet; I get the following: ERROR: File 'pet.txt' not found (Errcode: 2) I assumed that the text file was in the wrong directory, and reviewed the section describing the load data local command. Just to be safe, I put copies of the text file in several locations, including the menagerie directory (which should be the only one required), the bin directory, and the mysql directory. I still get the error message. What might I be doing wrong? Also, is there a FAQ that might hold the answer to newbie questions like this? Thanks for the help.
Re: beginner question
Not sure what the load data local infile thing is, but I always load text files from the command line like this (on Red Hat linux): % mysql -u some_guy -p some_db /full/path/to/file.txt I'm a new user, working through the tutorial provided in the MySQL documentation (chapter 9). I'm trying to load the data from the pet.txt file into the pet table using the specified command: mysql load data local infile pet.txt into table pet; I get the following: ERROR: File 'pet.txt' not found (Errcode: 2) I assumed that the text file was in the wrong directory, and reviewed the section describing the load data local command. Just to be safe, I put copies of the text file in several locations, including the menagerie directory (which should be the only one required), the bin directory, and the mysql directory. I still get the error message. What might I be doing wrong? Also, is there a FAQ that might hold the answer to newbie questions like this? Thanks for the 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
Basic Beginner Question
I am brand new to MySQL - my web host has set up a graphical tool for me to use, to try and administer it - it's called MysqlTool, Version 0.85. It's got a database there that my host created for me. Now I want to create an additional database, but don't see any kind of button for doing that. If I go inside the database he already created, I can find a prompt that says Execute SQL Statement or Generate Create Table Script, and a New Table - would either of these links allow my to create a new database? And if so, how would I do it? thanks j - 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: Basic Beginner Question
my experience with web hosts is that they usually give you one database per domain name hosted. So you likely CAN'T create another db that you can administer in this fashion. bummer, Dave Carter Chief Web Architect Accelerated Business Technologies, Inc. http://www.abti.cc 717.464.2970 -Original Message- From: Savin, Jill [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 24, 2001 12:27 PM To: [EMAIL PROTECTED] Subject: Basic Beginner Question I am brand new to MySQL - my web host has set up a graphical tool for me to use, to try and administer it - it's called MysqlTool, Version 0.85. It's got a database there that my host created for me. Now I want to create an additional database, but don't see any kind of button for doing that. If I go inside the database he already created, I can find a prompt that says Execute SQL Statement or Generate Create Table Script, and a New Table - would either of these links allow my to create a new database? And if so, how would I do it? thanks j - 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: Basic Beginner Question
Try clicking on Execute SQL Statement and entering CREATE DATABASE MyDatabaseName (without the quotes, and replacing MyDatabaseName with whatever you like). It should be as simple as that, but if it doesn't work, you may want to check with your host that you _are_ allowed to do it - some don't let you create multiple databases, you have to stash all your tables in one big database. Cheers Jon -Original Message- From: Savin, Jill [mailto:[EMAIL PROTECTED]] Sent: 24 May 2001 17:27 To: [EMAIL PROTECTED] Subject: Basic Beginner Question I am brand new to MySQL - my web host has set up a graphical tool for me to use, to try and administer it - it's called MysqlTool, Version 0.85. It's got a database there that my host created for me. Now I want to create an additional database, but don't see any kind of button for doing that. If I go inside the database he already created, I can find a prompt that says Execute SQL Statement or Generate Create Table Script, and a New Table - would either of these links allow my to create a new database? And if so, how would I do it? thanks j ** 'The information included in this Email is of a confidential nature and is intended only for the addressee. If you are not the intended addressee, any disclosure, copying or distribution by you is prohibited and may be unlawful. Disclosure to any party other than the addressee, whether inadvertent or otherwise is not intended to waive privilege or confidentiality' ** - 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
