Re: Designing Table for Both Global and Local Indices
Hi, Try to make a table for each company with only one integer field than insert a record and use the id to populate the other table. You can also delete periodically records from there companies tables. Santino At 21:48 -0500 23-11-2007, David T. Ashley wrote: Hi, I am developing a large database where the web interface may be shared among many companies, but the data will generally not be shared. For the purposes of example, let's call it a bug tracking system such as Bugzilla. Each company has their own private software bugs. Many companies may enter bugs that become part of the bugs table. However, depending on how a user is logged in (i.e. is part of which company) only that company's bugs will be queried or visible. If I just index the bugs with an autoincrement long integer and an integer representing the company, it will work fine. The disadvantage, however, is that people generally expect that after they enter Bug #567, Bug #568 comes next (which wouldn't be the case if the index of the bugs table is shared among all companies). What is the most efficient way to tackle this problem so that each company gets their own virtual private space of bug numbers but only one bugs table is used? The most obvious way to handle it is: a)Lock the table. b)Find the maximum bugnumber where company=X. c)Insert the new bug with company=X and bugnumber=max+1. d)Unlock the table. However, is there a better way to think about this? Thanks, Dave. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unusual sort
On 24-nov-2007, at 0:29, Jim wrote: I have a table containing web site host names, most of them having both a name.com and www.name.com version, that I'd like sorted in the following manner: axxx.com www.axxx.com bxxx.com www.bxxx.com wxxx.com www.wxxx.com zxxx.com www.zxxx.com Any way to do this? Sure, just create a extra sort column, and fill it with the original names after you stripped of the parts before the second dot, counting from the right. I'd first count the number of dots to decide wich fields have to be altered. Then, based on the number of dots, calculate the position of the one but last dot and do a substring on the field. Then sort based on that new column. HTH Peter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: IMAGES/PICTURES-MYSQL
Hi! I have seen in this page http://www.blobstreaming.org/ but i think that it´s quite difficult. I want to put only the url of the image, and as you said the pictures o images might be in the server folder, but what is the folder? Thanks. -- View this message in context: http://www.nabble.com/IMAGES-PICTURES-MYSQL-tf4845682.html#a13923823 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Unusual sort
Hi Jim it seems that you cannot create an index with a function soyou will need to establish a separate 12 character column which has all of the URL entries insertedalphabetically in ascending order (fully padded with www. prefix)backup your DBALTER TABLE table ADD TwelveCharacterURL CHAR(12), ADD FOREIGN KEY 12CharacterIndex (TwelveCharacterURL); UPDATE TABLE TABLE set TwelveCharacterURL=(LPAD(OldURLColumn,12,'www.')); Anyone else? Martin __Disclaimer and confidentiality noteEverything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Unusual sort Date: Fri, 23 Nov 2007 16:29:50 -0700 I have a table containing web site host names, most of them having both a name.com and www.name.com version, that I'd like sorted in the following manner: axxx.com www.axxx.com bxxx.com www.bxxx.com wxxx.com www.wxxx.com zxxx.com www.zxxx.com Any way to do this?-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Your smile counts. The more smiles you share, the more we donate. Join in. www.windowslive.com/smile?ocid=TXT_TAGLM_Wave2_oprsmilewlhmtagline
RE: Unusual sort
Hi, One thought, it might a good idea to make a trigger/procedure that inserts the seprate index field, so you can forget about it from here on. -Original Message- From: Martin Gainty [mailto:[EMAIL PROTECTED] Sent: Saturday, November 24, 2007 11:18 AM To: Jim; mysql@lists.mysql.com Subject: RE: Unusual sort Hi Jim it seems that you cannot create an index with a function soyou will need to establish a separate 12 character column which has all of the URL entries insertedalphabetically in ascending order (fully padded with www. prefix)backup your DBALTER TABLE table ADD TwelveCharacterURL CHAR(12), ADD FOREIGN KEY 12CharacterIndex (TwelveCharacterURL); UPDATE TABLE TABLE set TwelveCharacterURL=(LPAD(OldURLColumn,12,'www.')); Anyone else? Martin __Disclaimer and confidentiality noteEverything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Unusual sort Date: Fri, 23 Nov 2007 16:29:50 -0700 I have a table containing web site host names, most of them having both a name.com and www.name.com version, that I'd like sorted in the following manner: axxx.com www.axxx.com bxxx.com www.bxxx.com wxxx.com www.wxxx.com zxxx.com www.zxxx.com Any way to do this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Your smile counts. The more smiles you share, the more we donate. Join in. www.windowslive.com/smile?ocid=TXT_TAGLM_Wave2_oprsmilewlhmtagline -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Default result
Is there anything I am missing that will allow me to return a default row if the sought after row is not found? For example : Select * from table1 where column1=1234 If 1234 is not found, row1 would be returned instead. If 1234 is found then that is the row returned. Thanks Steffan --- T E L 6 0 2 . 7 9 3 . 0 0 1 4 | F A X 6 0 2 . 9 7 1 . 1 6 9 4 Steffan A. Cline [EMAIL PROTECTED] Phoenix, Az http://www.ExecuChoice.net USA AIM : SteffanC ICQ : 57234309 YAHOO : Steffan_Cline MSN : [EMAIL PROTECTED] GOOGLE: Steffan.Cline Lasso Partner Alliance Member --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Default result
In the last episode (Nov 24), Steffan A. Cline said: Is there anything I am missing that will allow me to return a default row if the sought after row is not found? For example : Select * from table1 where column1=1234 If 1234 is not found, row1 would be returned instead. If 1234 is found then that is the row returned. SELECT * FROM ( SELECT * FROM table1 WHERE column1=1234 UNION SELECT * FROM table1 WHERE column1=1 /* or whatever uniquely identifies row1 */ ) t LIMIT 1; -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Incrementing a Private Integer Space
I have a table with two integer fields (call them p and q). When I insert a record with a known p, I want to choose q to be one larger than the largest q with that p. What is the best and most efficient way to do this? For example, let's say the table contains (p,q): 1,1 1,2 1,3 2,1 2,2 2,3 2.4 2,5 3,1 3,2 If I insert a new record with p=2, I would want to choose q to be 6. But if I insert a record with p=3, I would want to choose q to be 3. Is there any alternative to locking the table, querying for max q with the desired p, then inserting? Thanks.
Re: Incrementing a Private Integer Space
David T. Ashley wrote: I have a table with two integer fields (call them p and q). When I insert a record with a known p, I want to choose q to be one larger than the largest q with that p. What is the best and most efficient way to do this? For example, let's say the table contains (p,q): 1,1 1,2 1,3 2,1 2,2 2,3 2.4 2,5 3,1 3,2 If I insert a new record with p=2, I would want to choose q to be 6. But if I insert a record with p=3, I would want to choose q to be 3. Is there any alternative to locking the table, querying for max q with the desired p, then inserting? Yes CREATE TABLE `t` ( `p` int(10) unsigned NOT NULL default '0', `q` int(10) unsigned NOT NULL auto_increment, PRIMARY KEY (`p`,`q`) ) ; -- Chris W KE5GIX Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm; Ham Radio Repeater Database. http://hrrdb.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
