how to handle duplicate keys in insert


Hi people

I have the following table definition:
Code: CREATE TABLE `suggested_synonyms` ( `Last_Name` varchar(255) NOT NULL, `Synonym` varchar(255) NOT NULL, PRIMARY KEY (`Last_Name`,`Synonym`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; The table stores mapping from a last name to a synonym.
I need to insert multiple rows into the table. Something like:
Code: insert into suggested_synonyms (`Last_Name`,`Synonym`) values ('smith', 'smeeth'), ('smith', 'smeath'); the problem is that some of the mappings may already exist in the table.
I tried to use the ON DUPLICATE KEY UPDATE syntax to prevent duplicate entries, but apparently I am not doing it correctly:
Code: insert into suggested_synonyms (`Last_Name`,`Synonym`) values ('smith', 'smeeth'), ('smith', 'smeath') ON DUPLICATE KEY UPDATE; As I need to specify the columns to update.

Is there a way to insert multiple rows in a single query and still be safe from duplicate rows

Posted On: Thursday 25th of October 2012 11:13:27 PM Total Views:  356
View Complete with Replies

RELATED TOPICS OF MYSQL PROGRAMMING LANGUAGE




Social website "friends lists". How is the data handled? Table design...

How would you think that busy sites like facebook and myspace manage each users' friends list. With the sites having millions if not hundreds of millions of users, and some users have thousands of one giant table with all friend connections seems like a bad idea. Each user get it's own table Then you could potentially end up with millions of tables. That doesn't seem like good design either. Granted, a table would only really need to be a few columns. record id (bigINT), member_id(int), friend_id(int), If you had 100million users and on average 100 friends per user, that's 10billion records. But it'd be 10billion small, tiny records with only integer based columns. While that'd be a huge record set, you could probably do a lot with caching, and creating different views. Or, just do a table for each letter of the alphabet. If your last name begins with T, you go int the T table for friends. Then that table only contains the friend records of members who's last name begins with T. That would only require 26 tables, and could then cut 10billion records into tables with only 390million records. DBA's, how would you approach this
VIEWS ON THIS POST

172

Posted on:

Thursday 11th October 2012
View Replies!

How to handle error in MySQL script

How can I handle error in mySQL script in c# we use try.. catch.. so, what about MySQL script
VIEWS ON THIS POST

159

Posted on:

Friday 12th October 2012
View Replies!

How many connection a mysql can handle and how can I tell it's overloaded?

Hi. I wonder how many connections a mysql server can normally handle And how can I tell (tools/technique) tell will tell me it's about to get overloaded that I need to do something
VIEWS ON THIS POST

149

Posted on:

Friday 12th October 2012
View Replies!

Which mysql version is more able to handle heavy traffic

hello, we are currently running mysql 4.1. we have problems with a php/mysql website that generates a lot of traffic and relies heavily on mysql (it's a "find your old schoolmates" community website). Sometimes it just says "too many connections", or just takes a long while before rendering the page. Would upgrading to mysql 5 help What are the best mysql settings in order to optimize the performance
VIEWS ON THIS POST

135

Posted on:

Friday 12th October 2012
View Replies!

How to handle this Query

Hi I have a query I don't know how to handle I have a leaders2subordinates table: Code: delimiter $$ CREATE TABLE `leaders2subordinates` ( `leader_uid_number` int(11) NOT NULL COMMENT 'Leaders user number', `leader_collection_id` smallint(4) NOT NULL COMMENT 'leaders type designation', `sub_uid_number` int(11) NOT NULL COMMENT 'Subordinate uid_number', `sub_collection_id` smallint(4) NOT NULL COMMENT 'Subordinate user type', PRIMARY KEY (`leader_uid_number`,`leader_collection_id`,`sub_uid_number`,`sub_collection_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1$$ That has this data leader_uid_number leader_collection_id sub_uid_number sub_collection_id 10006 9 10004 11 10007 10 10005 11 10008 8 10006 9 10009 8 10007 10 I have a leads table where one of its' fields is counselor_id; say for this example it is 10004I have a users table where all users designated by 10004 - 10009 are located. This users table includes u.first_name and u.last_name fields I can get a leads counselor by the following: Code: SELECT CONCAT(cnsl.first_name, ' ', cnsl.last_name) as 'Counselor' FROM leads as l INNER JOIN users as cnsl ON l.counselor_id = cnsl.uid_number WHERE l.lead_id = 1; This returns 'Bill Parker' as the Counselor. But things get more complicated from here as I also want to find the that same leads Sales Manager I have to determine who 'Bill Parkers' Sales Manager. To do this I need to involve the leaders2subordinates table. If 'Bill Parker' has an uid_number of 10004 and I want to return his Sales Manager then do I have enough information to return the Leads' counselor and the sales manager related to that leads counselor If so what would be the best way to return a row like: Lead Counselor Sales Manager Paulette Johnson Bill Parker Anita Ward
VIEWS ON THIS POST

141

Posted on:

Sunday 21st October 2012
View Replies!

how to handle different datas for different users in a registration table

all I am designing a table.My situations are as follows I have so many user types.I am defining the user types as type1,type2,type3 etc I have a registration system and I have different data for each types.Even admin can add the data for these types I am not getting an idea how to handle my registration table.Should I store everything in a single table for all types please help me
VIEWS ON THIS POST

192

Posted on:

Sunday 21st October 2012
View Replies!

Remotely hosted service - how to handle users/clients?

I'm in the process of planning a website that would provide a service. Each client (company) would be presented with their own space, which would be private to them. I'll use an example, a Project Management service. A user (as in, a person working for one of our clients) logs in to manage one of their projects. They are presented with only their data. Each client's data is contained in tables with their custom prefix. So client A's table have the prefix of 'c1_', client B has 'c2_' and so on. The reason for keeping the data separate is so that it can easily be exported, deleted, etc. There would need to be a table to link the clients to their table prefixes. That's fine. But where do I store the users If is logging in with an email/password combination does that mean I store all of the users together Because it wouldn't be feasible to query every prefixed table for a particular user. e.g. c1_users, c2_users, etc. -- just to find out which client they're from (which then leads us to which table prefix to use).
VIEWS ON THIS POST

161

Posted on:

Sunday 21st October 2012
View Replies!

How to handle references to multiple tables which exclude one another?

I am designing a product catalog of sorts. For now there will be three product types, and it is unlikely that there will significantly more different types of products. Because these products share very few properties, I thought it would be best to use a different table for each product. I'm too lazy to type the actual details of these products (sorry), but one can think of them as different sized boxes whose types are named A, B and C, and A is the biggest and C the smallest So let's talk about boxes. These boxes have specific relations: 1. only a box of type A can be by itself, other boxes must be inside a bigger box 2. ergo, a B must always be inside an A 3. a C can be inside an A or a B 4. but an A can immediately contain only either a B or a C, so if it contains both, the C must be inside a B 4. a single A can contain multiple Bs, and a single B can contain multiple Cs 5. but there can be only one C immediately inside an A So at the moment I have these tables (database is MySQL): Code: Product_A - id - properties... Product_B - id - product_a_id - properties... Product_C - id - product_a_id - product_b_id - properties... This "works". It's simple and easy to handle. I can maintain integrity outside database. I can make sure that Product_C.product_a_id is NULL (or zero) if Product_C.product_b_id is not, and vice versa. But I don't really like it. It seems clumsy, amateurish and I would really like the database to take care of data integrity. I thought about creating just one Products table and marking relationships with 'parent_id' and separating the properties into either several tables (one for each product) or just one big table (with references to Property_types table, or something like that), but it seems like overkill and would unnecessarily complicate the queries. (Or at least the way I thought I'd do it would.) There would also still be problems with data integrity. I could easily have an A contain Bs and Cs side to side. in all, to me it seems like even worse a solution. So I'm asking for some pointers here. I think I'm happy with having the three tables, but not the way Product_C table stands at the moment. I just don't have any idea how to make it better. To me it seems like I'd need to have one foreign key pointing to multiple tables (this is a recurring problem in this database, actually), and that smells like a design error.
VIEWS ON THIS POST

176

Posted on:

Sunday 21st October 2012
View Replies!

how best to handle yes/no columns?

I'm a total begginer, and I'm programming a database for my stock home plans. I have them in a spreadsheet and am importing them into a MySQL database table. I have 6 or 7 criteria that are Yes/No questions for each house. (It either has a basement or it doesn't. It either requires steel in the framing or it doesn't - and so forth). Each house could fit none, one or more of these criteria. I could have additional criteria in the future. Right now I have a separate column in the Excel spreadsheet with Yes or No. What's the smartest way to handle this in a database Should I still have a column for each, with a 0 or a 1 Or - is there a better way
VIEWS ON THIS POST

170

Posted on:

Sunday 21st October 2012
View Replies!

Best way to handle different user types in DB?

, I am having trouble with the following: I currently have a DB setup that has all the users in one table. However, there are subtle differences between these user types such that for some users, certain fields in the table need not be specified. Now sure, I can set those fields to NULL, but the fields I am talking about are Foreign Keys. My solution up until this point has been to set each field as NOT NULL, and allow the user to choose "n/a" as a value for those fields. This of course requires that my referencing tables have "n/a" as values as well, and I just don't like that. I can set up three separate user tables, which would eliminate the need for redundant fields, however this makes the login process more difficult. Right now, the user table has the login information. If I have three different tables, all with their own login information (username, password), then when the system validates, it would have to query three different tables and see which one yields a match. Again, this is not good. So, I say to myself, why not have all of the login information in one table I think this is a good idea, but I'm not sure how I should set up the references to the tables with the other user information. What do you think What is normally done in this type of situation
VIEWS ON THIS POST

119

Posted on:

Wednesday 24th October 2012
View Replies!

Best way to handle collations for multilingual column?

Hi , just a quick question, how do you handle different sort ordering in your multilingual web apps Assuming DB design like this , having rows for multiple languages in one column, how do you retrieve results in correct order for particular language It is neccessary to call COLLATE() for every single ORDER BY clause or is there any better way to achieve this
VIEWS ON THIS POST

181

Posted on:

Wednesday 24th October 2012
View Replies!

Duplicate records - how to not show duplicates?

I have a MYSQL Table which has many e-mail address duplicates. When our programmer did a newsletter function, he didn't add anything to check to see if they were already added. So, ultimately, what we have now, is a newsletter database of peoples e-mail addresses, of which there are many people in there more than once. What is a MYSQL string that we can run that will basically take all of the e-mail addresses in this table, and give us the output of just one (it won't show the duplicates). I would rather keep the duplicates where they are (they show another date that they joined the newsletter), but for our mailing list, I don't want the duplicate e-mail addresses in our mailing list. Any suggestions
VIEWS ON THIS POST

159

Posted on:

Wednesday 24th October 2012
View Replies!

Impossible duplicate...

, i have a very strange case; when i submit the following query Code: select id from text_tmp limit 25; my result is : Code: +----+ | id | +----+ | 1 | | 2 | | 4 | | 5 | | 6 | | 7 | | 8 | | 10 | | 11 | | 13 | | 15 | | 16 | | 17 | | 18 | | 20 | | 21 | | 23 | | 24 | | 25 | | 26 | | 27 | | 28 | | 30 | | 31 | | 32 | +----+ therefore, id 22 (and a few more) is missing... and when i ask for : Code: UPDATE text_tmp SET id='21', place=((text_tmp.original_id % 5) +1), round=round-1, occupied='no' WHERE occupied='yes' ORDER BY id DESC LIMIT 1; it returns me Code: DBD::mysql::db do failed: Duplicate entry '22' for key 1 .... how come p.s.: meanwhile i'm googling for help on the issue but the last 2 hours haven't brought be any info yet in my needs.
VIEWS ON THIS POST

150

Posted on:

Wednesday 24th October 2012
View Replies!

Finding duplicates that also do not match ! Almost duplicates

Guys. I have been using MySQL for a couple of years but am truly stuck with this one and would appreciate anyone's help. I am searching a large database of almost 1 million records so the statement needs to be as short as possible. In 1 table I am searching through 4 columns 3 of them need to match (duplicates) and one of them must be different. Vendor Name Invoice No Invoice Date Invoice Amount Boots The Chemist 001 01/01/07 10,000 Boots 001 01/01/07 10,000 Boots The Chemist 001 01/01/07 10,000 Boots The Chemist 001 01/01/07 10,000 So I need to do a search that would point out the above selection because the last 3 columns match and the Vendor Names do not match. But if all four columns match (duplicates) I need to ignore. Vendor Name Invoice No Invoice Date Invoice Amount WH Smiths 001 01/01/07 5,000 WH Smiths 001 01/01/07 5,000 WH Smiths 001 01/01/07 5,000 So the key is to display where the last 3 columns match and the first column does not match. So far, I have this bit it could be completely wrong: mysql = "SELECT * FROM (SELECT *, COUNT(*) AS Duplicates FROM tblMain GROUP BY InvAmo, InvNum, InvDate HAVING COUNT(*) > 1) a1 " &_ "GROUP BY VendName HAVING COUNT(VendName) < Duplicates" The nested select statement finds all duplicates for the last 3 fields and the outer select statement should then group by the Vendor Name and see if there are any difference but obviously it is not working... Many
VIEWS ON THIS POST

140

Posted on:

Wednesday 24th October 2012
View Replies!

Make fieldB unique if fieldA is duplicate

I'm sure that there is a way to do this thru SQL, but my inexperienced toying is not producing results. The way my client wants the record id's to look is 200609220643001 (200609220643 = year, month, date, hour, minute... generated in the PHP submit and stored in fieldA) + (001 = unique id generated if fieldA if duplicate). Currently fieldA is a varChar and fieldB is a tinyInt with unsigned_zerofill/auto_increment... but I don't really want fieldB to auto_increment unless fieldA is a duplicate. I've checked out this link with no luck: example-auto-increment. Whenever I used the settings described I would get errors. I'm using MySQL v.4.1.21, and phpmyadmin v.2.8.0.2. Any ideas on the appropriate table setup to do this, or will I have to write a PHP function to check for duplicates
VIEWS ON THIS POST

120

Posted on:

Wednesday 24th October 2012
View Replies!

form handler not working, have a look

I'm trying to use a form to add some info to my DB. I'm a noob so I have cut and pasted my way to get to this point, but I am stuck as to why this is not working. There are 2 parts to my code... the first is the actual form and then the second is the form function. This is the form: PHP Code: OpenTable(); \t\t//thisisadd-officeform \t\t{ \t\tprint""; \t\techo"Usetheformbelowtoaddadditionaloffices." \t\t."" \t\t."Step1,pleasecheckwherethisofficeislocated." \t\t.""._FW."" \t\t.""._NW."" \t\t.""._N."" \t\t.""._S."" \t\t.""._E."" \t\t.""._CH."" \t\t.""._ML."" \t\t.""._RF.""; \t\techo"Step2,pleasechangeinfobelowtoalteroraddanadditionalofficelocationwhereyoucanbefoundbythepublic." \t\t\t\t.""._PRONAME.":".$userinfo['name']."" \t\t\t\t.""._PRACTNAME.":" \t\t\t\t.""._ADDRESS1.":" \t\t\t\t.""._ADDRESS2.":" \t\t\t\t.""._CITY.":" \t\t\t\t.""._STATE.":" \t\t\t\t.""._ZIP.":" \t\t\t\t.""._PHONE.":" \t\t\t\t.""._FAX.":" \t\t\t\t.""._WEBSITE.":"; \t\techo"Step3,click'SaveOffice'." \t\t\t\t."" \t\t\t\t."" \t\t\t\t."" \t\t\t\t.""; \t\t} \t\tCloseTable(); That form above allwos users to check a radio button (only one of 8 can be checked) for a location and then they can input the office info (address, phone, etc) and this is the "add-office" function: PHP Code: functionsaveoffice($user_id,$username,$storynum,$ublockon,$ublock,$broadcast,$oid,$practname,$add1,$add2,$city,$state,$zip,$phone,$fax,$website){ \t\tglobal$user,$cookie,$userinfo,$user_prefix,$db,$module_name; \t\tcookiedecode($user); \t\t$check=$cookie[1]; \t\t$check2=$cookie[2]; \t\t$sql="SELECTuser_id,user_passwordFROM".$user_prefix."_usersWHEREusername='$check'"; \t\t$result=$db->sql_query($sql); \t\t$row=$db->sql_fetchrow($result); \t\t$vuid=intval($row['user_id']); \t\t$ccpass=$row['user_password']; \t\tif(($user_id==$vuid)AND($check2==$ccpass)){ \t\tif(isset($ublockon))$ublockon=1;else$ublockon=0; \t\t$ublock=FixQuotes($ublock); \t\t$db->sql_query("UPDATEofficesSEToid='$oid',locationid='$locationid',offname='$offname',add1='$add1',add2='$add2',city='$city',state='$state',zip='$zip',phone='$phone',fax='$fax',website='$website',WHEREuser_id='$user_id'"); \t\tgetusrinfo($user); \t\tdocookie($userinfo['user_id'],$userinfo['username'],$userinfo['user_password'],$userinfo['storynum'],$userinfo['umode'],$userinfo['uorder'],$userinfo['thold'],$userinfo['noscore'],$userinfo['ublockon'],$userinfo['theme'],$userinfo['commentmax']); \t\tHeader("Location:modules.phpname=$module_name"); \t\t} } Sorry for the long post but can anyone see any mistakes My site comes up OK, and redirects when I click on the "Add Office" button, but nothign goes into the DB andI get no errors. PS- this is a phpNuke site
VIEWS ON THIS POST

177

Posted on:

Wednesday 24th October 2012
View Replies!

On duplicate key Update did not work

Hi.. I encountered problem in using on duplicate key update.. here is my query: Code: INSERT INTO parameter_settings (P27, P27_max, P27LOT_max, P27_maxdoz, P27Doz_max, P27_min, P27LOT_min, P27_mindoz, P27Doz_min, P28, P28_max, P28LOT_max, P28_maxdoz, P28Doz_max, P28_min, P28LOT_min, P28_mindoz, P28Doz_min, P30, P30_max, P30LOT_max, P30_maxdoz, P30Doz_max, P30_min, P30LOT_min, P30_mindoz, P30Doz_min, P32, P32_max, P32LOT_max, P32_maxdoz, P32Doz_max, P32_min, P32LOT_min, P32_mindoz, P32Doz_min, P32W, P32W_max, P32WLOT_max, P32W_maxdoz, P32WDoz_max, P32W_min, P32WLOT_min, P32W_mindoz, P32WDoz_min, P33, P33_max, P33LOT_max, P33_maxdoz, P33Doz_max, P33_min, P33LOT_min, P33_mindoz, P33Doz_min, P35, P35_max, P35LOT_max, P35_maxdoz, P35Doz_max, P35_min, P35LOT_min, P35_mindoz, P35Doz_min, P35M, P35M_max, P35MLOT_max, P35M_maxdoz, P35MDoz_max, P35M_min, P35MLOT_min, P35M_mindoz, P35MDoz_min, P35W, P35W_max, P35WLOT_max, P35W_maxdoz, P35WDoz_max, P35W_min, P35WLOT_min, P35W_mindoz, P35WDoz_min, P38, P38_max, P38LOT_max, P38_maxdoz, P38Doz_max, P38_min, P38LOT_min, P38_mindoz, P38Doz_min, P41, P41_max, P41LOT_max, P41_maxdoz, P41Doz_max, P41_min, P41LOT_min, P41_mindoz, P41Doz_min, P42, P42_max, P42LOT_max, P42_maxdoz, P42Doz_max, P42_min, P42LOT_min, P42_mindoz, P42Doz_min, P43, P43_max, P43LOT_max, P43_maxdoz, P43Doz_max, P43_min, P43LOT_min, P43_mindoz, P43Doz_min, P45, P45_max, P45LOT_max, P45_maxdoz, P45Doz_max, P45_min, P45LOT_min, P45_mindoz, P45Doz_min, P46, P46_max, P46LOT_max, P46_maxdoz, P46Doz_max, P46_min, P46LOT_min, P46_mindoz, P46Doz_min, P47, P47_max, P47LOT_max, P47_maxdoz, P47Doz_max, P47_min, P47LOT_min, P47_mindoz, P47Doz_min, Total, Total_max, TotalLOT_max, Total_maxdoz, TotalDoz_max, Total_min, TotalLOT_min, Total_mindoz, TotalDoz_min ) VALUES ('$P27', '$P27_max', '$P27LOT_max', '$P27_maxdoz', '$P27Doz_max', '$P27_min', '$P27LOT_min', '$P27_mindoz', '$P27Doz_min', '$P28', '$P28_max', '$P28LOT_max', '$P28_maxdoz', '$P28Doz_max', '$P28_min', '$P28LOT_min', '$P28_mindoz', '$P28Doz_min', '$P30', '$P30_max', '$P30LOT_max', '$P30_maxdoz', '$P30Doz_max', '$P30_min', '$P30LOT_min', '$P30_mindoz', '$P30Doz_min', '$P32', '$P32_max', '$P32LOT_max', '$P32_maxdoz', '$P32Doz_max', '$P32_min', '$P32LOT_min', '$P32_mindoz', '$P32Doz_min', '$P32W', '$P32W_max', '$P32WLOT_max', '$P32W_maxdoz', '$P32WDoz_max', '$P32W_min', '$P32WLOT_min', '$P32W_mindoz', '$P32WDoz_min', '$P33', '$P33_max', '$P33LOT_max', '$P33_maxdoz', '$P33Doz_max', '$P33_min', '$P33LOT_min', '$P33_mindoz', '$P33Doz_min', '$P35', '$P35_max', '$P35LOT_max', '$P35_maxdoz', '$P35Doz_max', '$P35_min', '$P35LOT_min', '$P35_mindoz', '$P35Doz_min', '$P35M', '$P35M_max', '$P35MLOT_max', '$P35M_maxdoz', '$P35MDoz_max', '$P35M_min', '$P35MLOT_min', '$P35M_mindoz', '$P35MDoz_min', '$P35W', '$P35W_max', '$P35WLOT_max', '$P35W_maxdoz', '$P35WDoz_max', '$P35W_min', '$P35WLOT_min', '$P35W_mindoz', '$P35WDoz_min', '$P38', '$P38_max', '$P38LOT_max', '$P38_maxdoz', '$P38Doz_max', '$P38_min', '$P38LOT_min', '$P38_mindoz', '$P38Doz_min', '$P41', '$P41_max', '$P41LOT_max', '$P41_maxdoz', '$P41Doz_max', '$P41_min', '$P41LOT_min', '$P41_mindoz', '$P41Doz_min', '$P42', '$P42_max', '$P42LOT_max', '$P42_maxdoz', '$P42Doz_max', '$P42_min', '$P42LOT_min', '$P42_mindoz', '$P42Doz_min', '$P43', '$P43_max', '$P43LOT_max', '$P43_maxdoz', '$P43Doz_max', '$P43_min', '$P43LOT_min', '$P43_mindoz', '$P43Doz_min', '$P45', '$P45_max', '$P45LOT_max', '$P45_maxdoz', '$P45Doz_max', '$P45_min', '$P45LOT_min', '$P45_mindoz', '$P45Doz_min', '$P46', '$P46_max', '$P46LOT_max', '$P46_maxdoz', '$P46Doz_max', '$P46_min', '$P46LOT_min', '$P46_mindoz', '$P46Doz_min', '$P47', '$P47_max', '$P47LOT_max', '$P47_maxdoz', '$P47Doz_max', '$P47_min', '$P47LOT_min', '$P47_mindoz', '$P47Doz_min', '$Total', '$Total_max', '$TotalLOT_max', '$Total_maxdoz', '$TotalDoz_max', '$Total_min', '$TotalLOT_min', '$Total_mindoz', '$TotalDoz_min' ) ON DUPLICATE KEY UPDATE P27 = '$P27', P27_max = '$P27_max', P27LOT_max = '$P27LOT_max', P27_maxdoz = '$P27_maxdoz', P27Doz_max = '$P27Doz_max', P27_min = '$P27_min', P27LOT_min = '$P27LOT_min', P27_mindoz = '$P27_mindoz', P27Doz_min = '$P27Doz_min', P28 = '$P28', P28_max = '$P28_max', P28LOT_max = '$P28LOT_max', P28_maxdoz = '$P28_maxdoz', P28Doz_max = '$P28Doz_max', P28_min = '$P28_min', P28LOT_min = '$P28LOT_min', P28_mindoz = '$P28_mindoz', P28Doz_min = '$P28Doz_min', P30 = '$P30', P30_max = '$P30_max', P30LOT_max = '$P30LOT_max', P30_maxdoz = '$P30_maxdoz', P30Doz_max = '$P30Doz_max', P30_min = '$P30_min', P30LOT_min = '$P30LOT_min', P30_mindoz = '$P30_mindoz', P30Doz_min = '$P30Doz_min', P32 = '$P32', P32_max = '$P32_max', P32LOT_max = '$P32LOT_max', P32_maxdoz = '$P32_maxdoz', P32Doz_max = '$P32Doz_max', P32_min = '$P32_min', P32LOT_min = '$P32LOT_min', P32_mindoz = '$P32_mindoz', P32Doz_min = '$P32Doz_min', P32W = '$P32W', P32W_max = '$P32W_max', P32WLOT_max = '$P32WLOT_max', P32W_maxdoz = '$P32W_maxdoz', P32WDoz_max = '$P32WDoz_max', P32W_min = '$P32W_min', P32WLOT_min = '$P32WLOT_min', P32W_mindoz = '$P32W_mindoz', P32WDoz_min = '$P32WDoz_min', P33 = '$P33', P33_max = '$P33_max', P33LOT_max = '$P33LOT_max', P33_maxdoz = '$P33_maxdoz', P33Doz_max = '$P33Doz_max', P33_min = '$P33_min', P33LOT_min = '$P33LOT_min', P33_mindoz = '$P33_mindoz', P33Doz_min = '$P33Doz_min', P35 = '$P35', P35_max = '$P35_max', P35LOT_max = '$P35LOT_max', P35_maxdoz = '$P35_maxdoz', P35Doz_max = '$P35Doz_max', P35_min = '$P35_min', P35LOT_min = '$P35LOT_min', P35_mindoz = '$P35_mindoz', P35Doz_min = '$P35Doz_min', P35M = '$P35M', P35M_max = '$P35M_max', P35MLOT_max = '$P35MLOT_max', P35M_maxdoz = '$P35M_maxdoz', P35MDoz_max = '$P35MDoz_max', P35M_min = '$P35M_min', P35MLOT_min = '$P35MLOT_min', P35M_mindoz = '$P35M_mindoz', P35MDoz_min = '$P35MDoz_min', P35W = '$P35W', P35W_max = '$P35W_max', P35WLOT_max = '$P35WLOT_max', P35W_maxdoz = '$P35W_maxdoz', P35WDoz_max = '$P35WDoz_max', P35W_min = '$P35W_min', P35WLOT_min = '$P35WLOT_min', P35W_mindoz = '$P35W_mindoz', P35WDoz_min = '$P35WDoz_min', P38 = '$P38', P38_max = '$P38_max', P38LOT_max = '$P38LOT_max', P38_maxdoz = '$P38_maxdoz', P38Doz_max = '$P38Doz_max', P38_min = '$P38_min', P38LOT_min = '$P38LOT_min', P38_mindoz = '$P38_mindoz', P38Doz_min = '$P38Doz_min', P41 = '$P41', P41_max = '$P41_max', P41LOT_max = '$P41LOT_max', P41_maxdoz = '$P41_maxdoz', P41Doz_max = '$P41Doz_max', P41_min = '$P41_min', P41LOT_min = '$P41LOT_min', P41_mindoz = '$P41_mindoz', P41Doz_min = '$P41Doz_min', P42 = '$P42', P42_max = '$P42_max', P42LOT_max = '$P42LOT_max', P42_maxdoz = '$P42_maxdoz', P42Doz_max = '$P42Doz_max', P42_min = '$P42_min', P42LOT_min = '$P42LOT_min', P42_mindoz = '$P42_mindoz', P42Doz_min = '$P42Doz_min', P43 = '$P43', P43_max = '$P43_max', P43LOT_max = '$P43LOT_max', P43_maxdoz = '$P43_maxdoz', P43Doz_max = '$P43Doz_max', P43_min = '$P43_min', P43LOT_min = '$P43LOT_min', P43_mindoz = '$P43_mindoz', P43Doz_min = '$P43Doz_min', P45 = '$P45', P45_max = '$P45_max', P45LOT_max = '$P45LOT_max', P45_maxdoz = '$P45_maxdoz', P45Doz_max = '$P45Doz_max', P45_min = '$P45_min', P45LOT_min = '$P45LOT_min', P45_mindoz = '$P45_mindoz', P45Doz_min = '$P45Doz_min', P46 = '$P46', P46_max = '$P46_max', P46LOT_max = '$P46LOT_max', P46_maxdoz = '$P46_maxdoz', P46Doz_max = '$P46Doz_max', P46_min = '$P46_min', P46LOT_min = '$P46LOT_min', P46_mindoz = '$P46_mindoz', P46Doz_min = '$P46Doz_min', P47 = '$P47', P47_max = '$P47_max', P47LOT_max = '$P47LOT_max', P47_maxdoz = '$P47_maxdoz', P47Doz_max = '$P47Doz_max', P47_min = '$P47_min', P47LOT_min = '$P47LOT_min', P47_mindoz = '$P47_mindoz', P47Doz_min = '$P47Doz_min', Total = '$Total', Total_max = '$Total_max', TotalLOT_max = '$TotalLOT_max', Total_maxdoz = '$Total_maxdoz', TotalDoz_max = '$TotalDoz_max', Total_min = '$Total_min', TotalLOT_min = '$TotalLOT_min', Total_mindoz = '$Total_mindoz', TotalDoz_min = '$TotalDoz_min' I want to happen is only one row will add in my database and if I need to edit my data It will update the data that already been save. But in my code instead of updating my data in my database, it will insert or again in another row. I cant figure out whats wrong or missing in my query syntax. Thank you Thank you
VIEWS ON THIS POST

390

Posted on:

Thursday 25th October 2012
View Replies!

avoid entering duplicate records through procedure

Hi I want to insert the record into a table after checking that the record is not available in the table. below is the code:- CREATE PROCEDURE newprcSaveGPSLocation( _lat VARCHAR(45), _lng VARCHAR(45), _mph VARCHAR(45), _direction VARCHAR(45), _distance VARCHAR(45), _date VARCHAR(100), _locationMethod VARCHAR(100), _phoneNumber VARCHAR(20), _sessionID VARCHAR(50), _accuracy VARCHAR(20), _locationIsValid VARCHAR(5), _extraInfo VARCHAR(255), _recid VARCHAR(25) ) BEGIN DECLARE l_count INTEGER; select count(*) into l_count from locations where record_id ='_recid'; IF l_count
VIEWS ON THIS POST

135

Posted on:

Thursday 25th October 2012
View Replies!

avoiding duplicates

I have two mysql tables that are linked: subscribers, and subscribers_data. subscribers holds personal information about a subscriber, subscriber_data holds the technical info. the pk and fk are the subscriber's id. I'm having an issue while inserting email addresses into the subscribers_data table. I build a query and insert new email addresses into the subscribers table. I then want to take those inserted id numbers and insert them into the subscribers_data table. If I try to insert duplicate email addresses, they are ignored by the subscriber table (as they should be), however, the subscribers_data table continues with INSERTs. For instance. If I add two email addresses, then the subscribers table will now have 2, and the subscribers_data will have 2. Now, If I attempt to add those exact same email addresses, the subscribers table will reject them, but the subscribers_data table will now have 4 rows. It should reject them too. I'm just not sure how to do that. I tried foreach() and while() loops, but kept getting the same results. Code MySQL: CREATE TABLE IF NOT EXISTS `subscribers` ( `id` int(10) unsigned NOT NULL auto_increment, `fname` varchar(30) collate utf8_unicode_ci NOT NULL, `lname` varchar(30) collate utf8_unicode_ci NOT NULL, `email` varchar(60) collate utf8_unicode_ci NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `email` (`email`), ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Subscriber information' ; CREATE TABLE IF NOT EXISTS `subscribers_data` ( `sd_sub_id` int(10) unsigned NOT NULL, `sd_date_added` datetime NOT NULL default '0000-00-00 00:00:00', `sd_active` char(1) collate utf8_unicode_ci NOT NULL default '0', `sd_verified` char(1) collate utf8_unicode_ci NOT NULL default '0', PRIMARY KEY (`sd_sub_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='additional subscriber info'; PHP Code: //counteachPOSTedemailaddress $insert_count=0; $insert_id=get_max_sub_id()+1;//subscriberidneedstostartathighestid+1 $ok=array(); foreach($emailsas$email){ \t\tif(check_email_address($email)){ \t\t\t\t \t\t\t\t$ok[]="($insert_id,'$email')"; \t\t\t\t \t\t\t\t//checkforduplicateemailaddresses \t\t\t\t$email_check=email_check($email); \t\t\t\t \t\t\t\tif($email_check>0){ \t\t\t\t\t\t//emailaddressalreadyexists! \t\t\t\t\t\t$dup_error=1; \t\t\t\t\t\t$dup_email[]=$email; \t\t\t\t\t\t$dup_count=count($dup_email); \t\t\t\t} \t\t\t\t \t\t\t\t$insert_count++; \t\t\t\t$insert_id++; \t\t\t\t$new_email=$email; \t\t \t\t}else{ \t\t\t\t//errorstuff \t\t} } //createtemptableforinsertingnewemailaddresses $sql=mysql_query("CREATETEMPORARYTABLEsubscribers_temp(idINT(10),emailVARCHAR(60))TYPE=HEAP"; if(!empty($ok)){ \t\t$insert_query="INSERTINTOsubscribers_temp(id,email)VALUES".join(',',$ok); \t\t \t\t//insertnewemailaddressesintosubscribers_temptable \t\t$sql=mysql_query($insert_query)ordie(mysql_error()); } //getemailaddressesfromsubscribers_temptableandinsertintosubscriberstable $sql=mysql_query("INSERTIGNOREsubscribers(id,email)SELECTid,emailFROMsubscribers_temp"); //now,insertsubscriber'sidnumberandadditionaldataintosubscribersdatatable $query=mysql_query(" INSERTINTOsubscribers_data( \t\tsd_sub_id, \t\tsd_added_by, \t\tsd_date_added, \t\tsd_active, \t\tsd_verified )SELECTid,'a',now(),1,1FROMsubscribers_temp");
VIEWS ON THIS POST

138

Posted on:

Thursday 25th October 2012
View Replies!

How can I remove this ?no-duplicate? constrict ion?

I got this error massage: Duplicate entry '135' for key 1 SQL= INSERT INTO mos_comprofiler ( `id`,`user_id`,`approved` VALUES ( '135','135','1','1',' Its ok with me if id=id_user, How can I remove this no-duplicate constrict ion
VIEWS ON THIS POST

154

Posted on:

Thursday 25th October 2012
View Replies!