SEARCH YOUR SOLUTION HERE  

Primary Key Name



I have a question about how to name the primary row id key in my MySQL tables. Is it good practice to use the same naming convention for the primary row id field in every table e.g. "row_id", or should i name them all uniquely e.g. in the users table call it "users_row_id" and in the products table call it "products_row_id"

I would assume that using the same name in each table is the best way to go but i'm just worried that when i do a join query that it will be a bit more awkward to distinguish between each tables row_id field.

Posted On: Monday 31st of December 2012 01:46:17 AM Total Views:  1984
View Complete with Replies




Related Messages:

Composite primary key schema   (127 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
On duplicate key Update did not work   (112 Views)
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
Set field to default null drop foreign key   (113 Views)
in a table I've a field like `payment_id` CHAR(5) NOT NULL, with fk CONSTRAINT `order_fk_payment` FOREIGN KEY (`payment_id`) REFERENCES `payment_method` (`payment_id`) ON DELETE CASCADE ON UPDATE CASCADE now I need to do on client request (there are customer with not payment_id) ALTER TABLE `order` MODIFY `payment_id` CHAR(5) default NULL, so I'm wondering if I should get rid of the fk or not like ALTER TABLE `order` DROP FOREIGN KEY `order_fk_payment`;
MySQL: Fulltext v/s LIKE %keyword%   (114 Views)
Hi I was trying to learn what a Fulltext search is and was wondering: 1) How is it different from the LIKE '%keyword%' operator 2) Why should I use a Fulltext search and not the LIKE operator or when to use what Can someone please explain this to me Many
Selecting one or more records by primary index   (109 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   (115 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
How to combine effective keyword search with structured data criteria   (124 Views)
I'm curious what solutions people are employing to provide effective solutions for searching text and structured data in combination. For example, I have a collection of documents stored in a database and I need to make them searchable using both keywords and categories. The categories are stored using a typical, normalized database structure and are exposed to the user as checkboxes and select lists on the search form. In this case, for a document to match the search, it has to match the keyword phrase and also be associated with all of the categories selected by the user. The user can omit either the keyword phrase or categories and just search using one or the other. I have the category part handled. But how to get effective text search to go with that is trickier. The application is running on PHP 4 & MySQL 4.1. I'm currently using MySQL's full-text search features, but they leave quite a bit to be deed -- e.g. there's no stemming, it doesn't ignore punctuation, etc. I'm interested in finding out how other developers have effectively handled this kind of situation. I'm considering ditching, or at least supplementing, MySQL's full-text search in favor of some more effective keyword search solution. I've been taking a look at Google Site Search for example. I think that would probably provide a more effective text search solution than using MySQL full-text, but I don't know that there'd be a way to integrate that with the structured data that needs to be part of the criteria for searches in my app. Any suggestions
setup unique primary hash key?   (110 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
Joining tables having unmatching keys   (128 Views)
, I have two tables (in IBM DB2) to join. I will use inner join as Code SQL: SELECT * FROM T1 X INNER JOIN T2 Y ON X.C1=Y.C1 WHERE X.C2>12; Problem is key of the first table is the combination of three fields of another. Code PHP: $left_table_id = $right_table_id1 . $right_table_id2 . $right_table_id3 I am not sore how to do this in query. It has to be something like that Code SQL: SELECT * FROM T1 X INNER JOIN T2 Y ON X.C1=Y.D1 . Y.D2 . Y.D3 WHERE X.C2>12; Any opinion (For your interest, I am not the one who design this )
Include the primary key in the query   (110 Views)
, 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
Do I really need foreign keys   (118 Views)
I am currently designing a database that is to be used for ordering products from a site. member, order-states, order, order-lines, product, review I am considering using foreign keys between the tables. I am not sure if this would be the best way to do it or not. Q1: If I use fk which tables need to be innodb, the ones containing the fk, the ones the fk are from or all of them. Q2: If I were to delete a member do all related table entries get removed or does that only happen with the cascade keyword. This is important as you would still need a record of the transactions in the orders table. Q3: What benefits are there to fk using innodb over myisam and simply storing the value from one table as a field in the other. Hope these questions aren't to trivial and feel free to point me to tutorials.
Help with structure - primary key - inner join (i think)?   (117 Views)
version 4.1 / using php admin Hi all I basically have a no. tables storing photographs and need to understand how I can get this structure right I think a inner join is the right way What I want is to be able to select the 'date' and 'photo_set' and display all the photos What I have so far: gallery_information - date / date, primary key - title / varchar 90 photo_information - photo_id / primary key - photo_set gallery_photo_relation - date - photo_set
Database Design : Compound key vs surrogate key   (105 Views)
I've got two questions. The first, which is better a compound key or surrogate key I was under the impression that a compound key compossed of a user entered string and foreign key id will be slower and can cause more problems when compared to a surrogate key. Am I mistaken Secondly, there was mention in another thread that it would be wise to use the abreviated value of states in an address table as opposed to placing the states in a look up table and joining the two tables. I understand that having to join two tables will result in slower query times but won't the size of the database be larger when compared to having two seperate tables and doesn't that break a rule of normalization
how to handle duplicate keys in insert   (103 Views)
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
key1 without binary and key2 with binary   (116 Views)
I have two search keyes, key1 and key2. The following is where clause. Code: where say like '%#key1#%' and say like binary '%#key2#%' I have to use binary for key2 like the above. Now I like to make the following where clause. Code: say like '%#key1#%#key2#%' The where clause above will retrieve all records which has #key1# and #key2# in that order. My problem is in the use of binary, i.e I have to use binary for key2. I can make it like the following for using binary. Code: say like binary '%#key1#%#key2#%' But the code above makes not only key2 but also key1 bianry. Key1 doesn't need bianry but key2 needs bianry. How can I make the where clause key1 without binary and key2 with binary The following would-be code doesn't work correctly, but it'll show what I want. Code: woudl-be code say like '%#key1(non-binary)#%#key2(binary)#%'
Calling links from a database   (105 Views)
Can anyone tell me how I links should be inputed into a MySQL database Also, how do I call the link in a web page I do not want the URL displayed, but want something like the example below done in HTML: Click here!
SQL Dates Query   (98 Views)
, I'm new here and new to SQL. I have a phpbb_users table in my phpbb forums database. The table has a column user_lastvisit which uses a unix timestamp. Does anyone know a query that will return all rows where user_lastvisit is > 20 days ago
Connecting to external MySQL DB from a web server not running MySQL   (104 Views)
While I've been working with MySQL for years, this is the first time I've run across this very newbie-esq issue. Due to a client demand, I must host their website files (PHP) on a IIS server that is not running MySQL (instead, they are running MSSQL). However, I have developed the site using a MySQL database which is located on an external host (Rackspace Cloud). Obviously, my mysql_connect function is now bombing because MySQL is not running on localhost. Question: Is it even possible to hit an external MySQL database if localhost is not running MySQL Apologies for the rookie question, and many thanks in advance. * To clarify, I know how to connect to a remote MySQL server, but it is the fact that my IIS web server is not running ANY form of MySQL (neither server nor client) that is giving me trouble. Put another way, phpinfo() does not return anything about MySQL. Additionally, I do not have access to that IIS web server other than FTP *
INNER JOIN w/ some data not available   (102 Views)
I have three major pieces I'm trying to accomplish, if possible, with a single mySQL query dealing w/ joining data from multiple tables. Here's the first piece I'm having a problem with. I'm simplifying to highlight just what I need. Goal ********* Joining two tables, even if one in particular happens to not have associated data. Specifically, I have a table called events and a table called practitioners. Not all events will have practitioners, but for those that do, I want to display their practitioners. For those that don't, the event should still be shown, although with a blank practitioner. Table Structure/Content ******** events ** ID, date, practitionerID 1, 20091106, 0 2, 20091107, 8 practitioners ** ID, name 8, Jeremy Deed Outcome ********** 20091106, '' 20091107, 'Jeremy' Current Code ********* PHP Code: $query="SELECTevents.ID,events.date,events.practitionerID,practionerers.nameFROMeventsJOINpractitionersONpractitioners.ID=events.practitionerIDORDERBYevents.date"; Result ********* 20091107, 'Jeremy' Problem ********* Because there is no matching practitionerID to the first event row (with a practitioner ID of 0), it fails to produce that row of data. I want it to return the data and just leave practitioner.lastName blank.
NULL field   (118 Views)
Is there a way to run a query and check for a NULL field I have a DB with a NULL field right now. If it happens to be NULL, the queries will completely skip it. For example, if I have a table with a name field and it is set to NULL and I run Code: SELECT name FROM table WHERE name != 'bob' Any and all NULL fields are skipped as if they dont exist. I even tried Code: SELECT name FROM table WHERE name = NULL SELECT name FROM table WHERE name = 'NULL' And I get nothing. What is the proper way, if any
Linked server   (113 Views)
is there a way to create linked server between 2 mysql servers like in SQL SERVER
INSERT syntax issue   (100 Views)
Hi thanks in advance to all who respond to this. I have 1 database with 5 tables. The common field in all tables in the Student_Number field. PsWittStudents is a large table with all of the student names and addressses and such. PsWittMothers contains their mothers name and numbers. The PsWittStudents table has columns named Mother-First Mother-Last but there is no information there. It is all in the PsWittMothers tables. To get the data from the PsWittMothers table into the PsWittStudents tables I have been trying to use INSERT and have constantly come up short. The code I have been trying to use is: INSERT INTO `PsWittMothers` (`Mother-Last`,`Mother-First`,`MotherDayPhone`,`Mother_home_phone`) SELECT `PsWittMother`.`Mother-last`,`PsWittMother`.`Mother-first`,`PsWittMother`.`Mother-work`,`PsWittMother`.`Mother-home` FROM `PsWittMother` WHERE `PsWittStudents`.`Student_Number`=`PsWittMother`.`Student_Number`; I have played with the syntax on the mysql handbook site and I cannot see what I'm doing wrong. Any advice on how to insert that data would be most appreciated, as I am kind of under the gun at work about this
I can't restore my database...   (115 Views)
Hi Guys i have been workin' with MySql recently. I have learned how to backup a database and it worked fine: C:\Program Files\MySQL\MySQL Server 5.0\bin>mysqldump -uroot -ptemenos test > backupfile.sql After that i purposely dropped the test database. But when i tried restoring it, it gave me the following error: C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -ptemenos test < backupfile.sql ERROR 1049 (42000): Unknown database 'test' Can't somebody help me out
Php mysql joins   (118 Views)
hi, am looking for some guidance with my SQL... OK so I am trying to grab all topics from TOPIC which have a certain parent id (WHERE TOPIC.pID = 3 or whatever) but also match each returned row to the USER table to get the users name for the last post in that topic (match TOPIC.lastUID with USER.ID) problem is its only returning one row Code: "SELECT TOPIC.ID, TOPIC.pID, TOPIC.lastUID, TOPIC.TITLE FROM TOPIC INNER JOIN USER ON TOPIC.lastUID = USER.ID WHERE CP.pID = '$FORUM_id'"; can I do this all in one sql statement
Tracking changes like a wiki (not storing redundant data)   (105 Views)
Here's an interesting database problem that I thought some of you might know how to solve... If you do, I'd appreciate the help! I'm trying to set a system up where people can edit pages so that what they see is always the most recent version of a piece of text, but actually the database stores all previous versions as well, so that one can retrieve a copy of the text after each individual edit is submitted. Kind of like Wikipedia, although I don't need the ability to actually roll things back and forward, I just need the changes to be identifiable. Now, obviously I could do this by just storing a new row everytime the text is edited. But this could take up a lot more space than I'd like. Can anybody think of a way of storing only the changes, and not the data that stays constant between two edits PS: I mean besides relying on the binary logs .
Page 2 - Query max and min with two situation, same and different dat   (109 Views)
if php code is the solution can you give me an example I really don't know where to start.. Thank you so much.. , I told him my situation...but he told me that i can do it:(
Auto_increment problem while export-import   (120 Views)
, I have a problem that I googled for but couldn't get any resolution. I have 4 databases with 2 columns and a single central database with the same 2 columns. I want to put all the data from all the 4 databases into the central database. In my tables I have auto_increment field (id). When I export the data the id field too gets exported with its values. When I try to import it in the central DB then it gives problems. My central DB's auto_increment id field clashes with the id field of my other database tables. So, how can I perform this operation I thought maybe there is a way to avoid auto_increment field while exporting but there is none. Please help me since this is very urgent.
Database design help for Photography website   (112 Views)
1. gallery(gallery_id*,gallery_title,gallery_description,gallery_thumbnail_id) picture(picture_id*,picture_title,picture_description,picture_image_path) gallery_picture(gallery_id*,picture_id*) 2. format(format_id*,format) class(class_id*,class_name (e.g. 'Panoramics','Square','Portrait','Custom1'), class_description) format_class(class_id*,format_id*) * = (component of) PRIMARY KEY Now you can relate images and formats to price or price tier, either as additions or multiplications of that price. You might want some tables like this: image_price(image_id,price_id) format_price(format_id,price_id) , **Not to reply to my own post, but I wanted someone to know I made another comment.**
JOIN problem   (105 Views)
It's probably something stupid - but I've done more complicated joins than this in the past and I've never seen this problem. The error is: 1054: Unknown column 'mp.product_id' in 'on clause' Code: SELECT mp.product_id, mp.title, mp.description, mp.price, mp.image, mp.trade_id, m.merchant_id, m.name, p.title AS platform, ag.merchant_product_id FROM merchant_products AS mp, platforms AS p, merchant AS m LEFT JOIN assigned_games AS ag ON (mp.product_id = ag.merchant_product_id) WHERE mp.merchant_id = m.merchant_id AND mp.platform_id = p.id AND mp.platform_id = 9 AND mp.title LIKE '%Orange Box%' ORDER BY m.merchant_id ASC, mp.title ASC
Sql Help   (118 Views)
Hi Everyone, I have a simple question regarding outer join. Please see the attached word file. It has screen shots of the query I am running. My first query shows the result where i have M.ReservationID = MA.MeetingID and it counts NoofRSVP (# of times the query runs). I have to modify first query in such a way that it returns records from eCDReservations table even if there is no matching MeetingID in MeetingAttendees table (means Null, see the result of 2nd query in attached file). So in my result for that case NoofRSVP column should show either Null or 0.
How to do UNPIVOT with dynamic column names within a FUNCTION   (120 Views)
I have a problem and I hope you can help me. I need to use UNPIVOT inside a user defined function. The problem is, my column list should be dynamic. I tried to use EXEC (@SQL) to build my SELECT query and I found out it is impossible to use EXEC inside function so I don't know how I can make my query. I appreciate for you helps.
Search needing a tweak   (107 Views)
Hi I have just completed a search function that seams to be work well execpt for one or two small tweaks.. My search queries my db and checks for matching product names or similar as i thought. When I search for "flasks" it returns nothing but when I search for "flask" it returns products. I am using where like %search term% in my sql which I thought would return anything similar to that but doesnt This is my sql Code: SELECT prodID,pname,model,company,template,image,price, offerprice, parcat,offer FROM misc INNER JOIN suppliers ON supplier = suppliers.sid WHERE pname LIKE '%"& strPname &"%' AND supplier IN ("& strSupplier &") OR model LIKE '%"& strPname &"%' AND supplier IN ("& strSupplier &") Anyone got any ideas
Iterations in stored procedures!!!   (115 Views)
Can somebody give me an example of a loop in sql stored procedure. say this is what i want to do, for i = 1 to 10 call procedure1 i next Is there anything equivalent to this kind of iterations in stored procedures.
how to conver access into sql?   (116 Views)
to I'm new and i'm happy that i have found this. I had a question which might have been asked many times: how do I convert access codes into sql I have this data base in access which needs to be in sql . I wanted to know if i have to write codes or something else. I have office xp and sql server 2000 personal edition. hope to get an answer very soon.