Re: test

[Adams, Bill TQO]

[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: Query to CSV

[Adams, Bill TQO]

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: Creating indexes on large tables

[Adams, Bill TQO]

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: Creating indexes on large tables

[Adams, Bill TQO]

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

[Adams, Bill TQO]

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: repost: Too many open files

[Adams, Bill TQO]

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

[Adams, Bill TQO]

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: Table 'mysql.host' doesn't exist when try to start mysqld

[Adams, Bill TQO]

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: Query question!

[Adams, Bill TQO]

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: How many OR, AND in a query?

[Adams, Bill TQO]

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: DBI.pm

[Adams, Bill TQO]

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: DBI on FreeBSD MySQL

[Adams, Bill TQO]

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: [Q]How to delete recursively

[Adams, Bill TQO]

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: can any one help please?

[Adams, Bill TQO]

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: Query to return products given matches on attributes

[Adams, Bill TQO]

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  SS  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: Bad table and mysql 3.23.42

[Adams, Bill TQO]

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 somefile.sql

Of course make sure that the somefile.sql is not at your OS file-size limit.

--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: MyODBC question

[Adams, Bill TQO]

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: Portable MySQL?

[Adams, Bill TQO]

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: DBI-1.18.tar.gz Can't gzip -cd

[Adams, Bill TQO]

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: Password not working.

[Adams, Bill TQO]

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: creating tables using a number

[Adams, Bill TQO]

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: Comparing Dates

[Adams, Bill TQO]

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_dateDATE_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: Weird problem

[Adams, Bill TQO]

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: Corrupted tables -- for 'no apparent' reason

[Adams, Bill TQO]

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: Optimization question and possible bug

[Adams, Bill TQO]

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: Let's approach stored procedures

[Adams, Bill TQO]

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/0817244mode=threadthreshold=

 At a first glance, I think Oracle PL/SQL is the best (afaik)
 programming language for sp, but, in the case we implement

sarcasm
You should implement it in Python so the Perl vs. Python flame wars can rage
from within MySQL too.
/sarcasm

--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

[Adams, Bill TQO]

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: Limit query to a value

[Adams, Bill TQO]

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
Tot0

--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

[Adams, Bill TQO]

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.

flame
If you use MS SQL Server + ASP all of your profits will be eaten by licensing
fees.
/flame

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: Help about date conversion ...

[Adams, Bill TQO]

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: How to do simple stuff

[Adams, Bill TQO]

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: fields query

[Adams, Bill TQO]

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: MySQL Performance Problem

[Adams, Bill TQO]

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: Auto Importing

[Adams, Bill TQO]

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

[Adams, Bill TQO]

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 timestamp999783881;

--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 error

[Adams, Bill TQO]

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: Auto Importing

[Adams, Bill TQO]

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: Recursive same-table lookup, operator precedence (?)

[Adams, Bill TQO]

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: MySQL/PHP

[Adams, Bill TQO]

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 );

shameless_plug
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
/shameless_plug

--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]

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: fields query

[Adams, Bill TQO]

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

[Adams, Bill TQO]

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: table full error

[Adams, Bill TQO]

[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: MySQL/PHP

[Adams, Bill TQO]

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: Complex Query Question

[Adams, Bill TQO]

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: Design question from newbie

[Adams, Bill TQO]

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: Access and MySQL

[Adams, Bill TQO]

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: MySQL locks up

[Adams, Bill TQO]

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: order by number

[Adams, Bill TQO]

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: MERGE TABLES

[Adams, Bill TQO]

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: MERGE TABLES

[Adams, Bill TQO]

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: COUNTNULL function

[Adams, Bill TQO]

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: COUNTNULL function

[Adams, Bill TQO]

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: order by number

[Adams, Bill TQO]

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: SQL Problem

[Adams, Bill TQO]

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