Re: finding next and prev record in mysql
You actually do want to use the limit clause, but use 2 parameters. The first number is which record you want to start at, the second is how many records you want. So limit 2,1 will give you the second record. It's the same query every time, you're just incrementing the first number of the limit clause. - Original Message - From: Richard Kurth [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, May 11, 2007 1:15 AM Subject: finding next and prev record in mysql How would I find the next id and the prev id in sql statement like the one below. The id number is not going to be in order so I can't do a or limit 1 on the search SELECT id FROM contacts WHERE category = '5' AND subcategory = '1' AND members_id= '8' ORDER BY lastname -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysterious 'Lost connection' errors
On Thu, May 10, 2007 at 05:17:12PM +0100, Jon Ribbens wrote: I suspect some sort of bug in the MySQLd authentication code. I've managed to discover using --debug that it's due to MySQLd failing to handle EINTR from read() in the authentication stage. I've filed a bug report: http://bugs.mysql.com/bug.php?id=28359 Does anyone know how/where to best attract the attention of a MySQL developer to this bug? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: finding next and prev record in mysql
On Fri, May 11, 2007 07:15, Richard Kurth wrote: How would I find the next id and the prev id in sql statement like the one below. The id number is not going to be in order so I can't do a or limit 1 on the search SELECT id FROM contacts WHERE category = '5' AND subcategory = '1' AND members_id= '8' ORDER BY lastname The PHP variant could look like (prev id): SELECT id FROM contacts WHERE category = '5' AND subcategory = '1' AND members_id= '8' AND lastname $_POST['lastname'] ORDER BY lastname LIMIT 1; -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), 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]
Re: finding next and prev record in mysql
Hi, Richard Kurth wrote: How would I find the next id and the prev id in sql statement like the one below. The id number is not going to be in order so I can't do a or limit 1 on the search SELECT id FROM contacts WHERE category = '5' AND subcategory = '1' AND members_id= '8' ORDER BY lastname I wrote about some solutions that work for me, here: http://www.xaprb.com/blog/2006/04/28/how-to-find-next-and-previous-records-in-sql/ I don't know if you need to do this without subqueries or not, but the methods I explain work on MySQL 3.23. I hope that helps. -- Baron Schwartz http://www.xaprb.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Enterprise 5.0 x64 Intel C++
I'm a customer of MySQL Enterprise 5.0 The binary for Red Hat Enterprise 5 x64 compiled with Intel C++ does't exists anymore ? Tnks ! -- 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: finding next and prev record in mysql
Mogens Melander wrote: On Fri, May 11, 2007 07:15, Richard Kurth wrote: How would I find the next id and the prev id in sql statement like the one below. The id number is not going to be in order so I can't do a or limit 1 on the search SELECT id FROM contacts WHERE category = '5' AND subcategory = '1' AND members_id= '8' ORDER BY lastname The PHP variant could look like (prev id): SELECT id FROM contacts WHERE category = '5' AND subcategory = '1' AND members_id= '8' AND lastname $_POST['lastname'] ORDER BY lastname LIMIT 1; I forgot to say earlier: my article is about finding both next and previous records in a single query. I don't know if this is what you need or not but I assumed it was. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqlmanager: The instance ... is being stopped forcibly. Normallyit should not happen. ...
Hi, I installed the noinstall package of mysql 5.0.41 on Windows Server 2003 R2 SP2. I manage an instance with mysqlmanager of this package. The my.ini is the following: [manager] pid-file=mysqld37.pid [mysqld37] mysqld-path=D:/Programme/MySQL/mysql-5.0.41-win32/bin/mysqld-nt.exe datadir=D:/Daten/domestic default-storage-engine=MYISAM port = 3310 socket = /tmp/mysql.sock37 log log-error relay-log=proliant-relay-bin All mysqlmanager commands seem to work, with one exception: mysql stop instance mysqld37; Query OK, 0 rows affected (35.14 sec) You see that it takes more than half a minute until the instance ist stopped. The mysqlmanager displays when runnig with --standalone: The instance 'mysqld37' is being stopped forcibly. Normallyit should not happen. Probably the instance has beenhanging. You should also check your IM setup. You see my setup above, I cannot spot any problem. The error log is the following: 070511 18:11:34 InnoDB: Started; log sequence number 0 43655 070511 18:11:34 [Warning] 'db' entry 'searchresult_eth [EMAIL PROTECTED]' had database in mixed case that has been forced to lowercase because lower_case_table_names is set. It will not be possible to remove this privilege using REVOKE. 070511 18:11:34 [Warning] 'db' entry 'eth [EMAIL PROTECTED]' had database in mixed case that has been forced to lowercase because lower_case_table_names is set. It will not be possible to remove this privilege using REVOKE. 070511 18:11:34 [Warning] 'db' entry 'eth [EMAIL PROTECTED]' had database in mixed case that has been forced to lowercase because lower_case_table_names is set. It will not be possible to remove this privilege using REVOKE. 070511 18:11:34 [Warning] 'db' entry 'eth [EMAIL PROTECTED]' had database in mixed case that has been forced to lowercase because lower_case_table_names is set. It will not be possible to remove this privilege using REVOKE. 070511 18:11:34 [Note] D:\Programme\MySQL\mysql-5.0.41-win32\bin\mysqld-nt.exe: ready for connections. Version: '5.0.41-community-nt-log' socket: '' port: 3310 MySQL Community Edition (GPL) The name of our ETH database has capital letters but that did not matter for years. In the bug database there is no problem of this kind (http://bugs.mysql.com/search.php?cmd=displaystatus[]=Allseverity=allsearch_for=mysqlmanagerdirection=ASClimit=50reorder_by=id) Do you see a problem with my setup here or do you think that this is a bug? Thanks, Uwe Galle
RE: finding next and prev record in mysql
-Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: Friday, May 11, 2007 9:25 AM To: Mogens Melander Cc: mysql@lists.mysql.com Subject: Re: finding next and prev record in mysql Mogens Melander wrote: On Fri, May 11, 2007 07:15, Richard Kurth wrote: How would I find the next id and the prev id in sql statement like the one below. The id number is not going to be in order so I can't do a or limit 1 on the search SELECT id FROM contacts WHERE category = '5' AND subcategory = '1' AND members_id= '8' ORDER BY lastname The PHP variant could look like (prev id): SELECT id FROM contacts WHERE category = '5' AND subcategory = '1' AND members_id= '8' AND lastname $_POST['lastname'] ORDER BY lastname LIMIT 1; I forgot to say earlier: my article is about finding both next and previous records in a single query. I don't know if this is what you need or not but I assumed it was. Yes I read your article but I can't figure out how to use it with my existing sql statement. This is just an example of the search sql statement it could be different than this. I all ready know what Id number I what to find the prev and the next recorded number around. Say the id number is 52. If I print out the list of ids the one before number 52 is 503 the one after is 302. How can I use your script to find these numbers using the same sql statement. I am just not sure how to incorporate it in to the search. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Path information to a database
i have a question: i have a Projects folder on my D: drive under this folder is folder by the name of the Customer then Subfolders that Corespond to the Project Ex, d:\projects .. Jobs LSRW -- Rsview --RSLogix --Drawings --Labels I need to store all this relavent Path Information into a MySQL database, so i can Link it all into the database instead of using fileshares , has anyone done this before?? on Paths and Storeing into a database anyone have code examples -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Path information to a database
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Brian, There are several ways you could store this information, either complete paths, or relative paths, something along these lines: Tables: - --- Projcts: ++ | PROJ_ID | PATH | +-+--+ | 1 | D:\Projects\Jobs\LSRW\Rsview | | 2 | D:\Projects\Jobs\LSRW\RSLogic| ++ SELECT PATH FROM Projects WHERE PROJ_ID='1' or Projects_Table ++ | PROJ_ID | PROJ_Name | PROJ_ParentPath | +-+--+ | 1 | LSRW | D:\Projects\Jobs\LSRW | ++ Project_Components ++ | COMPONENT_ID | PROJ_ID | COMPONENT_PATH| +--+-+---+ | 1| 1 | Rsview| | 2| 1 | RSLogix | | 3| 1 | Drawings | ++ To get the full path for a component: SELECT CONCAT_WS(\,Projects_Table.PROJ_ParentPath,Projects_Components.COMPONENT_PATH) AS FullComponentPath FROM Project_Components LEFT JOIN Projects_Table on Project_Components.PROJ_ID=Project_Table.PROJ_ParentPath Brian E Boothe wrote: i have a question: i have a Projects folder on my D: drive under this folder is folder by the name of the Customer then Subfolders that Corespond to the Project Ex, d:\projects .. Jobs LSRW -- Rsview --RSLogix --Drawings --Labels I need to store all this relavent Path Information into a MySQL database, so i can Link it all into the database instead of using fileshares , has anyone done this before?? on Paths and Storeing into a database anyone have code examples - -- Mike Lockhart Information Engineer ChoiceMed, Inc Email: [EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.7 (Darwin) iD8DBQFGRKjxgCgDHkdt5m8RAjjHAKCZagyUdLs1zNvSsVx/Zv3mjSwPhQCfRWdd fCcKUAQ8w1fDD39furSR8CQ= =hwmh -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Path information into database
so how would i get the Actual file path to the folder into the database, i know ill have to use a file selector in asp / php but i wanna select the folder not the Files, it would be really great if u could help me with these issues, i'm not asking u to Code anything for me , just links of examples and for helpfull boosts along the way thanks soo very much -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: finding next and prev record in mysql
This good for giving me the next recorded but it will not give me the prev recorded that is the one I am having problems with You actually do want to use the limit clause, but use 2 parameters. The first number is which record you want to start at, the second is how many records you want. So limit 2,1 will give you the second record. It's the same query every time, you're just incrementing the first number of the limit clause. - Original Message - From: Richard Kurth [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, May 11, 2007 1:15 AM Subject: finding next and prev record in mysql How would I find the next id and the prev id in sql statement like the one below. The id number is not going to be in order so I can't do a or limit 1 on the search SELECT id FROM contacts WHERE category = '5' AND subcategory = '1' AND members_id= '8' ORDER BY lastname -- 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: Path information into database
Brian E Boothe wrote: so how would i get the Actual file path to the folder into the database, i know ill have to use a file selector in asp / php but i wanna select the folder not the Files, it would be really great if u could help me with these issues, i'm not asking u to Code anything for me , just links of examples and for helpfull boosts along the way thanks soo very much Couple of good examples FileSystemObject (ASP) http://www.google.com/search?hl=enq=asp+fso ADODB (Activex Data Objects Database - ASP) http://www.google.com/search?hl=enq=mysql+adodb+asp Of course, you'll need the odbc connector... If you get stuck on specific queries this is the place to ask, all other issues I would consider: http://www.google.com/search?hl=enq=asp+help I can't say what sites are better on those searches than others. I usually just Google my issue and try and sort it out myself. When I'm really stuck I'll post a question to http://www.Tek-Tips.com You also may want to consider a reading about SQL injection attacks (I searched on ASP, but php can have the same issue if you're also not careful): http://www.google.com/search?hl=enq=asp+mysql+sql+injection I hope those few links get you headed in the right direction :-) -- Thanks, James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query Cache Behavior
Hello, I am hoping to get pointed in the right direction/save some time... I have a db in which some web services are constantly inserting/updating data. However, when I run selects from the command line mysql app, I do not see the changes that these services have made unless I do a flush tables. This is even true if I log out and log back in. So, I am guessing that it has to do with caching. Any idea why this is happening? Thanks, Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Cache Behavior
Hi, Paul J. Boyes wrote: Hello, I am hoping to get pointed in the right direction/save some time... I have a db in which some web services are constantly inserting/updating data. However, when I run selects from the command line mysql app, I do not see the changes that these services have made unless I do a flush tables. This is even true if I log out and log back in. So, I am guessing that it has to do with caching. Any idea why this is happening? It's almost certainly not the MySQL query cache, because updates invalidate the cache. You can test by adding SQL_NO_CACHE to your SELECT, thusly: SELECT SQL_NO_CACHE If this still doesn't return the results you expect, something else is happening. Are the web services not committing their transactions? That's the only other thing that comes to my mind. I'm sure I'm missing something and someone else will be able to help more. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: finding next and prev record in mysql
Hi, Richard Kurth wrote: -Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: Friday, May 11, 2007 9:25 AM To: Mogens Melander Cc: mysql@lists.mysql.com Subject: Re: finding next and prev record in mysql Mogens Melander wrote: On Fri, May 11, 2007 07:15, Richard Kurth wrote: How would I find the next id and the prev id in sql statement like the one below. The id number is not going to be in order so I can't do a or limit 1 on the search SELECT id FROM contacts WHERE category = '5' AND subcategory = '1' AND members_id= '8' ORDER BY lastname The PHP variant could look like (prev id): Yes I read your article but I can't figure out how to use it with my existing sql statement. This is just an example of the search sql statement it could be different than this. I all ready know what Id number I what to find the prev and the next recorded number around. Say the id number is 52. If I print out the list of ids the one before number 52 is 503 the one after is 302. How can I use your script to find these numbers using the same sql statement. I am just not sure how to incorporate it in to the search. Yes, now that I look more closely at your question it does require some tweaking to work. I don't know your exact data, but I'll asume: mysql select * from contacts order by lastname; +--+--+-+---+ | id | category | subcategory | lastname | +--+--+-+---+ | 456 |5 | 1 | everybody | | 483 |5 | 1 | me| | 195 |5 | 1 | them | | 182 |5 | 1 | todo el mundo | | 290 |5 | 1 | us| | 44 |5 | 1 | we| | 365 |5 | 1 | yall | | 229 |5 | 1 | you | +--+--+-+---+ The first thing to notice is category and subcategory are constants (both in my data, and in your query, because you put them in the WHERE clause), so you can factor them out. This makes the rest of the query easier to write, and then you can plug back in the category and subcategory. Suppose I'm currently on record 290, 'us'. Next is 'we' and prev is 'todo el mundo.' select if(lastname 'us', 'next', 'prev') as direction, if(lastname 'us', min(lastname), max(lastname)) as lastname from contacts where lastname 'us' group by 1 order by if(lastname 'us', 1, -1); +---+---+ | direction | lastname | +---+---+ | prev | todo el mundo | | next | we| +---+---+ Now you can add back in the other clauses: select if(lastname 'us', 'next', 'prev') as direction, if(lastname 'us', min(lastname), max(lastname)) as lastname from contacts where lastname 'us' and category = 5 and subcategory = 1 group by 1 order by if(lastname 'us', 1, -1); If you need to get the id column back, I think you will need to join this result set back to the original table to get other columns. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Cache Behavior
This is our problem: http://bugs.mysql.com/bug.php?id=27210 Thanks, Paul Baron Schwartz wrote: Hi, Paul J. Boyes wrote: Hello, I am hoping to get pointed in the right direction/save some time... I have a db in which some web services are constantly inserting/updating data. However, when I run selects from the command line mysql app, I do not see the changes that these services have made unless I do a flush tables. This is even true if I log out and log back in. So, I am guessing that it has to do with caching. Any idea why this is happening? It's almost certainly not the MySQL query cache, because updates invalidate the cache. You can test by adding SQL_NO_CACHE to your SELECT, thusly: SELECT SQL_NO_CACHE If this still doesn't return the results you expect, something else is happening. Are the web services not committing their transactions? That's the only other thing that comes to my mind. I'm sure I'm missing something and someone else will be able to help more. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
