Data Insertion/Update Form Different Tables Into On Table
I have 3 tables say Employees, Benefits, Employee_Benefit_Mapping. These tables are something like this:
Employee:
========
Employee_Id
Name
Dept_Id
.
.
Benefits:
=========
Benefit_Id
Benefit Name
.
.
Employee_Benefit_Mapping:
=========================
Mapping_Id
Employee_Id
Benefit_Id
Its a many to many relation. Hence the mapping table.
Now the situation and question is:
====================================
Every employee "In a certain Dept" is eligible for some benefits by default. How can add these records with one (or minimum) insert statement(s) rather than going employee by employee?
Insert into Employee_Benefit_Mapping ((Select Employee_Id from Employee where Dept_Id = xx), (Select Benefits_Id from Benefits where Benefit_Id in(xx, yy)))
Will this work to insert all the data into the mapping table?
View Complete Forum Thread with Replies
Related Forum Messages:
Using Form To Update 2 Tables
how to change ths script below so it can update the same cells in another table aswell the current table. in other words updates two tables at the same time. <?php if ($submit) { require("db.php"); $con = mysql_connect("$db_host", "$db_user", "$db_pass"); $db = @mysql_select_db("$db_name",$con); $sql = "INSERT INTO support (prod_code,prod_name) VALUES ('$prod_code','$prod_name')"; $result = mysql_query($sql); echo "Thank you! Information entered. <a href=admin.php>Click Here</a> "; } else{ ?> <form method="post" action="<?php echo $PHP_SELF?>"> Product Code:<input type="Text" name="prod_code"><br> Product Name:<input type="Text" name="prod_name"><br> <input type="Submit" name="submit" value="Enter information"> </form> <?php } ?>
View Replies !
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 !
Help CHECKING FORM DATA AGAINST MYSQL TABLE DATA
PHP // See if relevant fields have been set. $submitted = IsSet($_REQUEST['Submit'])? TRUE : FALSE; $user= IsSet($_REQUEST['username']) ? cleanValue($_REQUEST['username']) : '' $pswd= IsSet($_REQUEST['password']) ? cleanValue($_REQUEST['password']) : '' if($submitted) {// Form submitted yet? if (empty($user) || empty($pswd)) {// Are Form fields empty? // Display error message when fields are not filled in. $message = 'Make sure you enter both a username and password.' } else { /* THIS IS MY HUMBLE EFFORT BELOWAND IT DONT WORK HELP */ //CONVERT PASSWORD FROM FORM INTO MD5 $pswd = md5($pswd); //CONNECT TO DATABASE $connection = mysql_pconnect("$dbhost","$dbusername","$dbpasswd") or die ("Couldn't connect to server."); $db = mysql_select_db("$database_name", $connection) or die("Couldn't select database."); //SELCT TABLE AND DATA $sql = @mysql_query("SELECT * FROM users WHERE username='$user' AND password='$pswd' AND activated=Ƈ' "); if (!$sql) { exit('<br /><br /><h3>Error retrieving Art members from database!<br />'. 'Error: ' . mysql_error() . '</h3><br /><hr /><br />'); } //MAKE VARIABLES FOR CHECKING FORM DATA AGAINSTIS THIS RIGHT!! while ($login_check = mysql_fetch_array($sql)) { $user = htmlspecialchars($login_check['username']); $pswd = $login_check['password']; //NOT SURE FROM HERE // ORIGINAL SCRIPT BELOW // Authenticate username and password (i.e. match against a database). // Start a session only after username and password have been verified. THIS IS THE EXAMPLE LOGIN CHECK THAT CAME WITH THE CLASS if ((!strcmp($user, 'empl')) &&// Does username match 'empl'? (!strcmp($pswd,'demo'))) {// Does password match 'demo'? $sess_param['new_sid'] = TRUE;// Create new session ID to be safe. $sess_param['security_level'] = 100;// Security level for Employees area $sess_param['gc_maxlifetime'] = 300;// Inactivity timeout of 300 seconds (5 minutes) $sess = new DB_eSession(&$sess_param);// session_start() done. $sess->setSessVar('authenticated', TRUE);// Set that authentication occurred. // Save the username for displaying and encrypt $sess->setSessVar('username', $user, ENCRYPT_VALUE, EXTRA_FIELD); $URI = $sess->setSessURI('employees.php');// Optional - pass session ID in URI. Not as safe. header("Location: $URI");// Go to members area. exit; } REGARDS Bill007
View Replies !
Selecting Data Form One Table That Is Based On An Entry In Another Table
This is the code I am using: *********** SELECT co_name,city,country,logo_small FROM $info WHERE $info.co_name=$categories.co_name AND WHERE $categories.everyday_wear='y' ORDER BY co_name ASC *********** I am using a while loop in php to loop through all of the records but no records are being displayed. What am I missing? Do I need to use a JOIN statement?
View Replies !
Mysql Data Insertion
I have quite a bit of records I need to insert into a database. My question is, would it be possible to insert them even though the key fields might be the same. I'm basically wanting to add records that were added after a backup was restored, thereby the new records have taken the old record's, beyond a certain point, record IDs. Can this easily be done or would I need to make sure the record IDs started with a number after the last record in the database table right now? Would the IDs be ignored/changed if I were to just import the data directly into mysql? I definitely do not want to lose any data.
View Replies !
Modifing Data Before Insertion Into DB
I have a problem and I hope someone has a solution. I have a column in a database that has data like "1000,,,,,,," and "1000,1040,,,,,,," on and on. I was wondering if anyone knows how to remove the commas before it is inserted into the DB. I have setup 7 dropdown boxes that must have the same name.
View Replies !
Lock Table For Insertion
Here is a stored procedure to add a new customer to my customers table. I want to be sure that name doesn't already exists in customers table. If yes then I want to return an error code, if no then I want to insert the new customer. My question is defined the following piece of code : CREATE DEFINER=`root`@`localhost` PROCEDURE `spCustomerAdd`(IN NAME VARCHAR(50), IN PWD VARCHAR(45), OUT NEW_ID INT, OUT ERROR_CODE INT) BEGIN # LOCK Customer table ??? Code:
View Replies !
PROGRAM TO UPDATE DATA TABLES
I need to be able to DELETE selected record & LOAD DATA from a text file or Foxpro table, from a program that can run continuously. I've tried Foxpro, but FoxPro views are too slow. I have also tried issuing these command from Foxpro with an ODBC connection established, but that does not work at all. The object is to remove all records for a member and replace these records with records from a new file. Each transaction will be up to 50,000 records. I need to execute 2 commands: DELETE FROM parts WHERE parts.member_id="xxx" and LOAD DATA INFILE 'd:/xxx.txt' INTO TABLE parts
View Replies !
Update Whole Table With Unique Data Per Row
This is my first post and I am fairly new to MySQL and PHP. I need to update an entire column of data with a column of data from another table. Each row is a price attached to a specific product. When I do an Update/Select, I get an error saying more than one row is being returned. Is there a way to do this using MYSQL alone, or do I need to use PHP and and a loop with individual inserts per row?
View Replies !
Update Statement :: Data From One Table To Other
I have two tables, and I want to take data from one table, and overwrite it onto the second. Unfortunately, my SQL is pretty weak. The first table has a mediumtext column and a id (primary key) column. The second has an id (the same as the first), a number, and a mediumtext column. I want to take the contents of the mediumtext field with the highest number and replace the contents of the field in the first column with it with the same id. My instinct is to do it this way: update forum1post p set p.pagetext = (select query1.message from (select pe.message, max(pe.editnum) from forum1post_edits pe group by p.postid) query1) where p.pagetext = 'Why did you all flame my topic. Screw your ****ing forums.' and p.postid = pe.postid However, that does not work, as pe is not recognized.
View Replies !
UPDATE Query With Getting Data From Another Table?
is it possible to use UPDATE command with getting data from another table. I have two tables: base1.table1 with COUNTRY_NAME, ID_NUMBER base2.table2 with COUNTRY_NAME, LINK I need to get value of ID_NUMBER from base1.table1 (about 300 rows) and UPDATE base2.table2. So, I need to copy values of ID_NUMBER to LINK. I wonder is it possible to do just with one query or I will need to write PHP parser to do this job?
View Replies !
Compare And Update Table Structure Of Two Tables
Is there an easy way to compare the structures of two tables and update the structure of one table based on the differences found? I imagine this might work by exporting the structure (not the data) from two tables to SQL files, then compare the two of them to produce a third SQL file with only the differences. I need something like this because I'm running the same database on a number of different sites, and don't want to have to manually update each DB everytime I make a change. Or perhaps someone could offer a procedure I could follow to make updating DB's with new structures less involved? Something that makes it easy for me to keep track of changes I've made.
View Replies !
Update One Table With Data From Another That Has Multiple Returns
I have a query that works but it takes extraordinary amounts of time to run and I'm sure there's got to be a better way. I haven't touched sql in many years and I just can't remember the proper way to do this. Table 1 has a column that I need to fill from table 2. Table 2 has multiples of the requested data. There are about 303 records in table2 that meet the >0 criteria but only 44 distinct values that I need to retrieve. col1 in both tables is the matching index. What I have so far is: update table1 a set a.col2 = (select b.col2 from table2 b where a.col1=b.col1 and b.col2>0 group by b.col1) I also tried limit 1 instead of group by but there was no difference in execution time (which is about 5 updates in 10 minutes!)
View Replies !
Update Column Data Based On Another Table
I've got 2 tables: 'city' that list over 2000000 rows and 'profile' where each row are associated to a city. What I want to do is to update cities popularities based on profile without having to scan the hole 2000000 rows of 'city'. So is it possible to make those 2 query in one so I do not need to do a php loop: SELECT city, COUNT(city) FROM profile GROUP BY city; then UPDATE city SET popularity = COUNT(city) WHERE city.city_id = profile.city
View Replies !
Import Update Of Csv/excel Data Into MySQL Table
I need to import some data from csv or excel files in my customers table so that for each customer ID it will update an existing field. My tools for accessing the database on the server is phpMyAdmin, but I could of course also copy the database down, manipulate on the PC, and then re-upload the entire manipulated database.
View Replies !
Deleting Form 2 Tables
I'm using SQL not sure if this is different from MySQL My problem is I have two tables which are related by ordernum: Orders (table) OrderItems (Table) l l ordernum (field) ordernum (field) I can't work out the sql delete statement which will delete the order from both tables. Can anyone see were I'm going wrong? DeleteCommand="DELETE FROM [OrdersItems} INNER JOIN OrdersItems ON Orders.ordernum = Orders.oredernum WHERE [ordernum] = @ordernum"> <DeleteParameters> <asp:Parameter Name="ordernum" /> </DeleteParameters> </asp:SqlDataSource>
View Replies !
Update A Table With Data From Another Table
I have two tables with similar data. The firs table contains data that is to be updated with data from the second table. The first table (tblA) has a unique key, but the second table (tblB) does not. I have to use the 'lastname', 'firstname' and 'dept' fields that are in both tables and join the tables on those three fields. I have tried: update tblA, tblB set tblA.empPty=tblB.empPty where ((tblA.empLName=tblB.empLName) and (tblA.empFName=tblB.empFName) and (tblA.empDept=tblB.empDept)); with some test data where I know I have a match using the three fields, but nothing gets updated.
View Replies !
Retrieving Data Through A Form.
I'm setting up a database and I want to retrieve data from the db through a form. I currently have: $sql = "SELECT * FROM Tasks WHERE t_id = '$_POST[t_id]'"; $result = mysql_query($sql, $conn) or die(mysql_error()); while ($newArray = mysql_fetch_array($result)){ $id = $newArray['t_id']; echo "ID: $id <br>"; I don't think that the 'WHERE t_id = '$_POST...' part is correct, as the code seems to do what it is supposed to when I take that part out, it just doesn't do what I want it to do. So.. to clarify; I insert a task id (t_id) into the form, hit the submit button, and it is supposed to show the results (echo "ID: $id <br>";). I hope this makes sense to someone. Code:
View Replies !
Web Form Data To Database
I have a root access to a virtual server running UNIX and SQL.I would like to have data from a web form go to a database and perhaps be emailed to me also.Typically some one would complete a form and attach a file and that is emailed to me using a cgi script. I would like to have this data populate a database. where to start.If someone could give me a basic list of what needs to be done,I can tackle those and then fine tune my questions.
View Replies !
How Do I Put Form Data Into A Database?
I would like to take the form data submitted on a web page and put it into a database besides having the data emailed to me like is currently the case. I don't know where to start on this. I've never set up a database. Once in the database how do I get that data to my computer?
View Replies !
How Do I Sort Data Fro A Bad Form Into Something Useful?
I made a bad form with a form creation app in a hurry. Now I have a table that is nearly useless. The form was meant to record teams for an upcoming volleyball tournament. I provided inputs for the school name, mascot, student, etc. The form app allows me to copy form elements, so I did. The table now shows about 120 student, and grade, position, sorted by a form id (one for each team) but they are sorted by the record id. Instead of: Team | Student | Grade | Position | etc..... I have: record | name | value | etc... I have tried some queries that just basically remove some superfluous column and GROUP BY record (that is the form id, so the third team to register has "3" under record. Can I salvage this data by a clever query or am I looking at making a page via php to display the results? Sample Data: student name1 Curtis Geurts student name1Tom Glasbergen student name1Bill Lammers student name1Marc Brouwer student name1Kurt fledderus student name1Nate Brink student name1James Brouwer student name1Scott Denbak student name1Dave Stares grade12 grade12 grade11 grade11 grade11 grade11 grade11 grade12 Player positionpower Player positionsetter Player positionmiddle Player positionmiddle Player positionpower Player positionoffside Player positionsetter Player positionsetter
View Replies !
Querying Form Multiple Tables
I'm trying to get info from multiple tables, and not having much success. I want it to be sorted by date, as if they were 1 table. Therein lies (one of) the problem, one is just date(YYYY-MM-DD) and the other is date and time(YYYY-MM-DD HH:MM:SS). Also, I'm not sure that everything else is right, or anywhere near it....
View Replies !
Formatting Data From Input Form
I have a form on my website and it's imperative that regardless of the way users enter their information into the form, I need the data to be stored in the database in a certain way. For example: john smith needs to be displayed as John Smith JOHN SMITH needs to be displayed as John Smith Can I do this at the database level after the fact or do I have to do it via PHP at the form level?
View Replies !
HTLM Form Link To Data In Mysql
I have a form that puts data into MySql and then in my website I have a table to display the Data. Everything works just fine at this point. The only thing left for me to do is that when the data is put into the table I want it so that one of the cells has a link, when the user clicks on it he can either see all the details from the data submited on the form in the same page or it is transfered to a new page with all the data submited in the form.
View Replies !
Import Data Form Txt Or Cvs File Format
I have a fully installed version of MySql Server 5.0 including MySql Administration, MIGRATION Tool, Query Browser and System Tray Monitor. I have lots of Data stored in a txt File. I do not want to type them all in. How do I import Data from a txt or cvs File format? They are standardizes.
View Replies !
Passing A Variable Form A Form To A Mysql Query In A Php Script
I have a form set up with a dropdown list of values for a field "Genre" in my database I can pass the variable to my php script. I know this because I use: $Genre = $_POST['Genre']; echo "<h1> Query result for Genre = ","$Genre", "</h1>"; And the page prints the appropriate value chosen from the form. I'm trying to use the $Genre variable in a mysql query to select records which match. My mysql query works when I use the following code:
View Replies !
Enctype=multipart/form-data Wont Pass File Name
when i add enctype="multipart/form-data" to the form i cant get the file name into the databass... but if i leave that part out of the form then the files dont upload into my upload folder. so its either one or the other. is there a way to upload the file to my uploads folder and pass the data (filename) to the databass at the same time? . . . <form action="insert.php" method="post" enctype="multipart/form-data"> .....
View Replies !
“best-practice” Approach For Storing Form Structures And Their Data
I’m looking for a “best-practice” approach to creating a db structure that will handle form creation and storage dynamically. FORM CREATION The idea is to allow the client to create forms for their products by specifying attributes and values that belong to a product category. The client will: 1.Enter a category # and name. 2.Enter the attributes available to the category. 3.Enter the values available for those attributes. The application will: 4.Deduce the form based on what the client enters. FORM STORAGE What’s the best approach for then storing the form attribute/value pairs? Is this a “wise” approach or is better to "hard-code" the forms with html and/or use a look-up table for the allowed attribute values of the fields? Do any of you have experience with this concept? Do you have any thoughts on it or a link to further reading?
View Replies !
Copying Selected Data From Two Tables Into One Table
I have a list of members in my "members" table, and they can buy tickets for an event. When they buy them, their member id number and the ticket info is stored in the "transactions" table. For the final "tickets" table, which I want to print out to put on the door, I also need to have their email and name, which is in the "members" table but not the "transactions" table. What I want to do is insert bits of the "transactions" table into the "tickets" table, and then also use the member id number from the "transactions" table to lift out the corresponding member name and email address and insert them in as well. Here's what I tried before I realised that I probably need something quite a bit more complicated: INSERT INTO tickets (SELECT transaction_id, ticket_member_id, ticket_code FROM transactions), (SELECT name, email FROM members WHERE member_id=ticket_member_id) member_id is the one in the "members" table. Basically, here's what I want the "tickets" table to look like (the columns): transaction_id (from "transactions") ticket_member_id (from "transactions") ticket_code (from "transactions") name (from "members" using ticket_member_id) email (from "members" using ticket_member_id) I think I need to use a while to iterate through everything but just thought I'd check in case there's an easy way.
View Replies !
Select Data From 2 Tables (join) REGARDLESS Of One Table Not Containing A Row
SQL SELECT o.title, o.quantity, o.price, o.product, i.supplierFROM store_order_inv o, store_inventory iWHERE o.product = i.productAND o.cart_order_id = ?-195509-3867' my only problem is if a row with Product ID doesnt exist in inventory, no result is returned, EVEN if a row exists in order_inve table with that product. So my question is, is it possible to select data from 2 tables, but where the presence of a row with matching PRODUCT field in the inventory table is OPTIONAL
View Replies !
A Table Or A Form Field
If I have a db where people enter in their basic info...one of those things being the country they live in....Is it better to make a seperate table in the databases with the country names OR...just put the country names as options in the html form dropdown list?
View Replies !
Create Table With Form
I have an html form that I want to create a new table when submitted. Is there a way to use input fields in the form ($username, $email) and create a table with it: "CREATE TABLE ('username', 'email')( various values )";
View Replies !
Pull Data From 2 Database Tables Into 1 Table On My Site.
I got 2 tables: a dev_tasks and a task_history table. The task table has to record information about the task I am working on AND the history table is for the task history logs. I am trying to display info from both tables into 1 table on my site. Most of the info will come from the dev_tasks table BUT i want to dedicate on column of the displayed info for the latest log made to the task_history table. The common column to both tables is the 'taskid' column. What would the query look like to pull the info from both tables, knowing that the taskid is common to both and that I want to display the last log per tasks from the task_history table?
View Replies !
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 !
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 !
Creating An Order Form - New Table For Each One?
I'm trying to create a simple database using mySQL which will be used to create various web based order forms, these forms will be used multiple times. The first table is for the item and is stuctured: ------------- |Item | |-------------| |Description | |Part No. | |Article No. | |No. Required | ------------- The order options form is structured: ------------- |Order Options| |-------------| |System Name | |PPNo | |Item | |Item | |Item...etc | ------------- Each order options form can have any number of items within it.
View Replies !
Join Records Form Same Table
I have results from a mySQL database that I am exporting to a CSV. These records are sales form an eCommerce store (ZenCart). What happens is that attributes for each part of an order make a new row for each attribute. Like this: .....
View Replies !
Jpg File Insertion
I want to store jpg files to BLOB field of a table. That table has other fields which are integer and char types. So i tried inserting image using LOAD DATA INTO ...... statement . It got stored , but when i retrieve it back , i'm not able to see the image file.I hope it didn't get stored in proper format. How do i acheieve this ?? __________________________________________________ _ Download the hottest & happening ringtones here! OR SMS: Top tone to 7333
View Replies !
Dynamic Insertion
Im trying to get dynamically insert the current date into a LOAD FILE statement for some ETL automation, but Im having difficulty passing the string into the LOAD statement: This will not work. SELECT @Today:=CURDATE(); LOAD DATA LOCAL INFILE '/s3/data/filena...@Today.txt' INTO TABLE Data; Anyone ever try something similar? How can this be done?
View Replies !
Id Of An Insertion Prior
How can I find out what the unique key, auto_incremented id is going to be before I insert something into a table? I know I could do something like: SELECT id FROM table ORDER BY id DESC LIMIT 1 and then add 1 to that result... but is there some special built in way that is most efficient to use in mysql?
View Replies !
Mass Insertion
I have a user event manager where they would like to be able to insert recurring events. This is to say that the user specifies that an event would recur (for example) every week at the same time, and then the script would insert all these seperate events. Is there anyway to use a single query to do something that inserts a certain amount of data into the proper records and sets the dates correctly? Or do I need to recursively contruct a query to insert all the records at the code level?
View Replies !
Last Insert Id After Insertion
drop table t1; create table t1 (id smallint not null auto_increment, name varchar(20), primary key(id)) engine=innodb; insert into t1(name) values ('a'); insert into t1(name) values ('a'); SELECT * FROM t1 t; select LAST_INSERT_ID(); ------------------------------------- The records are added correctly in the table, but last_insert_id always returns 0.
View Replies !
DATE_ADD Insertion
I created a query where I insert current date plus a given number of month but the query always insert '0000-00-00' The query I use : INSERT INTO utilisateur (date_expiration) VALUES ('DATE_ADD (Now(), INTERVAL 12 MONTH)')
View Replies !
View Insertion Failing
I am trying to insert into a view named 'request_files_v'. I get the error "SQLSTATE[HY000]: General error: 1471 The target table request_files_v of the INSERT is not insertable-into". I am giving the insert command from the same userid that had been used to define the view. Also, in information_schema.VIEWS, I see that the 'IS_UPDATABLE' field for this view is 'YES'. What could be the possible reason I am not able to insert into this view?
View Replies !
How To Obtain Autoincrement Value After Insertion?
In SQL Server within a stored procedure I can obtain a recently inserted row's "identity" value (same as autoincr in MySQL) by using the keyword @@IDENTITY. So it is possible to do something in TSQL like so.. DECLARE @ID INT INSERT INTO mastertable ([fields list]) VALUES ([values list]) SET @ID = @@IDENTITY Now I have the value in variable @ID and I can use it for inserting foreign keys in detail records in a master-detail relationship like so.. INSERT INTO detailtable (ID_Field, [fields list]) VALUES (@ID, [values list]) How can I do the same thing In MySQL?
View Replies !
MySQL Insertion Problem
I am having a small problem with inserting data into a database, but it makes all the difference in the program I wrote running. When I run the following queries, "root" needs to have an id of 0, but it always comes out as 2 after I run the queries. Does anyone know why this is happening? Am I missing something? ....
View Replies !
|
TRACKER
