Re: PHP / Mysql people question
Thought earlier that I could get by on just plain SQL.
Php needed. I'm getting a parse error on the if
line.
This is supposed to be a trigger then before
transaction counts the number of rows , id already
inserted, if exceeded , kick back error message , halt
transaction.
Not sure where I need to go with this statement to
make it work. Ideas , help, suggestions welcome!
//start trigger Trigger1
function KT_TriggerSTARTER_Trigger1($tNG) {
$result = mysql_query(SELECT LurkTitleTim.LurkID
WHERE
LurkID='$new_input');
if ($row=mysql_fetch_row($result) = 5) { error- data
already exists }
else { go ahead and add data with INSERT statement
}
}
if htis is exact code, you're error is at error- data already exists,
which is not a valid statement.
assuming its not exact code, you'll need to send that code to be able to
tell.
HTH
Jeff
Thank you ,
Stuart
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: excel
How do I export from mysql into excel format? Directly - use SELECT INTO OUTFILE and set up as a CSV, and open in excel Indirectly - use a programming language and library to write an Excel binary (php or perl with spreadsheetwrite_excel) HTH Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: excel
Hmm... I use ASP.net, is there any instruction that I can use to use ASP.net to export into excel or is there another way? Well, I know nothing of ASP.net, but you should be able to select from mysql, then instantiate excel via COM to write the binary. Jeff How do I export from mysql into excel format? Directly - use SELECT INTO OUTFILE and set up as a CSV, and open in excel Indirectly - use a programming language and library to write an Excel binary (php or perl with spreadsheetwrite_excel) HTH Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql 4.0 windows XP Downloads
May someone please send me the link to download mysql Binary distribution for the first time on windows XP, I have looked into mysql/downloads and they only have X86. Windows XP, and every other windows platform, runs on X86 architecture. That is the download you want. Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: General questions
I was wondering if anyone could answer a few questions for me. I have to do some research for a school project. 1. What is the user or connection limit for both versions of MySQL (Database Server and MaxDB)? 2. How much memory does MySQL take when started up? 3. Does MySQL take advantage of dual CPU systems? 4. Define referential integrity. I would appreciate it if someone could help. Thanks. With the *possible* exception of 4, all of this is in the manual( http://dev.mysql.com/doc/). If someone gives you these answers, great, but as someone much wiser once said: 'Give a man a fish, he eats for a day, teach a man to fish, he eats for a lifetime' and for 4 i put your qestion in google and got over 70,000 hits inclusive of one, on the first page,entittled Referential Integrity Definition HTH Jeff - SIUE Web Mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: remote connection php code
Hi All
Could someone give me a clue or a snippet of PHP code I can test a romte
connection to my MySQL DB for my website please?
Just take the one you're currently using and replace 'localhost' with
'someotherserver.com'
$cnx = mysql_connect('someotherserver.com','uname','pword');
mysql_select_db('dbname',$cnx);
mysql_query('Select * from table',$cnx);
etc
As long as the GRANT's are set up to accept connections from an exteral
client and Firewall ports are open(if needed), it will not differ much from
local testing.
HTH
Jeff
Thank you
Andrew
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Weeding out duplicates
I am trying to get rid of duplicate user info entries in a database. I am assuming that the email address is unique but other information, like first name, last name, address, etc are not. The email field is not a unique field in the database. I was trying something like the following, but not getting what I wanted. select distinct u1.id,u1.firstname,u1.lastname,u1.email from Users u1, Users u2 where u1.email=u2.email; i *think* something like this should work - select id,firstname,lastname,email from Users group by email having count(*)1; group and having are the keys, if/when that does not work. HTH Jeff How can I go about identifying the duplicate entries of email addresses? Thank you, Jonathan Duncan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DaDaBik
After spending days trying to make a PHP form for editing my tables that actually works, I discovered a wonderful script from www.dadabik.org. I haven't used it online yet but I got it up and running on my computer in about five minutes, and it appears to work flawlessly. There's just one catch. If you look at picture #3 at http://www.geoworld.org/userpass.gif you'll see that DaDaBik apparently makes copies of all your tables, thus presumably doubling the file size. I just wondered if anyone has any comments on this, or if you're familiar with other scripts that are comparable or superior. By the looks of the user interface. It seems that MySQLCC or phpMyAdmin would be comprable/superior options. I use and like both. I've not heard of or used dadabik, but by the screen shots it doesn't seem to be doing anything the other two do not. Since you were leaning the way of PHP, i'd highly reccomend taking at look at phpMyAdmin. http://phpmyadmin.net HTH Jeff __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Client programs only on a Novell server
We're going through an upgrade to Novell 6.5 and would like to use mysql on a single server to store info on who's upgraded the client and who has not. Can the command line client only be installed on the Novell server to connect to a foriegn mysql host? Its easy when its unix-unix, but don't know much about the Novell factor. I'd like to execute this by way of a windows batch file when the user upgrades. The Novell client is installed from a mapped drive on the users machine. I have other options, but if this would work, it would be the simplist to implement. I've searched around a bit but can't quite find what i'm looking for. Thanks Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: many updates really slow
Thanks Jack and Jeremy. But this is part of my application and I need to do this automatically. I don't want to write a server-client application to just to handle file transimission and do pass the update cmd to local server. Any idea? Thanks. If you have ssh installed on both machinesthis could be done with two commandsIf you set up passwdless ssh authentication, this could be automated in a script. even if you don't you'd just have to type your password. scp ./cmd.sql [EMAIL PROTECTED]:/path/to/cmd.sql ssh [EMAIL PROTECTED] '/path/to/mysql -u user -pPassword DBname /path/to/cmd.sql' I use stuff like this all the time and it works wonderfully. HTH...and i didn't miss the point. Jeff William --- Jack Coxen [EMAIL PROTECTED] wrote: I'm guessing here but it sounds like you have the cmd.sql file on your local machine (in Europe). If that's the case, compress the file, ftp it to the US server, uncompress it and do the load locally on the US server. If you're doing the update remotely, you're probably being killed by transmission time. Transferring the file in toto to the US server will be quicker than having the communications lag of doing sending a command from Europe to the US, having the command execute and then receiving the status back in Europe...times 5000 records. Hope this helps, Jack -Original Message- From: William Wang [mailto:[EMAIL PROTECTED] Sent: Thursday, May 06, 2004 1:12 PM To: [EMAIL PROTECTED] Subject: many updates really slow Hi everyone, Please help. I have MySQL server running on host A in US and I am using it on host B in Europe. Every query takes about 0.3 seconds. Now I want to do update db with 5000 updates. So I put all the UPDATES commands in a file cmd.sql and do: mysql -hA cmd.sql and it takes 30 MINUTES which is reasonable: 0.3 * 5000 but unbearable. Is there any better way to do this? Am I doing something stupid? Thanks a lot. __ Do you Yahoo!? Win a $20,000 Career Makeover at Yahoo! HotJobs http://hotjobs.sweepstakes.yahoo.com/careermakeover -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Win a $20,000 Career Makeover at Yahoo! HotJobs http://hotjobs.sweepstakes.yahoo.com/careermakeover -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Product link that goes to hard core porn site
No they don'tyour machine may be infected...I'd run a scan. or download some malware fixers (adaware/spybot) Jeff http://www.mysql.com/portal/software/convertors/index-2.html http://www.mysql.com/portal/software/item-266.html Regards Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Product link that goes to hard core porn site
Yesmy apologies...the OP made it seems like the main page. i didn't go hunting..but i guess i should have gerald_clark [EMAIL PROTECTED]To: [EMAIL PROTECTED] ystems.comcc: [EMAIL PROTECTED] Subject: Re: Product link that goes to hard core porn site 04/02/2004 09:38 AM Yes they do. Click on the Publishers site for the MS Access to Mysql Conversion Utility, and you will be sent to the porno site. [EMAIL PROTECTED] wrote: No they don'tyour machine may be infected...I'd run a scan. or download some malware fixers (adaware/spybot) Jeff http://www.mysql.com/portal/software/convertors/index-2.html http://www.mysql.com/portal/software/item-266.html Regards Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't login mysql with root account!
I have just installed MySQL server v3.23.49-8.5 (downloaded from Debian Packages Site) into my Debian v3.0r2 box! After finished installing, i had followed the instruction here to change root's password but right after this action, i can't login to mysql any more! I had also try to change ownership of /var/lib/mysql and all files and subfolders within this directory to mysql but still login fail! Any suggestion? Thanks! if you can't get in with the root password at all...then you'll need to try this-http://www.mysql.com/doc/en/Resetting_permissions.html What error is showing up? How are you logging in, are you supplying the password now? mysql -u root -pyournewpass (or omit the password and let it prompt you) if you are, make sure it worked correctly by tryng to login without the password mysql -u root hth Jeff __ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL installation in Fedora Core 1
I intend to build up a web server that is based on Fedora Core 1. Installation and configuration of Apache and PHP4 was pretty simple, but there are some problems in configuration of MySQL. I installed the MySQL server RPM included in the distribution. Even in the MySQL pages it is mentioned that running /etc/rc.d/init.d/mysqld start is enough for post-installation process. However, there is no such file. Running mysql leads to an error: i'm not that familar with FC1, but can you just cd into /path/to/mysql and run bin/mysqld_safe --user=mysql did you run scripts/mysql_install_db from /path/to/mysql ? or is that included as part of the rpm install. that may not be needed, i've always installed the binaries on Solaris/RH89 HTH jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sort a Sum
Is there any way to do the following? I get an error whenever I try this (Invalid use of Group function). Select Product, Sum(Qty) From Inventory Group By Product Order By Sum(Qty) DESC; This should work if you assign it an alias...it does for me on 4.018(not exact query) Select Product, Sum(Qty) AS MYSUM From Inventory Group By Product Order By MYSUM DESC; hth jEFF ___ I want the total quantities for all products but I want to see the products with the highest quantities first. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PHP script cannot connect MySQL server
n Tuesday 23 March 2004 02:15, [EMAIL PROTECTED] wrote: tcp0 0 *:3307 *:* LISTEN It seems that the correct socket is used. Here is the output: unix 2 [ ACC ] STREAM LISTENING 3303 private/relay unix 2 [ ACC ] STREAM LISTENING 3307 public/showq Looks like 3307 is indeed open, so you may want to modify your mysql_connect to use localhost:3307 - since that is likely to be the port mysqld is using based upon this output. The default is usually 3306, so if you do not include the :3307 port reference you may very well not be able to connect. Brad Eacker ([EMAIL PROTECTED]) I tried to connect to socket #3307 as follows: $link = mysql_connect(localhost:3307, root, passwd); but without any changes. Hmmm ... I did not expect this to be so hard. -- I've lost track of this thread so sorry if this is no longer valid... Have you read http://www.mysql.com/doc/en/Multiple_servers.html? Its not as simple as just specying a port, i don't think, you'll need to make sure mysql in using a different socket file on start up if you want to connect to an additional/different port. I set two up once..one on 3.x and one on 4.0x, and specifying the socket file/listen port was the magic combo for me.(2 years ago now, so my memory may not be serving me well) Does this already work on 3306 for you? Again, sorry if this is no longer valid or redundant. Jeff ___ Regards Sami -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PHP script cannot connect MySQL server
I finally managed to install MySQL succesfully. I created a simple DB with
two
different tables. I also created a simple PHP script to list all DBs and
tables.
However, it seems that the PHP script cannot access MySQL server, because
the
following error message is displayed:
_
This means that your server is not up, most likely. Can you connect from
the command line? try running
ps -efc | grep mys
do you see an entry for mysqld?
How did you start your server?
HTH
Jeff
Warning: mysql_connect(): Can't connect to local MySQL server through
socket
'/var/lib/mysql/mysql.sock' (2) in /srv/www/htdocs/sqltest.php on line 4
Warning: mysql_list_dbs(): supplied argument is not a valid MySQL-Link
resource in /srv/www/htdocs/sqltest.php on line 5
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result
resource in /srv/www/htdocs/sqltest.php on line 6
Any idea what might be wrong? There is no file 'mysql.sock' in '/var/lib/
mysql'. The PHP script is as follows:
htmlbody
?php
$link_id = mysql_connect(localhost, root);
$result = mysql_list_dbs($link_id);
$num_rows = mysql_num_rows($result);
while ($db_data = mysql_fetch_row($result)) {
echo $db_data[0]. BR;
$result2 = mysql_list_tables($db_data[0]);
$num_rows2 = mysql_num_rows($result2);
while ($table_data = mysql_fetch_row($result2)) {
echo -- . $table_data[0]. BR;
}
echo == $num_rows2 table(s) in . $db_data[0] . BR;
}
?
/body/html
Regards Sami
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: php script for new database and user
Hi,
-Original Message-
From: Elly Wisata [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 18, 2004 1:55 AM
To: [EMAIL PROTECTED]
Cc: 'BGLefty'
Subject: RE: php script for new database and user
I don't think php script can create database. But maybe I am wrong.
If I am, please somebody make it straight.
Create a database should do it through console or some kind like
it.
Well, phpMyAdmin can create databases. And if im not wrong its just a
bundle of php-scripts.
So maybe just install phpMyAdmin and create a DB. Iirc u can see the
commands it does to create.
---
php is only providing an interface to mysql. If wish to script a database
creation you can definitely use PHP. As soon as you authenticate to mysql
via mysql_connect(host,user,pass) you have the rights of that mysqluser,
then you can create all the dbs you want through regular SQL, using PHP's
mysql_query.
mysql_connect('localhost','root','rootpassword');
mysql_query('CREATE DATABASE PHP_TEST');
mysql_select_db('PHP_TEST');
mysql_query('CREATE TABLE STATEMENT')
But if you just want to get some datbases created, then it may just be
easier to use the command line/PHPmyAdmin/MySQLCC/lots of other programs.
HTH
jeff
Bernd
--
[Zufallssig 9]
[NightGhost] on AO forum: Perhaps it doesn't take two to make an
idiotic argument, although you have proven for a fact, that one man
indeed can make two idiotic arguments.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: lost sa password
jose manuel [EMAIL PROTECTED]To: [EMAIL PROTECTED] es cc: Subject: lost sa password 03/18/2004 11:31 AM Hi all: Long time ago I've installed MySQL on my Debian Machine and I'm pretty sure I did'n left the sa pwd blank as recommended. Now, I'm installing other apps that need that sa pwd in order to be installed properly butI can't remember the pwd. is possible to recover the sa password? _ Don't think soBut you can set it back to nothing, take a look at this. http://www.mysql.com/doc/en/Resetting_permissions.html HTH Jeff __ how? - Antivirus ? Filtros antispam ? 6 MB gratis ¿Todavía no tienes un correo inteligente? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PHP and using mysql_last_id()
Hi can anyone tell me how to use php's mysql_last_id() or MySQL's INSERT_LAST_ID - php's function is mysql_insert_id() and not mysql_last_id(), there is no php function called mysql_last_id()...if you saw that in a manual, i'd email the owner of the document. http://php.net/mysql_insert_id hth Jeff _ I have a page registration page, using a username, password and member level text box. I want to send the user (upon successful insert) to a member's detail form where the password table primary key is avaliable it can be kept and entered to subsequent tables as a Foriegn Key. But when I have tried it all I ever get is a value of 1. I have looked at the manuals online and found a couple of books that list these functions, and understand the syntax qnd what it does, but can not find a working example or inducation as to where it goes etc any pointers would be much appreciated stu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie - Please help with createing database
Hey, Thanks for replying. I login with username root and a password I set in the ensim control panel, it works perfectly. I dont really know if I am in the mysql database...but there are a couple of tables or dbs there... What id are you using to create the table? I guess root, coz thats what I login withnot really sure what id is. I do think its a permissions issue...but I dont want to touch any settings till I hear from someone who knows about this as I know squat. - Let us know what type of OS do you have this on. First guesses are persmissionsHave you set the permissions properly on the data directory for mysql? And what user is running mysql currently? When you started it, if *NIX, you should have supplied a user, i.e. bin/mysqld_safe --user=mysql This would mean that the mysql user would have to exist and have all rights to the data directory. hth Jeff - Cheers, -Ryan * * * * * * * * * * * * * * * * I'm not an expert (by any means) but are you in the mysql database when you're trying to create the new db? If so that might be the reason. What id are you using to create the table? Does this ID have the appropriate permissions to create the db? James . Hi, After 4 hours I am finally seeing a bit of results...am a newbie in MySql and have just been able to setup PHPMyAdmin, after loggin in I see the database MySql and 6 tables: columns_priv db func host tables_priv user I then tried to create a database and it gives me this error Error SQL-query : CREATE DATABASE `test1` MySQL said: Can't create database 'test1'. (errno: 13) Back can ANYBODY please tell me what to do? am going totally nuts trying to figure this out. Thanks in advance, -Ryan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Auto Increments and Other Keys
All - Running MySQL 4.0.17 under Red Hat 9, using MyISAM tables. I'm trying to add the auto_increment attribute to column in a table that already has a primary key defined. Here is the table I'm trying to create: CREATE TABLE IF NOT EXISTS dataTypes ( id int unsigned NOT NULL AUTO_INCREMENT KEY, dataType varchar(64) PRIMARY KEY, description tinytext, dbName tinytext, directory tinytext, updated timestamp, UNIQUE (dataType)) Sorry to jump emails, but i missed/deleted the first one, I don't know if i would build the table this way, but this CREATE will work. CREATE TABLE IF NOT EXISTS dataTypes( id int unsigned NOT NULL AUTO_INCREMENT , dataType varchar( 64 ) PRIMARY KEY , description tinytext, dbName tinytext, directory tinytext, updated timestamp, UNIQUE (dataType), KEY ( id ) ) By default the PRIMARY KEY can never be duplicated, so the unique is redundant (i think???). Maybe you really want the combo of the first two as your primary key? hth Jeff This create statement works w/o the id column. However, when I add the id column, I get an error on table creation. Is it complaining because it thinks I'm trying to add a second primary key (since all of the AUTO_INCREMENT attribute ints are unique)? What's the easiest way to do this? Thanks, Mark Already tested with phpMyAdmin ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Remove a RPM Installation
Please how can I remove a RPM installation. I used MySQL-server-4.1.1-1.i386.rpm to install MySQL, but how can I delete it. -- if this was the exact package you installed with try rpm -e MySQL-server-4.1.1-1.i386.rpm you can also query for the installed packages with rpm -q mysql and then if only the one comes up use rpm -e --allmatches mysql use rpm --help for all options. *Its a powerful tool, so be aware of what you're uninstalling. I would query (rpm -q ) before just executing the -e or --erase option a quick search on google of 'uninstall mysql rpm' came up with some great links. http://www.google.com/search?sourceid=navclientie=UTF-8oe=UTF-8q=uninstall+mysql+rpm HTH jeff -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Copy Data to Remote Box
Hello, I have a mysql database in which I run from a machine where I am developing. I would like to take the contents of a specific database (plus all tables) and copy all to another box (not networked). Could someone point me out to the specifics of what I need to do, or where in the docs this is? do you have any internet access from the second box that can connect to the first? if so, you can use mysqldump remotely from the secondary server and pull the data into it. You'll need to specify a host with -h yourhost.yourdomain.com. i.e mysqldump -C -h yourhost.yourdomain -u username -pPassword databasename outfile.sql (will grab all tables in databasename and place the contents in the local folder) if not you can run mysqldump locally and then move it to a box that does have a connection to the second server. checkout mysqldump on mysql.com for lots more options. hth jeff Thanks, Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RegExp Help
you should be able to use STR_REPLACE. update 02093_xdir_links SET title = REPLACE(*,,title); hth jeff Bob Cohen [EMAIL PROTECTED]To: [EMAIL PROTECTED] ve.com cc: Subject: RegExp Help 01/21/2004 10:55 AM Please respond to bcohen Sorry for the newbie question. [Begin]$Groveling_non-programmer_string_of_excuses[End]. I imported a bunch of records into a table. One of the fields came through bracketed in double quotes, e.g., field data. I want remove the double quotes but not the data bracketed within. E.g., field data to field data. Here's my stab at the SQL: UPDATE 02093_xdir_links SET title * WHERE title REGEXP[]*[]; Will this work?. Is there a better way? Did I get it right? Normally I'd just experiment but this is a live database. Thanks in advance. Bob Cohen b.p.e.Creative http://www.bpecreative.com Design and production services for the web Put creative minds to work for you -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RegExp Help
you should be able to use STR_REPLACE. DOH. Sorry, there is NO STR_REPLACE its just REPLACE. jd update 02093_xdir_links SET title = REPLACE(*,,title); hth jeff Bob Cohen [EMAIL PROTECTED]To: [EMAIL PROTECTED] ve.com cc: Subject: RegExp Help 01/21/2004 10:55 AM Please respond to bcohen Sorry for the newbie question. [Begin]$Groveling_non-programmer_string_of_excuses[End]. I imported a bunch of records into a table. One of the fields came through bracketed in double quotes, e.g., field data. I want remove the double quotes but not the data bracketed within. E.g., field data to field data. Here's my stab at the SQL: UPDATE 02093_xdir_links SET title * WHERE title REGEXP[]*[]; Will this work?. Is there a better way? Did I get it right? Normally I'd just experiment but this is a live database. Thanks in advance. Bob Cohen b.p.e.Creative http://www.bpecreative.com Design and production services for the web Put creative minds to work for you -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: RegExp Help
wow, one post, two mistakes. how right you are. sorry. update 02093_xdir_links SET title = REPLACE(\,,title); you may/may not need to escape the . hth Jeff Bob Cohen [EMAIL PROTECTED]To: [EMAIL PROTECTED], [EMAIL PROTECTED] ve.com cc: Subject: RE: RegExp Help 01/21/2004 01:47 PM Please respond to bcohen you should be able to use STR_REPLACE. DOH. Sorry, there is NO STR_REPLACE its just REPLACE. jd update 02093_xdir_links SET title = REPLACE(*,,title); Thank you very much for responding. Sorry to be dense but will this SQL find only those records with data in the TITLE field that are bracketed in double quotes and remove ONLY the quotes? E.g., Change the record from: IdTitle Address City State Zip 1 Joe 1313 Mockingbird Lane Transylvania PA 02098 ^ To: IdTitle Address City State Zip 1 Joe 1313 Mockingbird Lane Transylvania PA 02098 ^^^ To my untrained eye it looks like the REPLACE, as you wrote it above, searches the title field for anything e.g., *. And replaces it with nothing . Thanks. Bob Cohen b.p.e.Creative http://www.bpecreative.com Design and production services for the web Put creative minds to work for you -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Looking for a tool
[EMAIL PROTECTED] .org To: [EMAIL PROTECTED] cc: 01/13/2004 08:15 Subject: Looking for a tool PM Several years ago, I used a cgi based tool that allowed me to upload a csv file to a server and load the data into either a new table or an existing table. Anyone happen to know of one like it now. I can't seem to find it in my archives anymore. I know I can do it with load data infile, but I want the web interface so that I can had the task off to an admin with minimal skills. if you have php installed. phpmyadmin will do that for you and a whole lot more. (phpmyadmin.net) Jeff Thanks in advance -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help with syntax for mysqldump
I am really struggling with a mysqldump. I am trying to create a dump of a complete database called csi_db01 and I am trying to save the dump file to My Documents on the C drive. I am sure I am doing right, but could somebody email me the full syntax to use which comes after the mysql if you could send errors you are getting that would be a help for us One thing, you don't use mysqldump from the mysql prompt, you use it from the msyql directory on your C:/Drive --example C:\mysqlbin/mysqldump -u yourusername -pyoupassword --alldatabases outfile.sql specifying the password in the string is not the best way to go hth Jeff If I can't get the dump file to work, how do I get the database on my local machine up to the remote server. I am using version 4.0.15. Are there any bugs? Thanks Mat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to move data between tables in a database?
Syed Ali [EMAIL PROTECTED]To: [EMAIL PROTECTED] m cc: Subject: How to move data between tables in a database? 01/12/2004 12:54 PM Hello, I would like to move data between tables in mysql within the same database, I am not sure what the best way to go about it is. I do not need to move all the data in the tables, just the output of some select statements. You could use INSERT INTO TABLE2(SELECT * FROM TABLE 1) HTH Jeff From Manual on page http://www.mysql.com/doc/en/INSERT.html INSERT [LOW_PRIORITY | DELAYED] [IGNORE] [INTO] tbl_name [(col_name,...)] SELECT ... I am using Perl DBI and MySql 4.0.15-standard on RedHat 8.0. I guess I can run a select statement and copy all the data into a Perl array, then copy the data into another MySql table and then delete from the original table. But this seems like too much work, perhaps there is an easier way? Thank you... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem creating new user.
Unless i'm misunderstanding(highly possible)...if you are on the command
line, you are going to be seen by mysql as localhost and not allnet.com.
You'll only be seen as another host if you connect remotely. the webserver
running on the localmachine and the mysql client will be seen as localhost.
hth
Jeff
ads mysql
[EMAIL PROTECTED]To: [EMAIL PROTECTED]
om cc:
Subject: Problem creating new user.
12/17/2003 07:45
AM
Hi,
I can create new user with following.
mysqlINSERT INTO user (Host,User,Password)
VALUES('localhost','ads11',PASSWORD('ads11'));
mysqlINSERT INTO db
(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,
Create_priv,Drop_priv) VALUES
('localhost','bankaccount','ads11','Y','Y','Y','Y','Y','Y');
mysql FLUSH PRIVILEGES;
But when I use Host 'www.allnet.com' in place of 'localhost' in both above
mentioned INSERT command It enters values in to table but when I try to
access database by
mysqlmysql -u ads11 -p bankaccount
mysql (Here I enter password 'ads11')
It gives following error :[EMAIL PROTECTED] root]# mysql -u ads11 -p bankaccount
Enter password: (here I enter password 'ads11')
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)
Domain www.allnet.com is registered, having live IP on which web server is
running succefully.
Any clue about problem.
Thanks
-
Do you Yahoo!?
Free Pop-Up Blocker - Get it now
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem creating new user.
If that error is verbatim from the console, then DNS may be working, but it is not who MySQL is acknowledging you as, is there a firewall in between you and the MySQL server? looks like you'd need a grant for 202-63-167-192.exatt.com. and i know its not working, but you really shouldn't post passwords to this list. all posts are archived and that means your password can be read by anyone who google's for help or searches the mysql archive. hth Jeff ads mysql [EMAIL PROTECTED]To: gerald_clark [EMAIL PROTECTED] om cc: [EMAIL PROTECTED] Subject: Re: Problem creating new user. 12/17/2003 10:44 AM Hi, I tried to create user with GRANt as follows : mysql use mysql Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON bankaccount.* TO ads123@'www.networkshastrihall.com' IDENTIFIED BY 'ads123'; Query OK, 0 rows affected (0.00 sec) mysql FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) I think this is not entering into table. [EMAIL PROTECTED] root]# mysql -h www.networkshastrihall.com -u ads123 -p bankaccount Enter password: ERROR 1130: Host '202-63-167-192.exatt.com' is not allowed to connect to this MySQL server. DNS www.networkshastrihall.com is working. Help appreciated. Thanks gerald_clark [EMAIL PROTECTED] wrote: ads mysql wrote: I tried to connect as follows : # [EMAIL PROTECTED] root]# mysql -h www.allnet.com -u ads11 bankaccount ERROR 1130: Host 'xxx-xx-xxx-xxx.yyy.com' is not allowed to connect to this MySQL server # Your DNS is broken. It gives error. I sthere anything giving permission to connect to mysql for particular Host? Use GRANT unless you know what you are doing. Thanks for support. Duncan Hill wrote: On Wednesday 17 December 2003 12:45, ads mysql wrote: It gives following error :[EMAIL PROTECTED] root]# mysql -u ads11 -p bankaccount Enter password: (here I enter password 'ads11') ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) Domain www.allnet.com is registered, having live IP on which web server is Your mysql command line statement provides no host to connect to, so it uses the local socket to communicate. Mysql sees this as 'localhost'. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] - Do you Yahoo!? Free Pop-Up Blocker - Get it now -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] - Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best way to get value of autoincriment after inserting NULL?
since you're using PHP, you can also get this via the php function mysql_insert_id(). directly after your insert, i think another insert would be nearly impossible to get int he middle of these two. $insert = mysql_query(insert stuff into table); $last_id = mysql_insert_id($res_link); (resource link is optional). hth Jeff Aleksandar Bradaric To: Paul Fine [EMAIL PROTECTED] [EMAIL PROTECTED]cc: [EMAIL PROTECTED] net Subject: Re: Best way to get value of autoincriment after inserting NULL? 12/15/2003 01:13 PM Please respond to Aleksandar Bradaric Hi, I imagine there has to be a better way! Yes :) Take a look at the LAST_INSERT_ID() function. Take care, Aleksandar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: new install - command prompt doesn't work
have you changed the directory to the mysql/bin directory? normally when you get this error you're not in the correct directory. if mysql is in C:\mysql you'll need to be in the c:\mysql\bin and then excecute C:\mysql HTH Jeff Betta Jazzy Brown [EMAIL PROTECTED]To: Paul DuBois [EMAIL PROTECTED], Betta Jazzy Brown [EMAIL PROTECTED], ybrown.com [EMAIL PROTECTED] cc: 12/13/2003 09:48 Subject: Re: new install - command prompt doesn't work AM i have done that C:\mysql but it will say 'mysql' is not recognized as an internal or external command, operable program or batch file there must be something else i'm missing... Paul DuBois [EMAIL PROTECTED] wrote: At 18:21 -0800 12/12/03, Betta Jazzy Brown wrote: I have installed MySql on my PC and I was trying to run the program by going to the command prompt typing: C:\net start mysql it says The MySql service has started successfully. the problem is that the command prompt doesn't read mysql but still reads C:\ What is the problem here and how can I correct it... There is no problem. The server has started successfully. But you need to *connect* to the server using a client program. Try running mysql from the C prompt, for example: C:\ mysql i have installed MySql Database Server Standard Clients (4.0) and MySql Control Center and I'm using Win2K OS... -- As well...what specifically can I use MySql for? I have a website that I'm trying to learn a lot of things through to make my resume more beefy and to make myself more marketable...What do you all suggest? Thanks, b-jazzy -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with mysqlimport
is there a higher level directory that does not allow excecution? does it work if you run it as root? can you 'vi /root/mysql/gwarancje.txt'? just some thoughts Jeff Pawe Filutowski [EMAIL PROTECTED]To: [EMAIL PROTECTED] rfam.pl cc: Subject: Problem with mysqlimport 12/09/2003 10:27 AM I tryed to import from text file (columns divided by tabulators) like this: 5724KF2003CSDEUROPAPARKAN2003-12-12MarcinTamka nono . . . On PHPTriad (under Windows 2000) it works perfectly ! I use command: mysqlimport database file.txt But under Linux (RedHat) i have serious problem. System indicates problem: mysqlimport: Error: Can't get stat of '/root/mysql/gwarancje.txt' (Errcode: 13), when using table: gwarancje where gwarancje is the name of table. I tryed to set privileges by chmod 777 command or smilar but it still doesn`t work. Does anybody have any ideas ?? Regards, Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Timestamp
i've used FROM_UNIXTIME with success. you can also supply a format. http://www.mysql.com/doc/en/Date_and_time_functions.html hth jeff Mike Blezien [EMAIL PROTECTED]To: MySQL List [EMAIL PROTECTED] net.net cc: Subject: Timestamp 12/07/2003 12:23 PM Please respond to mickalo Hello, what is the best way to convert a TIMESTAMP value to a value similar to a DATETIME value ?? thx's -- MikemickaloBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Quality Web Hosting http://www.justlightening.net MSN: [EMAIL PROTECTED] =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Form Handling.
email the form contents to yourself or write it to a text file and store that on your server. hth jeff Wesley Philpot [EMAIL PROTECTED]To: [EMAIL PROTECTED] hoo.com cc: Subject: Form Handling. 12/06/2003 09:41 AM I have just made a form using html, I wish to publish it, on to my website, but do I need to have a databse on a server so that my customers can submit their information to me? Is there another way of doing it. I look forward to your reply. Yours Sincerely Wesley - Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql remote access on linux
You should not need to restart , you will need to 'flush privileges'
though. has that been done?
Jeff
John Nichel
[EMAIL PROTECTED]To: Louis van der Merwe [EMAIL
PROTECTED]
om cc: [EMAIL PROTECTED]
Subject: Re: mysql remote access on
linux
12/02/2003 10:14
AM
Louis van der Merwe wrote:
Hi,
Can someone please tell me how to set up a linux mysql server to accept
connections from remote machines.
I have tried creating users for all of the remote host, and creating
users with host '%' and '*', everytime I try to connect using myodbc or
mysqlcc, I get the error host {host name} is not allowed to connect to
this mysql server
Did you restart/reload MySQL after adding the users?
--
By-Tor.com
It's all about the Rush
http://www.by-tor.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Export in XML
first part of your questionphpMyAdmin can export in XMLthe less complex peice, in my mind... www.phpmyadmin.net [snip] Is there a way to export/import MySQL tables in XML format? [/snip] Well Todd, it is a little more complex than this. How much do you know about XML? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What's best
This is exactly what i use as well.both tools have their benefits and limitations Julian Zottl [EMAIL PROTECTED]To: Sale Zenzo [EMAIL PROTECTED], MySQL List [EMAIL PROTECTED] ov cc: Subject: Re: What's best 11/24/2003 02:40 PM That depends :) I use phpMyAdmin and mysqlcc, just depending on what I want to do. j- At 11:36 AM 11/24/2003 -0800, Sale Zenzo wrote: I would like to know what is the best program for administering MySAQL databases: is it PHPMyAmin or something else? __ Do you Yahoo!? Free Pop-Up Blocker - Get it now http://companion.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Julian Zottl Unix Systems Administrator NASA HQ - 202-358-1682 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
datetime vs timestamp for speed
If most of my queries against a DB are goign to be related to when things happened/within a date range... would it be more efficient to use timestamps or datetime fields? I plan to index either column, as it will be in many where clauses. I'm currently still running 3.23.38 with plans to upgrade to 4.0.16, very soon. TIA Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: copy the result content from one db to another
This works just like doing it from table to table, just specify hte db insert into dbname.tablename select * from db1.tablename where id 3; HTH Jeff Dr M Karthikeyan [EMAIL PROTECTED]To: [EMAIL PROTECTED] c.edu cc: Subject: copy the result content from one db to another 11/17/2003 10:51 AM I would like to copy the contents of result set by single command e.g., select * from db1 where id 3; result 1 result 2 now how to write this results into another db without writing to a text file (outtext)... when both source and destination has the same structure thanks m.karthikeyan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: subquery issue
Subquerys don't become available until 4.1 Version 3.X does not support them From Mysql.com For MySQL versions prior to 4.1, most subqueries can be successfully rewritten using joins and and other methods. See section 6.4.2.11 Rewriting Subqueries for Earlier MySQL Versions. Also, if they worked, the second select would return 5 rows of data, and with an IN you would want a comma seperated list. but then again, htis method could work and i just may not be familiar with it. hth Jeff Jeff Sawatzky [EMAIL PROTECTED]To: [EMAIL PROTECTED] om cc: Subject: subquery issue 11/14/2003 10:41 AM I have a table called journal with the following fields ID = auto INT Date = DATE Order = INT Entry = TEXT The table stores journal entries and saves the date that the entry was entered on. The Order just ells me what order the entries were entered if there is more than one entry per date. I want to retrieve all the entries for that last five days with entries. Below is my query, but it doesn't work. SELECT *, DATE_FORMAT(journal.Date, '%W, %M %D, %Y') AS newDate FROM journal WHERE journal.Date IN (SELECT DISTINCT journal.Date FROM journal ORDER BY journal.Date DESC LIMIT 5) ORDER BY journal.Date DESC, journal.Order DESC I get the following error: Query failed : You have an error in your SQL syntax near 'SELECT DISTINCT journal.Date FROM journal ORDER BY journal.Date DESC LIMIT 5) O' at line 1 Does anyone know why this is happening? The version of mySQL I am using is 3.23. Jeff Sawatzky [EMAIL PROTECTED] 416-250-9111 x253 1-800-525-2568 Ontario Service Safety Alliance 4950 Yonge Street Suite 1500 Toronto, Ontario, Canada M2N 6K1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Limitations on data for default...
i believe you'll need to use a timestamp column, which will set to the current date/time at each insert/update. I don't think you can set the date default to a function. You are also using a reserved word Date for your column name without escaping it, i.e. `Date`, also you have two modify's in your SQL...unless thats an email typo. hth Jeff Mike Morton [EMAIL PROTECTED]To: [EMAIL PROTECTED] m cc: Subject: Limitations on data for default... 11/13/2003 10:15 AM I am trying to change a column specification, a date column, so that the default value is the current date. Is this possible? I just get an error on the change query: alter table Hits modify modify Date date NOT NULL default CURDATE(); ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'date NOT NULL default CURDATE()' at line 1 Am I just stupid trying to have a function for a default? And if so, is it possible to get the 'current date' as the default value for a field? TIA! :) -- Cheers Mike Morton * * Tel: 905-465-1263 * Email: [EMAIL PROTECTED] * Indeed, it would not be an exaggeration to describe the history of the computer industry for the past decade as a massive effort to keep up with Apple. - Byte Magazine Given infinite time, 100 monkeys could type out the complete works of Shakespeare. Win 98 source code? Eight monkeys, five minutes. -- NullGrey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Page Numbers
On Wed, Nov 12, 2003 at 08:10:58PM -0600, Mike Blezien wrote: Hello, we've set up many displays using the Previous and Next simple linking set up of search results. But now would like to implement the page number style, IE. Previous [1] [2] [3] Next style format and was hoping someone may have a sample/example queries to accomplish this. Usually working with 10 results per page display. Paul DuBois provides easy-to-understand code for this exact format in _MySQL and Perl for the Web,_ and his _MySQL Cookbook._ also, depending on your language, there are classes out there for exactly that purpose. i know they exist for PERL and PHP... hth Jeff Jesse Sheidlower -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: GUI interface
if you want a windows client, get MySqlCC right off the home page www.mysql.com. I'm using htis in the exact setup you mentioned. a webclient? - http://www.phpmyadmin.net/home_page/ There are morethese are free. hth Jeff Alisa Brouwer [EMAIL PROTECTED]To: [EMAIL PROTECTED] cc: 11/11/2003 09:03 Subject: GUI interface AM Please respond to alisa Hello. I'm looking for a GUI client that will run under Windows 2000 and attach to Solaris 8 server that will allow me to see all the tables and data in my mysql database. Thanks for any help. Thank you, Alisa Brouwer Network Administrator eServ, LLC 8200 North Allen Road Peoria, IL 61615 (309) 691-4957 ext. 1033 cell - (309) 645-5318 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: single quotes in database
You can use addslashes and stripslashes when inserting and selecting respectively. addslashes will turn your name into O\'connel. and stripslashes will bring it back to the displayable format. check out php.net/addslashes php.net/stripslashes hth Jeff Steve Buehler [EMAIL PROTECTED]To: PHP [EMAIL PROTECTED], mysql [EMAIL PROTECTED] cc: 11/07/2003 11:30 Subject: single quotes in database AM I am using PHP/MySQL and am having a problem. We have some names and addresses in the database that have single quotes in them. For instance. There is a town around here called Lee's Summit. Also names like O'connel. When I pull from the database it skips these because of the quotes. I know there is something that I have seen before about this, but can't find it now. Can anybody help me? I really hope this makes since because I am sick today and am having a hard time thinking straight. Is it something that I will have to fix when putting things into the database? I am hoping on being able to fix this when going in and when coming out of the database so that I don't have to go back and redo all the ones that are already in the database. Thanks Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to get two columns from different tables
You're almost there in how you worded your quesiton. You'll need a join. select tab1.test1, tab2.test1 from tab1 inner join tab2 on tab1.id = tab2.id. -- this will get all columns where hte ids match. to get all ids that are 40, add a where clause. select tab1.test1, tab2.test1 from tab1 inner join tab2 on tab1.id = tab2.id where tab1.id = 40. you may also want to look at other types of joins for other results. http://www.mysql.com/doc/en/JOIN.html hth Jeff Pawe Filutowski [EMAIL PROTECTED]To: [EMAIL PROTECTED] rfam.pl cc: Subject: How to get two columns from different tables 11/06/2003 07:32 AM Hello, I`m a newer reader. I have problem with query. There are two tables: tab1: id | test1 0 | string1 1 | string2 2 | string3 and tab2: id | test1 0 | string1 1 | string2 2 | string3 I want to get columns test1 (from tab1) and test2 where id.tab1 = id.tab2 but i don`t know how compare this expresion to constant i.e. id.tab1 = id.tab2 = 40 Haw Can I do this ? Regards -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Last 75 entries from a table
SELECT article_num FROM $table order by article_num DESC LIMIT 75 hth Jeff Mark [EMAIL PROTECTED]To: [EMAIL PROTECTED] st.net cc: Subject: Last 75 entries from a table 11/06/2003 08:39 AM Hello, Using MySQL 3.23.58, what I want is to select the last 75 entries from a table. Like so: SELECT article_num FROM $table LIMIT 75 Except, of course, that this gives me the first 75, whereas I need the last 75. It sounds trivial; and it probably is, but I could not find it. article_num, by the way, is not necessarily sequentially numbered, so doing something from X-75 to X, will not work. Thanks, - Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Creating backups
mysqldump...lots and lots of options http://www.mysql.com/doc/en/mysqldump.html hth Jeff Erich C. Beyrent To: [EMAIL PROTECTED] [EMAIL PROTECTED]cc: yrent.net Subject: Creating backups 10/29/2003 09:03 AM Please respond to Erich C. Beyrent Hi there, Does anyone have some tips or advice for creating backups of all the tables and data contained therein? Do you use custom solutions or perhaps open-source projects? -Erich- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Finding information in the last record
this should work SELECT sku from [table] order by [key] desc limit 1 hth Jeff Hunter, Jess [EMAIL PROTECTED]To: [EMAIL PROTECTED] RC.ORG cc: Subject: Finding information in the last record 10/29/2003 10:50 AM Being real new to MySQL I am having difficulty finding information on how to retrieve data from the last record in a database. I have tried using the SELECT LAST_INSERT_ID() statement but that only works on a per connection basis and doesn't help all the times. Basically I want to goto the end of file and pull the data from a particular field (sku) from the last record/row. If anyone could point me in the right direction I would appreciate it Jess --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.530 / Virus Database: 325 - Release Date: 10/22/03 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Change of root password
to help the mysqld_safe 'finish' you'll need to append an '' to your command, which will have the process start in the background and give you your prompt back. /usr/local/mysql/bin/mysqld_safe --skip-grant-tables now you can hit the enter key, and you'll get your prompt back and the server will still be running. although i'd heed the warnings about runnign your server as root. hth Jeff Parminder Singh Chauhan To: MySql [EMAIL PROTECTED] [EMAIL PROTECTED]cc: t.com Subject: RE: Change of root password 10/29/2003 01:30 PM I can't get through I am doing as follow. Is it right or I am doing something wrong? [EMAIL PROTECTED] parminder]# /usr/local/mysql/share/mysql/mysql.server stop Killing mysqld with pid 31796 Wait for mysqld to exit\c done I can't use mysqld [EMAIL PROTECTED] parminder]# /usr/local/mysql/libexec/mysqld --skip-grant-tables Fatal error: Please read Security section of the manual to find out how to run mysqld as root! 031029 18:10:34 Aborting 031029 18:10:34 /usr/local/mysql/libexec/mysqld: Shutdown Complete But when i use mysqld_safe session never finishes. Any way mysql server is started. I have to close the console and start it again. [EMAIL PROTECTED] parminder]# /usr/local/mysql/bin/mysqld_safe --skip-grant-tables Starting mysqld daemon with databases from /usr/local/mysql/var I can't find what is going wrong On Tue, 2003-10-28 at 18:51, Barry Byrne wrote: -Original Message- From: Parminder Singh Chauhan [mailto:[EMAIL PROTECTED] After long struggle I (A newbee to linux) was able to setup mysql4.0.16 and I don't know what I have done, I set the root password which I don't know. Now I can't access the mysql. Is there anyway of changing root password.? and how? Start mysqld with the --skip-grant-tables option. Change the password and then run 'mysqladmin flush-privileges' to make the change take effect. - Barry -- Parminder Singh Chauhan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unable to Retrieve HTML Form Values(test1.html) from a PHP Page(test1.php)
you need to use $_POST['test1'] http://www.php.net/register_globals php, by default has register_globals=off. for security reasons...so you'll need to use $_POST, $_GET, $_FILES, $_REQUEST etc. hth jeff Prashant A [EMAIL PROTECTED]To: [EMAIL PROTECTED] thnyou.com cc: Subject: Unable to Retrieve HTML Form Values(test1.html) from a PHP Page(test1.php) 10/28/2003 09:23 AM Dear Sir Our Site is running on Linux/Apache/PHP/MySQL Combination. I am unable to retrieve the Values passed from HTML FORM(test1.html) in a PHP Page(test1.php) Is it something to do with the Web Server Settings or any thing else...? The code is as follows; Test1.html html head titleTest Page/title /head body form name=form1 method=Post action=test1.php input type=text name=text1 value= input type=submit value=Ok /form /body /html Test1.php html head titleTest Page/title /head body form name=form1 ?php echo $text1; ? /FORM /body /html After Submitting the form text1.html to text1.php by adding some text in textbox text1, i am unable to print the textbox value text1 in Text1.php page. Can you help me in this regard asap? Thanks Regards, Prashant S Akerkar. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is MySQL Relational? (was: Foreigner keys in MySQL?)
I tend to agree with the past two posts that mysql is in fact relational, and would like to add that if we are going to make blanket statements that something IS or IS NOT that we qualify our responses. personlly i would like to know why Martijn views it as being a non relational db, without argument. If you just say its so, why am is supposed to take that statement over those that provide a full email of text like bluejack or John that provided additional information via a link. Just saying No, to a question like that does not lend to learning only to confusion. sorry about the mini rant jeff bluejack [EMAIL PROTECTED]To: [EMAIL PROTECTED] om cc: Subject: Is MySQL Relational? (was: Foreigner keys in MySQL?) 10/28/2003 11:38 AM 10/28/03 8:11:16 AM, Martijn Tonies [EMAIL PROTECTED] wrote: If you are trying to determine whether MySQL is a relational database, the answer is yes. Ehm... the answer is no. It's getting better, that's for sure. MySQL may or may not conform to some standard or another, and it may or may not perform the tasks that you, personally, want it to, but in fact it *is* a relational database, as opposed to a flat file or an object database. I recommended the questioner evaluate her needs against MySQL's available feature set, because if you are used to Oracle (or some other robust, commercial relational database) you may be surprised at some of the differences or absences in MySQL. But the questioner made it seem as though she was really just trying to get a feel for the basics scope of MySQL, and in that context, yes, MySQL is designed to a implement a relational database model as opposed to some other fundamental type of database. So, what's your laundry list of things MySQL should do? --bluejack -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DB not restoring from dump file
can you send the contents of your dumpfile up to this point. assuming its line 118 of hte dumpfile and 21 of this build table query Jeff Chris W. Parker [EMAIL PROTECTED]To: [EMAIL PROTECTED] .comcc: Subject: DB not restoring from dump file 10/27/2003 01:45 PM Hey everyone, First post to the MySQL list so please be gentle. I recently emptied some tables I shouldn't have in a db of mine and I want to restore the data from a dump file made a few days ago. MySQL version is 3.23. This is the command I used to create the dump: mysqldump --opt -u root --password=password dbname dbname.dump I'm trying to restore that file with: mysql -u root -ppassword dbname dbname.dump I'm getting the following error: ERROR 1064 at line 118: You have an error in your SQL syntax near 'unique (email) ) TYPE=MyISAM' at line 21 Someone suggested to me that it might be because I have a reserved word for a column name but I checked this option out and I do not. Where did I go wrong? Thanks, Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: updating records without changing timestamp fields
use timestamp_col = timestamp_col in your query, to override the NOW() affect. I just passed by this comment this morning http://www.mysql.com/doc/en/DATETIME.html -- user comments at bottom of page hth Jeff Henning Heil [EMAIL PROTECTED]To: MySQL List [EMAIL PROTECTED] com cc: Subject: updating records without changing timestamp fields 10/27/2003 03:21 PM hi there, the subject tells almost everything, I try to update records in a table and everytime I do this, the timestamp field ist set to now(). how can I keep the previously stored value for this field (there are values created before)? thanks for your help, henning -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT DISTINCT question
SELECT DISTINCT(LEFT (product_number,5)) FROM products. http://www.mysql.com/doc/en/String_functions.html hth Jeff Personal [EMAIL PROTECTED]To: [EMAIL PROTECTED] .netcc: Subject: SELECT DISTINCT question 10/13/2003 12:38 PM Hello everybody: I have the following scenario: I have a database of products, each of those products have a product number (like 04543000). Now the first five digits represent the product number, and the 2 following digits represent a differet color for the same product. Now I have to show the thumbnails of this products but only for one color. So my query should look something like this: SELECT DISTINCT(product_number) FROM products. Now this query will return all the different product numbers i.e. but it will also return the different colors as a different product number. Is there anyway to have a query like: SELECT DISTINCT(first_five_product_numbers) FROM products? I can find the solution to it. Maybe the solution will be more experience :) But maybe the solution can be the list. Thanks, Hector -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FW: When inserting data it insert 2 records one correct and one blank!
are you doing this insert in any sort of loop? if you emailed yourself the
final query, or printed it on the screen and then ran it directly into
mysql...do you get the same results?
Jeff
Dan Greene
[EMAIL PROTECTED]To: [EMAIL PROTECTED]
active.com cc:
Subject: FW: When inserting data
it insert 2 records one correct and one blank!
10/09/2003 10:43 AM
sending on to list while I think... ;)
-Original Message-
From: Emilio Ruben Estevez [mailto:[EMAIL PROTECTED]
Sent: Thursday, October 09, 2003 10:34 AM
To: Dan Greene
Subject: RE: When inserting data it insert 2 records one correct and one
blank!
Ok, here it is the full statement:
$sql = INSERT INTO llamadas
(destinatario,fecha,hora,apellido,nombre,telefono,mail,receptor,mensaje)
VALUES
('$destinatario','$fecha','$hora','$apellido','$nombre','$telefono','$mail','$receptor','$mensaje');
$result = mysql_query($sql);
And, yes it happens to all users, and i ve tested pressing only once the
submit button but i allways got 2 records add the first one is a blank one
and the second the correct one.
From: Dan Greene [EMAIL PROTECTED]
To: Emilio Ruben Estevez [EMAIL PROTECTED],
[EMAIL PROTECTED]
Subject: RE: When inserting data it insert 2 records one correct and one
blank!
Date: Thu, 9 Oct 2003 10:09:00 -0400
Emilio,
Can you supply the full insert statement? Also, is this happening for all
users, or one in particular? I ask, as I've seen issues with web app
users
double-clicking the submit button causing similar issues
-Original Message-
From: Emilio Ruben Estevez [mailto:[EMAIL PROTECTED]
Sent: Thursday, October 09, 2003 10:02 AM
To: [EMAIL PROTECTED]
Subject: When inserting data it insert 2 records one correct and one
blank!
Hi, I have a page on a intranet where workers can insert data into a
database, the thing is that when they type the info an press
submit the page
use the INSERT INTO db then when i check the data in the
database i can
see the record that the user entered and a second record that
is all in
blank, exept for the date and time field that the value is
-00-00 and
00:00:00.
What´s wrong? maybe something of the date and time format?? Any Clue.
Thanks.
Emilio.
_
Add MSN 8 Internet Software to your existing Internet access
and enjoy
patented spam protection and more. Sign up now!
http://join.msn.com/?page=dept/byoa
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
_
High-speed Internet access as low as $29.95/month (depending on the local
service providers in your area). Click here. https://broadband.msn.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: General error: Incorrect key file for table: 'RoleM'. Try to repair it
i googled your error and the first link looks like it may help google - General error: Incorrect key file for table www.vbulletin.com/forum/ showthread.php?t=5387goto=nextoldest hth Jeff shahanawaz lakhani To: [EMAIL PROTECTED] [EMAIL PROTECTED]cc: oo.com Subject: General error: Incorrect key file for table: 'RoleM'. Try to repair it 10/03/2003 11:13 AM Hello, I am facing a very strange problem, my application says General error: Incorrect key file for table: 'RoleM'. Try to repair it but when i trying myisamchk then it doesn't give any corruption error. I have tried many things but nothing seems to be of any help, i would really appreciate anhy hint/solution towards this problem. Regards, S L __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Stripping carrige returns out of fields
I have a query that is pulling user comments, supplied by via web internface and creating a text file out of them. In these comments are all sorts of carrige returns. I've tried stripping them out with the following queries, but once imported into excel, the carrige returns are still there. so obviously i'm doing something wrong I tried doing with the ASCII number .(no idea if this is valid) SELECT Business_Unit.Business_Unit, Category.Category_Name, REPLACE(Comment_Original, ASCII(10),ASCII(32)) FROM `Comment` INNER JOIN Cat egory ON Comment.Category_ID = Comment.Category_ID INNER JOIN Survey_Response ON Comment.Survey_Key = Survey_Response.Survey_Key INNER JOIN Business_Unit ON Survey_Response.BUKey = Business_Unit.BUKey WHERE Comment_Original ' ' AND Category.Category_Name IS NOT NULL GROUP BY Business_Unit.BUKey, Category.Category_Name, Comment_Original ORDER BY Business_Unit.BUKey, Category.Category_ID ASC INTO OUTFILE '/export/home/jdyke/comments_NoB.csv' fields terminated by ',' OPTIONALLY ENCLOSED BY '' lines terminated by '\n'; Also with the escaped charatcer. SELECT Business_Unit.Business_Unit, Category.Category_Name, REPLACE(Comment_Original, '\n',' ') FROM `Comment` INNER JOIN Category ON Co mment.Category_ID = Comment.Category_ID INNER JOIN Survey_Response ON Comment.Survey_Key = Survey_Response.Survey_Key INNER JOIN Business_UnitON Survey_Response.BUKey = Business_Unit.BUKey WHERE Comment_Original ' ' AND Category.Category_Name IS NOT NULL GROUP BY Business_Unit.BUKey, Category.Category_Name, Comment_Original ORDER BY Business_Unit.BUKey, Category.Category_ID ASC INTO OUTFILE '/export/home/jdyke/comments_NoB.csv' fields terminated by ',' OPTIONALLY ENCLOSED BY '' lines terminated by '\n'; Is REPLACE the right SQL Function to use? is there a better way to determine this character? Thanks Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
join not using first primay key, per explain
I have four tables that i'm trying to join together most are pretty small(100-200 rows tops) and one, the Response table is 127,000 rows. The query i'm currently executing is SELECT Business_Unit.Business_Unit, Question.Text_Long, AVG(Response) from Question INNER JOIN Response on Question.Question_Key = Response.Question_Key INNER JOIN Survey_Response on Survey_Response.Survey_Key = Response.Survey_Key INNER JOIN Business_Unit on Survey_Response.BUKey = Business_Unit.BUKey WHERE Question.SurveyID = 1 Group by Business_Unit.BUKey I'll actually need to join in two more tables, but to this point MySQL contol center won't execute the query, telling me that it would have to examine to many records. The Query above returns in about 12 seconds adn i'd really like to cut that down, if possible. When looking at EXPLAIN it doesn't seem to be using the first KEY from the Question table...which i'd have to imaging is slowing it down considerably..or is it, there seems to be very little information based on the rest of the data. +++---++-++++ | table | type | possible_keys | key| key_len | ref| rows | Extra | +++---++-++++ | Question| ALL| PRIMARY | [NULL] | [NULL] | [NULL] | 49 | where used; Using temporary| | Response| ref| PRIMARY | PRIMARY| 4 | Question.Question_Key| 1267 || | Survey_Response| eq_ref | PRIMARY | PRIMARY| 4 | Response.Survey_Key| 1 || | Business_Unit| eq_ref | PRIMARY | PRIMARY| 4 | Survey_Response.BUKey| 1 || +++---++-++++ Any Thoughts/Suggestions are apprecitated. Jeff Question -- +-+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+---+ | Question_Number | int(11) | | | 0 | | | Text_Long | varchar(255) | YES | | [NULL] | | | Text_Short | varchar(255) | YES | | [NULL] | | | Category_ID | int(11) | YES | | [NULL] | | | SurveyID| int(11) | | PRI | 0 | | | End_Date| datetime | YES | | [NULL] | | | Question_Key| int(11) | | PRI | 0 | | +-+--+--+-+-+---+ **This has the Primary key at the end of the table...would this matter to MySQL??** The layout of the three tables are as follows Response (127,000) +--++--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--++--+-+-+---+ | Question_Key | int(11)| | PRI | 0 | | | Survey_Key | int(11)| | PRI | 0 | | | Response | tinyint(4) | | MUL | 0 | | +--++--+-+-+---+ Survey_Response +---+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-++ | Survey_Key| int(11) | | PRI | [NULL] | auto_increment | | Sex | varchar(5) | YES | | [NULL] || | Age | varchar(5) | YES | | [NULL] || | Ethnicity | varchar(5) | YES | | [NULL] || | Title | varchar(5) | YES | | [NULL] || | Functional_Area | varchar(5) | | | || | Years_of_Service | varchar(5) | YES | | [NULL] || | Employment_Source | varchar(20) | | | || | BUKey | int(11) | YES | | [NULL] || +---+-+--+-+-++ Business_Unit +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | BUKey | int(11) | | PRI | 0 | | | BU_Number | int(11) | | | 0 | | | Business_Unit | varchar(55) | YES | | [NULL] | | | End_Date | datetime| YES | | [NULL] | | | RegionKey | int(11) | YES | | [NULL] | | | Count | int(6) | | | 0 | | +---+-+--+-+-+---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
join optimization
I have two tables and am running a simple join between them to get questions and their repsective response averages from a survey. The question table has 49 rows and the Response table has 126,732. I'd like to cut down on the time its taking to run this specific query...as i'll be running many like it to generate reports. The query below is the selecting the most data, normally this will be limited to specific groups by joining more tables. I am executing the following query SELECT Question.Text_Long, AVG( Response ) FROM `Response` INNER JOIN Question ON Question.Question_Key = Response.Question_Key WHERE Question.Question_Key LIKE '2003%' GROUP BY Response.Question_Key ORDER BY Question.Question_Key ASC Everything i've done so far leaves this query taking about 7-8 seconds to excecute...and i'm trying to cut that time down. If i leave out the join and just execute --SELECT Question_Key, AVG( Response ) FROM `Response` GROUP BY Question_Key it takes about 3 seconds...is there anything i can do to speed the join up? i've tried using string functions instead of LIKE, but none of them proved to be faster. i've also changed the table that i'm requesing the data from and grouping by(Question and response)...all with mimimal impact. I'm running MySQL. 3.23 Thanks for any help/thoughts you may have. have a good weekend. Jeff the table layout is mysql describe Response; +--++--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--++--+-+-+---+ | Question_Key | int(11)| | PRI | 0 | | | Survey_Key | int(11)| | PRI | 0 | | | Response | tinyint(4) | | MUL | 0 | | +--++--+-+-+---+ 3 rows in set (0.00 sec) mysql describe Question; +-+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+---+ | Question_Number | int(11) | | | 0 | | | Text_Long | varchar(255) | YES | | NULL| | | Text_Short | varchar(255) | YES | | NULL| | | Category_ID | int(11) | YES | | NULL| | | SurveyID| int(11) | YES | | NULL| | | End_Date| datetime | YES | | NULL| | | Question_Key| int(11) | | PRI | 0 | | +-+--+--+-+-+---+ 7 rows in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: join optimization
ahhh yes, thanks for pointing that out. it was not origianlly designed as such and then the codes changed. time is about the same though Thanks gerald_clark [EMAIL PROTECTED]To: [EMAIL PROTECTED] ystems.comcc: [EMAIL PROTECTED] Subject: Re: join optimization 09/19/2003 04:05 PM [EMAIL PROTECTED] wrote: I have two tables and am running a simple join between them to get questions and their repsective response averages from a survey. The question table has 49 rows and the Response table has 126,732. I'd like to cut down on the time its taking to run this specific query...as i'll be running many like it to generate reports. The query below is the selecting the most data, normally this will be limited to specific groups by joining more tables. I am executing the following query SELECT Question.Text_Long, AVG( Response ) FROM `Response` INNER JOIN Question ON Question.Question_Key = Response.Question_Key WHERE Question.Question_Key LIKE '2003%' GROUP BY Response.Question_Key ORDER BY Question.Question_Key ASC You are doing a string compare on an integer field. Why? Everything i've done so far leaves this query taking about 7-8 seconds to excecute...and i'm trying to cut that time down. If i leave out the join and just execute --SELECT Question_Key, AVG( Response ) FROM `Response` GROUP BY Question_Key it takes about 3 seconds...is there anything i can do to speed the join up? i've tried using string functions instead of LIKE, but none of them proved to be faster. i've also changed the table that i'm requesing the data from and grouping by(Question and response)...all with mimimal impact. I'm running MySQL. 3.23 Thanks for any help/thoughts you may have. have a good weekend. Jeff the table layout is mysql describe Response; +--++--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--++--+-+-+---+ | Question_Key | int(11)| | PRI | 0 | | | Survey_Key | int(11)| | PRI | 0 | | | Response | tinyint(4) | | MUL | 0 | | +--++--+-+-+---+ 3 rows in set (0.00 sec) mysql describe Question; +-+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+---+ | Question_Number | int(11) | | | 0 | | | Text_Long | varchar(255) | YES | | NULL| | | Text_Short | varchar(255) | YES | | NULL| | | Category_ID | int(11) | YES | | NULL| | | SurveyID| int(11) | YES | | NULL| | | End_Date| datetime | YES | | NULL| | | Question_Key| int(11) | | PRI | 0 | | +-+--+--+-+-+---+ 7 rows in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
