Re: Query to CSV
Craig Newlander wrote:
> Hello,
> How can I execute query and have it's output (just data, no structure)
> saved to a CSV file?
> Thanks,
>From the command line? AFAIK mysql will only output tab separated results
so you have to do something like:
echo "SELECT 'a', 'b', 1" |mysql db |perl -ne 'chomp; print join(",", map {
m/\D/ ? qq("$_") : $_ } split/\t/)."\n"' > yourfile.csv
This is a bit simplistic, e.g. it will not handle columns that have quotes
in the result, etc..
Also, don't forget the first line returned contains the column names.
--Bill
-
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: test
[EMAIL PROTECTED] wrote: > No everyone is probably just busy working on the new release and getting it > installed. Give it a day and you'll see plenty of E-mail. > > And Welcome aboard!! > > M;) > > > Welcome Howard! This is my first day to the list. Seems rather > > slow...wonder if its always like this. Heh, just wait until the weekend passes. --Bill mysql - 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: Creating indexes on large tables
Apparently you have to compile for large file support and have glibc > 2.1.3. http://www.suse.de/~aj/linux_lfs.html I have not used it so I do not know the real-world limitations. Or (for 100MB files): mysqldump db table |split -b 1 - table --Bill Mike Lucente wrote: > I'm also unable to create files >2GB with mysqldump, even after a > recompile with gcc 2.96, RH 7.1 w/2.4.2 kernel, glibc 2.2.2. > > hmmm ... > > On Wed, 3 Oct 2001, Adams, Bill TQO wrote: > > > Perhaps your index file (.MYI) is growing larger than 2GB, the file-size > > limit on 2.2.x kernels? > > > > Or, if you have moved the tables with symlinks, MySQL will put the new file > > (most of the time?) in the configured data directory and not where the > > symlink points. > > > > --Bill > > > > > > > > Mike Lucente wrote: > > > > > I'm running out of space while creating indexes on some fairly large (1.8 > > > GB) tables, even though I have quite a bit of space available in the > > > partition (utilization is at 30%). > > > > > > I know that the create process works as follows (from the manual): > > > > > > Create a new table named `A-xxx' with the requested changes. > > > All rows from the old table are copied to `A-xxx'. > > > The old table is renamed `B-xxx'. > > > `A-xxx' is renamed to your old table name. > > > `B-xxx' is deleted. > > > > > > Given that, I should have plenty of room. Disk utilization doesn't appear > > > to exceed 60% during the process. > > > > > > Any idea what could be causing this? > > > > > > ** Running RH 7.1 on a Dell 2450. MySQL version 3.23.29a-gamma-log. > > > > > > - > > > 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 > > > > -- > > Bill Adams > > TriQuint Semiconductor > > > > > > > > > > - > > 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 -- Bill Adams TriQuint Semiconductor - 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: Creating indexes on large tables
David Turner wrote: > If this is the case. What are the steps necessary for index rebuilds? Can > I specify where the index file is rebuilt? Any idea when we can specify > the location of datafiles and indexfiles? For the kernel: You either need to upgrade to a later 2.4.x series kernel which, I believe, have the 64-bit file size capability. However, MySQL still has limitations on the table size (I do not know about the index file size 'though, see the manual about that). Another possibility is to use merge tables to break your table and index files into smaller chucks. But that can be error prone so just be prepared to ask some questions. ;-). You can change the location of the data/index files in the config file and/or in safe_mysqld. The problem only happens if you have moved the files via sym links. If you move the entire directory then it is not a problem: Okay (example): cd /usr/local/mysql/var ls -l good_db -> ../../../path/to/big/disk Bad: cd /usr/local/mysq/var ls -l bad_db cd bad_db ls -l table1.MYD -> ../../../path/to/big/disk/table1.MYD When you reindex/repair, etc. table1, it will be moved back to the bad_db directory. The only time you should do something like the second case is if you have multiple disks and want to put different tables on different disks for faster access (e.g you will access all of the tables at the same time). Of course SCSI is best for this and/or if each disk is on its own controller. Separating tables on two IDE disks on the same bus is pointless. --Bill - 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: Creating indexes on large tables
Perhaps your index file (.MYI) is growing larger than 2GB, the file-size limit on 2.2.x kernels? Or, if you have moved the tables with symlinks, MySQL will put the new file (most of the time?) in the configured data directory and not where the symlink points. --Bill Mike Lucente wrote: > I'm running out of space while creating indexes on some fairly large (1.8 > GB) tables, even though I have quite a bit of space available in the > partition (utilization is at 30%). > > I know that the create process works as follows (from the manual): > > Create a new table named `A-xxx' with the requested changes. > All rows from the old table are copied to `A-xxx'. > The old table is renamed `B-xxx'. > `A-xxx' is renamed to your old table name. > `B-xxx' is deleted. > > Given that, I should have plenty of room. Disk utilization doesn't appear > to exceed 60% during the process. > > Any idea what could be causing this? > > ** Running RH 7.1 on a Dell 2450. MySQL version 3.23.29a-gamma-log. > > - > 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 -- Bill Adams TriQuint Semiconductor - 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: repost: Too many open files
Colin Faber wrote: > it sounds like you've run out of file descriptors, I suggest > rebuilding your kernel to handle more. > > In a bsd kernel you can do this simply by upping the maximum number of > users allowed to access the machine at any given time. Or in Linux (in my rc.local): echo "Set max files to 32768" echo 32768 >/proc/sys/fs/file-max --Bill mysql - 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: sql syntax INSERT
Robert Martin wrote: > Hi, > > I?m still learning sql so I hope this doesn?t sound to basic. > > I would like to find out if there is a way to insert a record only when (X and Y) do >not exist. > I have the value to check against stored in a variable. ALTER TABLE table ADD UNIQUE u_xy_idx ( x, y ); Then when you go to insert a duplicate x,y you will get an error from MySQL. --Bill - 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 many OR, AND in a query?
Gerald Clark wrote: > >> You might want to try: > >> > >> WHERE mycolumn in (1, 3, 23, ...) > >> > >> Or even better, put your thousand records into a temporary table and then > > > > just do a join. > > > >> --jfarr I have yet to have a query that is to long when talking to MySQL via DBD, however, I believe that ODBC has some limitations either compiled in or set at run/query time. --Bill - 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 question!
As a side note, be sure that you do not use a float for any columns that deal with money or you will get rounding errors. --Bill mysql Yen-Chu Chen wrote: > Hi, > >Assume the name of the column is 'price' and the name of the table is > 'table', you could use > > SELECT SUM(price) FROM table; > > On Wed, 26 Sep 2001, Micke wrote: > > > Does anyone know how to write a query to get out the sum of a whole > > table > > column? > > > > ex. I'm playing around with a database where customers can buy stuff. > > I have a table where I save all the invoices, but now I want to write > > a query that sums upp all the prices in the price column in that > > table. > > > > Is this possible? And if, how??? > > > > > > Mikael Hultén - 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: Table 'mysql.host' doesn't exist when try to start mysqld
Amanda Shuler wrote: > I get the following error when I try to start to start mysqld: > > 010926 2:23:55 /usr/libexec/mysqld: Table 'mysql.host' doesn't exist > > I am a total newb and I don't have any idea how to get around this. > > Any ideas? Who ever installed mysql did not run 'scripts/mysql_install_db.sh' Said person should read INSTALL-SOURCE in the source tar ball. --Bill - 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: can any one help please?
David Iyoha wrote: > and the above does not seem to work I am loosing my mind > Did you do a 'mysqladmin reload' after you added the permissions? --Bill - 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: [Q]How to delete recursively
In MySQL the best (only?) solution I know of is to write a script (e.g. in
perl or python) that will traverse the tree for you.
e.g:
select a_keys from A
foreach key ( a_keys ){
select c_keys from B
... etc.
delete from b where key in ( a_keys )
If you were really looking for punishment you could create a table of
meta-data that descibed how the tables were related and then use that to do
the cascade delete. And of course, LEFT JOIN is your friend for finding
reocrds in lower tables that do not have a matching reocrod in the parent
table, e.g.: SELECT b_keys FROM B LEFT JOIN A on ( common_key ) where
A.common_key IS NULL;.
--Bill
½ÉÃ溸 wrote:
> Hi.
> I came across serious problem.
> There is hierarchy among groups listed below like directory structure.
>
> A(group)-B(group)
> |
> -C(group) D(group)
> |
>E(group)
> |
>F(group)
>
> I want to delete A group. This requires deletion of all child groups.
> Above figure is a little simple but if this tree grows large that's my
> problem.
>
> Would you please tell me the answer?
>
> For reference table is like this.
>
> Field Type
> groupname varchar(50)
> groupno int
> p_groupno int <- This means parent group no.
>
> Thanks in advance!!
>
> -
> 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
--
Bill Adams
TriQuint Semiconductor
-
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: DBI on FreeBSD & MySQL
Chris Aitken wrote: > I am writing a perl script on FreeBSD 4.3 running PHP and MySQL, however i > get the following error message when I try and use DBI to connect to a > MySQL database to pull some data. > > Can't locate DBI.pm in @INC (@INC contains: /usr/libdata/perl/5.00503/mach > /usr/libdata/perl/5.0 > 0503 /usr/local/lib/perl5/site_perl/5.005/i386-freebsd > /usr/local/lib/perl5/site_perl/5.005 .) a > t /usr/sbin/scriptname line 9. > > Is there something I have not installed or do i have to modify a setup file? > I have installed mod_perl and p5_apache_DBI and still getting this error. > > Any suggestions would be welcomed. This is really a perl/BSD question but... I doubt that p5_apache_DBI is the right port. You want pure 'DBI' and 'DBD::mysql' ports. Of course you can always either d/l the source for DBI, et. all from your favorite cpan mirror or do: su - perl -MCPAN -e 'install DBI' If you have further questions, please direct them to a perl or FreeBSD list. Thanks. --Bill - 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: DBI.pm
cedric wrote: > /usr/bin/mysql_setpermission returns the following: > Can't locate DBI.pm in @INC (@INC contains: ) > > Perl 5 is installed. > What do I do? This is really a perl question. But... su - perl -MCPAN -e 'install DBI' Or d/l the DBI code from your favorite CPAN mirror and install it manually. If you do not understand either of those, please direct your question to a perl list. Thanks. --Bill mysql - 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 to return products given matches on attributes
Or: SELECT DISTINCT T1.item_number FROM Attributes t1, Attributes t2, Attributes t3 WHERE t1.item_number=t2.item_number AND t2.item_number=t3.item_number AND t1.attribute='sport' AND t1.value='football' AND t2.attribute='league' AND t2.value='nfl' AND t3.attribute='typeb' AND t3.value='jersey'; However, I agree that the table layout should be redone. If you know that you are always going to have a sport, league, and typeb associated with an item_number, you should at least make them columns in a main table. Doing the self-join as above is general but also CPU expensive. I do not know about the limitations of MySQL but do not attempt a 6-level self join on a large table in Informix unless you are planning on watching a movie while it runs. --Bill Jason Clark wrote: > A book I've found very usefull is 'MySQL and mSQL' by O'Reilly > > You could use a query like this, but... > > "select distinct item_number from Attributes where attribute like 'football' > and value like 'nfl' " > > You will be happier if you rethink your layout and create multiple tables. > Give them unique identifiers (to link them) and your job will be much > easier. I can give you some suggestions if you like. > > ^ ^ > . . > >( O )< > M > ~ Seien Sie eins mit dem Rad ~ > - Pruf-Gerbil - > > -Original Message- > From: Chris Haupt [mailto:[EMAIL PROTECTED]] > Sent: Thursday, September 20, 2001 4:42 PM > To: [EMAIL PROTECTED] > Subject: Query to return products given matches on attributes > > Hello, > > I have a question about the best way to pose this question: > > We have a table named Attributes: > +-+--+--+-+-++ > | Field | Type | Null | Key | Default | Extra | > +-+--+--+-+-++ > | item_number | varchar(50) | YES | | NULL|| > | attribute | varchar(20) | YES | | NULL|| > | value | varchar(30) | YES | | NULL|| > +-+--+--+-+-++ > > Sample values for an item from Attributes > +-+--++ > | item_number | attribute| value | > +-+--++ > | OGI-02035 | Price| LT_75 | > | OGI-02035 | Sport| Football | > | OGI-02035 | League | NFL| > | OGI-02035 | TypeB| Jersey | > +-+--++ > > An example query I'm trying to figure out is: > Return all item_numbers where sport="football", league="nfl" and > typeb="jersey" > > Any help would be appreciated. Also any pointers to good books that > people have found that help them solve such problems. > > Thanks, > > Chris > O N L I N ES P O R T S = > Chris Haupt[EMAIL PROTECTED] > Online Sportshttp://www.onlinesports.com > Phone: 760-839-9363 Fax: 760-839-9370 > Directory of Thousands of Sports Items Available Online Today! > P R O D U C T S&S E R V I C E S > > - > 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 -- Bill Adams TriQuint Semiconductor - 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: MyODBC question
Valentin Kulikov wrote: > while:I have set 'myname' as a user name. Somebody (MyODBC? Windows2000?) > added a suffix '@mydialup-provider.com' to my db user name. > Or maybe a problem is in something else and a diagnostic is incorrect? Unless you connect to MySQL via a unix socket, the hostname of the client box will always be added to the connection string. That is for security and I believe MySQL does it. You (or your admin) needs to add an entry into the user or db table for your dial-up host. --Bill - 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: Bad table and mysql 3.23.42
Peter Zaitsev wrote: > I'm quite lucky with providing with bad tables which does not repear > properly or having other strange behavior. Sometimes when myisamchk does not work (or it says it has repaired the table but it quickly become corrupted again), I find that dumping and reloading the table is the best, sure way to repair it. mysqldump db table --add-drop-table >somefile.sql mysql db 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: Portable MySQL?
Mike wrote: > In working with some non profit groups I need to be able to send a complete > program that is installed and ran fairly easy. > To me MySQL is easy in most cases but to someone not familiar with it it can > be a daughtening task. Is there a way to send a setup program using MySQL? > Most of the time it's just a few tables so if there was an alternative way > to make the tables and use them in a program that would be great. In UN*X land, you could send a shell script, e.g.: #!/bin/sh # echo "Setting up the test_table..." mysql db -u user -ppassword <<'EOF' DROP TABLE IF EXISTS test_table; CREATE TABLE test_table ( a char, b char ); EOF echo "Done." Not really portable, 'thogh. --Bill - 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: creating tables using a number
powlow wrote: > first posting to the list so hello. My name is Paulo. I live and work in > lisbon, portugal. > > question : i want to create a table each month. want to call it somehting > like 012001 for January 2001. mysql does not seem to allow this. Jan2001 is > fine. d122001 is also fine. is it not possible to create tables with only a > number as the name? is there a way round this? Most RDBMS do not allow columns or tables to start with a number. I know that does not help your situation but it is not unusual. Also, why don't you put a prefix on the table name that indicates what it is going to hold? Another developer that sees a table called '012001' is not going to know what the heck the table is. But a name like 'custorders_200101' is more informative and gets around your problem. (I would always name dates in the order of year, month, day. Then you can sort and compare them with ease in just about any language .) --Bill - 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: Password not working.
Dave McNicholl wrote: > Hi, > I have had a mysql database for a few years now. It is used for auth > against apache using mod_auth_mysql. My apache conf has the username and > password in it. These have not changed for some time. > Now when I use mysqldump -u username -p mysql and supply the correct > password I get. > mysqldump: Got error: 1045: Access denied for user: 'username@localhost' > (Using password: YES) > It is the same for mysql command line. Have you tried specifiying the host? mysqldump dbname -u username -pPassword -h your.host.name --Bill - 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: DBI-1.18.tar.gz Can't gzip -cd
rjtalbo wrote: > I have been trying to uncompress DBI-1.18.tar.gz which I downloaded > together with > Msql-Mysql-modules-1.2216.tar.gz & > Data-ShowTable-3.3.tar.gz > > from the MySQL down load page... > I followed the direction in the DBE-1.18 README but if I use the pipe > as instructed... gzip -cd DBI-1.18.tar.gz | tar xf -cd DBI-1.18 > I get tar: Cannot open -cd: No such file or directory Depending on your OS, One of: zcat DBI-1.18.tar.gz |tar xf - tar xfvvz DBI-1.18.tar.gz gzip -cd DBI-1.18.tar.gz |tar xf - --Bill - 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: Comparing Dates
>I have a date comparison problem that I'm sure is simple enough but I'm just >not experienced enough to figure it out. I simply want to grab every entry Um. First of all, better code would be: my @now = localtime( ); $now[5] += 1900; $now[4] += 1; $currenttime = sprintf( '%04d%02d%02d%02d%02d%02d', @now[5, 4, 3, 2, 1, 0] ); Second, to add/subtract dates in perl and other languages, check out Date::Calc. Third, in MySQL you cand do something like: WHERE some_date>DATE_SUB( NOW( ), INTERVAL 5 DAY) Check out the MySQL manual for more info on DATE_SUB. --Bill - 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: Perl MySQL OO design question
Richard Reina wrote:
> I have a perl->DBI->MySQL database app. that handles everything from my
> order entry to my accounts payable. I am in the process of cleaning up
> a lot of the code I've written to make it easier to maintain. I
> probably use the following DBI staement handle about fifty different
> places on different tables:
>
> use DBI;
> my $dbh =
> DBI->connect("DBI:mysql:database=operations;192.128.0.1","abuser","whisky");
> my $q = "INSERT into table (column, column, column, column) VALUES (?,
> ?, ?, ?)";
> my $sth = $dbh->prepare($q);
> $sth->execute($val1, $val2, $val3, $val4);
>
> Would it be best to create a class called INSERT that all scripts call
> when they need to insert create a new record in a table?
If you are using this in more than one script, I would make a module.
Package LocalModules::GroupName::Insert;
use DBI;
my @dbhs;
return( 1 );
END {
foreach ( @dbhs ){ $_->disconnect( );}
}
sub new {
my $proto = shift;
my $class = ref( $proto ) || $proto;
my $self = {};
my $self->{test_db} = @_ ? shift : 0;
};
bless( $self, $class );
return $self;
}
sub connect {
my $self = shift;
if( defined $self->{DBH} && ref( $self->{DBH} )){
return( $self->{DBH} );
}
my $dbi = join(':', 'dbi', 'mysql', ( $self->{test_db} ? 'test' : 'realdb'
)).';host=some.host';
$self->{DBH} = DBI->connect( $dbi, 'user', 'password' ) or die "Could not
connect to '$dbi';
push @dbhs, $self->{DBH};
$self->{DBH};
}
sub insert {
my $self = shift;
my $dbh = $self->{DBH} || $self->connect( );
unless( @_ == 4 ){
my( $p, $f, $l ) = caller( );
die ref( $self )."::insert( ) -- Need Four Parameters, called from $f line
$l\n";
}
my $sth = $self->{insert_STH} || ( $self->{insert_STH} = $dbh->prepare( $q ))
|| die;
return( $sth->execute( @_ ));
}
__END__
--Bill
-
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: Weird problem
Silver Fox wrote: > I just got this problem... > > bin/mysql -h localhost -u root > logs me into MySQL at the command line > > However, the CGI script for Apache webserver gave me an error connecting to > the database > $source = "DBI:mysql:red:localhost"; > $username = "root"; > $password = ""; > > Did any of you ever face this problem before?? > BTW, I'm running Mandrake Linux... It is probably a problem with the permissions of the /path/to/mysql.sock. The web server usually runs as user nobody so make sure that the path to the mysql.sock has world read and execute. --Bill - 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: Please Advice
Shaji Khan wrote: > Hi, I am a newbie to MySql and I am planning to use MySql and PHP to develop > a shopping cart and other components of a full blown ecommerce site. Is this > a good choice for this kind of work or do you guys think some thing like SQL > Server and ASP is a better choice. > > Experienced users, please advice. If you use MS SQL Server + ASP all of your profits will be eaten by licensing fees. AFAIK, there is nothing that the MS solution has to offer that MySQL+PHP does not. MySQL+PHP is a good choice IMNOHO. --Bill - 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: Limit query to a value
Lorang Jacques wrote: > Hello, > How can I limit the query to those rows where "SUM(value) as Tot > 0". If I > put it in an where clause it get an error. So how to do that > SELECT id,url,title,description,top,keyword,SUM(value) as Tot FROM links > LEFT JOIN keywords on links.id=keywords.LinkId Group by id order by tot desc SELECT id,url,title,description,top,keyword,SUM(value) as Tot FROM links LEFT JOIN keywords on links.id=keywords.LinkId Group by id order by tot desc HAVING Tot>0 --Bill - 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: Let's approach stored procedures
Cal Evans wrote: > Stored procedures allow you to pre-compile and re-use code easily. They also > make transactions easier. (At least from one point of view) It's not like > the XML argument. SPs are also part of the ANSI spec so to be compliant, > MySQL will have to have them. XML is not part of the ANSI-SQL spec. The other thing about all of this is that MySQL is feature rich so most people might not have a use for stored procedures. Unlike, say, Informix which has not build in MIN or MAX function. Again, if having stored procedures built in does not slow down queries that do not use stored procedures, I do not see what the harm is. --Bill - 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: Let's approach stored procedures
Claudio Cicali wrote: > I'm going to attend a long period of holidays (finally) so, > I thought: why don't try to implement stored procedures > in mysql ? > Someone did it with perl. http://software.tangent.org/article.pl?sid=01/08/23/0817244&mode=thread&threshold= > At a first glance, I think Oracle PL/SQL is the best (afaik) > programming language for sp, but, in the case we implement --Bill - 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: Optimization question and possible bug
Stefan Pinkert wrote: > Examine that query with explain select... show that the indexes are not > used. The keyfields are unsigned tiny int where every bit has a different > meaning. Any clue how i can rewrite this query in a way where the indexes > are > used? If MySQL thinks it will be faster to scan, it will. Make sure you analyze the tables with [my]isamchk -a. If that does not help, please send the output from "SHOW INDEX FROM table" and the EXPLAIN. > In the database i have a merge-table that merges 10 myisam tables. > Sometimes the loadaverage of the system raise above 50 and the > long-query-log is > filled with some query accessing the merge table. This happens 2-3 times a > day. > Only a minute later everthing is okay again without doing anything. > I can't believe that it is a performance problem because there is a > summary of only 10,000 entries in the merge table and 50,000 entries in > other > tables. Does anybody experienced this strange problem, too? Is the long query different than the other queries? Have you done an expain on it? > The last thing i found is a possible bug in merge-table implementation > of 2.23.41. After an update from 2.23.37 to 2.23.41 i got only the first > row of the result set again and again. Maybe it's a communication problem > between php (with included mysql interface) and the new MySQL version. > (Haven't determined it yet). Don't know. But read this thread just to be sure: http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:mss:84077:200108:ilgknliamhblokdjmmhb --Bill - 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: Corrupted tables -- for 'no apparent' reason
Mark Papadakis wrote: > We are using mySQL on over 6 servers here, we have been doing so for over 2 years, >and we are constaly facing problems with corrupted tables, especially on two of our >busiest servers. > Tables seem to corrupt out of the blue and we have to shut them down ( the servers ) >occassionaly to fix all tables and then bring them up again. I have found that sometimes (esp. with older versions of MySQL which is not the case for you) that the only way to either repair or permanently repair a table is to dump/drop/reload the table and data. I had some instances a number of years back where the table would repair and be okay with isamchk but would, at a random time, be corrupted again. Doing the dump, etc. fixed the problem. --Bill - 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: Recursive same-table lookup, operator precedence (?)
Balazs Szemes wrote: > Hi, > It is a recursive table design, meaning that a person can have a boss. The > boss' id is stored in the associate_of column. Eg. elmer is barney's > associate, barney is boss of elmer, and diana > > Let's say I only know the user_name 'barney', and I would like to select > all his associates. > > I tried a subquery, as it was most logical: As you found out MySQL does not support subqueries. > > Any help is appreciated. Also, if someone could point me to the right > section in the on-line doc. I did not find stuff on operator precedence or > more than one '=' signs in the same expression. You need to do it in code (C/C++/Perl/Python/Cobal/etc., er C++/C/Python/Perl/Cobal/etc.). AFAIK you cannot have more than one equals sign in a statement in MySQL. --Bill - 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: Auto Importing
"Joshua M. Schmidlkofer" wrote: > Of course. you COULD use Python, cleaner that Perl(tm) [jk no flames > please] Sorry. If you don't program in hex assembly you are a wussy and you drink light beer. > How is your ex-hell [excel] file being generated? Is it a .csv or is it > actually an XLS? We have an excellent csv reader for python I think this is the real question. Why even put the data into Excel to begin with? Write or find a parser in your favorite language that can talk directly to MySQL and skip the Excel route. --Bill "Are you lookin' at me? I don't see anyone else here, you MUST be lookin at me" Adams - 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: Password error
Mike Ryerse wrote: > I have resently installed mysql 3.23.41 on Redhat 7.1 > > When I installed it, I could access the example > databases 'mysql' and 'test'. Mysql said to change > the root password right away with : > mysqladmin -h host -u root -p password 'new password' Note, do not put a space between the -p and the password normally: mysqladmin -h host -u root -ppassword OR mysqladmin -h host -u root --password=password That may be why you cannot connect. > so I did it, but now mysql won't let me log in as any > user, not even root. Did you really put a space in the password? Or was that for demonstration purposes only? > I have tried to re-install with rpm -i --force, and > uninstall with rpm -e, neither will change the > permissions of mysql. > > What should I do? As Gerald Clark said, the manual has a section on how to reset the password. --Bill - 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 Performance Problem
Henning Schroeder wrote: > At 17:52 06.09.01, you wrote: > > >It is copying ALL of the results into the temp table. If you can (e.g. > >you know you will > >never want more than N records), add a LIMIT 0,N to the end of the SELECT > >so that when you > >have an old timestamp it will not hang the database. > > as i use > > select count (*) from blah where blahblah; > > (actually i am just interested whether there is a row or not matching the > criterion. under normal operations there should never be more than one match) > > i don´t understand how a limit 0,1 would help here. wouldn´t it just say > that no more than one count(*) result should be returned (which never the > case anyway because count(*) returns exactly one row)? I misread your email. I thought you had a query that was 'copying into temp table' that was not the COUNT(*) query. Rereading your email, I see that for me it is better to remain silent than remove all doubt. ;-) Do your two queries both take a long time? What if you add a key on type and timestamp (in that order). >select count(*) from adviews where click="f" and uid=7618 and cid=11 and datestamp>=999782664; >select count(*) from chat where type="msg" and timestamp>999783881; --Bill - 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: Auto Importing
Mike wrote: > Is there a way to continualy update a MySQL database with an Excell file? > > Reason: > > Just to have some fun I am building a Code Red hall of shame in PHP and > MySQL. I am anoyed from cleaning XX's and NN's all day in my Apache > logs.And they call Open software Virul... > > And Instead of complaining I thought this might be better to point the ISP > to a page they can see for themselves. > > I can do this daily but an automated way would be nice. Why would you pass the log through Excel? A perl script might be better. In answer to your first question: I do not know about directly from Excel but you could set up a macro in MS Access to get the data from Excel and then update the MySQL table. And then set that to run periodically. If you REALLY hate yourself and Apache/PHP are on the same machine as the Excel file, you could query the Excel spreadsheet via ODBC. But you loose all DBA Karma points if you do. ;-) --Bill - 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 Performance Problem
Henning Schroeder wrote: > i also tried logging the queries that appear often with "copying to temp > table" status and now have a nice set of them, though i don´t quite > understand *why* the are copying. below are two: > (the rows count is *way* to high, probably because the timestamps are > ancient by the time i ran explain select) It is copying ALL of the results into the temp table. If you can (e.g. you know you will never want more than N records), add a LIMIT 0,N to the end of the SELECT so that when you have an old timestamp it will not hang the database. --Bill - 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: fields query
Harald Fuchs wrote:
> In article <[EMAIL PROTECTED]>,
> "Adams, Bill TQO" <[EMAIL PROTECTED]> writes:
>
> >> I need to read the fields of a table dynamically using
> >> DBI.pm
> >>
> >> The resulting cgi script should be a form that has a
> >> checkbox for each field...the user will then create a
> >> temporary table based on which fields are checked in
> >> the form.
>
> > my $dbh = DBI->connect( ... );
> > my $sth = $dbh->prepare( "SHOW COLUMNS FROM $table" ) or die;
> > $sth->execute( ) or die;
> > my @columns;
> > while( my( $column_name ) = $sth->fetchrow_array( )){
> > push @columns, $column_name;
> > }
> > $sth->finish( );
>
> This is complicated and not portable. "perldoc DBI" shows a better method:
>
> my @columns = @{$sth->{NAME}};
>
That only works if you have already done a select. I think the original
poster wanted to get the column names before a query was run so that the user
could choose via the web page which columns to download. The full code for
your portable solution would be:
my $dbh = DBI->connect( ... );
my $sth = $dbh->prepare( "SELECE * FROM $table" ) or die;
$sth->execute( ) or die;
my @columns = @{$sth->{NAME}};
$sth->finish( );
Which could be bad if the table was really big and the alternative database in
question was not smart enough to just start returning data. (And, of course,
"LIMIT" in MySQL is not portable either.) Or the table was locked for some
reason.
No, mine is not portable. (I am thinking of writing an addition to DBI to
abstract some of the non-portable things e.g. getting the column names from a
table, getting the table names, getting the ID of an auto-increment column,
etc..) But it also will not suffer from locking problems. And one could
surround the code with:
if( $dbh->{Driver}{Name} eq 'mysql' ){
...
}elsif( $dbh->{Driver}{Name} eq 'Informix' ){
...
} else {
die "I do not know how to get column names from ",
$dbh->{Driver}{Name};
}
For reference here is how you do it in Infomix and InterBase:
my @list = $dbh->func( $table, '_columns' );
foreach my $column (@list) {
$column->[3] =~ s/\s+//g; #remove trailing space.
push @columns, $column->[3];
}
And PostgreSQL:
my @list = $dbh->func( $table, 'table_attributes' );
foreach my $x (@list) {
foreach my $column (@$x) {
$column->{NAME} =~ s/\s+//g; #remove trailing space.
push @columns, $column->{NAME};
}
}
-
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 do simple stuff
Mike Wexler wrote: > Deryck Henson wrote: > > > > Let me rephrase that checkbox one:: > > > > > TRUE or FALSE > > ALTER TABLE myTable CHANGE COLUMN myColumn TINYINT(1) > > And you use 1 for TRUE and 0 for FALSE. Or ENUM: ALTER TABLE myTable CHANGE COLUM myColumn ENUM( 'T', 'F' ); Of course if you are using this to drive a web page Mike's solution is better. Or you would want to enum( 1, 0 ) or enum( '1', '' ); --Bill - 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 about date conversion ...
Paul DuBois wrote: > At 9:27 AM -0300 9/6/01, Amilton Martins wrote: > >I want to write date type in the format "DD/MM/" to the database? > >Can I do this with some parameter to mysql.ini? > > No. For storage, you must convert it to CCYY-MM-DD format. > For display, you can format it to the style you want using > DATE_FORMAT(d,'%d/%m/%Y'). Besides, dates like DD/MM/ are evil. Remember that Europe uses DD.MM. while we crazy americans use MM/DD/. That can cause lots of confusion esp. if the day of the month is less than 13. And, if your dates are of the form -MM-DD you can use strcmp( ) in C; eq/lt/gt in perl; or in PHP to compare dates. That alone is a big reason to use the -MM-DD form in my book. --Bill - 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 Performance Problem
Henning Schroeder wrote: > idea? And how am I supposed to find the slow queries? Use EXPLAIN SELECT ... http://www.mysql.com/doc/E/X/EXPLAIN.html Also, if you run 'mysqladmin processlist' if you see a 'copying into temp table' that lasts a while (e.g run mysqladmin multiple times and see if it is the same query that is copying) then that is the query that is selecting lots of stuff and hanging the database. You can see if you are really using the indexes you think and if they are being used in the 'right' order. Don't forget to do [my]isamcheck -a table.MYI to get better use of indexes. If you are joining on the userid and since it will be the first key for the user table (primary keys are always first), sort the index based on that: myisamchk -R 1 table.MYI. And don't forget myisamchk -r table.MYI. You have to run that /with/the/path/to/the/table.MYI. http://www.mysql.com/doc/O/p/Optimization.html > I`m not quite sure whether it is really a contention problem. A standard It looks like you are only using ISAM tables which do not support row-level locking. You might want to try InnoDB as the table type as that does support row-level locks. The other thing is that is you have a select that 'copies to temp table' it will lock the other queries until it completes that. > i just tried the above query ten times. result: 7x 0.00sec, 1x0.01, 1x0.02, > 1x0.04. does that spell "slow"? Naw. That should be fine. > Type II: > > update users set lastlogin=999697993, perstopre="f" where uid=40651; > > update users set lastlogin=999698763, votescast="1514", prevvote="-8" where > uid=54307; > > (the usual locked queries) > How long do these take? What does 'mysqladmin processlist' say for these when you execute them (if they take a long time)? --Bill - 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/PHP
Chakravarthy K Sannedhi wrote: > --- Steve Suehring <[EMAIL PROTECTED]> wrote: > > > > hmm. Are you sure that MySQL is running? Conversely, does the file > > /var/lib/mysql/mysql.sock exist? > > > > Steve > > The mysql server is running and the mysql.sock file exists. Also I am > able to connect to the mysql database by any of the following ways. > > mysql -u myname -p mypassword > mysql -u myname -p mypassword -h localhost > > But when I try to connect in the following way, it says 'ERROR 1130: > Host 'the.host.name' is not allowed to connect to this MySQL server > Make sure the user that is running httpd (probably 'nobody') can access /var/lib/mysql/mysql.sock. You can also add an entry to the user or db table for someone to connect via network sockets: INSERT INTO db ( host, db, user, select_priv ) values ( 'the.host.name', 'database', 'myuser', 'y' ); --Bill - 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: table full error
[EMAIL PROTECTED] wrote: > Hello, > > I keep getting this: > > insert into tmp select * from ascend_log_2001_08_25; > ERROR 1114: The table 'tmp' is full > > tmp is a heap table There is a size limit to temp tables. See: http://www.mysql.com/doc/F/u/Full_table.html about increasing the allowed size for temp tables. You can write the temp tables to disk with the SQL 'SET SQL_BIG_TABLES = 1;' http://www.mysql.com/doc/S/E/SET_OPTION.html --Bill - 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: Getting the pass mark
"Adams, Bill TQO" wrote: > SELECT SUM( test=x ) AS total, COUNT(*) AS pass FROM table WHERE text=x AND > value BETWEEN low AND high; > > b. Ugh, brain fart. SELECT COUNT(*) AS total, SUM( value BETWEEN low AND high ) AS pass FROM table WHERE text=x; --Bill - 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: fields query
my $dbh = DBI->connect( ... );
my $sth = $dbh->prepare( "SHOW COLUMNS FROM $table" ) or die;
$sth->execute( ) or die;
my @columns;
while( my( $column_name ) = $sth->fetchrow_array( )){
push @columns, $column_name;
}
$sth->finish( );
b.
"Anthony E." wrote:
> I need to read the fields of a table dynamically using
> DBI.pm
>
> The resulting cgi script should be a form that has a
> checkbox for each field...the user will then create a
> temporary table based on which fields are checked in
> the form.
>
> =
> --
> Anthony Ettinger
> [EMAIL PROTECTED]
> 415-504-8048
> http://chovy.com/resume.doc
>
> __
> Do You Yahoo!?
> Get email alerts & NEW webcam video instant messaging with Yahoo! Messenger
> http://im.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
--
Bill Adams
TriQuint Semiconductor
-
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: Getting the pass mark
SELECT SUM( test=x ) AS total, COUNT(*) AS pass FROM table WHERE text=x AND value BETWEEN low AND high; b. Chris Thorpe wrote: > Is it possible to modify the following query: > > SELECT COUNT(*) AS pass WHERE test=x AND value BETWEEN low AND high; > > to also count the total number of records where 'test=x' > and hence return the percentage pass mark all within one statement, or am I > stuck using 2 statements 1 as above to count the passes and a second : > > SELECT COUNT(*) AS total WHERE test=x; > > to count the total?? > > Chris Thorpe > Consultronics Europe Ltd > > - > 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 -- Bill Adams TriQuint Semiconductor - 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/PHP
Chakravarthy K Sannedhi wrote:
> $db=mysql_connect ("localhost","myname","mypassword");
> mysql_select_db ("newone",$db);
> $result = mysql_query ("select * from namelist");
[snip]
> Warning: MySQL Connection Failed: Can't connect to local MySQL server
> through socket '/var/lib/mysql/mysql.sock' (111) in
> /var/www/html/namelist.php on line 7
You could not connect (in case you did not figure that out) and:
> Warning: Supplied argument is not a valid MySQL-Link resource in
> /var/www/html/namelist.php on line 8
etc. are from the fact you do not have a valid $db.
$db = mysql_connect( ... );
if( $db ){
...
}
First, I would check the user/password with 'mysql', e.g.:
mysql newone myname mypassword
If that works, then it is a mystery to me as to why it is failing but you may
want to connect using network sockets e.g. by specififying the host:
$db = mysql_connect( 'the.host.name', 'myname', 'mypassword );
Also, you may want to check out class.DBI which abstracts the database like
the perl module by the same name: http://evilbill.org/php/DBI.php3
--Bill
-
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: Design question from newbie
Andrew Ward wrote: > As I said, not all organisations were asked the same questions so I can't > just put the data in directly > > The only way I can see of dealing with this is to create tables like > ID,QUESTION,RESPONSE > 1,"YEAR",2001 > 1,"SEX",1 > > This doesn't strike me as very smart. I would greatly value anyone's advice > on dealing with data that is similar in many respects but different in > others. Thank you very much. Actually it is not that bad of an idea esp. since you have different questions. You might make two tables: one to hold common information and the other to hold the questions, e.g: CREATE TABLE header ( question_id integer, year integer, age integer, sex char, UNIQUE( question_id)); CREATE TABLE questions ( question_id integer, question char(32), response_str char(32), response_int integer, KEY( question_id )); The whole response_str/response_int thing is only if you have lots of questions and do not want to calculate stats on cast strings. If your responses are always ints then, obviously, you would not need the _str/_int thing either. With a design like this you can start to get stats in a clean way, e.g.: How may male respondents questioned in and after 2000 liked beer? SELECT COUNT(*) FROM header, questions WHERE header.question_id=questions.question_id AND year>=2000 AND sex='M' AND question="LIKES BEER" AND response_str="Y"; You can count me as one. ;-) --Bill - 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: Complex Query Question
Carl Schrader wrote: > Field Special 1 has a related field with a Start Date and another field > for End date. > Field Special 2 has a related field with a Start Date and another field > for End date. > Field Special 3 has a related field with a Start Date and another field > for End date. > IMNSHO, Any time you have a design like this where you have somedata_1, somedata_2, etc., it much better to change it so that the columns appear in a long table and add an extra column e.g. special_num. In your case: special_num tinyint UNSIGNED, start_date date, end_date date, special char(255), etc.. 1) This makes it easy to add more specials without doing an alter table. 2) You can get the same output as your original design by doing self joins: SELECT T1.special AS special_1, T2.special AS special_2, etc. FROM table T1, table T2 WHERE T1.special_num=1 AND T2.special_num=2 AND etc.. > I will probably need to add up to 3 other fields to keep track of the > last shown date. Add a field to the above table. > one. What complicates this is that there are UP TO to 3 specials per > record. Some of them may not include any specials at all. Some may have > 1 or 2. Special 1 should be shown before Special 2. etc. Only 1 Special [snip] If you have a master table with specials, you can do a left join to get records where there are no specials. You can set up some sort of linkage between the main table an the specials, eg. an auto_increment field in the main table or perhaps a part number, etc.. --Bill Adams - 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 locks up
Jens Hjalmarsson wrote: > Hello, > > I just moved my databases from one machine to another. I recompiled > the whole thing, but I have some problems. MySQL now locks up, and > takes 100% cpu, after an hour or two. I need to shut it down and > restart it, and then it goes another hour again. > The machine is a dual pIII 866 with 2gb ram, dedicated to MySQL. It > serves two frontends running Apache. I am using the my-huge.cnf, with > max_connections set to (currently) 250, but I've tried both more and > less. > I am currently testing it with the binaries provided on MySQL.com, > and so far it hasn't crashed. But, it hasn't gone two hours yet. > > So, why does MySQL lock up on me? Please repost and include the output of: uname -a (hide the host in the post if you want) ldd /path/to/mysqld You did not even include your OS which makes it kinda hard for at least me to help. --Bill - 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 vs Oracle vs Acess
Gene Gurevich wrote: > them to Oracle. The developers are now considering > switching from Access to MySQL and I'm trying to > figure out what are the advantages of that move. I Any DB is better than Access for a sizable table. Once access gets to about 100k records in a table it completely bombs (queries take forever). If you put an Access db on the network, when you query a table in said db, Access copies the table to a local drive the first time. If the table is large (in file size) and your connection is slow, e.g. a T1, then the first time you run a query on a table it can take a lng time. If users do create tables in Access, be sure that they do not give tables/columns stupid names like "Run #", or "A Name With Spaces", etc.. Even MS SQL Server will not handle such atrocities even with the 'Upsize' Add-In (sounds like a value meal) should someone want to move the table to a different database at a later time. As for oracle versus MySQL: I have very little experience with Oracle but the way it handles dates and strings makes my head hurt. --Bill - 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: Access and MySQL
Stephen Johnson wrote: > I have a client that uses an Access dB in house and wants that database to > automatically update a MySQL database that I am creating for their website. > Is this possible and if so where can I find some reading material on how to > accomplish it? > > Thanks for the information > Use MyODBC of course. If they are not updating the MySQL table directly you will need to generate a macro to select into the table. I do not have a specific example but something like. DELETE FROM table; INSERT INTO table (col1, col2, ...) SELECT a_col1, a_col2, ... FROM access_table WHERE... --Bill - 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: order by number
You mean leading zeros? MySQL will translate as much of a string as it can into a
number when it is cast:
mysql> select ('a'+0);
+-+
| ('a'+0) |
+-+
| 0 |
+-+
1 row in set (0.00 sec)
mysql> select ('00010'+0);
+-+
| ('00010'+0) |
+-+
| 10 |
+-+
1 row in set (0.00 sec)
mysql> select ('0010a'+0);
+-+
| ('0010a'+0) |
+-+
| 10 |
+-+
1 row in set (0.00 sec)
mysql>
--bill
"William R. Mussatto" wrote:
> What about a number field w/zero fill?
> On Thu, 30 Aug 2001, Adams, Bill TQO wrote:
>
> > Date: Thu, 30 Aug 2001 08:48:11 -0700
> > From: Adams, Bill TQO <[EMAIL PROTECTED]>
> > To: [EMAIL PROTECTED]
> > Cc: [EMAIL PROTECTED]
> > Subject: Re: order by number
> >
> > If the column is an int then it will order it numerically. Other wise you have to
>cast it. And dont for get the difference between ORDER BY x ASC and ORDER BY x DESC.
> >
> > --Bill
> >
> >
> > DROP TABLE IF EXISTS test;
> > CREATE TABLE test ( i int, c char(20));
> > INSERT INTO test VALUES
> > ( 1, '5' ),
> > ( 2, '4' ),
> > ( 3, '3' ),
> > ( 4, '2' ),
> > ( 5, '1' ),
> > ( 1, '5' );
> >
> > SELECT '-- Natural Order';
> > SELECT * FROM test;
> > SELECT '- ORDER by INT';
> > SELECT * FROM test ORDER BY i;
> > SELECT '- ORDER BY cast char-- ';
> > SELECT *, ( c + 0) AS o_col FROM test ORDER BY o_col;
> >
> >
> > Philip Montgomery wrote:
> >
> > > When doing a select with an order by clause, how do you make mysql list the
>items in correct numberical order. Normally, when I run the command mysql will list
>1000 before 200 because of the initial digit. How do I correct this?
> > >
> > > Thanks,
> > >
> > > Phil
> > >
> > > Get 250 color business cards for FREE!
> > > http://businesscards.lycos.com/vp/fastpath/
> > >
> > > -
> > > 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
> >
> > --
> > Bill Adams
> > TriQuint Semiconductor
> >
> >
> >
> >
> > -
> > 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
> >
>
> Sincerely,
>
> William Mussatto, Senior Systems Engineer
> CyberStrategies, Inc
> ph. 909-920-9154 ext. 27
--
Bill Adams
TriQuint Semiconductor
-
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: COUNTNULL function
Yes. A feature of MySQL is that it returns 1 for true and 0 for false. When you select ... you can put logic tests and sum up the number of true values. In the case of "bool_was_connected=0" will have a value of 1 when it is true and then you sum up the results. You could also do something like: SUM( bool_was_connected IS NULL ) SUM( boul_was_connected IS NULL OR boul_was_connected=0 ) and do on. Just put the condition you want to be true and count in the SUM(). b. [EMAIL PROTECTED] wrote: > Thankyou kindly Bill. Just to verify, SUM adds up values. My data > is only 1 or 0. I used SUM as an example, the below query you recommend > will count all 'rows' which are 0, thus returning a number which > represents how many 0's were found? > > Thankyou for the fast reply, > Calvin > > On Thu, 30 Aug 2001, Adams, Bill TQO wrote: > > > SELECT SUM( bool_was_connected=1 ) AS was_connected, > > SUM( bool_was_connected=0 ) AS was_not_connected, etc... > > > > > > b. > > > > > > [EMAIL PROTECTED] wrote: > > > > > Evening Gurus, > > > > > > I have a small problem that I've beaten to death the past 2 days. I am > > > trying to get the value of a row, and update another table accordingly. > > > The data within the row is either a 1 or 0. My issue is in trying to > > > increment a count for both 1's and 0's. > > > > > > In other words, I want to SUM up all the 1's and update a table with > > > this sum. I also want to sum up all the 0's and add this total count to > > > the table as well. This can be accomplished in a long query, or in > > > through a language..what I am trying to accomplish is to get the total sum > > > of each value without using a WHERE clause. Something akin to: > > > > > > SELECT ... > > > SUM(bool_was_connected), > > > COUNTNULL(bool_was_connected), > > > etc... > > > > > > the SUM will sum up all non-null values. The COUNTNULL would sum up all > > > rows which have a value of 0. > > > > > > Does anyone have a UDF for this function? I currently don't have room on > > > my laptop to download the mysql source code in order to write this up. ( > > > Also have not written a udf for mysql up to this point either) > > > > > -- Bill Adams TriQuint Semiconductor - 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: COUNTNULL function
SELECT SUM( bool_was_connected=1 ) AS was_connected, SUM( bool_was_connected=0 ) AS was_not_connected, etc... b. [EMAIL PROTECTED] wrote: > Evening Gurus, > > I have a small problem that I've beaten to death the past 2 days. I am > trying to get the value of a row, and update another table accordingly. > The data within the row is either a 1 or 0. My issue is in trying to > increment a count for both 1's and 0's. > > In other words, I want to SUM up all the 1's and update a table with > this sum. I also want to sum up all the 0's and add this total count to > the table as well. This can be accomplished in a long query, or in > through a language..what I am trying to accomplish is to get the total sum > of each value without using a WHERE clause. Something akin to: > > SELECT ... > SUM(bool_was_connected), > COUNTNULL(bool_was_connected), > etc... > > the SUM will sum up all non-null values. The COUNTNULL would sum up all > rows which have a value of 0. > > Does anyone have a UDF for this function? I currently don't have room on > my laptop to download the mysql source code in order to write this up. ( > Also have not written a udf for mysql up to this point either) > - 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: MERGE TABLES
Monty helped me with this, and this is my understanding. Regardless of what order you generate the keys, MySQL puts the primary key first, then the UNIQUE keys, then the others. When you add a new UNIQUE key, it puts it after the primary key but before the other existing UNIQUE keys. In your case you have only non-UNIQUE keys in the merge table so you just need to add them in the same order as is in the dataN tables and MySQL will maintain the order: ALTER TABLE all_records DROP INDEX id_kex, DROP INDEX catalog_key; ALTER TABLE all_records ADD INDEX id_key (id_key), ADD INDEX catalog_key (catalog_key); --Bill Johnny Withers wrote: > Well, I don't know if you can specify what order > to put these in, > but mine just happen to not be in the same > order: > > mysql> show index from all_records; > ++-+--- > --+-+---+--- > --+--++ > | Non_unique | Key_name| Seq_in_index | > Column_name | Collation | > Cardinality | Sub_part | Packed | > ++-+- > +-+---+--- > --+--++ > | 1 | catalog_key |1 | > catalog | A | > NULL | NULL | NULL | > | 1 | id_key |1 | > id | A | > NULL | NULL | NULL | > ++ > +--+-+---+--- > --+--++ > 2 rows in set (0.00 sec) > > mysql> show index from data1[,2,3,4,5]; > ++-+--- > --+-+---+--- > --+--++ > | Non_unique | Key_name| Seq_in_index | > Column_name | Collation | > Cardinality | Sub_part | Packed | > ++-+- > +-+---+--- > --+--++ > | 0 | PRIMARY |1 | > id | A | > 93 | NULL | NULL | > | 1 | catalog_key |1 | > catalog | A | > NULL | NULL | NULL | > ++ > +--+-+---+--- > --+--++ > > As you can see, the data1,2,3,4,5 tables that > make up the 'all_records' > table > have a primary key defined on ID. I was unable > to define the ID field > in > my merged table as primary because it has to be > non_unique. This may > have > caused the problem. > > Maybe making the id columns in data1,2,3,4,5 a > normal key instead of > PIMARY will solve the problem. > > (Filter : MySQL,database,SQL,etc) > > - > Johnny Withers > [EMAIL PROTECTED] > p. 601.853.0211 > c. 601.209.4985 > > > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On > Behalf Of Adams, Bill TQO > Sent: Thursday, August 30, 2001 1:11 PM > To: Sergei Golubchik > Cc: Johnny Withers; Mysql-List > Subject: Re: MERGE TABLES > > I have been playing around with merge tables. > You MUST have the columns > and > indexes in the same order. > > Eg. Do > SHOW INDEX FROM real_table; > SHOW INDEX FROM merge_table; > > If the Column_name order is different you will > get either no records or > a > bunch of null records when you select on a > column that is indexed. > > b. -- Bill Adams TriQuint Semiconductor - 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: MERGE TABLES
I have been playing around with merge tables. You MUST have the columns and indexes in the same order. Eg. Do SHOW INDEX FROM real_table; SHOW INDEX FROM merge_table; If the Column_name order is different you will get either no records or a bunch of null records when you select on a column that is indexed. b. Sergei Golubchik wrote: > Hi! > > On Aug 29, Johnny Withers wrote: > > I'm not sure if this is a bug or if this is the way MERGE TABLES works > > in MySQL. > > > > It seems that if I have an INDEX in a field (id for instance), and I try > > to > > SELECT using a WHERE id=NUMBER, it will fail, even though that NUMBER is > > a > > valid id. ID is an int unsigned field with a key on it. However, when I > > drop > > the key, everything works as expected.. > > > > Is this a bug? > > > > (also if I tried to ORDER BY id, while indexed, that didn't work either) > > > > Both look like a bug. > Could you create a repeatable test case ? > > Regards, > Sergei > > -- > MySQL Development Team >__ ___ ___ __ > / |/ /_ __/ __/ __ \/ / Sergei Golubchik <[EMAIL PROTECTED]> > / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ > /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ><___/ > > - > 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 -- Bill Adams TriQuint Semiconductor - 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: order by number
If the column is an int then it will order it numerically. Other wise you have to cast it. And dont for get the difference between ORDER BY x ASC and ORDER BY x DESC. --Bill DROP TABLE IF EXISTS test; CREATE TABLE test ( i int, c char(20)); INSERT INTO test VALUES ( 1, '5' ), ( 2, '4' ), ( 3, '3' ), ( 4, '2' ), ( 5, '1' ), ( 1, '5' ); SELECT '-- Natural Order'; SELECT * FROM test; SELECT '- ORDER by INT'; SELECT * FROM test ORDER BY i; SELECT '- ORDER BY cast char-- '; SELECT *, ( c + 0) AS o_col FROM test ORDER BY o_col; Philip Montgomery wrote: > When doing a select with an order by clause, how do you make mysql list the items in >correct numberical order. Normally, when I run the command mysql will list 1000 >before 200 because of the initial digit. How do I correct this? > > Thanks, > > Phil > > Get 250 color business cards for FREE! > http://businesscards.lycos.com/vp/fastpath/ > > - > 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 -- Bill Adams TriQuint Semiconductor - 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: SQL Problem
SELECT (t1.cnt1 + t2.cnt3 - t3.cnt11) FROM table t1, table t2, table t3 WHERE t1.ColPoint=1 AND t2.ColPoint=5 AND t3.ColPoint=2 AND t1.id=1 AND t2.id=t1.id AND t3.id=t1.id Of course, self joins can be expensive, this grows in complexity as you need to add/subtract more counts ,and this is not general. --Bill Morten Søndergaard wrote: > i have a MySQL DB whit this tabel: > > Id ColPoint CntDateTime Cnt1 Cnt2 > Cntx. Cnt32 > 11 2001-08-01 00:05:0012 14 > > 21 2001-08-01 00:10:0011 12 > > 31 2001-08-01 00:15:00109 > > 41 2001-08-01 00:20:00032 > > 51 2001-08-01 00:25:0064 > > 61 2001-08-01 00:30:0014 22 > > 71 2001-08-01 00:35:0011 17 > > . > . > > x122001-08-01 00:05:00324 > > x222001-08-01 00:10:0031 22 > > x322001-08-01 00:15:0030 19 > > x422001-08-01 00:20:00022 > > x522001-08-01 00:25:0026 14 > > x622001-08-01 00:30:0024 22 > > x72 2001-08-01 00:35:001 17 > > . > . > > Ect. > I am collecting data from 8 collecting points > whit 32 counters each > > I have made a delphi program to show the data in > bars or curves, but the > uses shal > have the opportunite to add and subtrach the > datas > > Exampel for user 1 > ColPoint(Cnt1) +ColPoint5(Cnt3) > -ColPoint2(Cnt11) > > How can i build a sql-string that satisfied this > need > > > > > 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 -- Bill Adams TriQuint Semiconductor - 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
