Foreign Key Error
Hi; I have this command: create table if not exists categoriesRelationships (ID integer auto_increment primary key, Store varchar(60), Parent integer not null, foreign key (Parent) references categories (ID), Child integer not null, foreign key (Child) references categories (ID)) engine=innodb; show innodb status prints out this: LATEST FOREIGN KEY ERROR 110214 15:03:43 Error in foreign key constraint of table test/categoriesRelationships: foreign key (Parent) references categories (ID), Child integer not null, foreign key (Child) references categories (ID)) engine=innodb: Cannot find an index in the referenced table where the referenced columns appear as the first columns, or column types in the table and the referenced table do not match for constraint. Note that the internal storage type of ENUM and SET changed in tables created with = InnoDB-4.1.12, and such columns in old tables cannot be referenced by such columns in new tables. See http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html for correct foreign key definition. mysql describe categories; +--+-+--+-+-++ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-++ | ID | int(3) unsigned | NO | PRI | NULL| auto_increment | | Store| varchar(60) | YES | | NULL|| | Category | varchar(40) | YES | | NULL|| | Parent | varchar(40) | YES | | NULL|| +--+-+--+-+-++ 4 rows in set (0.00 sec) Please advise. TIA, Victor
Re: Foreign Key Error
Thank you! V On Mon, Feb 14, 2011 at 9:08 PM, Gavin Towey gto...@ffn.com wrote: or column types in the table and the referenced table do not match for constraint The columns Parent and Child are signed integers and ID is unsigned. Regards, Gavin Towey -Original Message- From: Victor Subervi [mailto:victorsube...@gmail.com] Sent: Monday, February 14, 2011 3:09 PM To: mysql@lists.mysql.com Subject: Foreign Key Error Hi; I have this command: create table if not exists categoriesRelationships (ID integer auto_increment primary key, Store varchar(60), Parent integer not null, foreign key (Parent) references categories (ID), Child integer not null, foreign key (Child) references categories (ID)) engine=innodb; show innodb status prints out this: LATEST FOREIGN KEY ERROR 110214 15:03:43 Error in foreign key constraint of table test/categoriesRelationships: foreign key (Parent) references categories (ID), Child integer not null, foreign key (Child) references categories (ID)) engine=innodb: Cannot find an index in the referenced table where the referenced columns appear as the first columns, or column types in the table and the referenced table do not match for constraint. Note that the internal storage type of ENUM and SET changed in tables created with = InnoDB-4.1.12, and such columns in old tables cannot be referenced by such columns in new tables. See http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html for correct foreign key definition. mysql describe categories; +--+-+--+-+-++ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-++ | ID | int(3) unsigned | NO | PRI | NULL| auto_increment | | Store| varchar(60) | YES | | NULL|| | Category | varchar(40) | YES | | NULL|| | Parent | varchar(40) | YES | | NULL|| +--+-+--+-+-++ 4 rows in set (0.00 sec) Please advise. TIA, Victor IMPORTANT: This email message is intended only for the use of the individual to whom, or entity to which, it is addressed and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are NOT the intended recipient, you are hereby notified that any use, dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please reply to the sender immediately and permanently delete this email. Thank you.
Too Strange for Words
Hi; Can someone please explain to me why Passengers.weight comes up null for the last value sometimes, and other times it comes up with the value 155?? mysql select p.id, c.id, p.name, p.weight, m.amount, p.round_trip, c.first_name, c.middle_name, c.last_name, c.suffix, c.sex, p.confirmation, m.type, m.payment, p.late_fee, p.late_fee_paid, m.discount from Passengers p join Customers c join Money m on m.foreign_id=p.id and m.customer_id=c.idwhere m.type=flight fee and p.flight_id=1; +++--+++++-+---++--+--++-+--+---+--+ | id | id | name | weight | amount | round_trip | first_name | middle_name | last_name | suffix | sex | confirmation | type | payment | late_fee | late_fee_paid | discount | +++--+++++-+---++--+--++-+--+---+--+ | 1 | 1 | NULL | NULL | 70.00 | 0 | Adolph | NULL| Hitler| NULL | Male | 123456 | flight fee | CC | no | n/a |0 | | 2 | 1 | NULL | NULL | 70.00 | 0 | Adolph | NULL| Hitler| NULL | Male | 987987987| flight fee | cash| no | n/a |0 | | 3 | 3 | NULL | NULL | 70.00 | 0 | Me | NULL| Too | NULL | Male | 123456 | flight fee | CC | no | n/a | 20 | +++--+++++-+---++--+--++-+--+---+--+ 3 rows in set (0.00 sec) mysql select * from Passengers; ++---+-+--+--++---++--+--+---+ | id | flight_id | customer_id | name | sex | weight | price | round_trip | confirmation | late_fee | late_fee_paid | ++---+-+--+--++---++--+--+---+ | 1 | 1 | 1 | NULL | Male | NULL | 0.00 | 0 | 123456 | no | n/a | | 2 | 1 | 1 | NULL | Male | NULL | 0.00 | 0 | 987987987| no | n/a | | 3 | 1 | 2 | NULL | Male | NULL | 0.00 | 0 | 123456 | no | n/a | | 4 | 1 | 3 | NULL | Male |155 | 70.00 | 0 | 123456 | no | n/a | ++---+-+--+--++---++--+--+---+ 4 rows in set (0.00 sec) mysql select p.weight from Passengers p; ++ | weight | ++ | NULL | | NULL | | NULL | |155 | ++ 4 rows in set (0.00 sec) TIA, Victor
Complex Select Query
Hi; I have the following query: select * from spreadsheets s join products p on p.Item=s.Item join categories c on p.Category=c.ID where s.Client=%s order by p.category, c.parent; mysql describe products; +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | ID | int(4) | NO | PRI | NULL| auto_increment | | Category| int(3) | YES | | NULL|| | Item| varchar(20) | YES | UNI | NULL|| | Description | varchar(255) | YES | | NULL|| | UOM | varchar(20) | YES | | NULL|| | Cost| float(7,2) | YES | | NULL|| +-+--+--+-+-++ 6 rows in set (0.00 sec) mysql describe categories; +--+-+--+-+-++ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-++ | ID | int(3) | NO | PRI | NULL| auto_increment | | Category | varchar(20) | YES | UNI | NULL|| | Parent | varchar(20) | YES | | NULL|| +--+-+--+-+-++ What I'm trying to accomplish is to order the results such that after stacking the data for all results for a certain category, that the next results to be stacked should be those whose parent = the former category, then move on to the next category, etc. How do I do this? TIA, Victor
Re: Complex Select Query
On Tue, Aug 24, 2010 at 1:43 PM, Peter Brawley peter.braw...@earthlink.netwrote: What I'm trying to accomplish is to order the results such that after stacking the data for all results for a certain category, that the next results to be stacked should be those whose parent = the former category, then move on to the next category, etc. How do I do this? It's a tree. See http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html and http://www.artfulsoftware.com/infotree/treequeryperformance.pdf. How do I cut down the tree? That stuff is *way* too complex for my needs. I just thought I'd make my presentation of data a little cleaner but frankly it ain't worth going through all that learning and experimentation to do it. If there isn't another way I'll just forget about it. Any other suggestions would be nice. TIA. Victor - -Original Message- From: Victor Subervi victorsube...@gmail.com Sent: Aug 24, 2010 1:14 PM To: mysql@lists.mysql.com Subject: Complex Select Query Hi; I have the following query: select * from spreadsheets s join products p on p.Item=s.Item join categories c on p.Category=c.ID where s.Client=%s order by p.category, c.parent; mysql describe products; +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | ID | int(4) | NO | PRI | NULL| auto_increment | | Category| int(3) | YES | | NULL|| | Item| varchar(20) | YES | UNI | NULL|| | Description | varchar(255) | YES | | NULL|| | UOM | varchar(20) | YES | | NULL|| | Cost| float(7,2) | YES | | NULL|| +-+--+--+-+-++ 6 rows in set (0.00 sec) mysql describe categories; +--+-+--+-+-++ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-++ | ID | int(3) | NO | PRI | NULL| auto_increment | | Category | varchar(20) | YES | UNI | NULL|| | Parent | varchar(20) | YES | | NULL|| +--+-+--+-+-++ What I'm trying to accomplish is to order the results such that after stacking the data for all results for a certain category, that the next results to be stacked should be those whose parent = the former category, then move on to the next category, etc. How do I do this? TIA, Victor
Join Problem
Hi;
I have this code:
select f.id from Flights f join Planes p where f.plane_id=p.id and
p.in_service=1
mysql describe Flights;
+-+---+--+-+-++
| Field | Type | Null | Key | Default | Extra |
+-+---+--+-+-++
| id | int(11) | NO | PRI | NULL| auto_increment |
| plane_id| int(11) | NO | MUL | NULL||
| pilot_id| int(11) | NO | MUL | NULL||
| flight_date | date | NO | | NULL||
| departure | time | NO | | NULL||
| arrival | time | NO | | NULL||
| origination | enum('STT','STX') | YES | | NULL||
| destination | enum('STT','STX') | YES | | NULL||
| price | float(6,2)| NO | | NULL||
+-+---+--+-+-++
mysql describe Planes;
+--+-+--+-+-++
| Field| Type| Null | Key | Default | Extra |
+--+-+--+-+-++
| id | int(11) | NO | PRI | NULL| auto_increment |
| name | varchar(20) | NO | | NULL||
| in_service | tinyint(1) | NO | | 1 ||
| capacity | tinyint(2) | NO | | NULL||
| total_weight | int(6) | NO | | NULL||
+--+-+--+-+-++
My goal is to exclude results in which in_service !=1; however, the filter
isn't working. Please advise.
TIA,
Victor
Why Does This Drop Fail?
Hi; mysql alter table personalData drop foreign key Store; ERROR 1025 (HY000): Error on rename of './test/personalData' to './test/#sql2-14ce-a61' (errno: 152) mysql describe personalData; +---+--+--+-+++ | Field | Type | Null | Key | Default| Extra | +---+--+--+-+++ | ID| int(10) unsigned | NO | PRI | NULL | auto_increment | | Store | varchar(40) | NO | MUL | NULL || | User | varchar(50) | NO | MUL | NULL || | FirstName | varchar(100) | NO | | NULL || | LastName | varchar(100) | NO | | NULL || | Phone | varchar(13) | YES | | NULL || | Cell | varchar(13) | YES | | NULL || | Fax | varchar(13) | YES | | NULL || | AddressID | int(11) | YES | | NULL || | ShippingAddressID | int(11) | YES | | NULL || | DOB | date | YES | | 2000-01-01 || | Email | varchar(100) | NO | | NULL || | PW| varchar(12) | NO | | NULL || +---+--+--+-+++ 13 rows in set (0.00 sec) How drop it? TIA, Victor
Re: Why Does This Drop Fail?
On Thu, Jul 22, 2010 at 8:47 AM, John Daisley daisleyj...@googlemail.comwrote: Most likely a foreign key constraint would be violated if the table were dropped. Check those index definitions on `Store` and `User` columns. But I don't want to drop the table, I want to drop the foreign key on Store. How? TIA, V Regards John Daisley Microsoft SQL Server 2005/2008 Database Administrator Certified MySQL 5 Database Administrator Certified MySQL 5 Developer Cognos BI Developer Telephone: +44 (0)7918 621621 Email: john.dais...@butterflysystems.co.uk On 22 July 2010 14:02, Victor Subervi victorsube...@gmail.com wrote: Hi; mysql alter table personalData drop foreign key Store; ERROR 1025 (HY000): Error on rename of './test/personalData' to './test/#sql2-14ce-a61' (errno: 152) mysql describe personalData; +---+--+--+-+++ | Field | Type | Null | Key | Default| Extra | +---+--+--+-+++ | ID| int(10) unsigned | NO | PRI | NULL | auto_increment | | Store | varchar(40) | NO | MUL | NULL || | User | varchar(50) | NO | MUL | NULL || | FirstName | varchar(100) | NO | | NULL || | LastName | varchar(100) | NO | | NULL || | Phone | varchar(13) | YES | | NULL || | Cell | varchar(13) | YES | | NULL || | Fax | varchar(13) | YES | | NULL || | AddressID | int(11) | YES | | NULL || | ShippingAddressID | int(11) | YES | | NULL || | DOB | date | YES | | 2000-01-01 || | Email | varchar(100) | NO | | NULL || | PW| varchar(12) | NO | | NULL || +---+--+--+-+++ 13 rows in set (0.00 sec) How drop it? TIA, Victor -- John Daisley Certified MySQL 5 Database Administrator Certified MySQL 5 Developer Cognos BI Developer Telephone: +44 (0)7918 621621 Email: john.dais...@butterflysystems.co.uk
Re: Why Does This Drop Fail?
On Thu, Jul 22, 2010 at 9:09 AM, John Daisley daisleyj...@googlemail.comwrote: Sorry, my bad! Must learn to read the whole message!! This can be caused because when a foreign key is created mysql adds an index key to the column in addition to the foreign key. Why I'm not sure, but I'm guessing its for performance. To drop this foreign key First do this to get the index names SHOW CREATE TABLE `table_name` Which will give an output something like this | table_name | CREATE TABLE `table_name` ( `id` int(10) unsigned NOT NULL auto_increment, `idx` int(5) unsigned NOT NULL, KEY `key_column` (`idx`), CONSTRAINT `table_name_ibfk_1` FOREIGN KEY (`idx`) REFERENCES `second_table` (`id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | So, first you remove the key: ALTER TABLE table_name DROP KEY `idx`; then the foreign key: ALTER TABLE table_name DROP FOREIGN KEY `table_name_ibfk_1`; Then that should do it! Thank you! V
Creating a Data Dictionary
Hi;
Perhaps I have a conflict of terms here, but my googling mysql data
dictionary turned up material that didn't seem to correspond with my
problem. In python I can create dictionaries:
my_dict = {'1': 'one', '2': 'two'}
Now, I would like to create the equivalent of an enum in which I could
utilize data like that. Of course, I could lump the whole key-value pairs
into one data and create an enum like that, then parse them later. I'm just
wondering if there's a more elegant way to do this.
TIA,
Victor
Two Primary Keys
Hi; I have the following: create table pics ( picture_id int auto_increment primary key, product_sku int not null primary key, picture_num int not null, picture_desc varchar(100), picture_data longblob ); which doesn't work I need to auto_increment and declare primary key on two fields. How do? TIA, Victor
Re: Two Primary Keys
2010/6/29 João Cândido de Souza Neto j...@consultorweb.cnt.br As far as I know, if you have an auto_increment primary key, you cant have any other field in its primary key. Makes sense. Actually, I was just copying what someone else gave me and adding the auto_increment, then I got to wondering, what is the purpose of having two primary keys? TIA, V
Re: Two Primary Keys
2010/6/29 João Cândido de Souza Neto j...@consultorweb.cnt.br I think the best, or may be the right way is to use picture_id as primary key and a unique index to product_sku. Yes, sounds good. So the purpose, then, is to speed lookups on fields commonly accessed. I'd forgotten that. Thanks, V
Foreign Key Problem
Hi; mysql alter table Flights type=InnoDB; Query OK, 1 row affected, 1 warning (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql alter table Flights add pilot_id int not null; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql alter table Flights add foreign key (pilot_id) references Pilots (id); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`seaflight/#sql-4d89_3ac`, CONSTRAINT `#sql-4d89_3ac_ibfk_1` FOREIGN KEY (`pilot_id`) REFERENCES `Pilots` (`id`)) mysql alter table Pilots type=InnoDB; Query OK, 1 row affected, 1 warning (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql alter table Flights add foreign key (pilot_id) references Pilots (id); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`seaflight/#sql-4d89_3ac`, CONSTRAINT `#sql-4d89_3ac_ibfk_1` FOREIGN KEY (`pilot_id`) REFERENCES `Pilots` (`id`)) mysql describe Pilots; +-+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-++ | id | int(11) | NO | PRI | NULL| auto_increment | | first_name | varchar(20) | NO | | NULL|| | middle_name | varchar(20) | YES | | NULL|| | last_name | varchar(20) | NO | | NULL|| | weight | int(11) | NO | | NULL|| +-+-+--+-+-++ 5 rows in set (0.00 sec) Please advise how to alter Flights to take the foreign key. TIA, Victor
Re: Foreign Key Problem
Problem solved. I tried everything that *should* have worked and didn't. Then I just wiped the test database and started with everything *fixed* (all engine=innodb, all keys of same type, etc.) and it all worked. V
Re: Foreign Key Problem
On Tue, Jun 22, 2010 at 11:53 AM, jayabharath jbhara...@gmail.com wrote: Hi Victor, The actual problem is with the key field. Flights.pilot_id is set to INT NOT NULL and you had specified Pilots.id to INT NULL. You have to change both the columns to NULL or else NOT NULL to avoid the error. Thanks. V
Re: Foreign Key Problem
This is just for the sake of future googlers of this thread. The correct
mysql command is:
ursor.execute('create table if not exists Passengers (id int(11)
auto_increment primary key, flights_id int(11) not null, customer_id int(11)
not null, foreign key (flights_id) references Flights (id), foreign key
(customer_id) references Customers (id), name varchar(40), weight int, price
float(6,2)) engine=InnoDB;')
beno
Another Foreign Key Problem
Hi;
When I try to execute this code from my Python script, I get this error:
Traceback (most recent call last):
File
/var/www/html/creative.vi/clients/sea-flight/reservations/create_edit_bags3.py,
line 38, in ?
create_edit_bags3()
File
/var/www/html/creative.vi/clients/sea-flight/reservations/create_edit_bags3.py,
line 32, in create_edit_bags3
cursor.execute('insert into Baggage values (Null, %s, %s, %s,
%s)', (flight_id, customer_id, weight, ticket_no))
File /usr/lib64/python2.4/site-packages/MySQLdb/cursors.py, line
163, in execute
self.errorhandler(self, exc, value)
File /usr/lib64/python2.4/site-packages/MySQLdb/connections.py,
line 35, in defaulterrorhandler
raise errorclass, errorvalue
OperationalError: (1452, 'Cannot add or update a child row: a foreign
key constraint fails (`seaflight/Baggage`, CONSTRAINT `Baggage_ibfk_2`
FOREIGN KEY (`customer_id`) REFERENCES `Customers` (`id`))')
However, when I try from the MySQL prompt after duly printing it out from
the code, it works. Why?
TIA,
Victor
Re: Another Foreign Key Problem
I'm canceling this thread. It belongs in the Python list. Sorry!
V
On Fri, May 21, 2010 at 1:24 PM, Victor Subervi victorsube...@gmail.comwrote:
Hi;
When I try to execute this code from my Python script, I get this error:
Traceback (most recent call last):
File
/var/www/html/creative.vi/clients/sea-flight/reservations/create_edit_bags3.py,
line 38, in ?
create_edit_bags3()
File
/var/www/html/creative.vi/clients/sea-flight/reservations/create_edit_bags3.py,
line 32, in create_edit_bags3
cursor.execute('insert into Baggage values (Null, %s, %s, %s, %s)',
(flight_id, customer_id, weight, ticket_no))
File /usr/lib64/python2.4/site-packages/MySQLdb/cursors.py, line 163, in
execute
self.errorhandler(self, exc, value)
File /usr/lib64/python2.4/site-packages/MySQLdb/connections.py, line 35,
in defaulterrorhandler
raise errorclass, errorvalue
OperationalError: (1452, 'Cannot add or update a child row: a foreign key
constraint fails (`seaflight/Baggage`, CONSTRAINT `Baggage_ibfk_2` FOREIGN
KEY (`customer_id`) REFERENCES `Customers` (`id`))')
However, when I try from the MySQL prompt after duly printing it out from
the code, it works. Why?
TIA,
Victor
Re: Foreign Key Problem
On Wed, May 19, 2010 at 12:02 PM, Shawn Green shawn.l.gr...@oracle.comwrote: Victor Subervi wrote: On Wed, May 19, 2010 at 10:59 AM, Shawn Green shawn.l.gr...@oracle.com wrote: Shawn Green wrote: look again closely at your FK definitions. The pattern should be FOREIGN KEY (child_table_column) REFERENCES parent_table(parent_table_column) Yours appears to be something else. And indeed it was. This works: create table if not exists Passengers (id int(11) auto_increment primary key, flights_id int(11) not null, customer_id int(11) not null, foreign key (id) references Flights (flights_id), foreign key (id) references Customers (customer_id), name varchar(40), weight tinyint(3)) engine=InnoDB; Yay! Thanks! V -- Shawn Green MySQL Principle Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN
Re: Foreign Key Problem
On Tue, May 18, 2010 at 2:23 PM, Shawn Green shawn.l.gr...@oracle.comwrote: Shawn Green wrote: I may be confused but how can the ID of the Passengers table be both the ID of the Flight they are taking and their Customer ID at the same time? http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html You may want additional ID columns in the Passengers table to point to the parent values in those other tables. Please help me out here. This is what I have: mysql describe Customers; +-++--+-+-++ | Field | Type | Null | Key | Default | Extra | +-++--+-+-++ | id | int(11)| NO | PRI | NULL| auto_increment | mysql describe Flights; +-+---+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+---+--+-+-++ | id | int(11) | NO | PRI | NULL| auto_increment | create table if not exists Passengers (id int(11) auto_increment primary key, foreign key (id) references Flights (flights_id), foreign key (id) references Customers (customer_id), name varchar(40), weight tinyint(3)) engine=InnoDB; Please help me see where I'm stumbling. All the fields have the same type. If I've got it right, foreign key (id) references Flights (flights_id) means that the field flights_id will be created in the table Customers and it will reference id in Flights. Trying to interchange those throws an error indicating that flights_id doesn't exist, presumably in Flights. I'm lost, but close to home ;) Please help. V
Re: Foreign Key Problem
On Wed, May 19, 2010 at 10:59 AM, Shawn Green shawn.l.gr...@oracle.comwrote: Shawn Green wrote: AH! that's your mistake. You think that creating the FK will also create the column. That does not happen. You have to define the table completely before you can associate the columns on this table (the child table) with the correct column on the parent table (either Flights or Customers). You need to declare two more fields before you can link them through a Foreign Key relationship to a field on another table: CREATE TABLE PASSENGERS ( id int auto_increment , flights_id int not null , customer_id int not null ... other passenger table columns here ... , PRIMARY KEY (id) , FOREIGN KEY (flights_id) REFERENCES Flights(id) , FOREIGN KEY (customer_id) REFERENCES Customer(id) ) ENGINE=INNODB; Try it that way and see if it helps. Well, that was certainly one error, but there's another: LATEST FOREIGN KEY ERROR 100519 8:46:10 Error in foreign key constraint of table seaflight/Passengers: foreign key (id) references Flights (flights_id), foreign key (id) references Customers (customer_id), name varchar(40), weight tinyint(3)) engine=InnoDB: Cannot resolve column name close to: ), foreign key (id) references Customers (customer_id), name varchar(40), weight tinyint(3)) engine=InnoDB Now, I've added the innodb engine to all tables and constraints on the id columns of the foreign key tables. What else? TIA, V
Foreign Key Problem
Hi; mysql create table if not exists Passengers (id int unsigned auto_increment primary key, foreign key (id) references Flights (flights_id), foreign key (id) references Customers (customer_id), name varchar(40), weight tinyint(3)); Query OK, 0 rows affected (0.00 sec) mysql select c.first_name, c.middle_name, c.last_name, c.suffix, c.discount, p.flights_id from Customers c join Passengers p on c.id=p.customer_id where flights_id=1; ERROR 1054 (42S22): Unknown column 'p.flights_id' in 'field list' mysql describe Passengers; ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | id | int(10) unsigned | NO | PRI | NULL| auto_increment | | name | varchar(40) | YES | | NULL|| | weight | tinyint(3) | YES | | NULL|| ++--+--+-+-++ 3 rows in set (0.01 sec) So, why didn't the foreign key get created? It exists as a primary key in Customers. Please advise. TIA, Victor
Re: Foreign Key Problem
On Tue, May 18, 2010 at 10:06 AM, Johan De Meersman vegiv...@tuxera.bewrote: You're not specifying an engine, and the default is MyISAM, which doesn't support foreign keys and will likely silently ignore requests for them. Can you confirm that you've changed the default engine to InnoDB ? Got me. No, it wasn't and I'm new to this. Set up my.cnf like this: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 [mysqld_safe] skip-external-locking max_connections=200 read_buffer_size=1M sort_buffer_size=1M log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid # Make sure that you have enough free disk space. innodb_data_file_path = ibdata1:10M:autoextend # # Set buffer pool size to 50-80% of your computer's memory innodb_buffer_pool_size=256M innodb_additional_mem_pool_size=20M # # Set the log file size to about 25% of the buffer pool size innodb_log_file_size=64M innodb_log_buffer_size=8M # innodb_flush_log_at_trx_commit=1 and restarted mysqld. Then this: mysql create table if not exists Passengers (id int unsigned auto_increment primary key, foreign key (id) references Flights (flights_id), foreign key (id) references Customers (customer_id), name varchar(40), weight tinyint(3)) engine=InnoDB; ERROR 1005 (HY000): Can't create table './seaflight/Passengers.frm' (errno: 150) So apparently it didn't like my foreign key. Do I need to do something with the table I'm referencing or what? TIA, V On Tue, May 18, 2010 at 3:44 PM, Victor Subervi victorsube...@gmail.comwrote: Hi; mysql create table if not exists Passengers (id int unsigned auto_increment primary key, foreign key (id) references Flights (flights_id), foreign key (id) references Customers (customer_id), name varchar(40), weight tinyint(3)); Query OK, 0 rows affected (0.00 sec) mysql select c.first_name, c.middle_name, c.last_name, c.suffix, c.discount, p.flights_id from Customers c join Passengers p on c.id=p.customer_id where flights_id=1; ERROR 1054 (42S22): Unknown column 'p.flights_id' in 'field list' mysql describe Passengers; ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | id | int(10) unsigned | NO | PRI | NULL| auto_increment | | name | varchar(40) | YES | | NULL|| | weight | tinyint(3) | YES | | NULL|| ++--+--+-+-++ 3 rows in set (0.01 sec) So, why didn't the foreign key get created? It exists as a primary key in Customers. Please advise. TIA, Victor -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Foreign Key Problem
On Tue, May 18, 2010 at 1:09 PM, Shawn Green shawn.l.gr...@oracle.comwrote: Johan De Meersman wrote: For additional details about failed FK attempts, check the error details in the SHOW INNODB STATUS report. I get this: 100518 10:26:22 Error in foreign key constraint of table seaflight/Passengers: constraint foreign key (id) references Flights (flights_id), constraint foreign key (id) references Customers (customer_id), name varchar(40), weight tinyint(3)) engine=InnoDB: Cannot resolve column name close to: ), constraint foreign key (id) references Customers (customer_id), name varchar(40), weight tinyint(3)) engine=InnoDB Here's my command: create table if not exists Passengers (id int unsigned auto_increment primary key, constraint foreign key (id) references Flights (flights_id), constraint foreign key (id) references Customers (customer_id), name varchar(40), weight tinyint(3)) engine=InnoDB; I think I've got those constraints right. I'm calling the data from those other tables, not the other way around. Please advise. TIA, V
Re: tmp tables
On Thu, Jan 14, 2010 at 1:35 AM, Chris W 4rfv...@cox.net wrote: I think the reason the other poster was so harsh is because others have suggested the right way to do it, if not in a lot of detail, and you have just argued with them. I don't recall anyone doing that. I don't recall arguing. I disagree. And I don't want anyone to create an argument over this comment! Since I'm not in the mood to work on my project I will give you an overview of my shopping cart process and data structure. I have copied over your excellent example and will review it for ideas. Thanks! V
Re: tmp tables
On Mon, Jan 11, 2010 at 3:21 PM, mos mo...@fastmail.fm wrote: At 09:56 AM 1/11/2010, Johnny Withers wrote: Victor, The temporary table solution is not a good one. Use a single table and store each item put into a cart identified by the session ID of the user. A process should clean out this table periodically since there are shoppers that abandon carts from time to time. The design of this table could be quite simple: id - int auto_inc session_id - varchar added_dt - datetime item_id - int quantity - int You could get more complicated depending in your need an item requirements (colors, sizes, etc). -JW I also agree with JW, a single table is better. Don't forget shoppers may spend 30 minutes of more filling up their shopping cart and may lose the connection to the site or have to exit without completing the order. You need to retain the registered user's shopping cart so he can go back to it later. Only delete it if there is no activity on it for 7-10 days. If you use temporary tables and throw out the shopping cart contents if the session is lost, then you will *iss off a lot of customers. I don't see how a single table is a solution. Tables are not 3D data structures. If they were, you'd be right! I need 2D to describe the customer's data: cols (ID, quantity, options, etc) X products. How can I add a third axis of custID??? No! Not possible. I am using cookies and committing data to the temp tables every time the customer puts something in the shopping cart. Can someone please answer my question as to which is better, putting the temp tables in a separate database or together with the 'workhorse' tables? TIA, V
Re: tmp tables
On Wed, Jan 13, 2010 at 2:33 PM, Johnny Withers joh...@pixelated.netwrote: No one designs a shopping cart system this way. http://www.google.com/search?q=shopping+cart+database+table+design If you are dead set on this crazy design it doesn't matter if you put the temp tables in the main database or another database. It'll be just as silly either way. You're so complimentary! Wouldn't it have been better to simply suggest FKs? Or maybe point out, as you did, how others have done it? Why be so harsh when I simply didn't know how it's done? Thank you anyway since you showed me how to do this. V
Re: tmp tables
On Mon, Jan 11, 2010 at 10:35 AM, Baron Schwartz ba...@xaprb.com wrote: Victor, On Sun, Jan 10, 2010 at 1:20 PM, Victor Subervi victorsube...@gmail.com wrote: Hi; I have a shopping cart that will spawn a tmp table for every shopping cart instance. Would it be better to create a separate database for these instead of having them in the same database as all the other tables for the shopping cart? It will not matter at all. But it would be better to choose a different design. Instead of adding a table per cart, just create a table and add a row(s) to it for every cart. This is what relational databases were designed for :-) That strikes me as messy. Each tmp table has as many rows as necessary for the products that are to be bough. To do as you say I would have to create a table with a zillion rows to accommodate however many products I *predict* buyers would buy. Therefore, I guess I should probably create a new database so as to not make a mess of the main database. TIA, V
Re: tmp tables
On Mon, Jan 11, 2010 at 10:49 AM, Baron Schwartz ba...@xaprb.com wrote: Victor, That strikes me as messy. Each tmp table has as many rows as necessary for the products that are to be bough. To do as you say I would have to create a table with a zillion rows to accommodate however many products I *predict* buyers would buy. Therefore, I guess I should probably create a new database so as to not make a mess of the main database. You fundamentally misunderstand relational database design. I suggest reading this book: http://www.xaprb.com/blog/2009/08/22/a-review-of-beginning-database-design-by-clare-churcher/ LOL. Ok, I'll put it on my list. *In the meantime*, since I am reworking my database design for the shopping cart I just finished building and need to get this up __n_o_w__, what would your advice be? V Regards, Baron -- Baron Schwartz Percona Inc: Services and Support for MySQL http://www.percona.com/ -- The Logos has come to bear http://logos.13gems.com/
Re: tmp tables
On Mon, Jan 11, 2010 at 11:38 AM, Keith Murphy bmur...@paragon-cs.comwrote: Victor, Don't want to butt in, and not trying to be rude, but he gave you advice. You don't seem inclined to take it. How else can he, or anyone else, help you? Clearly you don't understand some fundamental issue about relational databases. If you can't just accept his suggestion to put all carts in one table as the way to do it then there really isn't anything else to say. I said I would read the book. I will read it. Not today, however. Today, I will deal with this problem, because tomorrow I will be talking with the client. First things first. V
tmp tables
Hi; I have a shopping cart that will spawn a tmp table for every shopping cart instance. Would it be better to create a separate database for these instead of having them in the same database as all the other tables for the shopping cart? TIA, Victor -- The Logos has come to bear http://logos.13gems.com/
Re: Another Inserting Multiple Values with Set Problem
On Fri, Jan 8, 2010 at 2:26 AM, Thiyaghu CK theyaho...@gmail.com wrote:
Hi Victor,
You have given space after the comma(shown here: ('Small, Medium,
XSmall')). Take out the space and try, it will work.
Example:
mysql insert into products(sizes) values ('Small,Medium,small,medium');
Query OK, 1 row affected (0.05 sec)
insert into products (SKU, Category, Name, Title, Description, Price,
SortFactor, Availability, OutOfStock, Weight, ShipFlatFee, ShipPercentPrice,
ShipPercentWeight, sizes, colorsShadesNumbersShort) values(prodSKU1,
prodCat1, name1, title1, descr, 123.45, 500, 1, 0, 2.5,
10.00, 5, 2, ('Small,Medium,XSmall'),
('teal_E2725B,black_FF,yellow_9ACD32'));
mysql select sizes from products;
++
| sizes |
++
| Medium |
++
1 row in set (0.00 sec)
mysql select colorsShadesNumbersShort from products;
+--+
| colorsShadesNumbersShort |
+--+
| black_FF |
+--+
1 row in set (0.00 sec)
It only entered one of each!! Please help.
V
Re: Another Inserting Multiple Values with Set Problem
On Fri, Jan 8, 2010 at 9:29 AM, Thiyaghu CK theyaho...@gmail.com wrote:
Hi Victor,
Take out the double quotes from[ ('Small,Medium,XSmall'),
('teal_E2725B,black_FF,yellow_9ACD32')]. Its working fine for me.
mysql insert into products (sizes, colorsShadesNumbersShort)
values(('Small,Medium,XSmall'), ('teal_E2725B,black_FF,yellow_9ACD32'));
Query OK, 1 row affected (0.05 sec)
Yes, that worked! Thanks!
V
Re: Another Inserting Multiple Values with Set Problem
OK, guys, I'm totally confused:
mysql insert into products (SKU, Category, Name, Title, Description, Price,
SortFactor, Availability, OutOfStock, Weight, ShipFlatFee, ShipPercentPrice,
ShipPercentWeight, sizes, colorsShadesNumbersShort) values (prodSKU1,
prodCat1, name1, title1, descr, 12.34, 500, 1, 0, 2.5,
10, 5, 2, ('Small, Medium, XSmall'), ('teal_E2725B, black_FF,
olive_6B8E23, yellow_9ACD32'));
Query OK, 1 row affected, 2 warnings (0.00 sec)
mysql show warnings;
+-+--+---+
| Level | Code |
Message |
+-+--+---+
| Warning | 1265 | Data truncated for column 'sizes' at row
1|
| Warning | 1265 | Data truncated for column 'colorsShadesNumbersShort' at
row 1 |
+-+--+---+
2 rows in set (0.00 sec)
mysql truncate products;
Query OK, 0 rows affected (0.00 sec)
mysql insert into products (SKU, Category, Name, Title, Description, Price,
SortFactor, Availability, OutOfStock, Weight, ShipFlatFee, ShipPercentPrice,
ShipPercentWeight, sizes, colorsShadesNumbersShort) values (prodSKU1,
prodCat1, name1, title1, descr, 12.34, 500, 1, 0, 2.5,
10, 5, 2, ('Small', 'Medium', 'XSmall'), ('teal_E2725B',
'black_FF', 'olive_6B8E23', 'yellow_9ACD32'));
Query OK, 1 row affected, 2 warnings (0.00 sec)
mysql truncate products;
Query OK, 0 rows affected (0.00 sec)
mysql insert into products (SKU, Category, Name, Title, Description, Price,
SortFactor, Availability, OutOfStock, Weight, ShipFlatFee, ShipPercentPrice,
ShipPercentWeight, sizes, colorsShadesNumbersShort) values (prodSKU1,
prodCat1, name1, title1, descr, 12.34, 500, 1, 0, 2.5,
10, 5, 2, Small, Medium, XSmall, teal_E2725B, black_FF,
olive_6B8E23, yellow_9ACD32);
Query OK, 1 row affected, 2 warnings (0.00 sec)
mysql insert into products (sizes, colorsShadesNumbersShort) values
('Large, Small','aqua_7FFFD4, fuchsia_FF77FF');
Query OK, 1 row affected, 6 warnings (0.01 sec)
mysql show warnings;
+-+--+---+
| Level | Code |
Message |
+-+--+---+
| Warning | 1364 | Field 'SKU' doesn't have a default
value |
| Warning | 1364 | Field 'Name' doesn't have a default
value |
| Warning | 1364 | Field 'Title' doesn't have a default
value|
| Warning | 1364 | Field 'Description' doesn't have a default
value |
| Warning | 1265 | Data truncated for column 'sizes' at row
1|
| Warning | 1265 | Data truncated for column 'colorsShadesNumbersShort' at
row 1 |
+-+--+---+
6 rows in set (0.00 sec)
mysql describe products sizes;
+---+--+--+-+-+---+
| Field |
Type | Null
| Key | Default | Extra |
+---+--+--+-+-+---+
| sizes |
set('XSmall','Small','Medium','Large','XLarge','XXLarge','XXXLarge') | YES
| | NULL| |
+---+--+--+-+-+---+
1 row in set (0.00 sec)
mysql describe products colorsShadesNumbersShort;
+--+---+--+-+-+---+
| Field|
Type
| Null | Key | Default | Extra |
+--+---+--+-+-+---+
| colorsShadesNumbersShort |
set('black_FF','yellow_9ACD32','teal_E2725B','olive_6B8E23','green_00A550','white_0F4D92','silver_708090','red_FE2712','lime_32CD32')
| YES | | NULL| |
+--+---+--+-+-+---+
1 row in set (0.00 sec)
How, o how, do I insert into sets???
TIA,
V
Another Inserting Multiple Values with Set Problem
Hi;
The following doesn't work with or without enclosing the sets in quotes:
update products set SKU=prodSKU2, Category=prodCat1, Name=name2,
Title=title2, Description=descr, Price=22.55, SortFactor=500,
Availability=1, OutOfStock=0, Weight=5.5, ShipFlatFee=10.0,
ShipPercentPrice=5, ShipPercentWeight=2, sizes=('Extra-small', 'Large',
'Small', 'Medium', 'XLarge', 'XXLarge', 'XXXLarge'),
colorsShadesNumbersShort=('aqua:7FFFD4', 'blue:99', 'gray:465945',
'navy-blue:CC7722', 'black:FF', 'maroon:B03060', 'purple:50404D',
'yellow:9ACD32', 'fuchsia:FF77FF') where ID=2;
Everything inserts but the sets which throw warnings. What am I missing?
TIA,
Victor
--
The Logos has come to bear
http://logos.13gems.com/
Re: Another Inserting Multiple Values with Set Problem
On Wed, Jan 6, 2010 at 10:56 AM, Michael Dykman mdyk...@gmail.com wrote:
I don't really use sets when I can avoid them (too much special
wierdness) but from the manual I see this:
mysql INSERT INTO myset (col) VALUES
- ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');
Query OK, 5 rows affected (0.01 sec)
mysql SELECT col FROM myset;
+--+
| col |
+--+
| a,d |
| a,d |
| a,d |
| a,d |
| a,d |
+--+
which suggests your syntax is in error. It appears that it should be:
update products set
SKU=prodSKU2,
Category=prodCat1,
Name=name2,
Title=title2,
Description=descr,
Price=22.55,
SortFactor=500,
Availability=1,
OutOfStock=0,
Weight=5.5,
ShipFlatFee=10.0,
ShipPercentPrice=5,
ShipPercentWeight=2,
sizes= 'Extra-small, Large, Small, Medium, XLarge, XXLarge, XXXLarge',
colorsShadesNumbersShort='aqua:7FFFD4, blue:99, gray:465945,
navy-blue:CC7722, black:FF, maroon:B03060,
purple:50404D,yellow:9ACD32, fuchsia:FF77FF'
where ID=2;
watch for the line-wraps in the SET data specifications.. I did both
of your sets (I assume colorsShadesNumbersShort is a set.. I don't
know what you are trying to do there.
No, that thew the same errors. I know you have to enclose the sets in
parentheses and individually quote each element.
V
- michael dykman
On Wed, Jan 6, 2010 at 9:32 AM, Victor Subervi victorsube...@gmail.com
wrote:
On Wed, Jan 6, 2010 at 10:23 AM, Michael Dykman mdyk...@gmail.com
wrote:
What are the warnings?
mysql show warnings;
+-+--+---+
| Level | Code |
Message |
+-+--+---+
| Warning | 1265 | Data truncated for column 'sizes' at row
1|
| Warning | 1265 | Data truncated for column 'colorsShadesNumbersShort'
at
row 1 |
+-+--+---+
On Wed, Jan 6, 2010 at 9:04 AM, Victor Subervi victorsube...@gmail.com
wrote:
Hi;
The following doesn't work with or without enclosing the sets in
quotes:
update products set SKU=prodSKU2, Category=prodCat1, Name=name2,
Title=title2, Description=descr, Price=22.55, SortFactor=500,
Availability=1, OutOfStock=0, Weight=5.5, ShipFlatFee=10.0,
ShipPercentPrice=5, ShipPercentWeight=2, sizes=('Extra-small',
'Large',
'Small', 'Medium', 'XLarge', 'XXLarge', 'XXXLarge'),
colorsShadesNumbersShort=('aqua:7FFFD4', 'blue:99',
'gray:465945',
'navy-blue:CC7722', 'black:FF', 'maroon:B03060', 'purple:50404D',
'yellow:9ACD32', 'fuchsia:FF77FF') where ID=2;
Everything inserts but the sets which throw warnings. What am I
missing?
TIA,
Victor
--
The Logos has come to bear
http://logos.13gems.com/
--
- michael dykman
- mdyk...@gmail.com
May the Source be with you.
--
The Logos has come to bear
http://logos.13gems.com/
--
- michael dykman
- mdyk...@gmail.com
May the Source be with you.
--
The Logos has come to bear
http://logos.13gems.com/
Re: Another Inserting Multiple Values with Set Problem
On Wed, Jan 6, 2010 at 12:14 PM, Michael Dykman mdyk...@gmail.com wrote:
so you dropped the quotes around the unneccessarily bracketed
expression? It's hard to diagnose when your example isn't even what
you think is syntactically correct.
Here's my example again. Syntactically correct. From my original post:
update products set SKU=prodSKU2, Category=prodCat1, Name=name2,
Title=title2, Description=descr, Price=22.55, SortFactor=500,
Availability=1, OutOfStock=0, Weight=5.5, ShipFlatFee=10.0,
ShipPercentPrice=5, ShipPercentWeight=2, sizes=('Extra-small', 'Large',
'Small', 'Medium', 'XLarge', 'XXLarge', 'XXXLarge'),
colorsShadesNumbersShort=('
aqua:7FFFD4', 'blue:99', 'gray:465945', 'navy-blue:CC7722',
'black:FF', 'maroon:B03060', 'purple:50404D', 'yellow:9ACD32',
'fuchsia:FF77FF') where ID=2;
I can't find a reference, but i recall there is some limit on the
length of SET member identifiers.. some of yours look longish, maybe?
They're not. Awaiting someone who can help me here.
TIA,
V
Re: Another Inserting Multiple Values with Set Problem
On Wed, Jan 6, 2010 at 2:18 PM, Hassan Schroeder hassan.schroe...@gmail.com
wrote:
On Wed, Jan 6, 2010 at 11:39 AM, Victor Subervi victorsube...@gmail.com
wrote:
Here's my example again. Syntactically correct. From my original post:
update products set SKU=prodSKU2, Category=prodCat1, Name=name2,
Title=title2, Description=descr, Price=22.55, SortFactor=500,
Availability=1, OutOfStock=0, Weight=5.5, ShipFlatFee=10.0,
ShipPercentPrice=5, ShipPercentWeight=2, sizes=('Extra-small',
'Large',
'Small', 'Medium', 'XLarge', 'XXLarge', 'XXXLarge'),
Lose the enclosing double-quotes and put all the values within a
single set of single quotes.
colorsShadesNumbersShort=('
aqua:7FFFD4', 'blue:99', 'gray:465945', 'navy-blue:CC7722',
'black:FF', 'maroon:B03060', 'purple:50404D', 'yellow:9ACD32',
'fuchsia:FF77FF')
e.g. (shortened for lazyness):
colorsShadesNumbersShort = ('aqua:7FFFD4, blue:99')
where ID=2;
mysql update products set SKU=prodSKU2, Category=prodCat1,
Name=name2, Title=title2, Description=descr, Price=22.55,
SortFactor=500, Availability=1, OutOfStock=0, Weight=5.5,
ShipFlatFee=10.0, ShipPercentPrice=5, ShipPercentWeight=2,
sizes=('Extra-small, Large, Small, Medium, XLarge, XXLarge, XXXLarge'),
colorsShadesNumbersShort=('aqua:7FFFD4, blue:99, gray:465945,
navy-blue:CC7722, black:FF, maroon:B03060, purple:50404D, yellow:9ACD32,
fuchsia:FF77FF') where ID=2;
Query OK, 1 row affected, 2 warnings (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 2
mysql show warnings;
+-+--+---+
| Level | Code |
Message |
+-+--+---+
| Warning | 1265 | Data truncated for column 'sizes' at row
1|
| Warning | 1265 | Data truncated for column 'colorsShadesNumbersShort' at
row 1 |
+-+--+---+
2 rows in set (0.00 sec)
Guess again.
V
Re: Another Inserting Multiple Values with Set Problem
On Wed, Jan 6, 2010 at 2:34 PM, Hassan Schroeder hassan.schroe...@gmail.com
wrote:
On Wed, Jan 6, 2010 at 1:25 PM, Victor Subervi victorsube...@gmail.com
wrote:
mysql update products set SKU=prodSKU2, Category=prodCat1,
Name=name2, Title=title2, Description=descr, Price=22.55,
SortFactor=500, Availability=1, OutOfStock=0, Weight=5.5,
ShipFlatFee=10.0, ShipPercentPrice=5, ShipPercentWeight=2,
sizes=('Extra-small, Large, Small, Medium, XLarge, XXLarge, XXXLarge'),
colorsShadesNumbersShort=('aqua:7FFFD4, blue:99, gray:465945,
navy-blue:CC7722, black:FF, maroon:B03060, purple:50404D,
yellow:9ACD32,
fuchsia:FF77FF') where ID=2;
Query OK, 1 row affected, 2 warnings (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 2
mysql show warnings;
+-+--+---+
| Level | Code |
Message |
+-+--+---+
| Warning | 1265 | Data truncated for column 'sizes' at row
1|
| Warning | 1265 | Data truncated for column 'colorsShadesNumbersShort'
at
row 1 |
+-+--+---+
2 rows in set (0.00 sec)
Guess again.
Hardly a guess, that syntax works fine for me:
mysql update jeweleryproducts set audience = ('women,seniors') where
product = 'E30123E';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
mysql select product, audience from jeweleryproducts where product =
'E30123E';
+-+---+
| product | audience |
+-+---+
| E30123E | women,seniors |
+-+---+
1 row in set (0.00 sec)
mysql
Works well *only* for one entry! Not multiple entries.
What's the `create table` look like for the table in question?
mysql describe products;
+--+---+--+-+-++
| Field|
Type
| Null | Key | Default | Extra |
+--+---+--+-+-++
| ID | tinyint(5)
unsigned
| NO | PRI | NULL| auto_increment |
| SKU |
varchar(40)
| NO | UNI | NULL||
| Category |
varchar(40)
| YES | | NULL||
| Name |
varchar(50)
| NO | | NULL||
| Title|
varchar(100)
| NO | | NULL||
| Description |
mediumtext
| NO | | NULL||
| Price|
float(8,2)
| YES | | NULL||
| SortFactor |
int(4)
| YES | | 500 ||
| Availability |
tinyint(1)
| NO | | 1 ||
| OutOfStock |
tinyint(1)
| NO | | 0 ||
| Weight |
float(7,2)
| NO | | 0.00||
| ShipFlatFee |
float(5,2)
| NO | | 10.00 ||
| ShipPercentPrice | tinyint(2)
unsigned
| NO | | 5 ||
| ShipPercentWeight| tinyint(2)
unsigned
| NO | | 2 ||
| pic0 |
mediumblob
| YES | | NULL||
| pic1 |
mediumblob
| YES | | NULL||
| sizes|
set('Extra-small','Small','Medium','Large','XLarge','XXLarge','XXXLarge')
| YES | | NULL||
| colorsShadesNumbersShort |
set('blue:99','gray:465945','purple:50404D','navy-blue:CC7722','fuchsia:FF77FF','aqua:7FFFD4','maroon:B03060','black:FF','yellow:9ACD32')
| YES | | NULL||
+--+---+--+-+-++
18 rows in set (0.00 sec)
V
Re: Another Inserting Multiple Values with Set Problem
On Wed, Jan 6, 2010 at 2:40 PM, Michael Dykman mdyk...@gmail.com wrote: How about you show us the schema for the table so we know what is defined as what? Done in last email. Also, as the update does succeed, it would be interesting to see what value actually got stored. After you have accounted for each bit in the stored value, we might have a clue about what is being truncated. mysql select sizes, colorsShadesNumbersShort from products; +-+--+ | sizes | colorsShadesNumbersShort | +-+--+ | Extra-small | blue:99 | | Extra-small | aqua:7FFFD4 | | Extra-small | blue:99 | | | | +-+--+ 4 rows in set (0.00 sec) Ain't nothin' getting stored. One thing I did just note: the hyphen in 'Extra-Small'. Set identifiers need to be valid mysql identifiers and the hyphen '-' is not a valid identifier character (as it is an arithmatic operator). I can't imagine that those colons in the colour list are healthy either. They all work except in a certain case where I had to pull the hyphen out. I can enter all of these products individually. The point of a set identifier to be an easy mnemonic for a particular bit value. Nothing is gained by trying to represent data with the identifier itself. Huh? Please explain. V
Re: How Set Up This Table
On Mon, Jan 4, 2010 at 10:40 AM, Arthur Fuller fuller.art...@gmail.comwrote: The ProductPackages table is what is known as an associate table, and is used to implement a many-to-many relationship. You only need it if a given product can be in multiple packages. If not, then you can eliminate the associative table and just add a PackageID column to the Products table. There's also a possible tiny wrinkle that may require another new column. I once did an app similar to yours, with the many-to-many requirement. The wrinkle was that a given package might contain several instances of a given product (i.e. four jars of jam, for example). Because one of the goals of the app was to generate a packing list, and the packages were assembled only upon demand, the packing list told the shippers what to assemble. To meet this requirement, I added a quantity column to the associative table, so that in Package A there might be four jars of jam and in Package B only two. I don't think I have to do that. I've coded it but not tested yet. I think I have the concept, however. 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 :) 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
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
How Set Up This Table
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/
Inserting Multiple Values with Set
Hi;
mysql describe products;
+--+---+--+-+-++
| Field|
Type
| Null | Key | Default | Extra |
+--+---+--+-+-++
| ID | tinyint(5)
unsigned
| NO | PRI | NULL| auto_increment |
| SKU |
varchar(40)
| NO | UNI | NULL||
| Category |
varchar(40)
| YES | | NULL||
| Name |
varchar(50)
| NO | | NULL||
| Title|
varchar(100)
| NO | | NULL||
| Description |
mediumtext
| NO | | NULL||
| Price|
float(8,2)
| YES | | NULL||
| SortFactor |
int(4)
| YES | | 500 ||
| Availability |
tinyint(1)
| NO | | 1 ||
| OutOfStock |
tinyint(1)
| NO | | 0 ||
| ShipFlatFee |
float(5,2)
| NO | | 10.00 ||
| ShipPercentPrice | tinyint(2)
unsigned
| NO | | 5 ||
| ShipPercentWeight| tinyint(2)
unsigned
| NO | | 2 ||
| pic0 |
mediumblob
| YES | | NULL||
| pic1 |
mediumblob
| YES | | NULL||
| sizes|
set('Extra-small','Small','Medium','Large','XLarge','XXLarge','XXXLarge')
| YES | | NULL||
| colorsShadesNumbersShort |
set('blue:99','gray:465945','purple:50404D','navy-blue:CC7722','fuchsia:FF77FF','aqua:7FFFD4','maroon:B03060','black:FF','yellow:9ACD32')
| YES | | NULL||
+--+---+--+-+-++
17 rows in set (0.00 sec)
The following insert chokes when I try to insert multiple values from the
sets:
insert into products (SKU, Category, Name, Title, Description, Price,
SortFactor, Availability, OutOfStock, ShipFlatFee, ShipPercentPrice,
ShipPercentWeight, sizes, colorsShadesNumbersShort) values(prodSKU1,
prodCat1, name1, title1, descr, 12.34, 500, 1, 0, 10.00,
5, 2, Extra-small, 'aqua:7FFFD4', 'blue:99');
mysql show warnings;
+-+--+---+
| Level | Code |
Message |
+-+--+---+
| Warning | 1265 | Data truncated for column 'colorsShadesNumbersShort' at
row 1 |
+-+--+---+
1 row in set (0.00 sec)
Please advise.
TIA,
Victor
--
The Logos has come to bear
http://logos.13gems.com/
Re: Inserting Multiple Values with Set
On Fri, Jan 1, 2010 at 4:34 PM, Don Read don_r...@att.net wrote:
On Fri, 1 Jan 2010 14:34:48 -0500 Victor Subervi said:
Hi;
snip
The following insert chokes when I try to insert multiple values from the
sets:
insert into products (SKU, Category, Name, Title, Description, Price,
SortFactor, Availability, OutOfStock, ShipFlatFee, ShipPercentPrice,
ShipPercentWeight, sizes, colorsShadesNumbersShort) values(prodSKU1,
prodCat1, name1, title1, descr, 12.34, 500, 1, 0,
10.00,
5, 2, Extra-small, 'aqua:7FFFD4', 'blue:99');
INSERT INTO products ...
..., ('aqua:7FFFD4', 'blue:99'));
http://dev.mysql.com/doc/refman/5.0/en/set.html
Thanks
V
last_insert_id
Hi; mysql select * from products; ++--+--+---++-+---++--++-+--+---+--+---+---++--+--+--+--+-+--+ | ID | SKU | Category | Name | Title | Description | Price | SortFactor | Availability | OutOfStock | ShipFlatFee | ShipPercentPrice | ShipPercentWeight | Associations | TempPrice | LastDatePrice | Weight | Metal| PercentMetal | pic0 | pic1 | sizes | colorsShadesNumbersShort | ++--+--+---++-+---++--++-+--+---+--+---+---++--+--+--+--+-+--+ | 1 | prodSKU1 | prodCat1 | name1 | title1 | desc| 12.34 | 500 |1 | 0 | 10.00 |5 | 2 | | 1 | 2000-01-01| 2.50 | 14k gold | 20 | NULL | NULL | Extra-small | | ++--+--+---++-+---++--++-+--+---+--+---+---++--+--+--+--+-+--+ 1 row in set (0.00 sec) mysql select last_insert_id() from products; +--+ | last_insert_id() | +--+ |0 | +--+ 1 row in set (0.00 sec) mysql Now, I was expecting 1, not 0! What up? TIA, Victor
Re: last_insert_id
On Sun, Dec 27, 2009 at 11:27 AM, Mattia Merzi mattia.me...@gmail.comwrote:
2009/12/27 Victor Subervi victorsube...@gmail.com:
mysql select * from products;
[...]
mysql select last_insert_id() from products;
[...]
Now, I was expecting 1, not 0! What up?
[...] LAST_INSERT_ID() (no arguments) returns the first
automatically generated value successfully inserted for
an AUTO_INCREMENT column as a result of the most
recently executed INSERT statement. [...] If no rows
were (successfully) inserted, LAST_INSERT_ID() returns 0.
But it *is* auto incremented!
mysql describe products;
+--+---+--+-+++
| Field|
Type
| Null | Key | Default| Extra |
+--+---+--+-+++
| ID | tinyint(5)
unsigned
| NO | PRI | NULL | auto_increment |
| SKU |
varchar(40)
| NO | UNI | NULL ||
| Category |
varchar(40)
| YES | | NULL ||
| Name |
varchar(50)
| NO | | NULL ||
| Title|
varchar(100)
| NO | | NULL ||
| Description |
mediumtext
| NO | | NULL ||
| Price|
float(8,2)
| YES | | NULL ||
| SortFactor |
int(4)
| YES | | 500||
| Availability |
tinyint(1)
| NO | | 1 ||
| OutOfStock |
tinyint(1)
| NO | | 0 ||
| ShipFlatFee |
float(5,2)
| NO | | 10.00 ||
| ShipPercentPrice | tinyint(2)
unsigned
| NO | | 5 ||
| ShipPercentWeight| tinyint(2)
unsigned
| NO | | 2 ||
| Associations |
varchar(40)
| NO | | NULL ||
| TempPrice|
tinyint(1)
| NO | | 1 ||
| LastDatePrice|
date
| NO | | 2000-01-01 ||
| Weight |
float(7,2)
| NO | | NULL ||
| Metal| enum('14k gold','18k gold','white
gold','silver','tungsten','titanium')
| NO | | NULL ||
| PercentMetal | tinyint(2)
unsigned
| NO | | NULL ||
| pic0 |
mediumblob
| YES | | NULL ||
| pic1 |
mediumblob
| YES | | NULL ||
| sizes|
set('Extra-small','Small','Medium','Large','XLarge','XXLarge','XXXLarge')
| YES | | NULL ||
| colorsShadesNumbersShort |
set('blue:99','gray:465945','purple:50404D','navy-blue:CC7722','fuchsia:FF77FF','aqua:7FFFD4','maroon:B03060','black:FF','yellow:9ACD32')
| YES | | NULL ||
+--+---+--+-+++
23 rows in set (0.00 sec)
mysql
Re: last_insert_id
On Sun, Dec 27, 2009 at 12:00 PM, Michael Dykman mdyk...@gmail.com wrote: last_insert_id() returns the last id auto-incremented in *the current session*. If you disconnect and reconnect, it can not be retrieved. Ahah! So how do I retrieve the last id inserted irrespective of connection? TIA, V
Re: last_insert_id
On Sun, Dec 27, 2009 at 1:30 PM, Gary Smith li...@l33t-d00d.co.uk wrote: Victor Subervi wrote: On Sun, Dec 27, 2009 at 12:00 PM, Michael Dykman mdyk...@gmail.com wrote: last_insert_id() returns the last id auto-incremented in *the current session*. If you disconnect and reconnect, it can not be retrieved. Ahah! So how do I retrieve the last id inserted irrespective of connection? Would max() work for you? Ahah! No space! Got it. Thanks. V
Join Statement
Hi; I have the following: mysql select SKU, Quantity, Name, Price, p.sizes, p.colorsShadesNumbersShort from tem126080739853 t join products p on t.ProdID-p.ID; Empty set (0.00 sec) mysql select * from tem126080739853; +++--+-+--+ | ID | ProdID | Quantity | sizes | colorsShadesNumbersShort | +++--+-+--+ | 1 | 2 |2 | Extra-small | navy-blue:CC7722 | +++--+-+--+ 1 row in set (0.00 sec) mysql select SKU, Quantity, Name, Price, p.sizes, p.colorsShadesNumbersShort from tem126080739853 t join products p on t.ProdID-p.ID; Empty set (0.03 sec) mysql select SKU, Quantity, Name, Price, t.sizes, t.colorsShadesNumbersShort from tem126080739853 t join products p on t.ProdID-p.ID; Empty set (0.00 sec) mysql select ID, SKU, Name, Price from products; ++--+---++ | ID | SKU | Name | Price | ++--+---++ | 2 | prodSKU1 | name1 | 555.22 | ++--+---++ 1 row in set (0.00 sec) So I'm at a loss as to why the above select join statement fails. Please advise. TIA, Victor
Re: Join Statement
On Mon, Dec 14, 2009 at 12:37 PM, Peter Brawley peter.braw...@earthlink.net wrote: ... on t.ProdID-p.ID; Your join clause subtracts the two IDs, so it's on IDs that differ, and apparently there aren't any. I beg to differ: mysql select SKU, Quantity, Name, Price, p.sizes, p.colorsShadesNumbersShort from tem126080739853 t join products p on t.ProdID-p.ID http://t.prodid-p.id/; Empty set (0.00 sec) mysql select * from tem126080739853; +++--+ -+--+ | ID | ProdID | Quantity | sizes | colorsShadesNumbersShort | +++--+-+--+ | 1 | 2 |2 | Extra-small | navy-blue:CC7722 | +++--+-+--+ 1 row in set (0.00 sec) mysql select SKU, Quantity, Name, Price, p.sizes, p.colorsShadesNumbersShort from tem126080739853 t join products p on t.ProdID-p.ID http://t.prodid-p.id/; Empty set (0.03 sec) mysql select SKU, Quantity, Name, Price, t.sizes, t.colorsShadesNumbersShort from tem126080739853 t join products p on t.ProdID-p.ID http://t.prodid-p.id/; Empty set (0.00 sec) mysql select ID, SKU, Name, Price from products; ++--+---++ | ID | SKU | Name | Price | ++--+---++ | 2 | prodSKU1 | name1 | 555.22 | ++--+---++ 1 row in set (0.00 sec) t.ProdID == 2 p.ID == 2 That's a match. So why does my select join fail? TIA, V
Re: Join Statement
On Mon, Dec 14, 2009 at 1:04 PM, Peter Brawley peter.braw...@earthlink.netwrote: ...on t.ProdID-p.ID... _subtracts_ the two IDs. To match them use '=' rather than '-'. Thank you for all of you that caught that. V
Duplicate Entry, But Table Empty!
Hi; mysql insert into tem126072414516 (ProdID, Quantity) values (2, 2); ERROR 1062 (23000): Duplicate entry '2' for key 2 mysql select * from tem126072385457; Empty set (0.00 sec) mysql describe tem126072385457; +--+-+--+-+-++ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-++ | ID | int(5) unsigned | NO | PRI | NULL| auto_increment | | ProdID | int(5) unsigned | YES | UNI | NULL|| | Quantity | int(3) unsigned | YES | | NULL|| +--+-+--+-+-++ 3 rows in set (0.00 sec) Please advise. TIA, Victor
Re: Duplicate Entry, But Table Empty!
On Sun, Dec 13, 2009 at 12:21 PM, Pinter Tibor tib...@tibyke.hu wrote: Victor Subervi wrote: Hi; mysql insert into *tem126072414516* (ProdID, Quantity) values (2, 2); mysql select * from *tem126072385457*; mysql insert into *tem126072414516* (ProdID, Quantity) values (2, 2); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*tem126072414516* (ProdID, Quantity) values (2, 2)' at line 1 Please advise. V
inserting sets of data
Hi; I have a column defined as a set. How do I insert data into that column? Please give me an example. TIA, Victor
Re: inserting sets of data
On Sat, Dec 12, 2009 at 10:58 AM, David Giragosian dgiragos...@gmail.comwrote: On Sat, Dec 12, 2009 at 9:54 AM, Victor Subervi victorsube...@gmail.comwrote: Hi; I have a column defined as a set. How do I insert data into that column? Please give me an example. TIA, Victor Lots of examples here: http://dev.mysql.com/tech-resources/articles/mysql-set-datatype.html Perfect. Thanks! V
Update Doesn't Update!
Hi;
mysql update products set sizes=('Small', 'Large') where ID=0;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 1
mysql select sizes, colorsShadesNumbersShort from products where ID=0;
+---+--+
| sizes | colorsShadesNumbersShort |
+---+--+
| | |
+---+--+
1 row in set (0.00 sec)
Huh?
TIA,
Victor
Re: Update Doesn't Update!
On Fri, Dec 11, 2009 at 4:48 AM, Jørn Dahl-Stamnes
sq...@dahl-stamnes.netwrote:
On Friday 11 December 2009 10:38, Victor Subervi wrote:
Hi;
mysql update products set sizes=('Small', 'Large') where ID=0;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 1
Look at the message, 0 rows changed and 1 warning.
You cannot have ID=0 if ID is an index.
Yikes! Then how do I update this table? I will need to update every variable
*except* the ID, which is the primary key and an auto_increment.
V
Re: Update Doesn't Update!
On Fri, Dec 11, 2009 at 4:48 AM, Jørn Dahl-Stamnes
sq...@dahl-stamnes.netwrote:
On Friday 11 December 2009 10:38, Victor Subervi wrote:
Hi;
mysql update products set sizes=('Small', 'Large') where ID=0;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 1
Look at the message, 0 rows changed and 1 warning.
You cannot have ID=0 if ID is an index.
Yikes! Then how do I update this table? I will need to update every variable
*except* the ID, which is the primary key and an auto_increment.
V
Re: Update Doesn't Update!
On Fri, Dec 11, 2009 at 4:43 AM, cars...@bitbybit.dk wrote:
On Fri, 11 Dec 2009 04:38:01 -0500, Victor Subervi
victorsube...@gmail.com
wrote:
Hi;
mysql update products set sizes=('Small', 'Large') where ID=0;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 1
Warnings: 1
do a SHOW WARNINGS immediately after you execute the stmt.
mysql update products set sizes=('Small', 'Large') where SKU='prodSKU1';
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 1
mysql show warnings;
+-+--++
| Level | Code | Message|
+-+--++
| Warning | 1265 | Data truncated for column 'sizes' at row 1 |
+-+--++
1 row in set (0.00 sec)
What do? How do I enter multiple values?
TIA,
V
Re: Update Doesn't Update!
On Fri, Dec 11, 2009 at 5:13 AM, cars...@bitbybit.dk wrote:
On Fri, 11 Dec 2009 05:09:52 -0500, Victor Subervi
victorsube...@gmail.com
wrote:
mysql update products set sizes=('Small', 'Large') where
SKU='prodSKU1';
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 1
mysql show warnings;
+-+--++
| Level | Code | Message|
+-+--++
| Warning | 1265 | Data truncated for column 'sizes' at row 1 |
+-+--++
1 row in set (0.00 sec)
What do? How do I enter multiple values?
Impossible to say, until you let us know how you defined the column in the
first place...
Sorry. It's an enum of which the elements I am trying to add into a row are
elements of the same enum; that is, a subset.
V
Re: Update Doesn't Update!
On Fri, Dec 11, 2009 at 5:33 AM, cars...@bitbybit.dk wrote:
On Fri, 11 Dec 2009 05:28:41 -0500, Victor Subervi
victorsube...@gmail.com
wrote:
On Fri, Dec 11, 2009 at 5:13 AM, cars...@bitbybit.dk wrote:
On Fri, 11 Dec 2009 05:09:52 -0500, Victor Subervi
victorsube...@gmail.com
wrote:
mysql update products set sizes=('Small', 'Large') where
SKU='prodSKU1';
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 1
mysql show warnings;
+-+--++
| Level | Code | Message|
+-+--++
| Warning | 1265 | Data truncated for column 'sizes' at row 1 |
+-+--++
1 row in set (0.00 sec)
What do? How do I enter multiple values?
Impossible to say, until you let us know how you defined the column in
the
first place...
Sorry. It's an enum of which the elements I am trying to add into a row
are
elements of the same enum; that is, a subset.
You're using the wrong type. RTFM re. the difference between enums and
sets.
k. Thanks,
V
Re: Update Doesn't Update!
On Fri, Dec 11, 2009 at 8:43 AM, Johan De Meersman vegiv...@tuxera.bewrote:
On Fri, Dec 11, 2009 at 11:19 AM, Mark Goodge m...@good-stuff.co.uk
wrote:
Jørn Dahl-Stamnes wrote:
On Friday 11 December 2009 10:38, Victor Subervi wrote:
Hi;
mysql update products set sizes=('Small', 'Large') where ID=0;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 1
Look at the message, 0 rows changed and 1 warning.
You cannot have ID=0 if ID is an index.
You can, but not if it's an auto-increment field.
Also, not *entirely* correct, although you have to jump through a few
hoops:
it can occur if the field was changed to auto_increment *after* the 0 was
put in there.
Yes, I inherited a database like that once, and yes, it fucks up your day.
I'm lost. I set up this database originally with auto_increment and the
first value was 0. I thought that was always the case. Is there a problem
here?
V
Re: Select Problem
On Sun, Dec 6, 2009 at 2:42 PM, Steve Edberg edb...@edberg-online.comwrote: At 1:26 PM -0500 12/6/09, Victor Subervi wrote: Hi; I have the following: mysql select * from categoriesProducts as c inner join relationshipProducts as r on c.ID = r.Child inner join categoriesProducts as p on r.Parent = p.ID where p.Category = prodCat2; ERROR 1054 (42S22): Unknown column 'prodCat2' in 'where clause' mysql describe categoriesProducts; +--+-+--+-+-++ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-++ | ID | int(3) unsigned | NO | PRI | NULL| auto_increment | | Category | varchar(40) | YES | | NULL|| | Parent | varchar(40) | YES | | NULL|| +--+-+--+-+-++ 3 rows in set (0.00 sec) mysql select * from categoriesProducts; ++--++ | ID | Category | Parent | ++--++ | 1 | prodCat1 | None | | 2 | prodCat2 | None | ++--++ 2 rows in set (0.00 sec) So I'm at a loss. No, 'prodCat2' isn't a column, but I don't understand how I specified that in my query. Please advise. TIA, Victor You didn't quote prodCat2 in the query, so it was assuming you were referring to the column name. Try: select * from categoriesProducts as c inner join relationshipProducts as r on c.ID = r.Child inner join categoriesProducts as p on r.Parent = p.ID where p.Category = 'prodCat2'; Thanks! V
Second Request: Challenging Select Statement
Hi; I posted this Saturday. Perhaps it's too challenging for those who read it to answer. I hope someone can. I need to write a select statement that enables me to select column 'ID' from a table where a certain value is found in an enum of a specific column. For example... select column_type from information_schema.columns where table_name='products' and column_name='Categories'; will give me the column and its enumerations. How do I supply one of the enumerations from a column 'Categories' and get the ID? TIA, Victor
Re: Second Request: Challenging Select Statement
On Mon, Dec 7, 2009 at 8:08 AM, Tom Worster f...@thefsb.org wrote: On 12/7/09 5:26 AM, Victor Subervi victorsube...@gmail.com wrote: Hi; I posted this Saturday. Perhaps it's too challenging for those who read it to answer. I hope someone can. I need to write a select statement that enables me to select column 'ID' from a table where a certain value is found in an enum of a specific column. For example... select column_type from information_schema.columns where table_name='products' and column_name='Categories'; will give me the column and its enumerations. How do I supply one of the enumerations from a column 'Categories' and get the ID? WHERE col LIKE '%val%' i didn't answer this before not because it is challenging. on the contrary. i didn't answer because it is explained in the manual and on any number of easily found web pages. when you apparently don't make an effort to find the answer for yourself before you ask us, i don't feel much like making the effort to answer. I don't have the familiarity you have to see what is obvious only in hindsight. Had it been obvious in foresight, I would indeed have googled and not bothered the list. When one doesn't know enough to even properly formulate the question, however, it is a different matter. Please be understanding. Thank you. V
Select Problem
Hi; I have the following: mysql select * from categoriesProducts as c inner join relationshipProducts as r on c.ID = r.Child inner join categoriesProducts as p on r.Parent = p.ID where p.Category = prodCat2; ERROR 1054 (42S22): Unknown column 'prodCat2' in 'where clause' mysql describe categoriesProducts; +--+-+--+-+-++ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-++ | ID | int(3) unsigned | NO | PRI | NULL| auto_increment | | Category | varchar(40) | YES | | NULL|| | Parent | varchar(40) | YES | | NULL|| +--+-+--+-+-++ 3 rows in set (0.00 sec) mysql select * from categoriesProducts; ++--++ | ID | Category | Parent | ++--++ | 1 | prodCat1 | None | | 2 | prodCat2 | None | ++--++ 2 rows in set (0.00 sec) So I'm at a loss. No, 'prodCat2' isn't a column, but I don't understand how I specified that in my query. Please advise. TIA, Victor
Like Syntax
Hi; I remember vaguely how to do this but don't know how to google it: show tables like categories$; such that it will return tables such as: categoriesProducts, categoriesPrescriptions, etc. TIA, Victor
Re: Like Syntax
On Sat, Dec 5, 2009 at 11:09 AM, Michael Dykman mdyk...@gmail.com wrote: show tables like 'categories%'; Thanks. V
Challenging Select Statement
Hi; I need to write a select statement that enables me to select column 'ID' from a table where a certain value is found in an enum of a specific column. For example... select column_type from information_schema.columns where table_name='products' and column_name='Categories'; will give me the column and its enumerations. How do I supply one of the enumerations from a column 'Categories' and get the ID? TIA, Victor
Auto Increment Problem
Hi;
mysql insert into categories (Category, Parent) values ('test', NULL);
ERROR 1062 (23000): Duplicate entry '0' for key 1
mysql describe categories;
+--+-+--+-+-+---+
| Field| Type| Null | Key | Default | Extra |
+--+-+--+-+-+---+
| ID | int(3) unsigned | NO | PRI | NULL| |
| Category | varchar(40) | YES | | NULL| |
| Parent | varchar(40) | YES | | NULL| |
+--+-+--+-+-+---+
3 rows in set (0.00 sec)
Why the error?
TIA,
Victor
Re: Auto Increment Problem
Oops. Never mind.
V
On Sat, Dec 5, 2009 at 1:19 PM, Victor Subervi victorsube...@gmail.comwrote:
Hi;
mysql insert into categories (Category, Parent) values ('test', NULL);
ERROR 1062 (23000): Duplicate entry '0' for key 1
mysql describe categories;
+--+-+--+-+-+---+
| Field| Type| Null | Key | Default | Extra |
+--+-+--+-+-+---+
| ID | int(3) unsigned | NO | PRI | NULL| |
| Category | varchar(40) | YES | | NULL| |
| Parent | varchar(40) | YES | | NULL| |
+--+-+--+-+-+---+
3 rows in set (0.00 sec)
Why the error?
TIA,
Victor
Creating Table Through Union
Hi; I would like to create a table out of merging the fields in other, previously created tables. I have the following syntax which doesn't work: create table products union (b0basics, b0fieldValues, s0prescriptions, s0prescriptions0doctors, s0prescriptions0patient, pics); Please advise. TIA, Victor
Re: Creating Table Through Union
On Sun, Nov 22, 2009 at 9:29 PM, Gavin Towey gto...@ffn.com wrote: The form would be like: CREATE TABLE products SELECT b0basics, b0fieldValues, s0prescriptions, s0prescriptions0doctors, s0prescriptions0patient, pics FROM table1 UNION SELECT b0basics, b0fieldValues, s0prescriptions, s0prescriptions0doctors, s0prescriptions0patient, pics FROM table2 Thank you. V
Re: Foreign Keys Problem
On Thu, Nov 19, 2009 at 9:34 PM, Ye Yuan yuan4...@gmail.com wrote: Hi Victor, It looks to me the foreign key syntax is wrong. Can you create the Relationship table on your database by using below ddl? create table if not exists Relationship (ID integer auto_increment primary key, Parent integer not null, foreign key (Parent) references categories (ID), Child integer not null, foreign key (Child) references categories (ID), check (Parent Child) ); Yes, I can create it. Thank you. V
Foreign Keys Problem
Hi;
I don't claim to be an expert in MySQL. The following code was largely
supplied to me by someone who was. I don't really understand foreign keys.
He wrote this off the top of his head, and it's throwing an error. Here's
the python code:
def catTree():
user, passwd, db, host = login()
database = MySQLdb.connect(host, user, passwd, db)
cursor = database.cursor()
cursor.execute('''create table if not exists categories
(ID int(3) unsigned primary key,
Category varchar(40),
Parent varchar(40))''')
cursor.execute('select Category, Parent from categories;')
data = cursor.fetchall()
cursor.execute('select Category from categories order by Parent, ID')
print data
Categories = [itm[0] for itm in cursor] #untuple single column
if len(Categories) 0:
cursor.execute('select Parent from categories order by Parent, ID')
Parents = [itm[0] for itm in cursor]
MAXLEVEL = 15
cursor.execute('''create table if not exists categories
(ID integer auto_increment primary key,
Name varchar(40) not null,
unique (Name)
)''')
cursor.execute('''create table if not exists Relationship
(ID integer auto_increment primary key,
Parent integer not null foreign key references categories (ID),
Child integer not null foreign key references categories (ID),
check (Parent Child) );''')
# get top level
print 'ok'
cursor.execute('select Name from categories order by Name')
theTree = expand(cursor.fetchall())
getChildren(theTree)
connection.commit()
return printTree(theTree)
else:
return ['There are no categories yet.']
Here's the error:
[Thu Nov 19 10:59:24 2009] [error] [client 208.84.198.58] from catTree
import catTree
[Thu Nov 19 10:59:24 2009] [error] [client 208.84.198.58] File
/var/www/html/angrynates.com/cart/catTree.py, line 74, in ?
[Thu Nov 19 10:59:24 2009] [error] [client 208.84.198.58] catTree()
[Thu Nov 19 10:59:24 2009] [error] [client 208.84.198.58] File
/var/www/html/angrynates.com/cart/catTree.py, line 59, in catTree
[Thu Nov 19 10:59:24 2009] [error] [client 208.84.198.58]
cursor.execute('''create table if not exists Relationship
[Thu Nov 19 10:59:24 2009] [error] [client 208.84.198.58] File
/usr/lib64/python2.4/site-packages/MySQLdb/cursors.py, line 163, in
execute
[Thu Nov 19 10:59:24 2009] [error] [client 208.84.198.58]
self.errorhandler(self, exc, value)
[Thu Nov 19 10:59:24 2009] [error] [client 208.84.198.58] File
/usr/lib64/python2.4/site-packages/MySQLdb/connections.py, line 35, in
defaulterrorhandler
[Thu Nov 19 10:59:24 2009] [error] [client 208.84.198.58] raise
errorclass, errorvalue
[Thu Nov 19 10:59:24 2009] [error] [client 208.84.198.58]
_mysql_exceptions.ProgrammingError: (1064, You have an error in your SQL
syntax; check the manual that corresponds to your MySQL server version for
the right syntax to use near 'foreign key references categories
(ID),\\n Child integer not null foreign key' at line 3)
Please advise.
TIA,
Victor
Grant Privileges Problem
Hi; I created a user and then granted privileges: grant all to victor identified by 'pw'; Looked good. Tried to log in as victor an no go. Please advise. Victor
Re: Grant Privileges Problem
Never mind. Had to add @'localhost' On Mon, Nov 9, 2009 at 4:20 PM, Victor Subervi victorsube...@gmail.comwrote: Hi; I created a user and then granted privileges: grant all to victor identified by 'pw'; Looked good. Tried to log in as victor an no go. Please advise. Victor
Re: Inserting an Image
Well, I've spent the last 2 days chasing my tail just to discover that there
was some screwy python deal going on. The following code works:
sql = update productsX set Name='%s', Title='%s', Description='%s',
Price='%s', Bedrooms='%s', Bathrooms='%s', Conditions='%s', Acreage='%s',
Construction='%s', Location='%s', Estate='%s', Address='%s', Furnished='%s'
where ID=%s; % (name, title, description, price, bedrooms, bathrooms,
conditions, acreage, construction, location, estate, address, furnished, id)
cursor.execute(sql)
cursor.execute('update productsX set pic1=%s, pic2=%s, pic3=%s,
pic4=%s, pic5=%s, pic6=%s where ID=%s;', (pic1, pic2, pic3, pic4, pic5,
pic6, id))
Notice I have to use two separate sql calls and the only differences between
them are that the first have the strings in quotes AND there is a % sign
between the string widgets and the variables that replace them. That's it!
Wonder what the *#^%( difference that makes.
V
On Thu, Oct 15, 2009 at 4:54 PM, Michael Dykman mdyk...@gmail.com wrote:
Victor,
again, your question has more to do with python usage than MySQL
per-se.. you would be better off pursuing these questions in a python
forum.
- michael dykman
On Thu, Oct 15, 2009 at 3:28 PM, Gavin Towey gto...@ffn.com wrote:
Image in string form sounds like you're not inserting binary data,
rather some sort of encoded data. Even if it is binary, you'll have to
escape at least end quote characters, you can see clearly at the top of your
data there is :
'ÿØÿà JFIF ÿÛC $(4,$1'
Another thing to think about is storing image data directly in a database
is often not a good idea. See these links for more info:
http://mysqldump.azundris.com/archives/36-Serving-Images-From-A-Database.htmland
http://hashmysql.org/index.php?title=Storing_files_in_the_database
Regards,
Gavin Towey
-Original Message-
From: Victor Subervi [mailto:victorsube...@gmail.com]
Sent: Thursday, October 15, 2009 12:05 PM
To: mysql@lists.mysql.com
Subject: Inserting an Image
Hi;
I have successfully inserted images, like yesterday, before into MySQL
with
the following code:
sql = 'update productsX set pic1=%s where ID=2;' % pic1
cursor.execute(sql)
where pic1 is simply an image uploaded through a form then sent over
without
any alteration to another (python) script that uploads it. Printing it
out
looks like this (other variables included):
update productsX set Name=%s, Title=%s, Description=%s, Price=%s,
Bedrooms=%s, Bathrooms=%s, Conditions=%s, Acreage=%s, Construction=%s,
Location=%s, Estate=%s, Address=%s, Furnished=%s, pic1=%s, pic2=%s,
pic3=%s,
pic4=%s, pic5=%s, pic6=%s where ID=%s;', ('name1', 'title1', 'descr1',
'1.1', '2', '1', 'New', '1.5', 'New', 'arbor', 'abor', 'abor',
'Furnished',
'ÿØÿà JFIF ÿÛC $(4,$1' -=-157:::#+?D?8C49:7ÿÛC 7%
%77ÿÀ y| ÿÄ ÿÄM !1A Qa
qs
³ 27Bbc‚ #46Rrt‘¡±² $'3C’%5DTUƒ“¢£ÑñÿÄ ÿÄ ÿÚ ?œ*‹RP’¥¨% d’pªÔcµkä«”øZ Âá
îj
Xêxö, `...@$ý %6...@$ý ô¨2mÒžÚ ¦ ÚRÑ¥¬ò guD|!%bO :ðç’ Z…æµËÚvsM¡·¢T ‘œ¬
ºâ
“ñ â1Ò¶¶KT[%¦-² {‘£6 Ô÷“âNI=äÒfÓ ø/RhËîR”³q1 QüÇ“ƒŸ ƒE Š Š( (¢Š Š( (¢Š Š(
×ê
´{ –mÒYü VTâ†pUŽI $à Qvà ɼÜ/ZÒì{IrÝ,´£È x ß #¸$Š·÷F_Ë ë}……áRTdH ó
Á#ÐU“ö*DÙõ¨Yt]ž îêÑ +p}5ùêÿ¹F † vã Êöy5Ô‚\ˆëO Žc 'ö(Óõku-´^4õÊÚ Ú£8Ðð%$
ì84
Ú*ø G¥·T Vû#µc æ ·uÏ; ÖÓusN\\Ü‹5{ÑʸvoòÝû@ é ¼×CP U
¤¡%KPJR2I8T+´½¨®{ß{:)kzD‡; 2~1' ¤“ ïÙßA¶Õº®íª5 Ñú AiMçá ’O ’8 •tÆpHâNÇ
Uºl¤Ûmë¸é«ÝÙ øéí û’7�...@guc q Ó ŠhÙ¶ cFØ Í\çðäLJÎ^8$} ä í=i² Aj ©ôœ «‰J_u
/¥ƒ‰%*áÓˆÎ;ˆ¦ TÐö©6)WûjØZ`ü dÂsæ–ÝHQB E@ m5Ð QE EbÜî
íP]›q’Üx̧yn8pÿ߇3IP5–¨¿´©úgL2å©K)aéÒû ¼ 5 ã‚s 8žT æÕæ*ýµ)Q’ä4óp› ¦0
?ÌU]B
r + ç\»]_éŸ pTŒŸXU] Èò “¬gK ®tTxò^i‰ É 4‡ Rè ¤ à8 Y«›` üM ]ߊ󌼀ÑK
,¥Iü29 ƃž6™iU‡]Ý£ È/²G 5~xÇ£8öT۱͡§SÀ ›«¿éˆÈॠÆ[ ;ô‡Qן~4»vÒOܬðu ”ëÐÙ
Ë
%®a d“Ÿ g ¨Ãe1. õõœÛ æû2 ëËG$49' )$xç h3ö‰´Ë® ôHî.%œ,†ã ຠx S× ‡Ž3Nû
ÐËAûêº5Œ‚ˆ ¨qÁৠˆ ÓÜh±ìËOÏÚV¢‰ `ÛTÃÅß «.¤¨¥Gžè€ãŽg½ãPìM£h¸1 [
ÌÄ®;J)mA-
ÐR8 :g• ãXjû ‚™W‡Ô „†Xho8é ÷G‡yÀâ8ñ ‡¤õ5ëQ¥‰Ÿ{žCjxo7LÑÚ à ØG#Ú‡ ‘ž¨ t
–›-LjFžhÈÂXy¥(… \8) ŽJð} -ÜKp¬’ T–PÔu œî €“Œ ” ôTc¢ö…§íZ Ò«õùµMì
h‚µöwŽ7€É
1Î’šÛ27 ó ;= b*Sê e Æ7xž|p è:@Ö[YÓÚq.1 Ñs¸'€b2 BOÓ_!è ¡
¦²Özòbm©}÷ƒêÂ`ÂFâ ¤ $ õ Š•vq±è–bÍÏR†æ\ ˆÃÎiƒãùê °tÏ A‡§4Þ ÚÆoÚíkfÐ… õ¤
%ÁÑEÂ|Oœ¯ ŒÌM4Û-!¦ ”6€ ”$`$ @ ‚½t¢ƒˆ_Bš}Æ× ä(¥Yç k®vqyM÷DÚf…ï9äéiîþÑ
j³é#‚+™ö‘lU§]^¢) 'Ê”êüÅùéýÊ ñ÷?jÔÛ®¯iÙ®nÇœøÅG‚^ ˆû@ j...@ëa#럔 ú̯všÈÛGÉ
ë†|Ö½ò*θÇßö„â3å2º}ZjþÙ¾M/_ ß½E t_Ä™õiþ °ÖZogV÷ m qŽ¾´¤
¼:°2zð律¿‰³êÓü)/bC
7¶ Ë´ ß.€Ò ÿJ:ÛÕÀ÷J«ZÍá i:9ò•(5 ಔŽ' ƒŠ½¥‡ö¡x ‡ Ž©UMT3µ- ×ðSýÐ 5†Ñ5
«.³6Yf Õ‘ ºØ‚ ê¬ ½Ô±T‰Kß’û¯+½Å• ßSVÕö[c´Yn ŠÔãñ”…¡F Âšó–” Þ© vq“ÝÀVÝ
Óp!?D«„· aj kJ ¼ xà óñ çÆ vC¨e†Öë‹8J ’¢£à :~Ùö˦ê‰Ó ¹H6æ 8–ä6¤ öñ Ýð
:ž]ƦM
Zíñ´%®lxqÛ”ûJ/†ÀZüõsW3Ê«³ÒF©Öé…Ñ'ö¢ ƒKi[6•…äÖhik{ £ªóœt÷©\Ï£ è
nv´¡%...@ÉrŽ«sfg Ùs^C1ÙI[Ž,à$ ¦¢ØÓåm_Pöl¡Ö4m¹à§w I¸:8„Ÿ£Èîô ø‘º Í t¢‚
û¢t–˜š–*2 ix †r… i)ÏèÔ Ë®0ò ejmÆÔ …¤à¤Ž ƒß]«r ë E¾sIz4†Ën¶z¤ÿ Mr¶Ñt ý
r)XSö×T|šVï ôUÜ¡ûù ‘lÚÑ:Æû ]x¡7 Ò¤·1± Ù , CÐGJzÛ ÎÍoCêÛ
Inserting an Image
Hi;
I have successfully inserted images, like yesterday, before into MySQL with
the following code:
sql = 'update productsX set pic1=%s where ID=2;' % pic1
cursor.execute(sql)
where pic1 is simply an image uploaded through a form then sent over without
any alteration to another (python) script that uploads it. Printing it out
looks like this (other variables included):
update productsX set Name=%s, Title=%s, Description=%s, Price=%s,
Bedrooms=%s, Bathrooms=%s, Conditions=%s, Acreage=%s, Construction=%s,
Location=%s, Estate=%s, Address=%s, Furnished=%s, pic1=%s, pic2=%s, pic3=%s,
pic4=%s, pic5=%s, pic6=%s where ID=%s;', ('name1', 'title1', 'descr1',
'1.1', '2', '1', 'New', '1.5', 'New', 'arbor', 'abor', 'abor', 'Furnished',
'ÿØÿà JFIF ÿÛC $(4,$1' -=-157:::#+?D?8C49:7ÿÛC 7%
%77ÿÀ y| ÿÄ ÿÄM !1A Qa qs
³ 27Bbc‚ #46Rrt‘¡±² $'3C’%5DTUƒ“¢£ÑñÿÄ ÿÄ ÿÚ ?œ*‹RP’¥¨% d’pªÔcµkä«”øZ Âá îj
Xêxö, `...@$ý %6...@$ý ô¨2mÒžÚ ¦ ÚRÑ¥¬ò guD|!%bO :ðç’ Z…æµËÚvsM¡·¢T ‘œ¬ ºâ
“ñ â1Ò¶¶KT[%¦-² {‘£6 Ô÷“âNI=äÒfÓ ø/RhËîR”³q1 QüÇ“ƒŸ ƒE Š Š( (¢Š Š( (¢Š Š( ×ê
´{ –mÒYü VTâ†pUŽI $à Qvà ɼÜ/ZÒì{IrÝ,´£È x ß #¸$Š·÷F_Ë ë}……áRTdH ó
Á#ÐU“ö*DÙõ¨Yt]ž îêÑ +p}5ùêÿ¹F † vã Êöy5Ô‚\ˆëO Žc 'ö(Óõku-´^4õÊÚ Ú£8Ðð%$ ì84
Ú*ø G¥·T Vû#µc æ ·uÏ; ÖÓusN\\Ü‹5{ÑʸvoòÝû@ é ¼×CP U
¤¡%KPJR2I8T+´½¨®{ß{:)kzD‡; 2~1' ¤“ ïÙßA¶Õº®íª5 Ñú AiMçá ’O ’8 •tÆpHâNÇ
Uºl¤Ûmë¸é«ÝÙ øéí û’7�...@guc q Ó ŠhÙ¶ cFØ Í\çðäLJÎ^8$} ä í=i² Aj ©ôœ «‰J_u
/¥ƒ‰%*áÓˆÎ;ˆ¦ TÐö©6)WûjØZ`ü dÂsæ–ÝHQB E@ m5Ð QE EbÜî
íP]›q’Üx̧yn8pÿ߇3IP5–¨¿´©úgL2å©K)aéÒû ¼ 5 ã‚s 8žT æÕæ*ýµ)Q’ä4óp› ¦0 ?ÌU]B
r + ç\»]_éŸ pTŒŸXU] Èò “¬gK ®tTxò^i‰ É 4‡ Rè ¤ à8 Y«›` üM ]ߊ󌼀ÑK
,¥Iü29 ƃž6™iU‡]Ý£ È/²G 5~xÇ£8öT۱͡§SÀ ›«¿éˆÈॠÆ[ ;ô‡Qן~4»vÒOܬðu ”ëÐÙ Ë
%®a d“Ÿ g ¨Ãe1. õõœÛ æû2 ëËG$49' )$xç h3ö‰´Ë® ôHî.%œ,†ã ຠx S× ‡Ž3Nû
ÐËAûêº5Œ‚ˆ ¨qÁৠˆ ÓÜh±ìËOÏÚV¢‰ `ÛTÃÅß «.¤¨¥Gžè€ãŽg½ãPìM£h¸1 [ ÌÄ®;J)mA-
ÐR8 :g• ãXjû ‚™W‡Ô „†Xho8é ÷G‡yÀâ8ñ ‡¤õ5ëQ¥‰Ÿ{žCjxo7LÑÚ à ØG#Ú‡ ‘ž¨ t
–›-LjFžhÈÂXy¥(… \8) ŽJð} -ÜKp¬’ T–PÔu œî €“Œ ” ôTc¢ö…§íZ Ò«õùµMì h‚µöwŽ7€É
1Î’šÛ27 ó ;= b*Sê e Æ7xž|p è:@Ö[YÓÚq.1 Ñs¸'€b2 BOÓ_!è ¡
¦²Özòbm©}÷ƒêÂ`ÂFâ ¤ $ õ Š•vq±è–bÍÏR†æ\ ˆÃÎiƒãùê °tÏ A‡§4Þ ÚÆoÚíkfÐ… õ¤
%ÁÑEÂ|Oœ¯ ŒÌM4Û-!¦ ”6€ ”$`$ @ ‚½t¢ƒˆ_Bš}Æ× ä(¥Yç k®vqyM÷DÚf…ï9äéiîþÑ
j³é#‚+™ö‘lU§]^¢) 'Ê”êüÅùéýÊ ñ÷?jÔÛ®¯iÙ®nÇœøÅG‚^ ˆû@ j...@ëa#럔 ú̯všÈÛGÉ
ë†|Ö½ò*θÇßö„â3å2º}ZjþÙ¾M/_ ß½E t_Ä™õiþ °ÖZogV÷ m qŽ¾´¤ ¼:°2zð律¿‰³êÓü)/bC
7¶ Ë´ ß.€Ò ÿJ:ÛÕÀ÷J«ZÍá i:9ò•(5 ಔŽ' ƒŠ½¥‡ö¡x ‡ Ž©UMT3µ- ×ðSýÐ 5†Ñ5
«.³6Yf Õ‘ ºØ‚ ê¬ ½Ô±T‰Kß’û¯+½Å• ßSVÕö[c´Yn ŠÔãñ”…¡F Âšó–” Þ© vq“ÝÀVÝ
Óp!?D«„· aj kJ ¼ xà óñ çÆ vC¨e†Öë‹8J ’¢£à :~Ùö˦ê‰Ó ¹H6æ 8–ä6¤ öñ Ýð :ž]ƦM
Zíñ´%®lxqÛ”ûJ/†ÀZüõsW3Ê«³ÒF©Öé…Ñ'ö¢ ƒKi[6•…äÖhik{ £ªóœt÷©\Ï£ è
nv´¡%...@ÉrŽ«sfg Ùs^C1ÙI[Ž,à$ ¦¢ØÓåm_Pöl¡Ö4m¹à§w I¸:8„Ÿ£Èîô ø‘º Í t¢‚
û¢t–˜š–*2 ix †r… i)ÏèÔ Ë®0ò ejmÆÔ …¤à¤Ž ƒß]«r ë E¾sIz4†Ën¶z¤ÿ Mr¶Ñt ý
r)XSö×T|šVï ôUÜ¡ûù ‘lÚÑ:Æû ]x¡7 Ò¤·1± Ù , CÐGJzÛ ÎÍoCêÛ¾µ l‡å
Çë•üŠ©ûlc;6½z¶ýê( bþÏ«O𤽉üœ[}cþùtç b ‡ ÓËÐ)7bß'vÿ[#ß.€ÒÃûQÖ¼? Ç
VªóªømOCäsn~?é ®™9Úž²çýÄ ŸVkΫùTÐÞ® ¹ ÷¶¿“k¯¥Ÿ|Šm›Ÿ‚_#ýÝXéóiOm_· ùù Ëw—
†Có$µ Á ÇV Jxq4 û 9Ùµ ýRýâézϪ- V÷®fÞ%%¤ü$žÍ´ñqÓ¹É)ê pêE$Z6·÷¹ íÖK4NÖâÓK
H|a¶‰ZˆÂ~qÁë éåKÚ;J^v“¨dJ’ú».Ð9:jÀញ TqÀr ^[¨öÏ}òV·íÚr2ÂœJx„þ‘ùî ƒ ý¤Î–[L+
²=¶ØÂYŠÂwP‘ûÉ=I™ŒÍ!aÓ»IÑŒZ-0ÓÞZ§BŠœß)g)'xžGˆ©1‹d(÷ 7 c6‰’’„¾ð rÂF Ud[aI
sñ›rTMÿ'uCÎo|aXôŽ [iù5¼qèÏ·ðȦ¹ ꇿWWòÕÉðb\¢9 á ™1œÆû/ )*Á d ²¯) R jH(# $pÇu
nÇ3ý ÙsŸîÜçëWVt F±× ‰ ^Ñâ¬Ó ¾ Kd6áÛã5 3...@„4Òwrœ’n ¤“í¢-º I dEŒÓOJP[î!8SŠ
¨õáA“E P QE H»QÙë Ò ^Œ[bï $0ò‡ Ïq~ â BOy§ª(8®óg¸Øç. Ú#±d ñCƒŸˆˆñ *FûŸ,
ŸªÝ»¸Ùò{sGu];U Ð?˾ eH»vü ¦¯å5] ~@'õ§?‚h$Š(¢€¢Š( (¢€¢©U (ªUh (¢ƒÿÙ', '', '',
'', '', '', '')
MySQL complains about the image in string form. I'm sure the solution is
simple, but I don't know what it is.
TIA,
Victor
Re: No tuples, but AttributeError about tuple!
You were right about the % I'd forgotten. Still, I got the same error. So I
tried to change the command to this:
insert into products (ID, Name, Title, Description, Price, Bedrooms,
Bathrooms, Conditions, Acreage, Construction, Location, Estate, Address,
Furnished, pic1, pic2, pic3, pic4, pic5, pic6) values(1, name1,
title1, descr1, 1.1, 2, 1, New, 1.5, new, princesse,
princesse, 123 princesse, Not furnished, , , , , , );
Here's the table description:
(
ID int(3) unsigned primary key auto_increment,
Name varchar(40),
Title varchar(3),
Description varchar(512),
Price float(10,2),
Bedrooms int(1) unsigned,
Bathrooms int(1) unsigned,
Conditions varchar(255),
Acreage float(5,2),
Construction set('New','Existing'),
Location varchar(256),
Estate varchar(60),
Address varchar(100),
Furnished set('Furnished','Unfurnished'),
pic1 blob,
pic2 blob,
pic3 blob,
pic4 blob,
pic5 blob,
pic6 blob
)
Now I get this error:
[Mon Oct 05 13:23:36 2009] [error] [client 66.248.168.98] PythonHandler
mod_python.cgihandler: Traceback (most recent call last):, referer:
http://13gems.com/stxresort/cart/iud.py
[Mon Oct 05 13:23:36 2009] [error] [client 66.248.168.98] PythonHandler
mod_python.cgihandler: File
/usr/lib64/python2.4/site-packages/mod_python/apache.py, line 299, in
HandlerDispatch\n result = object(req), referer:
http://13gems.com/stxresort/cart/iud.py
[Mon Oct 05 13:23:36 2009] [error] [client 66.248.168.98] PythonHandler
mod_python.cgihandler: File
/usr/lib64/python2.4/site-packages/mod_python/cgihandler.py, line 96, in
handler\n imp.load_module(module_name, fd, path, desc), referer:
http://13gems.com/stxresort/cart/iud.py
[Mon Oct 05 13:23:36 2009] [error] [client 66.248.168.98] PythonHandler
mod_python.cgihandler: File /var/www/vhosts/
13gems.com/httpdocs/stxresort/cart/add_edit.py, line 360, in ?\n
add_edit(), referer: http://13gems.com/stxresort/cart/iud.py
[Mon Oct 05 13:23:36 2009] [error] [client 66.248.168.98] PythonHandler
mod_python.cgihandler: File /var/www/vhosts/
13gems.com/httpdocs/stxresort/cart/add_edit.py, line 314, in add_edit\n
cursor.execute(sql), referer: http://13gems.com/stxresort/cart/iud.py
[Mon Oct 05 13:23:36 2009] [error] [client 66.248.168.98] PythonHandler
mod_python.cgihandler: File
/usr/lib64/python2.4/site-packages/MySQLdb/cursors.py, line 163, in
execute\n self.errorhandler(self, exc, value), referer:
http://13gems.com/stxresort/cart/iud.py
[Mon Oct 05 13:23:36 2009] [error] [client 66.248.168.98] PythonHandler
mod_python.cgihandler: File
/usr/lib64/python2.4/site-packages/MySQLdb/connections.py, line 35, in
defaulterrorhandler\n raise errorclass, errorvalue, referer:
http://13gems.com/stxresort/cart/iud.py
[Mon Oct 05 13:23:36 2009] [error] [client 66.248.168.98] PythonHandler
mod_python.cgihandler: OperationalError: (1054, Unknown column 'Name' in
'field list'), referer: http://13gems.com/stxresort/cart/iud.py
Is it me, or does this actually make sense?
TIA,
V
On Sun, Oct 4, 2009 at 7:20 PM, Michael Dykman mdyk...@gmail.com wrote:
I assume you are using MySQLdb.
to clarify, I trust you know that this is a tuple:
(id, name, title,
description, price, bedrooms, bathrooms, conditions, acreage,
construction,
location, estate, address, furnished, pic1, pic2, pic3, pic4, pic5, pic6)
..
I'm not exactly an expert in python but I don't follow this statement:
sql = 'insert into products values(%s, %s, %s, %s, %s, %s, %s, %s,
%s,
%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);', (id, name, title,
description, price, bedrooms, bathrooms, conditions, acreage,
construction,
location, estate, address, furnished, pic1, pic2, pic3, pic4, pic5, pic6)
which is constructed as a = b,c and doesn't do what you think it does
.. if you were looking to do a text sprintf thing, it would be more
commonly
sql = 'insert into products values(%s, %s, %s, %s, %s, %s, %s, %s,
%s,
%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);' % (id, name, title,
description, price, bedrooms, bathrooms, conditions, acreage,
construction,
location, estate, address, furnished, pic1, pic2, pic3, pic4, pic5, pic6)
which will not enquote your values. note the '%' operator between the
string and the tuple.
what you want, I think is the 2 param form of cursor.execute() which
takes a string statement and a tuple of arguments:
cursor.execute('insert into products values(%s, %s, %s, %s, %s, %s, %s, %s,
%s,
%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)' , (id, name, title,
description, price, bedrooms, bathrooms, conditions, acreage,
construction,
location, estate, address, furnished, pic1, pic2, pic3, pic4, pic5, pic6))
conn.commit()
execute knows how to address the various data types in the tuple and
will escape/enquote as necessary.
- michael
Since you are
On Sun, Oct 4, 2009 at 2:04 PM, Victor Subervi victorsube...@gmail.com
wrote:
Hi;
I have the following python code:
sql = 'insert into products values(%s, %s, %s, %s, %s, %s, %s, %s,
%s,
%s, %s, %s
Re: No tuples, but AttributeError about tuple!
Same error :(
On Mon, Oct 5, 2009 at 4:26 PM, DaWiz da...@dawiz.net wrote:
Vic tor,
Try this instead:
insert into products (ID, Name, Title, Description, Price, Bedrooms,
Bathrooms, Conditions, Acreage, Construction, Location, Estate, Address,
Furnished, pic1, pic2, pic3, pic4, pic5, pic6) values('1', 'name1',
'title1', 'descr1', '1.1', '2', '1', 'New', '1.5', 'new', 'princesse',
'princesse', '123 princesse', 'Not furnished', '', '', '', '', '', '');
Values are enclosed in single quotes for SQL.
- Original Message - From: Victor Subervi
victorsube...@gmail.com
To: Michael Dykman mdyk...@gmail.com; mysql@lists.mysql.com
Sent: Monday, October 05, 2009 2:33 PM
Subject: Re: No tuples, but AttributeError about tuple!
You were right about the % I'd forgotten. Still, I got the same error. So I
tried to change the command to this:
insert into products (ID, Name, Title, Description, Price, Bedrooms,
Bathrooms, Conditions, Acreage, Construction, Location, Estate, Address,
Furnished, pic1, pic2, pic3, pic4, pic5, pic6) values(1, name1,
title1, descr1, 1.1, 2, 1, New, 1.5, new, princesse,
princesse, 123 princesse, Not furnished, , , , , , );
Here's the table description:
(
ID int(3) unsigned primary key auto_increment,
Name varchar(40),
Title varchar(3),
Description varchar(512),
Price float(10,2),
Bedrooms int(1) unsigned,
Bathrooms int(1) unsigned,
Conditions varchar(255),
Acreage float(5,2),
Construction set('New','Existing'),
Location varchar(256),
Estate varchar(60),
Address varchar(100),
Furnished set('Furnished','Unfurnished'),
pic1 blob,
pic2 blob,
pic3 blob,
pic4 blob,
pic5 blob,
pic6 blob
)
Now I get this error:
[Mon Oct 05 13:23:36 2009] [error] [client 66.248.168.98] PythonHandler
mod_python.cgihandler: Traceback (most recent call last):, referer:
http://13gems.com/stxresort/cart/iud.py
[Mon Oct 05 13:23:36 2009] [error] [client 66.248.168.98] PythonHandler
mod_python.cgihandler: File
/usr/lib64/python2.4/site-packages/mod_python/apache.py, line 299, in
HandlerDispatch\n result = object(req), referer:
http://13gems.com/stxresort/cart/iud.py
[Mon Oct 05 13:23:36 2009] [error] [client 66.248.168.98] PythonHandler
mod_python.cgihandler: File
/usr/lib64/python2.4/site-packages/mod_python/cgihandler.py, line 96, in
handler\n imp.load_module(module_name, fd, path, desc), referer:
http://13gems.com/stxresort/cart/iud.py
[Mon Oct 05 13:23:36 2009] [error] [client 66.248.168.98] PythonHandler
mod_python.cgihandler: File /var/www/vhosts/
13gems.com/httpdocs/stxresort/cart/add_edit.py, line 360, in ?\n
add_edit(), referer: http://13gems.com/stxresort/cart/iud.py
[Mon Oct 05 13:23:36 2009] [error] [client 66.248.168.98] PythonHandler
mod_python.cgihandler: File /var/www/vhosts/
13gems.com/httpdocs/stxresort/cart/add_edit.py, line 314, in add_edit\n
cursor.execute(sql), referer: http://13gems.com/stxresort/cart/iud.py
[Mon Oct 05 13:23:36 2009] [error] [client 66.248.168.98] PythonHandler
mod_python.cgihandler: File
/usr/lib64/python2.4/site-packages/MySQLdb/cursors.py, line 163, in
execute\n self.errorhandler(self, exc, value), referer:
http://13gems.com/stxresort/cart/iud.py
[Mon Oct 05 13:23:36 2009] [error] [client 66.248.168.98] PythonHandler
mod_python.cgihandler: File
/usr/lib64/python2.4/site-packages/MySQLdb/connections.py, line 35, in
defaulterrorhandler\n raise errorclass, errorvalue, referer:
http://13gems.com/stxresort/cart/iud.py
[Mon Oct 05 13:23:36 2009] [error] [client 66.248.168.98] PythonHandler
mod_python.cgihandler: OperationalError: (1054, Unknown column 'Name' in
'field list'), referer: http://13gems.com/stxresort/cart/iud.py
Is it me, or does this actually make sense?
TIA,
V
On Sun, Oct 4, 2009 at 7:20 PM, Michael Dykman mdyk...@gmail.com wrote:
I assume you are using MySQLdb.
to clarify, I trust you know that this is a tuple:
(id, name, title,
description, price, bedrooms, bathrooms, conditions, acreage,
construction,
location, estate, address, furnished, pic1, pic2, pic3, pic4, pic5, pic6)
..
I'm not exactly an expert in python but I don't follow this statement:
sql = 'insert into products values(%s, %s, %s, %s, %s, %s, %s, %s,
%s,
%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);', (id, name, title,
description, price, bedrooms, bathrooms, conditions, acreage,
construction,
location, estate, address, furnished, pic1, pic2, pic3, pic4, pic5,
pic6)
which is constructed as a = b,c and doesn't do what you think it does
.. if you were looking to do a text sprintf thing, it would be more
commonly
sql = 'insert into products values(%s, %s, %s, %s, %s, %s, %s, %s,
%s,
%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);' % (id, name, title,
description, price, bedrooms, bathrooms, conditions, acreage,
construction,
location, estate, address, furnished, pic1, pic2, pic3, pic4, pic5, pic6)
which will not enquote your values. note the '%' operator
No tuples, but AttributeError about tuple!
Hi;
I have the following python code:
sql = 'insert into products values(%s, %s, %s, %s, %s, %s, %s, %s, %s,
%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);', (id, name, title,
description, price, bedrooms, bathrooms, conditions, acreage, construction,
location, estate, address, furnished, pic1, pic2, pic3, pic4, pic5, pic6)
cursor.execute(sql)
which, when printed to screen, gives the following:
insert into products values ('1', 'name1', 'title1', 'descr1', '1.1', '2',
'1', 'New', '1.5', 'new', 'princesse', 'princesse', '123 princesse', 'Not
furnished', '', '', '', '', '', '');
which I can enter into the database directly. However, when I try to do it
through the script, I get the following error:
AttributeError: 'tuple' object has no attribute 'encode'
Why is that? There are no tuples here!! Nothing but strings!!
TIA,
Victor
Re: Nested Joins
Thanks! Got it! I was missing the word from...
select * FROM %s c ...
Thanks again ;)
V
On Thu, Oct 1, 2009 at 5:08 PM, Gavin Towey gto...@ffn.com wrote:
Victor,
Just noticed, the join condition from client to productions should be
changed:
select * from ben_franklin_planners c join products p on c.Item=p.ID join
categories cat on p.Category=cat.ID
If you’re still getting syntax errors you need to check your variables.
Try assigning the query you’re building to a string, then printing it out so
you know **exactly** what you’re sending to mysql.
Regards,
Gavin Towey
*From:* Victor Subervi [mailto:victorsube...@gmail.com]
*Sent:* Thursday, October 01, 2009 3:04 PM
*To:* Gavin Towey; mysql@lists.mysql.com
*Subject:* Re: Nested Joins
Well, your syntax is *exactly* what I had (with a few cosmetic changes).
I've been over the MySQL manual on joins with no luck. I'll read over your
resources tonight. Any other ideas would be appreciated.
Thanks,
V
On Thu, Oct 1, 2009 at 4:49 PM, Gavin Towey gto...@ffn.com wrote:
Victor,
Thank you for the information, that was helpful.
At least part of the problem is the variables you are replacing in that
string, which we can’t see.
The statement should be something like:
select * from ben_franklin_planners c join products p on c.Item=p.Item join
categories cat on p.Category=cat.ID
Make your code produce the above, and you should be fine. I suspect you
don’t need LEFT JOIN there, an inner join will suffice.
For more info on joins:
http://hashmysql.org/index.php?title=Introduction_to_Joins
For more indepth info:
http://dev.mysql.com/tech-resources/articles/mysql-db-design-ch5.pdf
http://dev.mysql.com/doc/refman/5.1/en/join.html
Regards,
Gavin Towey
*From:* Victor Subervi [mailto:victorsube...@gmail.com]
*Sent:* Thursday, October 01, 2009 2:25 PM
*To:* Gavin Towey; mysql@lists.mysql.com
*Subject:* Re: Nested Joins
On Thu, Oct 1, 2009 at 4:03 PM, Gavin Towey gto...@ffn.com wrote:
Joins aren't nested like that, unless you use a subquery. I think you just
need to remove the parens around the second join.
I tried that and no go :(
For better help:
1. show the real SQL -- echo the statement. Most people here don't like
looking at app code because your variables could contain anything.
ProgrammingError: (1064, You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right syntax to
use near ') left join categories on products.Category=categories.ID)' at
line 1), referer: http://13gems.com/global_solutions/spreadsheet_edit.py
2. Give the exact error message
Isn't that the same thing?
3. If there's no error, explain what you expect and what you're getting
4. Include table schema
DESCRIBE `ben_franklin_planners`
ID int(4) unsigned NULL
Item int(4) unsigned NULL
Discount int(2) unsigned NULL
DESCRIBE categories
ID int(3) primary key not NULL auto_increment
Category varchar(20) unique NULL
describe products
ID int(4) primary key not NULL
Category int(3) NULL
Item varchar(20) UNIQUE NULL
Description varchar(255) NULL
UOM varchar(20) NULL
Price float(7,2) NULL
5. Explain what you're trying to accomplish.
cursor.execute('select * from %s left join products on
%s.Item=products.Item left join categories on
products.Category=categories.ID;' % (client, client))
The client in this case is ben_franklin_planners
ben_franklin_planners has an item # that is the same as the item # in
products, where all the information about the products is to be found,
EXCEPT the name of the category. For that, we have to go to the categories
table.
Hope that makes it clear.
TIA,
V
--
The information contained in this transmission may contain privileged and
confidential information. It is intended only for the use of the person(s)
named above. If you are not the intended recipient, you are hereby notified
that any review, dissemination, distribution or duplication of this
communication is strictly prohibited. If you are not the intended recipient,
please contact the sender by reply email and destroy all copies of the
original message.
--
The information contained in this transmission may contain privileged and
confidential information. It is intended only for the use of the person(s)
named above. If you are not the intended recipient, you are hereby notified
that any review, dissemination, distribution or duplication of this
communication is strictly prohibited. If you are not the intended recipient,
please contact the sender by reply email and destroy all copies of the
original message.
Another Join Problem
Hi; I get the following error: *SQL query:* SELECT ID, Item FROM products JOIN categories ON categories.ID = products.Category LIMIT 0 , 30; *MySQL said:* #1052 - Column 'ID' in field list is ambiguous Please note the error is about ambiguity. products has an ID field and so does categories. If I run the statement taking out the ID from the select, it runs. So, where is the ambiguity?? TIA, V
Re: Another Join Problem
Thanks V On Fri, Oct 2, 2009 at 11:08 AM, Mark Goodge m...@good-stuff.co.uk wrote: Victor Subervi wrote: Hi; I get the following error: *SQL query:* SELECT ID, Item FROM products JOIN categories ON categories.ID = products.Category LIMIT 0 , 30; *MySQL said:* #1052 - Column 'ID' in field list is ambiguous Please note the error is about ambiguity. products has an ID field and so does categories. If I run the statement taking out the ID from the select, it runs. So, where is the ambiguity?? The ambiguity is that the select clause doesn't know which table you're referring to, since you're joining two of them that both have an 'ID' field. This will work: SELECT products.ID, Item FROM products JOIN categories ON categories.ID = products.Category LIMIT 0 , 30; Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=victorsube...@gmail.com
Nested Joins
Hi;
I'm new to join statements. Here's my python syntax:
cursor.execute('select * from %s left join products on
%s.Item=products.Item (left join categories on
products.Category=categories.ID);' % (client, client))
I believe it's clear how I want to nest, but I don't know how to repair my
syntax.
TIA,
V
Re: Nested Joins
On Thu, Oct 1, 2009 at 4:03 PM, Gavin Towey gto...@ffn.com wrote:
Joins aren't nested like that, unless you use a subquery. I think you just
need to remove the parens around the second join.
I tried that and no go :(
For better help:
1. show the real SQL -- echo the statement. Most people here don't like
looking at app code because your variables could contain anything.
ProgrammingError: (1064, You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right syntax to
use near ') left join categories on products.Category=categories.ID)' at
line 1), referer: http://13gems.com/global_solutions/spreadsheet_edit.py
2. Give the exact error message
Isn't that the same thing?
3. If there's no error, explain what you expect and what you're getting
4. Include table schema
DESCRIBE `ben_franklin_planners`
ID int(4) unsigned NULL
Item int(4) unsigned NULL
Discount int(2) unsigned NULL
DESCRIBE categories
ID int(3) primary key not NULL auto_increment
Category varchar(20) unique NULL
describe products
ID int(4) primary key not NULL
Category int(3) NULL
Item varchar(20) UNIQUE NULL
Description varchar(255) NULL
UOM varchar(20) NULL
Price float(7,2) NULL
5. Explain what you're trying to accomplish.
cursor.execute('select * from %s left join products on
%s.Item=products.Item left join categories on
products.Category=categories.ID;' % (client, client))
The client in this case is ben_franklin_planners
ben_franklin_planners has an item # that is the same as the item # in
products, where all the information about the products is to be found,
EXCEPT the name of the category. For that, we have to go to the categories
table.
Hope that makes it clear.
TIA,
V
Re: Nested Joins
Well, your syntax is *exactly* what I had (with a few cosmetic changes).
I've been over the MySQL manual on joins with no luck. I'll read over your
resources tonight. Any other ideas would be appreciated.
Thanks,
V
On Thu, Oct 1, 2009 at 4:49 PM, Gavin Towey gto...@ffn.com wrote:
Victor,
Thank you for the information, that was helpful.
At least part of the problem is the variables you are replacing in that
string, which we can’t see.
The statement should be something like:
select * from ben_franklin_planners c join products p on c.Item=p.Item join
categories cat on p.Category=cat.ID
Make your code produce the above, and you should be fine. I suspect you
don’t need LEFT JOIN there, an inner join will suffice.
For more info on joins:
http://hashmysql.org/index.php?title=Introduction_to_Joins
For more indepth info:
http://dev.mysql.com/tech-resources/articles/mysql-db-design-ch5.pdf
http://dev.mysql.com/doc/refman/5.1/en/join.html
Regards,
Gavin Towey
*From:* Victor Subervi [mailto:victorsube...@gmail.com]
*Sent:* Thursday, October 01, 2009 2:25 PM
*To:* Gavin Towey; mysql@lists.mysql.com
*Subject:* Re: Nested Joins
On Thu, Oct 1, 2009 at 4:03 PM, Gavin Towey gto...@ffn.com wrote:
Joins aren't nested like that, unless you use a subquery. I think you just
need to remove the parens around the second join.
I tried that and no go :(
For better help:
1. show the real SQL -- echo the statement. Most people here don't like
looking at app code because your variables could contain anything.
ProgrammingError: (1064, You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right syntax to
use near ') left join categories on products.Category=categories.ID)' at
line 1), referer: http://13gems.com/global_solutions/spreadsheet_edit.py
2. Give the exact error message
Isn't that the same thing?
3. If there's no error, explain what you expect and what you're getting
4. Include table schema
DESCRIBE `ben_franklin_planners`
ID int(4) unsigned NULL
Item int(4) unsigned NULL
Discount int(2) unsigned NULL
DESCRIBE categories
ID int(3) primary key not NULL auto_increment
Category varchar(20) unique NULL
describe products
ID int(4) primary key not NULL
Category int(3) NULL
Item varchar(20) UNIQUE NULL
Description varchar(255) NULL
UOM varchar(20) NULL
Price float(7,2) NULL
5. Explain what you're trying to accomplish.
cursor.execute('select * from %s left join products on
%s.Item=products.Item left join categories on
products.Category=categories.ID;' % (client, client))
The client in this case is ben_franklin_planners
ben_franklin_planners has an item # that is the same as the item # in
products, where all the information about the products is to be found,
EXCEPT the name of the category. For that, we have to go to the categories
table.
Hope that makes it clear.
TIA,
V
--
The information contained in this transmission may contain privileged and
confidential information. It is intended only for the use of the person(s)
named above. If you are not the intended recipient, you are hereby notified
that any review, dissemination, distribution or duplication of this
communication is strictly prohibited. If you are not the intended recipient,
please contact the sender by reply email and destroy all copies of the
original message.
Create Syntax (easy)
Hi; Please give me the syntax below such that I can force the insert statements to use only selected values (item1, item2, item3): create table (field SOMETHING_HERE item1 item2 item3, ... ) TIA, Victor
Re: Create Syntax (easy)
That's it! Thanks,
V
On Tue, Sep 29, 2009 at 12:13 PM, David Giragosian dgiragos...@gmail.comwrote:
On Tue, Sep 29, 2009 at 11:09 AM, Victor Subervi victorsube...@gmail.com
wrote:
Hi;
Please give me the syntax below such that I can force the insert
statements
to use only selected values (item1, item2, item3):
create table (field SOMETHING_HERE item1 item2 item3,
...
)
TIA,
Victor
CREATE TABLE set_test(
rowid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
myset SET('Travel','Sports','Dancing','Fine Dining')
);
From: http://dev.mysql.com/tech-resources/articles/mysql-set-datatype.html
David
--
There is more hunger for love and appreciation in this world than for
bread.- Mother Teresa
Problem w/ mysqldump
Hi: I have the following python code: import os os.system(mysqldump -u root -pPASSWORD --opt spreadsheets dump.sql) This nicely creates the file...but the file is empty! The database exists and has lots of data, I double-checked it. If there is nothing wrong with my code, is there some way to do the same thing from within MySQL? Can I at least print everything to screen and copy it? Where would I find my database in Windoze? TIA, Victor
Re: Problem w/ mysqldump
I checked my own backup script from earlier years and everything was good. You know, if I could simply figure out where the data was actually stored, in what file, I could copy it over to another computer. Any ideas? Thanks, V On Wed, Sep 2, 2009 at 4:09 PM, Daevid Vincent dae...@daevid.com wrote: While not python, maybe this bash script will give you some clues? http://daevid.com/content/examples/daily_backup.php Also, please don't cross post to multiple lists. Not everyone on this mySQL list is on the python list and vice versa. It's just bad netiquette. -Original Message- From: news [mailto:n...@ger.gmane.org] On Behalf Of Emile van Sebille Sent: Wednesday, September 02, 2009 7:18 AM To: mysql@lists.mysql.com Cc: python-l...@python.org Subject: Re: Problem w/ mysqldump On 9/2/2009 3:43 AM Victor Subervi said... Hi: I have the following python code: import os os.system(mysqldump -u root -pPASSWORD --opt spreadsheets dump.sql) First, test this at the system command line -- you'll likely get an empty file there as well, so calling from within python simply does the same. Then read the mysqldump docs for the command arguments and supply the database name... Emile This nicely creates the file...but the file is empty! The database exists and has lots of data, I double-checked it. If there is nothing wrong with my code, is there some way to do the same thing from within MySQL? Can I at least print everything to screen and copy it? Where would I find my database in Windoze? TIA, Victor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=dae...@daevid.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=victorsube...@gmail.com
Re: Problem w/ mysqldump
Not in Windoze. The only folders I have inside the Programs/MySQL are Docs, bin and Shared V On Wed, Sep 2, 2009 at 5:29 PM, Daevid Vincent dae...@daevid.com wrote: If it's MYISAM tables, then they're all self contained in folders in /var/lib/mysql/spreadsheets. Remember that if you do copy the files, to shut down mysql first or you could copy corrupt files. If you're using INNODB, then the schema is in that folder, but the actual data is in the /var/lib/mysql/ib* files. Along with all the other INNODB databases you may have on the system -- all mashed together. Hence the need for a mysql dump tool ;-) Mysql dump is the better way to go in either situation. -Original Message- From: Victor Subervi [mailto:victorsube...@gmail.com] Sent: Wednesday, September 02, 2009 1:43 PM To: Daevid Vincent; mysql@lists.mysql.com Subject: Re: Problem w/ mysqldump I checked my own backup script from earlier years and everything was good. You know, if I could simply figure out where the data was actually stored, in what file, I could copy it over to another computer. Any ideas? Thanks, V On Wed, Sep 2, 2009 at 4:09 PM, Daevid Vincent dae...@daevid.com wrote: While not python, maybe this bash script will give you some clues? http://daevid.com/content/examples/daily_backup.php Also, please don't cross post to multiple lists. Not everyone on this mySQL list is on the python list and vice versa. It's just bad netiquette. -Original Message- From: news [mailto:n...@ger.gmane.org] On Behalf Of Emile van Sebille Sent: Wednesday, September 02, 2009 7:18 AM To: mysql@lists.mysql.com Cc: python-l...@python.org Subject: Re: Problem w/ mysqldump On 9/2/2009 3:43 AM Victor Subervi said... Hi: I have the following python code: import os os.system(mysqldump -u root -pPASSWORD --opt spreadsheets dump.sql) First, test this at the system command line -- you'll likely get an empty file there as well, so calling from within python simply does the same. Then read the mysqldump docs for the command arguments and supply the database name... Emile This nicely creates the file...but the file is empty! The database exists and has lots of data, I double-checked it. If there is nothing wrong with my code, is there some way to do the same thing from within MySQL? Can I at least print everything to screen and copy it? Where would I find my database in Windoze? TIA, Victor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=dae...@daevid.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=victorsube...@gmail.com
Re: Suppressing Warnings
Right. Perhaps you could enlighten me as to what I should code to prevent
that warning? It repeats 2,400 times and makes finding the real errors a
pain in the behind.
Victor
On Tue, Aug 25, 2009 at 12:42 AM, peng yao xwei...@gmail.com wrote:
Warning: Can't create database 'spreadsheets'; database exists
This is the answer.
2009/8/24 Victor Subervi victorsube...@gmail.com
Hi,
I have the following python code:
cursor.execute('create database if not exists spreadsheets;')
cursor.execute('use spreadsheets;')
but it generates this warning:
Warning (from warnings module):
File C:\Python25\mysqlConverter.py, line 140
cursor.execute('use spreadsheets;')
Warning: Can't create database 'spreadsheets'; database exists
What do?
TIA,
Victor
Suppressing Warnings
Hi,
I have the following python code:
cursor.execute('create database if not exists spreadsheets;')
cursor.execute('use spreadsheets;')
but it generates this warning:
Warning (from warnings module):
File C:\Python25\mysqlConverter.py, line 140
cursor.execute('use spreadsheets;')
Warning: Can't create database 'spreadsheets'; database exists
What do?
TIA,
Victor
Update Syntax
Hi; I would like to test the following: update maps set map where site=mysite; to see if there is such an entry in maps. If there is, then update. If there is not, then I would like to execute an insert statement. How do I do that? TIA, Victor
