Re: Sorting Problem
The query using JOIN syntax (you can read about this in the documentation) describes the interelationship between the three tables you described, in this way you can select information based on a WHERE clause as it relates to the category table, while still ordering by the business table. Read up on joins, that may help. -Micah Sid Price wrote: I am sorry but I don't understand the select query you wrote, could you perhaps explain how it works or point me to a reference that might help me understand it? Many thanks for responding, Sid. Sid Price Software Design http://www.softtools.com -Original Message- From: Micah Stevens [mailto:[EMAIL PROTECTED] Sent: Sunday, March 25, 2007 9:23 PM To: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Re: Sorting Problem This doesn't work? SELECT businesses.name from businesses left join links using (businessID) left join categories using (categoryID) where category.name = 'something' order by businesses.name ASC On 03/25/2007 12:40 PM, Sid Price wrote: Hello, I have a MySQL database design that provides an online business directory. It consists of three tables; one with the names and addresses of the businesses (names), one with the categories of businesses (categories), and one that has an entry (a category ID and a business ID) for each business/category pairing (entries), a business may be in multiple categories. The problem I am having is after having queried the "entries" table for all the entries for a given category I query the "names" table for each entry to display the business name and address, I can not figure a way to sort the displayed data by company name, The "entries" table holds the business ID and category ID so sorting the entries that match the category doesn't help. Do I need to create a temporary table to hold the business names/addresses in a category so that I can then sort it? Thanks, Sid. Sid Price Software Design http://www.softtools.com
Re: right configuration.
Hi Friends, Any answers to these questions would be of great help to me. regards anandkl On 3/25/07, Ananda Kumar <[EMAIL PROTECTED]> wrote: Hi Friends, We are in the processing of building a new Mysql database. We are planing to use innodb. I need help on couple of questions. 1. What are the best parameter setting in my.cnf for innodb. 2. Is it possible to allocate each table a separate tablespace. 3. Is it possible to create index in a separate tablespace. 4. We planing to create partitioning at application level, is there a limit on no. of partitions, and also is there is a limit on number of rows on each partition. 5. We are planing to migrate from oracle to mysql, is there any particular way to speed up migrating huge data from oracle to mysql. 6. What are the different events do we need to moinitor in Mysql. thanks a lot for all you help. regards anandkl
RE: Sorting Problem
I am sorry but I don't understand the select query you wrote, could you perhaps explain how it works or point me to a reference that might help me understand it? Many thanks for responding, Sid. Sid Price Software Design http://www.softtools.com -Original Message- From: Micah Stevens [mailto:[EMAIL PROTECTED] Sent: Sunday, March 25, 2007 9:23 PM To: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Re: Sorting Problem This doesn't work? SELECT businesses.name from businesses left join links using (businessID) left join categories using (categoryID) where category.name = 'something' order by businesses.name ASC On 03/25/2007 12:40 PM, Sid Price wrote: > Hello, > > > > I have a MySQL database design that provides an online business directory. > It consists of three tables; one with the names and addresses of the > businesses (names), one with the categories of businesses (categories), and > one that has an entry (a category ID and a business ID) for each > business/category pairing (entries), a business may be in multiple > categories. > > > > The problem I am having is after having queried the "entries" table for all > the entries for a given category I query the "names" table for each entry to > display the business name and address, I can not figure a way to sort the > displayed data by company name, The "entries" table holds the business ID > and category ID so sorting the entries that match the category doesn't help. > > > > Do I need to create a temporary table to hold the business names/addresses > in a category so that I can then sort it? > > > > Thanks, > > Sid. > > > > Sid Price Software Design > > http://www.softtools.com > > > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sorting Problem
This doesn't work? SELECT businesses.name from businesses left join links using (businessID) left join categories using (categoryID) where category.name = 'something' order by businesses.name ASC On 03/25/2007 12:40 PM, Sid Price wrote: Hello, I have a MySQL database design that provides an online business directory. It consists of three tables; one with the names and addresses of the businesses (names), one with the categories of businesses (categories), and one that has an entry (a category ID and a business ID) for each business/category pairing (entries), a business may be in multiple categories. The problem I am having is after having queried the "entries" table for all the entries for a given category I query the "names" table for each entry to display the business name and address, I can not figure a way to sort the displayed data by company name, The "entries" table holds the business ID and category ID so sorting the entries that match the category doesn't help. Do I need to create a temporary table to hold the business names/addresses in a category so that I can then sort it? Thanks, Sid. Sid Price Software Design http://www.softtools.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Making a prefixed install / hard-wired global configuration files?
Hello Jeremy Thank you for the pointer. After make clean, re-configured, re-compiled then tried, but /path/to/bin/mysql --help still shows /etc/my.cnf as the default file. Jeremy Cole wrote: Hi Nick, This should do it: --sysconfdir=DIR read-only single-machine data [PREFIX/etc] Regards, Jeremy Nick Hill wrote: Is there an easy way to build MySQL client library, server and start-up script so that /etc/my.cnf is ignored? I need to build client and server so that it in no way interacts with the global system install, and build programs against that client library which also don't interact with the global system install. My attempts so far show that I need to make hacks to get it to work, where I expected something like ./configure --prefix=/my/prefix --global_config=blah would do the trick but doesn't seem to be the case. I notice Linux distros often have their configs in places other than /etc/my.cnf. Have I overlooked something? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Making a prefixed install / hard-wired global configuration files?
Hi Nick, This should do it: --sysconfdir=DIR read-only single-machine data [PREFIX/etc] Regards, Jeremy Nick Hill wrote: Is there an easy way to build MySQL client library, server and start-up script so that /etc/my.cnf is ignored? I need to build client and server so that it in no way interacts with the global system install, and build programs against that client library which also don't interact with the global system install. My attempts so far show that I need to make hacks to get it to work, where I expected something like ./configure --prefix=/my/prefix --global_config=blah would do the trick but doesn't seem to be the case. I notice Linux distros often have their configs in places other than /etc/my.cnf. Have I overlooked something? -- high performance mysql consulting www.provenscaling.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Making a prefixed install / hard-wired global configuration files?
Is there an easy way to build MySQL client library, server and start-up script so that /etc/my.cnf is ignored? I need to build client and server so that it in no way interacts with the global system install, and build programs against that client library which also don't interact with the global system install. My attempts so far show that I need to make hacks to get it to work, where I expected something like ./configure --prefix=/my/prefix --global_config=blah would do the trick but doesn't seem to be the case. I notice Linux distros often have their configs in places other than /etc/my.cnf. Have I overlooked something? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE : RE : SPAM ? SOLVED
Hi, For those who said they were experiencing the same problem than me, I think I found the solution. My mail client is currently configured to leave the messages on the server and it seems that the message causing the problem has something specific preventing my mail server and mail client to flag it as already sent, as a result it keeps being sent again and again to my client. Solution: delete the message on the server. -- Jaqcues Brignon > -Message d'origine- > De : Jacques Brignon [mailto:[EMAIL PROTECTED] > Envoyé : dimanche 25 mars 2007 15:21 > À : 'Stephen Cook' > Cc : 'Jerry Schwartz'; 'MySQL List' > Objet : RE : SPAM ? > > Hi, > > > http://lists.mysql.com/[EMAIL PROTECTED] > > > > that link ought to help you > > > Looks a bit extreme, as long as I feel there is some value to be part of > this list :-). > > I suspect all members of the list experience the same problem; I feel it > would be more valuable to solve the problem than to nuke the list. > > It is to be noticed that if you sort your inbox by date, the problem can > remain undetected for a while as long as all the messages have the same > date and time (look for messages from Jerry Schwartz > [EMAIL PROTECTED] dated 22/03/2007 17:53); I received another > 50 of them since my last post. > > As long as Jerry does not seem to receive the messages about the > problem, if he cannot be contacted otherwise is there a way to block his > messages or to unsubscribe this address until the problem is fixed? > > -- > Jacques Brignon > > > > > > -- > 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]
Selecting next/previous records based on
I have a table of photos, that I display in chronological order on an index page: http://dotancohen.com/eng/pictures/index.php I'd like to have next (and previous) buttons on the page that displays individual photos, but the photos ID numbers are not chronological. So I need to find a way to select the photo that is next chronologically, with that information being available in a datetime column. I was thinking of using this query: SELECT * FROM photos WHERE datetime > '$datetimeOfCurrentPicture' ORDER BY datetime asc LIMIT 1,1 Is there maybe a better way to do this? Thanks in advance. Dotan Cohen http://lyricslist.com/lyrics/lyrics/44/402/pink_floyd/atom_heart_mother.html http://what-is-what.com/what_is/sql_injection.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE : SPAM ?
Hi, > http://lists.mysql.com/[EMAIL PROTECTED] > > that link ought to help you Looks a bit extreme, as long as I feel there is some value to be part of this list :-). I suspect all members of the list experience the same problem; I feel it would be more valuable to solve the problem than to nuke the list. It is to be noticed that if you sort your inbox by date, the problem can remain undetected for a while as long as all the messages have the same date and time (look for messages from Jerry Schwartz [EMAIL PROTECTED] dated 22/03/2007 17:53); I received another 50 of them since my last post. As long as Jerry does not seem to receive the messages about the problem, if he cannot be contacted otherwise is there a way to block his messages or to unsubscribe this address until the problem is fixed? -- Jacques Brignon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SPAM ?
I assume someone send the copy of the message. So that we get the duplications. Since we all reply to all the mail senders. - Original Message - From: "Jacques Brignon" <[EMAIL PROTECTED]> To: "'Banyan He'" <[EMAIL PROTECTED]>; "'LP'" <[EMAIL PROTECTED]>; "'MySQL List'" Sent: Sunday, March 25, 2007 6:07 PM Subject: RE : SPAM ? Hi, What's kind of the message you guys mentioned? Its always the same message from the same author with the same content, same date same hour, it's the one below: -Message d'origine- De : Jerry Schwartz [mailto:[EMAIL PROTECTED] Envoyé : jeudi 22 mars 2007 17:53 À : [EMAIL PROTECTED]; 'James Tu' Cc : 'MySQL List' Objet : RE: Finding a record in a result set I don't think that will work. If there are 1,000 records that qualify but none for Joe, then it will return 1,001. If Joe is in record 1 of the retrieved record set, and there are 999 other people who match the WHERE clause, then it will retrieve 1,000. Am I missing something? Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 > -Original Message- > From: Peter Brawley [mailto:[EMAIL PROTECTED] > Sent: Thursday, March 22, 2007 12:33 PM > To: James Tu > Cc: MySQL List > Subject: Re: Finding a record in a result set > > >I want to do a query of all employees from Maine, ordered > by hiring date, > >and figure out where Joe falls in that list. (i.e. which > record number > is he?) > > If 'Joe' is a unique name LOL... > > SELECT 1 + COUNT(*) > FROM employees > WHERE name <> 'Joe' AND state = 'MA' AND hiredate < ; > > PB > > James Tu wrote: > > Is there some quick way to do the following in MySQL? (I > know I can > > use PHP to search through the result set, but I wanted to see if > > there's a quick way using some sort of query) > > > > Let's say I know that Joe is from Maine. > > I want to do a query of all employees from Maine, ordered by hiring > > date, and figure out where Joe falls in that list. (i.e. > which record > > number is he?) > > > > -James > > > > > > --MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > > --No virus found in this incoming message. > > Checked by AVG Free Edition. > > Version: 7.5.446 / Virus Database: 268.18.17/730 - Release Date: > > 3/22/2007 7:44 AM > > > > > > -- > 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] -- 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 : SPAM ?
Hi, > What's kind of the message you guys mentioned? Its always the same message from the same author with the same content, same date same hour, its the one below: > -Message d'origine- > De : Jerry Schwartz [mailto:[EMAIL PROTECTED] > Envoyé : jeudi 22 mars 2007 17:53 > À : [EMAIL PROTECTED]; 'James Tu' > Cc : 'MySQL List' > Objet : RE: Finding a record in a result set > > I don't think that will work. If there are 1,000 records that qualify but > none for Joe, then it will return 1,001. If Joe is in record 1 of the > retrieved record set, and there are 999 other people who match the WHERE > clause, then it will retrieve 1,000. > > Am I missing something? > > Regards, > > Jerry Schwartz > Global Information Incorporated > 195 Farmington Ave. > Farmington, CT 06032 > > 860.674.8796 / FAX: 860.674.8341 > > > > -Original Message- > > From: Peter Brawley [mailto:[EMAIL PROTECTED] > > Sent: Thursday, March 22, 2007 12:33 PM > > To: James Tu > > Cc: MySQL List > > Subject: Re: Finding a record in a result set > > > > >I want to do a query of all employees from Maine, ordered > > by hiring date, > > >and figure out where Joe falls in that list. (i.e. which > > record number > > is he?) > > > > If 'Joe' is a unique name LOL... > > > > SELECT 1 + COUNT(*) > > FROM employees > > WHERE name <> 'Joe' AND state = 'MA' AND hiredate < ; > > > > PB > > > > James Tu wrote: > > > Is there some quick way to do the following in MySQL? (I > > know I can > > > use PHP to search through the result set, but I wanted to see if > > > there's a quick way using some sort of query) > > > > > > Let's say I know that Joe is from Maine. > > > I want to do a query of all employees from Maine, ordered by hiring > > > date, and figure out where Joe falls in that list. (i.e. > > which record > > > number is he?) > > > > > > -James > > > > > > > > > --MySQL General Mailing List > > > For list archives: http://lists.mysql.com/mysql > > > To unsubscribe: > > > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > > > > > > --No virus found in this incoming message. > > > Checked by AVG Free Edition. > > > Version: 7.5.446 / Virus Database: 268.18.17/730 - Release Date: > > > 3/22/2007 7:44 AM > > > > > > > > > > -- > > 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SPAM ?
What's kind of the message you guys mentioned? - Original Message - From: "LP" <[EMAIL PROTECTED]> To: "'MySQL List'" Sent: Saturday, March 24, 2007 10:13 PM Subject: Re: SPAM ? Hi, I also get the problem of keep receiving the email from the email list Regards KEVIN - Original Message - From: "Jacques Brignon" <[EMAIL PROTECTED]> To: "'Jerry Schwartz'" <[EMAIL PROTECTED]> Cc: "'MySQL List'" Sent: Saturday, March 24, 2007 10:45 PM Subject: SPAM ? Hi, I keep receiving this message by several dozens each day, how can this be stopped? Regards Jacques -Message d'origine- De : Jerry Schwartz [mailto:[EMAIL PROTECTED] Envoyé : jeudi 22 mars 2007 17:53 À : [EMAIL PROTECTED]; 'James Tu' Cc : 'MySQL List' Objet : RE: Finding a record in a result set I don't think that will work. If there are 1,000 records that qualify but none for Joe, then it will return 1,001. If Joe is in record 1 of the retrieved record set, and there are 999 other people who match the WHERE clause, then it will retrieve 1,000. Am I missing something? Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 > -Original Message- > From: Peter Brawley [mailto:[EMAIL PROTECTED] > Sent: Thursday, March 22, 2007 12:33 PM > To: James Tu > Cc: MySQL List > Subject: Re: Finding a record in a result set > > >I want to do a query of all employees from Maine, ordered > by hiring date, > >and figure out where Joe falls in that list. (i.e. which > record number > is he?) > > If 'Joe' is a unique name LOL... > > SELECT 1 + COUNT(*) > FROM employees > WHERE name <> 'Joe' AND state = 'MA' AND hiredate < ; > > PB > > James Tu wrote: > > Is there some quick way to do the following in MySQL? (I > know I can > > use PHP to search through the result set, but I wanted to see if > > there's a quick way using some sort of query) > > > > Let's say I know that Joe is from Maine. > > I want to do a query of all employees from Maine, ordered by hiring > > date, and figure out where Joe falls in that list. (i.e. > which record > > number is he?) > > > > -James > > > > > > --MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > > --No virus found in this incoming message. > > Checked by AVG Free Edition. > > Version: 7.5.446 / Virus Database: 268.18.17/730 - Release Date: > > 3/22/2007 7:44 AM > > > > > > -- > 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] -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SPAM ?
http://lists.mysql.com/[EMAIL PROTECTED] that link ought to help you. Jacques Brignon wrote: Hi, I keep receiving this message by several dozens each day, how can this be stopped? Regards Jacques -Message d'origine- De : Jerry Schwartz [mailto:[EMAIL PROTECTED] Envoyé : jeudi 22 mars 2007 17:53 À : [EMAIL PROTECTED]; 'James Tu' Cc : 'MySQL List' Objet : RE: Finding a record in a result set I don't think that will work. If there are 1,000 records that qualify but none for Joe, then it will return 1,001. If Joe is in record 1 of the retrieved record set, and there are 999 other people who match the WHERE clause, then it will retrieve 1,000. Am I missing something? Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Thursday, March 22, 2007 12:33 PM To: James Tu Cc: MySQL List Subject: Re: Finding a record in a result set >I want to do a query of all employees from Maine, ordered by hiring date, >and figure out where Joe falls in that list. (i.e. which record number is he?) If 'Joe' is a unique name LOL... SELECT 1 + COUNT(*) FROM employees WHERE name <> 'Joe' AND state = 'MA' AND hiredate < ; PB James Tu wrote: Is there some quick way to do the following in MySQL? (I know I can use PHP to search through the result set, but I wanted to see if there's a quick way using some sort of query) Let's say I know that Joe is from Maine. I want to do a query of all employees from Maine, ordered by hiring date, and figure out where Joe falls in that list. (i.e. which record number is he?) -James --MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] --No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.446 / Virus Database: 268.18.17/730 - Release Date: 3/22/2007 7:44 AM -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]