SEARCH YOUR SOLUTION HERE  

How can I allow for duplicate ids in the primary key field

I have items that are being put in to my database. I can enter one item but then when I want to enter a new item I get a duplicate key error. The reason I need to have the same id for items is that the items correspond to clients and each client has different items. I thought I had this set up correctly but setting both of the fields in the DB table to be the primary keys but that is not working. Basically this is what my DB needs to look like.

soq_references_id-------bullet
----------------1-------bullet 1
----------------1-------bullet 2
----------------1-------bullet 3

It will continue down the line with the different ids. The Ids are coming from a table called soq_references, so each soq_references_id relates to a clietns ID and puts the bullets under the right client.

Cliff notes:
I need to know how to allow for duplicate soq_references_id's.

if you need more information please ask, thank you in advance for the help. , 9-bullet is just saying the id is "9" and the text being put in is "bullet". Im using CRUD so that's how it displays it.

Posted On: Wednesday 24th of October 2012 01:28:59 AM Total Views:  218
View Complete with Replies




Related Messages:

How can i get this SQL statement to remove apostrophes   (139 Views)
, I have the following SQL statement: Code: SELECT Out.ID FROM Quote Inc INNER JOIN Quote Out ON Inc.Code = Out.Code WHERE Inc.ID IN (@TourIDString) AND Inc.ID Out.ID @TourIDString contains values like so: '89918, 89641, 89644, 89668, 89649' So when putting this into context, the SQL Statement in return would be this: Code: SELECT Out.ID FROM Quote Inc INNER JOIN Quote Out ON Inc.Code = Out.Code WHERE Inc.ID IN ('89918, 89641, 89644, 89668, 89649') AND Inc.ID Out.ID My question is, that is there any way or removing the apostrophes so that i am returned with this: Code: SELECT Out.ID FROM Quote Inc INNER JOIN Quote Out ON Inc.Code = Out.Code WHERE Inc.ID IN (89918, 89641, 89644, 89668, 89649) AND Inc.ID Out.ID Hope someone can help me out Kind regards,
SQL - How can I SELECT from last to first   (122 Views)
How can I Select and get from the end on the list instade from the start
How can i do this via MySQL plz? [SOLVED]   (230 Views)
I'm working to create a quiz website. But i have a problem with (How to denied the viewed questions for a member). Let's me explain it: I've 3 table in my database. * The first table called "members" to store the members details like memberID, memberName, etc... * The second table called "denied_questions" is stored the answered questions and it's contains two field (qustionID, memberID). * The third table called "questions" is contains the questions list. Now. I want to get a random question from questions table. The question should not be viewed to the member in the past. The following SQL does not working! Code MySQL: SELECT qus.qusID, qus.question FROM `questions` AS qus, `denied_qus` AS denied WHERE denied.memberID != 3 AND qus.qusID != denied.qusID ORDER BY RAND() I just want to get a random question from the questions table and compare this question with the answered questions from the "denied_questions" table. Any idea please
how can i count number of rows for several tables in one efficient query?   (136 Views)
i need to count number of rows for 4 diff table, Code PHP: $sql = mysql_query("SELECT userid FROM table_1 WHERE userid='$member_id' AND foo=1"); $total_table_1_row = mysql_num_rows($sql); $sql = mysql_query("SELECT userid FROM table_2 WHERE userid='$member_id' AND foo=1"); $total_table_2_row = mysql_num_rows($sql); $sql = mysql_query("SELECT userid FROM table_3 WHERE userid='$member_id'"); $total_table_3_row = mysql_num_rows($sql); $sql = mysql_query("SELECT name FROM table_4 WHERE name='$member_name'"); $total_table_4_row = mysql_num_rows($sql); it is lightning fast for 1 or 2 rows while testing on localhost, but what if i have millions of rows for each table how can i combine them, with UNION
how can i found the column name in table using mysql   (132 Views)
end of post
Error in MySql File, can't find it, help!   (142 Views)
I am in the process of transferring a database from one part of my server to another. It is not a straight transfer due to a modification on the new database. Both databases run Zen Cart, the first one is a little over 6 months old. The second one is almost a fresh install with a specific modification for my designers so only my designers are added, no products. As a result I actually have three copies of a database to work with in creating a fourth. 1) My old database, named gblcreations/store 2) My new database, named secretcity-treasures/store, fresh install (as a check to make sure of the correct code) 3) My new database, named secretcity-treasures/store, with designers added. 4) My new workingsecretcitydatabase that is the mesh of the above three. Where I get hung up is in the products section. I have an error in my syntax and I can not find it. I am using the table data from #2 and the dump data, altered from #1. It accepted the designer data when I put in the table data from #2 and the dump data from #2. Anyway, here is the code: Code: -- Table structure for table `products` -- DROP TABLE IF EXISTS `products`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `products` ( `products_id` int(11) NOT NULL auto_increment, `products_type` int(11) NOT NULL default '1', `products_quantity` float NOT NULL default '0', `products_model` varchar(32) default NULL, `products_image` varchar(64) default NULL, `products_price` decimal(15,4) NOT NULL default '0.0000', `products_virtual` tinyint(1) NOT NULL default '0', `products_date_added` datetime NOT NULL default '0001-01-01 00:00:00', `products_last_modified` datetime default NULL, `products_date_available` datetime default NULL, `products_weight` float NOT NULL default '0', `products_status` tinyint(1) NOT NULL default '0', `products_tax_class_id` int(11) NOT NULL default '0', `manufacturers_id` int(11) default NULL, `products_ordered` float NOT NULL default '0', `products_quantity_order_min` float NOT NULL default '1', `products_quantity_order_units` float NOT NULL default '1', `products_priced_by_attribute` tinyint(1) NOT NULL default '0', `product_is_free` tinyint(1) NOT NULL default '0', `product_is_call` tinyint(1) NOT NULL default '0', `products_quantity_mixed` tinyint(1) NOT NULL default '0', `product_is_always_free_shipping` tinyint(1) NOT NULL default '0', `products_qty_box_status` tinyint(1) NOT NULL default '1', `products_quantity_order_max` float NOT NULL default '0', `products_sort_order` int(11) NOT NULL default '0', `products_discount_type` tinyint(1) NOT NULL default '0', `products_discount_type_from` tinyint(1) NOT NULL default '0', `products_price_sorter` decimal(15,4) NOT NULL default '0.0000', `master_categories_id` int(11) NOT NULL default '0', `products_mixed_discount_quantity` tinyint(1) NOT NULL default '1', `metatags_title_status` tinyint(1) NOT NULL default '0', `metatags_products_name_status` tinyint(1) NOT NULL default '0', `metatags_model_status` tinyint(1) NOT NULL default '0', `metatags_price_status` tinyint(1) NOT NULL default '0', `metatags_title_tagline_status` tinyint(1) NOT NULL default '0', `original_cat` int(11) NOT NULL default '0', `moved_to_cat` int(11) NOT NULL default '0', `product_on_queue` int(11) NOT NULL default '0', PRIMARY KEY (`products_id`), KEY `idx_products_date_added_zen` (`products_date_added`), KEY `idx_products_status_zen` (`products_status`), KEY `idx_products_date_available_zen` (`products_date_available`), KEY `idx_products_ordered_zen` (`products_ordered`), KEY `idx_products_model_zen` (`products_model`), KEY `idx_products_price_sorter_zen` (`products_price_sorter`), KEY `idx_master_categories_id_zen` (`master_categories_id`), KEY `idx_products_sort_order_zen` (`products_sort_order`), KEY `idx_manufacturers_id_zen` (`manufacturers_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `products` -- INSERT INTO `products` (`products_id`, `products_type`, `products_quantity`, `products_model`, `products_image`, `products_price`, `products_virtual`, `products_date_added`, `products_last_modified`, `products_date_available`, `products_weight`, `products_status`, `products_tax_class_id`, `manufacturers_id`, `products_ordered`, `products_quantity_order_min`, `products_quantity_order_units`, `products_priced_by_attribute`, `product_is_free`, `product_is_call`, `products_quantity_mixed`, `product_is_always_free_shipping`, `products_qty_box_status`, `products_quantity_order_max`, `products_sort_order`, `products_discount_type`, `products_discount_type_from`, `products_price_sorter`, `master_categories_id`, `products_mixed_discount_quantity`, `metatags_title_status`, `metatags_products_name_status`, `metatags_model_status`, `metatags_price_status`, `metatags_title_tagline_status`, `original_cat`, `moved_to_cat`, `product_on_queue`) VALUES (1, 1, 100, 'GBL10014', 'GraphicsByLiz_EasterTyme.gif', '0.0000', 0, '2009-07-11 21:10:15', '2010-02-03 00:42:05', NULL, 0, 0, 0, 8, 0, 1, 1, 0, 1, 0, 1, 0, 0, 0, 0, 0, 0, '0.0000', 8, 1, 0, 0, 0, 0, 0, 0, 0, 0), (2, 1, 100, 'gbp10053', 'graphicsbypokadot_coffeetime.gif', '4.0000', 0, '2009-07-12 00:19:04', '2010-02-03 12:00:55', NULL, 0, 1, 0, 9, 0, 1, 1, 0, 0, 0, 1, 0, 1, 0, 0, 0, 0, '4.0000', , 9, 0, 0, 0, 0, 0, 0, 0, 0), (3, 1, 100, 'ASD10012', 'AScrappersDream_ForeverYoursPREV.jpg', '2.0000', 0, '2009-07-12 02:16:52', '2010-02-03 01:13:05', NULL, 0, 1, 0, 3, 0, 1, 1, 0, 0, 0, 1, 0, 1, 0, 0, 0, 0, '2.0000', 3, 1, 0, 0, 0, 0, 0, 0, 0, 0), Here is the error message that I get tossed back at me: I also have one other question, I am in need of a sql editor...right now using a trial version of RazorSQL and while it is in my price range, I haven't figured it out completely. It may do everything I need. I would like one that would allow me to look at a sql file as if it were a text box or excel file and not a sql file, so that I can match up the columns. Also I need a way to do find/replace in batch in a better way. The integer that corresponds with `manufacturers_id` may need to be replaced with NULL to make this work.
Scanning Text for Mailing Addresses   (86 Views)
I'm in need of a program that does the following: scans documents or webpages for mailing addresses and copies the addresses into a database of some sort (like a MS Access or just MS Word). I'm contacting all the daycare centers in my town and I have access to hundreds of daycare centers in the area. I need to copy and paste the mailing address for each center into my label-maker program. It's not a select-all and copy job because there is content in between each mailing address which would need to be deleted. I need some kind of text-scanning program that will scan the web page for mailing addresses and copy them into an MS Office program or something similar. Anyone have an idea of where to find this And if there exists such a program, what is it called A text mining program
Newb biting off more than he can chew ... help...   (111 Views)
I'm making a website that will facilitate multiple users updating various sets of data. I'm a complete novice with MySQL, but I'm learning fast! My question is, should I use the same dba user account to do all the updates/modifcations (through PHP), or should I create a new user account for each registered user of the system If anyone has time to explain the "why" part I'd be appreciative.
can't figure out what's wrong with this mysql query   (137 Views)
INSERT INTO state (key,value) VALUES ('non-US','Non-U.S.') I get: Invalid query: 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 'key,value) VALUES ('non-US','Non-U.S.')' at line 1 I can't see what's wrong... Can you The table: CREATE TABLE `state` ( `key` varchar(10) NOT NULL, `value` varchar(200) NOT NULL, PRIMARY KEY (`key`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
store data that can be calculated in a table?   (200 Views)
Hi , As part of a current project I'm coding a simple forum as part of a website. The requirements are the usual, list of topics in various forums (categories) with associated posts. I'm at the database design stage and up til now I've been storing all fields (e.g. number of posts on a topic) in a table in previous forum-style projects I've done. but I realise that using sub-selects I can calculate the number of replies like so: Code SQL: (SELECT COUNT(*) FROM posts WHERE topic_id = x) AS replies in similar ways I can get the user who started the topic, the last user who posted on the topic and other info without having to actually store these. is this a sensible approach to store as little data as possible in the table (even if I'd only be storing a bunch of extra foreign keys) and make the database calculate/return the other data needed with a query I don't expect it will be a large forum so the performance hit should be unnoticeable - shouldn't it I only ask because I've never done a forum this way before.
Column 'PostDate' cannot be null   (286 Views)
I've created a small app for me and some friends to keep track of our projects. Everything worked out ok while running the app at home but when we uploaded it to the server it didn't work I mean, we can browse it but the problem is when we try to insert something into db this error is displayed: Column 'PostDate' cannot be null which is the mysql error thrown here: Code PHP: $queryInsert = @mysql_query("INSERT INTO messages ( PostDate, MsgContent, MsgTitle, AuthorName) VALUES ( convert_tz(CURRENT_TIMESTAMP(), 'SYSTEM', 'GMT') , '".$msgContent."' ,'".$msgTitle."' ,'".$author."')") or exit(mysql_error()); when we try to "send" each other a "message" (not a message like on YIM). the problem is with the Code MySQL: convert_tz(CURRENT_TIMESTAMP(), 'SYSTEM', 'GMT') function which returns null (when php is runing as a FastCGI application ). I've searched the web to find an answer to this problem but couldn't find anything to help me out... our web hoster can support the following types of configuration for php: CGI applicationFastCGI applicationISAPI extension for php version 5.2.5 on a ms windows environment. I tried all those three options, also I tried to set up an ini file but it didn't work .. why does it have to be that complicated I appreciate any help on this one as it's driving me crazy...
Dupilcating data to other rows, can you show me how?   (140 Views)
Hi , Firstly I'm a complete newbie with regard to writing SQL commands, I've been looking through the manuals with regard to the commands but cannot for the life of me get it to work myself. I would really appreciate it if someone could show be how to do this so I can reduce my project time. This is the current sql code of the table in question: Code MySQL: -- -- Table structure for table `jos_vm_product` -- CREATE TABLE `jos_vm_product` ( `product_id` int(11) NOT NULL auto_increment, `vendor_id` int(11) NOT NULL default '0', `product_parent_id` int(11) NOT NULL default '0', `product_sku` varchar(64) NOT NULL default '', `product_s_desc` varchar(255) default NULL, `product_desc` text, `product_thumb_image` varchar(255) default NULL, `product_full_image` varchar(255) default NULL, `product_publish` char(1) default NULL, `product_weight` decimal(10,4) default NULL, `product_weight_uom` varchar(32) default 'pounds.', `product_length` decimal(10,4) default NULL, `product_width` decimal(10,4) default NULL, `product_height` decimal(10,4) default NULL, `product_lwh_uom` varchar(32) default 'inches', `product_url` varchar(255) default NULL, `product_in_stock` int(11) default NULL, `product_available_date` int(11) default NULL, `product_availability` varchar(56) NOT NULL default '', `product_special` char(1) default NULL, `product_discount_id` int(11) default NULL, `ship_code_id` int(11) default NULL, `cdate` int(11) default NULL, `mdate` int(11) default NULL, `product_name` varchar(64) default NULL, `product_sales` int(11) NOT NULL default '0', `attribute` text, `custom_attribute` text NOT NULL, `product_tax_id` tinyint(2) NOT NULL default '0', `product_unit` varchar(32) default NULL, `product_packaging` int(11) default NULL, PRIMARY KEY (`product_id`), KEY `idx_product_vendor_id` (`vendor_id`), KEY `idx_product_product_parent_id` (`product_parent_id`), KEY `idx_product_sku` (`product_sku`), KEY `idx_product_ship_code_id` (`ship_code_id`), KEY `idx_product_name` (`product_name`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT=' products are stored here.' AUTO_INCREMENT=23 ; -- -- Dumping data for table `jos_vm_product` -- INSERT INTO `jos_vm_product` VALUES (17, 1, 0, 'Bedlam Farm Snow', 'Short Description here', '\r\nExtensive write up here about the print \r\n\r\n\r\nView framing style samples here \r\n\r\n', '49627845d3b6073533b4b40c25cf5a25.jpg', '62a43f330965813c212bfbe141fe7453.jpg', 'Y', '8.0000', 'pounds', '18.0000', '24.0000', '1.0000', 'inches', '', 0, 1203580800, '', 'N', 0, NULL, 1203612069, 1203778425, 'Bedlam Farm Snow', 0, 'Size,8x12 matted[=75.00],8x12 framed[=200.00],10x15 matted[=100.00],10x15 framed[=250.00];Frame Style,Antique Silver[+0.00],Rustic Wood[+0.00],Textured Black[+0.00]', '', 3, 'piece', 0); INSERT INTO `jos_vm_product` VALUES (18, 1, 0, 'Carriage barn in Moonlight', '', '', 'c0989cec6ac823497f33537783a7eae1.jpg', '93c181e943da3a9611c21c3ffcc1274f.jpg', 'Y', '0.0000', 'pounds', '0.0000', '0.0000', '0.0000', 'inches', '', 0, -57600, '', 'N', 0, NULL, 1203778633, 1203778633, 'Carriage barn in Moonlight', 0, '', '', 3, 'piece', 0); INSERT INTO `jos_vm_product` VALUES (19, 1, 0, 'Grace', '', '', '1f14189af3705dcae55bc859897bbe85.jpg', '1b4a04a963231fbbc2eba0e005e07ab4.jpg', 'Y', '0.0000', 'pounds', '0.0000', '0.0000', '0.0000', 'inches', '', 0, 1203753600, '', 'N', 0, NULL, 1203778671, 1203778671, 'Grace', 0, '', '', 3, 'piece', 0); INSERT INTO `jos_vm_product` VALUES (20, 1, 0, 'In the Light', '', '', '412dbb13411e6302ab10f92c7821b178.jpg', 'bd2b8a0c8c2ecfbe4d003396d85cc69e.jpg', 'Y', '0.0000', 'pounds', '0.0000', '0.0000', '0.0000', 'inches', '', 0, 1203753600, '', 'N', 0, NULL, 1203778697, 1203778697, 'In the Light', 0, '', '', 3, 'piece', 0); INSERT INTO `jos_vm_product` VALUES (21, 1, 0, 'Misty Bedlam Morning', '', '', '3626733cf0ce0634fea13dce705bf270.jpg', '6937de92bb7a164d073f220ae0c8770e.jpg', 'Y', '0.0000', 'pounds', '0.0000', '0.0000', '0.0000', 'inches', '', 0, 1203753600, '', 'N', 0, NULL, 1203778726, 1203778726, 'Misty Bedlam Morning', 0, '', '', 3, 'piece', 0); INSERT INTO `jos_vm_product` VALUES (22, 1, 0, 'Misty Morning', '', '', '7abd4d261d4098c1387fe462482a6495.jpg', '93faf63293f18ccf5301a6f1c10a6a70.jpg', 'Y', '0.0000', 'pounds', '0.0000', '0.0000', '0.0000', 'inches', '', 0, 1203753600, '', 'N', 0, NULL, 1203778751, 1203778751, 'Misty Morning', 0, '', '', 3, 'piece', 0); The first entry contains all the data I want to replicate to the other 5 rows of products apart from a few values. I would like to duplicate the following columns (hope thats the right terminology) to the other 5 rows: product_s_desc product_desc product_weight product_weight_uom product_length product_width product_height product_lwh_uom product_available_date attribute The rest of the rows are to remain intact as they are specific to the product. What method do I use I assume I then submit this sql change script into phpmyadmin and it will automatically update all the records. Is there an easy user friendly tool to do this
A challenging MySQL question for anyone who thinks they can take it   (133 Views)
The query below displays submissions in the order of how many times each submission has been viewed by a unique visitor in the last 7 days, from highest to lowest. How can I get it to show every record in the 'submission_data' table, even if there are no matching records in the 'submission_views' table Basically, I want the submissions with an 'id_count' of 0 to be displayed at the end of the results. SELECT COUNT(Distinct submission_data.id, submission_views.ip_address) AS id_count, submission_data.id, submission_data.thumbnail_source, submission_data.title FROM submission_data LEFT JOIN submission_views ON submission_data.id = submission_views.submission_id WHERE submission_views.timestamp > DATE_SUB(NOW(), INTERVAL 7 DAY) GROUP BY submission_data.id ORDER BY id_count DESC
how can I test the speed of a table in MYSQL?   (125 Views)
my web hoster told me that my site is slow because of my DB (MYSQL) and I have to perform some actions (linke indexing). how do I check if a table has an index tanks
How large can be a mysql database?   (106 Views)
! I have a q: how large can be a mysql database in order to operate in optimal conditions
Redesigned table...can't rewrite query.   (115 Views)
I've just altered the structure of a table in my database to make it clearer, and to remove duplicate records that would have been stored in it. The problem is I now want to query it, and can't work out how to do it in one query. I could do it in 3, but I'd rather be efficient if possible. The table structure is class classid - int(3) - auto increment - primary key classname - varchar(25) classparent - int(3) The idea being that a record from another table is linked to a classid, from which we can extract the parents of that particular class. There could be varying number of levels, but the top level has a classparent of 0. Is there a way of writing this into one query, ie. using a classid, select the values for that row, and its parent, grandparent etc.
mysqldump can't find table   (161 Views)
, I'm trying to do a backup of just 1 table but when I run the command mysqldump -h machine -u lagoona -p database_name [collection] > collection.sql it complains that it can't find the table called collection which is definitely in the database. mysqldump: Couldn't find table: "[collection]" Is this the right command to use, I'm not that familiar with mysqldump It's on linux and I'm running it from the command line. I can connect OK to the database itself when I use the command mysql -h machine -u lagoona -p I've also tried it as root and as my own username but no difference.
how can i take comma(,) as string in mysql query (was: Urgent)   (163 Views)
hi , i need urgent help.. how can i take comma(,) as string in mysql query.. actually i have a field value like (asd,sdf,asr,asf), (sdf,sdh,aqb), ...... now if someone searches for 'aq' then (sdf,sdh,aqb) value should be shown.. i think u got my point ... thnx
How can i roll back mysql??   (213 Views)
how can i roll back mysql
Error on 'You can't specify target table 'table' for update in FROM clause'   (258 Views)
Hi , I got an error with this statement, $deleteSQL = sprintf("DELETE FROM shop_cart_item WHERE product_id=(SELECT product_id FROM shop_cart_item WHERE shopcart_id=%s AND product_id=%s)", GetSQLValueString($shopcart_id, "int"), GetSQLValueString($_GET['product_id'], "text")); The error was, You can't specify target table 'shop_cart_item' for update in FROM clause. I don't understand why am I getting an error on update when I'm trying to delete a record.