innodb_data_file_path
Hi, I am using MySQL 5.0.85-community on Fedora 8. When i set innodb_data_home_dir = innodb_data_file_path = /dev/sda3:10Gnewraw;/dev/sda1:5Gnewraw and restart the server it gives an error Starting MySQL.Manager of pid-file quit without updating file.[FAILED] Please suggest how to resolve this issue. Thanks Jeetendra Ranjan
Render row without duplicates
Hi My fields something like hospital1,hospital2,hospital3,patientname, Exact table look like PatientName Hospital1Code Hospital2Code Hospital3Code Bharani 1234NULL NULL Kumar 56781234 NULL Senthil9632 56758524 John 1234 4567 8524 Can u tell me the query which return output like , HospitalID 1234 5678 9632 5675 8524 4567 8524 Constraint are 1. No Duplicate records, 2.One single column as Output Result , This query purpose is , i have around 1000 patients in my DB, Each patient may have one,two,three hospital code,that's y the field are hospital1,hosptial2,hospital3, i know , i can display all hospital code with unique , but i dont in the single column , with unique record, Can you tell me how to do this ? Thanks
Re: Render row without duplicates
Am 02.01.2010 13:43, schrieb bharani kumar: No Duplicate records, select hospital1code from *yourtable* grop by hospital1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Render row without duplicates
bharani kumar wrote: Hi My fields something like hospital1,hospital2,hospital3,patientname, [...] i know , i can display all hospital code with unique , but i dont in the single column , with unique record, Can you tell me how to do this ? Would it be possible to reconsider your table design? Instead of having the above, have something such as: Person(ID,Name) Hospital(ID,Name,Code) LinkTable(ID,PersonID,HospitalID) You'd then have something such as: Person(1,John) Hospital(800,Bart's London,1234) LinkTable(1000,1,800) You'd then be able to find all of the distinct hospital codes by doing select distinct code from hospital. Apologies if this isn't possible. Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Render row without duplicates
Benedikt Schackenberg wrote: Am 02.01.2010 13:43, schrieb bharani kumar: No Duplicate records, select hospital1code from *yourtable* grop by hospital1 This won't work as he's also looking for entities in the hospital2code and hospital3code fields to be returned in the same resultset, but as a single column. Essentially, hospital1code, hospital2code and hospital3code need to be merged to a single column, deduped, and then returned. Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Render row without duplicates
Your table structure makes the SQL a little inelegant, but I'd say this would give you what you seem to want: select Hospital1Code from tab where Hospital1Code is not null union select Hospital2Code from tab where Hospital2Code is not null union select Hospital3Code from tab where Hospital3Code is not null The union will eliminate duplicates. Maybe this would be better select * from ( select Hospital1Code from tab where Hospital1Code is not null union select Hospital2Code from tab where Hospital2Code is not null union select Hospital3Code from tab where Hospital3Code is not null ) A order by 1 Jim On Sat, Jan 2, 2010 at 6:43 AM, bharani kumar bharanikumariyer...@gmail.com wrote: Hi My fields something like hospital1,hospital2,hospital3,patientname, Exact table look like PatientName Hospital1Code Hospital2Code Hospital3Code Bharani 1234NULL NULL Kumar 56781234 NULL Senthil9632 56758524 John 1234 4567 8524 Can u tell me the query which return output like , HospitalID 1234 5678 9632 5675 8524 4567 8524 Constraint are 1. No Duplicate records, 2.One single column as Output Result , This query purpose is , i have around 1000 patients in my DB, Each patient may have one,two,three hospital code,that's y the field are hospital1,hosptial2,hospital3, i know , i can display all hospital code with unique , but i dont in the single column , with unique record, Can you tell me how to do this ? Thanks -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Re: How Set Up This Table
Hi Victor. I think that the first thing you need to consider is whether a product can be in more than one package, and second is whether a package can be in another package. Also, I don't know why you need to auto-generate in either case. It's pretty simple DDL. Case 1: product can only be in one package: 1. Add a Packages table with columns PackageID and PackageName and probably PackagePrice. 2. Add a PackageID column to the Products table and make it a foreign key referencing Packages. Case 2: product can be in multiple packages: 1. Same as above. 2. Create a ProductPackages table that contains PackageID and ProductID, both as foreign keys into Products and Packages. 3. Decide whether you want a compund PK on this new table, or you want instead an auto-increment column that would be the PK. (There are differences of opinion on this one, so I'm leaving it alone; I don't want to start a religious war :) hth, Arthur On Fri, Jan 1, 2010 at 5:09 AM, Victor Subervi victorsube...@gmail.comwrote: Hi; I have a table with products for a store to sell. I need to autogenerate from code a table or series of tables into which I can enter (and from which I can retrieve) the ID numbers of products which I am going to associate together and their package price. Product associations will vary, in that one association may have 2 products and another 20. What is the best way to MySQL this? TIA, Victor -- The Logos has come to bear http://logos.13gems.com/
Re: How Set Up This Table
On Sat, Jan 2, 2010 at 11:20 AM, Arthur Fuller fuller.art...@gmail.comwrote: Hi Victor. I think that the first thing you need to consider is whether a product can be in more than one package, and second is whether a package can be in another package. Also, I don't know why you need to auto-generate in either case. It's pretty simple DDL. Case 1: product can only be in one package: 1. Add a Packages table with columns PackageID and PackageName and probably PackagePrice. 2. Add a PackageID column to the Products table and make it a foreign key referencing Packages. Case 2: product can be in multiple packages: 1. Same as above. 2. Create a ProductPackages table that contains PackageID and ProductID, both as foreign keys into Products and Packages. 3. Decide whether you want a compund PK on this new table, or you want instead an auto-increment column that would be the PK. (There are differences of opinion on this one, so I'm leaving it alone; I don't want to start a religious war :) Thanks! That's reversing my thinking! I hadn't considered working the other direction. What is a PK and a compound PK? TIA, V
Re: How Set Up This Table
primary key is a candidate key to uniquely identify each row in a table. A unique key or primary key comprises a single column or set of columns. No two distinct rows in a table can have the same value (or combination of values) in those columns. Depending on its design, a table may have arbitrarily many unique keys but at most one primary key. a compound key is a key that consists of 2 or more attributes that uniquely identify an entity occurrence. On Sat, Jan 2, 2010 at 10:05 PM, Victor Subervi victorsube...@gmail.comwrote: On Sat, Jan 2, 2010 at 11:20 AM, Arthur Fuller fuller.art...@gmail.com wrote: Hi Victor. I think that the first thing you need to consider is whether a product can be in more than one package, and second is whether a package can be in another package. Also, I don't know why you need to auto-generate in either case. It's pretty simple DDL. Case 1: product can only be in one package: 1. Add a Packages table with columns PackageID and PackageName and probably PackagePrice. 2. Add a PackageID column to the Products table and make it a foreign key referencing Packages. Case 2: product can be in multiple packages: 1. Same as above. 2. Create a ProductPackages table that contains PackageID and ProductID, both as foreign keys into Products and Packages. 3. Decide whether you want a compund PK on this new table, or you want instead an auto-increment column that would be the PK. (There are differences of opinion on this one, so I'm leaving it alone; I don't want to start a religious war :) Thanks! That's reversing my thinking! I hadn't considered working the other direction. What is a PK and a compound PK? TIA, V -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: How Set Up This Table
On Sat, Jan 2, 2010 at 12:03 PM, prabhat kumar aim.prab...@gmail.comwrote: primary key Oh! PK is primary key! a compound key is a key that consists of 2 or more attributes that uniquely identify an entity occurrence. Thanks. V
Re: How Set Up This Table
On Sat, Jan 2, 2010 at 11:20 AM, Arthur Fuller fuller.art...@gmail.comwrote: Hi Victor. I think that the first thing you need to consider is whether a product can be in more than one package, and second is whether a package can be in another package. Also, I don't know why you need to auto-generate in either case. It's pretty simple DDL. Case 1: product can only be in one package: 1. Add a Packages table with columns PackageID and PackageName and probably PackagePrice. 2. Add a PackageID column to the Products table and make it a foreign key referencing Packages. Case 2: product can be in multiple packages: 1. Same as above. 2. Create a ProductPackages table that contains PackageID and ProductID, both as foreign keys into Products and Packages. 3. Decide whether you want a compund PK on this new table, or you want instead an auto-increment column that would be the PK. (There are differences of opinion on this one, so I'm leaving it alone; I don't want to start a religious war :) If I'm understanding this correctly, if I want products to be addable to multiple packages, then I want to create both a Packages table and a ProductPackages table. However, it would appear I don't need to add a PackageID column to the Products table if I'm going to create the ProductPackages table. Is that correct? TIA, V
Re: Render row without duplicates
Hi First i want to thanks to my mysql groups, Sorry , just now i find time to see mail, Am not sure, but i guess this union solves my problem, But let me check it, give me a time..plz On Sat, Jan 2, 2010 at 7:24 PM, Jim Lyons jlyons4...@gmail.com wrote: Your table structure makes the SQL a little inelegant, but I'd say this would give you what you seem to want: select Hospital1Code from tab where Hospital1Code is not null union select Hospital2Code from tab where Hospital2Code is not null union select Hospital3Code from tab where Hospital3Code is not null The union will eliminate duplicates. Maybe this would be better select * from ( select Hospital1Code from tab where Hospital1Code is not null union select Hospital2Code from tab where Hospital2Code is not null union select Hospital3Code from tab where Hospital3Code is not null ) A order by 1 Jim On Sat, Jan 2, 2010 at 6:43 AM, bharani kumar bharanikumariyer...@gmail.com wrote: Hi My fields something like hospital1,hospital2,hospital3,patientname, Exact table look like PatientName Hospital1Code Hospital2Code Hospital3Code Bharani 1234NULL NULL Kumar 56781234 NULL Senthil9632 56758524 John 1234 4567 8524 Can u tell me the query which return output like , HospitalID 1234 5678 9632 5675 8524 4567 8524 Constraint are 1. No Duplicate records, 2.One single column as Output Result , This query purpose is , i have around 1000 patients in my DB, Each patient may have one,two,three hospital code,that's y the field are hospital1,hosptial2,hospital3, i know , i can display all hospital code with unique , but i dont in the single column , with unique record, Can you tell me how to do this ? Thanks -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- Regards B.S.Bharanikumar http://php-mysql-jquery.blogspot.com/
Importing table contents
I have 2 databases, different domains. Both have a table named 'tips'... both have different contents in the table. Using phpMyAdmin for GUI. I want to export databaseA tips as sql (done) then import content into databaseB tips. But when I run that operation, the databaseB says that there is already a table named tips in databaseB. Yep, know that... I want to bring in the contents...not make a new table. Any help, most appreciated Thanks as always -- Patrice Olivier-Wilson 888-385-7217 http://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: Importing table contents
Patrice Olivier-Wilson wrote: I have 2 databases, different domains. Both have a table named 'tips'... both have different contents in the table. Using phpMyAdmin for GUI. I want to export databaseA tips as sql (done) then import content into databaseB tips. But when I run that operation, the databaseB says that there is already a table named tips in databaseB. Yep, know that... I want to bring in the contents...not make a new table. Any help, most appreciated When you export, PHPMyAdmin has the option to add drop table. This will drop the existing table structure and create a new one as it was when it was exported. Is this what you're after? Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Importing table contents
Gary Smith wrote: Patrice Olivier-Wilson wrote: I have 2 databases, different domains. Both have a table named 'tips'... both have different contents in the table. Using phpMyAdmin for GUI. I want to export databaseA tips as sql (done) then import content into databaseB tips. But when I run that operation, the databaseB says that there is already a table named tips in databaseB. Yep, know that... I want to bring in the contents...not make a new table. Any help, most appreciated When you export, PHPMyAdmin has the option to add drop table. This will drop the existing table structure and create a new one as it was when it was exported. Is this what you're after? Gary I have data I need to keep in both db just trying to merge. -- Patrice Olivier-Wilson 888-385-7217 http://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: Importing table contents
Patrice Olivier-Wilson wrote: I have data I need to keep in both db just trying to merge. There's two ways around this: First is to not export the structure (uncheck structure). The second is to export with if not exists. This should (IIRC) do a create table if not exists, so it'll do what you're wanting to do. Do you have any primary keys/auto increment columns that are going to overlap or anything like that? Cheers, Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Importing table contents
Gary Smith wrote: Patrice Olivier-Wilson wrote: I have data I need to keep in both db just trying to merge. There's two ways around this: First is to not export the structure (uncheck structure). The second is to export with if not exists. This should (IIRC) do a create table if not exists, so it'll do what you're wanting to do. Do you have any primary keys/auto increment columns that are going to overlap or anything like that? Cheers, Gary Gave it a try got this: MySQL said: #1062 - Duplicate entry '1' for key 1 -- Patrice Olivier-Wilson 888-385-7217 http://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
Getting the sum() for a column from a joined table
Hi, I have 3 tables, `agents`, `clients` and `sales` and I want to select a single agent from the `agents` table, and 2 more columns that contain the number of clients for the selected user (from the `clients` table) and the sum of the sales for the selected user (from the `sales` table). Is it possible to do this selection in a single query? I have tried using: select agents.id, agents.name, count(clients.name), sum(sales.value) from agents left join clients on agents.id=clients.agent, left join sales on agents.id=sales.agent where agent.id=100 group by clients.agent, sales.agent; But it doesn't give good results. The sum of sales is bigger than it should be... kind of multiplied with the number of clients that match, like if there were no group by columns specified. I have tried to group by more other columns like clients.id and sales.id or agents.id, but with no good results. Thank you for your help. Octavian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Importing table contents
Patrice Olivier-Wilson wrote: Gave it a try got this: MySQL said: #1062 - Duplicate entry '1' for key 1 Yeah, that's what I was saying about in my previous mail. It looks like you've got a primary key on one of your columns, and you're attempting to insert data into it with a duplicate primary key (ie what the error message says). The easiest way to get around this one would be to write a query that pulls all of the columns apart from the primary key, and then replace that field with '' or somesuch. For instance, let's say you've got a schema of the following: table1(primarykey,column2,column3,column4,column5) primarykey is obviously a primary key. You'd do something along the lines of select '',column2,column3,column4,column5 from table1; Then export that resultset to an SQL file. Anyone else aware of an easier way to do this? I've got into some bad habits over the years, but I'm not aware of another way to do what Patrice is trying to do. Cheers, Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Importing table contents
Gary Smith wrote: Patrice Olivier-Wilson wrote: Gave it a try got this: MySQL said: #1062 - Duplicate entry '1' for key 1 Yeah, that's what I was saying about in my previous mail. It looks like you've got a primary key on one of your columns, and you're attempting to insert data into it with a duplicate primary key (ie what the error message says). The easiest way to get around this one would be to write a query that pulls all of the columns apart from the primary key, and then replace that field with '' or somesuch. For instance, let's say you've got a schema of the following: table1(primarykey,column2,column3,column4,column5) primarykey is obviously a primary key. You'd do something along the lines of select '',column2,column3,column4,column5 from table1; Then export that resultset to an SQL file. Anyone else aware of an easier way to do this? I've got into some bad habits over the years, but I'm not aware of another way to do what Patrice is trying to do. Cheers, Gary If I export both db tables into csv, combine and then import back, that should do it, methinks... just create a new table called tips2, merge the 2 into one... -- Patrice Olivier-Wilson 888-385-7217 http://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: Getting the sum() for a column from a joined table
hi , Octavian you can try this SQL. = select agents.id, agents.name, (select count(*) from clients where agent=agents.id), (select sum(value) from sales where agent=agents.id) from agents where agent.id=100 = best regards ACMAIN To: mysql@lists.mysql.com From: orasn...@gmail.com Subject: Getting the sum() for a column from a joined table Date: Sun, 3 Jan 2010 01:35:49 +0200 Hi, I have 3 tables, `agents`, `clients` and `sales` and I want to select a single agent from the `agents` table, and 2 more columns that contain the number of clients for the selected user (from the `clients` table) and the sum of the sales for the selected user (from the `sales` table). Is it possible to do this selection in a single query? I have tried using: select agents.id, agents.name, count(clients.name), sum(sales.value) from agents left join clients on agents.id=clients.agent, left join sales on agents.id=sales.agent where agent.id=100 group by clients.agent, sales.agent; But it doesn't give good results. The sum of sales is bigger than it should be... kind of multiplied with the number of clients that match, like if there were no group by columns specified. I have tried to group by more other columns like clients.id and sales.id or agents.id, but with no good results. Thank you for your help. Octavian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=liuy...@live.com _ Keep your friends updated―even when you’re not signed in. http://www.microsoft.com/middleeast/windows/windowslive/see-it-in-action/social-network-basics.aspx?ocid=PID23461::T:WLMTAGL:ON:WL:en-xm:SI_SB_5:092010
Instll php on Window 2003 64Bit questions
Dear All, If the OS is Windows 2003 64Bit (IIS)... So, which php package must download and how to config it for running with IIS ? Due to I don't quite the online manual: http://www.php.net/manual/en/install.windows.iis.php Which installation mode is suitable of it ? Thanks ! Edward. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: 32bit ( php + mysql server ) on 64bit Windows 2003 Server performance
Jerry Schwartz wrote: From: Edward S.P. Leong [mailto:edward...@ita.org.mo] Sent: Tuesday, December 29, 2009 10:35 AM To: Jerry Schwartz Cc: mysql@lists.mysql.com Subject: Re: 32bit ( php + mysql server ) on 64bit Windows 2003 Server performance Jerry Schwartz wrote: Dear Jerry, Sorry, did you suggest to use 5.x / 5.y ( NO 6.x ) ? [JS] No, I am not saying that. The system I inherited was running 5.x, and I have not had the time nor urgent need to move upwards. There are some incompatibilities that I would have to deal with. For example, although short tags are deprecated in 5.x they are not allowed in 6.x; and the construct ?= $something ? is not allowed. I would have to go through all of our source code with a fine-tooth comb to fix these. We do run an international shop, with heavy use of UTF-8, so the internationalization features of PHP 6.x would be very useful. If you have the time to fix up your PHP code, then I would suggest that you go to 6.x. BTW, would you mind to tell me which newer version of php and mysql are you running now ? Due to I want to download a version of them and do the test under win2003... Then, I want to tell(reply) you the result(stable)! [JS] PHP 5.2.10, Apache 2.2.14 (Win32), MySQL 5.1.36-community Dear Jerry, Do you know which version of newer version of php which support the short tags ( allowed ) ? Or would you mind to tell me how to fix the problem of php source code and connect with mysql db ? [JS] I believe that all 5.x versions of PHP support short tags, but there is a setting in php.ini that you have to set. As for your second question, I don� know what you mean. With PHP 5.2.10, I�e used the mysql, mysqli, and PDO interfaces in both procedural and object-oriented forms (just to stretch my mind). I think that anything in 4.x would be using the mysql interface, although I� not positive. In any event, it probably is upward-compatible. You�l want a decent text editor that can search for strings in multiple files, preferably one that can use regular expressions. You really need to check the PHP documentation. Dear Jerry, Sorry... I just visit www.php.net... BUT I don't quite understanding the online manual: http://www.php.net/manual/en/install.windows.iis.php 1, Which installation mode is suitable of it ? 2, I want to know which tool must we download for install php and related program for IIS of Win2k3 OS ? Many Thanks ! Edward. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
