Users and Groups
Has anyone seen a plugin for MySQL that will allow you to set up users and groups for access where you can have a user who can login, create db etc but ONLY see the stuff that belongs to them? I'm speaking of a shared server where multiple people can use the same instance but be fully separated just like a file share. Thanks, Steffan
Returning years of data by month
Looking for suggestions on how to best pull some data. I need to do some calcs but pull the data by year and month to make a table like such. 201220132014 Jan $243$567$890 Feb $123$456$908 Mar Apr May I can get the data to be ordered by year, month but as you know that is not easily conducive to putting it into an HTML table without some middle ware storing and iterating through found sets. Suggestions? 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. Clinestef...@execuchoice.net http://www.ExecuChoice.net Phoenix, Arizona USA --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Random Code Stored Procedure
Rather than trying to reinvent the wheel, I was wondering if anyone might have a stored procedure already for what I want to do. I want to start with 4 characters using any unique combo and when all unique matches are used, it will move to 5 characters and so on. For example in any random order: AAAB ... A ... Z AA ... ZZ The codes would be validated for a dupe against an existing column which this will seed called code. Suggestions? Thanks Steffan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Select with counts of matching rows from another table...
I have 3 tables Table ³groups² groupid, groupname Table ³agmap² groupid, articleid Table ³articles² articleid, articletopic, articlebody The relation is that articles can have groups attached to it via the map table. I can insert this and work it out fine. The issue is when I want to pull the groups into a list of checkboxes and check them accordingly upon edit. So, this is what I have as a basis to work on assuming I am polling article #36. Select *, if(b.articleid=36,1,0) as checked from groups g Left join agmap a on g.groupid=a.groupid Left join articles b on a.articleid=b.articleid Order by g.groupname This will spit out the groups with all the articles mapped to the groups. What I need is to get back a list of groups with some indicator if there is a match to a particular article id. The results should look something like this: groupname articleid checked Group1 null 0 Group2 36 1 Group3 36 1 Group4 null 0 I tried adding ³group by groupname² which will give me back the 4 groups which is fine, but the checked column is wrong because it always grabs a lower numbered article id that is matched to the group although the ³checked² column will be right, in this case 0. This is a rough example of what it looks like without ³group by² groupname articleid checked Group1 26 0 Group1 14 0 Group2 1 0 Group2 3 0 Group2 36 1 Group3 36 1 Group4 null 0 I know there has to be a way to make it work right but its just not there Another way of explaining it is, I am trying to get a list of the groups, in order, and get a 1 or 0 in the checked column if a specific article is linked to the group (row) or not. 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 stef...@execuchoice.net Phoenix, Az http://www.ExecuChoice.net USA AIM: SteffanC Skype : steffancline GOOGLE : steffan.cl...@gmail.comMSN : stef...@hldns.com YAHOO : Steffan_Cline ICQ : 57234309 --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MySQL and Relevance
Anyone know of a way to do a query within MySQL kind of like a relevance but without the Full Text index? I am searching on country, region and city. Now, that's easy enough but what if I need to sort by full match down to partial and no matches but always get something like always a found set of 5 regardless? The country is searched as a 2 letter ANSI code and the region and city are searched as plain varchar fields. Any suggestions are welcome. 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 stef...@execuchoice.net Phoenix, Az http://www.ExecuChoice.net USA AIM : SteffanC ICQ : 57234309 YAHOO : Steffan_Cline MSN : stef...@hldns.com 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/mysql?unsub=arch...@jab.org
Foreign Keys
I am hoping for a little clarification/education here. My understanding of
how foreign keys work is very minimal.
In using MySQL for the last several years as a backend to web apps I have
always managed relationships amongst the tables with auto increment primary
keys myself.
I have a new project where I need to somehow automate some of that.
Traditionally I have just inserted the parent row into the parent table,
grab the PK and insert this into the child table with the data.
I am hoping that by using FK based relationships I can just do one massive
insert into the parent table and include all related columns and somehow
magically all field and relational keys fall into place.
Example:
Parent table - People
Columns - person_id, firstname, lastname
Child table - Homes
Columns - home_id, person_id, address
Then I could do something like:
insert into people (firstname, lastname, address) values ('xxx','xxx',xxx');
And hopefully due to the FK relationship it would match the proper field and
insert the data into the matching table and auto populate the person_id in
the homes table with the corresponding parent row's PK (person_id)
Am I totally off base or is something like this possible?
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]
Selecting virtual rows
I am working on a query where I need to get a certain number of rows based on a setting BUT if the number limited does not exist then return nulls instead. SELECT ca.filename,ca.attributeid FROM companyattributes ca WHERE ca.companyid=1234 LIMIT (SELECT at.LimitMovies FROM accounttypes AS at LEFT JOIN companies as c on c.accounttypeid=at.accounttypeid WHERE c.companyid=1234) This will return the first x rows BUT obviously if none exist I get 0 rows. If there are 2 in the limit statement I but if there are 0 found I need something like this attributeId | fileName NULLNULL NULLNULL OR if there is actually something found - attributeId | fileName 1 file1.jpg 2 file2.jpg 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]
Update but insert if not exist
I am trying to think of a trick way to handle something. I have been successful in using the multiple inserts in one row by using the ,(xx,xx,xx),(xx,xx,xx),(xx,xx,xx) in a values block. BUT Is it possible to insert if not there for example Update if anyone not found then insert new with same criteria as update Where region_id in (2,3,4,5,6) 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]
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: MySQL on Leopard
on 11/19/07 12:34 PM, Warren Young at [EMAIL PROTECTED] wrote: Steffan A. Cline wrote: Starting mysqld daemon with databases from /usr/local/mysql/var /usr/local/mysql/bin/mysqld_safe: line 426: 77090 Segmentation fault $NOHUP_NICENESS $ledir/$MYSQLD $defaults --basedir=$MY_BASEDIR_VERSION --datadir=$DATADIR $USER_OPTION --pid-file=$pid_file $err_log 21 STOPPING server from pid file /usr/local/mysql/var/Phat-G5.local.pid 071117 12:21:39 mysqld ended Is this some kind of shell error? No, a segfault (see end of second line) indicates either a bug in MySQL or one of the libraries it uses (unlikely) or an incompatibility between them. This being an OS less than 1 month out of the gate, I'd bet on the latter. For now, try installing the version from Fink instead. It'll have to rebuild itself from source, which will avoid many of the possible incompatibility problems. http://fink.sf.net/ I have always known fink to have this available but wanted to avoid it. I suppose I could just revert to using the init script but from what I have read it seems that the launchd is the better option. Yes, I saw the segfault but after looking into it, it seemed that it must be something I am missing. It would appear that for some reason those run time variables are not getting set. I built MySQL 5.045 from source and it will run fine, just not from launchd. Simply launching mysqld_safe works fine. Using the mysql.server start works too. So, other than using Fink, is there anything else that might seem obvious? I am willing to ride this one out and see what other options there are to try. 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: MySQL on Leopard
on 11/19/07 2:58 PM, Warren Young at [EMAIL PROTECTED] wrote: Steffan A. Cline wrote: I built MySQL 5.045 from source and it will run fine, just not from launchd. My previous post was made with the assumption that you were using the official binaries, and that they had not yet qualified them on Leopard. I suggested Fink because it's an easy way to ensure you build from source, not because I think Fink is in some essential way better. The fact that you did build from source invalidates my whole line of reasoning. I tried this because I heard that there were issues with the installers not working. I did it from source so that I could build a Leopard friendly 64bit PPC version. Was this machine upgraded to Leopard, or freshly installed? Fresh install. And if upgraded, did it have a previous version of MySQL on it before? If so, environment differences when running under launchd may be causing the linker to pick up old incompatible dynamic libraries. There was no instance of it but as I mentioned earlier that the only trouble I had was that the mapping of the mysql vs _mysql user was different. Try a 'make uninstall', then go back through /usr and /var by hand to ensure no traces remain, then reinstall. If MySQL's Makefiles don't support 'make uninstall', just do a by-hand removal. I'll hit this one up tomorrow if I don't find a solution later on. It seems surprising that no one else has discussed this issue so far. 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]
MySQL on Leopard
I was trying to get MySQL working from a fresh build. I used most of the tips from here: http://hivelogic.com/narrative/articles/installing-mysql-on-mac-os-x I have it installed and all but ran into an issue with the mysql user. Documentation for dscl is somewhat cryptic. When I was finally able to list the users I see that the usual uid and gid of 74 belongs to _mysql. What is the work around for this to change it to plain ole mysql so I can get this puppy working correctly again? 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: MySQL on Leopard
on 11/17/07 8:20 AM, William Allaire at [EMAIL PROTECTED] wrote: On Nov 17, 2007, at 4:06 AM, Steffan A. Cline wrote: Documentation for dscl is somewhat cryptic. When I was finally able to list the users I see that the usual uid and gid of 74 belongs to _mysql. What is the work around for this to change it to plain ole mysql so I can get this puppy working correctly again? Steffan, Why not just use _mysql where you normally use mysql as the uid/gid? It would probably be easier than changing plist files from _mysql back to mysql. Well, I figured it would be prudent to make it mimic the previous method to make it all work correctly as it did before. Well, now I have run into a few strange things. Mimicking the _mysql user works: Phat-G5:~ steffan$ sudo su -m _mysql sh-3.2$ /usr/local/mysql/bin/mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/var Using the tip for launchd does not seem to work. ?xml version=1.0 encoding=UTF-8? !DOCTYPE plist PUBLIC -//Apple//DTD PLIST 1.0//EN http://www.apple.com/DTDs/PropertyList-1.0.dtd; plist version=1.0 dict keyKeepAlive/key true/ keyLabel/key stringcom.mysql.mysqld/string keyProgram/key string/usr/local/mysql/bin/mysqld_safe/string keyRunAtLoad/key true/ keyUserName/key string_mysql/string keyWorkingDirectory/key string/usr/local/mysql/string /dict /plist I get no daemon running under ps -U _mysql when I use sudo launchctl load -w /Library/LaunchDaemons/com.mysql.mysqld.plist To launch it. Anything I am missing here? 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: MySQL on Leopard
on 11/17/07 12:05 PM, Steffan A. Cline at [EMAIL PROTECTED] wrote: on 11/17/07 8:20 AM, William Allaire at [EMAIL PROTECTED] wrote: On Nov 17, 2007, at 4:06 AM, Steffan A. Cline wrote: Documentation for dscl is somewhat cryptic. When I was finally able to list the users I see that the usual uid and gid of 74 belongs to _mysql. What is the work around for this to change it to plain ole mysql so I can get this puppy working correctly again? Steffan, Why not just use _mysql where you normally use mysql as the uid/gid? It would probably be easier than changing plist files from _mysql back to mysql. Well, I figured it would be prudent to make it mimic the previous method to make it all work correctly as it did before. Well, now I have run into a few strange things. Mimicking the _mysql user works: Phat-G5:~ steffan$ sudo su -m _mysql sh-3.2$ /usr/local/mysql/bin/mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/var Using the tip for launchd does not seem to work. ?xml version=1.0 encoding=UTF-8? !DOCTYPE plist PUBLIC -//Apple//DTD PLIST 1.0//EN http://www.apple.com/DTDs/PropertyList-1.0.dtd; plist version=1.0 dict keyKeepAlive/key true/ keyLabel/key stringcom.mysql.mysqld/string keyProgram/key string/usr/local/mysql/bin/mysqld_safe/string keyRunAtLoad/key true/ keyUserName/key string_mysql/string keyWorkingDirectory/key string/usr/local/mysql/string /dict /plist I get no daemon running under ps -U _mysql when I use sudo launchctl load -w /Library/LaunchDaemons/com.mysql.mysqld.plist To launch it. Anything I am missing here? Thanks Steffan Ok, I added a few keys to the launchd to see what the issue was. keyStandardErrorPath/key string/Library/Logs/mysqld.log/string keyStandardOutPath/key string/Library/Logs/mysqld.log/string I came across this: Starting mysqld daemon with databases from /usr/local/mysql/var /usr/local/mysql/bin/mysqld_safe: line 426: 77090 Segmentation fault $NOHUP_NICENESS $ledir/$MYSQLD $defaults --basedir=$MY_BASEDIR_VERSION --datadir=$DATADIR $USER_OPTION --pid-file=$pid_file $err_log 21 STOPPING server from pid file /usr/local/mysql/var/Phat-G5.local.pid 071117 12:21:39 mysqld ended Is this some kind of shell error? 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: Reply-to is to originator rather than to list
on 10/21/07 1:15 PM, Jochem van Dieten at [EMAIL PROTECTED] wrote: On 10/21/07, Rob Wultsch wrote: I was previously on a list where the reply-to was setup as it is on the mysql list, with the originator receiving a response rather than list. It ended up that that setting was the default, and had not been changed when the list was setup. Is there a good reason why the reply-to is setup as it is on this list? If you could explain why the answer in the FAQ doesn't satisfy you we might be of more assistance. I forget to change the destination address for most every email I write, I would guess I am not alone, and I do not think that this is good for the list. I believe it is excellent for the list as it raises the bar. Jochem I think the issue is that when people reply to the originator by mistake and not the list, the list does not get the subsequent correspondence that is definitely useful to the rest of us. If someone asks a question and someone else answers it I think we should all see the response without fail so we can all learn from the response. It also makes for better searchable lists and averts redundant questions. My $2USD ** OOPS, what do you know. I have to resend this because I forgot to reply all. :-P 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]
My first trigger in MySQL
I am new to triggers and am trying to figure this out. The goal I have is to set it up so that after a row in the users table is updated it will check to see if the affected row has a username in the username field and if not to update that row with a concatenation of firstname + + lastname. Does anyone have a good example of how to do this? 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: My first trigger in MySQL
on 10/7/07 12:26 PM, Steffan A. Cline at [EMAIL PROTECTED] wrote: I am new to triggers and am trying to figure this out. The goal I have is to set it up so that after a row in the users table is updated it will check to see if the affected row has a username in the username field and if not to update that row with a concatenation of firstname + + lastname. Does anyone have a good example of how to do this? Thanks Steffan I tried this : CREATE TRIGGER username_check AFTER UPDATE ON `users` FOR EACH ROW UPDATE users SET users.username = concat(users.firstname, ,users.lastname) where users.id=NEW.id; END ; But it throws the error: Can't update table 'users' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. Is this to stop infinite recursion? Is there anyway around this? In thinking on this if I do a BEFORE rather than AFTER is there anyway to test the query to see if it is updating the table and if there is no username being submitted to append it to the query so it will be set without causing such a recursion? 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]
New error to me...
I was trying to reduce a set of queries and ran into this: insert into forums (forum_reply_id, forum_dev_id, forum_subject, forum_message) values (0, 1, (select forum_subject from forums where forum_id=3 ), I figured this one needed a reply too.) yields: error: You can't specify target table from 'forums' for update in from clause I read somewhere online when they referenced 4.x that said you cannot do subqueries in an update. Is this true of 5.x ? Thanks Steffan --- T E L 6 0 2 . 5 7 9 . 4 2 3 0 | 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 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: New error to me...
Mogens , forum_id is a primary auto increment key therefore it's unlikely that there would be a dupe. I can see there being an error if there is a dupe but in this case there is not. Is it really necessary to do this: set @x = (select forum_subject from forums where forum_id=3 ); insert into forums (project_id, forum_reply_id, forum_dev_id, forum_subject, forum_message) values (42, 6, 1, @x, I figured this one needed a reply too.); ? Thanks Steffan --- T E L 6 0 2 . 5 7 9 . 4 2 3 0 | 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 Lasso Partner Alliance Member --- From: Mogens Melander [EMAIL PROTECTED] Date: Sun, 15 Jul 2007 04:02:18 +0200 (CEST) To: Steffan A. Cline [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: New error to me... On Sun, July 15, 2007 03:53, Steffan A. Cline wrote: I was trying to reduce a set of queries and ran into this: insert into forums (forum_reply_id, forum_dev_id, forum_subject, forum_message) values (0, 1, (select forum_subject from forums where forum_id=3 ), I figured this one needed a reply too.) Your subselect could return more than one row, and because of that, can't be used in a direct insert/update. yields: error: You can't specify target table from 'forums' for update in from clause I read somewhere online when they referenced 4.x that said you cannot do subqueries in an update. Is this true of 5.x ? Thanks Steffan --- T E L 6 0 2 . 5 7 9 . 4 2 3 0 | 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 Lasso Partner Alliance Member --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- 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]
Recursive queries
I am trying to set up a forum where there are main posts with replies and replies to replies. Rather than using nested queries in my middleware I am thinking there has to be some kind of recursive query where I can output the results in a format like so: MAIN Reply to main reply to reply to main reply to main reply to 2nd reply to main MAIN Reply reply The table structure is like so: +---+--+--+-+---+--- -+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+---+--- -+ | forum_id | bigint(11) | NO | PRI | NULL | auto_increment | | project_id| bigint(11) | YES | MUL | 0 | | | forum_reply_id| bigint(11) | YES | MUL | 0 | | | forum_dev_id | bigint(11) | YES | MUL | 0 | | | forum_type| varchar(255) | YES | | | | | forum_subject | varchar(255) | YES | | | | | forum_message | longtext | YES | | NULL | | | forum_date_posted | timestamp| NO | | CURRENT_TIMESTAMP | | +---+--+--+-+---+--- -+ Test data is like so mysql select * from forums; +--+++--++-- -+-+ -+ | forum_id | project_id | forum_reply_id | forum_dev_id | forum_type | forum_subject | forum_message | forum_date_posted | +--+++--++-- -+-+ -+ |1 | 42 | 0 |1 || First Post| I am the First! | 2007-07-08 15:09:41 | |2 | 42 | 1 |1 || | I am a reply to the first | 2007-07-08 15:30:36 | |3 | 42 | 0 |1 || sample data | this is some sample data in a new thread| 2007-07-08 15:10:03 | |4 | 42 | 2 |1 || | this is a reply to the reply of the first post. | 2007-07-08 15:33:54 | +--+++--++-- -+-+ -+ 4 rows in set (0.00 sec) I am figuring that if the reply_to_id is 0 then it is a parent thread otherwise it is a child or child of a child etc. Any way of doing this? Thanks Steffan --- T E L 6 0 2 . 5 7 9 . 4 2 3 0 | 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 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: Primary key
In the last episode (Jan 11), Steffan A. Cline said: Is there anyway to do a SELECT or DESCRIBE or SHOW statement which can return the primary key field of a specified table? Pseudo code: select primary_key_field_name from mytable. SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA='myschema' AND TABLE_NAME='mytable' AND CONSTRAINT_NAME='PRIMARY' ORDER BY ORDINAL_POSITION; For multi-column indexes, you will get multiple rows back. You should also use show create table mytable or show keys from mytable but you'll have to do extra parsing. Dan, Great! This is what I was after... I am building a class/ctype for Lasso for a user db. When it is instantiated, it loads the structure into the type from the db. Now, I have the primary key as the ID of the type I need to separate it from the rest of the columns. SO, I guess now I'll work on unless you have it handy where I can return all columns EXCEPT the primary key. Thanks Steffan --- T E L 6 0 2 . 5 7 9 . 4 2 3 0 | 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 Lasso Partner Alliance Member --- From: Dan Nelson [EMAIL PROTECTED] Date: Fri, 12 Jan 2007 01:00:03 -0600 To: Steffan A. Cline [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: Primary key In the last episode (Jan 11), Steffan A. Cline said: Is there anyway to do a SELECT or DESCRIBE or SHOW statement which can return the primary key field of a specified table? Pseudo code: select primary_key_field_name from mytable. SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA='myschema' AND TABLE_NAME='mytable' AND CONSTRAINT_NAME='PRIMARY' ORDER BY ORDINAL_POSITION; For multi-column indexes, you will get multiple rows back. You should also use show create table mytable or show keys from mytable but you'll have to do extra parsing. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Primary key
Is there anyway to do a SELECT or DESCRIBE or SHOW statement which can return the primary key field of a specified table? Pseudo code: select primary_key_field_name from mytable. Thanks Steffan --- T E L 6 0 2 . 5 7 9 . 4 2 3 0 | 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 Lasso Partner Alliance Member --- sql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
V 5.0.18 on Mac OS X
I am having an issue with MySQL running on Mac OS X. Currently the version as stated 5.0.18 on a dual G4 Xserve with 1gb of ram. MySQL is mainly the only thing running on this server. I am trying to track down an issue in which MySQL is being overloaded and it consistently damages the same one or two tables. I am trying to narrow down the issue to the web service connecting to MySQL or MySQL itself. When I check the status I see a ton of locks and unauthenticated connections. Any suggestions of what to look for on the MySQL side? It seems rather odd that being overloaded is that it damages the tables. There is no replication or auto backups in place with this that could cause these issues. Some of the queries thrown are indeed big ones and do require many ticks to calculate but still, what is left? I just made some changes to the config and this is now the base of what I have: # The MySQL server [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking key_buffer = 256M max_allowed_packet = 50M table_cache = 256 sort_buffer_size = 1M read_buffer_size = 1M read_rnd_buffer_size = 4M myisam_sort_buffer_size = 64M #thread_cache_size = 8 query_cache_type = 1 query_cache_size = 128M query_cache_limit = 128M # added the next few lines for debugging all the way to the next comment skip-name-resolve interactive_timeout = 300 wait_timeout = 300 max_connections = 250 thread_cache_size = 40 log_error = /var/log/mysqld-error.log log_slow_queries = /var/log/mysqld-slow.log # Try number of CPU's*2 for thread_concurrency thread_concurrency = 8 Could it be the version of MySQL that is causing the damage? I did a fsck on the drive and all comes back fine. What's left? Thanks Steffan --- T E L 6 0 2 . 5 7 9 . 4 2 3 0 | 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 Lasso Partner Alliance Member --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query missing rows in location of zip by distance
Ran into a strange problem. In this zip code I am searching in I know for sure I have 6 locations within the 63385 zip. Doing a simple select * from locations where zip = '63385' returns 6 rows. Basically all 6 should come up in the big query because they are within the same zip and that any other locations within the same area. They all have the same latitude and longitude but only 1 shows up. SELECT b.zip, b.state,b.storename, b.address1, b.address2, b.city, b.state, b.zip, b.id, ROUND((3956 * (2 * ASIN(SQRT( POWER(SIN(((a.lat-b.lat)*0.017453293)/2),2) + COS(a.lat*0.017453293) * COS(b.lat*0.017453293) * POWER(SIN(((a.lon-b.lon)*0.017453293)/2),2),2) AS distance FROM zipcodes a, locations b WHERE a.zip = 63385 GROUP BY distance HAVING distance = 5; A tough one. I restarted MySQL thinking it could somehow be a bad cache or something but no matter what I only get these results. Now, I did think of cheating and adding an OR zip=63385 but then what would happen if a neighboring zip had 5 locations. I'd probably only get 1 record from that as well. :/ Thanks Steffan --- T E L 6 0 2 . 5 7 9 . 4 2 3 0 | 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 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: Query missing rows in location of zip by distance
I have found in life that I get stuck, email a list and then figure it out myself often. It was rather dumb! Anyhow, here is what I have. SELECT b.zip, b.state,b.storename, b.address1, b.address2, b.city, b.state, b.zip, b.id, ROUND((3956 * (2 * ASIN(SQRT( POWER(SIN(((a.lat-b.lat)*0.017453293)/2),2) + COS(a.lat*0.017453293) * COS(b.lat*0.017453293) * POWER(SIN(((a.lon-b.lon)*0.017453293)/2),2),2) AS distance FROM zipcodes a, locations b WHERE a.zip = 63385 GROUP BY b.id HAVING distance = 5 ORDER BY distance, storename; Thanks Steffan --- T E L 6 0 2 . 5 7 9 . 4 2 3 0 | 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 Lasso Partner Alliance Member --- From: Jay Pipes [EMAIL PROTECTED] Organization: MySQL, Inc. Reply-To: [EMAIL PROTECTED] Date: Mon, 25 Sep 2006 09:59:19 -0400 To: Steffan A. Cline [EMAIL PROTECTED] Cc: MySQL List mysql@lists.mysql.com Subject: Re: Query missing rows in location of zip by distance On Mon, 2006-09-25 at 06:09 -0700, Steffan A. Cline wrote: Basically all 6 should come up in the big query because they are within the same zip and that any other locations within the same area. They all have the same latitude and longitude but only 1 shows up. If they all have the same lat/long, then the distance from the centroid of your supplied zip code will be the same for all 6. Because you are grouping on the distance, only 1 record will return. Remove the GROUP BY distance. Cheers, Jay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL
Is there anyone who has EXTENSIVE sql experience that is available for consulting? This is a short term opportunity for a single project. Maybe an couple hours tops to assist in some COMPLEX queries that do totals and the like across multiple tables. This is NOT for an amateur. IF you can handle COMPLEX SQL and have EXTENSIVE experience please contact me off list, just send me an email and I'll respond to them tonight. When you respond, please send the MOST COMPLEX query you have ever done as an example of your work. The caps are not meant to be yelling but rather accentuating the fact that I need someone assistance of someone extremely experienced for this project. Thanks Steffan --- T E L 6 0 2 . 5 7 9 . 4 2 3 0 | 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 Lasso Partner Alliance Member --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Group by with an IF
I have the following query: select *, if( season_week_date = 2006-08-16, on, off ) as stat, sum(overall_points) as total_points from rosters r left join celebs c on c.celeb_id = r.celeb_id where season_id=5062 and user_id=1 group by r.celeb_id order by overall_rank, ln, fn; It almost works as expected however the problem I am concerned about is that I never get a stat saying On after it resolves. Removing the sum() and adding group I get the total_points as expected but now the results are always off. Can anyone shed any light on this one? Thanks Steffan --- T E L 6 0 2 . 5 7 9 . 4 2 3 0 | 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 Lasso Partner Alliance Member --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Joining result sets into 1 row
I am in a situation where I have say 1 column called attribute I need and the result set is 3 rows. i.e. ROW 1 - Mechanic ROW 2 - Carpenter ROW 3 - Plumber I want to have the rows returned as one row Such as ROW 1 Mechanic, Carpenter, Plumber Something like a literal join would be beautiful such as : ROW 1 Mechanic, Carpenter and Plumber I think the latter is asking for too much but the first would be awesome. Any advice is much appreciated! Thanks Steffan --- T E L 6 0 2 . 5 7 9 . 4 2 3 0 | 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 Lasso Partner Alliance Member --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Full Text Search across 2 tables.
I have 2 tables which have full text index on each of their columns. Table 1 - forums_topics field - topic Table 2 - forums_messages field - message Is it possible to search them both in one query and determine which table the result is being returned from in the search results? Thanks Steffan --- T E L 6 0 2 . 5 7 9 . 4 2 3 0 | 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 Lasso Partner Alliance Member --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Multiple joins
What am I missing here? select m.*, d.discussion, d.discussion_id, u.user_id, t.topic_id from forums_messages left join forums_members m, forums_discussions d, users u, forums_topics t on m.topic_id = t.topic_id, t.discussion_id = d.discussion_id where m.topic_id = 1; Is it legal to do multiple joins like this? Thanks Steffan --- T E L 6 0 2 . 5 7 9 . 4 2 3 0 | 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 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: Multiple joins
This is what I finally settled on. It seems to work well. select m.*, d.discussion, d.discussion_id, u.user_id, u.icon, u.nick_name from forums_messages m left join forums_topics t on m.topic_id = t.topic_id left join forums_discussions d on t.discussion_id = d.discussion_id left join users u on m.user_id = u.user_id where m.topic_id = 1; Thanks Steffan --- T E L 6 0 2 . 5 7 9 . 4 2 3 0 | 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 Lasso Partner Alliance Member --- From: Gerald L. Clark [EMAIL PROTECTED] Date: Fri, 30 Jun 2006 09:54:14 -0500 To: Steffan A. Cline [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: Multiple joins Steffan A. Cline wrote: What am I missing here? select m.*, d.discussion, d.discussion_id, u.user_id, t.topic_id from forums_messages left join forums_members m, forums_discussions d, users u, forums_topics t on m.topic_id = t.topic_id, t.discussion_id = d.discussion_id where m.topic_id = 1; Is it legal to do multiple joins like this? Thanks Steffan forum_messages does not take part in any selection or any where clause. You have 5 tables listed, with only 4 of them appearing in 2 disjointed, and improperly formed joins. From the comma separated table list after a LEFT JOIN, I am not sure whether you intend them to be LEFT JOINs or INNER JOINs. Try: SELECT fields FROM file1 LEFT JOIN file2 ON something INNER JOIN file3 ON something INNER JOIN file4 ON something INNER JOIN file5 ON something WHERE somecondition -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Returning records in a circle
Is there a way I can get a set of records incrementally such as to get 2 then the next query get the next 2 then at the end of all records to get the 2 from the beginning? I need to keep going incrementally by 2 in a circle. Thanks Steffan --- T E L 6 0 2 . 5 7 9 . 4 2 3 0 | 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 The Executive's Choice in Lasso driven Internet Applications 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: Returning records in a circle
Well, basically it can be done to an extent in some kind of code. Basically I am drawing from a table 2 records at a time. I want to make sure that all records are pulled at least once. If at all possible not to have 2 from the same vendor. So, in this case. I have columns id, html, vendor So I can add columns as needed. I tried adding a flag and after returning to the client the 2 records I'd mark it as flag = 1 then search like this Select id, html from urls order by flag, id desc limit 3 Then after I have those I would then set the last of the 3 to flag = 1 so that on the next search I get the 2 after. In theory it worked fine but when multiple people hit the page simultaneously I had flags in different places and not in order. Maybe just mark them as flag = 1 after returned and then on search if found is 0 then set all to flag = 0 so they can be seen again? This doesn't seem so bad but them I guess I'd use distinct? If I cant use distinct with other parameters... ex: select id,html distinct(vendor) from urls where flag=0 limit 2; Would it be like : Select id,html from urls where flag = 0 group by distinct(vendor) limit 2 ? Thanks Steffan --- T E L 6 0 2 . 5 7 9 . 4 2 3 0 | 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 The Executive's Choice in Lasso driven Internet Applications Lasso Partner Alliance Member --- From: Rhino [EMAIL PROTECTED] Date: Fri, 12 May 2006 14:20:10 -0400 To: Steffan A. Cline [EMAIL PROTECTED], mysql@lists.mysql.com Subject: Re: Returning records in a circle - Original Message - From: Steffan A. Cline [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, May 12, 2006 1:38 PM Subject: Returning records in a circle Is there a way I can get a set of records incrementally such as to get 2 then the next query get the next 2 then at the end of all records to get the 2 from the beginning? I need to keep going incrementally by 2 in a circle. Are you trying to get these rows purely via SQL at the command line or in an SQL script? Or would an application be an option for you? If you are not willing to consider application code to grab the rows you want, the answer to your question is maybe. SQL has always been intended to return ALL of the rows that satisfy a query with a single invocation of the query, no matter how many rows that is. So if your query says: select * from mytab; you will normally get all of the rows that satisfy that query in one go, whether there are 0 rows, 100 rows, or a 100 million rows in the result. You _might_ be able to get the results you want by using the LIMIT clause. I'm not sure what version of MySQL you are using but the LIMIT clause is described in the MySQL 3.23/4.0/4.1 manual on this page: http://dev.mysql.com/doc/refman/4.1/en/select.html. The problem is that you'll still pretty much need some sort of script in order to keep executing the query to get the next two records and you may need to change the parameters of the LIMIT clause at the same time. If you are willing to write application code, things get a lot easier. For instance, a Java program could easily grab rows from a result set for you two at a time, let you process them, then grab two more, etc. I expect that it would similarly easy to do the same thing in Perl and PHP and C. In short, a program gives you a lot more ability to do what you want to do with your database data. But some shops have very little programming expertise and prefer to do everything via SQL. If you work for one of those shops, you might not be able to get your records two at a time with SQL alone, unless you can write a script that takes advantage of the LIMIT clause. I don't pretend to know MySQL exhaustively so someone else may have another suggestion for you but the only two approaches I can think of that might meet your needs are to use the LIMIT clause or to write an application. -- Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.5.6/337 - Release Date: 11/05/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Repairing data
I ran into a situation where upon importing a client's data I noticed that they had in address field 123 easy street #600. Is there a query I can do to take the field address and truncate the #600 and stick it into the field address2? I am sure it might take some regexp or something but its a bit over my head. Any suggestions? Thanks Steffan --- T E L 6 0 2 . 5 7 9 . 4 2 3 0 | 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 The Executive's Choice in Lasso driven Internet Applications 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: Triggers
This does not work as I am drawing the information from 2 tables. I did use
the NEW operator and every combination I could think of. Below I'll include
the triggers and so on. What the deal is that I have a query to find
locations within a certain radius. BUT from what I read in the table with
locations I need to have latitude and longitude. I was trying to make this
automatically populated so that the user would not have to do it.
Anyhow :
Triggers:
CREATE TRIGGER InsPopCoord AFTER INSERT ON locations FOR EACH ROW UPDATE
locations, zipcodes SET NEW.locations.test1=zipcodes.lat,
NEW.locations.test2=zipcodes.lon WHERE (NEW.locations.zip=zipcodes.zip AND
(NEW.locations.test1 IS NULL) AND (NEW.locations.test2 is NULL));
CREATE TRIGGER UpdPopCoord AFTER UPDATE ON locations FOR EACH ROW UPDATE
locations, zipcodes SET NEW.locations.test1=zipcodes.lat,
NEW.locations.test2=zipcodes.lon WHERE (NEW.locations.zip=zipcodes.zip AND
(NEW.locations.test1 IS NULL) AND (NEW.locations.test2 is NULL));
The query necessitating the lat and lon:
-sql=('SELECT b.zip, b.state,b.storename, b.address1, b.address2, b.city,
b.state, b.zip,
ROUND((3956 * (2 * ASIN(SQRT(
POWER(SIN(((a.lat-b.lat)*0.017453293)/2),2) +
COS(a.lat*0.017453293) *
COS(b.lat*0.017453293) *
POWER(SIN(((a.lon-b.lon)*0.017453293)/2),2),2) AS distance
FROM zipcodes a, locations b
WHERE
a.zip = ' ($zip) '
GROUP BY distance
having distance = ' ($range) ';');
The table structures :
Thanks
Steffan
---
T E L 6 0 2 . 5 7 9 . 4 2 3 0 | 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
The Executive's Choice in Lasso driven Internet Applications
Lasso Partner Alliance Member
---
From: Ian Sales (DBA) [EMAIL PROTECTED]
Date: Mon, 10 Oct 2005 08:51:55 +0100
To: Steffan A. Cline [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Subject: Re: Triggers
Steffan A. Cline wrote:
Upon insert or update I get the following error:
ERROR 1442 (HY000): Can't update table 'locations' in stored
function/trigger because it is already used by statement which invoked this
stored function/trigger.
What exactly is the meaning of this? Is there no way around this? I only
want to update the one that was just inserted/updated.
- you cannot use a table in a trigger which is triggered by an action on
that self-same table, as this is recursive. You can, however, use NEW as
a synonym for the data being changed which fires off the trigger. E.g.,
CREATE TRIGGER InsPopCoord AFTER INSERT ON locations FOR EACH ROW
UPDATE zipcodes
SET zipcodes.lat=NEW.lat, zipcodes.lon=NEW.lon
WHERE zipcodes.zip=NEW.zip;
- ian
--
+---+
| Ian Sales Database Administrator |
| |
| All your database are belong to us |
| ebuyer http://www.ebuyer.com |
+---+
--
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: Triggers
This does not work as I am drawing the information from 2 tables. I did use
the NEW operator and every combination I could think of. Below I'll include
the triggers and so on. What the deal is that I have a query to find
locations within a certain radius. BUT from what I read in the table with
locations I need to have latitude and longitude. I was trying to make this
automatically populated so that the user would not have to do it.
Anyhow :
Triggers:
CREATE TRIGGER InsPopCoord AFTER INSERT ON locations FOR EACH ROW UPDATE
locations, zipcodes SET NEW.locations.test1=zipcodes.lat,
NEW.locations.test2=zipcodes.lon WHERE (NEW.locations.zip=zipcodes.zip AND
(NEW.locations.test1 IS NULL) AND (NEW.locations.test2 is NULL));
CREATE TRIGGER UpdPopCoord AFTER UPDATE ON locations FOR EACH ROW UPDATE
locations, zipcodes SET NEW.locations.test1=zipcodes.lat,
NEW.locations.test2=zipcodes.lon WHERE (NEW.locations.zip=zipcodes.zip AND
(NEW.locations.test1 IS NULL) AND (NEW.locations.test2 is NULL));
The query necessitating the lat and lon:
-sql=('SELECT b.zip, b.state,b.storename, b.address1, b.address2, b.city,
b.state, b.zip,
ROUND((3956 * (2 * ASIN(SQRT(
POWER(SIN(((a.lat-b.lat)*0.017453293)/2),2) +
COS(a.lat*0.017453293) *
COS(b.lat*0.017453293) *
POWER(SIN(((a.lon-b.lon)*0.017453293)/2),2),2) AS distance
FROM zipcodes a, locations b
WHERE
a.zip = ' ($zip) '
GROUP BY distance
having distance = ' ($range) ';');
The table structures :
SET FOREIGN_KEY_CHECKS = 0;
CREATE TABLE `locations` (
`storename` varchar(255) default NULL,
`address1` varchar(255) default NULL,
`address2` varchar(255) default NULL,
`city` varchar(255) default NULL,
`state` varchar(255) default NULL,
`zip` varchar(255) default NULL,
`phone` varchar(255) default NULL,
`email` varchar(255) default NULL,
`url` varchar(255) default NULL,
`fax` varchar(255) default NULL,
`comments` text,
`firstname` varchar(255) default NULL,
`lastname` varchar(255) default NULL,
`lat` float default NULL,
`lon` float default NULL,
`id` bigint(11) NOT NULL auto_increment,
`test1` float default NULL,
`test2` float default NULL,
PRIMARY KEY (`id`),
KEY `zip` (`zip`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `zipcodes` (
`zip` varchar(5) default NULL,
`city` varchar(100) default NULL,
`county` varchar(100) default NULL,
`state` varchar(100) default NULL,
`areacode` varchar(10) default NULL,
`fips` varchar(10) default NULL,
`timezone` varchar(10) default NULL,
`dst` varchar(10) default NULL,
`lat` float default NULL,
`lon` float default NULL,
KEY `zip` (`zip`),
KEY `lat` (`lat`),
KEY `lon` (`lon`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
SET FOREIGN_KEY_CHECKS = 1;
Thanks
Steffan
---
T E L 6 0 2 . 5 7 9 . 4 2 3 0 | 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
The Executive's Choice in Lasso driven Internet Applications
Lasso Partner Alliance Member
---
From: Ian Sales (DBA) [EMAIL PROTECTED]
Date: Mon, 10 Oct 2005 08:51:55 +0100
To: Steffan A. Cline [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Subject: Re: Triggers
Steffan A. Cline wrote:
Upon insert or update I get the following error:
ERROR 1442 (HY000): Can't update table 'locations' in stored
function/trigger because it is already used by statement which invoked this
stored function/trigger.
What exactly is the meaning of this? Is there no way around this? I only
want to update the one that was just inserted/updated.
- you cannot use a table in a trigger which is triggered by an action on
that self-same table, as this is recursive. You can, however, use NEW as
a synonym for the data being changed which fires off the trigger. E.g.,
CREATE TRIGGER InsPopCoord AFTER INSERT ON locations FOR EACH ROW
UPDATE zipcodes
SET zipcodes.lat=NEW.lat, zipcodes.lon=NEW.lon
WHERE zipcodes.zip=NEW.zip;
- ian
--
+---+
| Ian Sales Database Administrator |
| |
| All your database are belong to us |
| ebuyer http://www.ebuyer.com |
+---+
--
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]
Triggers
I am trying to use triggers for the first time with MySQL 5.0. I have read the manual but I am not understanding why I would run into the following problem. I created the following triggers: CREATE TRIGGER UpdPopCoord AFTER UPDATE ON locations FOR EACH ROW UPDATE locations, zipcodes SET locations.lat=zipcodes.lat, locations.lon=zipcodes.lon WHERE (locations.zip=zipcodes.zip AND (locations.lat IS NULL) AND (locations.lon is NULL)); CREATE TRIGGER InsPopCoord AFTER INSERT ON locations FOR EACH ROW UPDATE locations, zipcodes SET locations.lat=zipcodes.lat, locations.lon=zipcodes.lon WHERE (locations.zip=zipcodes.zip AND (locations.lat IS NULL) AND (locations.lon is NULL)); Upon insert or update I get the following error: ERROR 1442 (HY000): Can't update table 'locations' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. What exactly is the meaning of this? Is there no way around this? I only want to update the one that was just inserted/updated. Thanks Steffan --- T E L 6 0 2 . 5 7 9 . 4 2 3 0 | 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 The Executive's Choice in Lasso driven Internet Applications 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: Distance between Zip codes
I think it was your formula that I finally got to work as I needed.
Excuse the Lasso in here :
-sql=('SELECT b.zip, b.state,b.storename, b.address1, b.address2, b.city,
b.state, b.zip,
ROUND((3956 * (2 * ASIN(SQRT(
POWER(SIN(((a.lat-b.lat)*0.017453293)/2),2) +
COS(a.lat*0.017453293) *
COS(b.lat*0.017453293) *
POWER(SIN(((a.lon-b.lon)*0.017453293)/2),2),2) AS distance
FROM zipcodes a, locations b
WHERE
a.zip = ' ($zip) '
GROUP BY distance
having distance = ' ($range) ';');
Its fast and works great. Although is there no way for it to lookup the
latitude and longitude for the zip in the b table before doing the query?
I was up late trying to get that to work and couldn't so I then looked at
using a trigger to insert it into the b (locations) table upon creation
of each new record but I have another thread on that issue. :(
Any suggestions to improve it or a better way of doing it?
Thanks
Steffan
---
T E L 6 0 2 . 5 7 9 . 4 2 3 0 | 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
The Executive's Choice in Lasso driven Internet Applications
Lasso Partner Alliance Member
---
From: Hank [EMAIL PROTECTED]
Reply-To: Hank [EMAIL PROTECTED]
Date: Sun, 9 Oct 2005 11:12:10 -0400
To: Steffan A. Cline [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com mysql@lists.mysql.com
Subject: Re: Distance between Zip codes
Don't forget that you're not caclucating driving distance, but
great circle distance, which is roughly a straight line over short
distances. If you radius is great than, say 50 miles, people might
complain that the actual driving distance is much greater than the
straight line distance you provided.
--
-Hank
mysql, query
--
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]
Distance between Zip codes
I was wondering if anyone might have a canned query I could use for the following scenario. I need to search for a list of locations within a certain distance of a user given zip code and order them by driving distance calculated from a table of zip codes containing lon and lat info from zipwise. Example: 1. I enter my zip of 85050 2. enter a range in miles 3. search a table of establishments within x miles from step 2 4. list top ten within range of step 2 ordered by distance. For the establishments I have the zip codes and as I said I have the zipwise tables. Any suggestions? This query if completely possible within MySQL is well above me. Thanks Steffan --- T E L 6 0 2 . 5 7 9 . 4 2 3 0 | 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 The Executive's Choice in Lasso driven Internet Applications 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: Distance between Zip codes
I saw those and several on finding matches and are close but the problem I ran into is that yeah.. they find all of the zips within the specified radius but I need to the matches that came from another table. Thanks Steffan --- T E L 6 0 2 . 5 7 9 . 4 2 3 0 | 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 The Executive's Choice in Lasso driven Internet Applications Lasso Partner Alliance Member --- From: Steffan A. Cline [EMAIL PROTECTED] Date: Sat, 08 Oct 2005 15:59:35 -0700 To: mysql@lists.mysql.com mysql@lists.mysql.com Subject: Distance between Zip codes I was wondering if anyone might have a canned query I could use for the following scenario. I need to search for a list of locations within a certain distance of a user given zip code and order them by driving distance calculated from a table of zip codes containing lon and lat info from zipwise. Example: 1. I enter my zip of 85050 2. enter a range in miles 3. search a table of establishments within x miles from step 2 4. list top ten within range of step 2 ordered by distance. For the establishments I have the zip codes and as I said I have the zipwise tables. Any suggestions? This query if completely possible within MySQL is well above me. Thanks Steffan --- T E L 6 0 2 . 5 7 9 . 4 2 3 0 | 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 The Executive's Choice in Lasso driven Internet Applications Lasso Partner Alliance Member --- -- 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: Distance between Zip codes
Ok. I think I have what I need... Almost. Here is the query I settled on: SELECT b.zip, b.state,b.storename, (3956 * (2 * ASIN(SQRT( POWER(SIN(((a.lat-b.lat)*0.017453293)/2),2) + COS(a.lat*0.017453293) * COS(b.lat*0.017453293) * POWER(SIN(((a.lon-b.lon)*0.017453293)/2),2) AS distance FROM zipcodes a, locations b WHERE a.zip = 85032 GROUP BY distance having distance = 10; The problem is that in my locations (stores) table I am required to have a latitude and longitude. Is there anyway to modify this query to look up the lat/lon of the zipcode in the locations (stores) table BEFORE it does the remainder of the query? The goal is to not need those in the locations table. Thanks Steffan --- T E L 6 0 2 . 5 7 9 . 4 2 3 0 | 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 The Executive's Choice in Lasso driven Internet Applications Lasso Partner Alliance Member --- From: Steffan A. Cline [EMAIL PROTECTED] Date: Sat, 08 Oct 2005 15:59:35 -0700 To: mysql@lists.mysql.com mysql@lists.mysql.com Subject: Distance between Zip codes I was wondering if anyone might have a canned query I could use for the following scenario. I need to search for a list of locations within a certain distance of a user given zip code and order them by driving distance calculated from a table of zip codes containing lon and lat info from zipwise. Example: 1. I enter my zip of 85050 2. enter a range in miles 3. search a table of establishments within x miles from step 2 4. list top ten within range of step 2 ordered by distance. For the establishments I have the zip codes and as I said I have the zipwise tables. Any suggestions? This query if completely possible within MySQL is well above me. Thanks Steffan --- T E L 6 0 2 . 5 7 9 . 4 2 3 0 | 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 The Executive's Choice in Lasso driven Internet Applications Lasso Partner Alliance Member --- -- 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]
Getting count(*) with LIMIT in SQL
I have been tinkering with finding the fastest way to do my searches and return less info for faster performance. In a project I have, I build the sql query with information from a form page. The query may look like this: SELECT * FROM masterlist WHERE market LIKE %% AND source LIKE %% AND clientstatus LIKE %% AND ( client LIKE %% OR contact LIKE %% ) ORDER BY client LIMIT 0,1 ; Ignore the LIKE %%. This is caused when fields are selected to search on and no keywords are found. I'll fix that later. This works fine and dandy except I need to get the overall found count as well. I was toying with COUNT(*) to see if I could get the found count returned too. If I do this : SELECT *, COUNT(*) AS found FROM masterlist WHERE market LIKE %% AND source LIKE %% AND clientstatus LIKE %% AND ( client LIKE %% OR contact LIKE %% ) ORDER BY client LIMIT 0,1 ; I get the following error : Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns is illegal if there is no GROUP BY clause (1140) If I do the following : SELECT COUNT(*) AS found FROM masterlist WHERE market LIKE %% AND source LIKE %% AND clientstatus LIKE %% AND ( client LIKE %% OR contact LIKE %% ) ORDER BY client LIMIT 0,1 ; I get the response of found 8214. This is correct! Isn't there some way to get the found count of the query returned with the results of the search? The goal is NOT to have 2 searches. One that gives the found count and then one that actually returns the data. BTW, Is there any MAJOR dent in performance if somehow I end up with a search where... column LIKE %% SQL Gurus! Any way to do this? Thanks Steffan --- T E L 6 0 2 . 5 7 9 . 4 2 3 0 | 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 The Executive's Choice in Lasso driven Internet Applications 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: Complex query woes
Leo, Thanks for the quick reply. There was a typo but I fixed it. Below is what I used after correcting it : select mgr.company, building.bldgname, tenant.id from customers mgr left join customers building on building.pid=mgr.id left join customers tenant on tenant.pid=building.id group by mgr.id, building.id, tenant.id order by mgr.company, building.bldgname, tenant.company; This was closer. Problem now is that it took 6.56 seconds and returned 610 rows. I have no idea how I now have 610 rows where there are only 279. Any thing else you would suggest? Thanks Steffan --- T E L 6 0 2 . 5 7 9 . 4 2 3 0 | 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 The Executive's Choice in Lasso driven Internet Applications Lasso Partner Alliance Member --- From: Leo [EMAIL PROTECTED] Date: Mon, 10 Nov 2003 13:17:50 +0700 To: Steffan A. Cline [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: Re: Complex query woes have you try left join? select mgr.company, building.bldgname, tenant.id from customers mgr left join customers building on building.pid=mgr.id left join costumers tenant on tenant.pid=building.id group by mgr.id, building.id, tenant.id order by mgr.company, building.bldgname, tenant.company hopefully it work :) -leo- - Original Message - From: Steffan A. Cline To: [EMAIL PROTECTED] Sent: Monday, November 10, 2003 12:56 PM Subject: Complex query woes Basically I have a table that contains 3 types of records. Property managers, buildings and tenants. They are related upon insert by an ID and a PID (parent id). For example : ID PID CategoryName - 1 PM ABC Management 2 1 BldgGlen Heights 3 2 tenant Joe's salon Hopefully this shows how they are related. My goal is to ultimately on a Lasso (like php) page to render them like this : ABC Management Glen Heights Joe's salon Some other building Some other tenant I am able to handle the formatting fine the issue is how to get the data returned like this. I tried the following : select mgr.company, building.bldgname, tenant.company from customers as mgr,customers as building, customers as tenant where building.pid = mgr.id and tenant.pid = building.id order by mgr.company,building.bldgname,tenant.company; But it only returns 173 rows are there are 279. As you will see in http://phattwelve.hldns.com:90/workorder/findaccount2.lasso there are some property managers with buildings and no tenants, also property manager with no buildings. These get omitted by the above sql. Currently I am doing this with nested statements via lasso but is getting ridiculously slow on the live system as they add more and more clients. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Complex query woes
I am hoping someone here might have an idea how to set up a complex query I have been trying to figure out. Basically I have a table that contains 3 types of records. Property managers, buildings and tenants. They are related upon insert by an ID and a PID (parent id). For example : ID PID CategoryName - 1 PM ABC Management 2 1 BldgGlen Heights 3 2 tenant Joe's salon Hopefully this shows how they are related. My goal is to ultimately on a Lasso (like php) page to render them like this : ABC Management Glen Heights Joe's salon Some other building Some other tenant I am able to handle the formatting fine the issue is how to get the data returned like this. I tried the following : select mgr.company, building.bldgname, tenant.company from customers as mgr,customers as building, customers as tenant where building.pid = mgr.id and tenant.pid = building.id order by mgr.company,building.bldgname,tenant.company; But it only returns 173 rows are there are 279. As you will see in http://phattwelve.hldns.com:90/workorder/findaccount2.lasso there are some property managers with buildings and no tenants, also property manager with no buildings. These get omitted by the above sql. Currently I am doing this with nested statements via lasso but is getting ridiculously slow on the live system as they add more and more clients. Is there any way around this with a single query instead of multiple queries in MySQL? Thanks Steffan --- T E L 6 0 2 . 5 7 9 . 4 2 3 0 | 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 The Executive's Choice in Lasso driven Internet Applications Lasso Partner Alliance Member --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Days in a month
Is there a function that I missed for calculating the number of days in a month? Steffan MySQL --- T E L 6 0 2 . 5 7 9 . 4 2 3 0 | 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 The Executive's Choice in Lasso driven Internet Applications --- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Full text search
Am I missing something on mysql full text search?
I was using a simple statement like
select firstname from contacts where match(firstname,lastname) against
('steffa');
I am actually looking for steffan but wanted to see what it would return.
Now, if I search for the full name steffan it finds it ok. Is there
something I am missing for it to return any matches containing steff or
steffa or even stef
Thanks
Steffan
---
T E L 6 0 2 . 5 7 9 . 4 2 3 0 | 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
The Executive's Choice in Lasso driven Internet Applications
---
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
FULLTEXT
Hello Everyone! I am a member of the Lasso list and heard this would be a good place to ask about a few things about MySQL. 1. In a few places I read about FULLTEXT searches. In one place I saw it said 16 columns and in another 15. But, I am finding that when using 15 I get an error but then go down to 14 and all is well. One of these I am searching is a TEXT field. Does this eat up 2 VARCHAR columns in the FULLTEXT? 2. Is there a way in maybe a rebuild of the code to change from 4 or more letters in the FULLTEXT search to 3 or more? I noticed on the FULLTEXT page someone commented on this. 3. Almost the same as above but is there a way to increase the number of columns for a FULLTEXT search? Thanks Steffan --- T E L 6 0 2 . 5 7 9 . 4 2 3 0 | 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 The Executive's Choice in Lasso driven Internet Applications --- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
