RE: Count syntax
Thanks - it works, but what does the 1 and 0 do in this - SUM(IF(supportertype = 'L', 1, 0)) -Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: September 28, 2007 1:00 PM To: Beauford Cc: mysql@lists.mysql.com Subject: Re: Count syntax Beauford wrote: Hi, I have the following line of code and I keep getting wrong results from it. Can someone let me know what I'm doing wrong here. I just can't quite figure out the syntax that I need. select count(*) as numrows, count(supportertype) as leadcar from registrar where supportertype = 'L'; What I want to do is count the total number of records and then the total number of records that have L as the supportertype and then display them. So I should have something like There are 100 total records and 22 with Supporter Type L. Try this: SELECT COUNT(*), SUM(IF(supportertype = 'L', 1, 0)) FROM registrar; Baron -- 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]
Count syntax
Hi, I have the following line of code and I keep getting wrong results from it. Can someone let me know what I'm doing wrong here. I just can't quite figure out the syntax that I need. select count(*) as numrows, count(supportertype) as leadcar from registrar where supportertype = 'L'; What I want to do is count the total number of records and then the total number of records that have L as the supportertype and then display them. So I should have something like There are 100 total records and 22 with Supporter Type L. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Count syntax
Thanks to all. -Original Message- From: Michael Dykman [mailto:[EMAIL PROTECTED] Sent: September 28, 2007 1:36 PM To: Beauford Cc: mysql@lists.mysql.com Subject: Re: Count syntax 1 means that 1 will be added to the sum if the condition tests, otherwise 0 will be added to the sum. the condition in this case is (supportertype = 'L') and will be applied to every row. On 9/28/07, Beauford [EMAIL PROTECTED] wrote: Thanks - it works, but what does the 1 and 0 do in this - SUM(IF(supportertype = 'L', 1, 0)) -Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: September 28, 2007 1:00 PM To: Beauford Cc: mysql@lists.mysql.com Subject: Re: Count syntax Beauford wrote: Hi, I have the following line of code and I keep getting wrong results from it. Can someone let me know what I'm doing wrong here. I just can't quite figure out the syntax that I need. select count(*) as numrows, count(supportertype) as leadcar from registrar where supportertype = 'L'; What I want to do is count the total number of records and then the total number of records that have L as the supportertype and then display them. So I should have something like There are 100 total records and 22 with Supporter Type L. Try this: SELECT COUNT(*), SUM(IF(supportertype = 'L', 1, 0)) FROM registrar; Baron -- 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] -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- 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]
Scheduled events
Hi, I have been trying for days to find a simple command in MySQL where I can automatically delete records based on some criteria after a certain timeframe. I found this in the MySQL manual, but I guess it only works with version 5.1+. Unfortunately the version I need this to work on is 4.1, and can't be upgraded. CREATE EVENT e_hourly ON SCHEDULE EVERY 1 HOUR COMMENT 'Clears out sessions table each hour.' DO DELETE FROM something; Is there something similar in version 4.1 that will do the same thing. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Scheduled events
I have been trying for days to find a simple command in MySQL where I can automatically delete records based on some criteria after a certain timeframe. I found this in the MySQL manual, but I guess it only works with version 5.1+. Unfortunately the version I need this to work on is 4.1, and can't be upgraded. CREATE EVENT e_hourly ON SCHEDULE EVERY 1 HOUR COMMENT 'Clears out sessions table each hour.' DO DELETE FROM something; Is there something similar in version 4.1 that will do the same thing. No. But there are cron jobs :-) And if you're deleting many rows and you don't want to interfere with other running queries (sounds like this is an OLTP system), try MySQL Archiver with the --purge argument (http://mysqltoolkit.sourceforge.net/). It's much more efficient at large data volumes than a single DELETE statement. Baron Really. I thought it would have some kind of scripting capability. I did check out the link, but really don't need anything that extensive. Is there a way to run the following command via cron. delete from contacts where TO_DAYS(CURDATE()) - TO_DAYS(today) = 30 and status != Y; Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Windows - logging into MySQL
This is why lists like this get a bad name. First off, these instructions are for 4.1. Secondly. What makes you think I haven't read the instructions or searched extensively on Google etc. Either give a proper answer or just shut your mouth. Never mind anyone responding, I'll find my own damn answers. Thanks for the help. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: November 30, 2005 12:16 AM To: Beauford Cc: mysql@lists.mysql.com Subject: Re: Windows - logging into MySQL Beauford [EMAIL PROTECTED] wrote on 11/29/2005 11:31:24 PM: I just installed MySQL 3.23 (the only one I could get to work) and I find that I can log into the server just by typing mysql at the DOS prompt. Ho do make it so you have to use a user name and password to get in. Thanks Running on Win2k Advance Server. You could try following the installation instructions in the manual http://dev.mysql.com/doc/refman/4.1/en/windows-installation.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Windows - logging into MySQL
I have already changed the PW for root and I can log in no problem with mysql -p root, but I can also get in with just mysql. Someone mentioned about anonymous access, I'll have to check on that. 4.0 and above give me an error that permission is denied for [EMAIL PROTECTED] I am not using an external database so not sure why this is. Thanks to all that posted legitimate answers. SGreen can biteme however. -Original Message- From: Cal Evans [mailto:[EMAIL PROTECTED] Sent: November 30, 2005 12:08 AM To: Beauford Cc: mysql@lists.mysql.com Subject: Re: Windows - logging into MySQL Hi, If you've just installed it then the root user has no password. Change the root user to have a password then you will have to specify the -p parameter on the command line before you can log in. (Or you can put it in your local my.cnf file) =C= www.calevans.com Beauford wrote: I just installed MySQL 3.23 (the only one I could get to work) and I find that I can log into the server just by typing mysql at the DOS prompt. Ho do make it so you have to use a user name and password to get in. Thanks Running on Win2k Advance Server. -- 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: Windows - logging into MySQL
There are always idots like Shawn Green that give BS answers just for the sake of responding, and for me it just reflects badly on the group as a whole. There are many others that help, but when I open my email and this is the first response I see, it just gets under my skin. FYI. I have posted some information in another response as to the problems with later versions etc. -Original Message- From: Logan, David (SST - Adelaide) [mailto:[EMAIL PROTECTED] Sent: November 30, 2005 5:57 PM To: Beauford; [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: RE: Windows - logging into MySQL Sorry Beauford, The url that Shawn gave you is valid for the version you quoted. There hasn't been much change and they work just as well. This is also the earliest documentation that now exists on the website. What is the bad name this list has gained? I've found it helpful on numerous occasions. I think a reconsideration of your opinions would result in a change of heart. The documentation states the defaults that the mysql client program would accept, it also details how to set users and passwords exactly as you requested. These all work on 3.23.58 (I have this version installed at a customer site as we write). Perhaps if you detail the problems you had installing the later versions, somebody may be able to help. Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: Beauford [mailto:[EMAIL PROTECTED] Sent: Thursday, 1 December 2005 9:21 AM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: RE: Windows - logging into MySQL This is why lists like this get a bad name. First off, these instructions are for 4.1. Secondly. What makes you think I haven't read the instructions or searched extensively on Google etc. Either give a proper answer or just shut your mouth. Never mind anyone responding, I'll find my own damn answers. Thanks for the help. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: November 30, 2005 12:16 AM To: Beauford Cc: mysql@lists.mysql.com Subject: Re: Windows - logging into MySQL Beauford [EMAIL PROTECTED] wrote on 11/29/2005 11:31:24 PM: I just installed MySQL 3.23 (the only one I could get to work) and I find that I can log into the server just by typing mysql at the DOS prompt. Ho do make it so you have to use a user name and password to get in. Thanks Running on Win2k Advance Server. You could try following the installation instructions in the manual http://dev.mysql.com/doc/refman/4.1/en/windows-installation.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- 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: Windows - logging into MySQL
Sorry, but I don't take kindly to idiots. I don't care if he's gods gift to MySQL. If there isn't enough info for him to give an informed answer then he should either ask for more or shut up. I'm just simply trying to resolve a problem, and flip answers aren't needed or appropriate. I don't send emails to the list at the first sign of problem, this is probably the first in over a year and a half. When I do however, it is because the manual or other attempts have not provided me the answers I need. In any event I will move on and find my answers elsewhere. -Original Message- From: J.R. Bullington [mailto:[EMAIL PROTECTED] Sent: November 30, 2005 6:37 PM To: mysql@lists.mysql.com Subject: RE: Windows - logging into MySQL You need to be careful what you write. I have found that Shawn is one of the best guys on this list. His answers are 99% right on target (1% because not enough information was given) and can help on every version of db out there. Don't start a flame war because your feelings were hurt. Grow up. J.R. -Original Message- From: Beauford [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 30, 2005 6:05 PM To: 'Logan, David (SST - Adelaide)' Cc: mysql@lists.mysql.com Subject: RE: Windows - logging into MySQL There are always idots like Shawn Green that give BS answers just for the sake of responding, and for me it just reflects badly on the group as a whole. There are many others that help, but when I open my email and this is the first response I see, it just gets under my skin. FYI. I have posted some information in another response as to the problems with later versions etc. -Original Message- From: Logan, David (SST - Adelaide) [mailto:[EMAIL PROTECTED] Sent: November 30, 2005 5:57 PM To: Beauford; [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: RE: Windows - logging into MySQL Sorry Beauford, The url that Shawn gave you is valid for the version you quoted. There hasn't been much change and they work just as well. This is also the earliest documentation that now exists on the website. What is the bad name this list has gained? I've found it helpful on numerous occasions. I think a reconsideration of your opinions would result in a change of heart. The documentation states the defaults that the mysql client program would accept, it also details how to set users and passwords exactly as you requested. These all work on 3.23.58 (I have this version installed at a customer site as we write). Perhaps if you detail the problems you had installing the later versions, somebody may be able to help. Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: Beauford [mailto:[EMAIL PROTECTED] Sent: Thursday, 1 December 2005 9:21 AM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: RE: Windows - logging into MySQL This is why lists like this get a bad name. First off, these instructions are for 4.1. Secondly. What makes you think I haven't read the instructions or searched extensively on Google etc. Either give a proper answer or just shut your mouth. Never mind anyone responding, I'll find my own damn answers. Thanks for the help. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: November 30, 2005 12:16 AM To: Beauford Cc: mysql@lists.mysql.com Subject: Re: Windows - logging into MySQL Beauford [EMAIL PROTECTED] wrote on 11/29/2005 11:31:24 PM: I just installed MySQL 3.23 (the only one I could get to work) and I find that I can log into the server just by typing mysql at the DOS prompt. Ho do make it so you have to use a user name and password to get in. Thanks Running on Win2k Advance Server. You could try following the installation instructions in the manual http://dev.mysql.com/doc/refman/4.1/en/windows-installation.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Windows - logging into MySQL
I just installed MySQL 3.23 (the only one I could get to work) and I find that I can log into the server just by typing mysql at the DOS prompt. Ho do make it so you have to use a user name and password to get in. Thanks Running on Win2k Advance Server. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem with tables
Duh.That's what I get for doing this stuff late at night. Funning how some things just don't click in until someone kicks you in the butt Thanks -Original Message- From: Aman Raheja [mailto:[EMAIL PROTECTED] Sent: September 16, 2004 8:35 AM To: [EMAIL PROTECTED] Subject: Re: Problem with tables; You need to do desc spamdata; NOT desc id. Aman Raheja Beauford wrote: Hi, I'm trying to create a table using the commands below, and everything seems to work and the table is there - I just can's access any of the fields. This happens on both my Windows and Linux versions of MySQL. Any help is appreciated. Note, I have 8 other databases that I created with no problem about 2 months ago (on both platforms) - this is the first time I have tried since then. Any help is appreciated Thanks I am in the proper database when I run the commands below... CREATE TABLE spamdata ( id bigint(20) NOT NULL, dates varchar(60) NOT NULL, probe varchar(100) NOT NULL, ip varchar(15) NOT NULL, fqdnvarchar(100) NOT NULL, reason varchar(100) NOT NULL, portvarchar(2) NOT NULL, codevarchar(10) NOT NULL, PRIMARY KEY (idm)); Query OK, 0 rows affected (0.08 sec) *** This tells me nothing happened... When I run 'desc id' (or any of the others) I get the following: mysql desc id; ERROR 1146: Table 'spamtool.id' doesn't exist This shows the table is there though. mysql show tables; ++ | Tables_in_spamtool | ++ | spamdata | ++ 1 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with tables;
Hi, I'm trying to create a table using the commands below, and everything seems to work and the table is there - I just can's access any of the fields. This happens on both my Windows and Linux versions of MySQL. Any help is appreciated. Note, I have 8 other databases that I created with no problem about 2 months ago (on both platforms) - this is the first time I have tried since then. Any help is appreciated Thanks I am in the proper database when I run the commands below... CREATE TABLE spamdata ( id bigint(20) NOT NULL, dates varchar(60) NOT NULL, probe varchar(100) NOT NULL, ip varchar(15) NOT NULL, fqdnvarchar(100) NOT NULL, reason varchar(100) NOT NULL, portvarchar(2) NOT NULL, codevarchar(10) NOT NULL, PRIMARY KEY (idm)); Query OK, 0 rows affected (0.08 sec) *** This tells me nothing happened... When I run 'desc id' (or any of the others) I get the following: mysql desc id; ERROR 1146: Table 'spamtool.id' doesn't exist This shows the table is there though. mysql show tables; ++ | Tables_in_spamtool | ++ | spamdata | ++ 1 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]
Problem with tables;
Just some updated info on this. It appears everything is there and working. I can update the table, do queries on the table, etc. - the only thing I can't do is a 'desc whatever' I still get the 1146 error. Thanks -Original Message- From: Beauford [mailto:[EMAIL PROTECTED] Sent: September 16, 2004 12:19 AM To: [EMAIL PROTECTED] Subject: Problem with tables; Hi, I'm trying to create a table using the commands below, and everything seems to work and the table is there - I just can's access any of the fields. This happens on both my Windows and Linux versions of MySQL. Any help is appreciated. Note, I have 8 other databases that I created with no problem about 2 months ago (on both platforms) - this is the first time I have tried since then. Any help is appreciated Thanks I am in the proper database when I run the commands below... CREATE TABLE spamdata ( id bigint(20) NOT NULL, dates varchar(60) NOT NULL, probe varchar(100) NOT NULL, ip varchar(15) NOT NULL, fqdnvarchar(100) NOT NULL, reason varchar(100) NOT NULL, portvarchar(2) NOT NULL, codevarchar(10) NOT NULL, PRIMARY KEY (idm)); Query OK, 0 rows affected (0.08 sec) *** This tells me nothing happened... When I run 'desc id' (or any of the others) I get the following: mysql desc id; ERROR 1146: Table 'spamtool.id' doesn't exist This shows the table is there though. mysql show tables; ++ | Tables_in_spamtool | ++ | spamdata | ++ 1 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]
Searh question
If I have a database with various dates in it, how can I search for all instances of the same date. Note that I do not know what the dates are (or how many there will be) so the search can't be hardcoded. These dates will be added based on user input from a website. Is there some kind of comparison function where I can search on field that have the same values..? TIA PS I want to end up with a table that follows this format (this is just an example) 01/01/03 Name Department Title Name Department Title Name Department Title Name Department Title Name Department Title 05/01/03 Name Department Title Name Department Title Name Department Title Name Department Title Name Department Title -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help. Not sure my other email got through!
Not sure my other email got through, so here it is again, with an updated description of the problem. Hi, I have a database with 3 names in it. In each of these names is 5 categories that have numbers in them. I want to be able to do a select and total these numbers for each name, and then sort them in descending order. I have tried for over 6 hours tonight and no matter what I do, there is one minor glitch. I am not supplying any code as I would like to know from you people first what the best way to go about this would be - as what I have done is obviously not working. TIA i.e This is what is in the database. Table 1 ID1 NAME 1 Bill 2 John 3 Fred Table 2 ID2 Col1 Col2 Col3 Col4 1 1456 2 3286 3 4922 Database 3 is a reference file that ties them together - so I would have to use a join in the select statement. ID1 ID2 11 22 33 This is what I want to be able to show: John 21 Fred 20 Bill 16 sql, query, queries, smallint - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Linux Mysql vs Windows Mysql
Hi,
I just installed MySQL and PHP on Windows XP and have a question. I created
a test database on Windows (exactly the same as the test one I use in Linux)
and copied a test PHP script which accesses Mysql and displays some
information. On Linux it works perfectly, but on Windows I get the following
error. Are there different commands or syntax on the two different
platforms?
Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result
resource in e:\directory\index.php on line 21.
This is the exact code below:
?
PRINT HTML;
PRINT HEAD;
PRINT TITLEADDRESSES/title;
PRINT /HEAD;
PRINT BODY BGCOLOR=white TEXT=#00 LINK=#FF VLINK=#66
aLINK=#FF;
$out[]=0; // I also got an error about this - without it here I get an
undefined variable error below
mysql_connect(localhost:3308, user, password) or
die(could not connect);
mysql_select_db(address);
$query = SELECT * FROM data;
$result = mysql_query ($query);
while ($line = mysql_fetch_row($result)) {
$out[]=$i;
}
?
form action=? echo $PHP_SELF; ? method=post
PTABLE WIDTH=400 BORDER=0
TRTD ALIGN=leftName :/TDTD ALIGN=leftINPUT TYPE=text
NAME=lastname SIZE=20 VALUE=? echo
$out[0][1]?/TD/TR
TRTD ALIGN=leftName :/TDTD ALIGN=leftINPUT TYPE=text
NAME=lastname SIZE=20 VALUE=? echo
$out[0][2]?/TD/TR
TRTD ALIGN=leftCity:/TDTD ALIGN=leftINPUT TYPE=text
NAME=city SIZE=20 VALUE=? echo
$out[0][3] ?/TD/TR
/TABLE
/FORM
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Duplicating a table question
These spam filters are really annoying. It's hard to get an answer to my question when my message gets bounced 'cause it doesn't have MySQL or query in the message. In any event, Is there a way to duplicate a complete table. I am making some modifications and don't want to use the working table for testing. TIA - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Automatically totalling columns.
Hi, I have two tables, one with peoples names in it and one with data associated to these people. What I need to do is to be able to automatically total certain items in the data table that correspond with a name in the first table and then update the name table.. i.e. This is a crude example, but you get the picture. When I add an item to the data table I want to have it so it automatically gets added to the correct person in the name table. I also have a third table which references these two tables. Can this be done from within MySQL. Names Data Col. 1 Col 2 Col. 1Col. 2 Fred$2.24Milk$1.00 Wilma $4.25Bread $1.24 Barney $1.00Eggs $2.19 Betty$3.19Butter $3.25 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Load Data Infile command
Try this when your logging into mysql, not when starting mysql. This works for me on Linux. mysql --local-infile=1 -u username-p - Original Message - From: Jon Bertsch [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, December 12, 2002 12:02 PM Subject: Load Data Infile command HI all, I'm new to the list so I apologize if this subject has been answered already, I couldn't find a mailing list archive. We just upgraded (?) mysql to 3.23.49. We are running it on Linux Redhat 7.3 The load data local command is turned off by default. I have attempted to follow the instructions in the documentation (Ch. 4.2.4 and others) about this but it has been a dismal failure. I tried the following on restarting mysql: /etc/init.d/mysqld start --local-infile[=1] as in the manual. No luck. I also made some variations on this: --local-infile=1 --local-infile --local-infile(=1) --local-infile=[1] --local-infile=(1) None of these worked. I added the following two lines to the my.cnf file: [client] enable-local-infile and restarted. No luck. Has anyone been able to get this to work? I would really appreciate any help on this since I use the command to load data and it is part of several utility applications I use. Thanks in advance Jon Bertsch - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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
Displaying output from MySQL
Hi, Not sure if this is a PHP of a MySQL question, so I am sending it to both groups. Basically I have a list of numbers with two decimal places in the MySQL database, but I only want to display some of them with the decimal points. i.e. 70 (not 70.00) 87 51.5 46.75 12 29 5.5 -1 45 I know it's probably a weird request, but any thoughts on how one would do this either through PHP or MySQL. TIA - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Select * From table where name Like 'help'; Help
I am using PHP on my website, but this is certainly a MySQL question. That shouldn't matter, because the real value $var is inserted before the query is send to the MySQL server, where REGEXP of the query is evaluated. If there is a problem, you need to be much more specific. Quote the error message, show the relevant part of your code and so on. The real value of $var is not inserted before it is sent to the server. REGEXP thinks that 'var' is part of the search pattern because $ is a reserved operator for REGEXP. So what gets sent to the server is totally different than what I want to be sent. There are no errors because the syntax is correct, its just not correct for the search I want it to perform. So the questions still remains, how do I get REGEXP to treat $var as a variable and not part of its own syntax. I have been looking at http://www.mysql.com/doc/en/Regexp.html, but it does not cover this. - Original Message - From: Benjamin Pflugmann [EMAIL PROTECTED] To: Beauford.2003 [EMAIL PROTECTED] Cc: Robert Citek [EMAIL PROTECTED]; MySQL List [EMAIL PROTECTED] Sent: Monday, December 09, 2002 3:30 AM Subject: Re: Select * From table where name Like 'help'; Help Hello. On Mon 2002-12-09 at 01:00:33 -0500, [EMAIL PROTECTED] wrote: I am doing this search from a website, What does that mean? Which scripting language do you use? PHP? This is more a question for your website environment than about MySQL. and from what I can tell there is no way to use a variable (i.e. $var) with REGEXP as the $ sign has a special meaning to REGEXP. That shouldn't matter, because the real value $var is inserted before the query is send to the MySQL server, where REGEXP of the query is evaluated. If there is a problem, you need to be much more specific. Quote the error message, show the relevant part of your code and so on. HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Select * From table where name Like 'help'; Help
My mistake, I assumed the space was just for clarity and not really meaningful, but since it is between the ' ' it makes sense that is. I tried a few variations on this and think I have found what I need - where title like binary '%$var%. Notice the key word 'binary' which makes the world of difference. From the tests I have run this will find the occurrence of any word that begins with a capital and then matches any remaining characters. So if I search for One it will fine One Day, Day One, This One Day - however, if I search for On it will also find On The Water and Your On Third. The only thing I could do is to add a space after $var, but them partial searches won't be found. i.e. searching for beaufor will not find beauford. Until I can find a better way, one of these will suffice. Thanks for your correction as I did miss the space thing. Beauford - Original Message - From: Jocelyn Fournier [EMAIL PROTECTED] To: Beauford.2003 [EMAIL PROTECTED] Sent: Monday, December 09, 2002 9:31 AM Subject: Re: Select * From table where name Like 'help'; Help No it doesn't, since I introduce a space between % and one. So stone will not be returned :) Regards, Jocelyn - Original Message - From: Beauford.2003 [EMAIL PROTECTED] To: Jocelyn Fournier [EMAIL PROTECTED] Sent: Monday, December 09, 2002 2:26 PM Subject: Re: Select * From table where name Like 'help'; Help That is what I am using, however, it is not as detailed as I need it to be. Like I said in my original question, I don't want to get 'stone' when I search for 'one', and using LIKE does exactly this. Thanks, Beauford - Original Message - From: Jocelyn Fournier [EMAIL PROTECTED] To: Beauford.2003 [EMAIL PROTECTED]; MySQL List [EMAIL PROTECTED] Sent: Sunday, December 08, 2002 7:57 PM Subject: Re: Select * From table where name Like 'help'; Help Hi, A dirty solution would be to search : field LIKE '% one %' or field LIKE 'one %' or field LIKE '% one' or field='one'; Regards, Jocelyn - Original Message - From: Beauford.2003 [EMAIL PROTECTED] To: MySQL List [EMAIL PROTECTED] Sent: Tuesday, December 10, 2002 12:50 AM Subject: Select * From table where name Like 'help'; Help Hi, Hey, did this list change their spam filters? Now every time I send an email it gets bounced unless the word MySQL or Query is in the email. Anyway, Please read below for a recent problem I am having. I want to be able to search on my database and be able to get the following, but running into a few snags. If I search for the word - One - I want to see: Another One One Day One on One Your The One But not: Fashioned Stone Everyone Also, and maybe part of the solution - is there a way for the search to be case sensitive. TIA Beauford - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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
Re: Select * From table where name Like 'help'; Help
Andy, I mentioned in my previous email that I am using PHP, and I have also tried putting quotes around $var (many different ways) with no better results. REGEXP just gives a syntax error when I do this. Through the suggestion of another list user I have found a way that suits my needs using the where name like binary '$var'. It's jury rigged, but works the way I need it to work for the most part. Thanks for your suggestions. - Original Message - From: Andy Bakun [EMAIL PROTECTED] To: Beauford.2003 [EMAIL PROTECTED] Cc: MySQL List [EMAIL PROTECTED] Sent: Monday, December 09, 2002 3:08 PM Subject: Re: Select * From table where name Like 'help'; Help You still have not mentioned what language you are using to interface with MySQL. If you are using PHP or Perl, then things like single and double quotes will make a difference here, and looking in the MySQL manual will not help you. Check the string that contains the query, if it looks like this: $q = 'select from table where col regexp $var'; change it to $q = select from table where col regexp '$var'; The enclosing single quotes are keeping the value of $var from being interpolated into the string in the above example. See the manual for your programming language for more information on variable interpolation. On Mon, 2002-12-09 at 08:22, Beauford.2003 wrote: I am using PHP on my website, but this is certainly a MySQL question. That shouldn't matter, because the real value $var is inserted before the query is send to the MySQL server, where REGEXP of the query is evaluated. If there is a problem, you need to be much more specific. Quote the error message, show the relevant part of your code and so on. The real value of $var is not inserted before it is sent to the server. REGEXP thinks that 'var' is part of the search pattern because $ is a reserved operator for REGEXP. So what gets sent to the server is totally different than what I want to be sent. There are no errors because the syntax is correct, its just not correct for the search I want it to perform. So the questions still remains, how do I get REGEXP to treat $var as a variable and not part of its own syntax. I have been looking at http://www.mysql.com/doc/en/Regexp.html, but it does not cover this. - Original Message - From: Benjamin Pflugmann [EMAIL PROTECTED] To: Beauford.2003 [EMAIL PROTECTED] Cc: Robert Citek [EMAIL PROTECTED]; MySQL List [EMAIL PROTECTED] Sent: Monday, December 09, 2002 3:30 AM Subject: Re: Select * From table where name Like 'help'; Help Hello. On Mon 2002-12-09 at 01:00:33 -0500, [EMAIL PROTECTED] wrote: I am doing this search from a website, What does that mean? Which scripting language do you use? PHP? This is more a question for your website environment than about MySQL. and from what I can tell there is no way to use a variable (i.e. $var) with REGEXP as the $ sign has a special meaning to REGEXP. That shouldn't matter, because the real value $var is inserted before the query is send to the MySQL server, where REGEXP of the query is evaluated. If there is a problem, you need to be much more specific. Quote the error message, show the relevant part of your code and so on. HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Select * From table where name Like 'help'; Help
This is most definitely a quoting issue with your PHP string. If it is
quoted correctly, the MySQL server will never see the string $var
appearing after the keyword REGEXP, so if $ has meaning to a regular
expression wouldn't matter (unless the value of $var contained a $).
I've used REGEXP with (really complex) dynamicly built regular
expressions stored in PHP variables a number of times and have never had
a problem as you've described above.
MySQL is not seeing $var, but it is seeing var from what I can understand of
Regexp (the manual on this is not very good), $ means to match the end of a
string, so since I have $var, I am thinking that Regexp thinks that the end
of the string I want to search for ends with var. In any event, the actual
value of $var never gets sent to MySQL, so the search will always fail.
Maybe I am misunderstanding the meaning of this, but since I really can't
make any sense out of the manual, I am guessing at what I think they mean.
Let me give you and example:
$var=One ($var is passed the string from a form on a webpage)
There are 3 entries in the database with the word One
One Day
Day One
That's the One
There are also other words that contain the word one, like stone and anyone.
I want to be able to run a search that will just show the first 3 by typing
in the word One ('one' would result in the string not found').
Beauford
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Fw: Thanks
Thanks to all who recently answered some of my MySQL questions, although I am still having some problems with the concept of joins, but what I have figured out certainly does make it more efficient and versatile. I have managed to eliminate two tables (actually combine them into one) which made my searches easier. Thanks again, Beauford - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: LOAD DATA command
If your using a Linux machine try using this when logging on to mysql: mysql --local-infile=1 -u username -p Not sure about Windows. There is apparently a security issue with this so it is not enable by default. Can't remember where I read this, but the above works for me. Beauford - Original Message - From: Insanely Great [EMAIL PROTECTED] To: Padma kuruganti [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Saturday, December 07, 2002 12:32 PM Subject: Re: LOAD DATA command Greetings... Are you using Windows to connect to the MySQL. Then you can try SQLyog at http://www.webyog.com/sqyog/download.html It will help you a lot in these works and you dont have to write queries also. Rgds Insane - Original Message - From: Padma kuruganti [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, December 07, 2002 12:46 AM Subject: LOAD DATA command Hi I just downloaded version 3.23.53 for evaluation. when I try to use LOAD DATA LOCAL INFILE 'some.txt into TABLE sometable I get a message saying 'the used command is not allowed in this version of mySQL. IS there any update on this? I could not find any info on your web site. Thanks KP __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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
Select * From table where name Like 'help'; Help
Hi, Hey, did this list change their spam filters? Now every time I send an email it gets bounced unless the word MySQL or Query is in the email. Anyway, Please read below for a recent problem I am having. I want to be able to search on my database and be able to get the following, but running into a few snags. If I search for the word - One - I want to see: Another One One Day One on One Your The One But not: Fashioned Stone Everyone Also, and maybe part of the solution - is there a way for the search to be case sensitive. TIA Beauford - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Select * From table where name Like 'help'; Help
I am doing this search from a website, and from what I can tell there is no way to use a variable (i.e. $var) with REGEXP as the $ sign has a special meaning to REGEXP. Any other suggestions, or would you know a way around this. TIA - Original Message - From: Robert Citek [EMAIL PROTECTED] To: Beauford.2003 [EMAIL PROTECTED] Cc: MySQL List [EMAIL PROTECTED] Sent: Sunday, December 08, 2002 8:16 PM Subject: Re: Select * From table where name Like 'help'; Help Try the REGEXP modifier. For example: mysql select Another One regexp [[::]]One[[::]] ; mysql select Fashioned regexp [[::]]One[[::]] ; The format differs from that used in sed, grep, awk, perl, python, etc. http://www.mysql.com/documentation/mysql/bychapter/manual_Regexp.html#Regexp Regards, - Robert - At 07:50 PM 12/9/2002 -0500, Beauford.2003 wrote: If I search for the word - One - I want to see: Another One But not: Fashioned - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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
Join help.
Hi, Is there a good tutorial somewhere on the join command. No matter what I do it just doesn't work. Obviously I'm doing something wrong, but the MySQL manual just doesn't help at all. TIA - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Table setup question
Adolfo, Thanks for the info, but can you elaborate on it, 'cause basically I am just not getting the concept. No way no how can I get these joins to work. mysql describe songs; (this contains an ID field and the title of the song and the artist.) ++-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-+---+ | ids| int(11) | | PRI | 0 | | | title | varchar(55) | YES | | NULL| | | artist | varchar(30) | YES | | NULL| | ++-+--+-+-+---+ 3 rows in set (0.00 sec) mysql describe album; (this contains an ID field and the name of the album the above songs came from.) +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | ida | int(11) | | PRI | 0 | | | name | varchar(35) | YES | | NULL| | +---+-+--+-+-+---+ 2 rows in set (0.00 sec) mysql describe reference; (this contains the ID's that corrspond to the above two tables.) +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | ida | int(11) | | PRI | 0 | | | ids | int(11) | | PRI | 0 | | +---+-+--+-+-+---+ 2 rows in set (0.00 sec) This table is in this format: The left column corresponds to the ID of the ablum and the right field correspond to the ID of the song. From the example below, you can see that song #1 appears on Albums 2, 3, 6, and 16. SongAlbum 12 13 16 115 27 221 223 So with all this information, how would I perform a select that would show the all the albums a particular song were on. (from the example above what would I need to do to show the album name and song title for song ID # 1 above). Sorry if this is long, I just wanted to make sure there is enough info. TIA Beauford - Original Message - From: Adolfo Bello [EMAIL PROTECTED] To: 'Beauford.2003' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, December 02, 2002 7:33 AM Subject: RE: Table setup question Try something like CREATE TABLE album( ida int primary key , title varchar(n) not null ) ; CREATE TABLE songs( ids intprimary key, song varchar(m) not null ) ; CREATE TABLE albumsongs( ida int not null, ids int not null, primary key(ida,ids), foreign key(ida) references album(ida), foreign key(ids) references songs(ids)); Querying for albums which have a songs is something like: SELECT title,song FROM album INNER JOIN albumsongs ON ... INNER JOIN songs ON ... WHERE song='your song name'; -Original Message- From: Beauford.2003 [mailto:[EMAIL PROTECTED]] Sent: Sunday, December 01, 2002 9:59 PM To: [EMAIL PROTECTED] Subject: Table setup question Hi, I have a database of albums by a group and I want to be able to search on this table to find out what songs are duplicated on what albums (there are 36+ albums). The problem is how do I set up the database. Example: Album1 has 3 songs. 1.song, 2.song, 3.song, 4.song Album2 has 4 songs. 4.song, 5.song, 6.song, 3.song Album3 has 4 songs. 7.song, 8.song, 1.song, 3.song So 3.song appears on all 3 albums. Currently I have it set up with two tables as shown below, but I am thinking there has to be a better way to do this than to duplicate the name of the song three, four, or five times in the table. Table AlbumName AlbumID == Album1 1 Album2 2 Album3 3 Table SongTitle SongID === == 3.song 1 3.song 2 3.song 3 7.song 3 etc. etc. So basically my search is - SELECT Album, Song FROM AlbumName, SongTitle WHERE AlbumName.ID=SongTitle.ID; Given the setup above, is there a way that I can put in the SongTitle.ID field that song appears on more than one album. Maybe something like: SongID === == 3.song 1, 2, 3 But then what would my search be. Sorry for the length of this, but I am learning MySQL and trying to get a handle on all of it. My way works, but I'm sure there has to be a better way. Any thoughts are appreciated. TIA, Beauford - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Table setup question
Thanks for the help, but I just can't grasp the concept (I'm not even sure I have my table layouts the way they should be to have this work). I'll have to look into this further, but for the time being the way I have it is working. Beauford - Original Message - From: Adolfo Bello [EMAIL PROTECTED] To: 'Beauford.2003' [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, December 04, 2002 4:28 PM Subject: RE: Table setup question Let's assume you want the entire albums list which the song Day and Night by Billie Holiday is in. SELECT t1.title,t1.artist,t3.name FROM sings t1 INNER JOIN reference t2 ON t1.ids=t2.ids INNER JOIN album t3 ON t2.ida=t3.ida WHERE t1.title='Day and Night' AND t1.artist='Billie Holiday' (or WHERE t1.ids=1 if you know the id of the song) Relationship is like: [songs] [album] \\// [reference] (this later one contains the list of songs of each album) This way you treat songs and album separately and link them through another table so you don't have any limit on the number of albums a song can belong to. If you know the id of a song all you have to do is Adolfo -Original Message- From: Beauford.2003 [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 05, 2002 3:42 PM To: Adolfo Bello Cc: [EMAIL PROTECTED] Subject: Re: Table setup question Adolfo, Thanks for the info, but can you elaborate on it, 'cause basically I am just not getting the concept. No way no how can I get these joins to work. mysql describe songs; (this contains an ID field and the title of the mysql song and the artist.) ++-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-+---+ | ids| int(11) | | PRI | 0 | | | title | varchar(55) | YES | | NULL| | | artist | varchar(30) | YES | | NULL| | ++-+--+-+-+---+ 3 rows in set (0.00 sec) mysql describe album; (this contains an ID field and the name of the mysql album the above songs came from.) +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | ida | int(11) | | PRI | 0 | | | name | varchar(35) | YES | | NULL| | +---+-+--+-+-+---+ 2 rows in set (0.00 sec) mysql describe reference; (this contains the ID's that corrspond to the above two tables.) +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | ida | int(11) | | PRI | 0 | | | ids | int(11) | | PRI | 0 | | +---+-+--+-+-+---+ 2 rows in set (0.00 sec) This table is in this format: The left column corresponds to the ID of the ablum and the right field correspond to the ID of the song. From the example below, you can see that song #1 appears on Albums 2, 3, 6, and 16. SongAlbum 12 13 16 115 27 221 223 So with all this information, how would I perform a select that would show the all the albums a particular song were on. (from the example above what would I need to do to show the album name and song title for song ID # 1 above). Sorry if this is long, I just wanted to make sure there is enough info. TIA Beauford - Original Message - From: Adolfo Bello [EMAIL PROTECTED] To: 'Beauford.2003' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, December 02, 2002 7:33 AM Subject: RE: Table setup question Try something like CREATE TABLE album( ida int primary key , title varchar(n) not null ) ; CREATE TABLE songs( ids intprimary key, song varchar(m) not null ) ; CREATE TABLE albumsongs( ida int not null, ids int not null, primary key(ida,ids), foreign key(ida) references album(ida), foreign key(ids) references songs(ids)); Querying for albums which have a songs is something like: SELECT title,song FROM album INNER JOIN albumsongs ON ... INNER JOIN songs ON ... WHERE song='your song name'; -Original Message- From: Beauford.2003 [mailto:[EMAIL PROTECTED]] Sent: Sunday, December 01, 2002 9:59 PM To: [EMAIL PROTECTED] Subject: Table setup question Hi, I have a database of albums by a group and I want to be able to search on this table to find out what songs are duplicated on what albums (there are 36+ albums). The problem is how do I set up the database. Example: Album1 has 3 songs. 1.song, 2.song, 3.song, 4.song Album2 has 4 songs
Select, mysql_fetch_array, PHP question
Hi,
I'm trying to do a search on database through a web interface using PHP and
having one small problem. Example: If I search for items that cost $5.00 and
there are three of them, the query works and displays the information, but
if there is only one item the query works but doesn't display the item. I
just get a blank page.
SELECT price, item FROM list WHERE price=$5.00;
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo $item; }
If I do the same search from the MySQL command line the query will display
one or more items.
Ideas?
TIA
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Table setup question
DL, Yep, I see what your saying. In my application it may work fine, but in larger applications where things may change, it would be easier to do it with the 3 tables. Thanks - Original Message - From: DL Neil [EMAIL PROTECTED] To: Beauford.2003 [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, December 03, 2002 7:56 AM Subject: Re: Table setup question Beauford, OK, I get it now. I thought there would need to be duplication in the AlbumSonglist DB. =indeed, the process of 'normalisation' (as mentioned earlier) helps to ascertain when and where 'duplication' is allowed/beneficial/should not happen. Just one other question though. What is the difference/benefits of doing it this way, than the way I have it. Currently I use two tables - the songlist table includes 2 id fields (one that corresponds with the album title, and one that is used for the song title). The difference between what you have said and what I have is that I have duplicated the actual song titles instead of a pointer to the song title This way I have eliminated one table. =try wording a query to get from album to song title, and then another query starting from song title and working to album. That's always a good test of two-way relationship situations. =if you have a pointer repeated then (usually) it will take up less storage space than a repeated song title. OTOH getting rid of a table and the join in each query will speed up processing time/throughput (by a v.small amount)... =if some data ever changes (admittedly I can't see that it would in your application - but you're the subject matter expert), eg someone remixes a song, so to be more precise you want to modify Leila to have Leila - original and Leila - remix as two different entries in the db; then how easy would it be to go through the song title data and make that change? In the m-m model the change would be needed only once (the Song Title tbl - all else unaffected). In your model, you would need to be sure that you changed every case - and without knowing how many changes is 'enough'! (now apply this to customer records and people changing addresses and you'll see why/which is the 'industry standard' approach). =however the ultimate answer to your question is: if it works for you/your users, then it is 'right'! Thanks again, Beauford =hope it helps you along your way to MySQL expertise, =dn - Original Message - From: DL Neil [EMAIL PROTECTED] To: Beauford.2003 [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, December 02, 2002 10:41 AM Subject: Re: Table setup question Beauford, [please reply to the list - there are always others 'lurking' who will learn from the discussion (and use of the archives is a wonderful dream...)] You are correct, there will be duplication in the AlbumSong table - but not within one field, only within one column. Album will consist of a unique ID (IDA) and each Title will, one assumes be unique (unless two groups use the same name - I guess that must be possible). Similarly Songlist will also consist of a unique ID (IDS) and once again, one assumes that any repetition of title will be two different songs that share the same name. These two ID 'uniqueness' rules must apply for the next to work, ie you will most likely define them as PRIMARY KEY columns. The 'translation' table, breaks the 'unique' 'rule', So if song Q apears on albums A, C, and E as you ask, then we will see: AlbumSong IDAIDS 11 21 31 Note that while 1 repeats in AlbumSong, it does not repeat (a) in Songlist, nor in (b) one row/field of AlbumSong, eg IDA IDS 1,2,31 THE ABOVE IS TOTALLY WRONG!!! In the case of AlbumSong the table will not be able to have a PRIMARY KEY column(s), but each of the individual columns should probably be INDEXed for read-speed. Be aware that AlbumSong contains no real 'data' from an end-user perspective. It is a table made up purely of 'links' or 'keys' or 'IDs' to the other two tables. The Album and Songlist tables do all the 'data' work, AlbumSong does all the 'heavy lifting' to relate Album's data to Songlist's, and/or vice-versa. Apologies if this was not clear, first time round, =dn DL, OK, that helps a lot, but I am still confused. I am still struggling with how you can not have duplication in at least one of the tables. A diagram here may help. So if song Q apears on albums A, C, and E - I don't quite understand how AlbumSong is going to be able to know which songs are duplicated without duplication of ID's. (There are 500 songs and only 36 albums). Thanks AlbumSonglistAlbumSong IDATITLEIDSSongIDAIDS 1
Re: Select, mysql_fetch_array, PHP question (sorry - typo in my original email)
Sorry, where it says echo $item should have read echo $line['item']; Simply
a typo, but the problem is still the same.
Hi,
I'm trying to do a search on database through a web interface using PHP and
having one small problem. Example: If I search for items that cost $5.00 and
there are three of them, the query works and displays the information, but
if there is only one item the query works but doesn't display the item. I
just get a blank page.
SELECT price, item FROM list WHERE price=$5.00;
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo $item; }
If I do the same search from the MySQL command line the query will display
one or more items.
Ideas?
TIA
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
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: Select, mysql_fetch_array, PHP question
Alex,
That's it - I have a if (mysql_fetch_row($result)) before the while loop.
I've changed it slightly and now it works fine. The little things are always
the ones that get you.
Thanks
- Original Message -
From: Alex Pukinskis [EMAIL PROTECTED]
To: Beauford.2003 [EMAIL PROTECTED]
Sent: Tuesday, December 03, 2002 7:41 PM
Subject: Re: Select, mysql_fetch_array, PHP question
Maybe you have an if ($line = mysql_fetch_array($result)) line
somewhere up above? That would cause the while loop to skip the first
record... I usually use a do...while loop with recordsets to avoid this
problem.
That's my best guess, without seeing more code.
-Alex
On Tuesday, December 3, 2002, at 01:40 PM, Beauford.2003 wrote:
That is what I have, a simple typo on my part.
- Original Message -
From: Alex Pukinskis [EMAIL PROTECTED]
To: Beauford.2003 [EMAIL PROTECTED]
Sent: Tuesday, December 03, 2002 5:04 PM
Subject: Re: Select, mysql_fetch_array, PHP question
To be honest, I'm not sure why what you're doing works at all. You
don't ever assign a value to the variable $item. Try changing
echo $item;
to
echo $line['item'];
Is there more code involved that you didn't include?
-Alex
On Tuesday, December 3, 2002, at 06:38 AM, Beauford.2003 wrote:
Hi,
I'm trying to do a search on database through a web interface using
PHP
and
having one small problem. Example: If I search for items that cost
$5.00 and
there are three of them, the query works and displays the information,
but
if there is only one item the query works but doesn't display the
item. I
just get a blank page.
SELECT price, item FROM list WHERE price=$5.00;
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo $item; }
If I do the same search from the MySQL command line the query will
display
one or more items.
Ideas?
TIA
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail mysql-unsubscribe-
[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: Table setup question
DL, OK, I get it now. I thought there would need to be duplication in the AlbumSonglist DB. Just one other question though. What is the difference/benefits of doing it this way, than the way I have it. Currently I use two tables - the songlist table includes 2 id fields (one that corresponds with the album title, and one that is used for the song title). The difference between what you have said and what I have is that I have duplicated the actual song titles instead of a pointer to the song title This way I have eliminated one table. Thanks again, Beauford - Original Message - From: DL Neil [EMAIL PROTECTED] To: Beauford.2003 [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, December 02, 2002 10:41 AM Subject: Re: Table setup question Beauford, [please reply to the list - there are always others 'lurking' who will learn from the discussion (and use of the archives is a wonderful dream...)] You are correct, there will be duplication in the AlbumSong table - but not within one field, only within one column. Album will consist of a unique ID (IDA) and each Title will, one assumes be unique (unless two groups use the same name - I guess that must be possible). Similarly Songlist will also consist of a unique ID (IDS) and once again, one assumes that any repetition of title will be two different songs that share the same name. These two ID 'uniqueness' rules must apply for the next to work, ie you will most likely define them as PRIMARY KEY columns. The 'translation' table, breaks the 'unique' 'rule', So if song Q apears on albums A, C, and E as you ask, then we will see: AlbumSong IDAIDS 11 21 31 Note that while 1 repeats in AlbumSong, it does not repeat (a) in Songlist, nor in (b) one row/field of AlbumSong, eg IDA IDS 1,2,31 THE ABOVE IS TOTALLY WRONG!!! In the case of AlbumSong the table will not be able to have a PRIMARY KEY column(s), but each of the individual columns should probably be INDEXed for read-speed. Be aware that AlbumSong contains no real 'data' from an end-user perspective. It is a table made up purely of 'links' or 'keys' or 'IDs' to the other two tables. The Album and Songlist tables do all the 'data' work, AlbumSong does all the 'heavy lifting' to relate Album's data to Songlist's, and/or vice-versa. Apologies if this was not clear, first time round, =dn DL, OK, that helps a lot, but I am still confused. I am still struggling with how you can not have duplication in at least one of the tables. A diagram here may help. So if song Q apears on albums A, C, and E - I don't quite understand how AlbumSong is going to be able to know which songs are duplicated without duplication of ID's. (There are 500 songs and only 36 albums). Thanks AlbumSonglistAlbumSong IDATITLEIDSSongIDAIDS 1A 1Q?? 2B2R?? 3C3S?? 4D4T?? 5E 5V ?? ? ? ? ? ? ? - Original Message - From: DL Neil [EMAIL PROTECTED] To: Beauford.2003 [EMAIL PROTECTED]; Sent: Monday, December 02, 2002 4:32 AM Subject: Re: Table setup question Hi Beauford, You are on the right track. Yes you should remove the songs to a separate table. If you merely duplicate the first example (below) in two tables you have created a one-to-many relationship between the Album table and the Songs table - one album has many songs on it. Your query code applies. However the Songs table still has duplication in it, eg 3.song appears thrice, and we can't have that! The problem is, if you cut down the Songs table entries so that 3.song appears only once, how do you link to multiple Albums? So now you are into a many-to-many relationship between the Album table and the Songs table - one album has many songs on it AND one song may appear on several albums. Problem! You should not have a field with 'repetition' within it, eg for each album hold two fields: ID, and Songs - where songs is a list of IDs/titles. This is not 'relational'. You can read up about a process known as normalisation which teaches a basic three step process (some go to more than
Table setup question
Hi, I have a database of albums by a group and I want to be able to search on this table to find out what songs are duplicated on what albums (there are 36+ albums). The problem is how do I set up the database. Example: Album1 has 3 songs. 1.song, 2.song, 3.song, 4.song Album2 has 4 songs. 4.song, 5.song, 6.song, 3.song Album3 has 4 songs. 7.song, 8.song, 1.song, 3.song So 3.song appears on all 3 albums. Currently I have it set up with two tables as shown below, but I am thinking there has to be a better way to do this than to duplicate the name of the song three, four, or five times in the table. Table AlbumName AlbumID == Album1 1 Album2 2 Album3 3 Table SongTitle SongID === == 3.song 1 3.song 2 3.song 3 7.song 3 etc. etc. So basically my search is - SELECT Album, Song FROM AlbumName, SongTitle WHERE AlbumName.ID=SongTitle.ID; Given the setup above, is there a way that I can put in the SongTitle.ID field that song appears on more than one album. Maybe something like: SongID === == 3.song 1, 2, 3 But then what would my search be. Sorry for the length of this, but I am learning MySQL and trying to get a handle on all of it. My way works, but I'm sure there has to be a better way. Any thoughts are appreciated. TIA, Beauford - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Update help
Hi, I'm trying to update a table from my webpage but not having much luck, although it works from the command line. Obviously I am missing something and would appreciate any help. Also. Is there a good tutorial or help page for Mysql. I find the one at www.mysql.com not very helpful. TIA The code: ? $db =mysql_connect(url,name,pass); mysql_select_db(dbasename); $query = UPDATE counter SET number=number+1; ? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Scrolling Databases
Hi, I have created a contact database using Mysql and want to be able to scroll back and forth through the database. Even after I have done a search and found a record, I want to be able to scroll back or forth from that point on. I am using PHP4 to display the results. This may be something easy to do, but I have not found any documentation on it, and with my newbie skills at both Mysql and PHP, I haven't been able to put it together. Can someone point me in the right direction. TIA - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Errors stating Mysql server
MySQL must be the owner of the data dir. Check the owner of MySQL data dir. It was, but I have since changed it to root and it works fine now. Any idea why this would be? drwxr-xr-x 12 root mysql4096 Nov 8 18:00 ./ drwxr-xr-x 15 root root 4096 Nov 8 20:12 ../ drwxr-xr-x2 root mysql4096 Nov 8 18:00 bin/ drwxr-xr-x3 root mysql4096 Nov 8 17:59 include/ drwxr-xr-x2 root mysql4096 Nov 8 17:59 info/ drwxr-xr-x3 root mysql4096 Nov 8 17:59 lib/ drwxr-xr-x2 root mysql4096 Nov 8 18:00 libexec/ drwxr-xr-x3 root mysql4096 Nov 8 18:00 man/ drwxr-xr-x6 root mysql4096 Nov 8 18:00 mysql-test/ drwxr-xr-x3 root mysql4096 Nov 8 18:00 share/ drwxr-xr-x7 root mysql4096 Nov 8 18:00 sql-bench/ drwxr-xr-x5 root mysql4096 Nov 9 05:39 var/ TIA -Original Message- From: Victoria Reznichenko [mailto:victoria.reznichenko;ensita.net] Sent: November 9, 2002 11:28 AM To: [EMAIL PROTECTED] Subject: re: Errors stating Mysql server Beauford, Saturday, November 09, 2002, 8:27:58 AM, you wrote: B I just reinstalled Slackware and now have the unenviable task of B getting Mysql working. What a major pain in the ***. Anyway, here are B the errors. I have read manuals, searched the web, as well as the B newsgroups, and still can't get it to work. No matter what B permissions I change, the errors are still there. Is there not a B simple easy way to do this. This is really ridiculous. B TIA B 021108 21:18:11 mysqld started B /usr/local/mysql/libexec/mysqld: File './beauford-bin.009' not found B (Errcode: 13) B 021108 21:18:11 Could not use beauford-bin for logging (error 13) B 021108 21:18:11 /usr/local/mysql/libexec/mysqld: Can't create/write B to file '/usr/local/mysql/var/beauford.pid' (Errcode: 13) 021108 B 21:18:11 /usr/local/mysql/libexec/mysqld: Can't find file: B './mysql/host.frm' (errno: 13) 021108 21:18:11 B /usr/local/mysql/libexec/mysqld: Error on delete of B '/usr/local/mysql/var/beauford.pid' (Errcode: 2) 021108 21:18:11 B mysqld ended MySQL must be the owner of the data dir. Check the owner of MySQL data dir. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Errors stating Mysql server
I just reinstalled Slackware and now have the unenviable task of getting Mysql working. What a major pain in the ***. Anyway, here are the errors. I have read manuals, searched the web, as well as the newsgroups, and still can't get it to work. No matter what permissions I change, the errors are still there. Is there not a simple easy way to do this. This is really ridiculous. TIA 021108 21:18:11 mysqld started /usr/local/mysql/libexec/mysqld: File './beauford-bin.009' not found (Errcode: 13) 021108 21:18:11 Could not use beauford-bin for logging (error 13) 021108 21:18:11 /usr/local/mysql/libexec/mysqld: Can't create/write to file '/usr/local/mysql/var/beauford.pid' (Errcode: 13) 021108 21:18:11 /usr/local/mysql/libexec/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 021108 21:18:11 /usr/local/mysql/libexec/mysqld: Error on delete of '/usr/local/mysql/var/beauford.pid' (Errcode: 2) 021108 21:18:11 mysqld ended - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
