Are Views Supported?
Hello, I was wondering when or will views be supported? -- Jake Johnson http://www.plutoid.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How can I select the column names?
I am trying to select the column names from a table to be displayed in a web page. Is there anyway to select column names without desc? Thanks, Jake Johnson [EMAIL PROTECTED] __ Plutoid - http://www.plutoid.com - Shop Plutoid for the best prices on Rims, Tires, and Wheel Packages. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqldump isn't working anymore
Hello, I have been backing up my db with the mysql dump command and it no longer works. This is an example of the command I was running... mysqldump -u dbuser -p -C mydb out.sql but returns this -- MySQL dump 8.21 -- -- Host: localhostDatabase: mydb - -- Server version 3.23.49-log Does anyone have any ideas why this doesn't work anymore? Regards, Jake Johnson [EMAIL PROTECTED] __ Plutoid - http://www.plutoid.com - Shop Plutoid for the best prices on Rims, Car Audio, and Performance Parts. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How do I show column names in php?
I am using php and mysql and I want to show the column names. How do I go about doing this without hardcoding the headers in my php? Regards, Jake Johnson [EMAIL PROTECTED] __ Plutoid - http://www.plutoid.com - Shop Plutoid for the best prices on Rims, Car Audio, and Performance Parts. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump isn't working anymore
Still the same problem, but I am still able to select from the DB using mysql. Regards, Jake Johnson [EMAIL PROTECTED] __ Plutoid - http://www.plutoid.com - Shop Plutoid for the best prices on Rims, Car Audio, and Performance Parts. On Wed, 23 Jul 2003, gerald_clark wrote: Did you try the -q option? Jake Johnson wrote: Hello, I have been backing up my db with the mysql dump command and it no longer works. This is an example of the command I was running... mysqldump -u dbuser -p -C mydb out.sql but returns this -- MySQL dump 8.21 -- -- Host: localhostDatabase: mydb - -- Server version 3.23.49-log Does anyone have any ideas why this doesn't work anymore? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Didn't find any fields in table 't_table'???
How do I fix this error? My tables have data. Regards, Jake Johnson [EMAIL PROTECTED] __ Plutoid - http://www.plutoid.com - Shop Plutoid for the best prices on Rims, Car Audio, and Performance Parts. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Didn't find any fields in table 't_table'???
Thanks for your help. I found that the permissions to the files were wrong and I had to restart mysql. I also needed to repair t_table to get things back to normal. I was scared I lost my data, but only for a second :-). Regards, Jake Johnson [EMAIL PROTECTED] __ Plutoid - http://www.plutoid.com - Shop Plutoid for the best prices on Rims, Car Audio, and Performance Parts. On Wed, 23 Jul 2003, gerald_clark wrote: Jake Johnson wrote: How do I fix this error? My tables have data. But they probably are not owned by mysql. Regards, Jake Johnson [EMAIL PROTECTED] __ Plutoid - http://www.plutoid.com - Shop Plutoid for the best prices on Rims, Car Audio, and Performance Parts. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT TOP
Be careful! Phil wanted the top 20 and the limit 20 will only return a random 20 records. Regards, Jake Johnson [EMAIL PROTECTED] __ Plutoid - http://www.plutoid.com - Shop Plutoid for the best prices on Rims, Car Audio, and Performance Parts. On Mon, 14 Jul 2003, Phil Bitis wrote: Yeah, put LIMIT 20 on the end. - Original Message - From: Jim McAtee [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, July 14, 2003 11:12 PM Subject: SELECT TOP What's wrong with the following query? The application used to use Access via ODBC, now running MySQL 3.23.xx, MyISAM table, accessed via MyODBC 3.51.01. // Return last 20 searches SELECT TOP 20 h.historyid, h.searchstring, h.serverid, s.shortname FROM history h INNER JOIN servers s ON h.serverid = s.serverid WHERE h.employeeid = 9 ORDER BY h.historyid DESC Works fine once I remove the TOP 20 from the query. If this isn't supported, is there an equivalent? Thanks, Jim -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT TOP
No kidding. I didn't know that. Thanks a lot! Regards, Jake Johnson [EMAIL PROTECTED] __ Plutoid - http://www.plutoid.com - Shop Plutoid for the best prices on Rims, Car Audio, and Performance Parts. On Mon, 14 Jul 2003, Gabriel Guzman wrote: On Mon, 2003-07-14 at 17:44, Jake Johnson wrote: Be careful! Phil wanted the top 20 and the limit 20 will only return a random 20 records. Actually, LIMIT x will return the first x rows of a query, not random records. In fact, you can even combine ORDER BY and LIMIT if you want to get the first x rows of some order. http://www.mysql.com/doc/en/SELECT.html http://www.mysql.com/doc/en/LIMIT_optimisation.html gabe. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: More tables or more joins
You don't want to have a separate table for each user. That would cause a maintenance nightmare. Try normalizing your data user table -- user_id cont_id user_name Contract lookup cont_id Cont_Name Contract Column Lookup -- col_id col_name Contract table user_id Cont_id col_id qty This should be a good start... Regards, Jake Johnson [EMAIL PROTECTED] __ Plutoid - http://www.plutoid.com - Shop Plutoid for the best prices on Rims, Car Audio, and Performance Parts. On Wed, 2 Jul 2003, Jackson Miller wrote: I am working on a program that is essentially a contact management tool for multiple users. There are currently about 200 users and will be over 1000 eventually. Each user may have between 10 and 500,000 contacts. Where it gets interesting is that each user needs to have the ability to control the fields that it is storing for it's contacts. I am considering giving each user it's own table for storing contacts. In this scenerio I would provide a means for editing the columns in the table. The other scenerio is to have a table to store field names, their type, and their default value and their account relationship. Then another table would store the contacts for all accounts with an account relationship. A final table would store relationships and values of contacts and the fields. I am mostly concerned with speed. My guess is that the first scenerio will be faster as long as all the queries only search the contacts for one account (i.e. one table). However I am a little concerned about having hundreds (and eventually thousands) of tables. Does anyone have experience with this kind of situation? Thanks, -Jackson -- 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: More tables or more joins
If you want to add another column name, just insert a new record into Contract Column Lookup -- col_id col_name Regards, Jake Johnson [EMAIL PROTECTED] __ Plutoid - http://www.plutoid.com - Shop Plutoid for the best prices on Rims, Car Audio, and Performance Parts. On Wed, 2 Jul 2003, Mike Hillyer wrote: Well, lets say that you suddenly remember that you need column X in the user table. In the normalized model you have to do one ALTER TABLE statement. In the design you have in place you need n ALTER TABLE statements where n = the number of users. It can also be easier to program against and manage normalized data. That being said, if your users have security concerns you need to maintain separate tables, as there are no views in MySQL (yet) and therefore you cannot prevent users from seeing each other's data in a normalized model. On another note, 2 million rows should not pose any performance issues, I can search tables with millions of rows and get back results quickly as long as I practice proper indexing (having fixed length rows also helps and is not hard to achieve). I would say that as long as contact privacy is not a concern, use the normalized approach for management ease. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Jackson Miller [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 02, 2003 10:47 AM To: Jake Johnson Cc: [EMAIL PROTECTED] Subject: Re: More tables or more joins I appreciate the idea of normalizing, but those tables wouldn't meet the spec. There would also have to be a column value table at the very least. Also, why would you have user_id and cont_id in both the user_table and the contract table. Also if you read my post you would see that I am talking about a minimum of 200 users each with an average of 20,000 contacts (with no overlap). This means that the contact table would have a minimum of 2,000,000 rows just to get started. The alternative would be to have 200 tables with 20,000 rows each. I understand that having this many tables is crazy, but I don't understand why it is not better. -Jackson On Wednesday 02 July 2003 11:49 am, Jake Johnson wrote: You don't want to have a separate table for each user. That would cause a maintenance nightmare. Try normalizing your data user table -- user_id cont_id user_name Contract lookup cont_id Cont_Name Contract Column Lookup -- col_id col_name Contract table user_id Cont_id col_id qty This should be a good start... Regards, Jake Johnson [EMAIL PROTECTED] __ Plutoid - http://www.plutoid.com - Shop Plutoid for the best prices on Rims, Car Audio, and Performance Parts. On Wed, 2 Jul 2003, Jackson Miller wrote: I am working on a program that is essentially a contact management tool for multiple users. There are currently about 200 users and will be over 1000 eventually. Each user may have between 10 and 500,000 contacts. Where it gets interesting is that each user needs to have the ability to control the fields that it is storing for it's contacts. I am considering giving each user it's own table for storing contacts. In this scenerio I would provide a means for editing the columns in the table. The other scenerio is to have a table to store field names, their type, and their default value and their account relationship. Then another table would store the contacts for all accounts with an account relationship. A final table would store relationships and values of contacts and the fields. I am mostly concerned with speed. My guess is that the first scenerio will be faster as long as all the queries only search the contacts for one account (i.e. one table). However I am a little concerned about having hundreds (and eventually thousands) of tables. Does anyone have experience with this kind of situation? Thanks, -Jackson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert problem
Have you tried single quotes (ie. registry_program='EA')? Regards, Jake Johnson [EMAIL PROTECTED] -- Plutoid - http://www.plutoid.com - Shop Plutoid for the best prices on Rims, Car Audio, and Performance Parts. On Mon, 30 Jun 2003, Sparky Kopetzky wrote: Greetings!! I'm trying this insert and it will not run in batch mode. I think the '' syntax is right but what else am I missing?? insert into registry set registry_program=EA, registry_key=BASE_PATH, registry_value=f:auction; Robin E. Kopetzky Black Mesa Computers/Internet Services www.blackmesa-isp.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sum() problems - I don't understand
What is the purpose of the join? I think I know what you are trying to do so make the first query a sub query and join the subquery to the carello table. Regards, Jake Johnson [EMAIL PROTECTED] -- Plutoid - http://www.plutoid.com - Shop Plutoid for the best prices on Rims, Car Audio, and Performance Parts. On Thu, 26 Jun 2003, gerald_clark wrote: Why would you expect they should have the same results? The second query contains a join and may have many times more rows in the result set. PaT! wrote: Dear All, I have these two queries which are supposed to return the same results for the fields with the same name (ex query 1 sum1 = query 2 sum1): The first query gives me back the correct result for the sum(): SELECT DATE_FORMAT(ordini.dataord,'%d-%m-%Y') date1, SUM(ordini.totale) sum1, SUM(ordini.quantita) sum2, ordini.cod, ordini.fromprev FROMordini WHERE ordini.numordine = 2302 GROUP BYordini.numordine; ++-+--++--+ | date1 | sum1| sum2 | cod| fromprev | ++-+--++--+ | 29-05-2003 | 436.498 | 10 | SC0001GP | 2666 | ++-+--++--+ this other one gives me problems with the sum(), wrong result. SELECT DATE_FORMAT(ordini.dataord,'%d-%m-%Y') date1, SUM(ordini.totale) sum1, SUM(ordini.quantita) sum2, ordini.cod, ordini.fromprev, carello.numprev, DATE_FORMAT(carello.dataordine,'%d-%m-%Y') date2 FROM ordini, carello WHEREordini.numordine = 2302 AND carello.numprev = ordini.fromprev GROUP BY ordini.numordine, carello.numprev; ++--+--+-+--+-+- ---+ | date1 | sum1 | sum2 | cod | fromprev | numprev | date2 | ++--+--+-+--+-+- ---+ | 29-05-2003 | 3491.984 | 80 | SC0001GP| 2666 |2666 | 28-05-2003 | ++--+--+-+--+-+- ---+ Help is required. I run Mysql 4.0.13 Thanks Patrizio -- 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: Best Pratices for mySQL Backups in Enterprise
Is this better than using mysqldump? Regards, Jake Johnson [EMAIL PROTECTED] -- Plutoid - http://www.plutoid.com - Shop Plutoid for the best prices on Rims, Car Audio, and Performance Parts. On Thu, 26 Jun 2003, SAQIB wrote: mysqlhotcopy does your locking for you. So running ./mysqlhostcopy dbase /path/to/backup/dir is perfectly safe while database operations (selct, insert, update etc) are being performed? --- Saqib Ali http://www.xml-dev.com -- 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: Best Pratices for mySQL Backups in Enterprise
Cool, Thanks a ton. I think I will stick with mysqldump for a while until my tables get too large. Currently my backups only take a few seconds. Regards, Jake Johnson [EMAIL PROTECTED] -- Plutoid - http://www.plutoid.com - Shop Plutoid for the best prices on Rims, Car Audio, and Performance Parts. On Thu, 26 Jun 2003, Ware Adams wrote: mysqldump creates text files containing insert statements that recreate a table and repopulate it with data. They are somewhat portable across database servers and human editable if necessary. They take up less space than the original table because they do not contain indices (only the statements that would create the indices). mysqldump is nice b/c it works for both InnoDB and MyISAM mysqlhotcopy makes a copy of the actual data files in your database. It is much faster than mysqldump, but the resulting backup is larger b/c it contains indices (unless you use the option to turn them off). Recovery is quicker as the tables exist in the backup directory in full MyISAM table form. With mysqldump you need to actually execute the dump files which can take a while for large tables. mysqlhotcopy does not work with InnoDB. --Ware Adams Jake Johnson wrote: Is this better than using mysqldump? mysqlhotcopy does your locking for you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: can you insert null?
Yes, that works for Teradata and Oracle as well. Regards, Jake Johnson [EMAIL PROTECTED] -- Plutoid - http://www.plutoid.com - Shop Plutoid for the best prices on Rims, Car Audio, and Performance Parts. On Thu, 26 Jun 2003 [EMAIL PROTECTED] wrote: hm? i just did a little test on mysql 3.23.56-nt, and had no problems setting a null value on a null column having a default value other than null. it turned to be null, rather than the default value. ··· yves at unclassified.de -Ursprüngliche Nachricht- Von: danchik [EMAIL PROTECTED] An: [EMAIL PROTECTED] Gesendet: Donnerstag, 26. Juni 2003 23:20 Betreff: Re: can you insert null? your statement will do just that, but make sure the field has no default value and allows NULL - Original Message - From: Bill2 [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, June 26, 2003 1:48 PM Subject: can you insert null? Hello all, Does anyone know if you can put the value(not the string) NULL? Something like UPDATE TABLE some_table SET field = NULL Thanks, Bill -- 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] -- 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: SQL question
Nice approach Bruce, but I too won't have any problems with your case because I am grouping by sku and stock in the sub-query. Regards, Jake Johnson [EMAIL PROTECTED] -- Plutoid - http://www.plutoid.com Shop Plutoid for the best prices on Rims and Car Audio Products On Thu, 19 Jun 2003, Bruce Feist wrote: Jake Johnson wrote: This is one quick way to get the newest records of a group if you are grouping by the sku and stock. select stock, sku, qty from table where concat(dt_tm,stock,sku) IN ( select concat(max(dt_tm), stock, sku) from table group by stock, sku ) Another approach (also assuming a current version of MySQL which supports subselects) is: SELECT stock, sku, qty FROM table t1 WHERE dt_tim = ( SELECT max(dt_tm) FROM table t2 WHERE t1.stock = t2.stock AND t1.sku = t2.sku ) This should be more reliable than Jake's solution, which will have problems with some data values. To illustrate the possible problem, run the above query on the following data: dt_tm stocksku qty dt1 A BB 1 dt1 ABB 2 Jake's query will return a single row instead of two rows. (Sorry, Jake, I don't mean to put you on the spot!) Bruce Feist I have a table with stock-status transactions like ... 2003-06-17 06:00 stockA SKU1 QTY 98 2003-06-16 06:10 stockA SKU1 QTY 101 2003-06-15 04:59 stockA SKU1 QTY 111 - the time for updating the transaction - each specific stock - each specific SKU / partnumber - quantity in stock at time of transaction The SQL issue - are there some way in SQL I can SELECT only latest transaction for each stock/SKU no matter date of update, or do I have to read thrue all transactions and select in the program ??? -- 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: SQL question
This is one quick way to get the newest records of a group if you are grouping by the sku and stock. select stock, sku, qty from table where concat(dt_tm,stock,sku) IN ( select concat(max(dt_tm), stock, sku) from table group by stock, sku ) Regards, Jake Johnson [EMAIL PROTECTED] -- Plutoid - http://www.plutoid.com Shop Plutoid for the best prices on Rims and Car Audio Products ) Hi, I have a SQL issue I cannot figure out, perhaps somebody can help / solve / input on this ... I have a table with stock-status transactions like ... 2003-06-17 06:00 stockA SKU1 QTY 98 2003-06-16 06:10 stockA SKU1 QTY 101 2003-06-15 04:59 stockA SKU1 QTY 111 - the time for updating the transaction - each specific stock - each specific SKU / partnumber - quantity in stock at time of transaction The SQL issue - are there some way in SQL I can SELECT only latest transaction for each stock/SKU no matter date of update, or do I have to read thrue all transactions and select in the program ??? Thanks and best regards Peter Rasmussen Copenhagen Denmark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Delete orphan records
Hello Greg, You are much better off using a not exists clause... delete from child c where not exists ( select 1 from parent p where p.id = c.id) Regards, Jake Johnson [EMAIL PROTECTED] -- Plutoid - http://www.plutoid.com Shop Plutoid for the best prices on Rims and Car Audio Products On Wed, 18 Jun 2003, Greg Klaus wrote: I am trying to delete some orphaned records in an old database on a website that I've recently taken over. Although the website is php driven, I am doing this manually in a mysql client. Mysql 3.23.54 Tables: Items: Items_ID Pictures: Picture_ID Items_ID I want to get rid of any entries in Pictures that are orphaned (No Items_ID in Items) Here is the query I'm trying to do, which I thought was correct, according to my surfing around google. DELETE FROM Pictures WHERE Picture_ID IN ( SELECT Pictures.Picture_ID FROM Pictures LEFT JOIN Items using (Items_ID) WHERE Items_ID IS NULL ) I also may have to do this in a 3 table scheme as well where the Cat_ID is gone and there are orphaned Items, which in turn means orphaned Pictures. Cats: Cat_ID Items: Items_ID Cat_ID Pictures: Picture_ID Items_ID Any help would be appreciated. Am I left to create a php script to do the cleaning for me or delete items individually? -- Greg Nec Mors, Nec Requies. Carpe Noctum! -- 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: MySQL query on 4 tables
How large is the status table? I would recommend trying to reduce the inital recordset of your largest table with a subquery and then joining the subquery to the other tables. Could you please show me the full query with the table sizes? Jake Johnson [EMAIL PROTECTED] http://www.plutoid.com On Mon, 16 Jun 2003, George Pitcher wrote: Hi, I'm having intermittent problems with a big query where I grab about 30 fields from 4 tables. I've snipped my query: Select ... Status_Msg from Extracts, Transacts, Publisher, Status where ( Transacts.Status=Status.ID and Transacts.Extract_ID=Extracts.ID and Extracts.Pub_ID=Publisher.ID and Transacts.ID=33237 It works sometimes, but not always. Can anyone suggest a more efficent way (that works) to do this? Cheers George in Oxford -- 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]
