Insert Data Into Multiple Tables
Is it possible to insert data into multiple tables with one insert statement?
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
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 !
View Related
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 !
View Related
Insert Into Single / Select From Multiple Tables
I have two tables from two databases (joomla2.jos_content and maxdev.jos_content) that I need to pull data from in order to populate a single table (joomla2.jos_magazine) Here is what I want to do (I know this query doesn't work but you get the idea what I am trying to do) insert joomla2.jos_magazine_articles (joomla2.jos_magazine_articles.name, joomla2.jos_magazine_articles.article_id, joomla2.jos_magazine_articles.category_id, joomla2.jos_magazine_articles.catid) select joomla2.jos_content.title, joomla2.jos_content.id, joomla2.jos_content.catid, maxdev.jos_content.catid
View Replies !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 Replies !
View Related
Data From Multiple Tables
I am working on a website about sea shells and basically I have 3 tables: - shell_tb; (table of shells) - site_tb; (sites where they can be found) - instrmt_tb; (instruments used for it) As you may already have guessed there are two columns in shell_tb (site and inst) which may use more than one item from the site_tb and instrmt_tb. 1st. How do I configure the columns in shell_tb in order to accept more than one value? (I tried: CREATE TABLE shell_tb ( shell_id INT(1) NOT NULL AUTO_INCREMENT, shell_name CHAR(40) NOT NULL, site INT(20) NOT NULL, inst INT(20) NOT NULL, PRIMARY KEY (shell_id) ) TYPE=MyISAM; but no luck at all when inserting more than on value into inst column.) 2nd. How do I retrieve the info from there?
View Replies !
View Related
Selecting Data From Multiple Tables
I have three related tables. tv_observersation, which contains stationID with the start time and end time for a tv station program viewed (the stationID isn't a key field as the same station can be viewed different times). A stations table, which contains the stationID and the station_Name, and an advertisement table, which contains a list of advertiserID and which stationID they are subscribed too (a one to many table). I am trying to generate a query, that would take out info i.e, the info from only stations that a specific advertiser has subscribed to in the advertisment table, but I am getting duplicate info in the query results from the observations table for a specific table. I think it may be a problem with how I am using the select statement. Do I want to select from two tables observations, stations like I am below, or should I use the other two (stations, and advertisments) with the inner join statement. The objective is to retrieve only the stations that a advertiser is subscribed to from the observations table. Code:
View Replies !
View Related
Retrieve Data From Multiple Tables
I have multiple tables I'd like to retrieve data from using the same query statment. All tables are setup as follows: ID Reference Value Timestamp I would like to select all the data from "table1" and "table2" where there timestamp is between Jan 01 2005 9:00:00 am till Jan 02 2005 16:00:00 Is this possible?
View Replies !
View Related
Select Data From Multiple Tables
I am trying to use the select statement to select data from multiple tables withing my database. could someone post me an example of how the code would look. if you could use the following info in the string the would be great. I want to selsect the following info....
View Replies !
View Related
Search Multiple Tables With Same Data
I have 3 tables where Product col is the same across all 3. Tables id /Product / Price So far I have been using $query = "Select `product`, `price` as p, 'tbl1' as n from `tbl1` where `product` REGEXP '".$trimmed."' UNION ALL Select `product`, `price` as p, 'tbl2' as n from `tbl2` where `product` REGEXP '".$trimmed."' UNION ALL Select `product`, `price` as p, 'tbl3' as n from `tbl3` where `product` REGEXP '".$trimmed."' ORDER by `name`"; Output gives me Product | vendor | Price Wanting to do a search where product is combine from all 3 tables and prices in different colums - >output Product | Price (tbl1) | Price (tbl2) | Price (tbl3) Any idea's ?
View Replies !
View Related
How To Pull Data From Multiple Tables
I have a db, with several tables in it, I have now reached to the point where I need to pull data from several tables. What is the best way to do this? Should I put a column in each table, and then insert a unique key in all of them at the time the record(row) is being created? If this is the way to do it, when I am putting the unique key in each table would I just do an insert in each of the tables?
View Replies !
View Related
Pull Data From Multiple Tables
Can you pull data from 2 tables in the same database onto the same page? What about more than 2 tables? Here is my dilema: I currently have a database with 1 table, and a ton of rows. The information is property information, and each property has multiple empty suites. As of now, I have columns for Building Name, Address, and then columns for Unit Type A (for the first unit), Rent A, Type B, Rent B, etc.... I was wondering if I would be able to put the listings we have into thier own property table, and then one bigger table of all the properties individual information. Keep in mind I need to call all tables together on my portfolio pages, so I would be connecting to quite a few tables at once. Or my other option I was thinking about was to just have 2 tables: 1 Buildings info, and 1 units info. Which of these would you do, or how would you organize this data.
View Replies !
View Related
Inserting Data Into Multiple Tables
I am just learning both MySQL and PHP, and have a question about a project I am working on. The first (and main) table for this project has the basic information about an refund request, including an autoincrement unique ID. Another table includes the products and uses the ID as a reference. The third table is the history of everything that happens to the request. For example, the information may be as follows. Credit table ID - autoincrement status - requested, needs authorization, processed, etc agent - who entered the request customer - person requesting the refund other customer info order table ID - reference to Credit table Item - item ID for amount requested quantity - number purchased price - price of each item (total information is calculated on above fields) history table ID - reference to credit table seq - id for each history log old status - the previous status new status- the status after the update to the record agent - agent making the change notes - a description of what was done. I have the PHP form that collects the data to be entered. My question is, since the first table is an autoincrement, what is the best way to insert a record into the Credit table, pull the ID that was just entered into the credit table and insert that ID into the order and history tables?
View Replies !
View Related
Get Data From Multiple Tables And Then Order By Field
$QueryHIST_QUOTES = " SELECT TIMEDIFF(TIMESTAMP, $starttime) AS diff, t.* FROM $currentTable t WHERE TIMESTAMP BETWEEN $starttime AND $endtime ORDER BY TIMESTAMP"; Originally I had the variable $currentTable = "PRICES_2008" but I now want to get rows from multiple tables, so I tried: $currentTable = "XAUUSDOZ_2003 XAUUSDOZ_2004 XAUUSDOZ_2005 XAUUSDOZ_2006 XAUUSDOZ_2007";
View Replies !
View Related
LOAD DATA INFILE - Multiple Tables
Is it possible to insert into multiple tables with a LOAD DATA INFILE statement? I'm going to receive a text file with many fields, and then all of these fields need to be imported into the correct tables. It seems as if LOAD DATA can only insert into one table at a time.
View Replies !
View Related
Complex - Extracting Data From Multiple Tables - Plz Help
I have spent more than 24 hours now just trying to build a query to extract data from multiple tables (which are a bit typical) in the format I need but in vain and I am goin MAD now . Any help would be really apprecaited. I have 5 tables: items, table1, table2, table3, and table4 table1, table2, table3, and table4 have got 368 columns each (366 representing each day of a year plus two additional columns - see below in structures) structures are as follows: items: ITEMID, TITLE, DESC ... table1: ITEMID P1JAN01, P1JAN02, ... P1JAN31, P1FEB01, P1FEB02, ...P1FEB29 ... P1DEC01, P1DEC02 ... P1DEC31, YEAR table2: ITEMID P2JAN01, P2JAN02, ... P2JAN31, P2FEB01, P2FEB02, ...P2FEB29 ... P2DEC01, P2DEC02 ... P2DEC31, YEAR table3: ITEMID P3JAN01, P3JAN02, ... P3JAN31, P3FEB01, P3FEB02, ...P3FEB29 ... P3DEC01, P3DEC02 ... P3DEC31, YEAR table4: ITEMID P4JAN01, P4JAN02, ... P4JAN31, P4FEB01, P4FEB02, ...P4FEB29 ... P4DEC01, P4DEC02 ... P4DEC31, YEAR Yes they are funny but that's the way they are. Note: Please note the month names within the column names Now I need to select SUM of a given month (say JAN) for a particular ITEMID from each of the tables for a given year (say 2006) for SUM i am using: PHP $p1 = "P1JAN01 + P1JAN02 + P1JAN03 + ... P1DEC31" $p2 = "P2JAN01 + P2JAN02 + P2JAN03 + ... P2DEC31" $p3 = "P3JAN01 + P3JAN02 + P3JAN03 + ... P3DEC31" $p4 = "P4JAN01 + P4JAN02 + P4JAN03 + ... P4DEC31" and then SELECT $p1, $p2, $p3, $p4 ... A basic query that I first tried was: PHP SELECT $p1 AS P1, $p2 AS P2, $p3 AS P3, $p4 AS P4 FROM items LEFT JOIN table1 on table1.ITEMID = items.ITEMID LEFT JOIN table2 on table2.ITEMID = items.ITEMID LEFT JOIN table3 on table3.ITEMID = items.ITEMID LEFT JOIN table4 on table4.ITEMID = items.ITEMID WHERE table1.YEAR = 2006 && table2.YEAR = 2006 && table3.YEAR = 2006 && table4.YEAR = 2006 This acts very funny...fetches me only the results which have got entries in all the tables. If there's no entry in any of the tables for a given item then that item is not fetched at all. The result set i need is something like this PHP ITEMID P1 P2 P3 P4 1 NULL 56 63 NULL 2 36 0 1253 63 3 NULL NULL NULL NULL and so on... and these all must be for a given year I tried playing around with the query with different joins and work arounds but no use. I really need this done soon. I'll be really thankful if you can suggest me anythig that can get me the result set i need. Please let me know if I am not clear anywhere.
View Replies !
View Related
Entering Form Data Into Multiple Tables
I've got a two tables in my database: "customers" and "orders." I want to insert information from a form into both of them - in some cases identical information will need to be inserted into both. The other twist is that I'd like to insert the automatically generated id customer 'id' value into the orders table. I've tried using joins. Something like: PHP Code: INSERT INTO orders,customers (customers.id, customers.first_name, orders.del_address   .....)
View Replies !
View Related
Retrieve Data Accross Multiple Similar Tables
I have a number of tables that all have the same table format. I want to query accross all the tables as if they where one large table. I am looking to retrieve data base on one of the columns like: select * from (data_readings.lts4a_line,data_readings.lts12_line,data_readings.lts14_line) where trigger_type='time of day' the error I get is 'column "trigger_type" in where clause is ambiguous' is there a way to do all this in one statment?
View Replies !
View Related
Multiple Tables Of Data, Single Category Table
I've searched and can't find what I'm after, so apologies if this has been covered before. I'm working on a small and simple CMS for a site I'm doing, and just as I was going to start the database I realised something... (I'm using PHP and MySQL) When it's finished, there will be articles, weblogs and content/features. Previously I've done a seperate categories table for each table I have, for example articles and articles_cats. Then a field in the articles table for the category. Now I'd like to use the same categories table for everything on the site. However, I'd really like to have a link table, so each article can have multiple categories. Would I have a table to link articles and cats, then a table to link weblog posts and cats?
View Replies !
View Related
MYSQL: Retrieve One Row With Data From JOINED Tables With Multiple Matching Rows
I'm not sure this is possible in one SQL query: I have a table called TB_EMAIL and another called TB_OTHER_BLOB. TB_EMAIL contains details like: to, from, subject, body and since I will be reusing headers and footers I aslo store header and footer reference to TB_OTHER_BLOB in TB_EMAIL. What I need is a row (given its id) with all columns (except the references to TB_OTHER_BLOB) from TB_EMAIL and two extra columns one being email_header and the other email_footer which are in two different rows in TB_OTHER_BLOB What I came up with is: Expand|Select|Wrap|Line Numbers
View Replies !
View Related
Insert Multiple Rows With One Insert Stmt And Nested Select
I'm trying to insert several rows into a table using only one insert statement: insert into component_feature values (select 3,1,sf.software_feature_id,1 from software_feature sf where sf.software_id = 1) When I run the select statement alone, I get the result I want: +---+---+---------------------+---+ | 3 | 1 | software_feature_id | 1 | +---+---+---------------------+---+ | 3 | 1 | 0 | 1 | | 3 | 1 | 1 | 1 | | 3 | 1 | 2 | 1 | +---+---+---------------------+---+ But when I run the complete statement I get: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select 3,1,sf.software_feature_id,1 from software_feature sf where sf.software_i' at line 1 This query is part of a PHP application I'm building. I'd rather not have to write a PHP loop to do multiple inserts.
View Replies !
View Related
Select / Insert Multiple Rows As A Single Row Of Multiple Columns
I have a nice database set up that contains information about orders and the items on those orders. If an order has 10 items on it, I can select the item data which returns 10 rows of data (let's say 5 colums each). Beautiful! Now I find myself needing to satisfy a program that requires all of the data on a single row. I can do this in a higher level language, but if I could accomplish it all in mysql it would be better. I don't need to sum or do any calculations. I just want to select those 5 columns of data about those 10 rows worth of items as a single row with 50 columns. For example, I'd want this: 1-1,1-2,1-3,1-4,1-5 2-1,2-2,2-3,2-4,2-5 To become: 1-1,1-2,1-3,1-4,1-5,2-1,2-2,2-3,2-4,2-5 The first complication is that the number of items on an order is variable, but is always at least 1 and can not exceed 20. The closest I've been able to get is to do something like: SELECT GROUP_CONCAT(item_number,",",qty,","",description,"",",price,",",location_number SEPARATOR ",") FROM items WHERE order_number=12345 This will give me a single text string containing the value content of the INSERT query (which will need to be manipuated outside of the SQL query to pad it with NULL values for the unused items' columns etc).
View Replies !
View Related
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 !
View Related
Joins With Multiple Tables And Multiple Rows
I'm making a good ol' forum, and i have three tables, users, threads and posts. when i query my threads table with a join, i need to access the users table twice to get the username of the first poster and last poster. But how? I can only figure out how to get one or the other. Is my design bad? eg SELECT TopicID, FirstPostID, LastPostID, Replies, Views, Topic, username FROM DiscussionThreads, users WHERE DiscussionThreads.FirstPostID=users.ID ORDER BY FirstPostDT DESC LIMIT 10 .
View Replies !
View Related
Select Multiple Keywords From Multiple Tables
am trying to do. I have a database called "members" that contains a table called "Reviews". This table has fields S1, S2, S3, S4, etc. that I want to search for the words ad, advertisement, and promotion. Here is the syntax I came up with that doesn't work: SELECT * FROM `REVIEWS` WHERE S1, S2, S3, S4, S5, S6, S7, S8, S9, S10, XTRA='ad, advertisement, promotion' It would be perfectly fine, if easier, to search all fields in the table.
View Replies !
View Related
String: Insert Multiple
I have strings with no " " in a text field. I need to output each string with with a " " after every 60 characters. In other words I need to change the line length from unlimited to 60. I do not want to change the stored version of the data, just the output. What is the best way of doing this SELECT?
View Replies !
View Related
Insert Multiple Rows At Once
I was faking it a couple of years ago when I made my first database for my photography website. Now I'm updating it and I'm inserting One item at a time manually thru PHP. It's slow and tedious and I've got 467 entries to do.... I'm sure there's an easy way of doing it all at once. I'm just too much of a retard and I'm too tired to try to learn some MySQL tonight. Could someone please help me? Here's the query that I'm ending up with. (I started at Number 100, I'm increasing the Number by 100 each time and I need to go to Number 46700.) SQL query: INSERT INTO `Headshots` ( `Number` , `Category` , `Name` ) VALUES ( '2800', 'Head', '' );
View Replies !
View Related
Multiple INSERT Queries
I have a for loop that generates and submits a query on every itteration. The number of itterations can be can be anywhere between just a couple dozen and a few thousand. Is there a more efficient use of MySQL than this... PHP Code: for () { INSERT INTO... } There must be some way of building the query in the for loop and sending the entire thing out as just one query submission.
View Replies !
View Related
Insert Multiple Rows
I want to insert multiples rows and use sintax "INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);". But, I get the following error: "Column count doesn't match value count at row 1". The number of fields is the same, only that insert multiple rows.
View Replies !
View Related
Multiple Insert Statement
Just wanted to know whether its possible to insert multiple feilds in different tables inj one nested query There are nested queries for SELECT statement like join etc Is it possible in case of INSERT statements as well
View Replies !
View Related
Multiple Batabases Vs. Multiple Tables
I am about migrate from an old program database to MySql (Running under RH LINUX)and I'm wondering which is the best option to do : I currently have one file for each of my modules, (I'm using filepro plus) , so like CUSTOMER, WARRANTY, ZIPCODES, INV, etc... like 2,000 files each with their own fields. 1) Should I create multiple databases on MySQL 2) Should I create a single database on MySql and with multiple tables ? Which one of these options is the best and more safer ?
View Replies !
View Related
Multiple Databases Or Multiple Tables
I have an application that is supposed to make multiple connections [around 10 connections per second] to a mysql database. The connections are done by mutlitple users at potentially the same time. I have to decide if I should use : 1)one database with one table for all users. 2)one database for each user. 3)one database with one table for each user. I would really appreciate if you can tell me what choice is best and why.
View Replies !
View Related
Multiple Insert, Unique Records Only
I want to insert lets say 100 records into a db that looks like the following: ID auto increment IPAddress varchar The query I am running inserts all 100 with one trip to the DB using php. What happens If I only want Now lets say I do not want more than 1 IP in this database. Surely I don't have to search the whole database looking to see if there is a match for IPAddress.
View Replies !
View Related
LAST_INSERT_ID With Multiple INSERT Statement
i have the following question: When inserting many entries into a table with 1 auto_increment key-attibute (say attribute user_id) and one data attribute (say attribute name) CREATE users(user_id int key auto_increment, name text) like INSERT INTO users(name) VALUES ('pete'),('josh'),('carl') When the inserting is done in a concurrent way (say 5 scripts do inserting operations like the one above) how do I get the autoincremented ids for all the inserted rows ? I know that LAST_INSERT_ID() yields the autoinc id of the first inserted row (of the multi-insert statement) say 1002 for 'pete' in this case and is also concurrent-safe. But is the insert statement atomic that i can assume that the subsequent generated ids for 'josh' and 'carl' are consecutive i.e. 'josh' gets 1003 and 'carl' gets 1004 when many scripts insert into this table ? I dont want to use a subsequent SELECT statement to fetch the auto_incremented ids.
View Replies !
View Related
Multiple Selects Within An Insert Statement
I am trying to write a statement that will take information from one table, and insert two similar, but subtly different, lines to another table. I have tried mutiple Selects within the statement but it didn't like this. Is there a way to achieve what I am after, or will I have to run it as two separate statements? The query I have been trying is: INSERT INTO table2 (col1,col2,col3,col4,col5,col6,col7,col8) SELECT ( SELECT ‘1’, now(), table1.customer, now(), ‘1’, ‘1’, ‘2’, table3.user FROM table1 JOIN table3 (table1.id=table3.id) (SELECT '6', now(), table1.customer ,null, null, '7','8', table3.user FROM table1 JOIN table3 (table1.id=table3.id) ); so the final table should look like: 1, time, customername, time, 1, 1, 2, user 6, time, customername, null, null, 7, 8, user
View Replies !
View Related
|
TRACKER
