Re: mysql and cgi
I finally got the following few lines of code to work:
$userdb=$feilds{'user'};
$rows=$dbh-do
(INSERT INTO bandbook (user, ect, ect, ...)
VALUES(?,?,?,?,?,?,?), undef, $userdb,
'ect', 'ect', )
|| die fucked up inserting data: $DBI::errstr;
print $rows row(s) added to bandbook\n;
I must admit that I don't understand the purpose of
the ?'s but after many hours of debugging, I'm not
complaining.
--- Jason Wong [EMAIL PROTECTED] wrote:
On Saturday 03 November 2001 04:26, paco anubis
wrote:
Hello,
I started learning Perl 4 weeks ago and mysql 3
days
ago so I know that I am in over my head, but here
goes
nothing. I am trying to build a Perl CGI
application
(using DBI) to pass form contents from a web page
directly into a MySql database.
I don't know how to pass the Perl variable from
the
form into the database. I tried to assign the
value
using the following code (where $feilds{user} is
the
Perl variable to pass):
$userdb=$dbh-($feilds{user});
$rows=$dbh-do
(INSERT INTO bandbook (user, ect, ect...)
VALUES ($userdb, 'ect', 'ect',
...))
|| die fucked up inserting data: $DBI::errstr;
print $rows row succesfully added to bandbook\n;
As you may have guessed the table, bandbook is
still
empty because the script always dies. Can anyone
point
me in a better direction. Due to my unfamiliarity
with
either language, I am definately not ruling out a
syntax error.
It's good practice to assign your SQL query to a
variable which you can
then print out for debugging purposes. It also
allows you to copy and
paste that query into mysql directly and see what
results it yields.
I think your problem is that you haven't quoted the
VALUES that you're
inserting. Try:
$QRY = INSERT INTO bandbook (user, ect1, ect2...)
VALUES ('$userdb', '$ect1', '$ect2', ...);
print($QRY); ## For debugging
$rows=$dbh-do($QRY) || die Error;
hth
--
Jason Wong
Gremlins Associates
www.gremlins.com.hk
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list
archive)
To request this thread, e-mail
[EMAIL PROTECTED]
To unsubscribe, e-mail
mysql-unsubscribe-##L=##[EMAIL PROTECTED]
Trouble unsubscribing? Try:
http://lists.mysql.com/php/unsubscribe.php
__
Do You Yahoo!?
Find a job, post your resume.
http://careers.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 mysql-unsubscribe-##L=##[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Where REGEX documentation?
Under Description of MySQL regular expression syntax, the manual says: This is a simplistic reference that skips the details. To get more exact information, see Henry Spencer's regex(7) manual page that is included in the source distribution. Since the source is 11 MB and I have no other need for it, I wonder if there's another source for this documentation. http://arglist.com/regex/ / Carsten -- Carsten H. Pedersen keeper and maintainer of the bitbybit.dk MySQL FAQ http://www.bitbybit.dk/mysqlfaq - 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
gui normalisation
Can anyone recommend any gui tools that would help during the normalisation proccess. kind regards Paul Thanks to paul DuBois for his excellent book 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
mysql_install_db error
Description: After doing scripts/mysql_install_db I get errors /usr/bin/mysqdl_safe: line 278: 2759 Illegal instruction $NOHUP_NICENESS $ledir/$MYSQLD $defaults --basedir=$MY_BASEDIR_VERSION --datadir=$DATADIR $USEROPTION --pid-file=$pid_file --skip-locking $err_log 21 011104 16:13:34 mysqld ended How-To-Repeat: step-by-step instalation using INSTALL-BINARY Fix: hostname ? Submitter-Id: submitter ID Originator: Organization: UNIX.LV ISP lo-level TRD support MySQL support: [none | licence | email support | extended email support ] Synopsis: Severity: Priority: medium Category: mysql Class: sw-bug Release: mysql-4.0.0-alpha (Official MySQL RPM) Environment: System: Linux groove 2.4.12-grsec-1.8.4 #14 Wed Oct 24 18:09:44 EEST 2001 i586 unknown Architecture: i586 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-slackware-linux/2.95.3/specs gcc version 2.95.3 20010315 (release) Compilation info: CC='gcc' CFLAGS='-O6 -fno-omit-frame-pointer -mpentium' CXX='gcc' CXXFLAGS='-O6 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mpentium' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Oct 16 03:30 /lib/libc.so.6 - libc-2.2.3.so -rwxr-xr-x1 root root 4783716 May 26 04:03 /lib/libc-2.2.3.so -rw-r--r--1 root root 24721042 May 26 04:00 /usr/lib/libc.a -rw-r--r--1 root root 178 May 26 04:00 /usr/lib/libc.so Configure command: ./configure --disable-shared --with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static --without-berkeley-db --with-innodb --enable-assembler --with-mysqld-user=mysql --with-unix-socket-path=/var/lib/mysql/mysql.sock --prefix=/ --with-extra-charsets=complex --exec-prefix=/usr --libexecdir=/usr/sbin --sysconfdir=/etc --datadir=/usr/share --localstatedir=/var/lib/mysql --infodir=/usr/info --includedir=/usr/include --mandir=/usr/man --with-embedded-server '--with-comment=Official MySQL RPM' - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How to modify safe_mysqld to email when mysql server missing?
NatureHi, How can I modify safe_mysqld to make it email me if the mysql server fails/shuts down for some reason please? regards, Graham Nichols. - 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: performance of joins
well. for now it can only be one contact and one address per customer. the thing is though, that also other people can have addresses and contacts. so for example there will be a user table or something like that. and a user will also have address and contact information. would you still recommend then to make a extra contact and adress table or should the address and contact columns be just added in each of the tables which needs them (like in that case cusstomers and users) im especially interested in what performs better in mysql.. for example is it an performance advantage to have extra tables for addresses and contacts, since not all of the queries will ask for the normal information, the address information and the contact information at the same time? thanks alot in advance! florian Well, you usualy split your data in tables for a reason, because some data in mulitple numbers are associated to one, or multiple data. In your case, if you have one address and contact per customers, then make it one table. If you have an unknown numer of contacts and numbers per customer, then make multiple tables. The joins will be on the primary keys (they should be at least). so it should be fast, even with 2 million records. -- - 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: Where UPDATE documentation?
Uriel Wittenberg writes: Carl, I appreciate your response, but I did check both the table of contents and the index. I did not do a string search on update, if that's what you had in mind. I admit that the search function is as good as one might wish. And I agree that finding things in the manual is not easy. There are some things that are hard to find even if you know that they're there, but I have no idea what to do about it. Multi-table updates and deletes are such fundamental features of SQL that it doesn't occur to new users like me that it could be unsupported. Indeed. It'll be a big day for MySQL when 4.1 is released (and stable). For now the only(?) workaround is to use locks, and that's horribly ugly, inefficient, and much work to do. Maybe we're all foolish and lazy, but maybe also it'd be smarter if the relevant doc section mentioned the lack of support. At least they could point out that the syntax given for a statement or function is exhaustive, and possibly the manual could be better at pointing out in what version different things were added (although this is mainly useful for 3.x versus 4.x, since upgrading to the latest stable version is something you're supposed to do anyway). //C - has 60 mails to read now for some reason. -- Carl Troein - Círdan / Istari-PixelMagic - UIN 16353280 [EMAIL PROTECTED] | http://pixelmagic.dyndns.org/~cirdan/ Amiga user since '89, and damned proud of it too. - 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
strange table corruption in 4.0
Hi, I am trying version 4 with myIsam tables. (I am almost ready to move to some other hopefully safer type ). The problem is I get table corruption. I have a large database(21000 tables, but the whole /var/lib/mysql/mydatabase directory is less than 800MB) database. I know this is strange, but there is a lot of work to be done per table and it is a waste to have to search every time I filled these tables in using some mysqldumps. Then I have a script to process them. It all goes well, until it gets a corrupted table. Corruption is, error 127 myisamchk -r claims to recover only 1/4 of the records. Even so, when I try to look at them ,the table remains corrupted(127) I go to /var/lib/mysql/mydatabase and erase this table (the .frm, .MYI and .MYD files. ) then I recreate the table (justthe definition) from file 1330.sql No complaints. Then I connect to the database and get the COUNT It says 23, which was the count I believe before the corruption EVEN THOUGH 1330.sql has NO data, just the table definitions. Then I try to SELECT a field and it says error 127. Could this be related to my large # of tables? Are there any settings say in my.cnf to change? . myuser@quality5:~/BACKUPN mysql -u myuser -p CDR 1330.sql Enter password: myuser@quality5:~/BACKUPN mysql -u myuser -pxx CDR Welcome to the MySQL monitor. Commands end with ; or \\g. Your MySQL connection id is 479 to server version: 4.0.0-alpha-log Type 'help;' or '\\h' for help. Type '\\c' to clear the buffer. mysql SELECT COUNT(*) FROM TABLE_1330_1; +--+ | COUNT(*) | +--+ | 23 | +--+ 1 row in set (0.01 sec) mysql SELECT file FROM TABLE_1330_1; ERROR 1030: Got error 127 from table handler mysql Here is my my.cnf (system is: 1 PIII x1000MHz 1 37GB SCSI HD 1GB RAM AHA29160N SCSI controller-does this matter? /var/log/messages has nothing relevant # Example mysql config file for very large systems. # # This is for large system with memory of 1G-2G where the system runs mainly # MySQL. # # You can copy this file to # /etc/mf.cnf to set global options, # mysql-data-dir/my.cnf to set server-specific options (in this # installation this directory is /var/lib/mysql) or # ~/.my.cnf to set user-specific options. # # One can in this file use all long options that the program supports. # If you want to know which options a program support, run the program # with --help option. # The following options will be passed to all MySQL clients [client] #password = your_password port= 3306 socket = /var/lib/mysql/mysql.sock # Here follows entries for some specific programs # The MySQL server [mysqld] port= 3306 socket = /var/lib/mysql/mysql.sock skip-locking set-variable= key_buffer=384M set-variable= max_allowed_packet=1M set-variable= table_cache=512 set-variable= sort_buffer=2M set-variable= record_buffer=2M set-variable= thread_cache=8 set-variable= thread_concurrency=2 # Try number of CPU's*2 set-variable= myisam_sort_buffer_size=64M log-bin server-id = 1 # Uncomment the following if you are using BDB tables #set-variable = bdb_cache_size=384M #set-variable = bdb_max_lock=10 # Point the following paths to different dedicated disks #tmpdir = /tmp/ #log-update = /path-to-dedicated-directory/hostname [mysqldump] quick set-variable= max_allowed_packet=256M [mysql] no-auto-rehash #safe-updates # Remove the comment character if you are not familiar with SQL [isamchk] set-variable= key_buffer=256M set-variable= sort_buffer=256M set-variable= read_buffer=2M set-variable= write_buffer=2M [myisamchk] set-variable= key_buffer=256M set-variable= sort_buffer=256M set-variable= read_buffer=2M set-variable= write_buffer=2M [mysqlhotcopy] interactive-timeout Any ideas or any suggestions?? P.S. If I change to InnoDB or something will these error 127 messages disappear? Thanks, S.Alexiou - 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
grant permission
Hi all, Do anyone have this experience before?? I have granted the permission for a machine to access mysql databases several times and then flush privileges. I use perl DBI module to connect to databases through Web. But I found that after a day or even several hours later. I found that the apache log said that the DBI cannot connect to the mysql. It is strange cause I can connect to the mysql from the mysql client of that machine So I set another machine with the same configuration of hardware, system and program setting and I found that this problem didn't occur If anyone know about that. Can you please advise me please. Many thanks. Kaming. - 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
Installation of grant tables failed
Description: I've tried to install MySQL-2.23.37 on LinuxPPC dr4 (not a recent distribution, I know that...), from the source code (mysql-3.23.37.tar.gz). unzip of the file was successfull, configuration and compilation seem to have worked fine, but the installation of the grant tables failed, and returned the following message: [root@localhost mysql-3.23.37]# sh .scripts/mysql_install_db --log Preparing db table Preparing host table Preparing user table Preparing func table Preparing tables_priv table Preparing columns_priv table mysqld got signal 11; The manual section 'Debugging a MySQL server' tells you how to use a stack trace and/or the core file to produce a readable backtrace that may help in finding out why mysqld died. Installation of grant tables failed! Examine the logs in /usr/local/mysql/var for more information. You can also try to start the mysqld daemon with: /usr/local/mysql/libexec/mysqld --skip-grant You can use the command line tool /usr/local/mysql/bin/mysql to connect to the mysql database and look at the grant tables: shell /usr/local/mysql/bin/mysql -u root mysql mysql show tables Try 'mysqld --help' if you have problems with paths.Using --log gives you a log in /usr/local/mysql/var that may be helpful. The latest information about MySQL is available on the web at ... etc... The log file was not much more explicit : /usr/local/mysql/libexec/mysqld, Version: 3.23.37-log, started with: Tcp port: 3306 Unix socket: /tmp/mysql.sock Time Id CommandArgument At least, I would like to know what does 'mysqld got signal 11;' means, or refers to !! Thanks for help How-To-Repeat: sources of MySQL in /root [root@localhost root]# gunzip -c mysql-3.23.37.tar.gz | tar xf - [root@localhost root]# cd mysql-3.23.37 [root@localhost mysql-3.23.37]# /sbin/groupadd mysql [root@localhost mysql-3.23.37]# /sbin/useradd -g mysql mysql [root@localhost mysql-3.23.37]# ./configure --prefix=/usr/local/mysql --with-low-memory [root@localhost mysql-3.23.37]# make ; make install [root@localhost mysql-3.23.37]# sh .scripts/mysql_install_db --log here, I get message saying that mysqld got signal 11, and that installation of grant tables failed Fix: Submitter-Id: KLEGOU stéphane Originator:root Organization:Personnal user MySQL support: none Synopsis: 'mysqld got signal 11;' when I've tried to install the grant tables with the command-line 'sh .scripts/mysql_install_db --log', and installation of grant tables failed. Severity: non-critical Priority: medium Category: mysql Class: support Release: mysql-3.23.37 (Source distribution) Environment: PowerMacintosh 5500/275 MacOS 8.1 System: Linux localhost.localdomain 2.2.1 #101 Fri Feb 5 16:17:12 EST 1999 ppc unknown Architecture: ppc Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /opt/egcs/lib/gcc-lib/powerpc-unknown-linux-gnulibc1/egcs-2.90.25/specs gcc version egcs-2.90.25 980302 (egcs-1.0.2 prerelease) Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS='' LIBC: lrwxrwxrwx 1 root root 12 Nov 1 00:46 /lib/libc.so.6 - libc-1.99.so -rwxr-xr-x 1 root root 909419 Jun 16 1998 /lib/libc-1.99.so -rw-r--r-- 1 root root 1672250 Jun 16 1998 /usr/lib/libc.a -rwxr-xr-x 1 root root 332 Jun 16 1998 /usr/lib/libc.so Configure command: ./configure --prefix=/usr/local/mysql --with-low-memory Perl: This is perl, version 5.004_01 - 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
Setting a DEFAULT value
I'm trying to create at table that inserts a default value into a field if nothing is supplied. If in interpret the documentation correctly this should be accomplished by something like this: CREATE TABLE Location (LocationID VARCHAR(10) NOT NULL PRIMARY KEY, Name VARCHAR(50), Address1 VARCHAR(50), Address2 VARCHAR(50), City VARCHAR(50), State VARCHAR(50), Zip VARCHAR(10), Phone VARCHAR(20), Email VARCHAR(50), URL VARCHAR(75) , Icon INT DEFAULT 1) Where the Icon field will get set to 1 if no value is supplied upon insert. When I insert a row such as: INSERT INTO LocationTEMP VALUES (100,1,2,3,4,5,6,7,8,9,'') zero (0) gets inserted in the Icon field. It appears DEFAULT doesn't work. Thanks, Chris Williams - 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: Setting a DEFAULT value
On Sun, 2001-11-04 at 13:52, Chris Williams wrote: I'm trying to create at table that inserts a default value into a field if nothing is supplied. If in interpret the documentation correctly this should be accomplished by something like this: CREATE TABLE Location (LocationID VARCHAR(10) NOT NULL PRIMARY KEY, Name VARCHAR(50), Address1 VARCHAR(50), Address2 VARCHAR(50), City VARCHAR(50), State VARCHAR(50), Zip VARCHAR(10), Phone VARCHAR(20), Email VARCHAR(50), URL VARCHAR(75) , Icon INT DEFAULT 1) Where the Icon field will get set to 1 if no value is supplied upon insert. When I insert a row such as: INSERT INTO LocationTEMP VALUES (100,1,2,3,4,5,6,7,8,9,'') zero (0) gets inserted in the Icon field. It appears DEFAULT doesn't work. No, you're telling it to insert a value -- namely, '', which becomes a 0. Try NULL instead, or better, try a query that doesn't set that value at all, like: INSERT INTO Location (LocationID, Name, Address1, Address2, City, State, Zip, Phone, Email, URL) VALUES (100, 1, 2, 3, 4, 5, 6, 7, 8, 9) Steve Meyers - 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: Using UNION and UNION ALL on a database running 4.0
Oh! Ok. Thanx! Although I didn't really want to do all of that, I guess it's the only way. Unless someone has something better. - Deryck Henson - http://www.sourcecodecafe.com - Original Message - From: Fournier Jocelyn [Presence-PC] [EMAIL PROTECTED] To: Deryck Henson [EMAIL PROTECTED]; MySQL [EMAIL PROTECTED] Sent: Saturday, November 03, 2001 7:37 PM Subject: Re: Using UNION and UNION ALL on a database running 4.0 Hi, Try : SELECT * FROM tbl1 UNION ALL SELECT * FROM tbl2 UNION SELECT * FROM tbl3 cut UNION SELECT * FROM tbl15 WHERE title LIKE 'something' ORDER BY date_created DESC Regards, Jocelyn Fournier Presence-PC - Original Message - From: Deryck Henson [EMAIL PROTECTED] To: MySQL [EMAIL PROTECTED] Sent: Sunday, November 04, 2001 2:26 AM Subject: Using UNION and UNION ALL on a database running 4.0 Hi all, I have looked throught the manual and found that I could use a UNION syntax to join a bunch of tables into 1 query. How the heck can I do this with 15 tables, order the records by date_created desc and have there be WHERE title LIKE 'something'. I have those other parts (order, etc) just need the union syntax. here is my current non-working query: SELECT * FROM tbl1 UNION ALL WHERE title LIKE 'something' ORDER BY date_created DESC Basicly, I want every single record in the database. Thanx ppl! And by the way, the MySQL Manual is NO HELP AT ALL. too confusing and doesn't even explain it fully. - Deryck Henson - http://www.sourcecodecafe.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 - 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
Refresh table after deleting a row
i have a table with and ID column that auto_increment with that following content ++--+++ | ID | JokeText | JokeDate | AuthorName | ++--+++ | 1 | what's up? | 2001-11-04 | duraid | | 2 | duraid | 2001-11-04 | mama | | 3 | why| 2001-11-04 || ++--+++ problem is say i delete ID=2 row i end up with ++--+++ | ID | JokeText | JokeDate | AuthorName | ++--+++ | 1 | what's up? | 2001-11-04 | duraid | | 3 | why| 2001-11-04 || ++--+++ and when add a new row , ID will be 4. how can i reorganize the table after deleting a row so that the ID wil remain intatct. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
join dilemma
I have a SQL statement that I use to grab movie profiles. When I constructed the input form, I decided I didn't want to use blank entries for tables that didn't have a entry for that particular film e.g. a poster wasn't available for sale. This SQL statement in PHP works fine when EVERY table has an entry for a title: $sql=SELECT allposters.allpostersurl, allposters.imageurl, officialsites.url, profiles.photo, profiles.photoalt, profiles.profile, soundtracks.title AS cdtitle, soundtracks.asin, soundtracks.coverart, titles.titlesid, titles.releaseyear, titles.title FROM allposters, officialsites, profiles, soundtracks, titles WHERE allposters.titlesid=titles.titlesid AND officialsites.titlesid=titles.titlesid AND profiles.titlesid=titles.titlesid AND soundtracks.titlesid=titles.titlesid AND profiles.profilesid='$profileid'; Of course, if one table is missing an entry for that film it turns up empty. I understand that MySQL (our host is using 3.22.32) doesn't support full outer joins which is what I think might be needed in this situation. Are my choices: a) code a separate SQL call for each table b) use a blank entry for the title if no other info exists or is there a third way to make that statement work? For those of you who use PHP, is there any pressing performance reason to use or not to use choice 'a'? Thanks! -- Ian Evans Digital Hit Entertainment http://www.digitalhit.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: join dilemma
I have a SQL statement that I use to grab movie profiles. When I constructed the input form, I decided I didn't want to use blank entries for tables that didn't have a entry for that particular film e.g. a poster wasn't available for sale. This SQL statement in PHP works fine when EVERY table has an entry for a title: $sql=SELECT allposters.allpostersurl, allposters.imageurl, officialsites.url, profiles.photo, profiles.photoalt, profiles.profile, soundtracks.title AS cdtitle, soundtracks.asin, soundtracks.coverart, titles.titlesid, titles.releaseyear, titles.title FROM allposters, officialsites, profiles, soundtracks, titles WHERE allposters.titlesid=titles.titlesid AND officialsites.titlesid=titles.titlesid AND profiles.titlesid=titles.titlesid AND soundtracks.titlesid=titles.titlesid AND profiles.profilesid='$profileid'; Of course, if one table is missing an entry for that film it turns up empty. I understand that MySQL (our host is using 3.22.32) doesn't support full outer joins which is what I think might be needed in this situation. Are my choices: a) code a separate SQL call for each table b) use a blank entry for the title if no other info exists or is there a third way to make that statement work? For those of you who use PHP, is there any pressing performance reason to use or not to use choice 'a'? I think Bob Hall has the answer to your questions. Here's a link to his excellent guide on MySQL SQL: http://users.starpower.net/rjhalljr/MySQL/sql.html / Carsten -- Carsten H. Pedersen keeper and maintainer of the bitbybit.dk MySQL FAQ http://www.bitbybit.dk/mysqlfaq - 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
Delphi MySQL w/InoDB
Is anyone using ZEOS and Delphi with MySQL w/InoDB? I am wondering how the transaction stuff runs or if it even works with this combination. Many Thanks M;) MySQL database sql - 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
Repair with keycache state while ALTERing TABLE
Hi, When doing an Alter Table, what does state Repair with keycache Mean, and should it take 27 hours (so far) on a 26,000,000 record table. And another issue: When searching the archives, I'll get something like 4. Re: Repair with keycache http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:mss:79389[4] ... * http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:mss:79389 As a result. Does the [4] indicate that there should be 4 messages in the thread, 'cos I can only see 2. TIA Quentin Bennett Transport Systems Division Infinity Solutions web http:\\www.infinity.co.nz mailto:[EMAIL PROTECTED] Phone : +64 9 358 9720 Fax : +64 9 309 4142 The information contained in this email is privileged and confidential and intended for the addressee only. If you are not the intended recipient, you are asked to respect that confidentiality and not disclose, copy or make use of its contents. If received in error you are asked to destroy this email and contact the sender immediately. Your assistance is appreciated. - 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: join dilemma
Thanks for your pointer. This SQL statement ended up working: Thanks for your pointer! This ended up working for me: SELECT * FROM profiles LEFT JOIN allposters ON (profiles.titlesid = allposters.titlesid) LEFT JOIN officialsites ON (profiles.titlesid = officialsites.titlesid) LEFT JOIN titles ON (profiles.titlesid = titles.titlesid) LEFT JOIN soundtracks ON (profiles.titlesid = soundtracks.titlesid) WHERE profiles.profilesid='$profileid' -- Ian Evans Digital Hit Entertainment http://www.digitalhit.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Setting a DEFAULT value
I suggest a few things. First, set it to NOT NULL DEFAULT 1, and then set the value explicitly to null, which will cause it to use the default value. The problem with inserting '' is that its casting to an integer and mysql decides that '' should be 0. Or try using : insert into table (column1, column2, etc) value (value1, value2, etc) but don't include icon in your list of columns and that will default it to 1. Chris -Original Message- From: Chris Williams [mailto:[EMAIL PROTECTED]] Sent: Sunday, November 04, 2001 3:52 PM To: [EMAIL PROTECTED] Subject: Setting a DEFAULT value I'm trying to create at table that inserts a default value into a field if nothing is supplied. If in interpret the documentation correctly this should be accomplished by something like this: CREATE TABLE Location (LocationID VARCHAR(10) NOT NULL PRIMARY KEY, Name VARCHAR(50), Address1 VARCHAR(50), Address2 VARCHAR(50), City VARCHAR(50), State VARCHAR(50), Zip VARCHAR(10), Phone VARCHAR(20), Email VARCHAR(50), URL VARCHAR(75) , Icon INT DEFAULT 1) Where the Icon field will get set to 1 if no value is supplied upon insert. When I insert a row such as: INSERT INTO LocationTEMP VALUES (100,1,2,3,4,5,6,7,8,9,'') zero (0) gets inserted in the Icon field. It appears DEFAULT doesn't work. Thanks, Chris Williams - 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
A small patch for mysqlhotcopy
Hi there,
I have been attempting to use mysqlhotcopy to make a backup
copy of all of the databases on a server. It wasn't completely
self-evident from the Mysql manual how best to do this, so I
tried the following:
mysqlhotcopy -u blah -p blah --regexp=.* --flushlog /blah/blah/blah/destdir
This resulted in the following error messages:
Use of uninitialized value in transliteration (tr///) at
/usr/local/mysql/bin/mysqlhotcopy line 230.
Use of uninitialized value in regexp compilation at /usr/local/mysql/bin/mysqlhotcopy
line 231.
Filtering tables with '(?-xism:)'
Use of uninitialized value in transliteration (tr///) at
/usr/local/mysql/bin/mysqlhotcopy line 230.
[similar stuff repeated many times ...]
No tables to hot-copy at /usr/local/mysql/bin/mysqlhotcopy line 322.
I found that the following patch solves the problem:
diff -c mysqlhotcopy.orig mysqlhotcopy
*** mysqlhotcopy.orig Sun Nov 4 18:03:49 2001
--- mysqlhotcopySun Nov 4 17:53:35 2001
***
*** 227,232
--- 227,233
## generate regex for tables/files
my $t_regex = $rdb-{t_regex};## assign temporary regex
+ $t_regex = .* if !defined $rdb-{t_regex}; ## MTC 2001.11.04
my $negated = $t_regex =~ tr/~//d;## remove and count negation operator:
we don't allow ~ in table names
$t_regex = qr/$t_regex/; ## make regex string from user regex
This occurred using MySQL 3.23.37 with Linux 2.4.0. I hope
this is of use to someone. If this is not the preferred method
for making a copy of a database, I would be grateful for
enlightenment.
Thanks.
--
Matthew Clegg | OKbridge -- The #1 Online Bridge Club
[EMAIL PROTECTED] | http://www.okbridge.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 mysql-unsubscribe-##L=##[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL Inquiry
Hello, Can anyone help me? I want to use MySQL locally. My PC has no networkcard and no modem. I want to access my database located in my PC what should I do? I am using Visual Basic and ODBC. What should I set so that I would not need to input the network address and etc. Thanks in Advance!! Myles IncrediMail - Email has finally evolved - Click Here
Re: A small patch for mysqlhotcopy
On Sun, Nov 04, 2001 at 08:07:36PM -0800, Matthew Clegg wrote: Hi there, I have been attempting to use mysqlhotcopy to make a backup copy of all of the databases on a server. It wasn't completely self-evident from the Mysql manual how best to do this, so I tried the following: [snip] I found that the following patch solves the problem: [snip] This occurred using MySQL 3.23.37 with Linux 2.4.0. I hope this is of use to someone. I patched mysqlhotcopy a few months to fix a few bugs, and the one you found looks really familiar. It may be fixed already in the later versions. I don't recall the exact version that contained my fix, though. Can you try a version 3.23.40 and see if the mysqlhotcopy in it works for you? Or mail me privately and I'll send you a recent one. Come to think of it, if you find my name in the script, then it's the patched version. If not, you probably hit the same bug that I patched. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 59 days, processed 1,315,681,864 queries (253/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
XML export
Is there any support for XML export from MySQL databases? - 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
[NEWBIE] How to install MySql++1.7.9?
Hello All. *disclaimer* I'm blond. I'm a linux newbie. I've searched the mailinglist/newsgroup archives. *end disclaimer* I'm trying / wanting to install mysql++ on my MANDRAKE 8.1 LINUX MACHINE. That means i'm running GCC/G++ VERSION 3.0.1 Could someone please be kind to explain IN DUMB SPEAK for a newbie like me, EXACTLY what i should do to install mysql++ ? Should i install it as ROOT? or just the normal account i normally use [user == pure]? I have mysql running already... mysql 1950 0.0 1.4 27064 4576 ?SNov01 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysq... so, could someone please explain what to do exactly please. ie. 1.log in as user 'pure' into shell 2. download file into pure root home directory [from where?] 3. download patch 4. rpm -vhi downloaded file 5. run patch 6. do this 7. do that 8. headbutt monitor 9. goto 6. etc.. etc.. i do appologise if this sounds very lame, but i'm fed up with trying to figure this stuff out by myself and i'm running out of time. Thank you dearly in-advance to any support i receive. Regards - Pure Krome, Melbourne, Austrlia [Note: This message has also been posted to USENET, mailing.database.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
[NEWBIE] How to install MySql++1.7.9?
My deepest appologies folks. I posted this to the wrong mailing list. Sorry for the spam. *bows low* I will now post this to the correct mailing list [[EMAIL PROTECTED]] Terribly sorry. -PK- [Filter bypass keywords: database,sql,query,table ;) ] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL to MS' Access '97
¿ÿ^:ô[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 - 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: Refresh table after deleting a row
At 4:26 PM -0500 11/4/01, duraid wrote: i have a table with and ID column that auto_increment with that following content ++--+++ | ID | JokeText | JokeDate | AuthorName | ++--+++ | 1 | what's up? | 2001-11-04 | duraid | | 2 | duraid | 2001-11-04 | mama | | 3 | why| 2001-11-04 || ++--+++ problem is say i delete ID=2 row i end up with ++--+++ | ID | JokeText | JokeDate | AuthorName | ++--+++ | 1 | what's up? | 2001-11-04 | duraid | | 3 | why| 2001-11-04 || ++--+++ and when add a new row , ID will be 4. how can i reorganize the table after deleting a row so that the ID wil remain intatct. Why bother? MySQL doesn't care if the IDs are sequential. - 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: XML export
At 10:16 PM -0800 11/4/01, Philip Hernandez wrote: Is there any support for XML export from MySQL databases? No, but you can do this relatively easily from within a program, e.g., using the XML::Generator::DBI Perl module. - 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: XML export
On Sun, Nov 04, 2001 at 10:49:55PM -0600, Paul DuBois wrote: At 10:16 PM -0800 11/4/01, Philip Hernandez wrote: Is there any support for XML export from MySQL databases? No, but you can do this relatively easily from within a program, e.g., using the XML::Generator::DBI Perl module. The mysql command-line tool (in recent versions) has a `-X' command-line option which tells it to spit out the results as XML. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 60 days, processed 1,315,887,700 queries (253/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Addendum: DBI Handle Has No Magic
On Sat, Nov 03, 2001 at 07:40:02PM +, Alex Kirk wrote: I forgot to mention one very important thing: this error cropped up after I edited /usr/local/libdata/perl5/site_perl/i386-openbsd/DBI.pm to use DBI version 1.20 instead of 1.15 (apparently, this file was not updated after I uninstalled 1.15 and installed 1.20, because I got errors complaining about the wrong version when I first tried this). Is there any way I can just delete everything associated with DBI and start fresh? I really need to get Perl to interface with MySQL. Fetch the latest DBI from CPAN. Then do: perl Makefile.PL make make test make install UNINST=1 That should wipe out the older stuff along the way. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 60 days, processed 1,315,959,328 queries (253/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problem mysql installation
On Mon, Oct 29, 2001 at 04:06:39PM -0300, Webmaster wrote: Hi all; i have problem with mysql -3.23.42 installation on my freebsd 4.3-RELEASE, when I execute a make i get an error make: no target to make. Thanks and have a great day! I guess you haven't run the configure script. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Spawned process, locked table.
On Sun, Nov 04, 2001 at 09:06:02PM -0400, Corey Kaye wrote:
This is my Perl script. I though that adding $query-finish would
unlock the table but it didn't. It looks like the only way around
this is to connect/disconnect from the DB between each update. All
that connecting/disconnecting i think would be expensive in itself.
THERE MUST BE A BETTER WAY ! Thoughts, comments please!
Hm. The table should only be locked while the update is running (a
very short time). Are you sure that the table is locked while the
script is sleeping?
for ($x=0;$x=$#dbAnswer;$x++) {
# sleep(5);
$sql2 = update answers set answersuper = \\, backuprating = $dbAnswer[$x],
backupna = $dbAnswerNA[$x] where userid = $input{\userid\} and questionid =
$dbQuestionID[$x];
$query2 = $database-prepare($sql2);
$query2-execute;
$query2-finish;
}
Also, if you read up on DBI, you'll see that you can factor the
$query2 = $database-prepare($sql2);
out of the loop and gain a bit of efficiency.
Jeremy
--
Jeremy D. Zawodny, [EMAIL PROTECTED]
Technical Yahoo - Yahoo Finance
Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936
MySQL 3.23.41-max: up 60 days, processed 1,315,963,286 queries (253/sec. avg)
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: performance of joins
On Sun, Nov 04, 2001 at 01:06:27AM +0100, florian wrote: im storing pretty big amounts of customer data in a mysql databases. its about 2 million rows. what im wondering about now is, how does mysql perform joins on such amounts of data? i was planning to split the info in at least 3 tables: a general customer table, a address table, and a contact table. since also other needs than just customers, for storing addresses and contacts. would it perform better though to store all that in one single table or is is it okay to split it up in 3 and join them later then, depening on what information is needed? How many rows do you plan on pulling at once? I have experience with it handling joins against a 150 million row table quite efficiently. And I know others have gone higher than that. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 60 days, processed 1,315,991,848 queries (253/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: scaling/tuning problem
On Fri, Nov 02, 2001 at 03:37:53PM -0800, another oracle dba wrote: Hi. I have a query which completes in about 9 sec when executed alone, but the time grows dramatically when I execute several similar queries at the same time. If I execute 3 queries the time grows to 27-40sec, for 5 queries - toabout 1min, for 20 queries ran simultaniously the time jumps to 2-4 min per query. Ouch. How do I go about increaseing performance. The tables included in the queries have indices on the fields in the where clause, but they are not composite indices. Would it help to combine these multiple indices to one or two composite indices? Can you show us the table structures, query, and the output of EXPLAIN SELECT on the query? Does mysql have something like oracle's explain plan to see how exactly is the query executed? Anything else? EXPLAIN SELECT ... Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 60 days, processed 1,316,000,005 queries (253/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Need help with a query ...
Hi John Kelly, This is not a perfect solution but may be useful to you. But still with two queries! From Mysql prompt issue these two queries. SELECT @maxcat := max(Category) FROM table WHERE category = 'Sports:Football:Players' OR category = 'Sports:Football' OR category = 'Sports'; Select * from table where category = @maxcat; Note that abc is greater than ab This will not be affected by the depth of category. Anvar. At 06:15 PM 02/11/2001 -0800, you wrote: Hi, I have a MySQL table with a column that contains some of a web site directory's category names in the format: Sports:Football:Players I am trying to build a query that that locates all records that match the above category name OR if none exist its parent Sports:Football OR if none exist its parent Sports. The top level category, in this case Sports, will always have at least one matching record. I know I can do this with multiple queries by checking the previous query's result, but I am trying to build a query that does it all in one lookup to avoid lots of lookups in deep categories. Something along the logical lines of ... SELECT * FROM table WHERE category = 'Sports:Football:Players' OR IF NONE category = 'Sports:Football' OR IF NONE category = 'Sports' ... of course the above query does not work but if anyone knows of how to accomplish something similar in one query I would much appreciate it. Thanks! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Design,Performance and Speed of MySQL
On Sat, Nov 03, 2001 at 12:07:03AM +, Manjeet Singh wrote: Hi Group, I am new to mysql, and would like you all to give some time in resolving my trouble. I am stuck up with a problem of designing stores database for a big company.The company is located in different places and wants web-based insertion, retrival and maintaining of data and records in the database. Okay... I am worried of the performance and speed of quering the database and getting displayed on the browser, as they have around 25 million records in the database. 25 million records isn't a lot, given modern hardware. I need to know: 1) What is the maximum storage capacity of MySQL, It's really, really big. I don't remember the exact number anymore, but there has been at least once instance of a database with over 2 billion rows in it. 2) How will be the performance for this type of huge database, where web-based frontend is used and what can be done so has to increase the performance. Make sure you give MySQL sufficient memory. Make sure you data is properly indexed. 3) How to speed-up the querying time, as the queryed results are manipluated through scripts and displayed on web browser. Same as above. 4) How can it be tuned for a better performance. Again, same as above. Have a read thru this: http://www.linux-mag.com/2001-06/mysql_01.html to start with. And watch for part #2 in the December issue. :-) As, i am still in the planning stage with the details of my client as above, i have to decide on with wich database i should proceed. I would also want to know will MySQL is capable of withstanding the load: Yes. 1) As so many users from different locations will be querying and getting results on web. How many users at once? 2) Even they are users who will be adding data to the database. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 60 days, processed 1,316,159,175 queries (253/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Replication Server is missing rows
On Fri, Nov 02, 2001 at 03:07:42PM +0600, Ershad Shafi Chowdhury wrote: Hi! Has anyone experienced a replication server to miss updates? Our master is running Mysql 3.23.33 on RH 6.1 Kernel 2.2.12-20 and the slave is running 3.23.24 on Solaris 2.5.1 Normally Inserts and updates occur on the master and SELECT queries occur on the slave. Recently the mirroring stopped (HDD became full), so as always, I shutdown the slave, ran myisamchk on the slave's DB, then restarted the slave. Replication resumed as normal, but later when I checked, I found several inserts onthe master missing from the slave. When the disk filled up, you may have lost data. Have you tried re-syncing the master and slave? After doing that, you shouldn't see any missed rows on the slave. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 60 days, processed 1,316,178,505 queries (253/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Windows MySQL client
Hello I am trying to get a mysql client working in windows, and have a few questions I would like to ask. My environment is this: I am using the Borland command-line tools, version 5.5, and am currently running Windows 98, but would obviously like the client to work on all(!!) flavours of Windows. As far as I understand, I need the mysql client library to link into my executable, and I need a set of header files so that the object files can be compiled. So far I have downloaded the 3.32.36 Borland mysqlclient.lib. Unfortunately, this did not include any of the header files. Do you know where I can find these? To replace the header files, I used the one that come in the 3.23.39 windows zip (!!) and everything seemed to compile fine. When it came to the link stage, however, there were a couple of unresolved references, that all seemed to be concerned with threads. Is there another library that I need to link in? So I guess I have two questions: * Where can I find the 3.23.36 header files, or alternatively (and preferably) is there a 3.23.39 Borland client library available? * How do I deal with the unresolved references to threading functions? Thank you in advance for any answers to these questions. Matthew Fincham - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
how to mysql database cluster! help!
Hi friends, I am a system administrator. I am from China:) We want to build a big system with Mysql for authortication,But we don't know how to establish cluster for Mysql, Could you help me please and tell me what we must to do! Waiting for your answer and thanks very much! WeiMing.Li - 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
