Insert Data Into 2 Tables With 1 Query Fails
I found 2 old threads on how to insert data into 2 tables with 1 query. Both of them said that i should seperate the inserts with a ;. But i must be doing something wrong, because it comes up with an error like this ".....right syntax to use near ' INSERT INTO eiland_details"
What am i doing wrong? It's possible, right? To insert data into multiple tables with 1 query?
$query = "
INSERT
INTO sub_pages
(mainpage_id, intro_text)
VALUES
('$main_data->mainpage_id','$sub_intro');
INSERT
INTO eiland_details
(inwoners, oppervlakte)
VALUES
('$detail_inwoners',$detail_oppervlakte')";
View Complete Forum Thread with Replies
Related Forum Messages:
Mysql Query Fails Due To A ' In Data.
I am having problems now with the following code. An example of my topic. "we'll meet again" But when the following line searches for it i get an erro saying that the code is wrong. I know that it is to do with the ' , but how do Iget round this problem i have. without having to remove all of them from the data. $topics = mysql_query("SELECT * FROM `forum_topics` WHERE `topic` = '" . $topic . "'") or die(mysql_error());
View Replies !
Insert Fails On Access
I have a database with about ten tables. I'm trying to fill one table with some dummy data (its a contact manager table holding names of people, addresses and whatever but I'm createing random values for the moment). The insert fails telling me that access is denied - however I use the exact same username/password/database name/host name on the other tables, and the insert and updates work just fine. I know this sounds odd - but is there any other reason as to why I might get the access denied message, other than what I assume should be just for an invalid username/password? Code:
View Replies !
Insert Data Into 2 Tables Using Php
i was wondering how i would go about insert data collected on a php form into 2 separate tables. i want to collect email and password for an admin table and everything else for a separate table why cant i just do this? $query = "INSERT INTO user_admin (s_email, s_password, salt) VALUES ('$email', '$encrypted', '$salt')"; mysql_query ($query) or die ('Could not register new account.'); $query = "INSERT INTO student (s_forname, s_surname, s_uni_id, s_email, s_course,) VALUES ('$forename', '$surname', '$uni_id', '$email', '$course')"; mysql_query ($query) or die ('Could not register new account.2'); the email and password get stored into the user_admin table but nothing is collected for teh student table do the values such as $forename need to be declared anywhere else before?
View Replies !
How To Insert Data Into 2 Tables At Once
I am realizing now that I have never done this before. I need to insert information - all at once into two seperate tables joined by a fk. So essentially I need to insert into one table then get the primary id from it and then insert this value long with the other fields - into the 2nd table. Can I do this without writing 2 insert statements - perhaps using a join? If so what is the basic syntax?
View Replies !
Insert Multiple Fails Completely If Any Duplicates Exist
I've been having some problems with database performance... Several threads are constantly attempting INSERTs of new records into a large table - that is hundreds of thousands of records -large by my account :-) The table has a VARCHAR field tagged as UNIQUE and inserts are kind of slow. i'm relying on the INSERT to fail on duplicates, i'm not performing any checking before i attempt the INSERT (i figured an additional SELECT would slow things even more - but any advice to the contrary would be appreciated). While these back end threads are constantly banging away with their INSERTS, front end users are hitting the DB with searches. so i end up with several threads simultaneously attempting very expensive DB operations. the result is that the front end that users see is DREADFULLY slow. My proposed speedup is to modify the behavior of the backend threads so that they don't constantly attempt INSERTS of single entries, but build up a hashtable of local entries and at some interval, attempt a multiple insert something like: INSERT INTO table (col1,col2,col3 VALUES (x1,y1,z1), (x2,y2,z2).... While i do expect to take a big hit when these multiple inserts launch, in the time between, i expect front end performance to be significantly improved. The problem with this situation is that the multiple insert may contain one or more new entries which will cause duplicates on my table's UNIQUE VARCHAR field. I would like to have duplicates ignored on insert, but have all newly unique entries be added. Sadly, from what i've seen, once a duplicate violation for any of the new entries is found, the call fails, leaving my newly UNIQUE entries NOT ADDED. Is there any way to get the call to work as i want? Doing many single row inserts sounds like it'll be much slower (judging from what i've read) so i'd rather not resort to that. I don't have a ton of DB experience (which may be obvious) so if i've overlooked something simple please let me know. If there's a better way to improve performance other than these multiple inserts, that would be nice to know about too. Thanks for any insight,suggestions, etc...
View Replies !
Insert Data Into Related Tables
I'm sure that this question has been asked many a times but I can't find it, probably don't know the right keywords to find it. I am new to MySQL, working knowledge of MS Access, so quite a leap forward. I have 2 tables (for learning purposes) 1. pages page_id page_title ... 2. articles article_id page_id article_title ... If a create a new article_id for which a page_id does not already exist, what method must I use. Basically how can I create a new article and page entry simultaneuosly? Must I do 2 seperate inserts (is this when a transaction should be created) or one using some sort of join? How do I ensure that whatever auto-increment page_id number is created by the database is associated to my new article entry?
View Replies !
Any Way To Insert Data Into Two Tables At The Same Time?
what I have is a table called `order` and a table called `lmcharge`. a person inputs data from a form and i need to take the data from both and some goes into one table, some goes into another here is an example: order values: student_id, class_id, order_type, date, status //student_id, class_id, order_type will be from the form lmcharge values: student_id, order_id, lm_number //student_id, and lm_number will also be from the form but order_id will have to be from the order table anyone have a clue?
View Replies !
Insert Data Into 10 Multiple Tables
Can I insert data into multiple tables at once? Basically just need to insert a number into the 'membersID' column into 10 tables, but is taking ages, so wondering if I can write a query which targets all the tables or is this impossible in SQL?
View Replies !
Create A Query To Insert Data
I have a form that includes a bunch of fields to fill out. I have the query working for most of them, but right now am stuck on getting one area to work. I have 7 fields where you can input a parts number (name=number[]) and then another 7 fields where you input the description of the part (name=description[]). I want to put them into a database that has one column for the number, one column for the description and then a primary key and foreign key to eventually join them to the main workorder table. I am not sure how to make a query that will INSERT them into the DB columns, one row for each number/description combination, and check to make sure there is data in the rows. There may not always be 7 part number/descriptions in the form fields and I don't want a bunch of empty rows in the DB table.
View Replies !
Insering Data Into Bit Field Fails
I know there's an issue with mySQL not being able to display BIT values, but that's not the issue, it's just not inserting them. Here's some info about server / table before i explain the problem. mysql Ver 14.12 Distrib 5.0.24a, for pc-linux-gnu (i486) using readline 5.1 The field causing problems is bit(3) Right, when I do... INSERT INTO waypoints (lat_long,date,type,marker) VALUES('12313123132,1231321','1174415400','2','1'); I get the line inserted and I get a warning, as follows. "Out of range value adjusted for column 'type' at row 1" At first I assumed this was because I was entering a decimal number, but when I do an update it inserts the decimal number correctly.
View Replies !
Query Fails With Db Name Qualification
I have a database named xdiabolicasolutions-main and a table named users. This query fails: SELECT COUNT(*) AS test FROM xdiabolicasolutions-main.users while this works: SELECT COUNT(*) AS test FROM users The error is: #1064 - You have an error in your SQL syntax; che.... Is there a limit on the length of the db name or am i using any illegal characters here?
View Replies !
Using IF() For Choosing The Query Fails
The following code is for the Archive. (Where users can store special messages) I want to receive one row for each message that has been sent OR that has been received. With the code beneath I get this error: "#1241 - Operand should contain 1 column(s)". How can I fix this? INFO AND CODE ......
View Replies !
Query Fails In MySQL 5
I just migrated a Mambo CMS site from a server runing MySQL 4 something to one running MySQL 5.0 and am getting one problem (was expecting many more) with a query generated in the admin section: SELECT c . * , g.name AS groupname, cc.name, u.name AS editor, f.content_id AS frontpage, s.title AS section_name FROM mos_content AS c, mos_categories AS cc, mos_sections AS s LEFT JOIN mos_users AS u ON u.id = c.checked_out LEFT JOIN mos_groups AS g ON g.id = c.access LEFT JOIN mos_content_frontpage AS f ON f.content_id = c.id WHERE c.state >=0 AND c.catid = cc.id AND cc.section = s.id AND s.scope = 'content' AND c.sectionid = ཊ' ORDER BY cc.ordering, cc.title, c.ordering LIMIT 0 , 10 MySQL said: Documentation #1054 - Unknown column 'c.checked_out' in 'on clause' The problem appears to be with the fact that the table mos_content is aliased as c and the the SELECT uses a wilcard to select all its columns. For some reason, when the ON clause test the value in any of the colums using the alias, it says they are onknown.
View Replies !
UPDATE Fails On Somewhat Large Data Sets
I am running into a problem with an UPDATE statement. I am using MySQL 5.0 server, and the mysql command line client that came with it. update tableA a, tableB b set a.value = b.value where a.key1 = 'foo' and a.id = b.id and a.col2 = b.col2; The tables use InnoDB as the engine. With small data sets, this works fine. However, with larger data sets, the UPDATE runs for a long time, then I get an error saying that the number of locks exceeds lock table size. In my case, the data sets in the two tables are < 5 million rows. I kept monitoring the InnoDB status, and see the number of log entries go up until finally rolling back. I saw a bug on the mysql bugsite: http://bugs.mysql.com/bug.php?id=15667 which sounds like the problem I am facing - though my data sets are not as large as the bug states. How do I overcome this issue? I was thinking about splitting the query into smaller ones, using LIMIT and doing some sort of ordering to guarantee same order of rows retrieved. Any better ideas? Maybe configuring the InnoDB differently (I read that the lock table size cannot be changed, darn.)
View Replies !
Cross Table Query Fails
I get this error which I can't quite understand....PHP Code: 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 'select x10_channel from devices where device = 'Lamp')' at line The line mentioned involves a cross table query:PHP Code: $sql = "select commandID from commands where x10_channel = (select x10_channel from devices where device = '$device')"; which works perfectly on a friends laptop. I'm guessing there is a configuration difference between our installations.
View Replies !
Fails To Start After Primary Server Fails
i have configured mysql Master Master Replication + DRBD + Heartbeat after Primary server Fails mysql should mount to secondary server and it should run automatically. I noticed that after primary server fails the mysql mounted in secondary server, but it fails to start automatically. here is my drbd.conf vi /etc/drbd.conf resource mysql { protocol C; #incon-degr-cmd "echo '!DRBD! pri-on-incon-degr' | wall ; sleep 60 ; #halt -f"; handlers { pri-on-incon-degr "..."; } startup { degr-wfc-timeout 120; # 2 minutes. } disk { on-io-error detach; } net { } syncer { rate 3M; #group 1; #al-extents 257; } on mysql01 { device /dev/drbd0; disk /dev/hdd6; address 192.168.1.34:7788; meta-disk /dev/hdd5[0]; } on mysql02 { device /dev/drbd0; disk /dev/sda5; address 192.168.1.35:7788; meta-disk /dev/sda6[0]; } }
View Replies !
Update Query Fails In Previous MySQL Version
The following query runs fine on my development server (v4.0.17): "update user inner join events on user.username = events.username set user.LastMessageDate = '$current_time' where events.text_sent=0 and events.to_mobile >0 and events.GMT_event_send_time<='$send_time'" but fails on my production server running v3.23.56. Anyone any ideas why it doesn't run and what I need to do to fix it?
View Replies !
Get 4 Tables Data In One Query
I am new to mysql relational programming, and creating a tutorial site like pixel2life. I am trying to get the data from 4 tables 1st table is main tutorials table, 2nd is subcategory (subcat) table, 3rd is categories table and 4th is users table. I don't know how to do it exactly, I googled but not got the proper method. I only got some hints http://www.brainbell.com/tutorials/M...ing_A_Join.htm from this site. I've created the following query which selects the title from tutorials table, subcategory (s_cat) from subcat table which reference is available as s_cat_id in tutorials table, then category (cat) from categories table which reference is available in subcategory (subcat) table and username from users table which reference is availble in tutorials table. Can somebody tell me this query is correct or there is any alternate method PHP SELECT d.title, s.s_cat, c.cat, u.username FROM tutorials d JOIN subcat s ON d.s_cat_id=s.id JOIN categories c ON s.cat_id=c.id JOIN users u ON d.author_id=u.id
View Replies !
Query Based On Data Of 3 Tables
this is what I've been trying to implement: I have, say, three tables. One is a user table, with id, username, etc Second is a question table with Question ID, Question, Category etc And third is an "answers" table that keeps answers given by the users. It has the classic id key, and Question id and User Id columns. What I want to do is this: Select a random question of category 1 lets say from the questions table for which user X has no record of answering in the answers table.
View Replies !
Retrieve Data From 2 Tables In 1 Query
I've got 2 tables content & sections I need to: SELECT id, title, section_id , status FROM content and now I'd like to get title from sections WHERE id = section_id from the first part of the query. I've tried with unions but doesn't seem to like it..
View Replies !
Merge Sort-of Similar Data From 2 Tables In Query
A bit of background. I'm not a web person, and I know enough about databases to scrape by. Most of my SQL is generated using templates or handy things (like PHPMyAdmin etc). I'm also not a PHP coder. I am a C/C++ coder so I've found I can hack some PHP together, and I've got roped into helping a friend with a problem Here's the issue. I have a MySQL database with 2 tables of interest. Let's call these tables categorydata and itemdata. Both of these tables contain records that describe individual items - the item name, its ID in the system, the date it was added, some free text about it etc. To be absolutely clear, the purpose of the categorydata table is for a record to define a category (think "puzzles", "board games", "vegetables", whatever), but the record row also includes data on the FIRST item in the category ("banana", "cluedo" etc) along with all its data. The purpose of the itemdata table is to list all the OTHER items in the category ("grapes", "twister" etc). Here's an example record in the categorydata table: categoryname vegetables categoryid 1234 categorycreated 10th Jan 2000 categoryowner Bob firstitemid 4567 firstitemname cucumber firstitemcreated 12th Jan 2000 firstitemdesc It's long and green Here's an example record in the itemdata table: itemid 4568 itemname orange itemcreated 13th Jan 2000 itemdesc It's round and, er, yeah. This seems really bad design to me but what do I know. Here's what I need to do. I need to know how to merge the data in these 2 tables in a single query, ordered by the itemid and firstitemid fields. In other words, I need the query to merge the data, ordering it so that regardless of the table the record came from the itemids are all in correct numerical order. I need to do this so that I can retrieve each item's data in order. itemid in the itemsdata table and firstitemid in the categorydata table are unique, so when merged there won't be any colliding ids. I need to put this in a PHP script (in a loop that dumps out all the items in order), but if someone can just help me with the SQL query syntax I'd appreciate it, I can do the rest. I think this has something to do with inner joins but I really don't understand how to make it work, or how to retrieve and merge data from 2 tables at once bearing in mind the fields (columns? what do you call them) have different names even though they have the same purpose (ie: firstitemid -v- itemid, firstitemname -v- itemname etc).
View Replies !
Determining Which Table To Query Based On Data Within Tables
I have 2 tables: default_categories column 1: category_id column 2: category_name column 3: category_parent custom_categories column 1: custom_cat_id column 2: custom_cat_name column 3: custom_cat_parent The custom_categories table won't necessarily have anything in it but if it does, I need to choose the data from the custom_categories table over the data from the default_categories table. So if the default category has 3 rows with IDs | names: 123 | Dogs 456 | Cats 789 | Fish And the custom category has 1 row with IDs | names: 456 | Very Cute Cats I want my query of these 2 tables to produce the following IDs | names: 123 | Dogs 456 | Very Cute Cats 789 | Fish I've tried joins like the one below but they aren't working because if there is no custom_cat_id, it won't give me the result for the default category_id. MySQL SELECT * FROM default_categories LEFT JOIN custom_categories ON category_id = custom_cat_id WHERE category_parent = '' AND custom_cat_parent = '' ORDER BY $order_by $sort
View Replies !
How To Insert Multiple Rows With 1 Insert Query
I am having a form on the front end which has for example 3 rows each with 3 columns. The user enters data in all the 3 rows. When he hits the add button these should get in the database. What insert query would I write to add all of them together to the database? Do I need to use some procedure?
View Replies !
2 Tables INSERT
I have two tables, and both have an id field. the id field in the first table is auto_increment, so when i insert data, i leave this field out. the problem is, i need to insert the same id number into my other table, but i cannot use auto_increment in the id field in the second table, because there are more than one occurence of each number.
View Replies !
INSERT Into Two Tables
How do I insert data into two tables at the same time? My DB structure is as follows: CREATE TABLE `speakers`( `speaker_id` INT(9) NOT NULL auto_increment, `speaker` VARCHAR(30) NOT NULL, PRIMARY KEY (speaker_id); ) CREATE TABLE `RSS_info`( `cast_id` INT(9) NOT NULL auto_increment, `speaker_id` INT(9) NOT NULL, `title` VARCHAR(50) NOT NULL, `filename` VARCHAR(20) NOT NULL, `description` VARCHAR(200) NOT NULL, `pubDate` VARCHAR(32) NOT NULL, `size` INT(20) NOT NULL, PRIMARY KEY (cast_id), FOREIGN KEY (speaker_id) REFERENCES `speakers` (speaker_id) on delete cascade ); TYPE=MYISAM My INSERT statement for one table is below. INSERT INTO `speakers` VALUES (NULL, 'Speaker Name'); What I need to be able to do is insert the rest of the data that I'll be receiving into the second table.
View Replies !
Insert Tables
I am using mysql through the contol panel on windows server. basically trying to build a dynamic website. In the control panel, i can see the menus to create DSN and database. Can anyone tell me where do i create the tables there is no site builder available, so should i create externally and then upload through ftp.
View Replies !
Insert In 2 Tables
I have two tables message, contacts, sch_contacts columns for table message: messageid,description columns for table contacts: contactid,email columns for table sch_contacts: messageid,contactid Now, i have to insert one row from table message and all the rows from table contacts in to table sch_contacts. insert into sch_contacts(1,(select contactid from contacts)) for ease 1 can be treated as messageid which user is going to input. I am getting error on sql which says subquery return more than 1 row how to insert all the rows from 1 table into another?
View Replies !
2 Tables And An INSERT
im trying to copy a row from one table to the other, but 1 of the table's has an extra AUTO_INCREMENT column. INSERT INTO fixtures_h values (NULL, SELECT * FROM fixtures WHERE No = 1).
View Replies !
Slow Insert To Tables
I am inserting 70,000 Records to one mysql table, this table is a simple table with no indexes or primary keys (it consists of 3 columns), add those records takes me like 35 mins on a P4 Machine with 2.5GB of RAM. It is very slow comparing to Access or SQL Server.
View Replies !
Insert From Multiple Tables
is it possible to write a query that will allow an insert, that selects information from multiple (but not joined) tables, e.g. something like Insert into table1 (col1, col2, col3) SELECT a.col11, b.col21, c.col31 FROM table2 a AND table3 b note that table2 and table3 have no common information so I can't use a simple Join statement.
View Replies !
Insert New Data
something happens on one table (not on others) i never observed before : when i insert a new data, it's written at the begining, not at the end of the table ?! could you tell me why ? this is a bug ?
View Replies !
Data Insert
I am using PHP + MySQL and buld websites. Recently I encountered one problem. Actually I am using CMS to insert data in MySQL, but it seems to insert only limited amount of it. For example, I have a large text, and when I try to insert it, it becomes cut. I am sure, id does not depend on CMS, but I cannot figure out, what should I change in MySQL settings or variables? To sum up: I don't know, how to increase the amount of text I try to insert - thus it becomes cut.
View Replies !
INSERT Values In Three Tables Using ID Column
how to insert values in different tables? Table t1 got a column named "id" and this value should appear in t2.t1_id In other words: How to fill all columns in all tables with one INSERT / UPDATE ? t1 id bigint autoincrement name varchar() bla bla t1 id ....
View Replies !
How Do I Insert An Auto Increment ID Into Other Tables
I have been trying to insert An auto_increment ID of one table into other tables and I am getting wierd results. I am trying to insert the CLIENT table primary key into the other three tables and it is inserted correctly into the CONTACTS table but the JOBSHEET and KNOWLEDGE tables both insert 1 into the clientID field. Code:
View Replies !
Insert Data Via A .sql File
I've just started using php and Mysql for the first time. I am following a couple of sample sites that are created in a book. I have created the database with the tables. I have a file called populate.sql which contains some sample data. The instructions in the book show that to use this file I can use Mysql with the following : mysql -u root -p < populate.sql ; Do I have to use root or can I use another user. I have tried it with another user but it doesnt work. I get a syntax error.In another example I have managed to use the LOAD DATA INFILE.... command. I would just like to get this method to work. I guess its something really daft but its doing my head in.
View Replies !
INSERT Data And RETRIEVE
I realize this is a simple question. I tried to search through the forums but am not sure how to word the question to get any results. My search queries returned no matches from the forums... If you can answer my question that would be great. Even better would be a suggestion on how to search this forum so that the answer turns up. this is the SQL so far: INSERT INTO image_object SET image_name='11185131276164.jpg', image_float='left', image_caption='Caption', image_alt='Alt Text'; What i need is for MySQL to return a result telling me the auto_increment id that was given to the record at the time of the insertion.
View Replies !
Insert Data Infile
I have a csv file that im inserting data from into a table. The problem I have is that I want to be selective on which columns to insert into the table. Then on top of that, 4 of the columns will need to be formatted to DATE format as they are currenty in dd/mm/yyyy format. Here is the command i have right now: LOAD DATA INFILE 'advexport.csv' INTO TABLE data FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY ' ' IGNORE 1 LINES And here is a list of the columsn I need to grab from the CSV file and put into the table: 1-7,12,13,17-28
View Replies !
Insert Multiline Data
1.) I have a table, pms, and a colum, MSG, the MSG colum's type is Text. I need to be able to insert multiline data, like: Hi Blah Blah ..And still be able to echo it in that format. 2.)How can I find a row in a table?
View Replies !
Simple Insert Data
Can someone write me a simple, tested, program that displays a form asking for a name with a submit button. When the submit button is pressed the name is then entered into a mysql database?
View Replies !
Insert Using Some Data From Another Record
i have a table with four fields: (one, two, three, four) the first three fields make up a composite key. i want to insert multiple records that just changes the value for field "one". example: current record: (11, 22, 33, 44) i want to add the record (22,22,33,44), then also add (33,22,33,44), also (44,22,33,44), etc. i use the innoDB version. is there any easy sql that does this?
View Replies !
Insert Bulk Data
Hello, first post here, hope i'm not braking too many rules, I have a question so I'll get to it: I have a file with the rows in this format: XXYYYYYYYYYY xx xxxx the XX is a fixed header, the YY is the info I want and the rest is garbage. Can i Specify that i want the data from a specific point to a specific point? and not the whole line?If so, can i specify this when using LOAD DATA INFILE ?
View Replies !
Insert Data W/out SQL Commands
I was wondering if there are GUI tools available which let me insert data into a MySQL table without writing SQL commands. MSSQL has this feature and I was wondering if something similar exists for MySQL.
View Replies !
Insert A Large Of Data
how to insert a large of data in .sql file format (include CREATE TABLE and INSERT TABLE), because after I export data to backup from my website, data file aprox 50MB, and script always timeout when I restore data using phpMyadmin? My Server use Linux OS and I have root account. May I use command line? Please help me and guide me step by step
View Replies !
|
TRACKER
