SEARCH YOUR SOLUTION HERE  

Include the primary key in the query

,
I would like to know if it is possible to include the primary key in a query without knowing it's name, for example:
Code SQL: SELECT * FROM `table` WHERE PRIMARY KEY = 5; ...for example, but that doesn't appear to work (error in MySQL syntax). I've tried changing "PRIMARY KEY" to things like INDEX, etc., but nothing appears to work. Is what I'm after actually possible, or do I need to get the name first

Posted On: Thursday 25th of October 2012 11:02:33 PM Total Views:  382
View Complete with Replies




Related Messages:

Mysql restarts ALOT (error included)   (208 Views)
, I have a heavy-traffic website and running 2 servers, one for apache and the other is for mysql .. both are celeron 2.6 with 1gb RAM, the mysql service restarts every 2 minutes by itself and here's what's written in the error messages .. Code: 061225 16:03:28 mysqld restarted 061225 16:03:29 InnoDB: Started; log sequence number 0 89760 /usr/local/libexec/mysqld: ready for connections. Version: '4.1.13-log' socket: '/tmp/mysql.sock' port: 3306 FreeBSD port: mysql-server-4.1.13 061225 16:05:22 [ERROR] /usr/local/libexec/mysqld: Out of memory (Needed 16391 bytes) 061225 16:05:22 [ERROR] /usr/local/libexec/mysqld: Out of memory (Needed 16391 bytes) 061225 16:05:22 [ERROR] /usr/local/libexec/mysqld: Out of memory (Needed 16391 bytes) 061225 16:05:22 [ERROR] /usr/local/libexec/mysqld: Out of memory (Needed 16391 bytes) mysqld got signal 10; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=402653184 read_buffer_size=1044480 max_used_connections=83 max_connections=250 threads_connected=23 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 904214 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. 061225 16:06:24 mysqld restarted 061225 16:06:24 InnoDB: Started; log sequence number 0 89760 /usr/local/libexec/mysqld: ready for connections. Version: '4.1.13-log' socket: '/tmp/mysql.sock' port: 3306 FreeBSD port: mysql-server-4.1.13 mysqld got signal 10; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=402653184 read_buffer_size=1044480 max_used_connections=110 max_connections=250 threads_connected=24 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 904214 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. 061225 16:10:16 mysqld restarted 061225 16:10:16 InnoDB: Started; log sequence number 0 89760 /usr/local/libexec/mysqld: ready for connections. Version: '4.1.13-log' socket: '/tmp/mysql.sock' port: 3306 FreeBSD port: mysql-server-4.1.13 mysqld got signal 10; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=402653184 read_buffer_size=1044480 max_used_connections=105 max_connections=250 threads_connected=16 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 904214 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. 061225 16:12:41 mysqld restarted 061225 16:12:41 InnoDB: Started; log sequence number 0 89760 /usr/local/libexec/mysqld: ready for connections. Version: '4.1.13-log' socket: '/tmp/mysql.sock' port: 3306 FreeBSD port: mysql-server-4.1.13 I tried to do "repair" for all the tables and still it restarts, any help will be greatly appretiated ..
dbconnect include file : where is the best place   (131 Views)
Is it best to have the php file that contains all of your dbconnect information in the directory before your htdocs (public_html etc) directory. I had a copy there that I linking to useing ../connectfile.php but some of my connections didn't work and I put it in the main htdocs and it seemed to fix the problem. But is it a security issue People shouldn't be able to see the info inside because it is php so all they would see is what is echoed to display in a webpage. Did I make sence
Unable to include #include   (142 Views)
I am unable to include #include in C++ project under windows platform. I am using Eclipse IDE. and i have added all the headers files MYSQL 5.1 is also installed on my system. i have searched the complete dir. but i am unable to find . ..\Application.h:32:28: mysql++/mysql++.h: No such file or directory Build error occurred, build is stopped I searched the complete directory but unable to locate the file... Can any one help me please...
ORDER BY WHERE + include rest of table or ORDER? Possible?   (91 Views)
I need to create some SQL that when run returns all the rows which a column is equal to number that has been specified, but then the query needs to return the rest of the rows in the table. How could this be done is it even possible Is their order syntax that could do this better
Left join that includes some values and excludes other values   (149 Views)
I have an article database that associates articles with tags through the table "articles_tags" (this table has two columns: article_id and tag_id). Articles can have multiple tags. I would like to be able to select articles that have certain tags but also do not have others. I have the part that only includes articles with certain tags working. In this example the query selects all articles that have been tagged with both tag id 10 and tag id 11: Code: SELECT `articles`.`id` FROM `articles` LEFT JOIN `articles_tags` ON `articles_tags`.`article_id` = `articles`.`id` WHERE `articles_tags`.`tag_id` IN(10,11) GROUP BY `articles`.`id` HAVING COUNT(tag_id) = 2 How can I modify this to still include articles with both tag ids 10 and 11 but that also do not have tags ids 12 or 13 So for example: article 1 (has tags 9, 10) - excluded article 2 (has tags 9,10,11) - included article 3 (has tags 9,10,11,12) - excluded article 4 (has tags 9,10,11,13) - excluded
How do I drop a primary key that has a foreign key in another table?   (140 Views)
I'm trying to drop a table I don't need but, it's primary key is a foreign key in another table. I tried to drop the foreign key but I get an error. I tried to drop the primary key but it won't let me. I'm using InnoDB. Can someone please help me with this Thank you.
How can I allow for duplicate ids in the primary key field   (165 Views)
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.
Resetting primary key to 0 in phpmyadmin   (159 Views)
Ok I have two tables and two primary keys one on each table. My hosting saddly doesnt suport InnoDB tables so the only way i have to match records between tables is the primary keys on each table. The problem I have is resetting them both back to 0 is there any way to do this in phpmyadmin
Page 2 - How to know which one is primary column   (161 Views)
'Show' does not support 'where' clause. Run this query SHOW KEYS FROM tbl_name WHERE Key_name = 'PRIMARY' you will get MySql error. '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 'WHERE Key_name = 'PRIMARY'' at line 1 '
Merging two data sets that have primary keys   (170 Views)
I am trying to merge two tables into one. These tables are the exact same structure and they both have primary keys. The numbers in these primary keys (INT) do conflict with each other. I am want to do a mysqldump on each table that can be used to merge them into one. I don't care what the primary key values are equal to after they are merged together. It can reindex the whole data set if it wants to I just don't want to get an error when I try to import the second sql file. INSERT IGNORE gets me around the error but it doesn't move all of the data in. Is there a way to either drop the primary key field out of the mysqldump or is there a way to get the server to just grab the next AUTO_INCREMENT value when there is a conflict
Can a foreign key be a primary key too?   (143 Views)
I am trying to do this but i'm getting this error: #1005 - Can't create table '.\startbron\m_shoutbox.frm' (errno: 150) This is the code, the parent and child table: Code: CREATE TABLE m_categorien ( cat_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, pagina VARCHAR(64) NOT NULL, ... PRIMARY KEY(cat_id), INDEX (pagina), FOREIGN KEY (pagina) REFERENCES m_paginas(pagina) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE = INNODB; CREATE TABLE m_link_groep ( cat_id INTEGER UNSIGNED NOT NULL, ... PRIMARY KEY(cat_id), FOREIGN KEY (cat_id) REFERENCES categorien(cat_id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE = INNODB; The primary key from the parent table allready uniquely indentifies the records so i have no reason to create a seperate primary key. As you can see; the parent table is also a child of another table.
Help: select distinct but include multiple columns?   (126 Views)
there i was hoping that some of you expert developers could help me out.. I have a table with these fields: id - account - ip - data what I want to do is select only unique ip, but also include the 'data' field in the result so that I can print the unique ip along with the associated data .. is this possible i tried 'select distinct ip,data from table' but that returns duplicate ips.. is there a way to do this
Mysql primary key problems   (146 Views)
in mysql can you have th primary key of one table appear twice in another table.
Is it possible to re-arrange primary key sequence?   (320 Views)
hi i am facing a problem with my application when someone deletes a record and hence its key disappears breaking the sequence e.g 1 - entry 2 - entry (3 - deleted no mere here) 4 - entry 5 - entry When key is not there my AJAX application fails to run properly due to not finding next id. Is there any method to re-arrange primary key values to a proper sequence when one or more numbers are missing so that i will write a query and whenever user deletes an item i will re-arrange the primary key sequence automatically....
exporting to mysql from access with primary keys intact?   (153 Views)
Is there any way to export my tables from access and keep the primary keys intact At the moment I use the DSN driver to link the tables and feed them into mysql but I lose the primary keys.
Composite primary key schema   (494 Views)
for a photo contest I've set up this schema PHP Code: CREATETABLEIFNOTEXISTSpc_contest( idINTUNSIGNEDNOTNULLauto_increment, titleVARCHAR(255)NOTNULL, slugVARCHAR(255)NOTNULL, descriptionTEXTDEFAULTNULL, valid_fromDATENOTNULL, valid_toDATENOTNULL, create_dateDATENOTNULL, max_n_picturesTINYINTDEFAULT1COMMENT'maxnumberofpicturesallowedinthecontest', login_typeTINYINTDEFAULT1COMMENT'1onlyfblogin,2fblogin+registration,3onlyregistration', picture_moderationTINYINT(1)DEFAULT0COMMENT'1allthepicturesshouldbemoderatebeforepublishing', statusTINYINT(1)DEFAULT0COMMENT'0unactive1active', headerVARCHAR(255)DEFAULTNULL, PRIMARYKEY (id), UNIQUEKEYunique_pc_contest_title(title), UNIQUEKEYunique_pc_contest_slug(slug) )ENGINE=InnoDB DEFAULTCHARSET=utf8; CREATETABLEIFNOTEXISTSpc_contestant( contest_idINTUNSIGNEDNOTNULL, user_fb_idBIGINTUNSIGNEDDEFAULTNULL, firstnameVARCHAR(255)NOTNULL, surnameVARCHAR(255)NOTNULL, emailVARCHAR(255)NOTNULL, join_datetimeDATETIMENOTNULL, join_ipVARCHAR(15)NOTNULL, hashVARCHAR(40)NOTNULL, PRIMARYKEY (contest_id,email), KEYpc_contestant_hash(hash) )ENGINE=InnoDB DEFAULTCHARSET=utf8; CREATETABLEIFNOTEXISTSpc_album( idINTUNSIGNEDNOTNULLAUTO_INCREMENT, contest_idINTUNSIGNEDNOTNULL, contestant_emailVARCHAR(255)NOTNULL, pictureVARCHAR(255)NOTNULL, visibleTINYINT(1)DEFAULT0COMMENT'1yes0no', PRIMARYKEY(`id`) )ENGINE=InnoDBDEFAULTCHARSET=utf8; I'm wondering if it's better using PRIMARY KEY (contest_id,email) in pc_contestant or it's better to set like PHP Code: CREATETABLEIFNOTEXISTSpc_contest( idINTUNSIGNEDNOTNULLauto_increment, titleVARCHAR(255)NOTNULL, slugVARCHAR(255)NOTNULL, descriptionTEXTDEFAULTNULL, valid_fromDATENOTNULL, valid_toDATENOTNULL, create_dateDATENOTNULL, max_n_picturesTINYINTDEFAULT1COMMENT'maxnumberofpicturesallowedinthecontest', login_typeTINYINTDEFAULT1COMMENT'1onlyfblogin,2fblogin+registration,3onlyregistration', picture_moderationTINYINT(1)DEFAULT0COMMENT'1allthepicturesshouldbemoderatebeforepublishing', statusTINYINT(1)DEFAULT0COMMENT'0unactive1active', headerVARCHAR(255)DEFAULTNULL, PRIMARYKEY (id), UNIQUEKEYunique_pc_contest_title(title), UNIQUEKEYunique_pc_contest_slug(slug) )ENGINE=InnoDB DEFAULTCHARSET=utf8; CREATETABLEIFNOTEXISTSpc_contestant( idINTUNSIGNEDNOTNULLAUTO_INCREMENT, contest_idINTUNSIGNEDNOTNULL, user_fb_idBIGINTUNSIGNEDDEFAULTNULL, firstnameVARCHAR(255)NOTNULL, surnameVARCHAR(255)NOTNULL, emailVARCHAR(255)NOTNULL, join_datetimeDATETIMENOTNULL, join_ipVARCHAR(15)NOTNULL, hashVARCHAR(40)NOTNULL, PRIMARYKEY (id), KEYpc_contestant_hash(hash) )ENGINE=InnoDB DEFAULTCHARSET=utf8; CREATETABLEIFNOTEXISTSpc_album( idINTUNSIGNEDNOTNULLAUTO_INCREMENT, contestant_idINTUNSIGNEDNOTNULL, pictureVARCHAR(255)NOTNULL, visibleTINYINT(1)DEFAULT0COMMENT'1yes0no', PRIMARYKEY(`id`) )ENGINE=InnoDBDEFAULTCHARSET=utf8; pc_album is 1:n
Very slow joins table (EXPLAIN included)   (144 Views)
I'm very confused about my dirty quires. So, I don't know what's wrong with my JOINS why they are so slow I have 2 table with large data: forum_messages :: 267,808+ row totalforum_users :: 332,042+ total Here is my Query: PHP Code: SELECTusers.type_id, users.full_name, users.user_id, msgs.message_id, msgs.message_body, msgs.msg_is_read, msgs.user_send_id, UNIX_TIMESTAMP(msgs.sent_date)ASsnt_date FROM `forum_messages`ASmsgs INNERJOIN`forum_users`ASusersONmsgs.user_send_id=users.user_id WHERE msgs.user_receive_id=1 ANDmsgs.msg_del_receive=1 ANDmsgs.is_shows=1 ORDERBYmsgs.sent_dateDESCLIMIT10 forum_messages Indexes: 1.jpg forum_messages EXPLAIN: 2.jpg forum_users Indexes: 3.jpg Is there any idea please
Selecting one or more records by primary index   (361 Views)
I'm trying to create a query that will extract one or more records from a database table. I've tried: Code: SELECT * FROM 'accommodations' WHERE 'accn_id' = 2007 OR 'accn_id' = 2024 which gives an empty result -- that's to say there's no error message, but no rows are returned, even though there ARE records with those accn_id values. I've also tried: Code: SELECT * FROM accommodations WHERE 'accn_id' = 2007 and that does give an error message, so presumably I've not got the syntax quite right I can't yet see what I've done wrong, but I hope I'm close.
Use of primary key in unique indices   (370 Views)
Consider a competition_entry table and its indices: Code: Keyname | Type | Field ----------+---------+--------------- PRIMARY | PRIMARY | competition_id EMAIL | UNIQUE | competition_id | | email DUPLICATE | UNIQUE | competition_id | | surname | | addr_1 | | post_code The following warnings are given: PRIMARY and INDEX keys should not both be set for column `competition_id` More than one UNIQUE key was created for column `competition_id` What Im trying to do is make sure that an email address is only entered once per competition and that combination of surname, first line of address and post code is also unique per competition. My question is: why does MySQL (phpMyAdmin actually, I created the table in MySQL Administrator and it didnt say anything) say that what Im doing is bad Or, more importantly: why is it bad, if it is
setup unique primary hash key?   (372 Views)
im assuming there must be a way to do this, im just unaware of it. im getting tired of setting up my tables as id PRIMARY KEY AUTO INCREMENT. this starts at 1 and then just starts counting up. it just seems like bad form to me considering its giving anyway information about a particular record that isnt necessarily true. is there a way to setup mysql to create a encrypted key each time a record is inserted