query problem
hi everyboby, How to select latest 3 days records from the table according to the latests date. The data is like this:- name date a 02-03-01 b 02-03-15 c 02-03-20 d 02-03-20 e 02-04-28 f 02-04-28 g 02-04-30 The result should be like this:- name date g 02-04-30 f 02-04-28 e 02-04-28 c 02-03-20 d 02-03-20 Is it possible.Please help me. query,database,sql Thanks in advance. _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp. - 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
Solved (Re: 4.0.2 Replication still buggy...)
1On Fri, Apr 26, 2002 at 09:11:52AM -0700, Jeremy Zawodny wrote: I started it up and it ran for about a day before it ran into a duplicate key error. The 3.23.47 slave hasn't hit the duplicate key error, nor have any of our other slaves. So it is a 4.0.2 bug of some sort. This has been resolve, I hope. After even more tracking stuff down, I managed to isolate it to a change made in MySQL 4.0.1. The query in question was doing: INSERT INTO ... SELECT ... rather than INSERT IGNORE INTO ... SELECT ... MySQL 3.23.xx lets the first sneak by without errors even if there are duplicates. 4.0.2 does not. It stops. Since our master is 3.23 and this particular slave is 4.0.2, the two didn't agree. *sigh* I hereby declare MySQL 4.0.2 replicating from 3.23.xx to be quite stable. :-) Back to your regularly scheduled mail... Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 83 days, processed 2,168,564,677 queries (300/sec. avg) - 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
Re: Good color coded SQL editor for MySQL?
On Wed, May 01, 2002 at 10:27:57PM +0100, Stewart Douglas wrote: GNU Emacs and sql-mode. :-) -- Jeremy D. Zawodny, [EMAIL PROTECTED] Jeremy I'm a big fan of sql-mode with Emacs for accessing Sybase servers at work, how do I config it to point to MySQL on my local machine (Win2K PC) - adding a new association doesn't work as the sql mode tries to look up my server in my sybase/ini/sql.ini file. Do I need a dedicated mysql mode ? Your post suggests that I don't... Hmm. I've been using the genreic sql-mode that comes with GNU Emacs, and it works well. What (if anything) about sql-mode do you have in your .emacs file (or the windows eqivelant)? Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 83 days, processed 2,169,799,558 queries (300/sec. avg) - 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
Re: mysql speed concerns
On Wed, May 01, 2002 at 10:49:36AM -0400, Jason Yates wrote: Currently our MySQL server runs around 20-30 queries per second. The upper management decided they wanted to add about 4 times the customers in the next two or three weeks. I'm worried that MySQL on this particular box won't be able to handle the load of around 100-120 queries per second. Not to mention the CGI scripts are also getting run on the same box with apache. The system has 1gb of RAM, 1 Pentium III 700Mhz, and some ultrascsi HDs (no raid), running Red Hat 7.1 and Linux 2.4.6. What are my options here? You'll be fine if your app is well designed and your tables are properly indexed. You should be able to easily pump out 500-1000 queries per second on that hardware. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 83 days, processed 2,169,815,560 queries (300/sec. avg) - 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
foreign key ?
Hello ! How to use foreign key in mysql. I am using mysql 3.23.36 Thanx in advance Regards Shimak - 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
FreeBSD threads
Hi We using FreeBSD 4.5 and MySQL 3.23.49 with native threads... It uses a lot of CPU and mite not be using both CPU's? So we have seen that compiling with Linux threads can help Do with just ./configure --with-named-threads-libs=??? What is the complete statement? And do we need to put any thing more for freeBSD? Thanks for any help Simon - 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
Re: Help: SIGSEGV inside mysql_connect to remote server
On Wed, May 01, 2002 at 04:24:05PM -0600, Guy Davis wrote: I am having a really strange problem with a in-house C application that calls mysql_connect() and generates a segfault deep within uname() when I configure it to contact a remote server. Using a local MySQL server works fine as uname is never called. Have any of you ever seen anything like this? Is that remote server's info in /etc/hosts on the client? If not, see if that makes a difference. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 83 days, processed 2,169,809,586 queries (300/sec. avg) - 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
RE: Re: How to Count(*) with LIMIT
I have a Where clause like: select count(*) from table where LIMIT 100 Unfortunately the Count(*) ignores the LIMIT clause entirely. Why? Because the query returns only ONE row and LIMIT limits rows, not values. See ex. below: SELECT count(login) FROM accounts WHERE domain_id=1 LIMIT 3 +--+ | count(login) | +--+ |6 | +--+ It seems to me that if a select * from table where ... limit 100 returns between 0 and 100 rows, you should be able to count it. Instead the count returns 55,000 or some ridiculously large number that has no bearing on the # of rows that will actually be returned (because of the LIMIT clause). Since this is running on a webserver, I don't want it to physically count more than 100 rows. Some of the tables may be over 1 million rows and counting that many rows when only 100 rows are returned is overkill. Then why Use COUNT if You're not interested of number of records ?? Could You maybe specify what you actually want to do with the Count ?? Is there a way around this counting problem? The only solution I've come up with is to traverse all the rows returned by counting them in a loop. This seems pretty lame and I'm hoping someone can come up with a better solution. If You want to know the number of rows in the recordset returned by the query then You should use mysql_num_rows(). How You do this depends on the language been used in Your application. Mike sql,query =d0Mi= DCS.net [EMAIL PROTECTED] - 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 - 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
Re: mysql uses 99% cpu under freebsd 4.3
On Wed, May 01, 2002 at 09:15:25PM +0200, Gunnar Helliesen wrote: List, Re: http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:sss:71261:200104:iidpojcdbmgdbajh aobn#b I have this exact problem on a single-CPU P-III 500 running FreeBSD 4.5-RELEASE, generic kernel. MySQL is 4.0.1-alpha. I have tried both the mysql.com-supplied binary package and rolling my own from sources. What happens is that after anywhere from 1-2 days of uptime the mysqld process suddenly starts eating all available CPU: last pid: 25537; load averages: 1.20, 1.61, 1.43 up 32+17:01:50 20:35:06 51 processes: 2 running, 49 sleeping CPU states: 21.0% user, 0.0% nice, 77.4% system, 1.6% interrupt, 0.0% idle Mem: 208M Active, 154M Inact, 96M Wired, 27M Cache, 60M Buf, 13M Free Swap: 1024M Total, 64K Used, 1024M Free PID USERNAME PRI NICE SIZERES STATETIME WCPUCPU COMMAND 93402 mysql 54 0 299M 43988K RUN368:57 98.00% 98.00% mysqld 93461 www 2 0 14400K 9356K sbwait 0:31 0.10% 0.10% httpd MySQL still answers queries so everything works, at least for as long as I allow the server to run in this state. I've seen it too. No on my primary servers, but on some that I occasionally work on. 'iostat' shows nothing much happening: # iostat tty da0 acd0 sa0 cpu tin tout KB/t tps MB/s KB/t tps MB/s KB/t tps MB/s us ni sy in id 0 14 0.00 0 0.00 0.00 0 0.00 0.00 0 0.00 5 0 14 1 80 # Same. and 'mysqladmin processlist' shows about 45-50 processes all in Sleep command. The only process in Query command is my own 'processlist'. Same. After a quick restart of the mysqld server load drops to almost 0 and mysqld settles down to its usual modest CPU utilization: Same. To make things interesting I have another FreeBSD server, this one an SMP 2-CPU P-III 1 GHz running FreeBSD 4.4-RELEASE and MySQL 4.0.0-alpha compiled from sources. This one is under heavier load but does not display this problem! Any ideas? I'm no MySQL expert so I could use a little hand-holding in running diagnostics. Odd. The only boxes I've seen it on so far were dual-CPU FreeBSD 4.5 machines. I've heard that it also hit one single-cpu FreeBSD 4.5 machine too, but I never got all the details on that one. The trick is to figure out how to reliably reproduce the problem. Once that happens, fixing it will be rather easy I think. So if anyone comes up with a way of doing it, please speak up. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 83 days, processed 2,169,755,750 queries (300/sec. avg) - 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
get one name for each row
I have one table with about 1000 hotels and name or the city they belong. I want to run a query to get one row for every city only, to put it in a pull down menu in the search form. How is that? Makis - 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
Re: How to Count(*) with LIMIT
Can't you use the num_rows funtion to provide the count? George - Original Message - From: mos [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, May 02, 2002 6:11 AM Subject: Re: How to Count(*) with LIMIT I have a Where clause like: select count(*) from table where LIMIT 100 Unfortunately the Count(*) ignores the LIMIT clause entirely. Why? It seems to me that if a select * from table where ... limit 100 returns between 0 and 100 rows, you should be able to count it. Instead the count returns 55,000 or some ridiculously large number that has no bearing on the # of rows that will actually be returned (because of the LIMIT clause). Since this is running on a webserver, I don't want it to physically count more than 100 rows. Some of the tables may be over 1 million rows and counting that many rows when only 100 rows are returned is overkill. Is there a way around this counting problem? The only solution I've come up with is to traverse all the rows returned by counting them in a loop. This seems pretty lame and I'm hoping someone can come up with a better solution. Mike sql,query - 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 - 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
Fw: How to Count(*) with LIMIT
Sorry, taken out of context please ignore my first response. George - Original Message - From: George Pitcher [EMAIL PROTECTED] To: [EMAIL PROTECTED]; mos [EMAIL PROTECTED] Sent: Thursday, May 02, 2002 9:46 AM Subject: Re: How to Count(*) with LIMIT Can't you use the num_rows funtion to provide the count? George - Original Message - From: mos [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, May 02, 2002 6:11 AM Subject: Re: How to Count(*) with LIMIT I have a Where clause like: select count(*) from table where LIMIT 100 Unfortunately the Count(*) ignores the LIMIT clause entirely. Why? It seems to me that if a select * from table where ... limit 100 returns between 0 and 100 rows, you should be able to count it. Instead the count returns 55,000 or some ridiculously large number that has no bearing on the # of rows that will actually be returned (because of the LIMIT clause). Since this is running on a webserver, I don't want it to physically count more than 100 rows. Some of the tables may be over 1 million rows and counting that many rows when only 100 rows are returned is overkill. Is there a way around this counting problem? The only solution I've come up with is to traverse all the rows returned by counting them in a loop. This seems pretty lame and I'm hoping someone can come up with a better solution. Mike sql,query - 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 - 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
Re: query problem
Hi, This might work for you: select @tempvar := max(datecolumn) from tablename group by datecolumn order by datecolumn desc limit 3; select * from tablename where datecol = @tempvar order by datecolumn desc; Anvar. At 06:12 AM 02/05/2002 +, you wrote: hi everyboby, How to select latest 3 days records from the table according to the latests date. The data is like this:- name date a 02-03-01 b 02-03-15 c 02-03-20 d 02-03-20 e 02-04-28 f 02-04-28 g 02-04-30 The result should be like this:- name date g 02-04-30 f 02-04-28 e 02-04-28 c 02-03-20 d 02-03-20 Is it possible.Please help me. query,database,sql Thanks in advance. _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp. - 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 - 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
RE: mysqlimpor and fixed length files.
Hi! Ramon == Ramon Arias [EMAIL PROTECTED] writes: cut Ramon I haven't done the big file yet... I did a smaller one that was 50 gigs, and Ramon it loaded fine after a few hours. (about 3) in a pc with tons of ram and Ramon scsi stripped drives. However each index take about 3-5 hours to create. For Ramon some reason it seems faster to add the indexes after importing the data. I Ramon don't understand why MySQL wants to copy the entire database over every time Ramon I add an index... It seems to me that it spends most of the time and Ramon resources coping the file over and relatively little processing time Ramon creating the actual index. Hmmm... I wonder whether there is a to streamline Ramon this process. The ALTER TABLE in MySQL is very flexible as it has a lot of non-standard but useful options. One drawback is that currently it's always doing a total reconstructions of the data and index. We plan to fix this in 4.1 (Maybe earlier if someone would like to sponsor this project). In other words, if you plan to create a lot of INDEX, you should do this with a single ALTER TABLE command. If you do a LOAD DATA INFILE into an empty file, MySQL will delay not unique index creation until all data is loaded. To get more speed for index creation, you can set the myisam_sort_buffer_size variable to a high value. Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland ___/ www.mysql.com - 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
Self Join in mysql
This question I am posting for a friend Below is post to another list... How does one create a self-join in MySQL tables? I've only played a little with SQL selects so far, some inner joins, and now I have to create a many-to-many relationship between records in the same table. So, I have a links table with the local key and the foreign key, that will link record A to record B, but I'd like the same record to also link back from B to A.. This is what I have now, 'table' is the related table, in this case the same as the current table, local_id the key value of the current record: SELECT t.id, t.title FROM table AS t INNER JOIN links AS l ON t.id = l.foreign_id WHERE l.local_id = local_id; Do I just run this query twice, swapping the id_fields, and merge the result sets? Or do I have to use two records for each both-ways link? Or is there an ugly way that puts both ids in the same field and does a LIKE query? How is this usually solved? One reply came in, and is below, with more info If I'm not mistaken you need to alias the the same table twice. I'm also not sure whether MySQL's query optimizer can deal with it: SELECT t1.id, t1.title , t2.name FROM table AS t1, table AS t2 WHERE t2.link = t1.id This is a self-join one-to-many though, isn't it? BTW, I think self joins are usually a sign of poor schema design - kind of a filemaker thingy. The only time I found it semi-useful was in building a hierarchical concept in a SQL table. Here, the records are people, and each person is linked to any number of other persons via relationships such as father, grandmother, aunt, husband etc. Record A can have a daughter-link to record B, which has a father-link back to record A. Both have many more links to other records, with some overlap (both can link to C, one as brother-relationship, one as son-relationship). One has to be able to add/remove any link(s). The whole picture doesn't need to be consistent, two records can link to eachother each using a grandfather-relationship, it will be up to the admin to avoid/fix this. I'm open to suggestions :-) - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. - 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
RE: query problem
* saraswathy saras How to select latest 3 days records from the table according to the latests date. The data is like this:- name date a 02-03-01 b 02-03-15 c 02-03-20 d 02-03-20 e 02-04-28 f 02-04-28 g 02-04-30 The result should be like this:- name date g 02-04-30 f 02-04-28 e 02-04-28 c 02-03-20 d 02-03-20 Is it possible.Please help me. Yes, use ORDER BY ... DESC and LIMIT: SELECT * FROM table ORDER BY date_field DESC LIMIT 3 URL: http://www.mysql.com/doc/S/E/SELECT.html -- Roger - 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
Reply to this lists goes to sender!!! ????
Ehm, is my mailer boofed, how come if I try to reply to a list message, it is set to go to the sender not the list? Am I on a weird sql list here? - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. - 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
Re: mysql speed concerns
.sorry ... RFC 793 - Original Message - From: Gelu [EMAIL PROTECTED] To: Michael Chang [EMAIL PROTECTED] Cc: Svensson, B.A.T. (HKG) [EMAIL PROTECTED]; Jason Yates [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, May 02, 2002 10:52 AM Subject: Re: mysql speed concerns TCP/IP is a protocol for data interchange, host to host, on the network (RFC731). Sockets ,indeed , are also used for IPC. _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Michael Chang [EMAIL PROTECTED] To: Gelu [EMAIL PROTECTED] Cc: Svensson, B.A.T. (HKG) [EMAIL PROTECTED]; Jason Yates [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, May 01, 2002 10:29 PM Subject: Re: mysql speed concerns Technically speaking, TCP/IP communication *is* a form of IPC. IPC isn't restricted to semaphores or message queues, etc. I think that's what he meant (i.e.: mysql client on one box communicating via TCP/IP to a MySQL server on another box). Michael On Wed, 1 May 2002, Gelu wrote: Hi, About at the same IPC(semaphore,share memory,message) i make references too.Are more ways to made a inter process communication mechanism. For example, in my applications i don't use semaphores and messages.This are system functions available from the Kernel. If you type ipcs you can see that mySQL don't use the system functions for inter process communication. My opinion is referencing at, if this CGI (about Shaun said early) retrieve huge data from RDBMS ,sure can create unbalanced processes. For this reason i said it's strongly recommended to setup MySQL on the other host. I have a bad experience with INFORMIX mounted on SCO running in a dual XEON machine.And i think SCO it's more stable than Linux. Of course who use mainframe don't must have any concern. Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Svensson, B.A.T. (HKG) [EMAIL PROTECTED] To: Gelu [EMAIL PROTECTED] Cc: Jason Yates [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, May 01, 2002 7:32 PM Subject: RE: mysql speed concerns With IPC I mean Inter Process Communication - any process that wants to talk with something else than with it self _needs_ necessarily IPC. Particularly an RDBMS needs IPC - otherwise it is useless. Anyhow. one way one might get faster performance could be to (if possible with the OS) move the swap space from disk into RAM instead. Or even better for speed and response time don't use swap at all! - but then you must know how much RAM memory your system will consume - otherwise you might get into big trouble. It is not impossible to estimate this, but tricky. One way could be to limit number of connections/jobs on the machine, and then assume the worst case scenario from this. -Original Message- From: Gelu Sent: Wednesday, May 01, 2002 6:12 PM To: Svensson, B.A.T. (HKG); Shaun Bramley Cc: Jason Yates; [EMAIL PROTECTED] Subject: Re: mysql speed concerns Hi, MySQL don't seems to use IPC.!?...Strange ..!?...And if Shaun will need real-time response from RDBMS,in my opinion, is strongly recomended to set up MySQL on the other host. Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Svensson, B.A.T. (HKG) [EMAIL PROTECTED] To: Shaun Bramley [EMAIL PROTECTED] Cc: Jason Yates [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, May 01, 2002 6:20 PM Subject: RE: mysql speed concerns An webserver uses mostly CPU resources while a DB uses I/O, or? Separation between the webserver and the RDBMS also suggest increased delays with IPC. I think one needs to find a balance somewhere in between, and I guess the only way to tell is to actually measure the performance of the system to see what's need to be adjusted in order to get better performance. I would start out by collecting some stats from the RDBMS and the OS, lets say during a weeks time or so, and then just create dummy instances that perform the very same things and slowly increases the load in steps in order to see what happens with the system. file://Anders -Original Message- From: Shaun Bramley [mailto:[EMAIL PROTECTED]]
RE: foreign key ?
* shimak How to use foreign key in mysql. I am using mysql 3.23.36 Just do it... :) A foreign key is a field in a table referencing the primary key of another table. In mysql you don't need to specify which columns are foreign keys, you just use them in a join, and mysql will do the rest. Consider this basic schema: Person: id int, name varchar(30) Phone: phone varchar(15),person int The Person table contains person, each person have an unique id, defined as the primary key. The Phone table contains phone numbers. The phone column is the primary key for the Phone table, two different persons can not have the same phone number in this schema, but some persons may have multiple phone numbers. The person column of the Phone table is an integer, and in this imaginary application, this is used as a foreign key to the Person table. Mysql does not need to know this, it is resolved when we do a query: SELECT name,phone FROM Person,Phone WHERE name LIKE Baklund% AND Phone.person = Person.id; The final row of this query takes care of the foreign key relationship. URL: http://www.mysql.com/doc/J/O/JOIN.html I should also mention the consept of forreign key constraints, the possibility to tell mysql which column is a foreign key to which table, and to make mysql enforce the integrity of the relationship. This is rather new in mysql, available from version 3.23.43b. URL: http://www.mysql.com/doc/S/E/SEC445.html -- Roger - 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
Re: mysql speed concerns
TCP/IP is a protocol for data interchange, host to host, on the network (RFC731). Sockets ,indeed , are also used for IPC. _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Michael Chang [EMAIL PROTECTED] To: Gelu [EMAIL PROTECTED] Cc: Svensson, B.A.T. (HKG) [EMAIL PROTECTED]; Jason Yates [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, May 01, 2002 10:29 PM Subject: Re: mysql speed concerns Technically speaking, TCP/IP communication *is* a form of IPC. IPC isn't restricted to semaphores or message queues, etc. I think that's what he meant (i.e.: mysql client on one box communicating via TCP/IP to a MySQL server on another box). Michael On Wed, 1 May 2002, Gelu wrote: Hi, About at the same IPC(semaphore,share memory,message) i make references too.Are more ways to made a inter process communication mechanism. For example, in my applications i don't use semaphores and messages.This are system functions available from the Kernel. If you type ipcs you can see that mySQL don't use the system functions for inter process communication. My opinion is referencing at, if this CGI (about Shaun said early) retrieve huge data from RDBMS ,sure can create unbalanced processes. For this reason i said it's strongly recommended to setup MySQL on the other host. I have a bad experience with INFORMIX mounted on SCO running in a dual XEON machine.And i think SCO it's more stable than Linux. Of course who use mainframe don't must have any concern. Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Svensson, B.A.T. (HKG) [EMAIL PROTECTED] To: Gelu [EMAIL PROTECTED] Cc: Jason Yates [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, May 01, 2002 7:32 PM Subject: RE: mysql speed concerns With IPC I mean Inter Process Communication - any process that wants to talk with something else than with it self _needs_ necessarily IPC. Particularly an RDBMS needs IPC - otherwise it is useless. Anyhow. one way one might get faster performance could be to (if possible with the OS) move the swap space from disk into RAM instead. Or even better for speed and response time don't use swap at all! - but then you must know how much RAM memory your system will consume - otherwise you might get into big trouble. It is not impossible to estimate this, but tricky. One way could be to limit number of connections/jobs on the machine, and then assume the worst case scenario from this. -Original Message- From: Gelu Sent: Wednesday, May 01, 2002 6:12 PM To: Svensson, B.A.T. (HKG); Shaun Bramley Cc: Jason Yates; [EMAIL PROTECTED] Subject: Re: mysql speed concerns Hi, MySQL don't seems to use IPC.!?...Strange ..!?...And if Shaun will need real-time response from RDBMS,in my opinion, is strongly recomended to set up MySQL on the other host. Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Svensson, B.A.T. (HKG) [EMAIL PROTECTED] To: Shaun Bramley [EMAIL PROTECTED] Cc: Jason Yates [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, May 01, 2002 6:20 PM Subject: RE: mysql speed concerns An webserver uses mostly CPU resources while a DB uses I/O, or? Separation between the webserver and the RDBMS also suggest increased delays with IPC. I think one needs to find a balance somewhere in between, and I guess the only way to tell is to actually measure the performance of the system to see what's need to be adjusted in order to get better performance. I would start out by collecting some stats from the RDBMS and the OS, lets say during a weeks time or so, and then just create dummy instances that perform the very same things and slowly increases the load in steps in order to see what happens with the system. file://Anders -Original Message- From: Shaun Bramley [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 01, 2002 5:13 PM To: Jason Yates; [EMAIL PROTECTED] Subject: Re: mysql speed concerns Hello, The first thing that I would do would be to separate the DB and apache. Set the DB up so that it is on a box of it's own. The system cannot serve pages if it is crunching through the db tables looking for information (and
Re: query problem
Uhm, 3? Didn't you wrote 5? If you want just 3 do this: SELECT name,date FROM dates ORDER BY date LIMIT 0,3 regars, Jan - Original Message - From: saraswathy saras [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, May 02, 2002 8:12 AM Subject: query problem hi everyboby, How to select latest 3 days records from the table according to the latests date. The data is like this:- name date a 02-03-01 b 02-03-15 c 02-03-20 d 02-03-20 e 02-04-28 f 02-04-28 g 02-04-30 The result should be like this:- name date g 02-04-30 f 02-04-28 e 02-04-28 c 02-03-20 d 02-03-20 Is it possible.Please help me. query,database,sql Thanks in advance. _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp. - 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 - 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
Re: query problem
Hi, This might work for you: select @tempvar := max(datecolumn) from tablename group by datecolumn order by datecolumn desc limit 3; select * from tablename where datecol = @tempvar order by datecolumn desc; Anvar. At 06:12 AM 02/05/2002 +, you wrote: hi everyboby, How to select latest 3 days records from the table according to the latests date. The data is like this:- name date a 02-03-01 b 02-03-15 c 02-03-20 d 02-03-20 e 02-04-28 f 02-04-28 g 02-04-30 The result should be like this:- name date g 02-04-30 f 02-04-28 e 02-04-28 c 02-03-20 d 02-03-20 Is it possible.Please help me. query,database,sql Thanks in advance. _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp. - 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 - 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
Re: How to Count(*) with LIMIT
Hi, LIMIT is applied AFTER select is executed and limits then number of rows returned to the client. Since SELECT COUNT(*)... generates a single row, LIMIT is useless. You can do this : SELECT somefield from LIMIT 100 and check the number of rows actually returned. I don't know which programming language you use but you should have an API function that returns the number of rows (numrows() in Perl) so that you don't need a loop to count them. (We use that trick and it is really fast as long as the WHERE clause can use an index). Hope this help -- Joseph Bueno NetClub/Trader.com mos wrote : I have a Where clause like: select count(*) from table where LIMIT 100 Unfortunately the Count(*) ignores the LIMIT clause entirely. Why? It seems to me that if a select * from table where ... limit 100 returns between 0 and 100 rows, you should be able to count it. Instead the count returns 55,000 or some ridiculously large number that has no bearing on the # of rows that will actually be returned (because of the LIMIT clause). Since this is running on a webserver, I don't want it to physically count more than 100 rows. Some of the tables may be over 1 million rows and counting that many rows when only 100 rows are returned is overkill. Is there a way around this counting problem? The only solution I've come up with is to traverse all the rows returned by counting them in a loop. This seems pretty lame and I'm hoping someone can come up with a better solution. Mike sql,query - 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
Re: deleted tabels in innodb file
On Wed, May 01, 2002 at 04:10:38PM +0200, Svend Erik H. Jørgensen wrote: Some how i have manged to delete a database, i'm not quite sure how. When i try to create the table's i get an error : 020501 15:08:38 InnoDB: Error: table noah/user already exists in InnoDB internal InnoDB: data dictionary. Have you deleted the .frm file InnoDB: and not used DROP TABLE? Have you used DROP DATABASE InnoDB: for InnoDB tables in MySQL version = 3.23.43? InnoDB: See the Restrictions section of the InnoDB manual. InnoDB: You can drop the orphaned table inside InnoDB by InnoDB: creating an InnoDB table with the same name in another InnoDB: database and moving the .frm file to the current database. InnoDB: Then MySQL thinks the table exists, and DROP TABLE will InnoDB: succeed. Is there anyway to restore the table design's ? Grab a copy of the deleted .frm file(s) from your most recent backup and put 'em back. -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 83 days, processed 2,169,823,962 queries (300/sec. avg) - 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
Re: get one name for each row
Try this
Table : col1 : col2
1. Hotel 1
select name = my_selection
?php
include config.php;
$res = mysql_query(SELECT * FROM tname);
while ($row = mysql_fetch_array($res))
{
$col1 = $row[col1];
$col2 = $row[col2];
option value = \$col1\ $col2
}
/select
?
Pradeep
Naharonline.com
- Original Message -
From: savaidis [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Thursday, May 02, 2002 1:37 PM
Subject: get one name for each row
I have one table with about 1000 hotels and name or the city they belong.
I want to run a query to get one row for every city only, to put it in a
pull down menu in the search form.
How is that?
Makis
-
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
-
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
transactions
I would greately appreciate if someone could advise me on which version of MySQL supports transactions ? Thanks, John - 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
Re: Re: How to Count(*) with LIMIT
mos, Thursday, May 02, 2002, 8:11:33 AM, you wrote: m I have a Where clause like: m select count(*) from table where LIMIT 100 m Unfortunately the Count(*) ignores the LIMIT clause entirely. Why? Because first of all SELECT is executed and then LIMIT is applied. m It seems to me that if a select * from table where ... limit 100 returns m between 0 and 100 rows, you should be able to count it. You can use functions that are present in programming languages (f.e. mysql_num_rows() in C API). m Instead the count m returns 55,000 or some ridiculously large number that has no bearing on the m # of rows that will actually be returned (because of the LIMIT clause). m Since this is running on a webserver, I don't want it to physically count m more than 100 rows. Some of the tables may be over 1 million rows and m counting that many rows when only 100 rows are returned is overkill. m Is there a way around this counting problem? The only solution I've come up m with is to traverse all the rows returned by counting them in a loop. This m seems pretty lame and I'm hoping someone can come up with a better solution. m Mike -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - 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
Re: foreign key ?
shimak, Thursday, May 02, 2002, 11:22:38 AM, you wrote: s How to use foreign key in mysql. :) I think checking MySQL manual will be usefull for you: http://www.mysql.com/doc/S/E/SEC445.html http://www.mysql.com/doc/e/x/example-Foreign_keys.html http://www.mysql.com/doc/A/N/ANSI_diff_Foreign_Keys.html You can find examples of foreign keys usage there, description of syntax of a foreign key constraint and some notes about foreign keys. s I am using mysql 3.23.36 It's a bit outdated version. It would be better to upgrade MySQL server. Since 3.23.36 there were added a lot of features for foreign key constraints and fixed a lot of bugs. s Regards s Shimak -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - 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
Re: get one name for each row
savaidis, Thursday, May 02, 2002, 11:07:18 AM, you wrote: s I have one table with about 1000 hotels and name or the city they belong. s I want to run a query to get one row for every city only, to put it in a s pull down menu in the search form. s How is that? Try SELECT DISTINCT city FROM your_table; s Makis -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - 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
Re: transactions
John, Thursday, May 02, 2002, 3:17:59 PM, you wrote: JN I would greately appreciate if someone could advise me on which version of JN MySQL supports transactions ? Transaction is supported with transaction-safe tables (InnoDB, BDB). http://www.mysql.com/doc/T/a/Table_types.html JN Thanks, JN John -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - 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
Re: Grants, rights, permissions
Thomas, Wednesday, May 01, 2002, 9:39:24 PM, you wrote: TS I am having some trubble understanding, how permissions work with MySQL: TS I've set up user, database, and host permissions: TS User: Hosts:PW: Permissions: TS tps Any pwAll TS Anonymous 192.168.107.204 ''None TS Database: User: Hosts: Permissions: TS duwtAnonymous Any All TS Databases: Hosts: Permissions: TS Any 192.168.107.0/24 All TS Now, if I understand correctly I might connect to database duwt as user TS tps from any host. But trying this I can't connect to database duwt. TS Why? Yes, it should work, I tested. Check if there are any other conflicting entries in privilege tables. Seems, you gave incomplete info. If you set up privileges directly editing privileges tables don't forget to execute FLUSH PRIVILEGES. -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - 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
RE: get one name for each row
[snip] I have one table with about 1000 hotels and name or the city they belong. I want to run a query to get one row for every city only, to put it in a pull down menu in the search form. How is that? [/snip] SELECT DISTINCT city FROM tblFOO So if you have this table; +---++ | City | Hotel| +---++ | Houston| Hotel A | | Houston| Hotel B | | Houston| Hotel C | | San Antonio | Hotel A | | Dallas | Hotel A | | Detroit | Hotel A | | Detroit | Hotel B | +---++ The query will return; +---+ | City | +---+ | Houston| | San Antonio | | Dallas | | Detroit | +---+ Hope this helps! Jay Blanchard - 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
Sorting letters in CZech
Realy BIG PROBLEM czech users! In czech alphabet is letter (#138 or alt+0138), (#154 or alt+0154), the problem is with sorting some datas with this letter in front. For example names are: Adam, Frank, Eve, Martin, Zoe, omin , William, Steve if you sort it (normaly) it should looks like this: Adam Eve Frank Martin Steve omin William Zoe but sorting in MySQL looks like this: Adam Eve Frank Martin Steve William Zoe omin it is same with lower-case version of . This letter stands after letter S. You should/could repair this cause when user has for example database with items in shop and he will print it all item starting with or will be on the end of output. I managing company database with all our workers and co-workers and my boss is angry cause of this. I have to sort it manualy, it is not a fun. So, please repair it or, reply to me with solution of this prob. Thanx a lot, Mike ZDARMA a RYCHL zasln SMS z www.posliSMS.cz - 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
Re: How to Count(*) with LIMIT
- Original Message - From: mos [EMAIL PROTECTED] I have a Where clause like: select count(*) from table where LIMIT 100 Unfortunately the Count(*) ignores the LIMIT clause entirely. Why? You could use least(). mysql select least(count(*),30) from ct; ++ | least(count(*),30) | ++ | 30 | ++ 1 row in set (0.00 sec) mysql select count(*) from ct; +--+ | count(*) | +--+ | 43 | +--+ 1 row in set (0.00 sec) Cheers, Ryan Fox, Geek email: [EMAIL PROTECTED] BackWatcher, Inc. web: http://backwatcher.com - 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
RE: Self Join in mysql
This question I am posting for a friend Below is post to another list... As always the answer is: It depends on! ;) How does one create a self-join in MySQL tables? I've only played a little with SQL selects so far, some inner joins, and now I have to create a many-to-many relationship between records in the same table. So, I have a links table with the local key and the foreign key, that will link record A to record B, but I'd like the same record to also link back from B to A.. This is what I have now, 'table' is the related table, in this case the same as the current table, local_id the key value of the current record: SELECT t.id, t.title FROM table AS t INNER JOIN links AS l ON t.id = l.foreign_id WHERE l.local_id = local_id; Do I just run this query twice, swapping the id_fields, and merge the result sets? Or do I have to use two records for each both-ways link? Or is there an ugly way that puts both ids in the same field and does a LIKE query? How is this usually solved? By an additional table actin holding relation information. One reply came in, and is below, with more info If I'm not mistaken you need to alias the the same table twice. I'm also not sure whether MySQL's query optimizer can deal with it: SELECT t1.id, t1.title , t2.name FROM table AS t1, table AS t2 WHERE t2.link = t1.id This is a self-join one-to-many though, isn't it? Yes and no, because the property of being one-to-may has not to do with the query it self, rather the data stored in the table (e.g. the intention with the table). For example: table A defines as: Id int, Person vrchar, Maried_to int select * from A Id Personmarried_to 1 Bob 3 2 John 4 3 Berlinda 1 4 Jane 2 select t1.person, t2.person from a as t1, A as t2 where married_to = Id while create a symmetric relation (eg if A is married to B then B is also married to A) However, this is not a many to many relation but a 1-1 relation. Since one person is married to one and only one person. However imagine we have an nochristiona tradition, then it might turn out to be like this: Id Person married_to 1 Bob 3 2 John4 3 Berlinda1 4 Jane2 5 Beth1 And then we will have a one-to-many situation since select t1.person, t2.person from a as t1, A as t2 where married_to = Id and t2.person = 'Bob' will yield the result: t1.persont2.person Berlinda Bob Beth Bob But this is kind of a strange relation, since Both Beth and Berlinda is married with Bob, but Bob is claimed to only be married with Berlinda so we introduce 1 Bob 5 and run the query: select t1.person, t2.person from a as t1, A as t2 where married_to = Id and NOT ( and NOT t1.Person = 'Bob' and NOT t2.person = 'Bob' ) and then suddenly we se that we have a many-to-may relation: t1.persont2.person Berlinda Bob Beth Bob Bob Berlinda Bob Beth Finaly: It is possible to get rid of the symmetric properties but that is the advance course we wont have today (e.g. I need to do some real job to.:) BTW, I think self joins are usually a sign of poor schema design - kind of a filemaker thingy. The only time I found it semi-useful was in building a hierarchical concept in a SQL table. Here, the records are people, and each person is linked to any number of other persons via relationships such as father, grandmother, aunt, husband etc. Record A can have a daughter-link to record B, which has a father-link Any many to many relation could preferable be model with a relation table carrying primary keys and a kind of semantic key since you then avoid duplication of records as in my example above. The example above could be modeled as such: Table A: Id Person Table A-into-A: Id1 Type Id2 Table A: Id PersonSex 1 Bob 1 2 John 1 3 Berlinda 0 4 Jane 0 5 Sam 1 Table A-into-A 1 Married 3 2 Married 4 3 Married 1 4 Married 2 2 Father 1 4 Father 1 1 Father 5 Father to could be searched as (not tested): Select t1.person, t2.person from a as t1, A-ito-A as t2 where t1.id = t2.id and t2.id = t1.id and Type = 'Father' and t1.Sex = 1 and t2.Sex = 1 Then t1.person is father to t2.person, and t2.person is sun to t1.person It is also possible to extract, for ex, only homosexual relation with this kind of model, so one needs to consider what kinds of information that one should store, and how to store it in order to prevent abuse of the stored information. (Remember how SS/Gestapo abused the city population archive of the Netherlands during the Second world war!) DISCLAIMER: I haven't tested any code above, so there is probably some errors within it somewhere - if you have trouble let me know and I will make an errata. back to record A. Both have many more links to other records, with
RE: [thelist] MySQL to Excel Problem Pivot Table SOLVED!
Fascinating. But how do you build the SELECT? Seems to me you would first have to SELECT RecordID FROM tblClass10 and then script an iteration to build the whole series of IF clauses. Or did I miss something? Andrew Hazen -Original Message- From: Gabriel [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 01, 2002 5:16 PM To: Jay Blanchard; [EMAIL PROTECTED] Subject: Re: [thelist] MySQL to Excel Problem Pivot Table SOLVED! Ahh, the joy of cross-tabulation. Whee. :) Jay Blanchard wrote: Sorry for the cross-post, both lists contributed to a solution. Here is a query (using MySQL 3.23) that will return cross tab, or pivot table information; mysql select RecordDate, - IF(RecordID='100101',count(*),0) AS 100101, - IF(RecordID='100118',count(*),0) AS 100118, - IF(RecordID='100119',count(*),0) AS 100119, - IF(RecordID='100131',count(*),0) AS 100131, - IF(RecordID='100132',count(*),0) AS 100132, - IF(RecordID='100135',count(*),0) AS 100135, - IF(RecordID='100137',count(*),0) AS 100137 - from tblClass10 - group by RecordDate; Returns; ++++++++ + | RecordDate | 100101 | 100118 | 100119 | 100131 | 100132 | 100135 | 100137 | ++++++++ + | 2001-10-26 | 3 | 0 | 0 | 0 | 0 | 0 | 0 | | 2001-10-31 | 1 | 0 | 0 | 0 | 0 | 0 | 0 This table contains over 5 million records. Nothing needs to be done in PHP except run this query and place the results. I feel like rudy today! :) Jay -- Gabriel Cain Unix Systems Administrator [EMAIL PROTECTED] Dialup USA, Inc. 888-460-2286 ext 208 - 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 - 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
Fw: data compare and duplicate entries..
hi, I was looking for an SQL statement or script that whould allow me to compare data when it was entered into a mysql-php field on a web page so that it checks whether the data entered has not been used before in the database.. In short, when data needs to be entered, the statement/script must check in the database whether the data is not already entered into the database and if its there, return with an error.. and not allow the data to be entered... if the data is not there, then it may be entered.. Thanx Willem Pretorius - 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
Re: Problem with SELECT
Unfortunately, you did not tell us what version you are running, or what platform you are running it on. Try rebuilding the indexes with myisamchk or whatever is appropriate for your table type. Sheni R. Meledath wrote: Hello: I have a problem while selecting the records from a table. This table has got 15494 records. But when selecting its displaying only 1745 records. ### $result=mysql_query(SELECT COUNT(*) as numrows FROM $table2); $row=mysql_fetch_array($result); $num_rows2 = $row[numrows]; echo $num_rows2; =15494 $result=mysql_query(select * from $table2); $num_rows1=mysql_num_rows($result); echo $num_rows1; =1745 ### Using the second command I can't display all the records. But using separate select statement I can select display any record which is not displayed in the above case. ### $result=mysql_query(select * from $table2 where id='15000'); $row=mysql_fetch_array($result); echo $row[name]; =sheni ### Could you please help me to figure it out. Regards Sheni R Meledath [EMAIL PROTECTED] - 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 mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - 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 mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problem with SELECT
Many Thanks for your quick reply. The MySQL version is 3.22 on Unix/FreeBSD/Apache. thanks At 08:43 AM 5/2/02 -0500, Gerald Clark wrote: Unfortunately, you did not tell us what version you are running, or what platform you are running it on. Try rebuilding the indexes with myisamchk or whatever is appropriate for your table type. Sheni R. Meledath wrote: Hello: I have a problem while selecting the records from a table. This table has got 15494 records. But when selecting its displaying only 1745 records. ### $result=mysql_query(SELECT COUNT(*) as numrows FROM $table2); $row=mysql_fetch_array($result); $num_rows2 = $row[numrows]; echo $num_rows2; =15494 $result=mysql_query(select * from $table2); $num_rows1=mysql_num_rows($result); echo $num_rows1; =1745 ### Using the second command I can't display all the records. But using separate select statement I can select display any record which is not displayed in the above case. ### $result=mysql_query(select * from $table2 where id='15000'); $row=mysql_fetch_array($result); echo $row[name]; =sheni ### Could you please help me to figure it out. Regards Sheni R Meledath [EMAIL PROTECTED] - 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 mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php Sheni R Meledath [EMAIL PROTECTED] - 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 mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: data compare and duplicate entries..
Not sure of your scripting language, but this logic should work. Just apply it to each web_form variable posted. Select count(*) as number FROM table WHERE field=value_from_web_form If number==0 then do your inserts Else Echo Sorry, field=value_from_web_form data already exists.; Reset form Andrew Hazen -Original Message- From: Tech @NorthWeb [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 02, 2002 9:32 AM To: mysql Subject: Fw: data compare and duplicate entries.. hi, I was looking for an SQL statement or script that whould allow me to compare data when it was entered into a mysql-php field on a web page so that it checks whether the data entered has not been used before in the database.. In short, when data needs to be entered, the statement/script must check in the database whether the data is not already entered into the database and if its there, return with an error.. and not allow the data to be entered... if the data is not there, then it may be entered.. Thanx Willem Pretorius - 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 - 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
finding values in TableA not in TableB
I'm sure this question has been asked before, but I received an error while attempting to search the list archives - so I apologize for redunancy. I have two tables, events and events_terms, that have a common key, events_ID. Now I want to find the rows in events_terms that have an events_ID that does not match a corresponding row in events. I came up with an SQL statement that seems to work but I want to make sure that I'm not missing anything. Here's the statement: select events_terms.* from events_terms left join events on events_terms.events_ID=events.events_ID where isnull(events.events_ID); Can anyone tell me if that looks like it should have the desired effect? It seems to, but I am going to eventually be using this to delete rows - not find them - so I need to be sure. Thanks, Duncan - 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
RE: [thelist] MySQL to Excel Problem Pivot Table SOLVED!
[snip]
Fascinating. But how do you build the SELECT? Seems to me you would
first have to SELECT RecordID FROM tblClass10 and then script an
iteration to build the whole series of IF clauses. Or did I miss
something?
[/snip]
You could do that for the RecordID if you do not know what they are (I knew
what RecordID's existed, so I could specifiy an IF statement for each). If I
remember correctly you could even have SQL produce do the code for you. In
PHP you could do it any number of ways (pseudocode follows - untested,
YMMV);
$query1 = SELECT DISTINCT RecordID ;
$query1 .= FROM tblFOO ;
$query2 = SELECT RecordDate, ;
$db1 = mysql_query($query1, $dbconnect)
while($dbrow = mysql_fetch_object($db1)){
$query2 .= IF(RecordID=$dbrow-RecordID,count(*),0) AS $dbrow-RecordID,
;
}
$query2 .= FROM tblFOO ;
$q11 .= GROUP BY RecordDate ;
Now, IF THIS WORKS (I will repeat that I have not tested it), it would be
very convenient. I may have made some small syntax errors, if I get the
chance I will try it later. If you try it and it works let me know.
Jay Blanchard
-
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
RE: [thelist] MySQL to Excel Problem Pivot Table SOLVED!
Yeah, that's about what I thought too. Certainly looks like it would
work. Depending on the size of the table you end up doing two fairly
large db calls, but that's gotta be faster and simpler than all the
other data manipulation.
You could then add Into outfile blah blah, and get it all done at
once, right?
Andrew Hazen
-Original Message-
From: Jay Blanchard [mailto:[EMAIL PROTECTED]]
Sent: Thursday, May 02, 2002 9:47 AM
To: 'Andrew Hazen'; [EMAIL PROTECTED]
Subject: RE: [thelist] MySQL to Excel Problem Pivot Table SOLVED!
[snip]
Fascinating. But how do you build the SELECT? Seems to me you would
first have to SELECT RecordID FROM tblClass10 and then script an
iteration to build the whole series of IF clauses. Or did I miss
something?
[/snip]
You could do that for the RecordID if you do not know what they are (I
knew
what RecordID's existed, so I could specifiy an IF statement for each).
If I
remember correctly you could even have SQL produce do the code for you.
In
PHP you could do it any number of ways (pseudocode follows - untested,
YMMV);
$query1 = SELECT DISTINCT RecordID ;
$query1 .= FROM tblFOO ;
$query2 = SELECT RecordDate, ;
$db1 = mysql_query($query1, $dbconnect)
while($dbrow = mysql_fetch_object($db1)){
$query2 .= IF(RecordID=$dbrow-RecordID,count(*),0) AS
$dbrow-RecordID,
;
}
$query2 .= FROM tblFOO ;
$q11 .= GROUP BY RecordDate ;
Now, IF THIS WORKS (I will repeat that I have not tested it), it would
be
very convenient. I may have made some small syntax errors, if I get the
chance I will try it later. If you try it and it works let me know.
Jay Blanchard
-
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
RE: [thelist] MySQL to Excel Problem Pivot Table SOLVED!
[snip 1]
You could do that for the RecordID if you do not know what they are (I
knew what RecordID's existed, so I could specifiy an IF statement for each).
If I remember correctly you could even have SQL produce do the code for you.
In PHP you could do it any number of ways (pseudocode follows - untested,
YMMV);
$query1 = SELECT DISTINCT RecordID ;
$query1 .= FROM tblFOO ;
$query2 = SELECT RecordDate, ;
$db1 = mysql_query($query1, $dbconnect)
while($dbrow = mysql_fetch_object($db1)){
$query2 .= IF(RecordID=$dbrow-RecordID,count(*),0) AS
$dbrow-RecordID,
;
}
$query2 .= FROM tblFOO ;
$q11 .= GROUP BY RecordDate ;
Now, IF THIS WORKS (I will repeat that I have not tested it), it would
be very convenient. I may have made some small syntax errors, if I get the
chance I will try it later. If you try it and it works let me know.
[/snip1]
[snip2]
Yeah, that's about what I thought too. Certainly looks like it would
work. Depending on the size of the table you end up doing two fairly
large db calls, but that's gotta be faster and simpler than all the
other data manipulation.
You could then add Into outfile blah blah, and get it all done at
once, right?
[/snip2]
In this case I am not doing INTO OUTFILE (though you could). I am placing
the query in a PHP page that is set to MIME M$ Excel so that the fields/data
are retrieved into a spreadsheet so that management can muck around with
numbers without destroying the original data.
Thanks!
Jay
-
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
User Variables and Previous Row Question
I have been trying to use 'user variables' to keep track of the previous row for use in a calculation of the present row. Is there a way I can do this? Or is there a better way in trying to use a previous rows value in the present row. For example; SELECT number as current_day, (number - prevnumber) as change_from_prev_day FROM TABLE ORDER BY DATE prevnumber is the reference that I need from the previous row. Am I missing something simple or do I have to do this outside of MYSQL? Any help would be appreciated. thanks, jasmin. __ Do You Yahoo!? Yahoo! Health - your guide to health and wellness http://health.yahoo.com - 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
Re: get one name for each row
It might be faster to instead of getting the list of cities and then in
seperate queries get all hotels for each city, to just sort by city and
fetch all, and in your application check if it has found a next city
already.
Example in php:
$res=mysql_query('select hotel,city from hotels order by city');
$lastcity=;
$firstline=;
while (list($hotel,$city)=mysql_fetch_row($res)) {
if ($city!=$lastcity) {
$lastcity=$city;
echo brb$city/b;
}
echo $hotel ;
}
Just an example, change the html output to what you want it.
- Original Message -
From: savaidis [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Thursday, May 02, 2002 10:07 AM
Subject: get one name for each row
I have one table with about 1000 hotels and name or the city they belong.
I want to run a query to get one row for every city only, to put it in a
pull down menu in the search form.
How is that?
Makis
-
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
-
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
Verizon.net auto-reply
Is anyone else getting auto-replies from [EMAIL PROTECTED] when they post to the list? Duncan [p.s. SQL just for the nice filter] - 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
Problems with MySQL on FreeBSD4.5
I'm having trouble with a MySQL installation on a FreeBSD4.5 system (MYSQL version 3.23.49). From time to time top shows that it runs 98% of CPU time, and of course the site's response gets sluggish... Sometimes queries even times out or MySQL reports that no sockects are free to complete the request. This actually does not happen anymore, but it did a few times earlier...short time after we had a disk crash... ;-) I had a comment from one of the users regarding this: I seriously suspect that mySQL under FreeBSD is your problem. On another heavily loaded website I use (www.perlmonks.org), we're experiencing similiar problems. Current speculation is that it has to do with the way the FreeBSD flock() function is broken. I imagine what you're seeing is lots of blocked mySQL processes, or where a query takes an exceptionally long time to complete. CPU utilization is remaining pretty low during the event. Just want to know if anybody has something to say to this matter, perhaps suggestions on where to start for debugging the problem? I have no clue where to begin looking for reasons, so any tip could very well be a good tip for me right now! Debugging tools or approaches to recommend...? Anybody? Best regards, Eivind, [EMAIL PROTECTED] - 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
RE: get one name for each row
[snip] It might be faster to instead of getting the list of cities and then in seperate queries get all hotels for each city, to just sort by city and fetch all, and in your application check if it has found a next city already. [/snip] Of course, you could use a single crosstab query and get something like CITY1 Hotel1 Hotel2 Hotel3 Hotel4 CITY2 Hotel1 Hotel2 CITY3 Hotel1 Hotel2 Hotel3 CITY4 Hotel1 Hotel2 Hotel3 Hotel4 Hotel5 Jay Blanchard - 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
RE: Verizon.net auto-reply
[snip] Is anyone else getting auto-replies from [EMAIL PROTECTED] when they post to the list? [/snip] Yes, and a couple of others. Plus some of my posts seem to be posting twice. Greeting MySQL mail-list manager, no-spam here! :) Jay Blanchard - 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
synopsis of the problem (one line)
Description: How-To-Repeat: Fix: Submitter-Id: submitter ID Originator:root Organization: MySQL support: [none | licence | email support | extended email support ] Synopsis: Severity: Priority: Category: mysql Class: Release: mysql-3.23.49 (Source distribution) Environment: System: Linux sparrow.ce.hangkong.ac.kr 2.4.2-2wl #1 ±Ý 6¿ù 8 05:21:58 KST 2001 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs gcc version 2.96 2731 (Red Hat Linux 7.1 2.96-81) Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 13 5¿ù 3 2002 /lib/libc.so.6 - libc-2.2.2.so -rwxr-xr-x1 root root 1236396 4¿ù 7 2001 /lib/libc-2.2.2.so -rw-r--r--1 root root 26350254 4¿ù 7 2001 /usr/lib/libc.a -rw-r--r--1 root root 178 4¿ù 7 2001 /usr/lib/libc.so lrwxrwxrwx1 root root 10 5¿ù 3 2002 /usr/lib/libc-client.a - c-client.a Configure command: ./configure --with-prefix=/usr/local/mysql --with-chareset=euc-kr - 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
RE: data compare and duplicate entries..
Are you writing a php script, perhaps with a form and a submit button, that connects to mysql? -Steve. -Original Message- From: Tech @NorthWeb [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 02, 2002 9:32 AM To: mysql Subject: Fw: data compare and duplicate entries.. hi, I was looking for an SQL statement or script that whould allow me to compare data when it was entered into a mysql-php field on a web page so that it checks whether the data entered has not been used before in the database.. In short, when data needs to be entered, the statement/script must check in the database whether the data is not already entered into the database and if its there, return with an error.. and not allow the data to be entered... if the data is not there, then it may be entered.. Thanx Willem Pretorius - 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 - 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
ensuring that I'm getting the correct last insert ID
Hi all, I know this has been asked a bunch of times, but i need some clarity (new mySQL user). I have an app that inserts a new user into one table, then inserts some user preferences into another table. the procedure is as follows: 1) insert new user 2) query for that user's id using select max(userID) as LastUserID from ... 3) insert into user preferences table using the previous query's LastUserID. To be clear, this last insert adds mutliple rows to a table, not one row. Thus, my data will look like this: UserTable UserID FirstName LastName _ 1...Marc...Smith 2...SteveJones and so on PreferencesTable PreferenceID PreferenceName 1SomeText 2SomeOtherText 3MoreText UserPreferences Table UserID PreferenceID (these preference IDs are passed through checkboxes on the form submission page) __ 1 1 1 3 2 2 2 3 So here's my question: I insert the new user, then query for the max(UserID). What happens if user1 starts an insert; then user2 comes along at the same millisecond and starts an insert. User2's insert takes far less time because he only inserts the required fields. So his insert completes; then the select statement runs to get the maxID. When that select statement runs, whose ID am I getting? the user whose insert started first? or the user whose insert completed first? I'm new to non-transaction tables, but I'm trying them because, well, just because. Any answers are appreciated: bottom line, I want to make sure that everytime I query for maxID I'm getting the ID of the user that I've just inserted. Thanks. Marc _ This mail was sent by Cablespeed Webmail - 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
inner select
i have to use a statement like: SELECT CODICE FROM TABSIC WHERE COM=1 AND SEN=1 AND PRI=1 AND CODICEALL(SELECT CODICE_TABSIC FROM ARCHIVIO_SICU_MIN WHERE (CODICE_ARCHIVIO=342)) but mysql doesn't support inner select. What statement should i use instead of this one ? Thanks... Alex. - 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
Re: Verizon.net auto-reply
Salada, Duncan a écrit : Is anyone else getting auto-replies from [EMAIL PROTECTED] when they post to the list? Duncan [p.s. SQL just for the nice filter] Yes. I have added their email to my spam filter. -- Joseph Bueno NetClub/Trader.com - 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
JDBC on HPUX
Hi, all: I meet a problem on HPUX. When I try to connect to MySQL with JDBC on HPUX. I connect to jdbc:mysql://localhost:3306/mysql with root, it failed. But it works before. Someone told me that it is becuase of some changes of DNS. When I connect to MySQL, MySQL translate root to [EMAIL PROTECTED], but MySQL don't have this user. Same thing happens if I connect to MySQL with hostname or full qualified-name. If I change the user table root@hostname to root@full-qualified-name, then connect with either hostname or full-qualified-name, everything is fine. But I want my application to be easy to install by customer. Do anyone have any suggestion how to fix this problem? thanks! Zengfa __ Do You Yahoo!? Yahoo! Health - your guide to health and wellness http://health.yahoo.com - 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
Re: ensuring that I'm getting the correct last insert ID
From: Jonnycattt [EMAIL PROTECTED] Hi all, I know this has been asked a bunch of times, but i need some clarity (new mySQL user). I have an app that inserts a new user into one table, then inserts some user preferences into another table. the procedure is as follows: 1) insert new user 2) query for that user's id using select max(userID) as LastUserID from ... 3) insert into user preferences table using the previous query's LastUserID. To be clear, this last insert adds mutliple rows to a table, not one row. If I were you, I'd use MySQL's LAST_INSERT_ID() function: http://www.mysql.com/doc/M/i/Miscellaneous_functions.html So long as your ID field is AUTO_INCREMENT, this will return the last auto-generated field in the current handle. Something like this: INSERT INTO UserTable... (your first user insert) SELECT LAST_INSERT_ID() FROM UserTable (this will return the userID of the user you just inserted) INSERT INTO OtherTables (pass the userID you just got to these queries) You'd mentioned worries that a user could be added while another user was still being processed, and the result would be the wrong userID being returned. LAST_INSERT_ID() is handle-based, though, so there should be no worries with that -- the sessions will be kept separate. Hope this helps! -- denonymous . : . : . AIM: denonymous http://www.coldcircuit.net ' : ' : ' http://24.91.199.33 According to one of our readers, the new MacOS X contains another Satanic holdover from the 'BSD Unix' OS mentioned above; to open up certain locked files one has to run a program much like the DOS prompt in Microsoft Windows and type in a secret code: 'chmod 666'. - 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
Fwd: Re: How to Count(*) with LIMIT
X-Sieve: CMU Sieve 2.1 X-Mail-from: [EMAIL PROTECTED] From: Ryan Fox [EMAIL PROTECTED] To: [EMAIL PROTECTED], mos [EMAIL PROTECTED] Subject: Re: How to Count(*) with LIMIT Date: Thu, 2 May 2002 08:42:28 -0400 X-Mailer: Microsoft Outlook Express 6.00.2600. - Original Message - From: mos [EMAIL PROTECTED] I have a Where clause like: select count(*) from table where LIMIT 100 Unfortunately the Count(*) ignores the LIMIT clause entirely. Why? You could use least(). That would give the correct result, but it would still physically count all the rows in the table which takes too long. This code will execute every time a web page opens that has a grid. Some of the grid pages are quite large, 1 million rows. The person who designed the web page originally didn't think that would matter much. I keep telling him you can't count 1 million rows every time a web page opens. The site would probably support around 5 people.g I will go with the mysql_num_rows which shouldn't add any additional CPU or I/O. It seems like a perfect solution once I move some code around. Thanks. Mike mysql select least(count(*),30) from ct; ++ | least(count(*),30) | ++ | 30 | ++ 1 row in set (0.00 sec) mysql select count(*) from ct; +--+ | count(*) | +--+ | 43 | +--+ 1 row in set (0.00 sec) Cheers, Ryan Fox, Geek email: [EMAIL PROTECTED] BackWatcher, Inc. web: http://backwatcher.com - 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
RE: Re: How to Count(*) with LIMIT
At 03:48 AM 5/2/2002, you wrote: I have a Where clause like: select count(*) from table where LIMIT 100 Unfortunately the Count(*) ignores the LIMIT clause entirely. Why? Because the query returns only ONE row and LIMIT limits rows, not values. See ex. below: SELECT count(login) FROM accounts WHERE domain_id=1 LIMIT 3 +--+ | count(login) | +--+ |6 | +--+ It seems to me that if a select * from table where ... limit 100 returns between 0 and 100 rows, you should be able to count it. Instead the count returns 55,000 or some ridiculously large number that has no bearing on the # of rows that will actually be returned (because of the LIMIT clause). Since this is running on a webserver, I don't want it to physically count more than 100 rows. Some of the tables may be over 1 million rows and counting that many rows when only 100 rows are returned is overkill. Then why Use COUNT if You're not interested of number of records ?? Could You maybe specify what you actually want to do with the Count ?? It is used to set up the navigator bar buttons for the grid on the web page (PHP) so it says Page 1 of 18 with buttons on either side of it. Is there a way around this counting problem? The only solution I've come up with is to traverse all the rows returned by counting them in a loop. This seems pretty lame and I'm hoping someone can come up with a better solution. If You want to know the number of rows in the recordset returned by the query then You should use mysql_num_rows(). How You do this depends on the language been used in Your application. This seems like the most economical way to approach it. Of course I would then have to delay setting up the buttons until after the query is executed. Right now it is done when the page first loads. But that should be a trivial matter (I hope!g). Thanks for everyone's input. Mike sql, query - 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
RE: RE: Verizon.net auto-reply
sql,query Salada, Duncan a ecrit : Is anyone else getting auto-replies from [EMAIL PROTECTED] when they post to the list? All the f*cking time. I sent mail to security and postmaster and asked them to please remove this user from the mailing list - no reaction so far. - 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
RE: ensuring that I'm getting the correct last insert ID
what happens if you are using connection pooling though? -Original Message- From: denonymous [mailto:[EMAIL PROTECTED]] Sent: 02 May 2002 08:09 To: [EMAIL PROTECTED] Subject: Re: ensuring that I'm getting the correct last insert ID From: Jonnycattt [EMAIL PROTECTED] Hi all, I know this has been asked a bunch of times, but i need some clarity (new mySQL user). I have an app that inserts a new user into one table, then inserts some user preferences into another table. the procedure is as follows: 1) insert new user 2) query for that user's id using select max(userID) as LastUserID from .. 3) insert into user preferences table using the previous query's LastUserID. To be clear, this last insert adds mutliple rows to a table, not one row. If I were you, I'd use MySQL's LAST_INSERT_ID() function: http://www.mysql.com/doc/M/i/Miscellaneous_functions.html So long as your ID field is AUTO_INCREMENT, this will return the last auto-generated field in the current handle. Something like this: INSERT INTO UserTable... (your first user insert) SELECT LAST_INSERT_ID() FROM UserTable (this will return the userID of the user you just inserted) INSERT INTO OtherTables (pass the userID you just got to these queries) You'd mentioned worries that a user could be added while another user was still being processed, and the result would be the wrong userID being returned. LAST_INSERT_ID() is handle-based, though, so there should be no worries with that -- the sessions will be kept separate. Hope this helps! -- denonymous . : . : . AIM: denonymous http://www.coldcircuit.net ' : ' : ' http://24.91.199.33 According to one of our readers, the new MacOS X contains another Satanic holdover from the 'BSD Unix' OS mentioned above; to open up certain locked files one has to run a program much like the DOS prompt in Microsoft Windows and type in a secret code: 'chmod 666'. - 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 - 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
Re: ensuring that I'm getting the correct last insert ID
Admittedly, I'm no expert. What *is* connection pooling? - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, May 02, 2002 11:21 AM Subject: RE: ensuring that I'm getting the correct last insert ID what happens if you are using connection pooling though? -Original Message- From: denonymous [mailto:[EMAIL PROTECTED]] Sent: 02 May 2002 08:09 To: [EMAIL PROTECTED] Subject: Re: ensuring that I'm getting the correct last insert ID From: Jonnycattt [EMAIL PROTECTED] Hi all, I know this has been asked a bunch of times, but i need some clarity (new mySQL user). I have an app that inserts a new user into one table, then inserts some user preferences into another table. the procedure is as follows: 1) insert new user 2) query for that user's id using select max(userID) as LastUserID from .. 3) insert into user preferences table using the previous query's LastUserID. To be clear, this last insert adds mutliple rows to a table, not one row. If I were you, I'd use MySQL's LAST_INSERT_ID() function: http://www.mysql.com/doc/M/i/Miscellaneous_functions.html So long as your ID field is AUTO_INCREMENT, this will return the last auto-generated field in the current handle. Something like this: INSERT INTO UserTable... (your first user insert) SELECT LAST_INSERT_ID() FROM UserTable (this will return the userID of the user you just inserted) INSERT INTO OtherTables (pass the userID you just got to these queries) You'd mentioned worries that a user could be added while another user was still being processed, and the result would be the wrong userID being returned. LAST_INSERT_ID() is handle-based, though, so there should be no worries with that -- the sessions will be kept separate. Hope this helps! -- denonymous . : . : . AIM: denonymous http://www.coldcircuit.net ' : ' : ' http://24.91.199.33 According to one of our readers, the new MacOS X contains another Satanic holdover from the 'BSD Unix' OS mentioned above; to open up certain locked files one has to run a program much like the DOS prompt in Microsoft Windows and type in a secret code: 'chmod 666'. - 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 - 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
Re: Sorting letters in CZech
nicronom, Thursday, May 02, 2002, 3:36:47 PM, you wrote: n Realy BIG PROBLEM czech users! n In czech alphabet is letter ©( or alt+0138),¹ ( or n alt+0154), the problem is with sorting some datas with this n letter in front. n For example names are: n Adam, Frank, Eve, Martin, Zoe, ©omin , William, Steve [skip] n it is same with lower-case version of ©. n This letter stands after letter S. n You should/could repair this cause when user has for example n database with items in shop and he will print it all item n starting with ¹ or © will be on the end of output. I managing n company database with all our workers and co-workers and my boss n is angry cause of this. I have to sort it manualy, it is not a n fun. Do you use czech character set? If no, please, set up default-character-set=czech in your my.cnf file or run mysqld with '--default-character-set=czech' option and test the sorting order again. nThanx a lot, Mike -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - 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
Re: finding values in TableA not in TableB
Salada, Thursday, May 02, 2002, 4:45:01 PM, you wrote: Seoen I'm sure this question has been asked before, but I received an error while Seoen attempting to search the list archives - so I apologize for redunancy. Seoen I have two tables, events and events_terms, that have a common key, Seoen events_ID. Now I want to find the rows in events_terms that have an Seoen events_ID that does not match a corresponding row in events. I came up with Seoen an SQL statement that seems to work but I want to make sure that I'm not Seoen missing Seoen anything. Here's the statement: Seoen select events_terms.* from events_terms left join events on Seoen events_terms.events_ID=events.events_ID where isnull(events.events_ID); Seoen Can anyone tell me if that looks like it should have the desired effect? Looks like it's a right statement for the desired effect. Seoen Thanks, Seoen Duncan -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - 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
Re: User Variables and Previous Row Question
Jasmin, Thursday, May 02, 2002, 5:00:14 PM, you wrote: JB I have been trying to use 'user variables' to keep JB track of the previous row for use in a calculation of JB the present row. Is there a way I can do this? Or JB is there a better way in trying to use a previous JB rows value in the present row. JB For example; JB SELECT number as current_day, (number - prevnumber) JB as change_from_prev_day FROM TABLE ORDER BY DATE JB prevnumber is the reference that I need from the JB previous row. JB Am I missing something simple or do I have to do this JB outside of MYSQL? You can do something like that: SELECT @a:=0; SELECT (number-@a) AS change_from_prev_day, @a:=number FROM your_table; Note: you should set value to variable _after_ calculation. You can find more info about user variables at: http://www.mysql.com/doc/V/a/Variables.html http://www.mysql.com/doc/e/x/example-user-variables.html JB Any help would be appreciated. thanks, jasmin. -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - 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
RE: Re: How to Count(*) with LIMIT
I have a Where clause like: select count(*) from table where LIMIT 100 Unfortunately the Count(*) ignores the LIMIT clause entirely. Why? The answer is: Because the SQL query with COUNT(*) only returns one row... - 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
RE: ensuring that I'm getting the correct last insert ID
its used a lot by java. As setting up and closing down connections to the database is relatively expensive, you use a pool manager, when the connection is closed by your code, it gets returned to the pool of open connections and is'nt actually closed. just held open and returned next time you want a connection. So I imagine if you have 20 users on the site at once, then they could all be executing various snippets of sql over 5 connections. -Original Message- From: denonymous [mailto:[EMAIL PROTECTED]] Sent: 02 May 2002 08:27 To: Sean O'Donnell; [EMAIL PROTECTED] Subject: Re: ensuring that I'm getting the correct last insert ID Admittedly, I'm no expert. What *is* connection pooling? - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, May 02, 2002 11:21 AM Subject: RE: ensuring that I'm getting the correct last insert ID what happens if you are using connection pooling though? -Original Message- From: denonymous [mailto:[EMAIL PROTECTED]] Sent: 02 May 2002 08:09 To: [EMAIL PROTECTED] Subject: Re: ensuring that I'm getting the correct last insert ID From: Jonnycattt [EMAIL PROTECTED] Hi all, I know this has been asked a bunch of times, but i need some clarity (new mySQL user). I have an app that inserts a new user into one table, then inserts some user preferences into another table. the procedure is as follows: 1) insert new user 2) query for that user's id using select max(userID) as LastUserID from .. 3) insert into user preferences table using the previous query's LastUserID. To be clear, this last insert adds mutliple rows to a table, not one row. If I were you, I'd use MySQL's LAST_INSERT_ID() function: http://www.mysql.com/doc/M/i/Miscellaneous_functions.html So long as your ID field is AUTO_INCREMENT, this will return the last auto-generated field in the current handle. Something like this: INSERT INTO UserTable... (your first user insert) SELECT LAST_INSERT_ID() FROM UserTable (this will return the userID of the user you just inserted) INSERT INTO OtherTables (pass the userID you just got to these queries) You'd mentioned worries that a user could be added while another user was still being processed, and the result would be the wrong userID being returned. LAST_INSERT_ID() is handle-based, though, so there should be no worries with that -- the sessions will be kept separate. Hope this helps! -- denonymous . : . : . AIM: denonymous http://www.coldcircuit.net ' : ' : ' http://24.91.199.33 According to one of our readers, the new MacOS X contains another Satanic holdover from the 'BSD Unix' OS mentioned above; to open up certain locked files one has to run a program much like the DOS prompt in Microsoft Windows and type in a secret code: 'chmod 666'. - 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 - 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
Re: ensuring that I'm getting the correct last insert ID
You should not SELECT MAX(userID), you should SELECT LAST_INSERT_ID(), which gives you tha last id *generated by your connection* regarless of any others, This is general purpose. If you are willing to be MySQL specific, both C and Java (and, I presume, other languages) allow you to retrieve the last insert id from the connection without the need to do an explicit SELECT, thus saving the need for a network operation to the database server. See http://www.mysql.com/doc/G/e/Getting_unique_ID.html And http://www.mysql.com/doc/M/i/Miscellaneous_functions.html Hi all, I know this has been asked a bunch of times, but i need some clarity (new mySQL user). I have an app that inserts a new user into one table, then inserts some user preferences into another table. the procedure is as follows: 1) insert new user 2) query for that user's id using select max(userID) as LastUserID from ... 3) insert into user preferences table using the previous query's LastUserID. To be clear, this last insert adds mutliple rows to a table, not one row. Thus, my data will look like this: UserTable UserID FirstName LastName _ 1...Marc...Smith 2...SteveJones and so on PreferencesTable PreferenceID PreferenceName 1SomeText 2SomeOtherText 3MoreText UserPreferences Table UserID PreferenceID (these preference IDs are passed through checkboxes on the form submission page) __ 1 1 1 3 2 2 2 3 So here's my question: I insert the new user, then query for the max(UserID). What happens if user1 starts an insert; then user2 comes along at the same millisecond and starts an insert. User2's insert takes far less time because he only inserts the required fields. So his insert completes; then the select statement runs to get the maxID. When that select statement runs, whose ID am I getting? the user whose insert started first? or the user whose insert completed first? I'm new to non-transaction tables, but I'm trying them because, well, just because. Any answers are appreciated: bottom line, I want to make sure that everytime I query for maxID I'm getting the ID of the user that I've just inserted. Thanks. Marc _ This mail was sent by Cablespeed Webmail - 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 - 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
Re: ensuring that I'm getting the correct last insert ID
But would the same instance of a script drop and pick up different connections? I haven't done any Java/MySQL work, but anything I've done with PHP or Perl is based on a model of: [begin script] [open database connection(s)] [execute queries] [close database connection(s)] [end script] Is it more common in Java to do something like: [begin script] [open database connection] [execute query] [close database connection] [open database connection] [execute query] [close database connection] [etc...] [end script] ? - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, May 02, 2002 11:28 AM Subject: RE: ensuring that I'm getting the correct last insert ID its used a lot by java. As setting up and closing down connections to the database is relatively expensive, you use a pool manager, when the connection is closed by your code, it gets returned to the pool of open connections and is'nt actually closed. just held open and returned next time you want a connection. So I imagine if you have 20 users on the site at once, then they could all be executing various snippets of sql over 5 connections. -Original Message- From: denonymous [mailto:[EMAIL PROTECTED]] Sent: 02 May 2002 08:27 To: Sean O'Donnell; [EMAIL PROTECTED] Subject: Re: ensuring that I'm getting the correct last insert ID Admittedly, I'm no expert. What *is* connection pooling? - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, May 02, 2002 11:21 AM Subject: RE: ensuring that I'm getting the correct last insert ID what happens if you are using connection pooling though? -Original Message- From: denonymous [mailto:[EMAIL PROTECTED]] Sent: 02 May 2002 08:09 To: [EMAIL PROTECTED] Subject: Re: ensuring that I'm getting the correct last insert ID From: Jonnycattt [EMAIL PROTECTED] Hi all, I know this has been asked a bunch of times, but i need some clarity (new mySQL user). I have an app that inserts a new user into one table, then inserts some user preferences into another table. the procedure is as follows: 1) insert new user 2) query for that user's id using select max(userID) as LastUserID from .. 3) insert into user preferences table using the previous query's LastUserID. To be clear, this last insert adds mutliple rows to a table, not one row. If I were you, I'd use MySQL's LAST_INSERT_ID() function: http://www.mysql.com/doc/M/i/Miscellaneous_functions.html So long as your ID field is AUTO_INCREMENT, this will return the last auto-generated field in the current handle. Something like this: INSERT INTO UserTable... (your first user insert) SELECT LAST_INSERT_ID() FROM UserTable (this will return the userID of the user you just inserted) INSERT INTO OtherTables (pass the userID you just got to these queries) You'd mentioned worries that a user could be added while another user was still being processed, and the result would be the wrong userID being returned. LAST_INSERT_ID() is handle-based, though, so there should be no worries with that -- the sessions will be kept separate. Hope this helps! -- denonymous . : . : . AIM: denonymous http://www.coldcircuit.net ' : ' : ' http://24.91.199.33 According to one of our readers, the new MacOS X contains another Satanic holdover from the 'BSD Unix' OS mentioned above; to open up certain locked files one has to run a program much like the DOS prompt in Microsoft Windows and type in a secret code: 'chmod 666'. - 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 - 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
RE: ensuring that I'm getting the correct last insert ID
if you are using a connection pool the [close database connection] part of your example doesnt actually close the connection. so 1. Your code might be using several connections 2. Your code might use a different connection for each statement. 3. Even if your code only does use the one connection, someone else might be sharing it. asides from that , you dont have to close your database connection to execute multiple queries in java. You may have to create multiple statement objects though(depending on the type of query) -Original Message- From: denonymous [mailto:[EMAIL PROTECTED]] Sent: 02 May 2002 08:36 To: Sean O'Donnell; [EMAIL PROTECTED] Subject: Re: ensuring that I'm getting the correct last insert ID But would the same instance of a script drop and pick up different connections? I haven't done any Java/MySQL work, but anything I've done with PHP or Perl is based on a model of: [begin script] [open database connection(s)] [execute queries] [close database connection(s)] [end script] Is it more common in Java to do something like: [begin script] [open database connection] [execute query] [close database connection] [open database connection] [execute query] [close database connection] [etc...] [end script] ? - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, May 02, 2002 11:28 AM Subject: RE: ensuring that I'm getting the correct last insert ID its used a lot by java. As setting up and closing down connections to the database is relatively expensive, you use a pool manager, when the connection is closed by your code, it gets returned to the pool of open connections and is'nt actually closed. just held open and returned next time you want a connection. So I imagine if you have 20 users on the site at once, then they could all be executing various snippets of sql over 5 connections. -Original Message- From: denonymous [mailto:[EMAIL PROTECTED]] Sent: 02 May 2002 08:27 To: Sean O'Donnell; [EMAIL PROTECTED] Subject: Re: ensuring that I'm getting the correct last insert ID Admittedly, I'm no expert. What *is* connection pooling? - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, May 02, 2002 11:21 AM Subject: RE: ensuring that I'm getting the correct last insert ID what happens if you are using connection pooling though? -Original Message- From: denonymous [mailto:[EMAIL PROTECTED]] Sent: 02 May 2002 08:09 To: [EMAIL PROTECTED] Subject: Re: ensuring that I'm getting the correct last insert ID From: Jonnycattt [EMAIL PROTECTED] Hi all, I know this has been asked a bunch of times, but i need some clarity (new mySQL user). I have an app that inserts a new user into one table, then inserts some user preferences into another table. the procedure is as follows: 1) insert new user 2) query for that user's id using select max(userID) as LastUserID from .. 3) insert into user preferences table using the previous query's LastUserID. To be clear, this last insert adds mutliple rows to a table, not one row. If I were you, I'd use MySQL's LAST_INSERT_ID() function: http://www.mysql.com/doc/M/i/Miscellaneous_functions.html So long as your ID field is AUTO_INCREMENT, this will return the last auto-generated field in the current handle. Something like this: INSERT INTO UserTable... (your first user insert) SELECT LAST_INSERT_ID() FROM UserTable (this will return the userID of the user you just inserted) INSERT INTO OtherTables (pass the userID you just got to these queries) You'd mentioned worries that a user could be added while another user was still being processed, and the result would be the wrong userID being returned. LAST_INSERT_ID() is handle-based, though, so there should be no worries with that -- the sessions will be kept separate. Hope this helps! -- denonymous . : . : . AIM: denonymous http://www.coldcircuit.net ' : ' : ' http://24.91.199.33 According to one of our readers, the new MacOS X contains another Satanic holdover from the 'BSD Unix' OS mentioned above; to open up certain locked files one has to run a program much like the DOS prompt in Microsoft Windows and type in a secret code: 'chmod 666'. - 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/
Re: MySQL to Excel ?
Hi,
Jay Blanchard wrote:
Howdy,
I need to write some data out to Excel spreadsheets for some of our managers
to muddle with for projections. The query works fine...
SELECT RecordID, RecordDate, count(*) AS Quantity
FROM tblFOO
Group By RecordID, RecordDate
It returns;
+--++--+
| RecordID | RecordDate | Quantity |
+--++--+
| 100101 | 2002-03-21 | 6675 |
| 100101 | 2002-03-22 | 6794 |
| 100101 | 2002-03-23 | 2916 |
| 100101 | 2002-03-24 | 1215 |
| 100101 | 2002-03-25 | 6152 |
| 100101 | 2002-03-26 | 5398 |
+--++--+
as expected (Only a subset here, there are hundreds of rows). I want to
place this data like this in an Excel table (bad ascii art example to
follow);
+++++
| RecordID | 100101 | 100120 | 100131 |
+++++
| Date ||||
+++++
| 2002-03-01 || 12 | 130101 |
+++++
| 2002-03-21 | 6675 || 1113 |
+++++
| 2002-03-22 | 6794 |287 | 29045 |
+++++
| 2002-03-23 | 2916 | 6 | 233427 |
+++++
RecordID along the top row, dates down the side, quantities at the
intersection that they belong. I could get an OBDC connection from Excel to
MySQL and then write a complicated macro to do this, or if posssible (which
is what I am really looking for) I would like to SELECT..INTO..OUTFILE an
Excel file with the proper formatting.
Any suggestions?
Thanks!
Jay Blanchard
here is a common way to do this staying within SQL. But it works only if
yuo know possible RecordID values in adnavce. i.e. for general case
you will need some scripting/programming to construct queres on the fly:
I will rewrite your table a little to give you a simplest example:
CREATE TABLE `foo` (
`r_id` enum('1','2','3','4','5') default NULL,
`d` date default NULL
)
Insert some 'foo' info
mysql select * from foo order by r_id, d;
+--++
| r_id | d |
+--++
| 1| 2002-04-30 |
| 1| 2002-05-01 |
| 1| 2002-05-02 |
| 2| 2002-04-30 |
| 2| 2002-04-30 |
| 2| 2002-04-30 |
| 2| 2002-05-01 |
| 2| 2002-05-02 |
| 3| 2002-04-30 |
| 4| 2002-04-30 |
| 4| 2002-05-01 |
| 5| 2002-05-01 |
| 5| 2002-05-01 |
+--++
Here is same query as yours but with little addition :):
mysql select d, r_id, count(*) as q_count, sum(1) as q_sum from foo
group by d, r_id;
++--+-+---+
| d | r_id | q_count | q_sum |
++--+-+---+
| 2002-04-30 | 1| 1 | 1 |
| 2002-04-30 | 2| 3 | 3 |
| 2002-04-30 | 3| 1 | 1 |
| 2002-04-30 | 4| 1 | 1 |
| 2002-05-01 | 1| 1 | 1 |
| 2002-05-01 | 2| 1 | 1 |
| 2002-05-01 | 4| 1 | 1 |
| 2002-05-01 | 5| 2 | 2 |
| 2002-05-02 | 1| 1 | 1 |
| 2002-05-02 | 2| 1 | 1 |
++--+-+---+
Note that columns q_count and q_sum contain same value. Do you see why
both are correct?
Let's use that fact and add if() in sum()
mysql select d, r_id, sum(1) as q_total, if(r_id = 1, 1, 0) as q_id1
from foo group by d, r_id;
++--+-+---+
| d | r_id | q_total | q_id1 |
++--+-+---+
| 2002-04-30 | 1| 1 | 1 |
| 2002-04-30 | 2| 3 | 0 |
| 2002-04-30 | 3| 1 | 0 |
| 2002-04-30 | 4| 1 | 0 |
| 2002-05-01 | 1| 1 | 1 |
| 2002-05-01 | 2| 1 | 0 |
| 2002-05-01 | 4| 1 | 0 |
| 2002-05-01 | 5| 2 | 0 |
| 2002-05-02 | 1| 1 | 1 |
| 2002-05-02 | 2| 1 | 0 |
++--+-+---+
This is whole magic :)
We know in advance there are 5 different ids so add some if's ...
mysql select d, r_id, sum(if(r_id = 1, 1, 0)) as q_id1, sum(if(r_id =
2, 1, 0)) as q_id2, sum(if(r_id = 3, 1, 0)) as q_id3, sum(if(r_id = 4,
1, 0)) as q_id4, sum(if(r_id = 5, 1, 0)) as q_id5, count(*) as q_total
from foo group by d, r_id;
++--+---+---+---+---+---+-+
| d | r_id | q_id1 | q_id2 | q_id3 | q_id4 | q_id5 | q_total |
++--+---+---+---+---+---+-+
| 2002-04-30 | 1| 1 | 0 | 0 | 0 | 0 | 1 |
| 2002-04-30 | 2| 0 | 3 | 0 | 0 | 0 | 3 |
| 2002-04-30 | 3| 0 | 0 | 1 | 0 | 0 | 1 |
| 2002-04-30 | 4| 0 | 0 | 0 | 1 | 0 | 1 |
| 2002-05-01 | 1| 1 | 0 | 0 | 0 | 0 | 1 |
| 2002-05-01 | 2| 0 | 1 |
RE: get one name for each row
I tried select distinct Location from hotels1 order by Location and worked. But how can I show all fields of the rows? It doesn't accept any field before distinct and shows every row after distinct (if I put select distinct Location, Name ... ) Thanks again Makis -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 02, 2002 3:23 PM To: 'savaidis'; [EMAIL PROTECTED] Subject: RE: get one name for each row [snip] I have one table with about 1000 hotels and name or the city they belong. I want to run a query to get one row for every city only, to put it in a pull down menu in the search form. How is that? [/snip] SELECT DISTINCT city FROM tblFOO So if you have this table; +---++ | City | Hotel| +---++ | Houston| Hotel A | | Houston| Hotel B | | Houston| Hotel C | | San Antonio | Hotel A | | Dallas | Hotel A | | Detroit | Hotel A | | Detroit | Hotel B | +---++ The query will return; +---+ | City | +---+ | Houston| | San Antonio | | Dallas | | Detroit | +---+ Hope this helps! Jay Blanchard - 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
Re: Re: How to Count(*) with LIMIT
- Original Message - From: mos [EMAIL PROTECTED] I have a Where clause like: select count(*) from table where LIMIT 100 Unfortunately the Count(*) ignores the LIMIT clause entirely. Why? If You want to know the number of rows in the recordset returned by the query then You should use mysql_num_rows(). This seems like the most economical way to approach it. Actually, I think you've got it completely backwards. :) Using mysql_num_rows() on a result set will force you to do a query that returns all of the rows, while using count(*) will (much more efficiently) use the index to return the number of rows. Cheers, Ryan Fox, Geek email: [EMAIL PROTECTED] BackWatcher, Inc. web: http://backwatcher.com - 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
Re: Help: SIGSEGV inside mysql_connect to remote server
On Thu, 2002-05-02 at 00:38, Jeremy Zawodny wrote: On Wed, May 01, 2002 at 04:24:05PM -0600, Guy Davis wrote: I am having a really strange problem with a in-house C application that calls mysql_connect() and generates a segfault deep within uname() when I configure it to contact a remote server. Using a local MySQL server works fine as uname is never called. Have any of you ever seen anything like this? Is that remote server's info in /etc/hosts on the client? If not, see if that makes a difference. Yes, adding the remote server's line to /etc/hosts clears up the problem. Does that meant that our DNS server is somehow misconfigured. Issuing 'host dev.pason.com' at the command line gives me the correct IP address. Here's /etc/host.conf: order hosts,bind Thanks. -- Guy Davishttp://www.guydavis.ca PGP: D2E2 76D4 0C9C 5D99 42AA EB6B B9C2 68CA 2DC7 F2E4 - 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
RE: MySQL to Excel ?
[snip] Are you satisfied? :) [/snip] Alexander, Thanks for the insight, yes I am satisfied! :) Did you see my solution with the IF statement in the SQL? select foo, if(bar = 'A', count(*), 0) as A, if(bar = 'B', count(*), 0) as B, if(bar = 'C', count(*), 0) as C from tblFOOBAR group by foo Gives you +-+---+---+---+ | foo | A | B | C | +-+---+---+---+ | d1 | 1 | 3 | 0 | | d2 | 1 | 6 | 2 | | d3 | 1 | 0 | 2 | +-+---+---+---+ Easily placed into Excel with a single SQL query from PHP Thanks! Jay Blanchard - 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
ROWID
Hello ! Is there, in MySQL 4.0 database tables, a pseudo column ROWID (containing a unique id for each row of the tables), like in Oracle or Informix databases ? If not, will this feature be in MySQL 4.1 ? Regards A. ROY - 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
RE: get one name for each row
[snip from mysql list message] I tried select distinct Location from hotels1 order by Location and worked. But how can I show all fields of the rows? It doesn't accept any field before distinct and shows every row after distinct (if I put select distinct Location, Name ... ) [/snip] Can we see your table? Can you also write out what you expect the results to be? Thanks! Jay Blanchard - 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
Re: finding values in TableA not in TableB
Hi, Salada, Duncan wrote: cut select events_terms.* from events_terms left join events on events_terms.events_ID=events.events_ID where isnull(events.events_ID); Can anyone tell me if that looks like it should have the desired effect? It seems to, but I am going to eventually be using this to delete rows - not find them - so I need to be sure. Thanks, Duncan select events_terms.* from events_terms left join events on events_terms.events_ID=events.events_ID where events.events_ID IS NULL; ISNULL(test) is function equivalent to IF(test IS NULL, 'Some new value', test); Where test can be any valid expression -- Best regards -- For technical support contracts, visit https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Alexander Keremidarski [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer /_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria ___/ www.mysql.com M: +359 88 231668 - 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
RE: How to Count(*) with LIMIT
Hi Again... !! It is used to set up the navigator bar buttons for the grid on the web page (PHP) so it says Page 1 of 18 with buttons on either side of it. I'm a bit confused now... You are building a pager to a webpage, right ?? But, if You select with LIMIT 100 then I assume that You want to show 100 records per page... If that's the case then You propably need to fetch the count of ALL matching rows after all. This seems like the most economical way to approach it. Of course I would then have to delay setting up the buttons until after the query is executed. Right now it is done when the page first loads. But that should be a trivial matter (I hope!g). You can call mysql_num_rows() directly after query BEFORE you loop through the recordset. (if it was that You ment with delay...) --- =d0Mi= , DCS.net [EMAIL PROTECTED] Original Message - Date: 2-May-2002 17:22:44 +0200 From: mos [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: RE: Re: How to Count(*) with LIMIT At 03:48 AM 5/2/2002, you wrote: I have a Where clause like: select count(*) from table where LIMIT 100 Unfortunately the Count(*) ignores the LIMIT clause entirely. Why? Because the query returns only ONE row and LIMIT limits rows, not values. See ex. below: SELECT count(login) FROM accounts WHERE domain_id=1 LIMIT 3 +--+ | count(login) | +--+ |6 | +--+ It seems to me that if a select * from table where ... limit 100 returns between 0 and 100 rows, you should be able to count it. Instead the count returns 55,000 or some ridiculously large number that has no bearing on the # of rows that will actually be returned (because of the LIMIT clause). Since this is running on a webserver, I don't want it to physically count more than 100 rows. Some of the tables may be over 1 million rows and counting that many rows when only 100 rows are returned is overkill. Then why Use COUNT if You're not interested of number of records ?? Could You maybe specify what you actually want to do with the Count ?? It is used to set up the navigator bar buttons for the grid on the web page (PHP) so it says Page 1 of 18 with buttons on either side of it. Is there a way around this counting problem? The only solution I've come up with is to traverse all the rows returned by counting them in a loop. This seems pretty lame and I'm hoping someone can come up with a better solution. If You want to know the number of rows in the recordset returned by the query then You should use mysql_num_rows(). How You do this depends on the language been used in Your application. This seems like the most economical way to approach it. Of course I would then have to delay setting up the buttons until after the query is executed. Right now it is done when the page first loads. But that should be a trivial matter (I hope!g). Thanks for everyone's input. Mike sql, query - 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 - 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
Help! with mod_auth from apache integrating with mysql
I have a databse on mysql server that authenticates users... However mod_auth did not have access to the database containing the user info. How can I set up my database with adequate permissions to allow mod_auth to access that database without leaving the database wide open... /Thanks Tim - 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
3.23 FullText 'AND' Search
Hello,
I'm having problems getting the fulltext search to return useful results. I
wan't to query all the results in my table that contain all of the search
words entered. I don't have mysql 4.0 so I can't use the binary search.
If I use ... where match field against ('word1') and match field against
('word2') then I get what I want, except that short words and words that
return no results cause the whole query to return no results.
Is there anyway to do a search of items contains all my words using the 3.23
fulltext search, and have the results returned in a decent order?
Thanks,
Chris
-
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
Re: Help: SIGSEGV inside mysql_connect to remote server
On Thu, May 02, 2002 at 09:21:14AM -0600, Guy Davis wrote: On Thu, 2002-05-02 at 00:38, Jeremy Zawodny wrote: On Wed, May 01, 2002 at 04:24:05PM -0600, Guy Davis wrote: I am having a really strange problem with a in-house C application that calls mysql_connect() and generates a segfault deep within uname() when I configure it to contact a remote server. Using a local MySQL server works fine as uname is never called. Have any of you ever seen anything like this? Is that remote server's info in /etc/hosts on the client? If not, see if that makes a difference. Yes, adding the remote server's line to /etc/hosts clears up the problem. Does that meant that our DNS server is somehow misconfigured. Issuing 'host dev.pason.com' at the command line gives me the correct IP address. Here's /etc/host.conf: order hosts,bind It probably points to either a bug in your system's resolver library or the way in which MySQL uses it. What OS are you running? I don't recall if you said. -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 84 days, processed 2,181,971,335 queries (300/sec. avg) - 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
How to strip off email domain for sorting by domain?
I use Perl/DBI with MySQL and I'm trying to find a way to sort email addresses by domain. I would assume there is probably an option in MySQL to strip off the text after the '@' sign that will allow me to sort on. In Perl: @fields = split(/@/, $email); My domain would be displayed as: $fields[1] Is there a comparable way to do this in MySQL? I was looking in the substring area, but I really need a split option. Thank you in advance! Andrew - 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
RE: get one name for each row
The table has about 80 fields. I would something like: select *, distinct Location from hotels order by Name (this one creates a MySQL error) to one full row for every Location. I suppose I have to do it with a php script. (with limit 1 to the second query) You can see it at http://www.macedonia-hotels.gr Makis -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 02, 2002 6:59 PM To: 'savaidis'; [EMAIL PROTECTED] Subject: RE: get one name for each row [snip from mysql list message] I tried select distinct Location from hotels1 order by Location and worked. But how can I show all fields of the rows? It doesn't accept any field before distinct and shows every row after distinct (if I put select distinct Location, Name ... ) [/snip] Can we see your table? Can you also write out what you expect the results to be? Thanks! Jay Blanchard - 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 - 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
Re: mysql speed concerns
Hi Anders, How ever, this is probaly not an interesstign discussion for the rest of the people who subscribes to the mysql list. I AGREE. But... i consider this place, offer a good opportunity to exchange ideas,knowledge and experiences about how we using or create MySQL application . In this way we can learn reciprocal: some from the others.Of course, maybe some answers can be considered wrong because it's possible to be applicable in other circumstances(unfortunately for the user/programmer who have problems) and not in what it's described.Are several reason for what is happened.But one of this is CONFUSION. To avoid any confusion or doubt , I feel is my duty to be more specific , for sake of the people who are subscribed in the mysql list: For this reason : IPC - InterProcess Communication mechanism in the Unix environment - described by the W. Richard Stevens(and not only) at pg.482 in book Advanced programming in the Unix environment.PIPE,MESSAGE QUEUES,SEMAPHORES AND SHARED MEMORY are CLASSICAL FORMS of IPC. STREAM PIPE,NAMED STREAM PIPE are ADVANCED FORMS of IPC. SOCKET is a type of file for network(or nonnetwork) communication between processes. ipcs - a little Unix program and provide information about IPC facilities. TCP/IP - it was,it is and always remain :Transmission Control Protocol (TCP) and is intended for use as a highly reliable host-to-host protocol between hosts in packet-switched computer communication networks.RFC 793 prepared for DARPA in 1981. So, it's a big difference between DATA COMMUNICATION and PROCESS COMMUNICATION. All this are not invented by me. I apologize for any inconvenience Sincerely, Gelu Gogancea _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Svensson, B.A.T. (HKG) [EMAIL PROTECTED] To: Gelu [EMAIL PROTECTED] Sent: Thursday, May 02, 2002 2:04 PM Subject: RE: mysql speed concerns As well as semaphores, shared memory and messages queues not necessarily involves IPC - on the other hand the protocoll family TCP/IP is always IPC. We are talking about two different issues, and maybe you missunderstodd what I did talk about in the first place. How ever, this is probaly not an interesstign discussion for the rest of the people who subscribes to the mysql list. Regards, file://Anders -Original Message- From: Gelu [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 02, 2002 9:52 AM To: Michael Chang Cc: Svensson, B.A.T. (HKG); Jason Yates; [EMAIL PROTECTED] Subject: Re: mysql speed concerns TCP/IP is a protocol for data interchange, host to host, on the network (RFC731). Sockets ,indeed , are also used for IPC. _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Michael Chang [EMAIL PROTECTED] To: Gelu [EMAIL PROTECTED] Cc: Svensson, B.A.T. (HKG) [EMAIL PROTECTED]; Jason Yates [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, May 01, 2002 10:29 PM Subject: Re: mysql speed concerns Technically speaking, TCP/IP communication *is* a form of IPC. IPC isn't restricted to semaphores or message queues, etc. I think that's what he meant (i.e.: mysql client on one box communicating via TCP/IP to a MySQL server on another box). Michael On Wed, 1 May 2002, Gelu wrote: Hi, About at the same IPC(semaphore,share memory,message) i make references too.Are more ways to made a inter process communication mechanism. For example, in my applications i don't use semaphores and messages.This are system functions available from the Kernel. If you type ipcs you can see that mySQL don't use the system functions for inter process communication. My opinion is referencing at, if this CGI (about Shaun said early) retrieve huge data from RDBMS ,sure can create unbalanced processes. For this reason i said it's strongly recommended to setup MySQL on the other host. I have a bad experience with INFORMIX mounted on SCO running in a dual XEON machine.And i think SCO it's more stable than Linux. Of course who use mainframe don't must have any concern. Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Svensson, B.A.T. (HKG) [EMAIL PROTECTED] To: Gelu [EMAIL PROTECTED] Cc: Jason Yates [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, May 01, 2002 7:32 PM Subject: RE: mysql speed concerns With IPC I mean Inter Process Communication - any
FullText Search 3.23.49-nt
Good evening List,
I have a problem on my fulltext-search. If I create the sample table from
MySQL-Doc it runs perfect.
If I create my own - it doesn't. I can't say more: Here is my dump:
CREATE TABLE tcl (id int(10) primary key not null, description varchar(254),
date timestamp, value int(11), department tinyint(3), text text,
fulltext(description,text))
If I take a look on my table stucture (using phpmyadmin or describe) it
seems ok.
Now I do some inserts like this:
INSERT INTO tcl VALUES(1,'dummy',,0,1,'lorem ipsum lorem ipsum');
If I take a look again it look harmless.
Now I do this
SELECT * FROM tcl() WHERE MATCH(description,text) AGAINST('ipsum')
or
SELECT tcl.'text' FROM tcl() WHERE MATCH(text) AGAINST('ipsum')
And nothing happes, this means, it retuns 0 rows.
Any suggestions?
Jan
-
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
Re: ensuring that I'm getting the correct last insert ID
Just make sure you call the last_insert_id() function before returning the connection to the pool. If you're using the mm.mysql driver in Java, you can cast the statement object to an org.gjt.mm.mysql.Statement object and use it's getLastInsertID() method: long lastInsertID = ((org.gjt.mm.mysql.Statement)stmt).getLastInsertID(); A connection pool of this sort can't share connections between different users simultaneously. The MySQL protocol only allows one user per connection at any instant. So, as long as you grab the last insert id before returning the connection to the pool, you will be fine. --jeff - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, May 02, 2002 8:36 AM Subject: RE: ensuring that I'm getting the correct last insert ID if you are using a connection pool the [close database connection] part of your example doesnt actually close the connection. so 1. Your code might be using several connections 2. Your code might use a different connection for each statement. 3. Even if your code only does use the one connection, someone else might be sharing it. asides from that , you dont have to close your database connection to execute multiple queries in java. You may have to create multiple statement objects though(depending on the type of query) -Original Message- From: denonymous [mailto:[EMAIL PROTECTED]] Sent: 02 May 2002 08:36 To: Sean O'Donnell; [EMAIL PROTECTED] Subject: Re: ensuring that I'm getting the correct last insert ID But would the same instance of a script drop and pick up different connections? I haven't done any Java/MySQL work, but anything I've done with PHP or Perl is based on a model of: [begin script] [open database connection(s)] [execute queries] [close database connection(s)] [end script] Is it more common in Java to do something like: [begin script] [open database connection] [execute query] [close database connection] [open database connection] [execute query] [close database connection] [etc...] [end script] ? - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, May 02, 2002 11:28 AM Subject: RE: ensuring that I'm getting the correct last insert ID its used a lot by java. As setting up and closing down connections to the database is relatively expensive, you use a pool manager, when the connection is closed by your code, it gets returned to the pool of open connections and is'nt actually closed. just held open and returned next time you want a connection. So I imagine if you have 20 users on the site at once, then they could all be executing various snippets of sql over 5 connections. -Original Message- From: denonymous [mailto:[EMAIL PROTECTED]] Sent: 02 May 2002 08:27 To: Sean O'Donnell; [EMAIL PROTECTED] Subject: Re: ensuring that I'm getting the correct last insert ID Admittedly, I'm no expert. What *is* connection pooling? - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, May 02, 2002 11:21 AM Subject: RE: ensuring that I'm getting the correct last insert ID what happens if you are using connection pooling though? -Original Message- From: denonymous [mailto:[EMAIL PROTECTED]] Sent: 02 May 2002 08:09 To: [EMAIL PROTECTED] Subject: Re: ensuring that I'm getting the correct last insert ID From: Jonnycattt [EMAIL PROTECTED] Hi all, I know this has been asked a bunch of times, but i need some clarity (new mySQL user). I have an app that inserts a new user into one table, then inserts some user preferences into another table. the procedure is as follows: 1) insert new user 2) query for that user's id using select max(userID) as LastUserID from .. 3) insert into user preferences table using the previous query's LastUserID. To be clear, this last insert adds mutliple rows to a table, not one row. If I were you, I'd use MySQL's LAST_INSERT_ID() function: http://www.mysql.com/doc/M/i/Miscellaneous_functions.html So long as your ID field is AUTO_INCREMENT, this will return the last auto-generated field in the current handle. Something like this: INSERT INTO UserTable... (your first user insert) SELECT LAST_INSERT_ID() FROM UserTable (this will return the userID of the user you just inserted) INSERT INTO OtherTables (pass the userID you just got to these queries) You'd mentioned worries that a user could be added while another user was still being processed, and the result would be the wrong userID being returned. LAST_INSERT_ID() is handle-based, though, so there should be no worries with that -- the sessions will be kept separate. Hope this helps! -- denonymous . : . : .
Re: Re: How to Count(*) with LIMIT
That would give the correct result, but it would still physically count all the rows in the table which takes too long. This code will execute every time a web page opens that has a grid. Some of the grid pages are quite large, 1 million rows. The person who designed the web page originally didn't think that would matter much. I keep telling him you can't count 1 million rows every time a web page opens. The site would probably support around 5 people.g I will go with the mysql_num_rows which shouldn't add any additional CPU or I/O. It seems like a perfect solution once I move some code around. Thanks. Mike MySQL's COUNT(*) function is optimized to quickly return the total number of rows in a table. Check the first definition on this page: http://www.mysql.com/doc/G/r/Group_by_functions.html Using a different SELECT statement and calling mysql_num_rows will return the entire result set, which is what you are trying to avoid. So, if you want to support more than 5 people on your site, use COUNT(*). --jeff - 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
RE: FullText Search 3.23.49-nt
Try not using the word 'text' as your column name. Using reserved and key words causes problems. Chris sql CREATE TABLE tcl (id int(10) primary key not null, description varchar(254), date timestamp, value int(11), department tinyint(3), text text, fulltext(description,text)) - 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
Re: FullText Search 3.23.49-nt
Fulltext search is based on relevance. If the words you're searching for
appear in over 50% of the rows, MySQL assumes they aren't relevant because
they occur too often. Try inserting more rows with different info in the
fields that have the fulltext index -- then try your search again.
--jeff
- Original Message -
From: Jan Peuker [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Thursday, May 02, 2002 10:24 AM
Subject: FullText Search 3.23.49-nt
Good evening List,
I have a problem on my fulltext-search. If I create the sample table from
MySQL-Doc it runs perfect.
If I create my own - it doesn't. I can't say more: Here is my dump:
CREATE TABLE tcl (id int(10) primary key not null, description
varchar(254),
date timestamp, value int(11), department tinyint(3), text text,
fulltext(description,text))
If I take a look on my table stucture (using phpmyadmin or describe) it
seems ok.
Now I do some inserts like this:
INSERT INTO tcl VALUES(1,'dummy',,0,1,'lorem ipsum lorem ipsum');
If I take a look again it look harmless.
Now I do this
SELECT * FROM tcl() WHERE MATCH(description,text) AGAINST('ipsum')
or
SELECT tcl.'text' FROM tcl() WHERE MATCH(text) AGAINST('ipsum')
And nothing happes, this means, it retuns 0 rows.
Any suggestions?
Jan
-
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
-
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
Re: User Variables and Previous Row Question
Hi, Jasmin Bertovic wrote: cut For example; SELECT number as current_day, (number - prevnumber) as change_from_prev_day FROM TABLE ORDER BY DATE prevnumber is the reference that I need from the previous row. Am I missing something simple or do I have to do this outside of MYSQL? 1. Yes you are missing something very basic (not simple). Tables in Relational Database Model are defined as Sets of Items. I.e. there is no Internal order of table. Server is free to store and retrieve rows in any order. ORDER BY clause is applied After rows are retrieved and processed (row by row) If you try to tranlsate your Query into Unordered Set terms it will sounds like: For each member of set do something with it and After that sort the result according to ... But since Set has no order you can not say For each member use Prev member 2. In your case there might be solution :) But it is very specific. Assuming date column is Primary Key you can just join table to it self SELECT t1.date_col, t1.num, t2.date_col, t2.num) FROM yourtable AS t1, yourtable AS t2 WHERE t1.date_col = t2.date_col + INTERVAL 1 DAY; Above is just for your info - to see what is happening. Having all these columns you can: SELECT t1.num as current, (t1.num - t2.num) as diff FROM yourtable AS t1, yourtable AS t2 WHERE t1.date_col = t2.date_col + INTERVAL 1 DAY; The only trick is to deal with 1st date because there is no previous, but I will let this excercise to you :) - 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
Re: Re: GUI managers for Linux
Victoria, You asked me to be sure that the information going into the MyCC dialog box about the server was correct, and it is, port, user, password, and host, but still no connect, php can even connect to the database with the server/user/password triple. so back to my original question, why cant mycc connect to the database, and why is the server refusing an apparently valid connection request. MyCC does support connection via sockets, and Im not using sockets, should I change just to get it running? Rance From: Victoria Reznichenko [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: Re: GUI managers for Linux Date: Wed, 1 May 2002 13:00:01 +0300 Rance, Wednesday, May 01, 2002, 12:19:42 AM, you wrote: RH Victoria, sorry it has taken so long to get back to you, Ive had a partition RH table problem that has taken a couple of days to fix RH anyway, RH Using MyCC, in the database connection dialog, I have the host localhost RH a valid user name, and password RH Identified the port as the default 3306 but dont know if this is the right RH answer for my server, or how to find out. You can check it by command: SHOW VARIABLES LIKE 'port'; RH no other boxes checked on the front page of the connection dialog. RH When I try to connect using this information, I get Error 10061: Connection RH to Mysql Server Failed. This error occures when server refused connection ... Rance, check if all data (username, password, host, port) is correctly. I know it's a bit of bore request, but I ask you to check it. From: Victoria Reznichenko [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: GUI managers for Linux Date: Mon, 29 Apr 2002 16:48:02 +0300 Rance, Sunday, April 28, 2002, 2:44:45 AM, you wrote: RH Ive downloaded and sucessfully installed both mysqlgui, and mycc from the RH mysql.com site RH for some wierd reason I cant connect to the database with either of them RH I can with the text mysql client, and the text mysqladmin RH I have created a database, and granted rights to that database to a user. RH that user can log in using mysql -u username -p RH This is a Mandrake Linux 8.2 box running mysql 3.23.47 RH mysqlgui version 1.7.4 RH mycc version 0.8.2 alpha Did you fill up entries for connection? (Database Connection Dialog in MyCC and Options in MySQLGUI) What errors did you receive? RH any hints much appreciated RH Rance -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - 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 _ Join the worlds largest e-mail service with MSN Hotmail. http://www.hotmail.com - 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
RE: backslash about MySQL++/Microsoft Visual C++ 6.0 (2)
Hi,
I have had a similar problem...
This also occurs with ' or .
in any field you expect this character use:
REPLACE(c:\mysql\test,\,/)
Returns the string str with all all occurrences of the string from_str
replaced by the string to_str:
mysql select REPLACE('www.mysql.com', 'w', 'Ww');
- 'WwWwWw.mysql.com'
to change '\' to '/'
Rich
-Original Message-
From: Joerg Geistmann [mailto:[EMAIL PROTECTED]]
Sent: Thursday, May 02, 2002 1:59 PM
To: Mysql-De; MySQL Win32; MySQL Plusplus
Cc: Lazy Fox
Subject: RE: backslash about MySQL++/Microsoft Visual C++ 6.0 (2)
hello again!
sorry, i know that i must use dopple backslash in c++ ;-)
my problem is really to get backslash in database about
mysql++/vc (1.7.1)
also i try
Query insert into blafasel values ('1', 'c:\\mysql\\test', '0');
i doesn´t work!
a hint?
if i do it manual about the console and i use ' in values
it doesnt work too! if i use it works !!!
any idea?
@Darin:
Use the API function mysql_escape_string to prepare the value for
insertion.
how can i use it? for the complette string or only for the backslash
character?
thanks,
jörg
hello there? :-)
in which way can i handle backslashes in mysql++/vc?
if i make a insert command like:
Query insert into blafasel values ('1', 'c:\mysql\test', '0');
in c++
i get no backslash in db!
if i do it manual about the console it works and i have a
backslash in the db!
somebody any idea?
thanks,
jörg
-
Please check
http://www.mysql.com/Manual_chapter/manual_toc.html; before
posting. To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, send a message to the address shown in the
List-Unsubscribe header of this message. If you cannot see it,
e-mail [EMAIL PROTECTED] instead.
-
Please check http://www.mysql.com/Manual_chapter/manual_toc.html; before
posting. To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, send a message to the address shown in the
List-Unsubscribe header of this message. If you cannot see it,
e-mail [EMAIL PROTECTED] instead.
-
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
Re: User Variables and Previous Row Question
This makes sence, now that I have had some time to think about it. If I self join, I have to make sure that my data is continuous to shift all the rows by 1. Some of the things could have missing dates or maybe I could self join on an incremental counter using a tmp table. I have do go back to 'drawing board' and do some more thinking. This is great info. Thanks again. --- Alexander Keremidarski [EMAIL PROTECTED] wrote: Hi, Jasmin Bertovic wrote: cut For example; SELECT number as current_day, (number - prevnumber) as change_from_prev_day FROM TABLE ORDER BY DATE prevnumber is the reference that I need from the previous row. Am I missing something simple or do I have to do this outside of MYSQL? 1. Yes you are missing something very basic (not simple). Tables in Relational Database Model are defined as Sets of Items. I.e. there is no Internal order of table. Server is free to store and retrieve rows in any order. ORDER BY clause is applied After rows are retrieved and processed (row by row) If you try to tranlsate your Query into Unordered Set terms it will sounds like: For each member of set do something with it and After that sort the result according to ... But since Set has no order you can not say For each member use Prev member 2. In your case there might be solution :) But it is very specific. Assuming date column is Primary Key you can just join table to it self SELECT t1.date_col, t1.num, t2.date_col, t2.num) FROM yourtable AS t1, yourtable AS t2 WHERE t1.date_col = t2.date_col + INTERVAL 1 DAY; Above is just for your info - to see what is happening. Having all these columns you can: SELECT t1.num as current, (t1.num - t2.num) as diff FROM yourtable AS t1, yourtable AS t2 WHERE t1.date_col = t2.date_col + INTERVAL 1 DAY; The only trick is to deal with 1st date because there is no previous, but I will let this excercise to you :) - 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 __ Do You Yahoo!? Yahoo! Health - your guide to health and wellness http://health.yahoo.com - 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
Re: Re: Re: ensuring that I'm getting the correct last insert ID
Gotcha. So far, selecting the last_insert_id() is working. I'll see what the CF forums have to say. thanks. --- Original Message From: Jeff Kilbride To: Jonnycattt CC: Subject: Re: Re: ensuring that I'm getting the correct last insert ID Date: 02/05/02 I don't have any experience with ColdFusion, but you should be able to execute more than one SQL statement per connection. sounds like it should work -- you can't break a transaction across multiple connections, either. You might try asking this on a ColdFusion list, or re-post it to the MySQL list with something like ColdFusion and last_insert_id as the subject. Maybe you'll find someone with relevant experience. I think using the Lock Tables strategy should be your last resort. It will severely impair performance. --jeff - Original Message - From: Jonnycattt To: Jeff Kilbride Sent: Thursday, May 02, 2002 2:03 PM Subject: Re: Re: ensuring that I'm getting the correct last insert ID I'm doing this from ColdFusion, so I don't think I have control over when I can return the connection. Typically in ColdFusion, multiple queries such as this are handled with wrappers. I can use to ensure I always have the right ID, but I'm wondering if there's a better way...a native mySQL wayto do this. What if i use Lock Tables around the first insert and the select? then unlock, then do my second insert? --- Original Message From: Jeff Kilbride To: [EMAIL PROTECTED] , [EMAIL PROTECTED] , [EMAIL PROTECTED] CC: Subject: Re: ensuring that I'm getting the correct last insert ID Date: 02/05/02 Just make sure you call the last_insert_id() function before returning the connection to the pool. If you're using the mm.mysql driver in Java, you can cast the statement object to an org.gjt.mm.mysql.Statement object and use it's getLastInsertID() method: long lastInsertID = ((org.gjt.mm.mysql.Statement)stmt).getLastInsertID(); A connection pool of this sort can't share connections between different users simultaneously. The MySQL protocol only allows one user per connection at any instant. So, as long as you grab the last insert id before returning the connection to the pool, you will be fine. --jeff - Original Message - From: To: ; Sent: Thursday, May 02, 2002 8:36 AM Subject: RE: ensuring that I'm getting the correct last insert ID if you are using a connection pool the [close database connection] part of your example doesnt actually close the connection. so 1. Your code might be using several connections 2. Your code might use a different connection for each statement. 3. Even if your code only does use the one connection, someone else might be sharing it. asides from that , you dont have to close your database connection to execute multiple queries in java. You may have to create multiple statement objects though(depending on the type of query) -Original Message- From: denonymous [mailto:[EMAIL PROTECTED]] Sent: 02 May 2002 08:36 To: Sean O'Donnell; [EMAIL PROTECTED] Subject: Re: ensuring that I'm getting the correct last insert ID But would the same instance of a script drop and pick up different connections? I haven't done any Java/MySQL work, but anything I've done with PHP or Perl is based on a model of: [begin script] [open database connection(s)] [execute queries] [close database connection(s)] [end script] Is it more common in Java to do something like: [begin script] [open database connection] [execute query] [close database connection] [open database connection] [execute query] [close database connection] [etc...] [end script] ? - Original Message - From: To: ; Sent: Thursday, May 02, 2002 11:28 AM Subject: RE: ensuring that I'm getting the correct last insert ID its used a lot by java. As setting up and closing down connections to the database is relatively expensive, you use a pool manager, when the connection is closed by your code, it gets returned to the pool of open connections and is'nt actually closed. just held open and returned next time you want a connection. So I imagine if you have 20 users on the site at once, then they could all be executing various snippets of sql over 5 connections. -Original Message- From: denonymous [mailto:[EMAIL PROTECTED]] Sent: 02 May 2002 08:27 To: Sean O'Donnell; [EMAIL PROTECTED] Subject: Re: ensuring that I'm getting the correct last insert ID Admittedly, I'm no expert. What *is* connection pooling? - Original Message - From: To: ; Sent: Thursday, May 02, 2002 11:21 AM Subject: RE: ensuring that I'm getting the correct last insert ID what happens if you are using connection pooling though? -Original Message- From: denonymous [mailto:[EMAIL PROTECTED]] Sent: 02 May 2002 08:09
ID - sort problem
I have one table on my computer and one remote to my host on Internet. I have this problem: I use = ALTER TABLE hotels1 DROP ID; ALTER TABLE hotels1 AUTO_INCREMENT=1, ADD ID int unsigned not null auto_increment default '0' first, ADD primary key (ID); to refresh the ID starting from 1. BUT: In my remote table, the records are displayed with ID *AND* Name in accending order wich doesn't happent to my local table: ID NAME 1ACH 2ADA 3ARIS ecc. (in remote table) ID NAME 1 XEN 2 VER 3 TOUR ecc (in local table) So how is possible to change the local table - I suppose this is only possible- to set the IDs identicals to Local+remote tables? I need this to pass the UPDATEs I make to local table by coping the query to remote table. PS. I deleted all recs on remote table, I export the data from my local and re-create the data to remote and I run the above query , still the same. I think something is different on Internet server's MySQL config. Thanks Makis - 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
How to upgrade MySql?
Hi, We have a Web server with Apache 1.3.22+MySql 3.23.44+php 4.0.6 on Solaris 8. Now we want to upgrade MySql to latest version. Should I re-compile both Apache, MySql, and PHP or just re-compile MySql? The question is how to upgrade MySql on Apache server with multi-application? TIA - 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
RE: Re: RE: confirm subscribe to mysql@lists.mysql.com
[EMAIL PROTECTED] wrote: Your message cannot be posted because it appears to be either spam or simply off topic to our filter. To bypass the filter you must include one of the following words in your message: sql,query If you just reply to this message, and include the entire text of it in the reply, your reply will go through. However, you should first review the text of the message to make sure it has something to do with MySQL. Just typing the word MySQL once will be sufficient, for example. You have written the following: __ Your favorite stores, helpful shopping tools and great gift ideas. Experience the convenience of buying online with Shop@Netscape! http://shopnow.netscape.com/ Get your own FREE, personal Netscape Mail account today at http://webmail.netscape.com/ __ Your favorite stores, helpful shopping tools and great gift ideas. Experience the convenience of buying online with Shop@Netscape! http://shopnow.netscape.com/ Get your own FREE, personal Netscape Mail account today at http://webmail.netscape.com/ - 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
Re: Good color coded SQL editor for MySQL?
Hmm. I've been using the genreic sql-mode that comes with GNU Emacs, and it works well. What (if anything) about sql-mode do you have in your .emacs file (or the windows eqivelant)? Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Jeremy The only entries I have in my .emacs file relating to the sql mode are entries to use sql mode when entering .sql and .ddl files and to create a list of associations: (setq sql-association-alist '((dhist (BLKSRVD1 user pass) dev blk) )) I've tried adding a mysql entry using localhost as the server but this hasn't worked. The server names (BLKSRVD1 above) refer to entries in my sql.ini file which is the equivalent of the Unix interfaces file. I'm unclear how to point at the MySQL server Anyone got this working from Emacs on Win2k / NT ? Cheers Stew -- Stewart Douglas, Snowgold http://www.snowgold.com [EMAIL PROTECTED] - 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
FW: How to upgrade MySql?
Hi, We have a Web server with Apache 1.3.22+MySql 3.23.44+php 4.0.6 on Solaris 8. Now we want to upgrade MySql to latest version. Should I re-compile both Apache, MySql, and PHP or just re-compile MySql? The question is how to upgrade MySql on Apache server with multi-application? TIA - 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
