stored procedure insert statement
Hi all, I can't explain strange behaviour of the INSERT statement in the stored procedure. The idea is to generate a list based on the output of 3 INNER JOIN of regularly updated tables. Something like : INSERT INTO storage (column list) SELECT column list FROM t1 JOIN t2 ON t1.x=t2.y JOIN t3 ON t2.z=t3.w WHERE CONDITIONS; The procedure runs daily by crontask and it inserts correct number of output rows. But after It runs and populated a storage table, I added new entries and expect to find them in the storage table. Even though they were picked up by SELECT statement, they haven't been INSERTed into the storage table. If I DELETE or TRUNCATE from the storage table and run the procedure all newly added entries and existed entries are their, but if I add new rows and run the procedure again It doesn't update the table. All tables have a unique identifier, so duplicate errors are impossible. I use INNODB engine for all tables. I understand that stored procedure is a precompiled thing and I believe it could be something to do with cache but I couldn't find proper explanation or similar case online. I found when I use INSERT IGNORE INTO or INSERT REPLACE INTO, I receive the proper result with newly entries added to the storage table. Any ideas guys? Have a nice weekend ALL. Cheers, Igor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: stored procedure insert statement
It seems to me that your insert statement is trying to insert duplicate rows into the storage table. This is why insert ignore and replace work. On Jul 9, 2011 3:49 AM, Igor Shevtsov nixofort...@googlemail.com wrote: Hi all, I can't explain strange behaviour of the INSERT statement in the stored procedure. The idea is to generate a list based on the output of 3 INNER JOIN of regularly updated tables. Something like : INSERT INTO storage (column list) SELECT column list FROM t1 JOIN t2 ON t1.x=t2.y JOIN t3 ON t2.z=t3.w WHERE CONDITIONS; The procedure runs daily by crontask and it inserts correct number of output rows. But after It runs and populated a storage table, I added new entries and expect to find them in the storage table. Even though they were picked up by SELECT statement, they haven't been INSERTed into the storage table. If I DELETE or TRUNCATE from the storage table and run the procedure all newly added entries and existed entries are their, but if I add new rows and run the procedure again It doesn't update the table. All tables have a unique identifier, so duplicate errors are impossible. I use INNODB engine for all tables. I understand that stored procedure is a precompiled thing and I believe it could be something to do with cache but I couldn't find proper explanation or similar case online. I found when I use INSERT IGNORE INTO or INSERT REPLACE INTO, I receive the proper result with newly entries added to the storage table. Any ideas guys? Have a nice weekend ALL. Cheers, Igor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=joh...@pixelated.net
Re: stored procedure insert statement
That's what is bad of SP in MySQL, debugging. Just out of the blue, can you try to disable query cache? *SET GLOBAL query_cache_size = 0;* * SET GLOBAL query_cache_type = 0; * it could be a bug Claudio 2011/7/9 Johnny Withers joh...@pixelated.net It seems to me that your insert statement is trying to insert duplicate rows into the storage table. This is why insert ignore and replace work. On Jul 9, 2011 3:49 AM, Igor Shevtsov nixofort...@googlemail.com wrote: Hi all, I can't explain strange behaviour of the INSERT statement in the stored procedure. The idea is to generate a list based on the output of 3 INNER JOIN of regularly updated tables. Something like : INSERT INTO storage (column list) SELECT column list FROM t1 JOIN t2 ON t1.x=t2.y JOIN t3 ON t2.z=t3.w WHERE CONDITIONS; The procedure runs daily by crontask and it inserts correct number of output rows. But after It runs and populated a storage table, I added new entries and expect to find them in the storage table. Even though they were picked up by SELECT statement, they haven't been INSERTed into the storage table. If I DELETE or TRUNCATE from the storage table and run the procedure all newly added entries and existed entries are their, but if I add new rows and run the procedure again It doesn't update the table. All tables have a unique identifier, so duplicate errors are impossible. I use INNODB engine for all tables. I understand that stored procedure is a precompiled thing and I believe it could be something to do with cache but I couldn't find proper explanation or similar case online. I found when I use INSERT IGNORE INTO or INSERT REPLACE INTO, I receive the proper result with newly entries added to the storage table. Any ideas guys? Have a nice weekend ALL. Cheers, Igor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=joh...@pixelated.net -- Claudio
Re: stored procedure insert statement
Thanks Johnny, In this case I wouldn't be able to insert a completely new row but replace the existent one, so row count would stay the same. This is a storage table with the only unique constraints on: dda_debits_id column. the test data is very small, so I would've noticed any duplicates and they wouldn't make it to the table anyway with or without INSERT IGNORE. +---+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-++ | dda_debits_id | int(11) | NO | PRI |0 || | created_on| datetime| YES | | NULL || | reference_number | varchar(18) | YES | | NULL || | user_format_debit_ref | varchar(18) | YES | | NULL || | amount| int(11) | YES | | NULL || | debit_date| datetime| YES | | NULL || | status| tinyint(1) | YES | | NULL || | debit_type| tinyint(1) | YES | | NULL || | recharge_for_id | int(11) | YES | | NULL || | processed_on | datetime| YES | | NULL || | service_user_id | int(11) | YES | | NULL || +---+-+--+-+-++ Claudio, good point. Unfortunately, didn't work. I tried it before but no luck. Thanks, Igor On 07/09/2011 02:43 PM, Johnny Withers wrote: It seems to me that your insert statement is trying to insert duplicate rows into the storage table. This is why insert ignore and replace work. On Jul 9, 2011 3:49 AM, Igor Shevtsov nixofort...@googlemail.com mailto:nixofort...@googlemail.com wrote: Hi all, I can't explain strange behaviour of the INSERT statement in the stored procedure. The idea is to generate a list based on the output of 3 INNER JOIN of regularly updated tables. Something like : INSERT INTO storage (column list) SELECT column list FROM t1 JOIN t2 ON t1.x=t2.y JOIN t3 ON t2.z=t3.w WHERE CONDITIONS; The procedure runs daily by crontask and it inserts correct number of output rows. But after It runs and populated a storage table, I added new entries and expect to find them in the storage table. Even though they were picked up by SELECT statement, they haven't been INSERTed into the storage table. If I DELETE or TRUNCATE from the storage table and run the procedure all newly added entries and existed entries are their, but if I add new rows and run the procedure again It doesn't update the table. All tables have a unique identifier, so duplicate errors are impossible. I use INNODB engine for all tables. I understand that stored procedure is a precompiled thing and I believe it could be something to do with cache but I couldn't find proper explanation or similar case online. I found when I use INSERT IGNORE INTO or INSERT REPLACE INTO, I receive the proper result with newly entries added to the storage table. Any ideas guys? Have a nice weekend ALL. Cheers, Igor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=joh...@pixelated.net
Re: Send INSERT statement from MS SQL SERVER to MySQL
Save the sql statement to text file,then execute it inside the mysql shell. On Feb 12, 2008 2:00 PM, Mário Gamito [EMAIL PROTECTED] wrote: Hi, Is it possible to send an INSERT statement from a Windows server running MS SQL SERVER 2005 to a Linux box running MySQL ? If so, how ? Do I need any special tools ? Any help would be appreciated. Warm Regards, Mário Gamito -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a mysql DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Send INSERT statement from MS SQL SERVER to MySQL
Mário Gamito [EMAIL PROTECTED] wrote on 02/12/2008 01:00:25 AM: Hi, Is it possible to send an INSERT statement from a Windows server running MS SQL SERVER 2005 to a Linux box running MySQL ? If so, how ? Do I need any special tools ? Any help would be appreciated. Warm Regards, Mário Gamito We use MS SQL Server 2000 and MySQL and move data using DTS and an ODBC connection to MySQL. You can also try to set up SQL Server transactional replication to an ODBC data source. I experimented with this a while back and couldn't make it work, but it might be easier in MSSQL 2005. Donna
Send INSERT statement from MS SQL SERVER to MySQL
Hi, Is it possible to send an INSERT statement from a Windows server running MS SQL SERVER 2005 to a Linux box running MySQL ? If so, how ? Do I need any special tools ? Any help would be appreciated. Warm Regards, Mário Gamito -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question related to INSERT statement into table1 and SELECT statement from table1 to populate a column field into table1
I have a table with a PRIMARY KEY on id field, whos evalue is populated usin auto_increment. CREATE TABLE `key` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `sid` smallint(4) unsigned NOT NULL DEFAULT '0', `email` varchar(128) NOT NULL DEFAULT '', PRIMARY KEY (`id`), UNIQUE KEY `email` (`email`) ) ENGINE=InnoDB; Question: 1) When I INSERT a row is there any way to be able in the same INSERT statement (without doing an UPDATE after the insert) to populate the field `sid`, which is base on the value that the field `id` gets (e.g. sid= MOD (id, 20)) Sounds like an excellent case for a TRIGGER. Sorry, I think I'm wrong here -- This would only work if NEW.ID already has a value when the BEFORE INSERT trigger get's called, but I think it has not, as the id column would only be filled when doing the actual insert, and not before. You might wanna try that out. AutoInc stinks. Always does :-) Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Transactions and testing an Insert statement
Hello,
Me again. Excuse for sending two questions so closely together.
I'm looking through the MySQL manual (as well as searching Google and
the PHP site's MySQL functions) trying to find out how to test an Insert
statement (or any other statement for that matter).
Although I haven't found a direct answer, my searching usually points me
to transactions in InnoDB. Is this what I will need to use to do what I
want?
I'm preparing to import a bunch of data that is coming from an Excel
file from one the vendors we deal with and I want to find out what
manual data preparation I need to do. I'm using PHP's
mysql_real_escape_string as well as some other custom functions but I
need to find out if this is enough.
As I imagine it in my head:
TEST INSERT INTO `table` VALUES ('value', 'value');
And then get back a success or fail error code.
Using MySQL 4.1.
Thank you for your time,
Chris.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and testing an Insert statement
Chris W. Parker wrote:
Hello,
Me again. Excuse for sending two questions so closely together.
I'm looking through the MySQL manual (as well as searching Google and
the PHP site's MySQL functions) trying to find out how to test an Insert
statement (or any other statement for that matter).
Although I haven't found a direct answer, my searching usually points me
to transactions in InnoDB. Is this what I will need to use to do what I
want?
I'm preparing to import a bunch of data that is coming from an Excel
file from one the vendors we deal with and I want to find out what
manual data preparation I need to do. I'm using PHP's
mysql_real_escape_string as well as some other custom functions but I
need to find out if this is enough.
As I imagine it in my head:
TEST INSERT INTO `table` VALUES ('value', 'value');
You can't test an insert like this but you could do:
begin;
insert into table values(value1, value2);
rollback;
which does mean you need innodb tables. That will rollback any changes
that the insert does, however I'm not sure what happens to an
auto_increment field in that situation (ie does the value get rolled
back or is it left incremented).
The mysql_query function in php returns a resource or failure, so you
could do:
$result = mysql_query($my_query);
if (!$result) {
echo Query ${my_query} failed: . mysql_error() . br/;
} else {
echo Query ${my_query} worked!br/;
}
see php.net/mysql_query for more info.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Compound Insert Statement
Sheeri is correct. Rich's statement should have worked. What Rich is
looking for is the syntax for doing what the manual calls extended
inserts.
quoting TFM (http://dev.mysql.com/doc/refman/5.0/en/insert.html)
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
VALUES ({expr | DEFAULT},...),(...),...
[ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
That means that his 4 statements could be rewritten as :
INSERT INTO mytable (myfield) VALUES ('alpha'), ('bravo'), ('charlie'),
('delta');
Which is what he posted as his desired syntax. What puzzles me is if he
got it right the first time, why did he post the question?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
sheeri kritzer [EMAIL PROTECTED] wrote on 03/30/2006 12:41:01 AM:
That is, in fact, the exact correct syntax. What error are you
getting when you try to run that on the commandline? What version of
MySQL are you using?
-Sheeri
On 3/29/06, Rich [EMAIL PROTECTED] wrote:
Hi folks. I come to the list with another compound question.
My middleware allows me to build any syntax for the actual sql
statement, so I'm trying to minimize the work done to insert several
records at one try. I currently have multiple insert statements, but
can't find any reference to multiple records added using one insert
statement. I now have:
-SQL = INSERT INTO mytable (myfield) VALUES ('alpha');INSERT INTO
mytable (myfield) VALUES ('bravo');INSERT INTO mytable (myfield)
VALUES
('charlie');INSERT INTO mytable (myfield) VALUES ('delta');
What I would like to find is this:
-SQL = INSERT INTO mytable (myfield) VALUES ('alpha'), ('bravo'),
('charlie'), ('delta');
I am either looking in the wrong place in Dubois (Third) or it can't
be
done.
Any recommendations?
Appreciate it.
Cheers
Compound Insert Statement
Hi folks. I come to the list with another compound question.
My middleware allows me to build any syntax for the actual sql
statement, so I'm trying to minimize the work done to insert several
records at one try. I currently have multiple insert statements, but
can't find any reference to multiple records added using one insert
statement. I now have:
-SQL = INSERT INTO mytable (myfield) VALUES ('alpha');INSERT INTO
mytable (myfield) VALUES ('bravo');INSERT INTO mytable (myfield) VALUES
('charlie');INSERT INTO mytable (myfield) VALUES ('delta');
What I would like to find is this:
-SQL = INSERT INTO mytable (myfield) VALUES ('alpha'), ('bravo'),
('charlie'), ('delta');
I am either looking in the wrong place in Dubois (Third) or it can't be
done.
Any recommendations?
Appreciate it.
Cheers
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Compound Insert Statement
That is, in fact, the exact correct syntax. What error are you
getting when you try to run that on the commandline? What version of
MySQL are you using?
-Sheeri
On 3/29/06, Rich [EMAIL PROTECTED] wrote:
Hi folks. I come to the list with another compound question.
My middleware allows me to build any syntax for the actual sql
statement, so I'm trying to minimize the work done to insert several
records at one try. I currently have multiple insert statements, but
can't find any reference to multiple records added using one insert
statement. I now have:
-SQL = INSERT INTO mytable (myfield) VALUES ('alpha');INSERT INTO
mytable (myfield) VALUES ('bravo');INSERT INTO mytable (myfield) VALUES
('charlie');INSERT INTO mytable (myfield) VALUES ('delta');
What I would like to find is this:
-SQL = INSERT INTO mytable (myfield) VALUES ('alpha'), ('bravo'),
('charlie'), ('delta');
I am either looking in the wrong place in Dubois (Third) or it can't be
done.
Any recommendations?
Appreciate it.
Cheers
--
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]
Insert statement problem
I'm trying to build an insert query that will add a value into a field , after the first insert query adds the record. Relevant table information: +-+-++--+ | RecordID| InitOn | LenChoice | EndDate | + [PrimID,AutoInc]| [Date] | [int]| [Date] | +-+-++--+ So in the first insert the RecordID, InitOn (Using select NOW()), and LenChoice would be inserted. Looking something like this: +-+---++--+ | RecordID| InitOn| LenChoice | EndDate | +-+---++--+ | 10043 | 11/26/2004| 7 | | +-+---++--+ Now I try to use (and I've tried an update statement as well: Insert MyTable (EndDate) Values(DATE_ADD(InitOn, INTERVAL LenChoice DAY)) Which I would hope to result in: +-+---++--+ | RecordID| InitOn| LenChoice | EndDate | +-+---++--+ | 10043 | 11/26/2004| 7 |12/02/2004| +-+---++--+ However what is returned is an error message Column EndDate cannot be NULL. Anyway idea what I'm doing wrong ? Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert statement problem
Stuart Felenstein [EMAIL PROTECTED] wrote on 26/11/2004 13:54:31: I'm trying to build an insert query that will add a value into a field , after the first insert query adds the record. Relevant table information: +-+-++--+ | RecordID| InitOn | LenChoice | EndDate | + [PrimID,AutoInc]| [Date] | [int]| [Date] | +-+-++--+ So in the first insert the RecordID, InitOn (Using select NOW()), and LenChoice would be inserted. Looking something like this: +-+---++--+ | RecordID| InitOn| LenChoice | EndDate | +-+---++--+ | 10043 | 11/26/2004| 7 | | +-+---++--+ Now I try to use (and I've tried an update statement as well: Insert MyTable (EndDate) Values(DATE_ADD(InitOn, INTERVAL LenChoice DAY)) Which I would hope to result in: +-+---++--+ | RecordID| InitOn| LenChoice | EndDate | +-+---++--+ | 10043 | 11/26/2004| 7 |12/02/2004| +-+---++--+ However what is returned is an error message Column EndDate cannot be NULL. Anyway idea what I'm doing wrong ? I think you need an UPDATE statement UPDATE MyTable set EndDate=DATE_ADD(InitOn, INTERVAL LenChoice DAY) WHERE RecordID = value ; Insert *always* creates new records if successful and cannot be used to modify them. Update *always* updates recirds in position and cannot be used to insert them Replace is a hybrid whcih can do either if you set your indexes right. I think what you want is an Update, not an Insert. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [SOLVED]Insert statement problem
--- [EMAIL PROTECTED] wrote: I think you need an UPDATE statement UPDATE MyTable set EndDate=DATE_ADD(InitOn, INTERVAL LenChoice DAY) WHERE RecordID = value ; Insert *always* creates new records if successful and cannot be used to modify them. Update *always* updates recirds in position and cannot be used to insert them Replace is a hybrid whcih can do either if you set your indexes right. I think what you want is an Update, not an Insert. Alec Thank you Alex. It works. ! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Insert statement with large numbers gives Zero result
I have a database that is constantly moving large numbers around.
At the moment when a simple INSERT into Table_Name
('3573286532896523465328654654365436543'); is run
the value entered into the table is a zero. The field type that I am inserting
into is DOUBLE(200,0) unsigned.
Also this is all being done through PHP, but I am praying that it is a database
error
IMPORTANT INFORMATION
This message and any files transmitted with it are confidential and should be
read only by those persons to whom it is addressed.
If you have received this message in error, please notify us immediately by way
of reply.
Please also destroy and delete the message from you computer. Any unauthorised
form of reproduction of this message is strictly prohibited.
It is the duty of the recipient to virus scan and otherwise test the
information provided before loading onto any computer system.
EMRC does not warrant that the information is free of a virus or any other
defect or error.
EMRC is not liable for the proper and complete transmission of the information
contained in this communication, nor for any delay in its receipt.
Any views expressed in this message are those of the individual sender, except
where the sender specifically states them to be the views of EMRC.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Multiple Insert Statement?
My query:
SELECT * FROM wow.resume r INNER JOIN wow.candidate c WHERE r.Section_ID =
'1' AND MATCH (r.Section_Value) AGAINST ('+baan' IN BOOLEAN MODE) AND
c.Candidate_ID = r.Candidate_ID;
Is it possible to do a multiple insert statement like so?
INSERT INTO wow.candidate_erp
(Candidate_ID, Section_ID, Section_Value)
INSERT INTO wow.resume_erp
(Candidate_ID, Vendor_ID, etc.)
SELECT SQL_CALC_FOUND_ROWS *
FROM wow.resume r
INNER JOIN wow.candidate c;
Or do I have to break out the INSERT statements seperately?
Thanks!
- Eve
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple Insert Statement?
I know that this is off topic and such... but can you explain the
Match / Against that you used in your query? i have never seen syntax
like that in SQL
On Tue, 28 Sep 2004 14:16:56 -0400, Eve Atley [EMAIL PROTECTED] wrote:
My query:
SELECT * FROM wow.resume r INNER JOIN wow.candidate c WHERE r.Section_ID =
'1' AND MATCH (r.Section_Value) AGAINST ('+baan' IN BOOLEAN MODE) AND
c.Candidate_ID = r.Candidate_ID;
Is it possible to do a multiple insert statement like so?
INSERT INTO wow.candidate_erp
(Candidate_ID, Section_ID, Section_Value)
INSERT INTO wow.resume_erp
(Candidate_ID, Vendor_ID, etc.)
SELECT SQL_CALC_FOUND_ROWS *
FROM wow.resume r
INNER JOIN wow.candidate c;
Or do I have to break out the INSERT statements seperately?
Thanks!
- Eve
--
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: Multiple Insert Statement?
At 14:16 -0400 9/28/04, Eve Atley wrote:
My query:
SELECT * FROM wow.resume r INNER JOIN wow.candidate c WHERE r.Section_ID =
'1' AND MATCH (r.Section_Value) AGAINST ('+baan' IN BOOLEAN MODE) AND
c.Candidate_ID = r.Candidate_ID;
Is it possible to do a multiple insert statement like so?
No.
INSERT INTO wow.candidate_erp
(Candidate_ID, Section_ID, Section_Value)
INSERT INTO wow.resume_erp
(Candidate_ID, Vendor_ID, etc.)
SELECT SQL_CALC_FOUND_ROWS *
FROM wow.resume r
INNER JOIN wow.candidate c;
Or do I have to break out the INSERT statements seperately?
Yes.
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple Insert Statement?
That's the syntax used to do a full-text search in MySQL. Here's some
light reading:
http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
GH [EMAIL PROTECTED] wrote on 09/28/2004 03:14:21 PM:
I know that this is off topic and such... but can you explain the
Match / Against that you used in your query? i have never seen syntax
like that in SQL
On Tue, 28 Sep 2004 14:16:56 -0400, Eve Atley [EMAIL PROTECTED]
wrote:
My query:
SELECT * FROM wow.resume r INNER JOIN wow.candidate c WHERE
r.Section_ID =
'1' AND MATCH (r.Section_Value) AGAINST ('+baan' IN BOOLEAN MODE) AND
c.Candidate_ID = r.Candidate_ID;
Is it possible to do a multiple insert statement like so?
INSERT INTO wow.candidate_erp
(Candidate_ID, Section_ID, Section_Value)
INSERT INTO wow.resume_erp
(Candidate_ID, Vendor_ID, etc.)
SELECT SQL_CALC_FOUND_ROWS *
FROM wow.resume r
INNER JOIN wow.candidate c;
Or do I have to break out the INSERT statements seperately?
Thanks!
- Eve
--
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: Problem with insert statement; ERROR 1030 at line 188: Got error 28 from table handler
Hi Sebastian, If the new cds_catalog is created with primary key, this should produce a duplicate key error for the second iteration of the second table, as the data selected is from cds_catalog alone, but joining two tables causing cartisian joint to be formed(n*(m- t1.fieldt2.field)), each time the same set of data being inserted. reg, Eldo. On Mon, 27 Sep 2004 14:07:54 +0200 (CEST), Tobias Asplund [EMAIL PROTECTED] wrote: On Mon, 27 Sep 2004, Sebastian Geib wrote: I have a huge problem with the following insert statement: INSERT INTO cds_catalog SELECT cds_stage.cds_catalog.* FROM cds.cds_catalog, cds_stage.cds_catalog WHERE cds_stage.cds_catalog.prodidcds.cds_catalog.prodid; Look at this query, it will create a huge table as a result, as an approximisation the table created will have the number of rows in both tables multiplied with eachother. Has anyone else any idea? I tried all Google resources I could get hands on, but they were all about disk space on the tmp partition or repairing the db which both isn't the problem here. Are you sure 60GB is enough? Look above, say you have 1000 rows in each table, the result could be up to 100 rows. -- 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: Multiple Insert Statement?
Then I need help getting on the right track here. What I really want to do
is something like the following:
INSERT INTO wow.resume_erp (Candidate_ID, Section_ID, Section_Value) SELECT
* FROM wow.resume r WHERE r.Candidate_ID = '13103';
INSERT INTO wow.candidate_erp
(Candidate_ID, Vendor_ID, Last_Name, First_Name, Middle_Initial,
Condition_Type, Employer, Country_ID, Visa_Status, Dt_Visa, MMDD_Birth, SSN,
CSG_Comments, Working, Available, Start_Date, Location, HoldOnPeriod,
Relocation, Tech_Ranking, Comm_Ranking, Availability, Cert_Comments,
Dt_Submitted, Def_Rate, Def_Rate_Unit, Other_Country, Currency_id,
Interview_Availability, Interview_Contact, US_Experience, Location_Country)
SELECT *
FROM wow.candidate c
WHERE c.Candidate_ID = '13103';
Yet pulled from the resultset in this query:
SELECT * FROM wow.resume r INNER JOIN wow.candidate c WHERE r.Section_ID =
'1' AND MATCH (r.Section_Value) AGAINST ('+baan' IN BOOLEAN MODE) AND
c.Candidate_ID = r.Candidate_ID;
Perhaps the above isn't set up correctly, as when I attempt these queries:
INSERT INTO wow.candidate_erp
(Candidate_ID, Section_ID, Section_Value)
SELECT SQL_CALC_FOUND_ROWS *
FROM wow.candidate;
INSERT INTO wow.resume_erp
(Candidate_ID, Vendor_ID, Last_Name, First_Name, Middle_Initial,
Condition_Type, Employer, Country_ID, Visa_Status, Dt_Visa, MMDD_Birth, SSN,
CSG_Comments, Working, Available, Start_Date, Location, HoldOnPeriod,
Relocation, Tech_Ranking, Comm_Ranking, Availability, Cert_Comments,
Dt_Submitted, Def_Rate, Def_Rate_Unit, Other_Country, Currency_id,
Interview_Availability, Interview_Contact, US_Experience, Location_Country)
SELECT SQL_CALC_FOUND_ROWS *
FROM wow.resume;
...it returns an error of 1136: Column count doesn't match value count at
row 1. The query INSERT INTO wow.resume_erp (Candidate_ID, Section_ID,
Section_Value) SELECT * FROM wow.resume r WHERE r.Candidate_ID = '13103';
is impractical when my results are over 400.
Thanks,
Eve
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Multiple Insert Statement?
At 02:56 PM 9/28/2004, you wrote:
Then I need help getting on the right track here. What I really want to do
is something like the following:
INSERT INTO wow.resume_erp (Candidate_ID, Section_ID, Section_Value) SELECT
* FROM wow.resume r WHERE r.Candidate_ID = '13103';
You need to match up the columns in the Insert to the Select statement
(they both have to have the same number of columns and same column types
are preferred). So explicitly specify the columns in the Select statement as:
INSERT INTO wow.resume_erp (Candidate_ID, Section_ID, Section_Value) SELECT
Candidate_ID, Section_ID, Section_Value FROM wow.resume r WHERE
r.Candidate_ID = '13103';
Using * on your Select statements to fill an Insert is dangerous because
the table structure could change in the future.
Mike
INSERT INTO wow.candidate_erp
(Candidate_ID, Vendor_ID, Last_Name, First_Name, Middle_Initial,
Condition_Type, Employer, Country_ID, Visa_Status, Dt_Visa, MMDD_Birth, SSN,
CSG_Comments, Working, Available, Start_Date, Location, HoldOnPeriod,
Relocation, Tech_Ranking, Comm_Ranking, Availability, Cert_Comments,
Dt_Submitted, Def_Rate, Def_Rate_Unit, Other_Country, Currency_id,
Interview_Availability, Interview_Contact, US_Experience, Location_Country)
SELECT *
FROM wow.candidate c
WHERE c.Candidate_ID = '13103';
Yet pulled from the resultset in this query:
SELECT * FROM wow.resume r INNER JOIN wow.candidate c WHERE r.Section_ID =
'1' AND MATCH (r.Section_Value) AGAINST ('+baan' IN BOOLEAN MODE) AND
c.Candidate_ID = r.Candidate_ID;
Perhaps the above isn't set up correctly, as when I attempt these queries:
INSERT INTO wow.candidate_erp
(Candidate_ID, Section_ID, Section_Value)
SELECT SQL_CALC_FOUND_ROWS *
FROM wow.candidate;
INSERT INTO wow.resume_erp
(Candidate_ID, Vendor_ID, Last_Name, First_Name, Middle_Initial,
Condition_Type, Employer, Country_ID, Visa_Status, Dt_Visa, MMDD_Birth, SSN,
CSG_Comments, Working, Available, Start_Date, Location, HoldOnPeriod,
Relocation, Tech_Ranking, Comm_Ranking, Availability, Cert_Comments,
Dt_Submitted, Def_Rate, Def_Rate_Unit, Other_Country, Currency_id,
Interview_Availability, Interview_Contact, US_Experience, Location_Country)
SELECT SQL_CALC_FOUND_ROWS *
FROM wow.resume;
...it returns an error of 1136: Column count doesn't match value count at
row 1. The query INSERT INTO wow.resume_erp (Candidate_ID, Section_ID,
Section_Value) SELECT * FROM wow.resume r WHERE r.Candidate_ID = '13103';
is impractical when my results are over 400.
Thanks,
Eve
--
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: Multiple Insert Statement?
- Original Message -
From: Eve Atley [EMAIL PROTECTED]
To: Paul DuBois [EMAIL PROTECTED]; [EMAIL PROTECTED];
[EMAIL PROTECTED]
Sent: Tuesday, September 28, 2004 3:56 PM
Subject: RE: Multiple Insert Statement?
Then I need help getting on the right track here. What I really want to do
is something like the following:
INSERT INTO wow.resume_erp (Candidate_ID, Section_ID, Section_Value)
SELECT
* FROM wow.resume r WHERE r.Candidate_ID = '13103';
INSERT INTO wow.candidate_erp
(Candidate_ID, Vendor_ID, Last_Name, First_Name, Middle_Initial,
Condition_Type, Employer, Country_ID, Visa_Status, Dt_Visa, MMDD_Birth,
SSN,
CSG_Comments, Working, Available, Start_Date, Location, HoldOnPeriod,
Relocation, Tech_Ranking, Comm_Ranking, Availability, Cert_Comments,
Dt_Submitted, Def_Rate, Def_Rate_Unit, Other_Country, Currency_id,
Interview_Availability, Interview_Contact, US_Experience,
Location_Country)
SELECT *
FROM wow.candidate c
WHERE c.Candidate_ID = '13103';
Yet pulled from the resultset in this query:
SELECT * FROM wow.resume r INNER JOIN wow.candidate c WHERE r.Section_ID =
'1' AND MATCH (r.Section_Value) AGAINST ('+baan' IN BOOLEAN MODE) AND
c.Candidate_ID = r.Candidate_ID;
Perhaps the above isn't set up correctly, as when I attempt these queries:
INSERT INTO wow.candidate_erp
(Candidate_ID, Section_ID, Section_Value)
SELECT SQL_CALC_FOUND_ROWS *
FROM wow.candidate;
INSERT INTO wow.resume_erp
(Candidate_ID, Vendor_ID, Last_Name, First_Name, Middle_Initial,
Condition_Type, Employer, Country_ID, Visa_Status, Dt_Visa, MMDD_Birth,
SSN,
CSG_Comments, Working, Available, Start_Date, Location, HoldOnPeriod,
Relocation, Tech_Ranking, Comm_Ranking, Availability, Cert_Comments,
Dt_Submitted, Def_Rate, Def_Rate_Unit, Other_Country, Currency_id,
Interview_Availability, Interview_Contact, US_Experience,
Location_Country)
SELECT SQL_CALC_FOUND_ROWS *
FROM wow.resume;
...it returns an error of 1136: Column count doesn't match value count at
row 1. The query INSERT INTO wow.resume_erp (Candidate_ID, Section_ID,
Section_Value) SELECT * FROM wow.resume r WHERE r.Candidate_ID = '13103';
is impractical when my results are over 400.
The following simple script illustrates that multiple rows can be copied
from one table into another by putting a Select from the source table within
the Insert for the target table.
#use tmp;
#Drop/Create source table
drop table if exists source;
create table if not exists source
(idno smallint not null,
surname char(10) not null,
primary key(id));
#Populate source table
insert into source (idno, surname) values
(1, 'Adams'),
(2, 'Bailey'),
(3, 'Collins');
#Display populated source table
select * from source;
#Drop/Create target table
drop table if exists target;
create table if not exists target
(id smallint not null,
name char(10) not null,
primary key(id));
#Populate target table
insert into target (id, name)
select * from source;
#Display populated target table
select * from target;
The Insert/Select (second last statement in the script) will work as long as
the column list, which is '(id, name)' in this case, has the same number of
columns as is returned by the Select clause. In this case, the source table
has two columns so 'select *' returns two columns so we have satisfied this
requirement.
Also, the two columns identified in the column list must correspond in
datatype and size to the columns listed in the select. In this case, 'select
* from source' translates into 'select idno, surname from source'; idno is a
smallint as is the corresponding column in the target table, id; surname is
a char(10) as is the corresponding column in the target table, name.
Therefore, the Insert/Select works.
The Insert/Select could also have been written 'insert into target(id, name)
select idno, surname from source' and still worked.
However, this would not have worked:
insert into target(id, name) select surname, idno from source;
because the column names don't correspond in datatype and length: id does
not correspond to surname and name does not correspond to idno.
I hope this clarifies the use of Insert/Select for you.
Rhino
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple Insert Statement?
Sorry, there were a few typos in my reply. I have amended the reply at the
bottom
Rhino
- Original Message -
From: Rhino [EMAIL PROTECTED]
To: [EMAIL PROTECTED]; Paul DuBois [EMAIL PROTECTED];
[EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Tuesday, September 28, 2004 5:41 PM
Subject: Re: Multiple Insert Statement?
- Original Message -
From: Eve Atley [EMAIL PROTECTED]
To: Paul DuBois [EMAIL PROTECTED]; [EMAIL PROTECTED];
[EMAIL PROTECTED]
Sent: Tuesday, September 28, 2004 3:56 PM
Subject: RE: Multiple Insert Statement?
Then I need help getting on the right track here. What I really want to
do
is something like the following:
INSERT INTO wow.resume_erp (Candidate_ID, Section_ID, Section_Value)
SELECT
* FROM wow.resume r WHERE r.Candidate_ID = '13103';
INSERT INTO wow.candidate_erp
(Candidate_ID, Vendor_ID, Last_Name, First_Name, Middle_Initial,
Condition_Type, Employer, Country_ID, Visa_Status, Dt_Visa, MMDD_Birth,
SSN,
CSG_Comments, Working, Available, Start_Date, Location, HoldOnPeriod,
Relocation, Tech_Ranking, Comm_Ranking, Availability, Cert_Comments,
Dt_Submitted, Def_Rate, Def_Rate_Unit, Other_Country, Currency_id,
Interview_Availability, Interview_Contact, US_Experience,
Location_Country)
SELECT *
FROM wow.candidate c
WHERE c.Candidate_ID = '13103';
Yet pulled from the resultset in this query:
SELECT * FROM wow.resume r INNER JOIN wow.candidate c WHERE r.Section_ID
=
'1' AND MATCH (r.Section_Value) AGAINST ('+baan' IN BOOLEAN MODE) AND
c.Candidate_ID = r.Candidate_ID;
Perhaps the above isn't set up correctly, as when I attempt these
queries:
INSERT INTO wow.candidate_erp
(Candidate_ID, Section_ID, Section_Value)
SELECT SQL_CALC_FOUND_ROWS *
FROM wow.candidate;
INSERT INTO wow.resume_erp
(Candidate_ID, Vendor_ID, Last_Name, First_Name, Middle_Initial,
Condition_Type, Employer, Country_ID, Visa_Status, Dt_Visa, MMDD_Birth,
SSN,
CSG_Comments, Working, Available, Start_Date, Location, HoldOnPeriod,
Relocation, Tech_Ranking, Comm_Ranking, Availability, Cert_Comments,
Dt_Submitted, Def_Rate, Def_Rate_Unit, Other_Country, Currency_id,
Interview_Availability, Interview_Contact, US_Experience,
Location_Country)
SELECT SQL_CALC_FOUND_ROWS *
FROM wow.resume;
...it returns an error of 1136: Column count doesn't match value count
at
row 1. The query INSERT INTO wow.resume_erp (Candidate_ID, Section_ID,
Section_Value) SELECT * FROM wow.resume r WHERE r.Candidate_ID =
'13103';
is impractical when my results are over 400.
The following simple script illustrates that multiple rows can be copied
from one table into another by putting a Select from the source table
within
the Insert for the target table.
#use tmp;
#Drop/Create source table
drop table if exists source;
create table if not exists source
(idno smallint not null,
surname char(10) not null,
primary key(id));
#Populate source table
insert into source (idno, surname) values
(1, 'Adams'),
(2, 'Bailey'),
(3, 'Collins');
#Display populated source table
select * from source;
#Drop/Create target table
drop table if exists target;
create table if not exists target
(id smallint not null,
name char(10) not null,
primary key(id));
#Populate target table
insert into target (id, name)
select * from source;
#Display populated target table
select * from target;
The Insert/Select (second last statement in the script) will work as long
as
the column list, which is '(id, name)' in this case, has the same number
of
columns as is returned by the Select clause. In this case, the source
table
has two columns so 'select *' returns two columns so we have satisfied
this
requirement.
Also, the two columns identified in the column list must correspond in
datatype and size to the columns listed in the select. In this case,
'select
* from source' translates into 'select idno, surname from source'; idno is
a
smallint as is the corresponding column in the target table, id; surname
is
a char(10) as is the corresponding column in the target table, name.
Therefore, the Insert/Select works.
The Insert/Select could also have been written 'insert into target(id,
name)
select idno, surname from source' and still worked.
However, this would not have worked:
insert into target(id, name) select surname, idno from source;
because the column names don't correspond in datatype and length: id does
not correspond to surname and name does not correspond to idno.
I hope this clarifies the use of Insert/Select for you.
===
AMENDED REPLY
===
Most of what I said above is correct but the script had a mistake. (I
started editing the script on the fly to improve it but wasn't able to test
the amended version due to a temporary glitch on our server. I sent the note
anyway, assuming it was correct, and only discovered
Problem with insert statement; ERROR 1030 at line 188: Got error 28 from table handler
Hi! I have a huge problem with the following insert statement: INSERT INTO cds_catalog SELECT cds_stage.cds_catalog.* FROM cds.cds_catalog, cds_stage.cds_catalog WHERE cds_stage.cds_catalog.prodidcds.cds_catalog.prodid; Whenever I'm running it, it produces the error mentioned above and sadly it has nothing to do with either the memory nor the hdd space because on the hdd there's still more than 60% free and there's about 500 Megs of RAM free during the transaction. Hopefully anyone can help me because I don't know how to work around this transaction. Any hint is appreciated. Thanks in advance. Best regards, Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with insert statement; ERROR 1030 at line 188: Got error 28 from table handler
Sebastian Geib wrote: Hi! I have a huge problem with the following insert statement: INSERT INTO cds_catalog SELECT cds_stage.cds_catalog.* FROM cds.cds_catalog, cds_stage.cds_catalog WHERE cds_stage.cds_catalog.prodidcds.cds_catalog.prodid; Whenever I'm running it, it produces the error mentioned above and sadly it has nothing to do with either the memory nor the hdd space because on the hdd there's still more than 60% free and there's about 500 Megs of RAM free during the transaction. Hopefully anyone can help me because I don't know how to work around this transaction. Any hint is appreciated. Thanks in advance. Best regards, Sebastian Sebastian, Just googling it appears either one of the tables needs to be repaired or you're running of disk space for tmp tables. walt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem wil insert statement merging values
* dan orlic
INSERT INTO cp.Items SELECT distinct g.RecordID as id,'' as
category_id, '' as pattern_id,'' as manufacturer_id, g.Item + g.Desc1
+ g.Desc2 + g.Desc3 as description, g.Desc4 as price,0 as quantity, ''
as comments,'Active' as status,'n' AS is_bridal, 'Gallery' AS type,
now() as created, now() as last_modified FROM copperlamp.Items g order
by g.RecordID asc;
and it does not error out, but there are problems with this... for
example:
... g.Desc4 as price ... g.Desc4 ($500.00) is a varchar and price is a
BigDecimal(10.2). but when it gets inserted the value is 0.00 for
every field
The string $500.00 is easily identified as a price for a human, but mysql
don't know that $ means money. In general mysql will try to convert a
string to a number if the string is used in a numeric context, but a string
starting with $ is not identified as a number:
mysql select $500.00+0,500.00+0,mid($500.00,2)+0;
+-+++
| $500.00+0 | 500.00+0 | mid($500.00,2)+0 |
+-+++
| 0 |500 |500 |
+-+++
1 row in set (0.00 sec)
You could use MID(g.Desc4,2) in your statement to make mysql ignore the $
character.
that's one the other is:
...g.Item + g.Desc1 + g.Desc2 + g.Desc3 as description ... but though
they are all varchars i can't seem to incorporate all the values into
that one field. any thoughts would be great.
See the CONCAT() function:
URL: http://dev.mysql.com/doc/mysql/en/String_functions.html#IDX1246
CONCAT(g.Item,g.Desc1,g.Desc2,g.Desc3) as description
Often one would like a space between the columns that are concatenated, in
that case CONCAT_WS() is what you want:
CONCAT_WS(' ',g.Item,g.Desc1,g.Desc2,g.Desc3) as description
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
problem wil insert statement merging values
dan orlic wrote: INSERT INTO cp.Items SELECT distinct g.RecordID as id,'' as category_id, '' as pattern_id,'' as manufacturer_id, g.Item + g.Desc1 + g.Desc2 + g.Desc3 as description, g.Desc4 as price,0 as quantity, '' as comments,'Active' as status,'n' AS is_bridal, 'Gallery' AS type, now() as created, now() as last_modified FROM copperlamp.Items g order by g.RecordID asc; and it does not error out, but there are problems with this... for example: ... g.Desc4 as price ... g.Desc4 ($500.00) is a varchar and price is a BigDecimal(10.2). but when it gets inserted the value is 0.00 for every field that's one the other is: ...g.Item + g.Desc1 + g.Desc2 + g.Desc3 as description ... but though they are all varchars i can't seem to incorporate all the values into that one field. any thoughts would be great. dan -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Insert statement where value depends on auto-generated value from previous insert
I've got two separate tables, each with id fields that are auto-increment. The created fields below are timestamps. The tables are Innodb tables using transactions to process the statements. This will be an online registration process for our business, using PHP and MySQL. PHP is using session ids for tracking state. table1 id first_name last_name created etc. table2 - id table1_id created etc I need to insert a row into table1, using a null value for the id so it generate an id automatically. Then I need to insert a row into table2, including the id from table 1 (table1.id needs to be inserted into table2.table1_id). Any best practices to doing this? I've considered adding additional fields to both tables to represent a unique id or hash that will be generated by PHP. That way I can tie the two tables together and pull the newly-generated table1.id value out based on the unique hash and insert it into table2. But I'd rather not do that if I don't have to. I don't know if the tables are locked with transactions. If they were, I could insert into table1, then find the last id generated for table1 and then put that into table2. Do transactions behave this way? I can't just use the PHP session id because someone with the same session may register another person, and then I would have the same session ID in two rows. I can't assume the names or other info are unique either. Any ideas or is the PHP unique id/hash going to be my best bet? It just seems wasteful to have to add another field just to tie the data together until I can tie it together with the generated ids. I've looked through the PHP Cookbook and the MySQL cookbook and haven't seen a solution. I've thought about trying to make some unique hash with the data to be inserted but if there's another identical set of data, then the hash would match. I can't use a timestamp in the hash Now that I'm thinking about it, could I do this: 1. Generate a timestamp value 2. Insert into table1 all the info I need. 3. Create a unique hash from the timestamp and inserted info 4. Find the row and id from table1 where the hash of the timestamp value from step 1 and the data in the row match the hash from step 3 5. Put the id value into table 2 Seems like a lot of work to do to find the answer. Any gurus got an idea? Scott Plumlee PGP Public key: http://plumlee.org/pgp/ D64C 47D9 B855 5829 D22A D390 F8E2 9B58 9CBF 1F8D -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert statement where value depends on auto-generated value from previous insert
Scott Plumlee wrote: I've looked through the PHP Cookbook and the MySQL cookbook and haven't seen a solution. I've thought about trying to make some unique hash with the data to be inserted but if there's another identical set of data, then the hash would match. I can't use a timestamp in the hash I think I was trying to say I can't use the time that the row was created because I don't know if the timestamp value I get upon insert will exactly match the value I would get from something like a NOW() statement. IE, how do I know if the insert happens at the same moment I get a time value? -- Scott Plumlee PGP Public key: http://plumlee.org/pgp/ D64C 47D9 B855 5829 D22A D390 F8E2 9B58 9CBF 1F8D -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert statement where value depends on auto-generated value from previous insert
* Scott Plumlee I've got two separate tables, each with id fields that are auto-increment. The created fields below are timestamps. The tables are Innodb tables using transactions to process the statements. This will be an online registration process for our business, using PHP and MySQL. PHP is using session ids for tracking state. table1 id first_name last_name created etc. table2 - id table1_id created etc I need to insert a row into table1, using a null value for the id so it generate an id automatically. Then I need to insert a row into table2, including the id from table 1 (table1.id needs to be inserted into table2.table1_id). Any best practices to doing this? Use the LAST_INSERT_ID() function: INSERT INTO table1 SET first_name='roger',last_name='baklund'; INSERT INTO table2 SET table1_id=LAST_INSERT_ID(),etc='whatnot'; This function is connection specific, you will get the correct id even if you have multiple simultaneous users. URL: http://www.mysql.com/doc/en/ANSI_diff_Transactions.html#IDX141 URL: http://www.mysql.com/doc/en/Information_functions.html#IDX1428 -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert statement where value depends on auto-generated value from previous insert
Found that function right after I posted. Thanks, it should do exactly what I need. Roger Baklund wrote: * Scott Plumlee I've got two separate tables, each with id fields that are auto-increment. The created fields below are timestamps. The tables are Innodb tables using transactions to process the statements. This will be an online registration process for our business, using PHP and MySQL. PHP is using session ids for tracking state. table1 id first_name last_name created etc. table2 - id table1_id created etc I need to insert a row into table1, using a null value for the id so it generate an id automatically. Then I need to insert a row into table2, including the id from table 1 (table1.id needs to be inserted into table2.table1_id). Any best practices to doing this? Use the LAST_INSERT_ID() function: INSERT INTO table1 SET first_name='roger',last_name='baklund'; INSERT INTO table2 SET table1_id=LAST_INSERT_ID(),etc='whatnot'; This function is connection specific, you will get the correct id even if you have multiple simultaneous users. URL: http://www.mysql.com/doc/en/ANSI_diff_Transactions.html#IDX141 URL: http://www.mysql.com/doc/en/Information_functions.html#IDX1428 -- Roger -- Scott Plumlee PGP Public key: http://plumlee.org/pgp/ D64C 47D9 B855 5829 D22A D390 F8E2 9B58 9CBF 1F8D -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Adding NOW() to existing INSERT statement?
I'm trying to get a fixed entry date using two
DATETIME fields (dt_update, dt_create).
The first DATETIME field (dt_update)
automatically updates each time the record is
modified, the second DATETIME field (dt_create)
doesn't.
I've been able to get it using this:
INSERT INTO `test` SET dt_create = NOW()
However, what I really need is to have the above
INSERT statement (or something that
does the same thing) integrated into another
INSERT statement which looks
something like this:
INSERT INTO `test` (`Title`, `Email`, `City`,
`State`, `Country`, `URL`, `Date`,
`Description`, `rid`) VALUES ('Widget',
'[EMAIL PROTECTED]', 'Glendale', 'CA', 'USA ',
'http://www.widget.com', 'Tuesday, February 10,
2004 ', 'Widgets for sale', '')
Anybody know how to do this?
Many thanks in advance,
-Bob
__
Do you Yahoo!?
Yahoo! Finance: Get your refund fast by filing online.
http://taxes.yahoo.com/filing.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Adding NOW() to existing INSERT statement?
At 01:55 PM 2/10/2004, you wrote:
I'm trying to get a fixed entry date using two
DATETIME fields (dt_update, dt_create).
The first DATETIME field (dt_update)
automatically updates each time the record is
modified, the second DATETIME field (dt_create)
doesn't.
I've been able to get it using this:
INSERT INTO `test` SET dt_create = NOW()
However, what I really need is to have the above
INSERT statement (or something that
does the same thing) integrated into another
INSERT statement which looks
something like this:
INSERT INTO `test` (`Title`, `Email`, `City`,
`State`, `Country`, `URL`, `Date`,
`Description`, `rid`) VALUES ('Widget',
'[EMAIL PROTECTED]', 'Glendale', 'CA', 'USA ',
'http://www.widget.com', 'Tuesday, February 10,
2004 ', 'Widgets for sale', '')
Anybody know how to do this?
Many thanks in advance,
-Bob
Bob,
If you insert a NULL into a DateTime column that does not allow
nulls, it will set it to the current date time. So just replace your
'Tuesday, February 10,
2004 ' (which btw won't work because it has the wrong date format, should
be '2004-02-10') with NULL.
Mike
__
Do you Yahoo!?
Yahoo! Finance: Get your refund fast by filing online.
http://taxes.yahoo.com/filing.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Adding NOW() to existing INSERT statement?
Never mind! I figured it out :-) Thanks, -Bob __ Do you Yahoo!? Yahoo! Finance: Get your refund fast by filing online. http://taxes.yahoo.com/filing.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Insert statement with an ' in it
Help! I'm trying to do an insert statement where one of the fields sometimes contains an apostrophe. The field type is a varchar. Everytime that one of these values comes up with an apostrophe, it tells me there is an error in my SQL statement (obviously because it thinks there are more information than there are fields I'm inserting into) I'm sure this is something simple, but I can't seem to find the answer on the web. Please help! -Zach
Re: Insert statement with an ' in it
why don't you just insert your values after you escaped some special characters? specifically, you have to replace all ' by \' (prepend a single backslash character), and everything works fine! i guess you do your INSERTs from out of some programming language, like PHP, Perl or C. just use the appropriate str_replace() functions of that language to replace those apostrophes. -yves -Ursprngliche Nachricht- Von: Zachary Perschall [EMAIL PROTECTED] An: [EMAIL PROTECTED] Gesendet: Samstag, 28. Juni 2003 20:24 Betreff: Insert statement with an ' in it Help! I'm trying to do an insert statement where one of the fields sometimes contains an apostrophe. The field type is a varchar. Everytime that one of these values comes up with an apostrophe, it tells me there is an error in my SQL statement (obviously because it thinks there are more information than there are fields I'm inserting into) I'm sure this is something simple, but I can't seem to find the answer on the web. Please help! -Zach -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert statement with an ' in it
Well, the easiest thing is to escape any single quotes with a backslash character (\). PHP includes a function named addslashes() that does this. http://us4.php.net/manual/en/function.addslashes.php Same logic applies in other languages. Hope this helps. Andrew Help! I'm trying to do an insert statement where one of the fields sometimes contains an apostrophe. The field type is a varchar. Everytime that one of these values comes up with an apostrophe, it tells me there is an error in my SQL statement (obviously because it thinks there are more information than there are fields I'm inserting into) I'm sure this is something simple, but I can't seem to find the answer on the web. Please help! -Zach -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert statement with an ' in it
El Sb 28 Jun 2003 12:24, Zachary Perschall escribi: Help! I'm trying to do an insert statement where one of the fields sometimes contains an apostrophe. The field type is a varchar. Everytime that one of these values comes up with an apostrophe, it tells me there is an error in my SQL statement (obviously because it thinks there are more information than there are fields I'm inserting into) I'm sure this is something simple, but I can't seem to find the answer on the web. Please help! -Zach If you are programming in C, then you can use mysql_real_escape_string(). See the manual. Regards. -- Alfredo J. Cole http://www.acyc.com http://www.clshonduras.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using SET in the INSERT statement
At 11:37 +1000 2/7/03, boclair wrote:
- Original Message -
From: Paul DuBois [EMAIL PROTECTED]
To: boclair [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Friday, February 07, 2003 8:47 AM
Subject: Re: Using SET in the INSERT statement
: At 14:19 +1000 2/5/03, boclair wrote:
: I notice some of my colleagues using SET in INSERT statements.
:
: DuBois makes a short reference on p565, DoorStop1.
:
: The reference is short because there isn't much to say about it. :-)
: The SET clause consists of col_name=value assignments, separated
: by comma if there is more than one assignment. That's all.
:
: Other examples are on pp 40-41 (Doorstop I) or p48 (Doorstop II).
:
I know this is so basic that I should understand, but in that case (p40)
INSERT INTO member SET last_name='Stein' ,first_name='Waldo';
why not use that form always instead of (also p40)
INSERT INTO member (last_name,first_name) VALUES('Stein' , 'Waldo');
On the surface it seems a simpler syntax and is similar, in this
respect, to the syntax for UPDATE.
Correct.
I see that http://www.mysql.com/doc/en/INSERT.html provides that SET is
an alternative to the VALUES syntax but the explanation that follows,
apart from being a model of simplicity, only mentions the use of SET in
relation to unique columns.
You can use it for any column. Same applies for REPLACE.
BTW, many thanks for your excellent book. Invaluable (and it opens
flat)
Thanks!
Louise
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Using SET in the INSERT statement
At 14:19 +1000 2/5/03, boclair wrote: I notice some of my colleagues using SET in INSERT statements. DuBois makes a short reference on p565, DoorStop1. The reference is short because there isn't much to say about it. :-) The SET clause consists of col_name=value assignments, separated by comma if there is more than one assignment. That's all. Other examples are on pp 40-41 (Doorstop I) or p48 (Doorstop II). The Manual has some mention in http://www.mysql.com/doc/en/SET.html I sort of get it but I am looking for a fuller explanation. Louise - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Using SET in the INSERT statement
- Original Message -
From: Paul DuBois [EMAIL PROTECTED]
To: boclair [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Friday, February 07, 2003 8:47 AM
Subject: Re: Using SET in the INSERT statement
: At 14:19 +1000 2/5/03, boclair wrote:
: I notice some of my colleagues using SET in INSERT statements.
:
: DuBois makes a short reference on p565, DoorStop1.
:
: The reference is short because there isn't much to say about it. :-)
: The SET clause consists of col_name=value assignments, separated
: by comma if there is more than one assignment. That's all.
:
: Other examples are on pp 40-41 (Doorstop I) or p48 (Doorstop II).
:
I know this is so basic that I should understand, but in that case (p40)
INSERT INTO member SET last_name='Stein' ,first_name='Waldo';
why not use that form always instead of (also p40)
INSERT INTO member (last_name,first_name) VALUES('Stein' , 'Waldo');
On the surface it seems a simpler syntax and is similar, in this
respect, to the syntax for UPDATE.
I see that http://www.mysql.com/doc/en/INSERT.html provides that SET is
an alternative to the VALUES syntax but the explanation that follows,
apart from being a model of simplicity, only mentions the use of SET in
relation to unique columns.
BTW, many thanks for your excellent book. Invaluable (and it opens
flat)
Louise
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Using SET in the INSERT statement
Jennifer, It is the third alternative syntax. or INSERT [LOW_PRIORITY | DELAYED] [IGNORE] [INTO] tbl_name SET col_name=(expression | DEFAULT), ... [ ON DUPLICATE KEY UPDATE col_name=expression, ... ] Louise - Original Message - From: Jennifer Goodie [EMAIL PROTECTED] To: boclair [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, February 06, 2003 6:17 AM Subject: RE: Using SET in the INSERT statement : Are you referring to the data type SET or the Command SET? The manual page : you point to is talking about the data type, but usually when seen in an : insert it is the Command. I can't recall seeing the datatype referred to in : an INSERT, but maybe my brain isn't fully on yet today. : http://www.mysql.com/doc/en/INSERT.html : : : : -Original Message- : From: boclair [mailto:[EMAIL PROTECTED]] : Sent: Tuesday, February 04, 2003 8:19 PM : To: [EMAIL PROTECTED] : Subject: Using SET in the INSERT statement : : : I notice some of my colleagues using SET in INSERT statements. : : DuBois makes a short reference on p565, DoorStop1. : : The Manual has some mention in http://www.mysql.com/doc/en/SET.html : : I sort of get it but I am looking for a fuller explanation. : : Louise : : : : : : - : Before posting, please check: :http://www.mysql.com/manual.php (the manual) :http://lists.mysql.com/ (the list archive) : : To request this thread, e-mail [EMAIL PROTECTED] : To unsubscribe, e-mail : [EMAIL PROTECTED] : Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php : : : - : Before posting, please check: :http://www.mysql.com/manual.php (the manual) :http://lists.mysql.com/ (the list archive) : : To request this thread, e-mail [EMAIL PROTECTED] : To unsubscribe, e-mail [EMAIL PROTECTED] : Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php : - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Using SET in the INSERT statement
- Original Message - From: Keith C. Ivey [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, February 06, 2003 6:47 AM Subject: Re: Using SET in the INSERT statement : On 5 Feb 2003, at 14:19, boclair wrote: : : I notice some of my colleagues using SET in INSERT statements. : : DuBois makes a short reference on p565, DoorStop1. : : The Manual has some mention in http://www.mysql.com/doc/en/SET.html : : You seem to be confusing two unrelated meanings of SET. The SET in : INSERT statements is mentioned here: : :http://www.mysql.com/doc/en/INSERT.html : : It's just an alternative syntax so that an INSERT query can look like : an UPDATE query. : : The SET from the manual page you mention is a column type and would : be used in creating a table. Thanks, I had not seen the alternate syntax used before, and I was confusing the different usages of SET. Louise - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Using SET in the INSERT statement
On 5 Feb 2003, at 14:19, boclair wrote: I notice some of my colleagues using SET in INSERT statements. DuBois makes a short reference on p565, DoorStop1. The Manual has some mention in http://www.mysql.com/doc/en/SET.html You seem to be confusing two unrelated meanings of SET. The SET in INSERT statements is mentioned here: http://www.mysql.com/doc/en/INSERT.html It's just an alternative syntax so that an INSERT query can look like an UPDATE query. The SET from the manual page you mention is a column type and would be used in creating a table. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org Phone 202-667-6653 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Using SET in the INSERT statement
Are you referring to the data type SET or the Command SET? The manual page you point to is talking about the data type, but usually when seen in an insert it is the Command. I can't recall seeing the datatype referred to in an INSERT, but maybe my brain isn't fully on yet today. http://www.mysql.com/doc/en/INSERT.html -Original Message- From: boclair [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 04, 2003 8:19 PM To: [EMAIL PROTECTED] Subject: Using SET in the INSERT statement I notice some of my colleagues using SET in INSERT statements. DuBois makes a short reference on p565, DoorStop1. The Manual has some mention in http://www.mysql.com/doc/en/SET.html I sort of get it but I am looking for a fuller explanation. Louise - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Using SET in the INSERT statement
I notice some of my colleagues using SET in INSERT statements. DuBois makes a short reference on p565, DoorStop1. The Manual has some mention in http://www.mysql.com/doc/en/SET.html I sort of get it but I am looking for a fuller explanation. Louise - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SET in INSERT statement
I notice some of my colleagues have used SET in INSERT statements. DuBois makes a short reference on p565, DoorStop1. The Manual has some mention in http://www.mysql.com/doc/en/SET.html I sort of get it but I am looking for a fuller explanation. Louise (for the filter this time: sql,query,queries,smallint) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
select box result on page1 to insert statement table name on page2- not working
I first posted this on the php-db list, but am not getting the proper
results yet, so I am copying here, hopefully someone can help me figure
this one out...
I have a form with a select list and a hidden field to save the selected
item. On submit another page is loaded with a few fields to be filled in
and submitted to a table. Using get I see the data is being passed from
the first page to the second properly, and the second page sends its
data properly. The hidden field from the first page is to be used by the
second page as the name of the table in the query. I have a place where
I echo the contents of the hidden field just to be sure it is correct,
and that does indeed show what I expect. I then make the query statement
point to the variable but it always responds that it cannot find the
table. The table does exist, the variable does contain the appropriate
table name, but is not being replaced by the name. What am I doing
wrong?
On Sat, 2002-08-03 at 06:05, Rich Hutchins wrote:
Try referencing the $listbox variable in you SQL statement like this:
$sql = insert into .$listbox.
values(NULL,'$date','$exercise','$reps','$comments');
I'm guessing that it might also work like this:
$sql = insert into '$listbox'
values(NULL,'$date','$exercise','$reps','$comments');
I think your core problem is that the $listbox variable is not being
evaluated properly in the SQL statement. Once you solve that, you're
good to
go.
Hope this helps.
Rich
I tried both suggestions and neither are working. I am using the get
format for the form so I can see what is being sent, and I am getting
this:
http://192.168.1.53/workout-absflexor.php?exerciselist=%24listboxexercise=80
reps=12comments=submit=Send+Data
Notice that the $listbox variable is still not being sent. Notice the
two echo statements both of which shows what's in that variable, and it
displays the expected result (see code below). I am at a loss as to why
the one in the sql insert statement is not working.
If I replace the $listbox variable with the table name shown in the echo
statement, a connection is made and the query is completed.
--
Chip W
www.wiegand.org
[EMAIL PROTECTED]
Below is the code for the first page --
html
head
title/title
/head
body
div align=center
?
$exercises=array(absflexor,absmachine,leglifts);
echo form action='workout-absflexor.php' method='get';
echo table width='70%' border='0' align='center';
echo trth align='center'h2Exercise Data Input/h2/th/tr;
echo trth align='center'select name='listbox';
echo option$exercises[0]/option;
echo option$exercises[1]/option;
echo option$exercises[2]/option;
echo /selectbrbr;
echo /th/tr/table;
echo input type='hidden' name='exerciselist' value='$listbox';
echo input type='submit';
echo /form;
?
/div
/body
/html
And below is the code for the second page (the form hidden field was
later added, but still no good) (the echo statement at the bottom was
later added to verify the variable) --
!DOCTYPE html PUBLIC -//W3C//DTD XHTML 1.0 Transitional//EN
http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd;
html xmlns=http://www.w3.org/1999/xhtml;
head
titleWorkout Data Entry Form/title
style type=text/css
body { background-color: aqua; }
div.c1 {text-align: center}
/style
/head
body
div class=c1
h2Work-Out Data Entry Screen/h2
form action=? PHP_SELF ? method=get
input type='hidden' name='exerciselist' value='$listbox'
table summary= width=60% border=1 align=center
bgcolor=green
tr
thWeight/th
td align=leftinput type=text name=exercise
maxlength=4/td
/tr
tr
thReps/th
td align=leftinput type=text name=reps
maxlength=4/td
/tr
tr
thComments/th
td colspan=2textarea cols=50 rows=3
name=comments
/textarea/td
/tr
trtd? echo $listbox; ?/td/tr
/table
br /
input type=submit name=submit value=Send Data / input
type=reset /
/form
/div
?
if(isset($submit)):
$db = mysql_connect(localhost,root,carvin);
if(!$db) error_message(sql_error());
mysql_select_db(workout,$db) or die (Ack! Where's the database?);
$date = date(m-d);
$sql = insert into .$listbox.
values(NULL,'$date','$exercise','$reps','$comments');
mysql_query($sql) or die (Ack! No response when I queried the
server!);
endif;
echo $listbox;
?
/body
/html
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Error on insert statement...
At 17:30 -0400 6/21/02, Michael Jessop wrote: Can someone tell me what this means? I mean, I guess I *know* what it means, but why? Placeholders for Python scripts using MySQLdb should be %s, not ?. Convert your ? characters to %s and see what happens. The insert statement into a mysql database is... 'insert into works values(?, ?, ?, ?, ? ,?)' The invocation of the dynamic sql is... status = cur_mysql.execute(sql, (wrk_inst, ZERO, trs_inst, aas_inst, title, NADA)) (ZERO = 0L and NADA = 'N/A') I do a print of the wrk_inst, trs_inst, aas_inst (and zero and nada) followed by a print of title before the error... 80956 177802 0 0 N/A WANTED ... A BROTHER ...here is the error... Exception in Tkinter callback Traceback (most recent call last): File C:\Python22\lib\lib-tk\Tkinter.py, line 1292, in __call__ return apply(self.func, args) File C:\Documents and Settings\mjessop\My Documents\Python Development\Catalo g\catalog_gui.py, line 82, in engage catalog.extract_works() File C:\Documents and Settings\mjessop\My Documents\Python Development\Catalo g\catalog_model.py, line 249, in extract_works (wrk_inst, ZERO, trs_inst, aas_inst, title, NADA)) File C:\Python22\Lib\site-packages\MySQLdb\cursors.py, line 70, in execute raise ProgrammingError, m.args[0] ProgrammingError: not all arguments converted == here is a description of the database table... mysql describe works - ; +--+--+--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-+---+ | wrk_inst | int(11) | | MUL | 0 | | | sinker | int(11) | | MUL | 0 | | | trs_inst | int(11) | YES | | 0 | | | aas_inst | int(11) | YES | | 0 | | | title| varchar(255) | YES | | NULL| | | pub_name | varchar(80) | YES | | NULL| | +--+--+--+-+-+---+ 6 rows in set (0.03 sec) Any thoughts? Thank you, in advance... +++-+++--+++ + Michael S. Jessop+ | Senior Web Developer | + Copyright Clearance Center, Inc. + +++-+++--+++ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Error on insert statement...
Can someone tell me what this means? I mean, I guess I *know* what it means, but why? The insert statement into a mysql database is... 'insert into works values(?, ?, ?, ?, ? ,?)' The invocation of the dynamic sql is... status = cur_mysql.execute(sql, (wrk_inst, ZERO, trs_inst, aas_inst, title, NADA)) (ZERO = 0L and NADA = 'N/A') I do a print of the wrk_inst, trs_inst, aas_inst (and zero and nada) followed by a print of title before the error... 80956 177802 0 0 N/A WANTED ... A BROTHER ...here is the error... Exception in Tkinter callback Traceback (most recent call last): File C:\Python22\lib\lib-tk\Tkinter.py, line 1292, in __call__ return apply(self.func, args) File C:\Documents and Settings\mjessop\My Documents\Python Development\Catalo g\catalog_gui.py, line 82, in engage catalog.extract_works() File C:\Documents and Settings\mjessop\My Documents\Python Development\Catalo g\catalog_model.py, line 249, in extract_works (wrk_inst, ZERO, trs_inst, aas_inst, title, NADA)) File C:\Python22\Lib\site-packages\MySQLdb\cursors.py, line 70, in execute raise ProgrammingError, m.args[0] ProgrammingError: not all arguments converted == here is a description of the database table... mysql describe works - ; +--+--+--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-+---+ | wrk_inst | int(11) | | MUL | 0 | | | sinker | int(11) | | MUL | 0 | | | trs_inst | int(11) | YES | | 0 | | | aas_inst | int(11) | YES | | 0 | | | title| varchar(255) | YES | | NULL| | | pub_name | varchar(80) | YES | | NULL| | +--+--+--+-+-+---+ 6 rows in set (0.03 sec) Any thoughts? Thank you, in advance... +++-+++--+++ + Michael S. Jessop+ | Senior Web Developer | + Copyright Clearance Center, Inc. + +++-+++--+++ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
an example INSERT statement
Hello,
I'm having some trouble constructing my first INSERT statement into
multiple tables. I'm using PHP4. I have written the following INSERT
statement with no problems:
$sql = INSERT INTO main (name, ext, stor_pre, stor_base, width, height,
file_size, proj_id, date_cre, cre_by, division) VALUES ('$name', '$ext',
'$stor_pre', '$stor_base', '$width', '$height', '$file_size',
'$proj_id', '$date_cre', '$cre_by', '$division');
This inserts the variables as values into the designated columns of the
table main.
But I have expanded my database, restructuring it so that it no longer
uses one table (main). These various columns are now distributed
across five different tables. If I were to SELECT data from these
tables, I would use a join statement. How would I go about writing an
INSERT statement that joins several different tables together? Even a
pointer to where I can find this info would be helpful. The INSERT
syntax page in the official documentation
(http://www.mysql.com/doc/I/N/INSERT.html) doesn't address this specific
circumstance, or if it does then I'm too dense to figure it out.
Thanks!
Erik
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: insert statement new trouble
At 2:43 AM + 10/31/01, Curtis Gordon wrote: I have a query where I am inserting a record into a db, and would like to have the primary key value returned, I have been reading and reading, but I can't seem to find any mention of this. I would think that this would be useful. Can anybody help? You can't get the primary key value returned from the INSERT statement itself. But (assuming the key is an AUTO_INCREMENT column), you can issue this query after the INSERT to get the value: SELECT LAST_INSERT_ID() - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
insert statement
hi Jim, I am finding a TON of errors like this one.. [Sat Oct 27 01:14:57 2001] [error] [client **.***.***.**] File does not exist: c:/phpweb/scripts/..%5c/winnt/system32/cmd.exe I am not on a winnt system though, I'm running winME here at home. It looks to me like somebody is trying to access my system, and I don't see any errors that mention mysql at all. _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: insert statement
That's W32.Nimda, I believe. Maybe Code Red. Every 10 minutes or so, you'll get 16 or so attempts shot to yer log. But yes, this has nothing to do with MySQL. Mike Curtis Gordon wrote: hi Jim, I am finding a TON of errors like this one.. [Sat Oct 27 01:14:57 2001] [error] [client **.***.***.**] File does not exist: c:/phpweb/scripts/..%5c/winnt/system32/cmd.exe I am not on a winnt system though, I'm running winME here at home. It looks to me like somebody is trying to access my system, and I don't see any errors that mention mysql at all. _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: insert statement
omg! code red!! I JUST installed apache and mysql the other day because my host went down and I didn't want to miss out on dev time! Is is possible that the downloads i grabbed were already infected? _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: insert statement
Hi Curtis! On Sat, 27 Oct 2001, Curtis Gordon wrote: omg! code red!! I JUST installed apache and mysql the other day because my host went down and I didn't want to miss out on dev time! Is is possible that the downloads i grabbed were already infected? I think you're fine. Those were Nimda hits -on- you, not from you. (Code Red looks for default.ida.) You're probably OK because those lines said error - file not found. Have a :) day! jb ob-filter: database sql table -- jim barchuk [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
server rejecting 15MB insert statement
Hello, I am trying to insert large TEXT data into a MySQL database. The server rejects insert statements that include a 15MB text file. The manual says to change max_allowed_packet for the server to accept large data. I have set this on the server side to 65535KB, which appears to be the largest value allowed for that variable. The server still rejects large packets. Using the MM JAVA driver, errors suggest the server is set at 64KB. mysqladmin variables definitely shows something larger than 64KB (actually it says 67107840). I am using MySQL version 3.23.31. Is there a client side variable that also needs to be defined? Thanks, Anthony -- Anthony Lau - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Sub-selects in a insert statement - is it possible to rewrite?
As I've understood it mysql doesn't support sub-selects inside for example insert statements (apart from inset ... select). Background-stuff: What I'm working on is parsing logfiles and inserting them into a mysql database through the C API, to minimize traffic and to maximize time, I was hoping I could get away with a single insert query instead of having to do a select and then use that information to form a insert. This is how I'd like the query to work: insert into host (host_id, host_IP) values ((select from mail mail_id where mail_addr = [EMAIL PROTECTED]), '127.0.0.1') The table host has columns: email, host, host_id where host_id would be used as a forreign key in a table with the actual log entries in. Is it possible to do this in a single INSERT or would I have to use a select and then a separate insert? I'm open for any suggestions (and no the host columns shouldn't be normalized into the table with the log entries as that would be a table with 200k+ rows, and I'd prefer to have hosts in a separate table to make it easier to present a list of hosts that have logged something without doing queries on the much bigger log table). Cheers, -- Andreas D Landmark / noXtension Real Time, adj.: Here and now, as opposed to fake time, which only occurs there and then. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Sub-selects in a insert statement - is it possible to rewrite?
This is how I'd like the query to work: insert into host (host_id, host_IP) values ((select from mail mail_id where mail_addr = [EMAIL PROTECTED]), '127.0.0.1') Try: insert into host (host_id, host_IP) select mail_id, '127.0.0.1' from mail where mail_addr = '[EMAIL PROTECTED]'; - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Special Characters in INSERT Statement
I apologize because I know I've seen this somewhere on the list before
(should have saved it). I'm trying to insert some data into CHAR fields
that have trailing Ascii 0 characters, but get a syntax error.
The data would like this:
23302220202X
Where "X" represents the ascii character 0.
INSERT INTO MyTable (ThisNumber) VALUES
('23302220202X')
...Creates the syntax error.
Any help would be appreciated.
Lee Jenkins
Re: Special Characters in INSERT Statement
At 5:30 PM -0500 3/23/01, Lee Jenkins wrote:
I apologize because I know I've seen this somewhere on the list before
(should have saved it). I'm trying to insert some data into CHAR fields
that have trailing Ascii 0 characters, but get a syntax error.
The data would like this:
23302220202X
Where "X" represents the ascii character 0.
Escape them using \0.
http://www.mysql.com/doc/S/t/String_syntax.html
INSERT INTO MyTable (ThisNumber) VALUES
('23302220202X')
...Creates the syntax error.
Any help would be appreciated.
Lee Jenkins
--
Paul DuBois, [EMAIL PROTECTED]
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
