Re: auto_increment
On Tue, 22 Apr 2008, Sebastian Mendel wrote:
Sebastian Mendel schrieb:
Hiep Nguyen schrieb:
hi list,
reading manual on mysql regarding auto_increment with multiple-column
index:
CREATE TABLE animals (
grp ENUM('fish','mammal','bird') NOT NULL,
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (grp,id)
);
INSERT INTO animals (grp,name) VALUES
('mammal','dog'),('mammal','cat'),
('bird','penguin'),('fish','lax'),('mammal','whale'),
('bird','ostrich');
SELECT * FROM animals ORDER BY grp,id;
+++-+
| grp| id | name|
+++-+
| fish | 1 | lax |
| mammal | 1 | dog |
| mammal | 2 | cat |
| mammal | 3 | whale |
| bird | 1 | penguin |
| bird | 2 | ostrich |
+++-+
my question is what id would be if i:
UPDATE `animals` SET `grp`='mammal' WHERE `grp`='bird' AND `id`='2' AND
`name`='ostrich' LIMIT 1;
you do not need `name`='ostrich' in WHERE, cause grp,id are already UNIQUE
your key is grp,id (bird,2)
but your query will fail, because there is already grp,id (mammal,2) and
therre can not be two identical UNIQUE (PRIMARY) keys
auto_increment comes only in effect when inserting NULL (or 0 in some SQL
mode) or nothing (with default NULL, 0 what should be always the case for
auto_increment fields)
your query should look like this:
UPDATE `animals`
SET `grp` = 'mammal',
`id` = NULL
WHERE `grp` = 'bird'
AND `id` = '2'
LIMIT 1;
--
Sebastian Mendel
thanks, i got it.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
auto_increment
hi list,
reading manual on mysql regarding auto_increment with multiple-column
index:
CREATE TABLE animals (
grp ENUM('fish','mammal','bird') NOT NULL,
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (grp,id)
);
INSERT INTO animals (grp,name) VALUES
('mammal','dog'),('mammal','cat'),
('bird','penguin'),('fish','lax'),('mammal','whale'),
('bird','ostrich');
SELECT * FROM animals ORDER BY grp,id;
+++-+
| grp| id | name|
+++-+
| fish | 1 | lax |
| mammal | 1 | dog |
| mammal | 2 | cat |
| mammal | 3 | whale |
| bird | 1 | penguin |
| bird | 2 | ostrich |
+++-+
my question is what id would be if i:
UPDATE `animals` SET `grp`='mammal' WHERE `grp`='bird' AND `id`='2' AND
`name`='ostrich' LIMIT 1;
i'm confused on auto_increment now.
thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
database design
hi all, after a little 'research' and consultation with friends, i come up with these tables for my database project. table Item(ItemID,warehouse,ShapeID,weight,category,description) table Shape(ShapeID,physical shape) table Dimension(DimensionID,dimension) table ShapeDimension(ShapeDimensionID,ShapeID,DimensionID) table ItemShapeDimension(ItemID,ShapeDimensionID,value) ItemID,ShapeID,DimensionID, and ShapeDimensionID are primary keys, auto increment Shape Dimension are static tables Shape: Round, Hex, Angle, Channel, Pipe,..etc. Dimension: Diameter, Length, Width, Depth, Inner Diameter, Outer Diameter...etc. do you have any comment for this??? is there any potential structure problem with these relationship? thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: grant user privileges
On Wed, 16 Apr 2008, Daniel Brown wrote:
On Tue, Apr 15, 2008 at 9:03 AM, Hiep Nguyen [EMAIL PROTECTED] wrote:
hi all, i have an existing database (internal) with a user named 'admin',
everything works fine as far as privileges concern.
i just created a new database (test) and want to grant admin's privileges
on test as same as internal.
how do i do this???
i tried (as root):
grant all on test.* to 'admin'@'localhost';
grant all on test.* to 'admin'@'10.0.0.%';
GRANT ALL PRIVILEGES ON test.* TO 'admin'@'localhost' IDENTIFIED
BY 'passwordString';
GRANT ALL PRIVILEGES ON test.* TO 'admin'@'%' IDENTIFIED BY
'passwordString';
FLUSH PRIVILEGES;
The first query grants all privileges to admin when accessing the
host locally. The second query allows you to connect from ANY host.
The third query flushes the old privileges data and ensures that the
updated mysql.users information is used. Be sure to replace
passwordString with your password.
If you only want access to be on the Class C private network
subnet for 10.0.0.x (RFC 1918) as well as localhost, then adjust query
#2 to:
GRANT ALL PRIVILEGES ON test.* TO 'admin'@'10.0.0.%' IDENTIFIED BY
'passwordString';
and then FLUSH PRIVILEGES; again.
i understand all of these, but let me re-state my question:
assume user admin has a few privileges (which i don't know, but can find
out) on internal database.
let say that i create a new database and want to grant admin's privileges
on test so that admin's privileges on internal = admin's privileges on
test
instead of find out what are privileges of admin on internal (select,
update, insert,..) and then
grant select,update, insert,... on on test.* to 'admin'@'localhost'
identified by 'password';
is there any command that can set so that admin's privileges on internal =
admin's privileges on test???
what i'm trying to avoid is manually adjust admin's privileges on test if
admin's privileges on internal changed.
if it's not still clear, then stupid menever mind.
t. hiep
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
grant user privileges
hi all, i have an existing database (internal) with a user named 'admin', everything works fine as far as privileges concern. i just created a new database (test) and want to grant admin's privileges on test as same as internal. how do i do this??? i tried (as root): grant all on test.* to 'admin'@'localhost'; grant all on test.* to 'admin'@'10.0.0.%'; but it seems not right. thanks, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert select
On Thu, 3 Apr 2008, Hiep Nguyen wrote: hi all, i have a question on insert ... select statement. tbl_1(fld1,fld2,fld3, ) fld1 int primary key auto_increment not null tbl_2(fld_a,fld_b,fld_c,...) how do i construct my select statement so that fld1 is auto increment? insert into tbl_1(fld1,fld2,fld3) select xxx, fld_b, NOW() from tbl_2 where fld_a = '5'; what should 'xxx' be??? my goal is to get fld1 = auto increment fld2 = fld_b fld3 = NOW() i saw someone used '1', other used null for xxx. i'm confused. thank you for all the helps that i got. i got this works. now i have more tables to do and wonder if this is possible to do in one statement. let say i have 3rd table (tbl_3) with fld_i, fld_ii, fld_iii, fld_iv and this is what i'm doing now after successfully inserted into tbl_1: select fld_i from tbl_3 where fld_i = fld_b limit 1; if record set != 1 insert into tbl_3 (fld_i) values (fld_b); in other word, insert a record into tbl_3 if and only if fld_d doesn't existing in tbl_3. here is the relationship between 3 tables: fld_2, fld_b, and fld_i are primary key of its respective table fld_2 = fld_b = fld_i this is the reason that i haven't try this with JOIN clause. thank you, t. hiep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
insert select
hi all, i have a question on insert ... select statement. tbl_1(fld1,fld2,fld3, ) fld1 int primary key auto_increment not null tbl_2(fld_a,fld_b,fld_c,...) how do i construct my select statement so that fld1 is auto increment? insert into tbl_1(fld1,fld2,fld3) select xxx, fld_b, NOW() from tbl_2 where fld_a = '5'; what should 'xxx' be??? my goal is to get fld1 = auto increment fld2 = fld_b fld3 = NOW() i saw someone used '1', other used null for xxx. i'm confused. thanks. t. hiep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert select
On Thu, 3 Apr 2008, Johan Höök wrote: Hi Hiep, you can put in either xxx = NULL or you can skip it completely: insert into tbl_1(fld2,fld3) select fld_b, NOW() from tbl_2; Regards, /Johan Hiep Nguyen skrev: hi all, i have a question on insert ... select statement. tbl_1(fld1,fld2,fld3, ) fld1 int primary key auto_increment not null tbl_2(fld_a,fld_b,fld_c,...) how do i construct my select statement so that fld1 is auto increment? insert into tbl_1(fld1,fld2,fld3) select xxx, fld_b, NOW() from tbl_2 where fld_a = '5'; what should 'xxx' be??? my goal is to get fld1 = auto increment fld2 = fld_b fld3 = NOW() i saw someone used '1', other used null for xxx. i'm confused. thanks. t. hiep insert into tbl_1(fld1,fld2,fld3) select NULL,fld_b, NOW() from tbl_2 where fld_a = '5' limit 1; how do i prevent the insertion when select returned empty record? thanks, t. hiep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
select *
hi all, how do i select all fields in tbl1,tbl2, and only fld1,fld2 in tbl3? is this possible??? select *,*,tbl3.fld1,tbl3.fld2 from tbl1,tbl2,tbl3; the reason for this is b/c there are hundred of fields in tbl1 tbl2 that i don't want to type them all. thanks, t. hiep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select *
On Tue, 1 Apr 2008, Tim McDaniel wrote: On Tue, 1 Apr 2008, Rob Wultsch [EMAIL PROTECTED] wrote: On Tue, Apr 1, 2008 at 11:15 AM, Hiep Nguyen [EMAIL PROTECTED] wrote: how do i select all fields in tbl1,tbl2, and only fld1,fld2 in tbl3? select tbl2.*, tbl1.*, tbl3.fld1, tbl3.fld2 from tbl1,tbl2,tbl3; Hiep, will you be supplying a WHERE clause to keep from getting all possible combinations of rows from tbl1, tbl2, and tbl3? (Or am I misunderstanding something?) Please also consider using explicit JOINs. Another way to do the same thing with different syntax, right? -- Tim McDaniel, [EMAIL PROTECTED] thanks. the WHERE is very long, so i skipped here. i'll try JOIN, newbie on JOIN. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqlimport load data infile
i read about mysqlimport load data infile for mysql, but i can't find a way to import text file using length of column, instead of delimiter my text file contains fixed length column: -- i can use ms excel to convert all files to .csv format and import, but it would take a long time and i have to escape delimiter. so, is there a way to import text file with fixed column size into mysql??? thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
revisit data structure
hi all, i come up these tables for my project and i'm looking for advice/suggestion how to improve/optimize the structure further. table item(itemid,warehouse,category,shapeid,) table shape(shapeid,physical_shape,...) table dimension(dimensionid,itemid,shapeid,characteristic,...) possible values for physical_shape: round, hex, sheet, tubing, ... etc possible values for characteristic: diameter, length, width, height, ... etc i have 2 questions: 1) can this be optimized any further? 2) i like to seperate itemid from dimension table, is it possible??? thanks, t. hiep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
log changes
hi all, i have a table (not my design) with a lot of fields and users have access to insert/update/delete record from this table. is there a way that mysql can log all transactions who change what on this table??? or do i have to create a seperate table to keep track the changes? for example: someone changed the price from 2.00 to 2.50 on price this needs to be done for the purpose of auditing. i found some docs on mysql.com, but mostly for query log server log. thanks, t. hiep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: change pw
On Mon, 3 Mar 2008, Daniel Brown wrote:
On Mon, Mar 3, 2008 at 2:46 PM, Hiep Nguyen [EMAIL PROTECTED] wrote:
mysql select user,host,password from mysql.user;
+--+--+--+
| user | host | password |
+--+--+--+
| root | localhost| |
| root | dev.jss.com | |
| | dev.jss.com | |
| | localhost| |
+--+--+--+
4 rows in set (0.00 sec)
Okay, I wasn't aware that it's all on the same server. Try this:
USE mysql;
UPDATE mysql.user SET Password=PASSWORD('newpwd') WHERE
User='root' AND host='dev.jss.com' LIMIT 1;
FLUSH PRIVILEGES;
do i have to worry about those don't have user name? what are they use
for? should i delete them???
t. hiep
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: change pw
On Tue, 4 Mar 2008, Dan Rogart wrote: You should definitely consider getting rid of them, otherwise people can log in to MySQL from any host with no credentials. They are created during installation by the mysql_install_db script. This tells you how to remove them: http://dev.mysql.com/doc/refman/5.0/en/default-privileges.html i followed the instruction and typed: mysql DROP USER ''; ERROR 1396 (HY000): Operation DROP USER failed for ''@'%' mysql DROP USER ''@'localhost'; Query OK, 0 rows affected (0.00 sec) and mysql DROP USER ''@'localhost'; ERROR 1396 (HY000): Operation DROP USER failed for ''@'localhost' what's wrong here??? t. hiep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: change pw
got it. thanks. t. hiep On Tue, 4 Mar 2008, Dan Rogart wrote: That error occurs when the user has already been dropped - so it's good news :). You can check for users with blank user names and/or blank passwords by querying the mysql.user table: select user,host,password from mysql.user where user = '' or password = ''; Those are the users you should consider dropping or assigning passwords to. Hope that helps, Dan On 3/4/08 9:57 AM, Hiep Nguyen [EMAIL PROTECTED] wrote: On Tue, 4 Mar 2008, Dan Rogart wrote: You should definitely consider getting rid of them, otherwise people can log in to MySQL from any host with no credentials. They are created during installation by the mysql_install_db script. This tells you how to remove them: http://dev.mysql.com/doc/refman/5.0/en/default-privileges.html i followed the instruction and typed: mysql DROP USER ''; ERROR 1396 (HY000): Operation DROP USER failed for ''@'%' mysql DROP USER ''@'localhost'; Query OK, 0 rows affected (0.00 sec) and mysql DROP USER ''@'localhost'; ERROR 1396 (HY000): Operation DROP USER failed for ''@'localhost' what's wrong here??? t. hiep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
grant user
hi all, i have a user that can only access localhost, how do i grant this user permission so that can also be accessed from 192.168.1.50? thanks t. hiep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: grant user
On Mon, 3 Mar 2008, Hiep Nguyen wrote: hi all, i have a user that can only access localhost, how do i grant this user permission so that can also be accessed from 192.168.1.50? i got it. thanks t. hiep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
change pw
hi all, i just installed mysql and started mysqld. it suggested i change pw for root, so i did: mysqladmin -u root password my_pw; but i can't do: mysqladmin -u root -h dev.jss.com password my_pw; how do i change pw for [EMAIL PROTECTED] thanks, t. hiep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: change pw
On Mon, 3 Mar 2008, Daniel Brown wrote:
On Mon, Mar 3, 2008 at 2:01 PM, Hiep Nguyen [EMAIL PROTECTED] wrote:
hi all, i just installed mysql and started mysqld.
it suggested i change pw for root, so i did:
mysqladmin -u root password my_pw;
but i can't do:
mysqladmin -u root -h dev.jss.com password my_pw;
how do i change pw for [EMAIL PROTECTED]
Quickly STFW'ing/RTFM'ing would give you an answer.
One way is to log into the remote host (dev.jss.com) via SSH as
root (or use a control panel such as cPanel).
Another is to use the MySQL client and log in remotely as such:
mysql -h dev.jss.com -u root -p
(Enter the MySQL root password)
Then type the following MySQL queries:
USE mysql;
UPDATE mysql.user SET Password=PASSWORD('newpwd') WHERE User='root';
FLUSH PRIVILEGES;
Just be sure to replace 'newpwd' with the password you want to
use. MySQL's PASSWORD() function will handle hashing the password, so
don't send it encrypted or pre-hashed.
mysql select user,host,password from mysql.user;
+--+--+--+
| user | host | password |
+--+--+--+
| root | localhost| |
| root | dev.jss.com | |
| | dev.jss.com | |
| | localhost| |
+--+--+--+
4 rows in set (0.00 sec)
i have no problem set password for [EMAIL PROTECTED], but for some reason i
can't set a password for [EMAIL PROTECTED]
is there any security issue not to set password for [EMAIL PROTECTED]
what about these two:
''@'localhost' ''@'dev.jss.com'
thanks,
t. hiep
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select
On Fri, 18 Jan 2008, Sebastian Mendel wrote: Hiep Nguyen schrieb: hi all, i have a table looks like this: ID sDate 1 1997-03-21 2 1997-04-30 3 1997-05-30 4 1998-01-29 5 1998-02-24 6 1998-03-21 7 1999-05-10 8 1999-07-12 9 1999-10-20 10 2000-01-01 11 2000-02-15 12 2000-03-20 13 2000-05-18 how do i construct my select statement so that i only get distinct year? so the above data will return something like this: sDate 2000 1999 1998 1997 did your tried: SELECT DISTINCT YEAR(`sDate`); -- Sebastian thanks, that's perfect. t. hiep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
select
hi all, i have a table looks like this: ID sDate 1 1997-03-21 2 1997-04-30 3 1997-05-30 4 1998-01-29 5 1998-02-24 6 1998-03-21 7 1999-05-10 8 1999-07-12 9 1999-10-20 10 2000-01-01 11 2000-02-15 12 2000-03-20 13 2000-05-18 how do i construct my select statement so that i only get distinct year? so the above data will return something like this: sDate 2000 1999 1998 1997 thanks, t. hiep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
import from exel into mysql
hi everyone, i have a large ms excel data (text) file that i need to import to my table in mysql. does any one have a suggestion how to do this? i'm try to export to csv file, then import to my table, but i have so much problems with delimeters thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
possible combine update statement
hi list, i have a loop to update my table: update tbl_idea set col1 = 'text1', col2 = NOW() where ideaID = 1; update tbl_idea set col1 = 'text2', col2 = NOW() where ideaID = 5; ... update tbl_idea set col1 = 'textzzz', col2 = NOW() where ideaID = XXX; my question is can i combine above statements into one statement so i can only do update only once? thanks, T. Hiep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
help with select
hi list, i have two tables: idea(iid int not null primary_key auto_increment, completed_by int, submitted_by int); employee(eid int not null primary_key auto_increment, first varchar(20), last varchar(30)); table idea data: 1 | 4 | 10 2 | 3 | 7 table employee data: 3 | john | Doe 4 | betty | smith 7 | bob | Gomez 10 | sun | mcnab i'm trying to select from idea table such that when iid = 1, i should get betty smith for completed_by column and sun mcnab for submitted_by column. 1st trial: select iid,completed_by,submitted_by from idea where iid=1 i got: 1 | 4 | 10 2nd trial: select idd,concat(first, ,last),submitted_by from idea,employee where iid=1 and completed_by=eid; i got: 1 | betty smith | 10 now, instead of 10 for the submitted_by column, how do i get sun mcnab? thanks, T. Hiep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
speical characters in text column
hi friends, i searched on google but not file a solution. is there a way that i can store special characters (return, new line, tab, etc) into mysql (ver. 4.1.12) table with text type? i use textarea tag for user to enter the text. my goal is to store/display EXACTLY what user entered in the textarea tag. Thanks, T. Hiep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
repost timestamp for update insert
i asked this question before, but when i tried what D.Vin suggested, i got an error. mysql version is 4.1.12 i tried: CREATE TABLE tbl_spdate( spdate_ID int NOT NULL PRIMARY KEY AUTO_INCREMENT , spdate_date date NOT NULL , spdate_notes varchar( 100 ) , spdate_created timestamp default 0, spdate_updated default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); and i got this: #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 'default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP )' at line 7 am i have a wrong version? Thanks, T. Hiep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
select sum order
hi there, this seems so easy, but i'm out of sql for a long time and need help i have: id,amount,state 1,2.00,il 2,2.00,oh 3,1.00,il 4,1.00,ks 5,3.00,ks 6,4.00,oh how do i construct a sql statement that results as following: il,3.0 oh,6.0 ks,4.0 sum (amount) all the same state. thank much, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select sum order
thanks, T. Hiep On Tue, 13 Nov 2007, Baron Schwartz wrote: Hi, Hiep Nguyen wrote: hi there, this seems so easy, but i'm out of sql for a long time and need help i have: id,amount,state 1,2.00,il 2,2.00,oh 3,1.00,il 4,1.00,ks 5,3.00,ks 6,4.00,oh how do i construct a sql statement that results as following: il,3.0 oh,6.0 ks,4.0 sum (amount) all the same state. Try this: select state, sum(amount) from tbl group by state; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
load data
hi there,
i have a text file that i prepare:
insert into `sa2007` (`id`,`amount`,`state`) values
('','1.00','oh'),
('','2.00','il'),
('','4.00','ks')
how do i import this file to sa2007 table from the command line? i tried
via phymyadmin, but it doesn't work (300 seconds timeout).
thnx,
T. Hiep
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
timestamp for update and insert
Hi list, i tried to create a table with inserted lastupdated timestamp fields: create table temp ( id int not null primary ke auto_increment, data varchar(100), inserted timestamp default now(), lastupdated timestamp(8)); how do i get mysql to put in the current timestamp for inserted lastupdated fields when i insert a record and only lastupdated when i update the record? thanks, T. Hiep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: timestamp for update and insert
is it possible to do without trigger? i google and found this link: http://sql-info.de/mysql/examples/CREATE-TABLE-examples.html but when i tried to combine two examples into one CREATE statement and it didn't work. any idea? is there a way to create this table that accomplishes these two goals? thanks, T. Hiep On Tue, 4 Sep 2007, Michael Dykman wrote: Triggers are a fine idea, but I would use a trigger for both cases.. no point putting that level of housekeeping on the application when you can set rules in the database and more or less forget about it. - michael On 9/4/07, Olaf Stein [EMAIL PROTECTED] wrote: I would use a trigger (at least for the update) The first insert should work with now() and you can leave lastupdateted empty Olaf On 9/4/07 3:01 PM, Hiep Nguyen [EMAIL PROTECTED] wrote: Hi list, i tried to create a table with inserted lastupdated timestamp fields: create table temp ( id int not null primary ke auto_increment, data varchar(100), inserted timestamp default now(), lastupdated timestamp(8)); how do i get mysql to put in the current timestamp for inserted lastupdated fields when i insert a record and only lastupdated when i update the record? thanks, T. Hiep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: timestamp for update and insert
so, if trigger is used then create table temp ( id int not null primary key auto_increment, data varchar(100), inserted timestamp, lastupdated timestamp) is good enough, right? trigger will use now() function to set inserted lastupdated. any thought on backup restore tables tringgers??? thank you for your helps. T. Hiep On Tue, 4 Sep 2007, Michael Dykman wrote: There is nothing terribly wrong with the approach documented in 'http://sql-info.de/mysql/examples/CREATE-TABLE-examples.html' but, as you no doubt have read, it does mean that you have to make sure that every insert statement is specifically designed to set the *second* timestamp field to now() and then count on the built-in properties to see the first one updated on every UPDATE. The only other caveats are: your application behaviour is now dependent on the ordering of columns; ok in the short-term, increasingly annoying over time as maintainence phases grow the app in complexity. importing data from your system to another system might prove hairy as you figure out how to temporarily avoid this bevahiour to keep your data intact. The trigger method is universal in that this solution will port to any half-way reasonable database engine but, as in all things IT, do whatever best meets your circumstances. - michael On 9/4/07, Hiep Nguyen [EMAIL PROTECTED] wrote: is it possible to do without trigger? i google and found this link: http://sql-info.de/mysql/examples/CREATE-TABLE-examples.html but when i tried to combine two examples into one CREATE statement and it didn't work. any idea? is there a way to create this table that accomplishes these two goals? thanks, T. Hiep On Tue, 4 Sep 2007, Michael Dykman wrote: Triggers are a fine idea, but I would use a trigger for both cases.. no point putting that level of housekeeping on the application when you can set rules in the database and more or less forget about it. - michael On 9/4/07, Olaf Stein [EMAIL PROTECTED] wrote: I would use a trigger (at least for the update) The first insert should work with now() and you can leave lastupdateted empty Olaf On 9/4/07 3:01 PM, Hiep Nguyen [EMAIL PROTECTED] wrote: Hi list, i tried to create a table with inserted lastupdated timestamp fields: create table temp ( id int not null primary ke auto_increment, data varchar(100), inserted timestamp default now(), lastupdated timestamp(8)); how do i get mysql to put in the current timestamp for inserted lastupdated fields when i insert a record and only lastupdated when i update the record? thanks, T. Hiep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
zipcode to timezone
Hi there, we have warehouses all over U.S. and i just wonder what is the best way to find out their timezone base on zipcode. Should i buy a database or is there any function in mysql or php to get timezone base on a zipcode? Thanks T. Hiep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: zipcode to timezone
On Fri, 6 Jul 2007, David T. Ashley wrote: On 7/6/07, Hiep Nguyen [EMAIL PROTECTED] wrote: we have warehouses all over U.S. and i just wonder what is the best way to find out their timezone base on zipcode. Should i buy a database or is there any function in mysql or php to get timezone base on a zipcode? I looked at the zipcode databases ... not as expensive as I would have thought. It might be worth it just to spend the $100 or so. However, ... My understanding is that U.S. zipcodes have their first two digits based on state, i.e. 48 is Michigan. Since most of the time zone boundaries seem to fall on state boundaries, a simple mapping from the first two digits to the time zone might get you most of the way there. http://images.google.com/imgres?imgurl=http://worldatlas.com/webimage/countrys/namerica/usstates/timezone.gifimgrefurl=http://worldatlas.com/webimage/countrys/namerica/usstates/timezone.htmh=307w=427sz=23tbnid=pXERv6TKqAu7DM:tbnh=91tbnw=126prev=/images%3Fq%3Du.s.%2Btime%2Bzone%2Bmap%26um%3D1start=2sa=Xoi=imagesct=imagecd=2 However, for those states that are split, I don't know an easy way ... but there shouldn't be very many of those. Dave. i don't think there is any state got 2 timezones, i could be wrong. but if that the case, state - timezone is working for me. now, how do i look up for timezone if i got state? i don't mind buying the database, but i just don't want to replace the database if something change down the road. thanks T. Hiep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: zipcode to timezone
On Fri, 6 Jul 2007, Dirk Bremer wrote: Indiana has two time zones as I recall. The state is divided roughly in half between the two. Dirk Bremer - Senior Systems Engineer - Utility - AMS - NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2502 [EMAIL PROTECTED] www.nisc.coop -Original Message- From: Hiep Nguyen [mailto:[EMAIL PROTECTED] Sent: Friday, July 06, 2007 10:51 To: David T. Ashley Cc: mysql@lists.mysql.com Subject: Re: zipcode to timezone On Fri, 6 Jul 2007, David T. Ashley wrote: On 7/6/07, Hiep Nguyen [EMAIL PROTECTED] wrote: we have warehouses all over U.S. and i just wonder what is the best way to find out their timezone base on zipcode. Should i buy a database or is there any function in mysql or php to get timezone base on a zipcode? I looked at the zipcode databases ... not as expensive as I would have thought. It might be worth it just to spend the $100 or so. However, ... My understanding is that U.S. zipcodes have their first two digits based on state, i.e. 48 is Michigan. Since most of the time zone boundaries seem to fall on state boundaries, a simple mapping from the first two digits to the time zone might get you most of the way there. http://images.google.com/imgres?imgurl=http://worldatlas.com/webimage/co untrys/namerica/usstates/timezone.gifimgrefurl=http://worldatlas.com/we bimage/countrys/namerica/usstates/timezone.htmh=307w=427sz=23tbnid=p XERv6TKqAu7DM:tbnh=91tbnw=126prev=/images%3Fq%3Du.s.%2Btime%2Bzone%2B map%26um%3D1start=2sa=Xoi=imagesct=imagecd=2 However, for those states that are split, I don't know an easy way ... but there shouldn't be very many of those. Dave. i don't think there is any state got 2 timezones, i could be wrong. but if that the case, state - timezone is working for me. now, how do i look up for timezone if i got state? i don't mind buying the database, but i just don't want to replace the database if something change down the road. thanks T. Hiep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] now, why do they do this??? it doesn't make any sense at all to have multiple timezones in a state, at least to me. men, they have too much time in their hand and this is they came up, two timezones in a state. thank you all, T. Hiep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: database structure
Now, if I have a location table with id, name, address, phone, fax, etc... Should I put id or name into the tag table? If id used, then how do i look up the name, address, phone, fax, etc... when I do a select on tag table? Thank you for all your helps T. Hiep -Original Message- From: Mogens Melander [mailto:[EMAIL PROTECTED] Sent: Monday, July 02, 2007 3:45 PM To: mysql@lists.mysql.com Subject: Re: database structure On Mon, July 2, 2007 21:10, Hiep Nguyen wrote: take your advice, i looked in to JOIN and i got the idea. but i noticed that in order to use JOIN, don't you need to have the same column name in both tables? i just don't see it in your example here. is there something that i'm missing? Using the form: select t1.field1 data1, t2.field1 data2, t3.fieldn data3 from table_a t1 left join table_b t2 on ( t1.id=t2.t1_ref ) left join table_n t3 on ( t2.id=t3.t2_ref ); You can join on allmost anything. ?? Can typecasts be used in this scenario ?? can u give a select example with JOIN on three tables above? -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
database structure
Hi all, i'm seeking for help/suggestion on how to create relationship for this scenario: I have one table (tag) with the following fields: tagid, location, weight, grade, heat, shape, diameter, length, width, height, ... (and many other fields) what i want to do is move the shape field into a different table, however, each shape will have different dimensions for sample: rod bar has diamter and length, but square/flat bar has width, height (thickness), and length. wire has only diameter. but i also don't want to create a huge table to hold all the possible fields in all shapes, so i'm thinking create a table per shape: round table (diameter, length) square table (width, height, length) i-beam table (flange height, flange thickness, web thickness) angle table (leg1, leg2, thickness) ... and so on. in turn, i have 2 tables: tag, shape + as many table as there are shapes my question is: is there a better to do this? how do i query with this structure? i'm a bit confuse on how to manipulate data if each shape have diffent fields??? Thank you for all your help (sorry for a long email) T. Hiep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
database structure (fwd)
is this list working? my last post failed? re-try. T. Hiep -- Forwarded message -- Date: Mon, 2 Jul 2007 05:53:17 -0700 (PDT) From: Hiep Nguyen [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: database structure Hi all, i'm seeking for help/suggestion on how to create relationship for this scenario: I have one table (tag) with the following fields: tagid, location, weight, grade, heat, shape, diameter, length, width, height, ... (and many other fields) what i want to do is move the shape field into a different table, however, each shape will have different dimensions for sample: rod bar has diamter and length, but square/flat bar has width, height (thickness), and length. wire has only diameter. but i also don't want to create a huge table to hold all the possible fields in all shapes, so i'm thinking create a table per shape: round table (diameter, length) square table (width, height, length) i-beam table (flange height, flange thickness, web thickness) angle table (leg1, leg2, thickness) ... and so on. in turn, i have 2 tables: tag, shape + as many table as there are shapes my question is: is there a better to do this? how do i query with this structure? i'm a bit confuse on how to manipulate data if each shape have diffent fields??? Thank you for all your help (sorry for a long email) T. Hiep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: database structure
On Mon, 2 Jul 2007, Rajesh Mehrotra wrote: Hi, You can do this in four tables: 1. Tag 2. Shape (with an additional field, let us call it X, describing how many data elements each shape has) 3. ShapeElements : one record describing each data element (length, width etc.) for each shape. Record count for each shape: X 4. Data Table : X number of records for each TagID. References ShapeElements. The number of table will remain fixed at four, no matter how many shapes you have. And your SQL statements will be generic, most of the times, regardless of the shape. Sincerely, Raj Mehrotra hccs - Experts in Healthcare Learning (516) 478-4100, x105 [EMAIL PROTECTED] -Original Message- From: Hiep Nguyen [mailto:[EMAIL PROTECTED] Sent: Monday, July 02, 2007 8:53 AM To: mysql@lists.mysql.com Subject: database structure Hi all, i'm seeking for help/suggestion on how to create relationship for this scenario: I have one table (tag) with the following fields: tagid, location, weight, grade, heat, shape, diameter, length, width, height, ... (and many other fields) what i want to do is move the shape field into a different table, however, each shape will have different dimensions for sample: rod bar has diamter and length, but square/flat bar has width, height (thickness), and length. wire has only diameter. but i also don't want to create a huge table to hold all the possible fields in all shapes, so i'm thinking create a table per shape: round table (diameter, length) square table (width, height, length) i-beam table (flange height, flange thickness, web thickness) angle table (leg1, leg2, thickness) ... and so on. in turn, i have 2 tables: tag, shape + as many table as there are shapes my question is: is there a better to do this? how do i query with this structure? i'm a bit confuse on how to manipulate data if each shape have diffent fields??? Thank you for all your help (sorry for a long email) T. Hiep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] thank you, but what happen when X change let say from 5 to 7? that means i have to insert 2 more records into ShapeElements. what are we going to do with Data Table? Also, Data Table will be HUGE, isn't it? T. Hiep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: database structure
On Mon, 2 Jul 2007, Borokov Smith wrote:
Or:
Tag
ShapeDimension (type enum('height', 'thickness', etc), value VARCHAR() or
INT())
TagsShapeDimensions (FOREIGN KeY TAG, FOREIGN KEY ShapeDimension)
1 less table
Greetz,
boro
Rajesh Mehrotra schreef:
Hi,
You can do this in four tables:
1. Tag
2. Shape (with an additional field, let us call it X, describing how
many data elements each shape has)
3. ShapeElements : one record describing each data element (length,
width etc.) for each shape. Record count for each shape: X
4. Data Table : X number of records for each TagID. References
ShapeElements.
The number of table will remain fixed at four, no matter how many shapes
you have. And your SQL statements will be generic, most of the times,
regardless of the shape.
Sincerely,
Raj Mehrotra
hccs - Experts in Healthcare Learning
(516) 478-4100, x105
[EMAIL PROTECTED]
-Original Message-
From: Hiep Nguyen [mailto:[EMAIL PROTECTED] Sent: Monday, July 02, 2007 8:53
AM
To: mysql@lists.mysql.com
Subject: database structure
Hi all,
i'm seeking for help/suggestion on how to create relationship for this
scenario:
I have one table (tag) with the following fields:
tagid, location, weight, grade, heat, shape, diameter, length, width,
height, ... (and many other fields)
what i want to do is move the shape field into a different table,
however, each shape will have different dimensions
for sample:
rod bar has diamter and length, but square/flat bar has width, height
(thickness), and length. wire has only diameter.
but i also don't want to create a huge table to hold all the possible
fields in all shapes, so i'm thinking create a table per shape:
round table (diameter, length)
square table (width, height, length)
i-beam table (flange height, flange thickness, web thickness)
angle table (leg1, leg2, thickness)
...
and so on.
in turn, i have 2 tables: tag, shape + as many table as there are shapes
my question is: is there a better to do this? how do i query with this
structure? i'm a bit confuse on how to manipulate data if each shape
have diffent fields???
Thank you for all your help (sorry for a long email)
T. Hiep
i'm a novice and confused, can you enlight a little bit more? example of
data if possible.
thanks,
T. Hiep
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: database structure
On Mon, 2 Jul 2007, Christophe Gregoir wrote:
CREATE TABLE `tags` (`tagid` INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`location` VARCHAR(255) NOT NULL DEFAULT '') ENGINE=INNODB;
CREATE TABLE `dimension_type` (`id` ..., `type` VARCHAR(255) NOT NULL DEFAULT
'') ENGINE=INNODB;
CREATE TABLE `tags_shape_dimensions` (`tag` INT(11) UNSIGNED, `dim` INT(11)
UNSIGNED, `value` VARCHAR(255) NOT NULL DEFAULT '') ENGINE=INNODB;
1) You fill the dimension_type table with all the possible characteristics
(in fact, characteristics would be a better name for that table) you will be
using, with the advantage of being able to very easily add an extra
characteristic later down the road.
2) You fill the tags table with all your differenent tags and locations.
3) You fill in the tags_shape_dimensions table with your tag and any
characteristic that applies to it and its associated value.
You would select data from these tables by using JOIN's. A bit difficult to
grasp if you're an absolute beginner, but you'll never want to go back
afterwards.
Greetz,
boro
Hiep Nguyen schreef:
On Mon, 2 Jul 2007, Borokov Smith wrote:
Or:
Tag
ShapeDimension (type enum('height', 'thickness', etc), value VARCHAR() or
INT())
TagsShapeDimensions (FOREIGN KeY TAG, FOREIGN KEY ShapeDimension)
1 less table
Greetz,
boro
Rajesh Mehrotra schreef:
Hi,
You can do this in four tables:
1. Tag
2. Shape (with an additional field, let us call it X, describing how
many data elements each shape has)
3. ShapeElements : one record describing each data element (length,
width etc.) for each shape. Record count for each shape: X
4. Data Table : X number of records for each TagID. References
ShapeElements.
The number of table will remain fixed at four, no matter how many shapes
you have. And your SQL statements will be generic, most of the times,
regardless of the shape.
Sincerely,
Raj Mehrotra
hccs - Experts in Healthcare Learning
(516) 478-4100, x105
[EMAIL PROTECTED]
-Original Message-
From: Hiep Nguyen [mailto:[EMAIL PROTECTED] Sent: Monday, July 02, 2007
8:53 AM
To: mysql@lists.mysql.com
Subject: database structure
Hi all,
i'm seeking for help/suggestion on how to create relationship for this
scenario:
I have one table (tag) with the following fields:
tagid, location, weight, grade, heat, shape, diameter, length, width,
height, ... (and many other fields)
what i want to do is move the shape field into a different table,
however, each shape will have different dimensions
for sample:
rod bar has diamter and length, but square/flat bar has width, height
(thickness), and length. wire has only diameter.
but i also don't want to create a huge table to hold all the possible
fields in all shapes, so i'm thinking create a table per shape:
round table (diameter, length)
square table (width, height, length)
i-beam table (flange height, flange thickness, web thickness)
angle table (leg1, leg2, thickness)
...
and so on.
in turn, i have 2 tables: tag, shape + as many table as there are shapes
my question is: is there a better to do this? how do i query with this
structure? i'm a bit confuse on how to manipulate data if each shape
have diffent fields???
Thank you for all your help (sorry for a long email)
T. Hiep
i'm a novice and confused, can you enlight a little bit more? example of
data if possible.
thanks,
T. Hiep
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
take your advice, i looked in to JOIN and i got the idea. but i noticed
that in order to use JOIN, don't you need to have the same column name in
both tables? i just don't see it in your example here. is there
something that i'm missing?
can u give a select example with JOIN on three tables above?
Thanks,
T. Hiep
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: indexes and size
i believe date is a reserved word for mysql. don't use reserved word for the column name. it's confused. On Tue, 20 Mar 2007, Peter wrote: Hello, I have a a large a table which a field called date, type date. When I select a smaller range e.g 3 months system uses the index 'date'. That is for let's say 2 million rows. If I select wider date range mysql stops using key. It says possible key date, but do not use it and goes over all 28 Million rows. If I use force index the query becomes even slower. Table type is Myisam. Please advise what should I tune so mysql uses the index without force index and query becomes faster. Thanks :-) Peter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Distinct select over 2 fields?
select * from table where . limit 1 that would do it if you don't care which one it returns JC On Tue, 12 Sep 2006, Brian Dunning wrote: I'm searching a database of geopoints, and when two records have the same latitude and longitude, I only want to return one of them - basically just find all the unique locations. How do you set up a select like this? Thanks... -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
