A Newbie question about make and the term.c file

[Peter Schrock]

I am trying to install mysql 5.1.59 on my ppc running os x and I get this
error message in the term.c file.

cc1: warnings being treated as errors
term.c: In function ‘term_set’:
term.c:946: warning: passing argument 1 of ‘tgetflag’ discards qualifiers
from pointer target type
term.c:947: warning: passing argument 1 of ‘tgetflag’ discards qualifiers
from pointer target type
term.c:949: warning: passing argument 1 of ‘tgetflag’ discards qualifiers
from pointer target type
term.c:950: warning: passing argument 1 of ‘tgetflag’ discards qualifiers
from pointer target type
term.c:952: warning: passing argument 1 of ‘tgetflag’ discards qualifiers
from pointer target type
term.c:953: warning: passing argument 1 of ‘tgetflag’ discards qualifiers
from pointer target type
term.c:955: warning: passing argument 1 of ‘tgetnum’ discards qualifiers
from pointer target type
term.c:956: warning: passing argument 1 of ‘tgetnum’ discards qualifiers
from pointer target type
make[2]: *** [term.o] Error 1
make[1]: *** [all-recursive] Error 1
make: *** [all-recursive] Error 1

I can't figure out what it means or how to fix it. Help please.

Peter

Re: A Newbie question about make and the term.c file

[Michael Dykman]

I can't help directly with the error message (the warning seems fairly
harmless), but may I inquire why you are building MySQL instead of using one
of the prepared binaries?  Compiling under OS/X can be pretty harrowing.

 - michael dykman

On Sat, Oct 8, 2011 at 2:49 AM, Peter Schrock peter.schr...@gmail.comwrote:

 I am trying to install mysql 5.1.59 on my ppc running os x and I get this
 error message in the term.c file.

 cc1: warnings being treated as errors
 term.c: In function ‘term_set’:
 term.c:946: warning: passing argument 1 of ‘tgetflag’ discards qualifiers
 from pointer target type
 term.c:947: warning: passing argument 1 of ‘tgetflag’ discards qualifiers
 from pointer target type
 term.c:949: warning: passing argument 1 of ‘tgetflag’ discards qualifiers
 from pointer target type
 term.c:950: warning: passing argument 1 of ‘tgetflag’ discards qualifiers
 from pointer target type
 term.c:952: warning: passing argument 1 of ‘tgetflag’ discards qualifiers
 from pointer target type
 term.c:953: warning: passing argument 1 of ‘tgetflag’ discards qualifiers
 from pointer target type
 term.c:955: warning: passing argument 1 of ‘tgetnum’ discards qualifiers
 from pointer target type
 term.c:956: warning: passing argument 1 of ‘tgetnum’ discards qualifiers
 from pointer target type
 make[2]: *** [term.o] Error 1
 make[1]: *** [all-recursive] Error 1
 make: *** [all-recursive] Error 1

 I can't figure out what it means or how to fix it. Help please.

 Peter




-- 
 - michael dykman
 - mdyk...@gmail.com

 May the Source be with you.

Re: A Newbie question about make and the term.c file

[Peter Schrock]

I can understand your point, however, as stated, I am using a ppc
architecture and am not afforded the luxury of binaries in the most up to
date versions. I know using 5.1.59 isn't the most up to date, but I was also
having issues with the most current version. I might start with the most
current binary for ppc and then try upgrading.
About the error, I am sure it is harmless, but I can't get past it, which
means I can't use mysql.

Peter

On Oct 8, 2011, at 7:54 AM, Michael Dykman mdyk...@gmail.com wrote:

I can't help directly with the error message (the warning seems fairly
harmless), but may I inquire why you are building MySQL instead of using one
of the prepared binaries?  Compiling under OS/X can be pretty harrowing.

 - michael dykman

On Sat, Oct 8, 2011 at 2:49 AM, Peter Schrock peter.schr...@gmail.comwrote:

 I am trying to install mysql 5.1.59 on my ppc running os x and I get this
 error message in the term.c file.

 cc1: warnings being treated as errors
 term.c: In function ‘term_set’:
 term.c:946: warning: passing argument 1 of ‘tgetflag’ discards qualifiers
 from pointer target type
 term.c:947: warning: passing argument 1 of ‘tgetflag’ discards qualifiers
 from pointer target type
 term.c:949: warning: passing argument 1 of ‘tgetflag’ discards qualifiers
 from pointer target type
 term.c:950: warning: passing argument 1 of ‘tgetflag’ discards qualifiers
 from pointer target type
 term.c:952: warning: passing argument 1 of ‘tgetflag’ discards qualifiers
 from pointer target type
 term.c:953: warning: passing argument 1 of ‘tgetflag’ discards qualifiers
 from pointer target type
 term.c:955: warning: passing argument 1 of ‘tgetnum’ discards qualifiers
 from pointer target type
 term.c:956: warning: passing argument 1 of ‘tgetnum’ discards qualifiers
 from pointer target type
 make[2]: *** [term.o] Error 1
 make[1]: *** [all-recursive] Error 1
 make: *** [all-recursive] Error 1

 I can't figure out what it means or how to fix it. Help please.

 Peter




-- 
 - michael dykman
 - mdyk...@gmail.com

 May the Source be with you.

Newbie question: Association table and Foreign Key

[Wagyu Beef]

Hey guys,

Am a newbie here and need a little help.

Part of the database consists of two tables events and categories which
look like this

+---+-+
 |  eventID   | eventName |
+---+-+
 |   1   | Event A  |
 |   2   | Event B  |
 |   3   | Event C  |
+---+-+
Primary Key: eventID

+---+-+
 |  categoryID  |   categoryName   |
+---+-+
 |   1   | Category A |
 |   2   | Category B |
 |   3   | Category C |
+---+-+

Primary Key: categoryID


The idea is that an event may have multiple categories and from what I've
read here (http://lists.mysql.com/mysql/171645), many-to-many relationships
in the database should be avoid.  According to the link and a couple of
others I found, I'm supposed to create a separate events_categories table
and make linkages using a Foreign Key.  Am not sure how to translate this to
a SQL query.  Can I get some help.

Thanks a million!

Regards,
Suren

Re: Newbie question: Association table and Foreign Key

[Dhaval Jaiswal]

Just curious as it is not mentioned.  Can Category ID also have multiple 
event id ?



--
Cheers
Dhaval Jaiswal

On 01/03/2011 5:53 PM, Wagyu Beef wrote:

Hey guys,

Am a newbie here and need a little help.

Part of the database consists of two tables events and categories which
look like this

+---+-+
  |  eventID   | eventName |
+---+-+
  |   1   | Event A  |
  |   2   | Event B  |
  |   3   | Event C  |
+---+-+
Primary Key: eventID

+---+-+
  |  categoryID  |   categoryName   |
+---+-+
  |   1   | Category A |
  |   2   | Category B |
  |   3   | Category C |
+---+-+

Primary Key: categoryID


The idea is that an event may have multiple categories and from what I've
read here (http://lists.mysql.com/mysql/171645), many-to-many relationships
in the database should be avoid.  According to the link and a couple of
others I found, I'm supposed to create a separate events_categories table
and make linkages using a Foreign Key.  Am not sure how to translate this to
a SQL query.  Can I get some help.

Thanks a million!

Regards,
Suren

   

font Face='Arial' style='font-size:9pt'This e-mail, and any attachments are 
strictly confidential and may also contain legally privileged information. It is 
intended for the addressee(s) only. If you are not the intended recipient, please do 
not print, copy, store or act in reliance on the e-mail or any of its attachments. 
Instead, please notify the sender immediately and then delete the e-mail and any 
attachments.

Unless expressly stated to the contrary, the views expressed in this e-mail are not 
necessarily the views of Enzen Global Solutions (P) Limited or any of its 
subsidiaries or affiliates (Group Companies), and the Group Companies, their 
directors, officers and employees makes no representation and accept no liability for 
the accuracy or completeness of this e-mail. You are responsible for maintaining your 
own virus protection and the Group Companies do not accept any liability for viruses. 
Enzen reserves the right to monitor and review the content of all messages sent to or 
from this e-mail address. Messages sent to or from this e-mail address may be stored 
on the Enzen e-mail system./font

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Newbie question: Association table and Foreign Key

[Wagyu Beef]

Oh yeah, forgot to mention that.  Yes, one event will have multiple
categories.  And one category can be applicable to multiple events.

On Tue, Mar 1, 2011 at 8:33 PM, Dhaval Jaiswal 
jaiswal.dha...@enzenglobal.com wrote:


 Just curious as it is not mentioned.  Can Category ID also have multiple
 event id ?


 --
 Cheers
 Dhaval Jaiswal


 On 01/03/2011 5:53 PM, Wagyu Beef wrote:

 Hey guys,

 Am a newbie here and need a little help.

 Part of the database consists of two tables events and categories
 which
 look like this

 +---+-+
  |  eventID   | eventName |
 +---+-+
  |   1   | Event A  |
  |   2   | Event B  |
  |   3   | Event C  |
 +---+-+
 Primary Key: eventID

 +---+-+
  |  categoryID  |   categoryName   |
 +---+-+
  |   1   | Category A |
  |   2   | Category B |
  |   3   | Category C |
 +---+-+

 Primary Key: categoryID


 The idea is that an event may have multiple categories and from what I've
 read here (http://lists.mysql.com/mysql/171645), many-to-many
 relationships
 in the database should be avoid.  According to the link and a couple of
 others I found, I'm supposed to create a separate events_categories
 table
 and make linkages using a Foreign Key.  Am not sure how to translate this
 to
 a SQL query.  Can I get some help.

 Thanks a million!

 Regards,
 Suren



 font Face='Arial' style='font-size:9pt'This e-mail, and any attachments
 are strictly confidential and may also contain legally privileged
 information. It is intended for the addressee(s) only. If you are not the
 intended recipient, please do not print, copy, store or act in reliance on
 the e-mail or any of its attachments. Instead, please notify the sender
 immediately and then delete the e-mail and any attachments.

 Unless expressly stated to the contrary, the views expressed in this e-mail
 are not necessarily the views of Enzen Global Solutions (P) Limited or any
 of its subsidiaries or affiliates (Group Companies), and the Group
 Companies, their directors, officers and employees makes no representation
 and accept no liability for the accuracy or completeness of this e-mail. You
 are responsible for maintaining your own virus protection and the Group
 Companies do not accept any liability for viruses. Enzen reserves the right
 to monitor and review the content of all messages sent to or from this
 e-mail address. Messages sent to or from this e-mail address may be stored
 on the Enzen e-mail system./font

Re: Newbie question: Association table and Foreign Key

[S�ndor Hal�sz]

 2011/03/01 20:23 +0800, Wagyu Beef 
Part of the database consists of two tables events and categories which
look like this

+---+-+
 |  eventID   | eventName |
+---+-+
 |   1   | Event A  |
 |   2   | Event B  |
 |   3   | Event C  |
+---+-+
Primary Key: eventID

+---+-+
 |  categoryID  |   categoryName   |
+---+-+
 |   1   | Category A |
 |   2   | Category B |
 |   3   | Category C |
+---+-+

Primary Key: categoryID


The idea is that an event may have multiple categories and from what I've
read here (http://lists.mysql.com/mysql/171645), many-to-many relationships
in the database should be avoid.  According to the link and a couple of
others I found, I'm supposed to create a separate events_categories table
and make linkages using a Foreign Key.  Am not sure how to translate this to
a SQL query. 


Well, if your problem is really like that in the example that you quote, then 
look up 'REFERENCES' under 'CREATE TABLE'. That shows you what to put in the 
common table.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: newbie question database tables

[Patrice Olivier-Wilson]

Thanks all... I got this to work! Much appreciated..

And thanks for patience with a newbie!

--
Patrice Olivier-Wilson
http://biz-comm.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

newbie question database tables

[Patrice Olivier-Wilson]

I'm working on a database that needs to do a few things and getting 
brain freeze on one part.


Scenario:


I want to compile a db of articles with these tables:

Categories
Topics
Users

Categories


cat_ID | cat_name


Topics

top_ID | top_name  | top_content | cat_ID


Users

user_ID | user_name | top_ID
or
user_ID | user_name | top_ID | top_IDb | top_IDc etc

(output to web page using php)

But I need to show which users are using which topics, and I can add 
top_ID to the user file, which is fine if they are only using one topic.


I could add 5 different topic to each user, but then I couldn't expand 
later.


Reverse is true if I add user_ID to the Topics.

So, need an idea how to solve this so it doesn't matter how many new 
users I keep adding, I can still see who is using the topics.


As I said, a newbie question. Thanks much.
--
Patrice Olivier-Wilson
http://biz-comm.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: newbie question database tables

[Jerry Schwartz]

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: je...@gii.co.jp
Web site: www.the-infoshop.com

-Original Message-
From: Patrice Olivier-Wilson [mailto:b...@biz-comm.com]
Sent: Friday, September 10, 2010 12:09 PM
To: mysql@lists.mysql.com
Subject: newbie question database tables

I'm working on a database that needs to do a few things and getting
brain freeze on one part.

Scenario:


I want to compile a db of articles with these tables:

Categories
Topics
Users

Categories


cat_ID | cat_name


Topics

top_ID | top_name  | top_content | cat_ID


Users

user_ID | user_name | top_ID
or
user_ID | user_name | top_ID | top_IDb | top_IDc etc

(output to web page using php)

But I need to show which users are using which topics, and I can add
top_ID to the user file, which is fine if they are only using one topic.

[JS] What you need is another table, users_topic:

users_topics: user_ID | top_ID

and get rid of the top_ID field from the users table.

That's the general technique to use when you need a cross-reference.


I could add 5 different topic to each user, but then I couldn't expand
later.

Reverse is true if I add user_ID to the Topics.

So, need an idea how to solve this so it doesn't matter how many new
users I keep adding, I can still see who is using the topics.

As I said, a newbie question. Thanks much.
--
Patrice Olivier-Wilson
http://biz-comm.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=je...@gii.co.jp





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: newbie question database tables

[Patrice Olivier-Wilson]

On 9/10/10 12:31 PM, Jerry Schwartz wrote:

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: je...@gii.co.jp
Web site: www.the-infoshop.com



Thank you!

--
Patrice Olivier-Wilson
http://biz-comm.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Newbie question: importing cvs settings - followup

[Patrice Olivier-Wilson]

Thanks again for assistance. FYI, I did track this thread down

http://ask.metafilter.com/57007/Missing-commas-in-CSV-file

(exerpt:
Maybe there is a space or something in the 14th column of the first 15 rows.
posted by. on February 14, 2007


It's a bug in Excel (not something you did wrong.)
posted by . February 14, 2007

)


--
Patrice Olivier-Wilson
888-385-7217
http://biz-comm.com


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: Newbie question: importing cvs settings

[Jerry Schwartz]

-Original Message-
From: Patrice Olivier-Wilson [mailto:b...@biz-comm.com]
Sent: Sunday, September 27, 2009 10:19 AM
Cc: 'mysql'
Subject: Re: Newbie question: importing cvs settings

Back again... I have 192 records to import, and tried my extra line at
the end hoping for a work around, but nope, it failed at line 17 again.


Invalid field count in CSV input on line 17.

Anyone have an idea why this might be happening?

[JS] This is just a shot in the dark, but Excel can be rather surprising when 
it puts out a CSV file. Depending upon the data, and exactly how you've 
specified the export, it can put double-quotes in unexpected places.

If you leave out the 17th line of data what happens?

Regards,

Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341

www.the-infoshop.com





Patrice Olivier-Wilson wrote:
 Yep, typo ...:-(

 I did some screen shots of 2 tests. A workaround solution is to make a
 final entry in the csv file that I don't really need. Then everything
 up to that point gets imported ok using CSV method. The LOAD DATA
 method did not enter anything.

 My earlier assumption about line 17 was false. It was dependent on how
 many rows, and I had been using tests with same amount of data.
 As I said, very beginner level, so thanks for the patience.

 screenshots at
 http://biz-comm.com/mysqlprojects/

 thank you







--
Patrice Olivier-Wilson
888-385-7217
http://biz-comm.com


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=jschwa...@the-
infoshop.com





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Newbie question: importing cvs settings

[Patrice Olivier-Wilson]

Jerry Schwartz wrote:

[JS] This is just a shot in the dark, but Excel can be rather surprising when 
it puts out a CSV file. Depending upon the data, and exactly how you've 
specified the export, it can put double-quotes in unexpected places.


If you leave out the 17th line of data what happens?

Regards,

Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341

www.the-infoshop.com


  
  

Thanks Jerry, Gavin and John:

Sorry for not an immediate response to all of your suggestions. Other 
demands were pulling at me since I first asked for assistance.



I opened the .csv file with Text Wrangler, and the commas are missing at 
about line 17



portfolio_ID,portfolio_sort_ID,portfolio_title,portfolio_bodycopy,portfolio_image,portfolio_before
,1,Kitchens,,123.jpg,
,1,Kitchens,,123.jpg,
,1,Kitchens,,123.jpg,
,1,Kitchens,,123.jpg,
,1,Kitchens,,123.jpg,
,1,Kitchens,,123.jpg,
,1,Kitchens,,123.jpg,
,1,Kitchens,,123.jpg,
,1,Kitchens,,123.jpg,
,1,Kitchens,,123.jpg,
,1,Kitchens,,123.jpg,
,1,Kitchens,,123.jpg,
,1,Kitchens,,123.jpg,
,1,Kitchens,,123.jpg,
,1,Kitchens,,123.jpg,
,1,Kitchens,,123.jpg
,1,Kitchens,,123.jpg
,1,Kitchens,,123.jpg

So not sure why that is happening. I'm on a Mac, using Excel 2008. But 
at least you all have helped me find what it is doing, so now, I can at 
least pull into a txt file and make corrections manually.


My thanks to all of you for your help and patience.

(above represents an empty portfolio_ID, a filled in sort_ID, title, 
empty bodycopy, image, empty before)


Thank you.

--
Patrice Olivier-Wilson
888-385-7217
http://biz-comm.com


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: Newbie question: importing cvs settings

[Gavin Towey]

1. Try opening up the csv file in a text editor, viewing it in a spreadsheet 
looks like it's hiding some extra formatting or lines that may be causing 
problems.

2. Try importing through the mysql CLI.  From the screenshot you posted, it 
looks like PMA is parsing the file and creating an insert statement for each 
line.  It may be incorrectly handling some of that data.  Using the CLI you'll 
get better feedback about what, if any, the error is.

Regards,
Gavin Towey

-Original Message-
From: Patrice Olivier-Wilson [mailto:b...@biz-comm.com]
Sent: Saturday, September 26, 2009 11:02 AM
To: 'mysql'
Subject: Re: Newbie question: importing cvs settings

Yep, typo ...:-(

I did some screen shots of 2 tests. A workaround solution is to make a
final entry in the csv file that I don't really need. Then everything up
to that point gets imported ok using CSV method. The LOAD DATA method
did not enter anything.

My earlier assumption about line 17 was false. It was dependent on how
many rows, and I had been using tests with same amount of data.
As I said, very beginner level, so thanks for the patience.

screenshots at
http://biz-comm.com/mysqlprojects/

thank you

John wrote:
 I assume you mean csv not cvs!

 What is the error you get when the import fails? What version of MySQL are
 you using? Can you post the output of SHOW CREATE TABLE for the table you
 are trying to load the file in to and a sample of the csv which is failing
 to load?

 Do you get the same error if you try and load the files using MySQL client
 and the LOAD DATA INFILE command? (See this link for details on how to use
 LOAD DATA INFILE http://dev.mysql.com/doc/refman/5.1/en/load-data.html)

 Regards

 John Daisley
 MySQL  Cognos Contractor

 Certified MySQL 5 Database Administrator (CMDBA)
 Certified MySQL 5 Developer (CMDEV)
 IBM Cognos BI Developer

 Telephone +44 (0)7812 451238
 Email j...@butterflysystems.co.uk

 -Original Message-
 From: Patrice Olivier-Wilson [mailto:b...@biz-comm.com]
 Sent: 26 September 2009 17:08
 To: mysql
 Subject: Newbie question: importing cvs settings

 Greetings:

 I have a project for which need to import cvs files into db.

 I can do so up to a point. The import will only do 16 lines,
 consistently. Error is failing at line 17.

 Steps:

 create table fields in Excel document, where they all match database fields
 enter information in several of the columns, but not all as client will
 be filling it in online (leaving ID blank)
 save excel to .cvs
 log into phpMyAdmin
 import cvs


 I've experimented with several settings in the import, but consistently,
 it fails at line 17, even with different .cvs files.


 Any guidance, most appreciated.




--
Patrice Olivier-Wilson
888-385-7217
http://biz-comm.com


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com


The information contained in this transmission may contain privileged and 
confidential information. It is intended only for the use of the person(s) 
named above. If you are not the intended recipient, you are hereby notified 
that any review, dissemination, distribution or duplication of this 
communication is strictly prohibited. If you are not the intended recipient, 
please contact the sender by reply email and destroy all copies of the original 
message.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Newbie question: importing cvs settings

[Patrice Olivier-Wilson]

Back again... I have 192 records to import, and tried my extra line at 
the end hoping for a work around, but nope, it failed at line 17 again.



Invalid field count in CSV input on line 17.

Anyone have an idea why this might be happening?



Patrice Olivier-Wilson wrote:

Yep, typo ...:-(

I did some screen shots of 2 tests. A workaround solution is to make a 
final entry in the csv file that I don't really need. Then everything 
up to that point gets imported ok using CSV method. The LOAD DATA 
method did not enter anything.


My earlier assumption about line 17 was false. It was dependent on how 
many rows, and I had been using tests with same amount of data.

As I said, very beginner level, so thanks for the patience.

screenshots at
http://biz-comm.com/mysqlprojects/

thank you



 






--
Patrice Olivier-Wilson
888-385-7217
http://biz-comm.com


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: Newbie question: importing cvs settings

[John]

Patrice,

Can you post the output of SHOW CREATE TABLE for the table you are having
difficulty inserting into? Without knowing the table structure its very
difficult to work out why your data load is failing.

Regards

John Daisley
MySQL  Cognos Contractor

Certified MySQL 5 Database Administrator (CMDBA)
Certified MySQL 5 Developer (CMDEV)
IBM Cognos BI Developer

Telephone +44 (0)7812 451238
Email j...@butterflysystems.co.uk


-Original Message-
From: Patrice Olivier-Wilson [mailto:b...@biz-comm.com] 
Sent: 27 September 2009 15:19
Cc: 'mysql'
Subject: Re: Newbie question: importing cvs settings

Back again... I have 192 records to import, and tried my extra line at 
the end hoping for a work around, but nope, it failed at line 17 again.


Invalid field count in CSV input on line 17.

Anyone have an idea why this might be happening?



Patrice Olivier-Wilson wrote:
 Yep, typo ...:-(

 I did some screen shots of 2 tests. A workaround solution is to make a 
 final entry in the csv file that I don't really need. Then everything 
 up to that point gets imported ok using CSV method. The LOAD DATA 
 method did not enter anything.

 My earlier assumption about line 17 was false. It was dependent on how 
 many rows, and I had been using tests with same amount of data.
 As I said, very beginner level, so thanks for the patience.

 screenshots at
 http://biz-comm.com/mysqlprojects/

 thank you


  




-- 
Patrice Olivier-Wilson
888-385-7217
http://biz-comm.com


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=john.dais...@butterflysystems.co.uk

No virus found in this incoming message.
Checked by AVG - www.avg.com 
Version: 8.5.409 / Virus Database: 270.13.113/2396 - Release Date: 09/26/09
05:51:00


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Newbie question: importing cvs settings

[Patrice Olivier-Wilson]

Greetings:

I have a project for which need to import cvs files into db.

I can do so up to a point. The import will only do 16 lines, 
consistently. Error is failing at line 17.


Steps:

create table fields in Excel document, where they all match database fields
enter information in several of the columns, but not all as client will 
be filling it in online (leaving ID blank)

save excel to .cvs
log into phpMyAdmin
import cvs


I've experimented with several settings in the import, but consistently, 
it fails at line 17, even with different .cvs files.



Any guidance, most appreciated.

--
Patrice Olivier-Wilson
888-385-7217
http://biz-comm.com


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: Newbie question: importing cvs settings

[John]

I assume you mean csv not cvs!

What is the error you get when the import fails? What version of MySQL are
you using? Can you post the output of SHOW CREATE TABLE for the table you
are trying to load the file in to and a sample of the csv which is failing
to load?

Do you get the same error if you try and load the files using MySQL client
and the LOAD DATA INFILE command? (See this link for details on how to use
LOAD DATA INFILE http://dev.mysql.com/doc/refman/5.1/en/load-data.html)

Regards

John Daisley
MySQL  Cognos Contractor

Certified MySQL 5 Database Administrator (CMDBA)
Certified MySQL 5 Developer (CMDEV)
IBM Cognos BI Developer

Telephone +44 (0)7812 451238
Email j...@butterflysystems.co.uk

-Original Message-
From: Patrice Olivier-Wilson [mailto:b...@biz-comm.com] 
Sent: 26 September 2009 17:08
To: mysql
Subject: Newbie question: importing cvs settings

Greetings:

I have a project for which need to import cvs files into db.

I can do so up to a point. The import will only do 16 lines, 
consistently. Error is failing at line 17.

Steps:

create table fields in Excel document, where they all match database fields
enter information in several of the columns, but not all as client will 
be filling it in online (leaving ID blank)
save excel to .cvs
log into phpMyAdmin
import cvs


I've experimented with several settings in the import, but consistently, 
it fails at line 17, even with different .cvs files.


Any guidance, most appreciated.

-- 
Patrice Olivier-Wilson
888-385-7217
http://biz-comm.com


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=john.dais...@butterflysystems.co.uk

No virus found in this incoming message.
Checked by AVG - www.avg.com 
Version: 8.5.409 / Virus Database: 270.13.113/2396 - Release Date: 09/26/09
05:51:00


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Newbie question: importing cvs settings

[Patrice Olivier-Wilson]

Yep, typo ...:-(

I did some screen shots of 2 tests. A workaround solution is to make a 
final entry in the csv file that I don't really need. Then everything up 
to that point gets imported ok using CSV method. The LOAD DATA method 
did not enter anything.


My earlier assumption about line 17 was false. It was dependent on how 
many rows, and I had been using tests with same amount of data.

As I said, very beginner level, so thanks for the patience.

screenshots at
http://biz-comm.com/mysqlprojects/

thank you

John wrote:

I assume you mean csv not cvs!

What is the error you get when the import fails? What version of MySQL are
you using? Can you post the output of SHOW CREATE TABLE for the table you
are trying to load the file in to and a sample of the csv which is failing
to load?

Do you get the same error if you try and load the files using MySQL client
and the LOAD DATA INFILE command? (See this link for details on how to use
LOAD DATA INFILE http://dev.mysql.com/doc/refman/5.1/en/load-data.html)

Regards

John Daisley
MySQL  Cognos Contractor

Certified MySQL 5 Database Administrator (CMDBA)
Certified MySQL 5 Developer (CMDEV)
IBM Cognos BI Developer

Telephone +44 (0)7812 451238
Email j...@butterflysystems.co.uk

-Original Message-
From: Patrice Olivier-Wilson [mailto:b...@biz-comm.com] 
Sent: 26 September 2009 17:08

To: mysql
Subject: Newbie question: importing cvs settings

Greetings:

I have a project for which need to import cvs files into db.

I can do so up to a point. The import will only do 16 lines, 
consistently. Error is failing at line 17.


Steps:

create table fields in Excel document, where they all match database fields
enter information in several of the columns, but not all as client will 
be filling it in online (leaving ID blank)

save excel to .cvs
log into phpMyAdmin
import cvs


I've experimented with several settings in the import, but consistently, 
it fails at line 17, even with different .cvs files.



Any guidance, most appreciated.

  



--
Patrice Olivier-Wilson
888-385-7217
http://biz-comm.com


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Newbie Question - MySQL Administrator

[Walter Heck]

The online help for mysql administrator is here:
http://dev.mysql.com/doc/administrator/en/index.html

OlinData: Professional services for MySQL
Support * Consulting * Administration
http://www.olindata.com



On Fri, Feb 20, 2009 at 7:17 AM, Jeff Murdock jeff_murd...@yahoo.com wrote:
 This is on a Mac OS X (v10.5.6) system in case that matters.



 1. - MySQL Administrator Help button says:

 HELP

 Help isn't available for MySQL Administrator.


 Really, no help or did I screw-up the install somehow?



 2. - I tried to create my first Table in MySQL Administrator but got this
 message:

 ERROR

 Error executing SQL commands to create table.
 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 'DEFAULT NULL,
  `LastName` VARCHAR DEFAULT NULL,
  `Street1` VARCHAR DEFAULT NUL' at line 3 (error 1064)


 Trying to Execute this:


 CREATE TABLE `test`.`AddressBook` (
  `RecNo` INT NOT NULL AUTO_INCREMENT,
  `FirstName` VARCHAR DEFAULT NULL,
  `LastName` VARCHAR DEFAULT NULL,
  `Street1` VARCHAR DEFAULT NULL,
  `Street2` VARCHAR DEFAULT NULL,
  `City` VARCHAR DEFAULT NULL,
  `State` VARCHAR DEFAULT NULL,
  `Zip` VARCHAR DEFAULT NULL,
  `HomePhone` VARCHAR DEFAULT NULL,
  `CellPhone` VARCHAR DEFAULT NULL,
  PRIMARY KEY (`RecNo`)
 )
 CHARACTER SET utf8
 COMMENT = 'Sample';







 Jeff






-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Newbie Question - MySQL Administrator

[Jeff Murdock]

This is on a Mac OS X (v10.5.6) system in case that matters.



1. - MySQL Administrator Help button says:

HELP

Help isn’t available for MySQL Administrator.


Really, no help or did I screw-up the install somehow?



2. - I tried to create my first Table in MySQL Administrator but got  
this message:


ERROR

Error executing SQL commands to create table.
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 'DEFAULT NULL,

  `LastName` VARCHAR DEFAULT NULL,
  `Street1` VARCHAR DEFAULT NUL' at line 3 (error 1064)


Trying to Execute this:


CREATE TABLE `test`.`AddressBook` (
  `RecNo` INT NOT NULL AUTO_INCREMENT,
  `FirstName` VARCHAR DEFAULT NULL,
  `LastName` VARCHAR DEFAULT NULL,
  `Street1` VARCHAR DEFAULT NULL,
  `Street2` VARCHAR DEFAULT NULL,
  `City` VARCHAR DEFAULT NULL,
  `State` VARCHAR DEFAULT NULL,
  `Zip` VARCHAR DEFAULT NULL,
  `HomePhone` VARCHAR DEFAULT NULL,
  `CellPhone` VARCHAR DEFAULT NULL,
  PRIMARY KEY (`RecNo`)
)
CHARACTER SET utf8
COMMENT = 'Sample';







Jeff

Re: Newbie Question - MySQL Administrator

[Darryle Steplight]

Jeff,
 For starters, it looks like you need a value for VARCHAR. Try the
same statement but with VARCHAR(255) .

On Fri, Feb 20, 2009 at 1:17 AM, Jeff Murdock jeff_murd...@yahoo.com wrote:
 This is on a Mac OS X (v10.5.6) system in case that matters.



 1. - MySQL Administrator Help button says:

 HELP

 Help isn't available for MySQL Administrator.


 Really, no help or did I screw-up the install somehow?



 2. - I tried to create my first Table in MySQL Administrator but got this
 message:

 ERROR

 Error executing SQL commands to create table.
 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 'DEFAULT NULL,
  `LastName` VARCHAR DEFAULT NULL,
  `Street1` VARCHAR DEFAULT NUL' at line 3 (error 1064)


 Trying to Execute this:


 CREATE TABLE `test`.`AddressBook` (
  `RecNo` INT NOT NULL AUTO_INCREMENT,
  `FirstName` VARCHAR DEFAULT NULL,
  `LastName` VARCHAR DEFAULT NULL,
  `Street1` VARCHAR DEFAULT NULL,
  `Street2` VARCHAR DEFAULT NULL,
  `City` VARCHAR DEFAULT NULL,
  `State` VARCHAR DEFAULT NULL,
  `Zip` VARCHAR DEFAULT NULL,
  `HomePhone` VARCHAR DEFAULT NULL,
  `CellPhone` VARCHAR DEFAULT NULL,
  PRIMARY KEY (`RecNo`)
 )
 CHARACTER SET utf8
 COMMENT = 'Sample';







 Jeff






-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Newbie question, how do I respond to post?

[Kc9cdt]

Hello,
I feel stupid here... How do I respond to a specific post?
I posted last night, I got a response... now I want to post a response to the 
response.
Don't see a way to do that...
Thanks,
Lee


**
Start the year off right.  Easy ways to stay in shape.
 
http://body.aol.com/fitness/winter-exercise?NCID=aolcmp0030002489

RE: Newbie question, how do I respond to post?

[David Ruggles]

Either use reply to all, or manually enter the list email address in the to
field.

There are two general feelings about list operation:
1) all replies should go to the list
2) all replies should default to the sender

This list is configured as #2 (I prefer #1 myself)

Thanks,

David Ruggles
CCNA MCSE (NT) CNA A+
Network EngineerSafe Data, Inc.
(910) 285-7200  [EMAIL PROTECTED]



-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, January 23, 2008 10:19 AM
To: mysql@lists.mysql.com
Subject: Newbie question, how do I respond to post?


Hello,
I feel stupid here... How do I respond to a specific post?
I posted last night, I got a response... now I want to post a response to
the 
response.
Don't see a way to do that...
Thanks,
Lee


**
Start the year off right.  Easy ways to stay in shape.
 
http://body.aol.com/fitness/winter-exercise?NCID=aolcmp0030002489



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Newbie Question connecting with windows named pipes (resolved)

[John Comerford]

Thanks Sebastian, that did the trick. 


The full command I use is:
 $mysqli = new mysqli(., $username,$password, 
$database,null,/tmp/mysql.sock);


Regards,
JC

Sebastian Mendel wrote:

John Comerford schrieb:
  

Hi Folks,

I have a database running on Window XP, that I want to disable network
connections to and enable 'named pipes'.  I am running MySQL 5.0.27  
and my.ini looks like...


[...]

I can connect to the DB using the GUI tools if I set my pipe name to
'/tmp/mysql.sock' using the login dialog box.
[...]



mysql_connect('.', ...);

you have to a dot as host: '.'



  

Re: Newbie Question connecting with windows named pipes (resolved)

[Sebastian Mendel]

John Comerford schrieb:
 Thanks Sebastian, that did the trick.
 The full command I use is:
  $mysqli = new mysqli(., $username,$password,
 $database,null,/tmp/mysql.sock);

there is no need for the socket, their are no sockets on windows

$mysqli = new mysqli('.', $username, $password, $database);



-- 
Sebastian Mendel

www.sebastianmendel.de

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Newbie Question connecting with windows named pipes (resolved)

[John Comerford]

Hi Sebastian,

I did read somewhere that sockets was not valid for windows, and  
'/tmp/mysql.sock' doesn't resolve to anything on my machine.  But I have 
tried the following commands:


$mysqli = new mysqli(., $username,$password, $database);
or
$mysqli = new mysqli(., $username,$password, $database,null);

and I get the following error:

Can't open named pipe to host: . pipe: MySQL (2)

but when I include the /tmp/mysql.sock parameter it works fine.  I was 
thinking that maybe I have things configured in such a way that MySQL is 
using /tmp/mysql.sock as the pipe name.  I am going to do some testing 
this evening to see if my suspicion is correct.


Thanks again,
 John

Sebastian Mendel wrote:

John Comerford schrieb:
  

Thanks Sebastian, that did the trick.
The full command I use is:
 $mysqli = new mysqli(., $username,$password,
$database,null,/tmp/mysql.sock);



there is no need for the socket, their are no sockets on windows

$mysqli = new mysqli('.', $username, $password, $database);



  

Re: Newbie Question connecting with windows named pipes (resolved)

[Sebastian Mendel]

John Comerford schrieb:
 Hi Sebastian,
 
 I did read somewhere that sockets was not valid for windows, and 
 '/tmp/mysql.sock' doesn't resolve to anything on my machine.  But I have
 tried the following commands:
 
 $mysqli = new mysqli(., $username,$password, $database);
 or
 $mysqli = new mysqli(., $username,$password, $database,null);
 
 and I get the following error:
 
 Can't open named pipe to host: . pipe: MySQL (2)

yes, 'MySQL' is default name of the pipe, used by the libaray


 but when I include the /tmp/mysql.sock parameter it works fine.  I was
 thinking that maybe I have things configured in such a way that MySQL is
 using /tmp/mysql.sock as the pipe name.  I am going to do some testing
 this evening to see if my suspicion is correct.

yes:

 Note: you can specify a pipe name on the advanced network page, if required. 


possible this is et with the soccket paramter in my.cnf

 [mysqld]
 #port= 3306
 socket= /tmp/mysql.sock 


-- 
Sebastian Mendel

www.sebastianmendel.de

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Newbie Question connecting with windows named pipes (resolved)

[John Comerford]

Hi Sebastian,

That was it alright.  I commented out the socket lines from  mysql.ini 
(restarted it) and changed my php command to:


$mysqli = new mysqli(., $username,$password, $database);

and all seems to be working fine now.

Thanks again for your help, much appreciated.

Regards,
 John


Sebastian Mendel wrote:

John Comerford schrieb:
  

Hi Sebastian,

I did read somewhere that sockets was not valid for windows, and 
'/tmp/mysql.sock' doesn't resolve to anything on my machine.  But I have

tried the following commands:

$mysqli = new mysqli(., $username,$password, $database);
or
$mysqli = new mysqli(., $username,$password, $database,null);

and I get the following error:

Can't open named pipe to host: . pipe: MySQL (2)



yes, 'MySQL' is default name of the pipe, used by the libaray


  

but when I include the /tmp/mysql.sock parameter it works fine.  I was
thinking that maybe I have things configured in such a way that MySQL is
using /tmp/mysql.sock as the pipe name.  I am going to do some testing
this evening to see if my suspicion is correct.



yes:

  
Note: you can specify a pipe name on the advanced network page, if required. 




possible this is et with the soccket paramter in my.cnf

  

[mysqld]
#port= 3306
socket= /tmp/mysql.sock 




  



--

1^st Floor, 184 -186 Glenferrie Road, Malvern VIC 3144

PH:*(03) 9500 1466*
FX :*(03) 9500 1469*
Email: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED]
Web:  www.styleman.com.au http://www.styleman.com.au

The information in this e-mail is confidential and is intended solely 
for the addressee. Any views or opinions presented are solely those of 
the author and do not necessarily represent those of Option Systems Pty 
Ltd. If you are not the intended recipient, please delete this message 
and contact the sender.

Newbie Question connecting with windows named pipes

[John Comerford]

Hi Folks,


I have a database running on Window XP, that I want to disable network 
connections to and enable 'named pipes'.  I am running MySQL 5.0.27   
and my.ini looks like...


[client]
#password= your_password
port= 3306
socket= /tmp/mysql.sock

[mysqld]
#port= 3306
socket= /tmp/mysql.sock
#Allow connections via named pipes (Windows NT+ only). Note: you can 
specify a pipe name on the advanced network page, if required.

enable-named-pipe
#Don't allow connections via TCP/IP.
skip-networking



I can connect to the DB using the GUI tools if I set my pipe name to 
'/tmp/mysql.sock' using the login dialog box.
However when I try and connect using PHP I get an error.  I have tried 
several variants of the connect command and I get various errors but all 
are along the lines of:

Unknown MySQL server host '/tmp/mysql.sock' (11004)  or
Can't connect to MySQL server on 'localhost' (10061)

I have tried
$mysqli = new mysqli(null, $username,$password, $database);
$mysqli = new mysqli(localhost:/tmp/mysql.sock, $username,$password, 
$database);
$mysqli = new mysqli(localhost, $username,$password, 
$database,3306,/tmp/mysql.sock);
$mysqli = new mysqli(localhost, $username,$password, 
$database,/tmp/mysql.sock);

$mysqli = new mysqli(/tmp/mysql.sock, $username,$password, $database);

I have also tried the above commands using mysqli_connect ?  I have done 
a few searches of the web but seem to always come up with something like 
the above?  Anybody have any ideas why it won't connect in PHP ?


I know this is not a PHP forum, but I am wondering if I have something 
wrong in my MySQL setup ?


TIA,
 JC

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Newbie Question connecting with windows named pipes

[Sebastian Mendel]

John Comerford schrieb:
 Hi Folks,
 
 I have a database running on Window XP, that I want to disable network
 connections to and enable 'named pipes'.  I am running MySQL 5.0.27  
 and my.ini looks like...
 
 [...]
 
 I can connect to the DB using the GUI tools if I set my pipe name to
 '/tmp/mysql.sock' using the login dialog box.
 [...]

mysql_connect('.', ...);

you have to a dot as host: '.'



-- 
Sebastian Mendel

www.sebastianmendel.de

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Importing CSV file into MySQL DB - Newbie Question

[sheeri kritzer]

Hi Derek,

You never gave us a SHOW CREATE TABLE simple1, which would have
helped.  To replicate your problem, I did the following, on a linux
box (it looks like you're using Windows), using mysql
5.0.18-standard-log:

CREATE DATABASE cars;

use cars;

CREATE TABLE `simple1` (
  `one` char(10) default NULL,
  `two` char(10) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

and then made a text file like your simple1.csv and ran the following:

mysqlimport  --lines-terminated-by=\n --fields-terminated-by=, --local
--user=root --password=rootpass cars simple1.csv

and got:

cars.simple1: Records: 1  Deleted: 0  Skipped: 0  Warnings: 1

I'd guess I got a warning and you didn't because of the version of
MySQL.  when I checked out the table, indeed I found that I had only
one row.

So I tried again, figuring that the binary knew what the end of the line was:

mysqlimport --fields-terminated-by=, --local --user=root
--password=rootpass cars simple1.csv
cars.simple1: Records: 3  Deleted: 0  Skipped: 0  Warnings: 1

aha!  3 records this time!

select * from simple1;
+--+-+
| one  | two |
+--+-+
| test1  | test2 |
| test11 | test3 |
|  | NULL|
+--+-+
3 rows in set (0.00 sec)

I can guess that I got a warning because there was no comma-separated
list on the 3rd line, so it put the first value (blank) into the first
field of the 3rd record, but had nothing to put in the 2nd value, so
it put NULL.

You don't need double quotes in the file.  However, if you are
importing someone else's file or a previous export, you can put a
--fields-enclosed-by='' (that is, single-quote double-quote
single-quote) tag to tell mysqlimport that it shouldn't look at the
double quotes.

hope this helps!

Sheeri

On 3/5/06, Derek Doerr [EMAIL PROTECTED] wrote:
 I have a CSV file that I want to import into a MySQL DB table. The file
 contains 15 fields.  The able to import into will contain those same 15
 fields, plus an auto-generated Primary Key fields.

 Since this is the first time I'm working with mysqlimport, I created a
 small test table to start with - simple1, containing two varchar
 fields - field1 and field2.

 I'm trying to import a small test file into simple1, to get the hang of
 using mysqlimport.  The test file contains 2 records and 3 lines - the
 3rd line is blank:

 test1,test2
 test11,test3

 I run the import as follows:
 C:\Program Files\xampp\mysql\binmysqlimport.exe
 --lines-terminated-by=\r --fields-terminated-by=, --local --user=root
 cars c:\dev\test\simple1.csv

 The import report shows:   Records: 1  Deleted: 0  Skipped: 0 Warnings: 0

 The data that ends up in the DB, however, only contains 1 record:
 \test1\,\test2\
 \test11\

 (1) how do I get mysqlimport to import both records, properly parsing
 the fields - two fields per record?
 (2) do I need to wrap the imported records in double-quotes?  Why do the
 double-quotes show up in the MySQL DB table?

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

Importing CSV file into MySQL DB - Newbie Question

[Derek Doerr]

I have a CSV file that I want to import into a MySQL DB table. The file 
contains 15 fields.  The able to import into will contain those same 15 
fields, plus an auto-generated Primary Key fields.


Since this is the first time I'm working with mysqlimport, I created a 
small test table to start with - simple1, containing two varchar 
fields - field1 and field2.


I'm trying to import a small test file into simple1, to get the hang of 
using mysqlimport.  The test file contains 2 records and 3 lines - the 
3rd line is blank:


test1,test2
test11,test3

I run the import as follows:
C:\Program Files\xampp\mysql\binmysqlimport.exe
--lines-terminated-by=\r --fields-terminated-by=, --local --user=root 
cars c:\dev\test\simple1.csv


The import report shows:   Records: 1  Deleted: 0  Skipped: 0 Warnings: 0

The data that ends up in the DB, however, only contains 1 record:
\test1\,\test2\
\test11\

(1) how do I get mysqlimport to import both records, properly parsing 
the fields - two fields per record?
(2) do I need to wrap the imported records in double-quotes?  Why do the 
double-quotes show up in the MySQL DB table?


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Hi, newbie question on a select statement

[sheeri kritzer]

Perhaps you were using CAST() incorrectly?  What was your attempt?

my test table:

show create table ultimas_repuestas;
+---+-+
| Table | Create Table
   |
+---+-+
| ultimas_repuestas | CREATE TABLE `ultimas_repuestas` (
  `valorSNMP` varchar(3) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+---+-+
1 row in set (0.00 sec)

then I put some values in it:

mysql select * from ultimas_repuestas order by valorSNMP;
+---+
| valorSNMP |
+---+
| 1 |
| 10|
| 11|
| 12|
| 13|
| 14|
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
+---+
14 rows in set (0.02 sec)

Then I tried a CAST() statement:

mysql select * from ultimas_repuestas order by CAST(valorSNMP as
SIGNED INTEGER);
+---+
| valorSNMP |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10|
| 11|
| 12|
| 13|
| 14|
+---+
14 rows in set (0.00 sec)

works just fine in MySQL 5.0.

not that you gave the version #.although this works fine, too:

mysql select * from ultimas_repuestas order by valorSNMP+0;
+---+
| valorSNMP |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10|
| 11|
| 12|
| 13|
| 14|
+---+
14 rows in set (0.00 sec)

Hope this helps!  Next time, instead of saying this didn't work give
the example, and what you got back (what does 'doesn't work' mean? 
did you get an error?  Or it didn't return things in the right order?)

-Sheeri

On 2/16/06, Ariel Sánchez Mora [EMAIL PROTECTED] wrote:
 I searched the other lists and couldn't find one that was more appropiate for 
 this question; if there is, please tell me so :)

 I am monitoring networking equipment and so far I'm saving all my data in a 
 MySQL database (hence an email to this list). I'm using only VARCHARs because 
 the SNMP agent returns only strings and I didn't find enough a reason for 
 converting the different types of answers, since most string comparations are 
 donde correctly; I received both text and numbers from the monitoring tasks 
 and wanted to keep it simple.

 However when I execute this select statement I am getting this problem:

 select info_oficina,valorSNMP from ultimas_respuestas_snmp where 
 columna_donde_guardar='USO_CPU_1min' order by valorSNMP desc limit 10;

 info_oficina valorSNMP
 CSF Desamparados error
 Periferica Palmares  4
 CSF San Pedro4
 Sucursal Guapiles4
 Periferica Pentagono San Pablo   30
 Periferica Tibas 3
 Periferica Buenos Aires  3
 Sucursal Turrialba   3
 Ventanilla Florencia 3
 CSF del Sur (Ciudad Neilly)  3

 Where info_oficina, valorSNMP are columns, ultimas_respuestas_snmp is my 
 table, etc.

 The summary question is:

 given a column with the numbers 0 to 99 of type varchar, how can i tell MySQL 
 to order it as if they were integer values, so that instead of

 99,98,97,96,95,94,93,92,91,90,9,89

 I'd have

 99,98,97,96,95,94,93,92,91,90,89,88


  Apparently, CAST() does not have an effect (but feel free to prove me wrong)

 Thanks!

 Ariel



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Hi, newbie question on a select statement

[Ariel Sánchez Mora]

I searched the other lists and couldn't find one that was more appropiate for 
this question; if there is, please tell me so :)
 
I am monitoring networking equipment and so far I'm saving all my data in a 
MySQL database (hence an email to this list). I'm using only VARCHARs because 
the SNMP agent returns only strings and I didn't find enough a reason for 
converting the different types of answers, since most string comparations are 
donde correctly; I received both text and numbers from the monitoring tasks and 
wanted to keep it simple.
 
However when I execute this select statement I am getting this problem:
 
select info_oficina,valorSNMP from ultimas_respuestas_snmp where 
columna_donde_guardar='USO_CPU_1min' order by valorSNMP desc limit 10;
 
info_oficina valorSNMP  
CSF Desamparados error  
Periferica Palmares  4  
CSF San Pedro4  
Sucursal Guapiles4  
Periferica Pentagono San Pablo   30 
Periferica Tibas 3  
Periferica Buenos Aires  3  
Sucursal Turrialba   3  
Ventanilla Florencia 3  
CSF del Sur (Ciudad Neilly)  3  
 
Where info_oficina, valorSNMP are columns, ultimas_respuestas_snmp is my table, 
etc.
 
The summary question is:
 
given a column with the numbers 0 to 99 of type varchar, how can i tell MySQL 
to order it as if they were integer values, so that instead of
 
99,98,97,96,95,94,93,92,91,90,9,89
 
I'd have
 
99,98,97,96,95,94,93,92,91,90,89,88
 
 
 Apparently, CAST() does not have an effect (but feel free to prove me wrong)
 
Thanks!
 
Ariel

RE: Hi, newbie question on a select statement

[Logan, David (SST - Adelaide)]

Hi Ariel,

It works fine for me

localhost.testshow create table mytest\G
*** 1. row ***
   Table: mytest
Create Table: CREATE TABLE `mytest` (
  `id` int(11) NOT NULL auto_increment,
  `test_col` varchar(10) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

5.0.18-max on port mysql.sock as [EMAIL PROTECTED]
localhost.test

5.0.18-max on port mysql.sock as [EMAIL PROTECTED]
localhost.testselect * from mytest order by test_col;
++--+
| id | test_col |
++--+
|  1 | 1|
| 10 | 10   |
| 12 | 11   |
|  2 | 2|
| 11 | 20   |
|  3 | 3|
|  4 | 4|
|  5 | 5|
|  6 | 6|
|  7 | 7|
|  8 | 8|
|  9 | 9|
++--+
12 rows in set (0.01 sec)

5.0.18-max on port mysql.sock as [EMAIL PROTECTED]
localhost.testselect * from mytest order by CAST(test_col as unsigned);
++--+
| id | test_col |
++--+
|  1 | 1|
|  2 | 2|
|  3 | 3|
|  4 | 4|
|  5 | 5|
|  6 | 6|
|  7 | 7|
|  8 | 8|
|  9 | 9|
| 10 | 10   |
| 12 | 11   |
| 11 | 20   |
++--+
12 rows in set (0.00 sec)

Regards


---
** _/ **  David Logan 
***   _/ ***  ITO Delivery Specialist - Database
*_/*  Hewlett-Packard Australia Ltd
_/_/_/  _/_/_/    E-Mail: [EMAIL PROTECTED]
   _/  _/  _/  _/     Desk:   +618 8408 4273
  _/  _/  _/_/_/  Mobile: 0417 268 665
*_/   **
**  _/    Postal: 148 Frome Street,
   _/ **  Adelaide SA 5001
  Australia 
invent   
---

-Original Message-
From: Ariel Sánchez Mora [mailto:[EMAIL PROTECTED] 
Sent: Friday, 17 February 2006 11:29 AM
To: mysql@lists.mysql.com
Subject: Hi, newbie question on a select statement

I searched the other lists and couldn't find one that was more appropiate for 
this question; if there is, please tell me so :)
 
I am monitoring networking equipment and so far I'm saving all my data in a 
MySQL database (hence an email to this list). I'm using only VARCHARs because 
the SNMP agent returns only strings and I didn't find enough a reason for 
converting the different types of answers, since most string comparations are 
donde correctly; I received both text and numbers from the monitoring tasks and 
wanted to keep it simple.
 
However when I execute this select statement I am getting this problem:
 
select info_oficina,valorSNMP from ultimas_respuestas_snmp where 
columna_donde_guardar='USO_CPU_1min' order by valorSNMP desc limit 10;
 
info_oficina valorSNMP  
CSF Desamparados error  
Periferica Palmares  4  
CSF San Pedro4  
Sucursal Guapiles4  
Periferica Pentagono San Pablo   30 
Periferica Tibas 3  
Periferica Buenos Aires  3  
Sucursal Turrialba   3  
Ventanilla Florencia 3  
CSF del Sur (Ciudad Neilly)  3  
 
Where info_oficina, valorSNMP are columns, ultimas_respuestas_snmp is my table, 
etc.
 
The summary question is:
 
given a column with the numbers 0 to 99 of type varchar, how can i tell MySQL 
to order it as if they were integer values, so that instead of
 
99,98,97,96,95,94,93,92,91,90,9,89
 
I'd have
 
99,98,97,96,95,94,93,92,91,90,89,88
 
 
 Apparently, CAST() does not have an effect (but feel free to prove me wrong)
 
Thanks!
 
Ariel

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Query Complexity Limit Question [Newbie Question]

[David T. Ashley]

Hi,

I have several tables linked in various ways so that an inner join is
possible.  However, at the same time and in the same SQL query, I'd also
like to query by some field values in one of the tables.

Two quick questions:

a)Will MySQL allow joins that involve more than two tables (in my case,
perhaps as many as 5)?

b)Can limits on a key field be included in the join in the same SQL
statement as does the join, i.e. ... WHERE N3 AND N20 ... or something
like that.

Thanks, Dave.

---
P.S.--It might seem that I'm a lazy bum and unwilling to just try it.  My
situation is that I'm working on design documentation for the database and
working through in my head whether everything can be done in approximately
O(N) time.  The book I have doesn't mention joins on more than two tables.
 I will get the database set up and try it soon.  Thanks for your
patience.

---
David T. Ashley ([EMAIL PROTECTED])
Thousand Feet Consulting, LLC


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query Complexity Limit Question [Newbie Question]

[nigel wood]

David T. Ashley wrote:


Hi,

I have several tables linked in various ways so that an inner join is
possible.  However, at the same time and in the same SQL query, I'd also
like to query by some field values in one of the tables.

Two quick questions:

a)Will MySQL allow joins that involve more than two tables (in my case,
perhaps as many as 5)?

 

Yes. The join limit is 128 tables but if you hit it  you've just done it 
wrong.



b)Can limits on a key field be included in the join in the same SQL
statement as does the join, i.e. ... WHERE N3 AND N20 ... or something
like that.

 


Yes. Though not everyone considers it good practice. As an example:

SELECT p.name, c.name
FROM parent p
INNER JOIN child c ON c.parent_id=p.id AND c.age  18
WHERE  p.sex in ('male','unspecified');

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query Complexity Limit Question [Newbie Question]

[Alec . Cawley]

David T. Ashley [EMAIL PROTECTED] wrote on 07/02/2006 14:03:04:

 Hi,
 
 I have several tables linked in various ways so that an inner join is
 possible.  However, at the same time and in the same SQL query, I'd also
 like to query by some field values in one of the tables.
 
 Two quick questions:
 
 a)Will MySQL allow joins that involve more than two tables (in my case,
 perhaps as many as 5)?
 
 b)Can limits on a key field be included in the join in the same SQL
 statement as does the join, i.e. ... WHERE N3 AND N20 ... or something
 like that.

Yes, you can do multi-way joins, and people often do. My biggest is 3-way, 
but some people do at least 5-way. Beware that it is easy to specify 
operations which will heavily load the system if you are not careful.

The constraints in the WHERE statement are *logically* and syntactically 
done on the huge table produced by the joins. However, the MySQL optimiser 
is not stupid and will perform the filter upstream of the JOIN where 
possible. Some experimentation and use of the EXPLAIN statement may be 
necessary to find the best ordering for queries.

Alec




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query Complexity Limit Question [Newbie Question]

[David T. Ashley]

On Tue, February 7, 2006 10:46 am, [EMAIL PROTECTED] wrote:
 David T. Ashley [EMAIL PROTECTED] wrote on 07/02/2006 14:03:04:

 a)Will MySQL allow joins that involve more than two tables (in my case,
 perhaps as many as 5)?

 b)Can limits on a key field be included in the join in the same SQL
 statement as does the join, i.e. ... WHERE N3 AND N20 ... or something
 like that.

 Yes, you can do multi-way joins, and people often do. My biggest is 3-way,
 but some people do at least 5-way. Beware that it is easy to specify
 operations which will heavily load the system if you are not careful.

 The constraints in the WHERE statement are *logically* and syntactically
 done on the huge table produced by the joins. However, the MySQL optimiser
 is not stupid and will perform the filter upstream of the JOIN where
 possible. Some experimentation and use of the EXPLAIN statement may be
 necessary to find the best ordering for queries.

My thought process was that if all the fields involved--both in the JOIN
and limits on any fields--were key fields, then the operation should be
approximately O(log N).

But, now that I think about it:

a)A simple limit operation or search operation on a KEY field should be
O(log N) (i.e. doesn't MySQL build index tables or something on key fields
to get approximately that behavior?), BUT

b)I think you are right about the caution ... just thinking about it, it
isn't clear that a JOIN will be O(log N) when the only condition is
something like (table1.a = table2.b).  It seems that a limiting condition
(sex=MALE or datebirthday1 and datebirthday2 or whatever) would have to
be applied first to a key field (by the optimizer?) or else the JOIN would
be something like O(N) or maybe even worse.

Thanks for pointing out the EXPLAIN keyword.  That may be helpful.

I'd be curious on any perspective ... what computational complexity is an
inner join with no other conditions (i.e. the only condition is table1.a =
table2.b)?  I'd guess it isn't O(log N).

Well that concludes my newbie questions.  Thanks for the help.  I'll now
begin using MySQL ...
---
David T. Ashley ([EMAIL PROTECTED])
Thousand Feet Consulting, LLC


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query Complexity Limit Question [Newbie Question]

[James Harvard]

At 3:46 pm + 7/2/06, [EMAIL PROTECTED] wrote:
Yes, you can do multi-way joins, and people often do. My biggest is 3-way, but 
some people do at least 5-way.

My record is and 8-way join (7 tables, one twice). So there! ;-)

Beware that it is easy to specify operations which will heavily load the 
system if you are not careful.

Yes indeed. You need to think carefully about what indices you need on your 
tables. As has already been said, EXPLAIN SELECT is your friend!

James Harvard

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

AUTOINCREMENT / UNIQUE Behavior [Newbie Question]

[David T. Ashley]

I remember in MySQL that you can define an integer table field as
AUTOINCREMENT and UNIQUE (I might have the specific keywords wrong, but
everyone will know what I mean).

In the life of a database where there are frequent additions and
deletions, 2^32 isn't that large of a number.

When the integer field reaches 2^32-1 or whatever the upper limit is, what
happens then?  Will it try to reuse available values from records that
have been deleted?  Or is it always an error?

Thanks, Dave.
---
David T. Ashley ([EMAIL PROTECTED])
Thousand Feet Consulting, LLC


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: AUTOINCREMENT / UNIQUE Behavior [Newbie Question]

[Dan Nelson]

In the last episode (Feb 06), David T. Ashley said:
 I remember in MySQL that you can define an integer table field as
 AUTOINCREMENT and UNIQUE (I might have the specific keywords wrong,
 but everyone will know what I mean).
 
 In the life of a database where there are frequent additions and
 deletions, 2^32 isn't that large of a number.
 
 When the integer field reaches 2^32-1 or whatever the upper limit is,
 what happens then?  Will it try to reuse available values from
 records that have been deleted?  Or is it always an error?

It will roll over and return a duplicate key error on the first
insert of a low-numbered value that still exists.  If you think you're
going to generate more than 2 billion records, use a BIGINT which will
never roll over (well, if you inserted 2 billion records per second, it
would roll over in ~270 years).

-- 
Dan Nelson
[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Newbie Question: listing open issues regardless of project

[Gleb Paharenko]

Hello.



 Please, I don't need comments telling me that I'm missing the semicolon

 or that comments such as doesn't work are worthless.  I know there's

 something inherently wrong with this query (obviously . . . since it

 doesn't give me the results that I am looking for).  Does anyone havea

 constructive comment as to how to do this?



Please, could you answer what are you going to get in the last columns.

You've said that you had problems with the last seven columns, but from

the logic of your query I see that you just want a column, which changes

its value depending on the value in other field. Am I correct? Please

provide a sample output (what you want to see in the results) in case

I'm wrong. Now I'm not talking about syntax, but rather about sense

of the query.









Kraer, Joseph wrote:

 I am sending this message to both Eventum and MySQL support lists.

 

 I am trying to write a select statement in the MySQL Query Browser (v.

 1.1.10) on my Eventum (v. 1.4) database; MySQL version is 4.0.21 (PHP is

 4.3.10).  My goal is to list certain data from all open issues, across

 projects, as well as listing some issue details contained in custom

 fields.  I don't have a problem getting the data for the first eight

 columns of my query.  My problem lies with listing the next seven

 columns, which come from custom fields.  I thought of using a PHP script

 but this is a one-time deal and my knowledge of PHP is as poor as that

 of MySQL.

 

 As Eventum users may know, headings for custom fields are extracted from

 cells in the eventum_custom_field_option table.  I thought that IF

 statements would do the job, but I get a syntax error (1064).

 Obviously, they are not the way to go.  Nevertheless, here's the

 complete query so you can get an idea of where I want to go:

 

 SELECT DISTINCT

   eventum_issue.iss_id AS Issue ID,

   eventum_project_priority.pri_title AS Priority,

   eventum_user.usr_full_name AS Assigned,

   eventum_project.prj_title AS Project Name,

   eventum_project_category.prc_title AS Category,

   eventum_status.sta_title AS Status,

   eventum_issue.iss_updated_date AS Last Update Date,

   eventum_issue.iss_summary AS Summary,

 

   IF eventum_custom_field.fld_id =3D 47

   THEN eventum_custom_field_option.cfo_value AS Product

 Name

   ELSE IF eventum_custom_field.fld_id =3D 59

   THEN eventum_custom_field_option.cfo_value AS Project

 Name

   ELSE IF eventum_custom_field.fld_id =3D 4

   THEN eventum_custom_field_option.cfo_value AS Change

 Requester

   ELSE IF eventum_custom_field.fld_id =3D 1

   THEN eventum_custom_field_option.cfo_value AS Change

 Type

   ELSE IF eventum_custom_field.fld_id =3D 2

   THEN eventum_custom_field_option.cfo_value AS Requested

 Completion Date

   ELSE IF eventum_custom_field.fld_id =3D 46

   THEN eventum_custom_field_option.cfo_value AS BSA Lead

   ELSE IF eventum_custom_field.fld_id =3D 37

   THEN eventum_custom_field_option.cfo_value AS

 Developer

 

 FROM eventum_issue, eventum_custom_field, eventum_custom_field_option

 INNER JOIN

   eventum_project_priority,

   eventum_issue_user,

   eventum_user,

   eventum_project,

   eventum_project_category,

   eventum_status

   eventum_issue_custom_field

 WHERE eventum_issue.iss_pri_id =3D eventum_project_priority.pri_id

 AND eventum_issue.iss_id =3D eventum_issue_user.isu_iss_id

 AND eventum_issue_user.isu_usr_id =3D eventum_user.usr_id

 AND eventum_issue.iss_prj_id =3D eventum_project.prj_id

 AND eventum_issue.iss_prc_id =3D eventum_project_category.prc_id

 AND eventum_issue.iss_sta_id =3D eventum_status.sta_id

 AND (eventum_issue.iss_closed_date IS NULL

   OR (eventum_issue.iss_closed_date IS NOT NULL

   AND (eventum_issue.iss_sta_id !=3D 5

   OR eventum_issue.iss_sta_id !=3D 6

   OR eventum_issue.iss_sta_id !=3D 9)))

 ORDER BY eventum_issue.iss_id

 

 Please, I don't need comments telling me that I'm missing the semicolon

 or that comments such as doesn't work are worthless.  I know there's

 something inherently wrong with this query (obviously . . . since it

 doesn't give me the results that I am looking for).  Does anyone have a

 constructive comment as to how to do this?

 

 Thank you very much in advance,

 

 Joseph Tito Kraer

 Business Systems Analyst

 Taylor, Bean  Whitaker Mortgage Corp

 

 

 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   ___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To 

RE: Newbie Question: listing open issues regardless of project

[Kraer, Joseph]

Gleb,

What I need to have is a total of 16 columns: issue ID, priority,
assigned (to), project name, category, status, last update date,
summary, product name, project name (not the same as before), change
requester, change type, requested completion date, lead, developer,
impacted dept.

I have no problems generating the first eight or the last columns.  The
seven in between are the issue as they are not columns per se.  They are
cells in different tables.  I need to extract the contents of certain
cells, based on certain IDs, to be used as the column headings in the
output to my query.  Then, I need to look into other tables to fill
those columns.  This is what I need help with: how do I generate those
columns?  I guess they could be generated separately and then I could
put both of my outputs together.  Unfortunately, time is running out.

TIA,

Joseph Tito Kraer
Business Systems Analyst
Taylor, Bean  Whitaker Mortgage Corp

-Original Message-
From: Gleb Paharenko [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, December 06, 2005 6:52 AM
To: mysql@lists.mysql.com
Subject: Re: Newbie Question: listing open issues regardless of project

Hello.



 Please, I don't need comments telling me that I'm missing the
semicolon
 or that comments such as doesn't work are worthless.  I know there's
 something inherently wrong with this query (obviously . . . since it
 doesn't give me the results that I am looking for).  Does anyone have
a
 constructive comment as to how to do this?

Please, could you answer what are you going to get in the last columns.
You've said that you had problems with the last seven columns, but from
the logic of your query I see that you just want a column, which changes
its value depending on the value in other field. Am I correct? Please
provide a sample output (what you want to see in the results) in case
I'm wrong. Now I'm not talking about syntax, but rather about sense
of the query.

Kraer, Joseph wrote:

 I am sending this message to both Eventum and MySQL support lists.
 
 I am trying to write a select statement in the MySQL Query Browser (v.
 1.1.10) on my Eventum (v. 1.4) database; MySQL version is 4.0.21 (PHP
is
 4.3.10).  My goal is to list certain data from all open issues, across
 projects, as well as listing some issue details contained in custom
 fields.  I don't have a problem getting the data for the first eight
 columns of my query.  My problem lies with listing the next seven
 columns, which come from custom fields.  I thought of using a PHP
script
 but this is a one-time deal and my knowledge of PHP is as poor as that
 of MySQL.
 
 As Eventum users may know, headings for custom fields are extracted
from
 cells in the eventum_custom_field_option table.  I thought that IF
 statements would do the job, but I get a syntax error (1064).
 Obviously, they are not the way to go.  Nevertheless, here's the
 complete query so you can get an idea of where I want to go:
 
 SELECT DISTINCT
   eventum_issue.iss_id AS Issue ID,
   eventum_project_priority.pri_title AS Priority,
   eventum_user.usr_full_name AS Assigned,
   eventum_project.prj_title AS Project Name,
   eventum_project_category.prc_title AS Category,
   eventum_status.sta_title AS Status,
   eventum_issue.iss_updated_date AS Last Update Date,
   eventum_issue.iss_summary AS Summary,
 
   IF eventum_custom_field.fld_id =3D 47
   THEN eventum_custom_field_option.cfo_value AS Product
 Name
   ELSE IF eventum_custom_field.fld_id =3D 59
   THEN eventum_custom_field_option.cfo_value AS Project
 Name
   ELSE IF eventum_custom_field.fld_id =3D 4
   THEN eventum_custom_field_option.cfo_value AS Change
 Requester
   ELSE IF eventum_custom_field.fld_id =3D 1
   THEN eventum_custom_field_option.cfo_value AS Change
 Type
   ELSE IF eventum_custom_field.fld_id =3D 2
   THEN eventum_custom_field_option.cfo_value AS Requested
 Completion Date
   ELSE IF eventum_custom_field.fld_id =3D 46
   THEN eventum_custom_field_option.cfo_value AS BSA Lead
   ELSE IF eventum_custom_field.fld_id =3D 37
   THEN eventum_custom_field_option.cfo_value AS
 Developer
 
 FROM eventum_issue, eventum_custom_field, eventum_custom_field_option
 INNER JOIN
   eventum_project_priority,
   eventum_issue_user,
   eventum_user,
   eventum_project,
   eventum_project_category,
   eventum_status
   eventum_issue_custom_field
 WHERE eventum_issue.iss_pri_id =3D eventum_project_priority.pri_id
 AND eventum_issue.iss_id =3D eventum_issue_user.isu_iss_id
 AND eventum_issue_user.isu_usr_id =3D eventum_user.usr_id
 AND eventum_issue.iss_prj_id =3D eventum_project.prj_id
 AND eventum_issue.iss_prc_id =3D eventum_project_category.prc_id
 AND eventum_issue.iss_sta_id =3D eventum_status.sta_id
 AND (eventum_issue.iss_closed_date IS NULL
   OR (eventum_issue.iss_closed_date IS NOT NULL

RE: Newbie Question: listing open issues regardless of project

[SGreen]

I see it! 

Tito, you have to pivot those fields out of your custom fields/custom 
field values table(s). One of the best places to do that is into a 
temporary table so that you can join your pivoted rows into the rest of 
the report. I am assuming that the `eventum_custom_field` table has a 
field something like `issue_id` that associates a field with an issue.  I 
am also assuming that there is only one custom field of any one type per 
issue.

CREATE TEMPORARY TABLE tmpCustFields SELECT
   cf.issue_id
 , MAX(if(fld_id=47,cfo.cfo_value,NULL)) as ProductName
 , MAX(if(fld_id=59,cfo.cfo_value,NULL)) as ProjectName
 , MAX(if(fld_id=4,cfo.cfo_value,NULL)) as ChangeRequester
 , MAX(if(fld_id=1,cfo.cfo_value,NULL)) as ChangeType
 , MAX(if(fld_id=2,cfo.cfo_value,NULL)) as ReqCompletionDate
 , MAX(if(fld_id=46,cfo.cfo_value,NULL)) as BSALead
 , MAX(if(fld_id=37,cfo.cfo_value,NULL)) as Developer
FROM eventum_custom_fields cf
LEFT JOIN eventum_custom_field_options cfo
   ON cf.cf_id = cfo.cfo_custom_field_id
GROUP BY cf.issue_id;

You will need to modify the column names in the ON clause to match the 
actual names of the columns that you need to relate an option to a field 
or an issue (whichever works). This query builds your middle columns into 
a table of their own. I hope that once you get them this far, JOINing this 
temp table to the rest of the tables you need to build your query will 
look pretty straight-forward.

Look at the data to see what we did

SELECT * from tmpCustFields limit 100;

Hope that helps!

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine



Kraer, Joseph [EMAIL PROTECTED] wrote on 12/06/2005 10:39:01 AM:

 Gleb,
 
 What I need to have is a total of 16 columns: issue ID, priority,
 assigned (to), project name, category, status, last update date,
 summary, product name, project name (not the same as before), change
 requester, change type, requested completion date, lead, developer,
 impacted dept.
 
 I have no problems generating the first eight or the last columns.  The
 seven in between are the issue as they are not columns per se.  They are
 cells in different tables.  I need to extract the contents of certain
 cells, based on certain IDs, to be used as the column headings in the
 output to my query.  Then, I need to look into other tables to fill
 those columns.  This is what I need help with: how do I generate those
 columns?  I guess they could be generated separately and then I could
 put both of my outputs together.  Unfortunately, time is running out.
 
 TIA,
 
 Joseph Tito Kraer
 Business Systems Analyst
 Taylor, Bean  Whitaker Mortgage Corp
 
 -Original Message-
 From: Gleb Paharenko [mailto:[EMAIL PROTECTED] 
 Sent: Tuesday, December 06, 2005 6:52 AM
 To: mysql@lists.mysql.com
 Subject: Re: Newbie Question: listing open issues regardless of project
 
 Hello.
 
 
 
  Please, I don't need comments telling me that I'm missing the
 semicolon
  or that comments such as doesn't work are worthless.  I know there's
  something inherently wrong with this query (obviously . . . since it
  doesn't give me the results that I am looking for).  Does anyone have
 a
  constructive comment as to how to do this?
 
 Please, could you answer what are you going to get in the last columns.
 You've said that you had problems with the last seven columns, but from
 the logic of your query I see that you just want a column, which changes
 its value depending on the value in other field. Am I correct? Please
 provide a sample output (what you want to see in the results) in case
 I'm wrong. Now I'm not talking about syntax, but rather about sense
 of the query.
 
 Kraer, Joseph wrote:
 
  I am sending this message to both Eventum and MySQL support lists.
  
  I am trying to write a select statement in the MySQL Query Browser (v.
  1.1.10) on my Eventum (v. 1.4) database; MySQL version is 4.0.21 (PHP
 is
  4.3.10).  My goal is to list certain data from all open issues, across
  projects, as well as listing some issue details contained in custom
  fields.  I don't have a problem getting the data for the first eight
  columns of my query.  My problem lies with listing the next seven
  columns, which come from custom fields.  I thought of using a PHP
 script
  but this is a one-time deal and my knowledge of PHP is as poor as that
  of MySQL.
  
  As Eventum users may know, headings for custom fields are extracted
 from
  cells in the eventum_custom_field_option table.  I thought that IF
  statements would do the job, but I get a syntax error (1064).
  Obviously, they are not the way to go.  Nevertheless, here's the
  complete query so you can get an idea of where I want to go:
  
  SELECT DISTINCT
 eventum_issue.iss_id AS Issue ID,
 eventum_project_priority.pri_title AS Priority,
 eventum_user.usr_full_name AS Assigned,
 eventum_project.prj_title AS Project Name,
 eventum_project_category.prc_title AS Category,
 eventum_status.sta_title AS Status

Re: Newbie Question: listing open issues regardless of project

[Gleb Paharenko]

Hello.



So it is clear now, that you should have 16 columns and to build

dynamically the column headings. The usual way to do such things

in SQL is prepared statements. See:

  http://dev.mysql.com/doc/refman/5.0/en/sqlps.html



And good example of how to use them (though it is an article about

stored routines) here:

  http://dev.mysql.com/tech-resources/articles/mysql-storedproc.html



You will need to use user variables as well:

  http://dev.mysql.com/doc/refman/5.0/en/example-user-variables.html



CONCAT function:

  http://dev.mysql.com/doc/refman/5.0/en/string-functions.html



Control flow functions:

  http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html



Correlated subqueries:

  http://dev.mysql.com/doc/refman/5.0/en/correlated-subqueries.html



Hope that'll be enough to make your query work.







Kraer, Joseph wrote:

 Gleb,

 

 What I need to have is a total of 16 columns: issue ID, priority,

 assigned (to), project name, category, status, last update date,

 summary, product name, project name (not the same as before), change

 requester, change type, requested completion date, lead, developer,

 impacted dept.

 

 I have no problems generating the first eight or the last columns.  The

 seven in between are the issue as they are not columns per se.  They are

 cells in different tables.  I need to extract the contents of certain

 cells, based on certain IDs, to be used as the column headings in the

 output to my query.  Then, I need to look into other tables to fill

 those columns.  This is what I need help with: how do I generate those

 columns?  I guess they could be generated separately and then I could

 put both of my outputs together.  Unfortunately, time is running out.

 

 TIA,

 

 Joseph Tito Kraer

 Business Systems Analyst

 Taylor, Bean  Whitaker Mortgage Corp

 

 -Original Message-

 From: Gleb Paharenko [mailto:[EMAIL PROTECTED]

 Sent: Tuesday, December 06, 2005 6:52 AM

 To: mysql@lists.mysql.com

 Subject: Re: Newbie Question: listing open issues regardless of project



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   ___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Newbie Question: listing open issues regardless of project

[Kraer, Joseph]

Shawn, Glen, and everyone else,

Thank you very much!  I do believe that I have enough material to work
on it now!

I appreciate your help very much!

Joseph Tito Kraer
Business Systems Analyst
Taylor, Bean  Whitaker Mortgage Corp

-Original Message-
From: Gleb Paharenko [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, December 06, 2005 11:03 AM
To: mysql@lists.mysql.com
Subject: Re: Newbie Question: listing open issues regardless of project

Hello.



So it is clear now, that you should have 16 columns and to build

dynamically the column headings. The usual way to do such things

in SQL is prepared statements. See:

  http://dev.mysql.com/doc/refman/5.0/en/sqlps.html



And good example of how to use them (though it is an article about

stored routines) here:

  http://dev.mysql.com/tech-resources/articles/mysql-storedproc.html



You will need to use user variables as well:

  http://dev.mysql.com/doc/refman/5.0/en/example-user-variables.html



CONCAT function:

  http://dev.mysql.com/doc/refman/5.0/en/string-functions.html



Control flow functions:

  http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html



Correlated subqueries:

  http://dev.mysql.com/doc/refman/5.0/en/correlated-subqueries.html



Hope that'll be enough to make your query work.







Kraer, Joseph wrote:

 Gleb,

 

 What I need to have is a total of 16 columns: issue ID, priority,

 assigned (to), project name, category, status, last update date,

 summary, product name, project name (not the same as before), change

 requester, change type, requested completion date, lead, developer,

 impacted dept.

 

 I have no problems generating the first eight or the last columns.
The

 seven in between are the issue as they are not columns per se.  They
are

 cells in different tables.  I need to extract the contents of certain

 cells, based on certain IDs, to be used as the column headings in the

 output to my query.  Then, I need to look into other tables to fill

 those columns.  This is what I need help with: how do I generate those

 columns?  I guess they could be generated separately and then I could

 put both of my outputs together.  Unfortunately, time is running out.

 

 TIA,

 

 Joseph Tito Kraer

 Business Systems Analyst

 Taylor, Bean  Whitaker Mortgage Corp

 

 -Original Message-

 From: Gleb Paharenko [mailto:[EMAIL PROTECTED]

 Sent: Tuesday, December 06, 2005 6:52 AM

 To: mysql@lists.mysql.com

 Subject: Re: Newbie Question: listing open issues regardless of
project



-- 
For technical support contracts, goto
https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   ___/   www.mysql.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]

Newbie question to both lists on listing open issues regardless of project

[Kraer, Joseph]

I am trying to write a select statement in the MySQL Query Browser (v.
1.1.10) on my Eventum (v. 1.4) database; MySQL version is 4.0.21 (PHP is
4.3.10).  My goal is to list certain data from all open issues, across
projects, as well as listing some issue details contained in custom
fields.  I don't have a problem getting the data for the first eight
columns of my query.  My problem lies with listing the next seven
columns, which come from custom fields.  I thought of using a PHP script
but this is a one-time deal and my knowledge of PHP is as poor as that
of MySQL.

As Eventum users may know, headings for custom fields are extracted from
cells in the eventum_custom_field_option table.  I thought that IF
statements would do the job, but I get a syntax error (1064).
Obviously, they are not the way to go.  Nevertheless, here's the
complete query so you can get an idea of where I want to go:

SELECT DISTINCT
eventum_issue.iss_id AS Issue ID,
eventum_project_priority.pri_title AS Priority,
eventum_user.usr_full_name AS Assigned,
eventum_project.prj_title AS Project Name,
eventum_project_category.prc_title AS Category,
eventum_status.sta_title AS Status,
eventum_issue.iss_updated_date AS Last Update Date,
eventum_issue.iss_summary AS Summary,

IF eventum_custom_field.fld_id = 47
THEN eventum_custom_field_option.cfo_value AS Product
Name
ELSE IF eventum_custom_field.fld_id = 59
THEN eventum_custom_field_option.cfo_value AS Project
Name
ELSE IF eventum_custom_field.fld_id = 4
THEN eventum_custom_field_option.cfo_value AS Change
Requester
ELSE IF eventum_custom_field.fld_id = 1
THEN eventum_custom_field_option.cfo_value AS Change
Type
ELSE IF eventum_custom_field.fld_id = 2
THEN eventum_custom_field_option.cfo_value AS Requested
Completion Date
ELSE IF eventum_custom_field.fld_id = 46
THEN eventum_custom_field_option.cfo_value AS BSA Lead
ELSE IF eventum_custom_field.fld_id = 37
THEN eventum_custom_field_option.cfo_value AS
Developer

FROM eventum_issue, eventum_custom_field, eventum_custom_field_option
INNER JOIN
eventum_project_priority,
eventum_issue_user,
eventum_user,
eventum_project,
eventum_project_category,
eventum_status
eventum_issue_custom_field
WHERE eventum_issue.iss_pri_id = eventum_project_priority.pri_id
AND eventum_issue.iss_id = eventum_issue_user.isu_iss_id
AND eventum_issue_user.isu_usr_id = eventum_user.usr_id
AND eventum_issue.iss_prj_id = eventum_project.prj_id
AND eventum_issue.iss_prc_id = eventum_project_category.prc_id
AND eventum_issue.iss_sta_id = eventum_status.sta_id
AND (eventum_issue.iss_closed_date IS NULL
OR (eventum_issue.iss_closed_date IS NOT NULL
AND (eventum_issue.iss_sta_id != 5
OR eventum_issue.iss_sta_id != 6
OR eventum_issue.iss_sta_id != 9)))
ORDER BY eventum_issue.iss_id

Please, I don't need comments telling me that I'm missing the semicolon
or that comments such as doesn't work are worthless.  I know there's
something inherently wrong with this query (obviously . . . since it
doesn't give me the results that I am looking for).  Does anyone have a
constructive comment as to how to do this?

Thank you very much in advance,

Joseph Tito Kraer
Business Systems Analyst
Taylor, Bean  Whitaker Mortgage Corp



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Newbie Question: listing open issues regardless of project

[Kraer, Joseph]

I am sending this message to both Eventum and MySQL support lists.

I am trying to write a select statement in the MySQL Query Browser (v.
1.1.10) on my Eventum (v. 1.4) database; MySQL version is 4.0.21 (PHP is
4.3.10).  My goal is to list certain data from all open issues, across
projects, as well as listing some issue details contained in custom
fields.  I don't have a problem getting the data for the first eight
columns of my query.  My problem lies with listing the next seven
columns, which come from custom fields.  I thought of using a PHP script
but this is a one-time deal and my knowledge of PHP is as poor as that
of MySQL.

As Eventum users may know, headings for custom fields are extracted from
cells in the eventum_custom_field_option table.  I thought that IF
statements would do the job, but I get a syntax error (1064).
Obviously, they are not the way to go.  Nevertheless, here's the
complete query so you can get an idea of where I want to go:

SELECT DISTINCT
eventum_issue.iss_id AS Issue ID,
eventum_project_priority.pri_title AS Priority,
eventum_user.usr_full_name AS Assigned,
eventum_project.prj_title AS Project Name,
eventum_project_category.prc_title AS Category,
eventum_status.sta_title AS Status,
eventum_issue.iss_updated_date AS Last Update Date,
eventum_issue.iss_summary AS Summary,

IF eventum_custom_field.fld_id = 47
THEN eventum_custom_field_option.cfo_value AS Product
Name
ELSE IF eventum_custom_field.fld_id = 59
THEN eventum_custom_field_option.cfo_value AS Project
Name
ELSE IF eventum_custom_field.fld_id = 4
THEN eventum_custom_field_option.cfo_value AS Change
Requester
ELSE IF eventum_custom_field.fld_id = 1
THEN eventum_custom_field_option.cfo_value AS Change
Type
ELSE IF eventum_custom_field.fld_id = 2
THEN eventum_custom_field_option.cfo_value AS Requested
Completion Date
ELSE IF eventum_custom_field.fld_id = 46
THEN eventum_custom_field_option.cfo_value AS BSA Lead
ELSE IF eventum_custom_field.fld_id = 37
THEN eventum_custom_field_option.cfo_value AS
Developer

FROM eventum_issue, eventum_custom_field, eventum_custom_field_option
INNER JOIN
eventum_project_priority,
eventum_issue_user,
eventum_user,
eventum_project,
eventum_project_category,
eventum_status
eventum_issue_custom_field
WHERE eventum_issue.iss_pri_id = eventum_project_priority.pri_id
AND eventum_issue.iss_id = eventum_issue_user.isu_iss_id
AND eventum_issue_user.isu_usr_id = eventum_user.usr_id
AND eventum_issue.iss_prj_id = eventum_project.prj_id
AND eventum_issue.iss_prc_id = eventum_project_category.prc_id
AND eventum_issue.iss_sta_id = eventum_status.sta_id
AND (eventum_issue.iss_closed_date IS NULL
OR (eventum_issue.iss_closed_date IS NOT NULL
AND (eventum_issue.iss_sta_id != 5
OR eventum_issue.iss_sta_id != 6
OR eventum_issue.iss_sta_id != 9)))
ORDER BY eventum_issue.iss_id

Please, I don't need comments telling me that I'm missing the semicolon
or that comments such as doesn't work are worthless.  I know there's
something inherently wrong with this query (obviously . . . since it
doesn't give me the results that I am looking for).  Does anyone have a
constructive comment as to how to do this?

Thank you very much in advance,

Joseph Tito Kraer
Business Systems Analyst
Taylor, Bean  Whitaker Mortgage Corp



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Newbie question to both lists on listing open issues regardless of project

[Daniel Kasak]

Kraer, Joseph wrote:

snipped


I thought that IF
statements would do the job, but I get a syntax error (1064).
Obviously, they are not the way to go.  Nevertheless, here's the
complete query so you can get an idea of where I want to go:
 


Your if statement is wrong.

The syntax is:

if ( condition, value_if_true, value_if_false )

You can also use 'case' statements:

case when condition then value_if_true else value_if_false end

Substitute your:

- condition
- value_if_true
- value_if_false

into the above syntax.

--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: http://www.nusconsulting.com.au

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Newbie Question on Update

[Kraer, Joseph]

I am trying to update a couple of rows in a table by doing the
following:

update table
set column5 = number1
where column 1 = number2
and column1 = number3

but it is not working.  I tried listing the conditions separated by
commas (where column 1 = number2, column1 = number3) and also didn't
work.  What am I doing wrong?

Thanks in advance,

Joseph Tito Kraer
Business Systems Analyst
Taylor, Bean  Whitaker Mortgage Corp



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Newbie Question on Update

[Michael Stassen]

Kraer, Joseph wrote:

I am trying to update a couple of rows in a table by doing the
following:

update table
set column5 = number1
where column 1 = number2
and column1 = number3


This appears to be correct syntax.


but it is not working.


We can't help you if you don't tell us what you mean by not working.  Do you 
get an error message?  If so, what is it?  Do you get unexpected results?  If 
so, tell us what you expected and what you got.



I tried listing the conditions separated by
commas (where column 1 = number2, column1 = number3) and also didn't
work.  What am I doing wrong?


Because that isn't valid syntax.  Don't make things up.  Read the manual 
instead.  UPDATE syntax is described in detail 
http://dev.mysql.com/doc/refman/5.0/en/update.html.



Thanks in advance,

Joseph Tito Kraer
Business Systems Analyst
Taylor, Bean  Whitaker Mortgage Corp


Michael


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Newbie Question on Update

[ISC Edwin Cruz]

I guess that the problem is the reserved word table
Try it:

Update `table`
Set column5=number1
where column 1 = number2
and column1 = number3


But the sintax is ok

Regards!

-Mensaje original-
De: Kraer, Joseph [mailto:[EMAIL PROTECTED] 
Enviado el: Miércoles, 30 de Noviembre de 2005 10:59 a.m.
Para: mysql@lists.mysql.com
Asunto: Newbie Question on Update


I am trying to update a couple of rows in a table by doing the
following:

update table
set column5 = number1
where column 1 = number2
and column1 = number3

but it is not working.  I tried listing the conditions separated by commas
(where column 1 = number2, column1 = number3) and also didn't work.  What am
I doing wrong?

Thanks in advance,

Joseph Tito Kraer
Business Systems Analyst
Taylor, Bean  Whitaker Mortgage Corp



-- 
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 Question on Update

[SGreen]

Kraer, Joseph [EMAIL PROTECTED] wrote on 11/30/2005 11:58:56 AM:

 I am trying to update a couple of rows in a table by doing the
 following:
 
 update table
 set column5 = number1
 where column 1 = number2
 and column1 = number3
 
 but it is not working.  I tried listing the conditions separated by
 commas (where column 1 = number2, column1 = number3) and also didn't
 work.  What am I doing wrong?
 
 Thanks in advance,
 
 Joseph Tito Kraer
 Business Systems Analyst
 Taylor, Bean  Whitaker Mortgage Corp
 

UPDATE statements are evaluated on a row-by-row basis. What you actually 
told MySQL to do was to change any rows where column1 has BOTH values at 
the same time. Since one column in a single row cannot possibly have two 
values at once (not even SET columns because they do not have more than 
one set of values at a single time) this condition cannot possibly be met 
and the UPDATE will never happen. Make sense?

You said:
WHERE column1=number2 AND column1=number3

The AND is a logical comparator meaning that both comparisons must be true 
for the WHERE clause to be true and your desired UPDATE to occur.

What I think you wanted to do is to UPDATE two rows. One row where 
column1=number2 and the other row where column1=number3. That is an OR 
situation not an AND situation:
WHERE column1=number2 OR column1=number3

Another way to write that condition is with an IN clause :
WHERE column1 IN (number2, number3)

HTH!

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

RE: Newbie Question on Update

[mel list_php]

Hi,
In your query you try to update on the condition  column 1 = number2 and 
column1 = number3.

I think what you want is  column 1 = number2 or column1 = number3 .
If you use and it will try and found a record in column which has a value = 
number2 and at the same time = number3.

hth,
melanie



From: Kraer, Joseph [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Subject: Newbie Question on Update
Date: Wed, 30 Nov 2005 11:58:56 -0500

I am trying to update a couple of rows in a table by doing the
following:

update table
set column5 = number1
where column 1 = number2
and column1 = number3

but it is not working.  I tried listing the conditions separated by
commas (where column 1 = number2, column1 = number3) and also didn't
work.  What am I doing wrong?

Thanks in advance,

Joseph Tito Kraer
Business Systems Analyst
Taylor, Bean  Whitaker Mortgage Corp



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]




_
Be the first to hear what's new at MSN - sign up to our free newsletters! 
http://www.msn.co.uk/newsletters



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Newbie Question on Update

[Kraer, Joseph]

Thank you, Shawn, for understanding what I meant and for explaining the
issue so clearly.  I apologize to all others if I wasn't clear enough,
but, yes, I wanted to update two separate rows.  Now, I understand why
an OR is needed; I'll study the other option too.

 

Thank you,

Joseph Tito Kraer 
Business Systems Analyst 
Taylor, Bean  Whitaker Mortgage Corp 





From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, November 30, 2005 12:13 PM
To: Kraer, Joseph
Cc: mysql@lists.mysql.com
Subject: Re: Newbie Question on Update

 



Kraer, Joseph [EMAIL PROTECTED] wrote on 11/30/2005 11:58:56 AM:

 I am trying to update a couple of rows in a table by doing the
 following:
 
 update table
 set column5 = number1
 where column 1 = number2
 and column1 = number3
 
 but it is not working.  I tried listing the conditions separated by
 commas (where column 1 = number2, column1 = number3) and also didn't
 work.  What am I doing wrong?
 
 Thanks in advance,
 
 Joseph Tito Kraer
 Business Systems Analyst
 Taylor, Bean  Whitaker Mortgage Corp
 

UPDATE statements are evaluated on a row-by-row basis. What you actually
told MySQL to do was to change any rows where column1 has BOTH values at
the same time. Since one column in a single row cannot possibly have two
values at once (not even SET columns because they do not have more than
one set of values at a single time) this condition cannot possibly be
met and the UPDATE will never happen. Make sense? 

You said: 
WHERE column1=number2 AND column1=number3 

The AND is a logical comparator meaning that both comparisons must be
true for the WHERE clause to be true and your desired UPDATE to occur. 

What I think you wanted to do is to UPDATE two rows. One row where
column1=number2 and the other row where column1=number3. That is an OR
situation not an AND situation: 
WHERE column1=number2 OR column1=number3 

Another way to write that condition is with an IN clause : 
WHERE column1 IN (number2, number3) 

HTH! 

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: Newbie question: UPDATE

[Gleb Paharenko]

Hello.



I'm not a PHPMyAdmin guru, but at least LOAD DATA LOCAL feature (if it is

present in PHPMyAdmin) can be disabled due to some security reasons. See:

  http://dev.mysql.com/doc/refman/5.0/en/load-data-local.html









Joe Herman wrote:

 To those thinking of answering this question, There is additional

 information.

 

 For some reason, I found out that the version of PHPMyAdmin at my web

 hosting provider was missing the Insert data from a text file into the

 table feature found at the bottom of the page after clicking on the

 name of the table.

 

 I thought that the reason for this was because they were using an older

 version of PHPMyAdmin, so he was kind enough to download and install the

 recent stable version into a folder under my hosting account via ftp. He

 told me this copy was for me alone to use, since he could not upgrade it

 for the entire system.

 

 After I hung up with him, I launched the version of PHPMyAdmin he

 installed for me and went to look for the feature. Unfortunately, the

 feature was still not there!

 

 Is this feature special, an add on to PHPMyAdmin? Or should it be there

 with the standard installation? If it is special, where can it be found?

 

 Thank you for your help.

 

 Joe.

 

 

 

 

 

 -Original Message-

 From: Joe Herman [mailto:[EMAIL PROTECTED] 

 Sent: Sunday, November 27, 2005 12:21 AM

 To: mysql@lists.mysql.com

 Subject: Newbie question: How to import data into a table?

 

 

 Hello folks,

  

 What is the best way to import an excel spreadsheet, or text file for

 that matter into a table?

  

 Is there a way to do this with PHPMyAdmin?

  

 Thank you so much for your kind help.

  

 Joe

 

 









-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   ___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Newbie Question on Update

[Peter Brawley]

Joseph

update table
set column5 = number1
where column 1 = number2
and column1 = number3

but it is not working. I tried listing the conditions separated by
commas (where column 1 = number2, column1 = number3) and also didn't
work. What am I doing wrong?

No commas in the WHERE clause. WHERE ... AND ... '

'Where column 1 = ...' is wrong. If by 'column 1' you mean 'column1', your
condition can hold only when number2=number3, in which case the 'and' 
clause

is apparently superfluous.

Otherwise, you'll have to tell us what you mean by not working.

PB

-

Kraer, Joseph wrote:


I am trying to update a couple of rows in a table by doing the
following:

update table
set column5 = number1
where column 1 = number2
and column1 = number3

but it is not working.  I tried listing the conditions separated by
commas (where column 1 = number2, column1 = number3) and also didn't
work.  What am I doing wrong?

Thanks in advance,

Joseph Tito Kraer
Business Systems Analyst
Taylor, Bean  Whitaker Mortgage Corp



 




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.13.10/189 - Release Date: 11/30/2005


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Newbie question: UPDATE

[Gleb Paharenko]

Hello.



I'm not PHPMyAdmin guru, but at least LOAD DATA LOCAL feature (if it is

present in PHPMyAdmin) can be disabled due to some security reasons. See:

  http://dev.mysql.com/doc/refman/5.0/en/load-data-local.html









Joe Herman wrote:

 To those thinking of answering this question, There is additional

 information.

 

 For some reason, I found out that the version of PHPMyAdmin at my web

 hosting provider was missing the Insert data from a text file into the

 table feature found at the bottom of the page after clicking on the

 name of the table.

 

 I thought that the reason for this was because they were using an older

 version of PHPMyAdmin, so he was kind enough to download and install the

 recent stable version into a folder under my hosting account via ftp. He

 told me this copy was for me alone to use, since he could not upgrade it

 for the entire system.

 

 After I hung up with him, I launched the version of PHPMyAdmin he

 installed for me and went to look for the feature. Unfortunately, the

 feature was still not there!

 

 Is this feature special, an add on to PHPMyAdmin? Or should it be there

 with the standard installation? If it is special, where can it be found?

 

 Thank you for your help.

 

 Joe.

 

 

 

 

 

 -Original Message-

 From: Joe Herman [mailto:[EMAIL PROTECTED] 

 Sent: Sunday, November 27, 2005 12:21 AM

 To: mysql@lists.mysql.com

 Subject: Newbie question: How to import data into a table?

 

 

 Hello folks,

  

 What is the best way to import an excel spreadsheet, or text file for

 that matter into a table?

  

 Is there a way to do this with PHPMyAdmin?

  

 Thank you so much for your kind help.

  

 Joe

 

 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   ___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Newbie question: UPDATE

[Joe Herman]

To those thinking of answering this question, There is additional
information.

For some reason, I found out that the version of PHPMyAdmin at my web
hosting provider was missing the Insert data from a text file into the
table feature found at the bottom of the page after clicking on the
name of the table.

I thought that the reason for this was because they were using an older
version of PHPMyAdmin, so he was kind enough to download and install the
recent stable version into a folder under my hosting account via ftp. He
told me this copy was for me alone to use, since he could not upgrade it
for the entire system.

After I hung up with him, I launched the version of PHPMyAdmin he
installed for me and went to look for the feature. Unfortunately, the
feature was still not there!

Is this feature special, an add on to PHPMyAdmin? Or should it be there
with the standard installation? If it is special, where can it be found?

Thank you for your help.

Joe.





-Original Message-
From: Joe Herman [mailto:[EMAIL PROTECTED] 
Sent: Sunday, November 27, 2005 12:21 AM
To: mysql@lists.mysql.com
Subject: Newbie question: How to import data into a table?


Hello folks,
 
What is the best way to import an excel spreadsheet, or text file for
that matter into a table?
 
Is there a way to do this with PHPMyAdmin?
 
Thank you so much for your kind help.
 
Joe


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Newbie question: How to import data into a table?

[Joe Herman]

Hello folks,
 
What is the best way to import an excel spreadsheet, or text file for
that matter into a table?
 
Is there a way to do this with PHPMyAdmin?
 
Thank you so much for your kind help.
 
Joe

Re: Newbie Question

[Michael J. Pawlowsky]

Jeffrey G. Ubalde wrote:


Good day list!

I would just like to ask a somewhat not so intelligent question. What 
is the downside of indexing almost all of the fields in a table? Is it 
advisable?




Indexes have to be built...  so if you did that, for every query that 
alters the data many indexes will have to be written. A lot of extra 
overhead if they will never be used.
Look to see where indexes are needed by the types of queries you are 
writing. Add a slow query log to my.cnf.

This will give you a very good idea of where you might need some indexes.
Then trace the queries to make sure the indexes you've created are being 
used.



Cheers,
Mike





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Newbie Question

[Scott Noyes]

  I would just like to ask a somewhat not so intelligent question. What
  is the downside of indexing almost all of the fields in a table? Is it
  advisable?

 Indexes have to be built

They also consume disk space, so if you're tight on hard drives, they
might put you over the top.

--
Scott Noyes
[EMAIL PROTECTED]

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Newbie Question

[Rhino]

Just to amplify what Michael has said

If you had a table with 50 columns and created a separate index for each of
those columns, then you'd probably get some performance benefit from having
those indexes. But you'd also have to absorb the following overhead:
1. For each new row that you created, you'd have to add an entry to each of
the 50 indexes on the table.
2. For each row that you deleted, you'd have to remove an entry from each of
the 50 indexes on the table.
3. For each row that you changed, you'd have to change the index entries on
all columns that changed.
4. For each index you build, you'd have to pay for building that index and
you'd have to pay for whatever storage that index used.

That adds up to a lot of overhead, likely far more than the benefit you got
by indexing the columns in the first place.

Rather than putting indexes on every column database designers tend to put
them in the following places:
1. A unique index on the primary key (which is required on every foreign key
to enable Referential Integrity).
2. A unique or non-unique index (as appropriate) on each foreign key to help
performance of Referential Integrity and joins, which are frequently on
foreign keys.
3. A unique or non-unique index (as appropriate) on any other columns where
query performance is critical.
4. A non-unique index on the column (or combination of columns) that you
want as your clustering key. (The clustering key governs the physical
sequence in which data is stored.) [NOTE: I mostly use DB2 and clustering is
a big deal there but I'm not sure if it works the same way in MySQL.]

Rhino

- Original Message - 
From: Michael J. Pawlowsky [EMAIL PROTECTED]
To: Jeffrey G. Ubalde [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Monday, November 14, 2005 8:19 PM
Subject: Re: Newbie Question


 Jeffrey G. Ubalde wrote:

  Good day list!
 
  I would just like to ask a somewhat not so intelligent question. What
  is the downside of indexing almost all of the fields in a table? Is it
  advisable?
 

 Indexes have to be built...  so if you did that, for every query that
 alters the data many indexes will have to be written. A lot of extra
 overhead if they will never be used.
 Look to see where indexes are needed by the types of queries you are
 writing. Add a slow query log to my.cnf.
 This will give you a very good idea of where you might need some indexes.
 Then trace the queries to make sure the indexes you've created are being
 used.


 Cheers,
 Mike





 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]


 -- 
 No virus found in this incoming message.
 Checked by AVG Free Edition.
 Version: 7.1.362 / Virus Database: 267.13.0/167 - Release Date: 11/11/2005





-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.13.0/167 - Release Date: 11/11/2005


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Newbie question: number of connections

[Brian Dunning]

My RaQ4 is throwing the Too many connections error. Now, it is  
getting hit pretty hard by traffic, but I wonder if my coding might  
be exacerbating this error.


Due to my relatively lazy structure of includes and functions, my  
pages might have half a dozen or so of these (most are to the same  
database):


$connection = mysql_connect($db_host, $db_username, $db_password);
mysql_select_db($db_name, $connection);

Is that creating half a dozen connections, even though it's just one  
web user?


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Newbie question: number of connections

[Brian Dunning]

In one case I do need to jump back and forth between databases that  
are on different physical servers. What's the most efficient way to  
handle this?



On Aug 2, 2005, at 11:14 AM, Devananda wrote:


Brian Dunning wrote:

My RaQ4 is throwing the Too many connections error. Now, it is   
getting hit pretty hard by traffic, but I wonder if my coding  
might  be exacerbating this error.
Due to my relatively lazy structure of includes and functions, my   
pages might have half a dozen or so of these (most are to the  
same  database):

$connection = mysql_connect($db_host, $db_username, $db_password);
mysql_select_db($db_name, $connection);
Is that creating half a dozen connections, even though it's just  
one  web user?




Yep. Each call to mysql_connect creates a new connection to the  
database. If you need to connect to multiple database servers then  
you obviously need multiple connections, but if you are only  
connecting to one database server, then you should really reuse a  
single connection. FYI, the process of creating a new connection  
requires substantially more processing time than executing a simple  
query (ie, mysql_select_db).


One common practice is to have a single include file which creates  
the database connection and instantiates a global variable with the  
handle to that connection. Then, all pages which include that file  
merely use that handle to communicate with the database.


Regards,
Devananda vdv




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Newbie question: number of connections

[Devananda]

Brian Dunning wrote:
In one case I do need to jump back and forth between databases that  are 
on different physical servers. What's the most efficient way to  handle 
this?



On Aug 2, 2005, at 11:14 AM, Devananda wrote:


Brian Dunning wrote:

My RaQ4 is throwing the Too many connections error. Now, it is   
getting hit pretty hard by traffic, but I wonder if my coding  might  
be exacerbating this error.
Due to my relatively lazy structure of includes and functions, my   
pages might have half a dozen or so of these (most are to the  same  
database):

$connection = mysql_connect($db_host, $db_username, $db_password);
mysql_select_db($db_name, $connection);
Is that creating half a dozen connections, even though it's just  
one  web user?




Yep. Each call to mysql_connect creates a new connection to the  
database. If you need to connect to multiple database servers then  
you obviously need multiple connections, but if you are only  
connecting to one database server, then you should really reuse a  
single connection. FYI, the process of creating a new connection  
requires substantially more processing time than executing a simple  
query (ie, mysql_select_db).


One common practice is to have a single include file which creates  
the database connection and instantiates a global variable with the  
handle to that connection. Then, all pages which include that file  
merely use that handle to communicate with the database.


Regards,
Devananda vdv






Create two connections at the beginning of your script/page, then query 
each as you need to. For example,


$connA = mysql_connect(A.server.com,...);
mysql_select_db($db_name,$connA);

$connB = mysql_connect(B.server.com,...);
mysql_select_db($another_db_name,$connB);

$hdl_some_query_on_A = mysql_query(SELECT something FROM sometable, 
$connA);	# this query goes to A.server.com
$hdl_some_query_on_B = mysql_query(SELECT some_other_thing FROM 
another_table,$connB);		# this one goes to B.server.com



and so on. By storing the resource returned from mysql_connect, you can 
tell mysql_query which connection to use, and thus utilize multiple 
connections (but only create each connection once).



Regards,
Devananda vdv


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

newbie question create table

[Aji Andri]

hi seniors,

I'm trying to create a table, here my table
properties,

create table user (
UserID int primary,
Password varchar (20),
User_stats int multi
);

i'm still confuse in User_stats properti's that is
multi,
what really use 'multi' is ?

Thx before the guide,

Aji  




__ 
Yahoo! Messenger 
Show us what our next emoticon should look like. Join the fun. 
http://www.advision.webevents.yahoo.com/emoticontest

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Newbie question about test database...

[Jeff Justice]

Okay, so MySQL is now installed and running.  It looks like a database 
named test was created with no tables.  Is this just used for 
installation purposes?  Can it now be deleted or is there a reason to 
keep it?  Or does it have some magical purpose like the mysql 
database?

Jeff
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Newbie question about test database...

[Michael Dykman]

on one level, the test database is there just have something to log into
after intall to prove it's working..   some init.d startup scripts (I'm
thinking RedHat here)  try to log into the database as an authenticated
user after each start up and when doing a 'status' call just to make
sure it's alive.  some scrupulously paranoid sysadmins prefer to delete
it the moment the system comes up, other leave it there for
convenience..


On Mon, 2005-03-14 at 19:52, Jeff Justice wrote:
 Okay, so MySQL is now installed and running.  It looks like a database 
 named test was created with no tables.  Is this just used for 
 installation purposes?  Can it now be deleted or is there a reason to 
 keep it?  Or does it have some magical purpose like the mysql 
 database?
 
 Jeff
-- 
 - michael dykman
 - [EMAIL PROTECTED]


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

newbie question

[jsf]

This may be more of a PHP question than a MySQL question but here goes:

I have a small database with two tables in it.  It's a database of
Botanical Gardens in the US.

Table 1 contains the botanical gardens and has 8 fields:

(I'm abbreviating for brevity and clarity):  id, name, address, town,
state_id, zip, phone, url

Table 2 contains the states referred to in the 'state_id' field and
itself has 3 fields

id, abbreviation, name

So, 'id' in the 'state' table is linked to the 'gardens' table via
that table's 'state_id' field.

Now, in pulling data out of the database to display on a web page I
have all of my connection stuff working and the query of 'select *
from gardens' along with this php code:

  td align=center?php echo $row_Recordset1['botgard_name']; ?/td
td align=center?php echo $row_Recordset1['botgard_address']; ?/td
td align=center?php echo $row_Recordset1['botgard_town']; ?/td
td align=center?php echo $row_Recordset1['state_id']; ?/td
td align=center?php echo $row_Recordset1['botgard_zip']; ?/td
td align=center?php echo $row_Recordset1['botgard_phone']; ?/td
td align=center?php echo $row_Recordset1['botgard_url']; ?/td

works fine, pulling records out of the 'gardens' table and displaying
it on a web page, but, of course, I'm seeing the 'state_id' instead of
either the 'state_abbreviation' or the 'state_name'.

I am at a complete and total loss as to how to edit my code at this
point so that, before displaying anything, i can grab either
'state_abbr' or 'state_name' from the states table, properly
associated with the 'state_id' and display the actual state
abbreviation or state name in my web page.

I'm so close, yet so far.  I know if I can be shown once how this
works, I'll be able to apply the solution again in the future.

Thanks in advance for any help with this.

Sincerely,

Joshua

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: newbie question

[Pete Moran]

You need to do a join on the tables,

Simplest way is 

Select * from gardens a, state b where a.state_id = b.id

Assuming id in the state table is actually what your planning on joining on.
Try to do it on the mysql command line before doing in code to make sure you
actually have the data you need.



-Original Message-
From: jsf [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, 23 February 2005 3:08 PM
To: mysql@lists.mysql.com
Subject: newbie question

This may be more of a PHP question than a MySQL question but here goes:

I have a small database with two tables in it.  It's a database of
Botanical Gardens in the US.

Table 1 contains the botanical gardens and has 8 fields:

(I'm abbreviating for brevity and clarity):  id, name, address, town,
state_id, zip, phone, url

Table 2 contains the states referred to in the 'state_id' field and
itself has 3 fields

id, abbreviation, name

So, 'id' in the 'state' table is linked to the 'gardens' table via
that table's 'state_id' field.

Now, in pulling data out of the database to display on a web page I
have all of my connection stuff working and the query of 'select *
from gardens' along with this php code:

  td align=center?php echo $row_Recordset1['botgard_name']; ?/td
td align=center?php echo $row_Recordset1['botgard_address'];
?/td
td align=center?php echo $row_Recordset1['botgard_town']; ?/td
td align=center?php echo $row_Recordset1['state_id']; ?/td
td align=center?php echo $row_Recordset1['botgard_zip']; ?/td
td align=center?php echo $row_Recordset1['botgard_phone']; ?/td
td align=center?php echo $row_Recordset1['botgard_url']; ?/td

works fine, pulling records out of the 'gardens' table and displaying
it on a web page, but, of course, I'm seeing the 'state_id' instead of
either the 'state_abbreviation' or the 'state_name'.

I am at a complete and total loss as to how to edit my code at this
point so that, before displaying anything, i can grab either
'state_abbr' or 'state_name' from the states table, properly
associated with the 'state_id' and display the actual state
abbreviation or state name in my web page.

I'm so close, yet so far.  I know if I can be shown once how this
works, I'll be able to apply the solution again in the future.

Thanks in advance for any help with this.

Sincerely,

Joshua

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]



-- 
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 266.4.0 - Release Date: 22/02/2005
 

-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 266.4.0 - Release Date: 22/02/2005
 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: newbie question

[Tom Crimmins]

On Tuesday, February 22, 2005 22:08, jsf wrote:

 This may be more of a PHP question than a MySQL question but here
 goes: 
 
 I have a small database with two tables in it.  It's a database of
 Botanical Gardens in the US.
 
 Table 1 contains the botanical gardens and has 8 fields:
 
 (I'm abbreviating for brevity and clarity):  id, name, address, town,
 state_id, zip, phone, url
 
 Table 2 contains the states referred to in the 'state_id' field and
 itself has 3 fields
 
 id, abbreviation, name
 
 So, 'id' in the 'state' table is linked to the 'gardens' table via
 that table's 'state_id' field.
 
 Now, in pulling data out of the database to display on a web page I
 have all of my connection stuff working and the query of 'select *
 from gardens' along with this php code:

You need join the two tables:

SELECT t1.name, t1.address, t1.town, t2.abbreviation, t1.zip, t1.phone,
t1.url
FROM table1 as t1 INNER JOIN table2 as t2 ON (t1.state_id = t2.id);

Obviously you will have to modify this example since I don't have the exact
info 
for your tables (such as names).

You can find more info on joins here:

http://dev.mysql.com/doc/mysql/en/join.html

Also, you can alias the columns as well, to make them easier to reference in

your code ie. t1.name as name

 
   td align=center?php echo $row_Recordset1['botgard_name'];
 ?/td td align=center?php echo
 $row_Recordset1['botgard_address']; ?/td td
 align=center?php echo $row_Recordset1['botgard_town'];
 ?/td td align=center?php echo
 $row_Recordset1['state_id']; ?/td td align=center?php
 echo $row_Recordset1['botgard_zip']; ?/td td
 align=center?php echo $row_Recordset1['botgard_phone']; ?/td
 td align=center?php echo $row_Recordset1['botgard_url']; ?/td
 
 works fine, pulling records out of the 'gardens' table and displaying
 it on a web page, but, of course, I'm seeing the 'state_id' instead of
 either the 'state_abbreviation' or the 'state_name'.
 
 I am at a complete and total loss as to how to edit my code at this
 point so that, before displaying anything, i can grab either
 'state_abbr' or 'state_name' from the states table, properly
 associated with the 'state_id' and display the actual state
 abbreviation or state name in my web page.
 
 I'm so close, yet so far.  I know if I can be shown once how this
 works, I'll be able to apply the solution again in the future.
 
 Thanks in advance for any help with this.
 
 Sincerely,
 
 Joshua

-- 
Tom Crimmins
Interface Specialist
Pottawattamie County, Iowa

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Easy newbie question re: option file and passwords

[Gleb Paharenko]

Hello.



The mysql program uses user sebyte and password for  user ddj.

When mysql starts it calls for load_defaults(), which put

the arguments from your config file before the command line options

that you specified. And than uses the last given argument. It looks like

mysql was invoked with -hlocalhost -uroot -px -hlocalhost -usebyte 







Sebastian Tennant [EMAIL PROTECTED] wrote:

 Hi there,

 

 For some reason, despite entering my passwords in ~/.my.cnf, I still have

 to enter a password on the command line to login to mysql.  I have tried

 this with each of the accounts I have created and all return the same

 error:

 

 $ mysql -u sebyte

 ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)

 

 What am I doing wrong?  Here are the accounts I have created, and below

 that is my ~/.my.cnf.  I have 'x'ed out my actual passwords in this post

 but is there something wrong with the syntax I am using.  I have verified

 that the file IS being read each time I attempt to login.

 

 Any help much appreciated.

 

 TIA

 

 Sebastian

 

 mysql select host,user,password from user;

 +---+--+--+

 | host  | user | password |

 +---+--+--+

 | localhost | root | 4be7c493348ee750 |

 | localhost | guest|  |

 | localhost | debian-sys-maint | 7985ba067899ea77 |

 | localhost | sebyte   | 5722c7a41e81cbb3 |

 | localhost | ddj  | 7b17b74d22ac2a88 |

 +---+--+--+

 5 rows in set (0.08 sec)

 

 # -*- mode: shell-script -*-

 # ~/.my.cnf

 #

 

 [mysql]

 host=localhost

 user=root

 password='xx'

 

 host=localhost

 user=sebyte

 password='x'

 

 host=localhost

 user=ddj

 password='xxx'

 

 [mysqladmin]

 host=localhost

 user=root

 password='xx'

 

 

 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   ___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Easy newbie question re: option file and passwords

[Sebastian Tennant]

On Fri, 28 Jan 2005 09:47:24 +0200, Gleb Paharenko wrote:

 Hello.
 
 The mysql program uses user sebyte and password for  user ddj.
 When mysql starts it calls for load_defaults(), which put
 the arguments from your config file before the command line options
 that you specified. And than uses the last given argument. It looks like
 mysql was invoked with -hlocalhost -uroot -px -hlocalhost -usebyte 

I see... 

Thanks a lot.

Sebastian



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Easy newbie question re: option file and passwords

[Sebastian Tennant]

Hi there,

For some reason, despite entering my passwords in ~/.my.cnf, I still have
to enter a password on the command line to login to mysql.  I have tried
this with each of the accounts I have created and all return the same
error:

$ mysql -u sebyte
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)

What am I doing wrong?  Here are the accounts I have created, and below
that is my ~/.my.cnf.  I have 'x'ed out my actual passwords in this post
but is there something wrong with the syntax I am using.  I have verified
that the file IS being read each time I attempt to login.

Any help much appreciated.

TIA

Sebastian

mysql select host,user,password from user;
+---+--+--+
| host  | user | password |
+---+--+--+
| localhost | root | 4be7c493348ee750 |
| localhost | guest|  |
| localhost | debian-sys-maint | 7985ba067899ea77 |
| localhost | sebyte   | 5722c7a41e81cbb3 |
| localhost | ddj  | 7b17b74d22ac2a88 |
+---+--+--+
5 rows in set (0.08 sec)

# -*- mode: shell-script -*-
# ~/.my.cnf
#

[mysql]
host=localhost
user=root
password='xx'

host=localhost
user=sebyte
password='x'

host=localhost
user=ddj
password='xxx'

[mysqladmin]
host=localhost
user=root
password='xx'



-- 
CC me by all means but a follow-up will usually do.





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Newbie-question: Error when compiling MySQL 4.1.9 with BerkeleyDB 4.3.27

[Harry Rter]

Hi,
greetings to the list.
I'm in process of compiling MySQL 4.1.9 using BerkeleyDB 4.3.27.
When i start make, after configuring i get the following
error-message :
--snipp--
ha_berkeley.cc:103: error: type specifier omitted for parameter `db_notices'
ha_berkeley.cc:103: error: syntax error before `)' token
--snipp--
As db_notices is nowhere declared i
look around in the sources and find the following in
/bdb/build_win32/db.h :
--snipp--
typedef enum
{
  DB_NOTICE_LOGFILE_CHANGED
} db_notices;
--snipp--
So i put it into ha_berkeley.h
and it seems to work , but then the next errormessages
come up , which haven't such a simple solution :
--snipp--
ha_berkeley.cc:139: error: invalid conversion from `void (*)(const char*,
   char*)' to `void (*)(const DB_ENV*, const char*, const char*)'
ha_berkeley.cc:141: error: `set_noticecall' undeclared (first use this
   function)
ha_berkeley.cc:141: error: (Each undeclared identifier is reported only once
   for each function it appears in.)
ha_berkeley.cc:150: error: `DB_VERB_CHKPOINT' undeclared (first use this
   function)
ha_berkeley.cc: In function `int berkeley_commit(THD*, void*)':
ha_berkeley.cc:215: error: `txn_commit' undeclared (first use this function)
ha_berkeley.cc: In function `int berkeley_rollback(THD*, void*)':
ha_berkeley.cc:228: error: `txn_abort' undeclared (first use this function)
ha_berkeley.cc: At global scope:
ha_berkeley.cc:291: error: type specifier omitted for parameter `db_notices'
ha_berkeley.cc:291: error: syntax error before `)' token
ha_berkeley.cc: In function `void berkeley_noticecall(...)':
ha_berkeley.cc:293: error: `notice' undeclared (first use this function)
ha_berkeley.cc:295: error: `DB_NOTICE_LOGFILE_CHANGED' undeclared (first use
   this function)
ha_berkeley.cc: In function `int berkeley_cmp_packed_key(DB*, const
DBT*, const
   DBT*)':
ha_berkeley.cc:387: error: 'const struct __db_dbt' has no member named '
   app_private'
ha_berkeley.cc:387: error: 'const struct __db_dbt' has no member named '
   app_private'
ha_berkeley.cc: In member function `virtual int ha_berkeley::open(const
char*,
   int, unsigned int)':
ha_berkeley.cc:547: error: `txn_begin' undeclared (first use this
function ..
--snipp (and so on)  --
So, does anybody know a solution for this ?
My setup :
Suse Linux 9.1
BDB 4.3.27(installed in /usr/local/bdb-4.3.9, symlinked to
   /usr/local/bdb-4.3)
MySQL 4.1.9
Here's how i configure (i always create a little script):
--snipp--
#!/bin/sh
#
# configure  make template-script
#
# generated 2005.01.19 19:24,06 by make-mk
#
# (c) H. Rueter 01/2005
#
CPPFLAGS=-I/usr/local/bdb-4.3/include
LDFLAGS=-L/usr/local/bdb-4.3/lib
export CPPFLAGS LDFLAGS
make clean
rm config.cache
./configure \
--prefix=/usr/local/mysql-4.1.9 \
--mandir=/usr/man \
--enable-shared \
--enable-static \
--enable-thread-safe-client \
--enable-assembler \
--enable-local-infile \
--with-isam \
--with-pthread \
--with-unix-socket-path=/var/run/mysql.sock \
--with-mysqld-user=mysql \
--with-openssl=/usr \
--with-extra-charsets=all \
--with-berkeley-db=/usr/local/bdb-4.3 \
--with-berkeley-db-includes=/usr/local/bdb-4.3/include \
--with-berkeley-db-libs=/usr/local/bdb-4.3/lib \
 make  checkinstall
--snipp--
Thanks in advance
Harry
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Newbie question: segmentation fault in mysql_init

[Paul DuBois]

At 19:32 +0100 12/13/04, Alina BiŸkowska wrote:
Description:
When I try to call mysql_init() several times in 
my program it finishes with segmentation fault.
This happens in different places of my program but always in mysql_init().
This is the gdb output:

#0  0x007ba6ae in malloc_consolidate () from /lib/tls/libc.so.6
#1  0x007b9e6a in _int_malloc () from /lib/tls/libc.so.6
#2  0x007b925d in malloc () from /lib/tls/libc.so.6
#3  0x001716ae in my_malloc () from /usr/local/lib/mysql/libmysqlclient.so.12
#4  0x0016c648 in mysql_init () from /usr/local/lib/mysql/libmysqlclient.so.12
#5  0x0804dee2 in myconnect () at databaseFunctions.c:14
#6  0x0804dfc0 in give_geneValue_with_key 
(table=0x8051fe8 \selfRoot_shell\, id=9463)
at databaseFunctions.c:37
#7  0x08049a27 in selfProfiler () at library.c:232
#8  0x0804952c in negativeSelection () at library.c:176
#9  0x0804a92d in negativeSelectionSentinel () at library.c:420
#10 0x0804edad in communicator (arg=0x755d00) at communicator.c:60
#11 0x08048cdf in main () at primary.c:169

Here are the functions I use.
The first function is only to make a connection to database:
MYSQL* myconnect()
{
   MYSQL *my_connection=malloc(sizeof(MYSQL *));
my_connection=mysql_init (NULL);
I don't think this solves your problem, but I'm curious:
Why do you allocate memory and assign it to my_connection, and then
immediately throw that memory away by assigning my_connection the value
of mysql_init()?  That's a memory leak right there.

if(mysql_real_connect (my_connection, 
\130.225.16.5\, \ala\, \alaSdb\, \ala\, 
0, NULL, 0))
  {
 return my_connection;
   }
   else
   {
 fprintf (stderr, \Connection failed !!!\\n\);
 if (mysql_errno (my_connection))
 {
	 		fprintf (stderr, 
\Connection error %d: %s\\n\, mysql_errno 
(my_connection), mysql_error (my_connection));
			return NULL;
		}
   	}
 	return NULL;
}

This function and a couple of others similar are 
to withdraw some data from database. All of them 
use myconnection() function;

char* give_geneValue_with_key(char *table,int id)
{
  MYSQL* my_connection=myconnect();
  MYSQL_RES *result=malloc(sizeof(MYSQL_RES*));
  MYSQL_ROW row;
  char temp[1];
 sprintf(temp,\SELECT geneValue FROM  %s where id=\'%d\'\,table,id);
 if(mysql_real_query (my_connection,temp,strlen(temp))) return \0\;
 if(!(result=mysql_store_result(my_connection))) return \0\;
 row=mysql_fetch_row(result);
 mysql_free_result(result);
 mysql_close(my_connection);
 return row[0];
}
If you have any ideas what can be a problem, please let me know.
Alina Binkowska

--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Newbie question: segmentation fault in mysql_init

[Alina Bikowska]

Description:
When I try to call mysql_init() several times in my program it finishes with 
segmentation fault.
This happens in different places of my program but always in mysql_init(). 
This is the gdb output:

#0  0x007ba6ae in malloc_consolidate () from /lib/tls/libc.so.6
#1  0x007b9e6a in _int_malloc () from /lib/tls/libc.so.6
#2  0x007b925d in malloc () from /lib/tls/libc.so.6
#3  0x001716ae in my_malloc () from /usr/local/lib/mysql/libmysqlclient.so.12
#4  0x0016c648 in mysql_init () from /usr/local/lib/mysql/libmysqlclient.so.12
#5  0x0804dee2 in myconnect () at databaseFunctions.c:14
#6  0x0804dfc0 in give_geneValue_with_key (table=0x8051fe8 \selfRoot_shell\, 
id=9463)
at databaseFunctions.c:37
#7  0x08049a27 in selfProfiler () at library.c:232
#8  0x0804952c in negativeSelection () at library.c:176
#9  0x0804a92d in negativeSelectionSentinel () at library.c:420
#10 0x0804edad in communicator (arg=0x755d00) at communicator.c:60
#11 0x08048cdf in main () at primary.c:169


Here are the functions I use.
The first function is only to make a connection to database:

MYSQL* myconnect()
{
   MYSQL *my_connection=malloc(sizeof(MYSQL *)); 
my_connection=mysql_init (NULL);

if(mysql_real_connect (my_connection, \130.225.16.5\, \ala\, \alaSdb\, 
\ala\, 0, NULL, 0))
  {
 return my_connection;
   }
   else
   {
 fprintf (stderr, \Connection failed !!!\\n\);
 if (mysql_errno (my_connection))
 {
fprintf (stderr, \Connection error %d: %s\\n\, 
mysql_errno (my_connection), mysql_error (my_connection));
return NULL;
}
}
return NULL;
}

This function and a couple of others similar are to withdraw some data from 
database. All of them use myconnection() function;

char* give_geneValue_with_key(char *table,int id)
{

  MYSQL* my_connection=myconnect();
  MYSQL_RES *result=malloc(sizeof(MYSQL_RES*));
  MYSQL_ROW row;
  char temp[1];
 sprintf(temp,\SELECT geneValue FROM  %s where id=\'%d\'\,table,id);
  
 if(mysql_real_query (my_connection,temp,strlen(temp))) return \0\;
 if(!(result=mysql_store_result(my_connection))) return \0\;
 row=mysql_fetch_row(result);
 mysql_free_result(result);
 mysql_close(my_connection);
 return row[0];
}

If you have any ideas what can be a problem, please let me know.
Alina Binkowska

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Newbie question about index (why are they not updating?)

[Gleb Paharenko]

Hello.

Cardinality of index is updated by running ANALYZE TABLE or
myisamchk -a. See:
  http://dev.mysql.com/doc/mysql/en/SHOW_INDEX.html


I've created a table with several indexes as follows:

$query = CREATE TABLE `data_raw` (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
run_id VARCHAR(20) DEFAULT 'error_internal' NOT NULL,
time_run DATETIME DEFAULT '-00-00 00:00:00' NOT NULL,
...clip.
PRIMARY KEY (id),
INDEX x_run_id (run_id),
INDEX x_comp_code (comp_code),
INDEX x_time_run (time_run),
INDEX x_url (url)
) COMMENT = 'Raw data samples'
;

$query executed with php mysql_query.

The table is created OK.

I add many records to the table with

$query = INSERT INTO data_raw( id, run_id, time_run, time_sample,

comp_code, url, url_index, err_number, err_desc, err_src ) VALUES( 0,

'$run_id', '$time_run', '$ts', '$data[1]', '$data[2]', $data[3],
$data[4], '$data[5]', '$data[6]' );

Data is added OK.

But, the indexes are not updated! Running myPHPAdmin shows:

Indexes: Documentation
Keyname  Type Cardinality Action Field
PRIMARY   PRIMARY 12932  Edit Drop id
x_run_id   INDEX None  Edit Drop
run_id
x_comp_code  INDEX None  Edit Drop comp_code
x_time_run   INDEX None  Edit Drop
time_run
x_url INDEX None  Edit Drop
url

Access is slow. If I do an ALTER TABLE ... ADD INDEX ... indexes are
updated and subsequent INSERTS update the indexes. I'm sure I'm doing
something simple wrong but need a clue as to what?
Any help much appreciated.

Richard Bell [EMAIL PROTECTED] wrote:


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   ___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Newbie question about index (why are they not updating?)

[Richard Bell]

I've created a table with several indexes as follows:

$query = CREATE TABLE `data_raw` (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
run_id VARCHAR(20) DEFAULT 'error_internal' NOT NULL,
time_run DATETIME DEFAULT '-00-00 00:00:00' NOT NULL,
...clip.
PRIMARY KEY (id),
INDEX x_run_id (run_id),
INDEX x_comp_code (comp_code),
INDEX x_time_run (time_run),
INDEX x_url (url)
) COMMENT = 'Raw data samples'
;

$query executed with php mysql_query.

The table is created OK.

I add many records to the table with

$query = INSERT INTO data_raw( id, run_id, time_run, time_sample,

comp_code, url, url_index, err_number, err_desc, err_src ) VALUES( 0,

'$run_id', '$time_run', '$ts', '$data[1]', '$data[2]', $data[3],
$data[4], '$data[5]', '$data[6]' );

Data is added OK.

But, the indexes are not updated! Running myPHPAdmin shows:

Indexes: Documentation
Keyname  Type Cardinality Action Field
PRIMARY   PRIMARY 12932  Edit Drop id
x_run_id   INDEX None  Edit Drop
run_id
x_comp_code  INDEX None  Edit Drop comp_code
x_time_run   INDEX None  Edit Drop
time_run
x_url INDEX None  Edit Drop
url

Access is slow. If I do an ALTER TABLE ... ADD INDEX ... indexes are
updated and subsequent INSERTS update the indexes. I'm sure I'm doing
something simple wrong but need a clue as to what?
Any help much appreciated.

 

 

Newbie question about web users

[Matthew Benton]

Hello all,
 
Basic question about creating a database that will be accessed by many users. Do I 
create one web user account with no password in the mysql.user table or add each user 
to the mysql.user table as they join up to my site? (Lots of books and documentation 
says how to create users, the privelege system etc, but none seem to address this 
basic structural question - presumably because it's obvious to anyone who already 
knows). I'm planning a mysql and PHP site.
 
Taking a mundane example - lets say a second hand bookstore has a site which lets 
users search through a list of books, and lets subscribed users select favorite books, 
0-n number of books, in a table fav_books. Each fav_book tuple is then related to at 
least one user.  When the favourite book comes into stock the store sends a message to 
the user to let them know it's available. (I said it was a mundane example!).
 
If the bookstore created a default user then I suppose anyone can alter anyone elses 
data in the fav_book table (undesirable) ? Would they create a table of 
authenticated_users for example instead of putting them in the mysql.user table, then 
use PHP to retain a record of which user is logged in at that time and so which tuples 
in the fav_book table they can change? Alternatively if each user is listed in 
mysql.user table they would all get the same priveleges and the bookstore would have 
to create a seperate fav_book table for each user to contain just their data. With as 
many tables as users it would be surely impossible to run background processes which 
check the new stock against books people are looking for?
 
As I say it's a basic question about web use of a database - hope the answer isn't too 
obvious.
 
Thanks for any help
matt


-
 ALL-NEW Yahoo! Messenger - all new features - even more fun!  

Re: Newbie question about web users

[SGreen]

It's not a simple question at all.

Your users actually DON'T talk to the database, your PHP application does. 
Your users converse with your PHP code. Your web server will have one 
layer of authentication just to allow the user to view the pages your site 
is composed of.  Assume for a second  that anyone can see any page on your 
site (no security), you can still set up application-level security by 
requiring your users to enter more information once they get to your site 
(like a login screen). Where you store your login information is up to you 
but since you already have a MySQL database at your disposal you can put 
it in there.

Since your application needs to be able to verify that a particular 
username/password combination is valid running a query against the 
database, it doesn't make it try to login with the user's credentials in 
order to verify those credentials. When I am designing web-based 
applications I usually need only 2 logins. One will be used by the 
application for read-only access. The second will be used only when the 
application needs to update something.  Notice I was talking about the 
application doing those things. Most users won't get direct access to your 
applicaiton data (usually) as it's only there to run the application. 

Now, within a MySQL database can be a set of tables (that you design) that 
contain everything your application needs to know about each user's 
account information, login permissions, and any other rights they have 
within the application (among other things).  This will be information 
used by your website to decide if a user has the correct privileges to 
perform some kind of action. Your application will use one of *its* logins 
to connect to that database in order to add, edit, or delete rows from 
those tables. The users don't interact with this data.

So, user security basically boils down to two layers. The first layer is 
determined by the security settings on the web server. The second is 
managed by your application code.  By keeping user security and 
application security separate, you improve the chances that your users 
will be unable to read your databases directly. Keep reading and 
researching. There are a lot of things to worry about when you start 
locking-down a web-based application.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Matthew Benton [EMAIL PROTECTED] wrote on 11/04/2004 03:53:57 PM:

 Hello all,
 
 Basic question about creating a database that will be accessed by 
 many users. Do I create one web user account with no password in the
 mysql.user table or add each user to the mysql.user table as they 
 join up to my site? (Lots of books and documentation says how to 
 create users, the privelege system etc, but none seem to address 
 this basic structural question - presumably because it's obvious to 
 anyone who already knows). I'm planning a mysql and PHP site.
 
 Taking a mundane example - lets say a second hand bookstore has a 
 site which lets users search through a list of books, and lets 
 subscribed users select favorite books, 0-n number of books, in a 
 table fav_books. Each fav_book tuple is then related to at least one
 user.  When the favourite book comes into stock the store sends a 
 message to the user to let them know it's available. (I said it was 
 a mundane example!).
 
 If the bookstore created a default user then I suppose anyone can 
 alter anyone elses data in the fav_book table (undesirable) ? Would 
 they create a table of authenticated_users for example instead of 
 putting them in the mysql.user table, then use PHP to retain a 
 record of which user is logged in at that time and so which tuples 
 in the fav_book table they can change? Alternatively if each user is
 listed in mysql.user table they would all get the same priveleges 
 and the bookstore would have to create a seperate fav_book table for
 each user to contain just their data. With as many tables as users 
 it would be surely impossible to run background processes which 
 check the new stock against books people are looking for?
 
 As I say it's a basic question about web use of a database - hope 
 the answer isn't too obvious.
 
 Thanks for any help
 matt
 
 
 -
  ALL-NEW Yahoo! Messenger - all new features - even more fun! 

Re: Newbie question about web users

[John McCaskey]

The answer is you create one user for the PHP (webserver) process to
use.

Then you keep track of user permissions in your application code.  This
probably involves creating your own user table within your own database
and storing users of your site there.  Then in the other tables you
associate items with users, like in your fav_book table one of the
fields would be user_id, and would allow you to figure out which user
the favorite entry belonged to.

You do not create mysql users to represent application users however as
the users don't access the database, the webserver does.

On Thu, 2004-11-04 at 20:53 +, Matthew Benton wrote:
 Hello all,
  
 Basic question about creating a database that will be accessed by many users. 
 Do I create one web user account with no password in the mysql.user table or 
 add each user to the mysql.user table as they join up to my site? (Lots of 
 books and documentation says how to create users, the privelege system etc, 
 but none seem to address this basic structural question - presumably because 
 it's obvious to anyone who already knows). I'm planning a mysql and PHP site.
  
 Taking a mundane example - lets say a second hand bookstore has a site which 
 lets users search through a list of books, and lets subscribed users select 
 favorite books, 0-n number of books, in a table fav_books. Each fav_book 
 tuple is then related to at least one user.  When the favourite book comes 
 into stock the store sends a message to the user to let them know it's 
 available. (I said it was a mundane example!).
  
 If the bookstore created a default user then I suppose anyone can alter 
 anyone elses data in the fav_book table (undesirable) ? Would they create a 
 table of authenticated_users for example instead of putting them in the 
 mysql.user table, then use PHP to retain a record of which user is logged in 
 at that time and so which tuples in the fav_book table they can change? 
 Alternatively if each user is listed in mysql.user table they would all get 
 the same priveleges and the bookstore would have to create a seperate 
 fav_book table for each user to contain just their data. With as many tables 
 as users it would be surely impossible to run background processes which 
 check the new stock against books people are looking for?
  
 As I say it's a basic question about web use of a database - hope the answer 
 isn't too obvious.
  
 Thanks for any help
 matt
 
   
 -
  ALL-NEW Yahoo! Messenger - all new features - even more fun!  
-- 
John A. McCaskey
Software Development Engineer
Klir Technologies, Inc.
[EMAIL PROTECTED]
206.902.2027

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Newbie question on multiple tables

[Gleb Paharenko]

Hi.

You may read:

  http://dev.mysql.com/doc/mysql/en/JOIN.html

  http://dev.mysql.com/doc/mysql/en/SELECT.html



There is a good description.

Searching in more than two tables (3 in my example) may be done

in such way:



  select t1.* from t1,t2,t3 where t1.a=t2.a and t1.b=t3.b



But using left or inner joins depends on results you are expecting. 





Steve Grosz [EMAIL PROTECTED] wrote:

 I am just now getting the hang of searching through 2 tables, and 

 getting the corresponding information using a pri  foreign key 

 (ID1=ID1) kinda thing.  How is this done with more than 2 tables?

 

 I had asked on a different forum, but I got some funky select statement 

 using a left inner join, and lots of other statements that didn't make a 

 lot of sense to me.

 

 Is that the way it would need to be done?  A inner join?  If so why?

 

 Thanks!

 

 Steve

 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   ___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Newbie question on multiple tables

[Steve Grosz]

I am just now getting the hang of searching through 2 tables, and 
getting the corresponding information using a pri  foreign key 
(ID1=ID1) kinda thing.  How is this done with more than 2 tables?

I had asked on a different forum, but I got some funky select statement 
using a left inner join, and lots of other statements that didn't make a 
lot of sense to me.

Is that the way it would need to be done?  A inner join?  If so why?
Thanks!
Steve
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

**newbie question** renaming a database

[Carolina]

using mysql version 4.0.13
- **total newbie here**... something as simple as renaming a
database...i've not found anything relating to that on the mysql site
or anywhere else. what i have found is that in order to do this, i'd
have to shutdown and restart the mysql database in order to do
something as simple as this. is there another way? as mysql user
'root' i've tried:

mysql rename database old_db to new_db


thnx,
cheers!
-a

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: **newbie question** renaming a database

[gerald_clark]

Carolina wrote:
using mysql version 4.0.13
- **total newbie here**... something as simple as renaming a
database...i've not found anything relating to that on the mysql site
or anywhere else. what i have found is that in order to do this, i'd
have to shutdown and restart the mysql database in order to do
something as simple as this. is there another way? as mysql user
'root' i've tried:
mysql rename database old_db to new_db
thnx,
cheers!
-a
 

You may think this is simple, but you have to make sure all users are 
out of the database, and all records are flushed
before it could be renamed.  It is much safer to shutdown the server and 
rename the directory.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: **newbie question** renaming a database

[Paul DuBois]

At 11:16 -0500 10/7/04, gerald_clark wrote:
Carolina wrote:
using mysql version 4.0.13
- **total newbie here**... something as simple as renaming a
database...i've not found anything relating to that on the mysql site
or anywhere else. what i have found is that in order to do this, i'd
have to shutdown and restart the mysql database in order to do
something as simple as this. is there another way? as mysql user
'root' i've tried:
mysql rename database old_db to new_db
thnx,
cheers!
-a

You may think this is simple, but you have to make sure all users 
are out of the database, and all records are flushed
before it could be renamed.  It is much safer to shutdown the server 
and rename the directory.
It's simple conceptually, but complex to actually implment.  In addition
to the issues gerald mentions, remember that if you have InnoDB tables,
they aren't actually stored in the database directory unless you're
using individual tablespaces -- and even then, there is an entry for
them in the InnoDB data dictionary that is stored in the shared tablespace.
And those entries include the database name.  If you rename the database
directory, those entries become invalid.  Also, if you have foreign key
relationship, there are similar difficulties.
Might be better to create a new database and then RENAME TABLE each table
from the original database into the new database.  Then drop the original
database.
The strategy of shutting down the server, renaming the database directory,
and restartingg the server does work fine _if_ your database contains only
MyISAM (or ISAM) tables, though.
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Newbie question - Input limitations on mysql client?

[Ted Byrne]

As I mentioned in my post, I did wind up using a perl script, and I will 
continue down that path in the future.  I was mainly wondering about limits 
to the mysql client for quick-n-dirty testing (as someone who is learning 
MySQL, as opposed to developing for a production environment).

Thanks for pointing out the mysqlimport tool - that sounds like what I 
should really be using instead of cut-n-paste...

Ted
At 08:08 PM 9/29/2004, Andrew Kreps wrote:
On Wed, 29 Sep 2004 10:05:29 -0400, Ted Byrne [EMAIL PROTECTED] wrote:

 Is the apparent line length limit a restriction imposed by the client? (And
 does it apply if you are piping or redirecting output from another process
 or a file?)

It sounds like you should either be using a scripting language (like
Perl or PHP), or using the command line tool mysqlimport or LOAD DATA
INFILE from the mysql client.  Any of those methods should allow you
to insert long rows.
--
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]

Newbie question - Input limitations on mysql client?

[Ted Byrne]

Greetings,
I posted this to the MySQL Newbie forum, but have not received a 
response.  Any feedback would be appreciated.
-tb

Environment:
mysql server v4.0.13 (Win2K)
mysql client v5.0.0.0(Win2K)
In attempting to (manually) insert records with some rather lengthy 
strings, I pasted the insert statements into the client window; I wound up 
getting mismatched quotes, although the original statements that I copied 
were properly quoted.

After taking a closer look at what was happening, it appeared that the 
lines being pasted in were getting truncated at about 256 characters. I was 
able to eliminate most of the errors by splitting the insert statements 
across multiple lines (but wound up with newline characters mid-string in 
some of the values). Eventually, I wound up including the inserts in a perl 
script, which was successful

Is the apparent line length limit a restriction imposed by the client? (And 
does it apply if you are piping or redirecting output from another process 
or a file?)

Thanks in advance,
Ted 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Newbie question - Input limitations on mysql client?

[Andrew Kreps]

On Wed, 29 Sep 2004 10:05:29 -0400, Ted Byrne [EMAIL PROTECTED] wrote:
 
 Is the apparent line length limit a restriction imposed by the client? (And
 does it apply if you are piping or redirecting output from another process
 or a file?)
 

It sounds like you should either be using a scripting language (like
Perl or PHP), or using the command line tool mysqlimport or LOAD DATA
INFILE from the mysql client.  Any of those methods should allow you
to insert long rows.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Newbie question about blob data types ie. Storing .xls in blob

[Jack Taffar]

Is it possible to store an excel file into a blob field in mysql?  If so how
do I go about importing the file into the blob field, and get it back out
into an .xls file?


Thanks in advnace

Jack Taffar
AOG





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Newbie question about blob data types ie. Storing .xls in blob

[DreamWerx]

Good binary storage article w/ sample code:

http://php.dreamwerx.net/forums/viewtopic.php?t=6


On Mon, 27 Sep 2004 16:47:37 -0500, Jack Taffar [EMAIL PROTECTED] wrote:
 
 Is it possible to store an excel file into a blob field in mysql?  If so how
 do I go about importing the file into the blob field, and get it back out
 into an .xls file?
 
 Thanks in advnace
 
 Jack Taffar
 AOG
 
 --
 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 question on scrolling through a table one record at a time

[Kerry Frater]

Many thanks for your reply Tom.

I will read it more closely over the week-end, but wanted to say thanks
straight away.

Kerry

-Original Message-
From: Tom Horstmann [mailto:[EMAIL PROTECTED]
Sent: 20 August 2004 15:52
To: 'Kerry Frater'; 'MySQL List'
Subject: RE: newbie question on scrolling through a table one record at
a time


Hi Kerry,

 The problem is more fundamental with the scrolling through
 the records/rows of Master. [..] It is not sensible to allow
 200 million pieces of data to be transferred to the Delphi PC
 to build a local Dataset to scroll through.

[..]
 Getting the first row is easy

 select * from Master order by MasterRef limit 1;

It is, but LIMIT mostly is executed after getting all records
meeting the WHERE-condition. That might be quite slow.


 but getting the next record isn't as straight forward. Mainly
 because I have no idea of what the next value of MasterRef
 is. All I know is that I want to get the next row in
 sequence. None of the papers I have or have seen addresses
 this issue. Either this concept is not required in SQL
 programming or it is so obvious that it doesn't need
 explaining. Either way I can't see the wood for the trees.

 If MasterRef is a unique value column then the next record  would be:

 select * from Master order by MasterRef limit 1 where
 MasterRef  MyCurrentMasterRefValue;

 This simply raises questions 1) how to get the previous row
 (presume you use the DESCENDING keyword of the table, 2) how
 to test for Begining and End of Table and 3) what to do if
 the column being ordered on is not unique.

I ran through the same problem the last days (still). This is
how i did it: At first you need a unique key (auto-increment).
I name it id. If you have records that are non-unique by the
column you want to sort them, try to add other columns to sort
on to get them as unique as you can. Having non-unique records
isn't a problem as long there are not many beeing equal.

How to move through records:
Starting with a value of '', always SELECT the record having a
higher value in that columns you sorted on than the last one.
As you said, that's all, if there all records are unique.

You will need LIMIT to get through equal records. It's quite
easy having an example:

record   key
1a
2a
3b
4b
5c

Get the first record (assuming your id starts with 0):
SELECT key FROM table WHERE key='' AND id-1
ORDER BY key,id LIMIT 0,1

Store these:
old_key = key
old_id  = id

Get the next record:
old_key = SELECT key FROM table WHERE key=old_key AND idold_id
ORDER BY key,id LIMIT 1,1

The LIMIT has to be 1 here to get the second record. As long as key
stays equal to old_key, increase the limit by one for each record
fetched. When getting a new value for key, set the limit to 1 again.

You need to find a good relation between the amount of equal records
and the columns you use to sort and move on. 500 records having the
same key will make things slow, but using a long WHERE- clause will
do, too.
I tested this for upto 3 records. If there is an index on all
columns used in the SELECTs, speed seems to be stable upto that size.

hth,

TomH

--
PROSOFT EDV-Loesungen GmbH  Co. KGphone: +49 941 / 78 88 7 - 121
Ladehofstrasse 28, D-93049 Regensburg cellphone: +49 174 / 41 94 97 0
Geschaeftsfuehrer: Axel-Wilhelm Wegmann  [EMAIL PROTECTED]
AG Regensburg HRA 6608 USt.183/68311http://www.proSoft-Edv.de
--



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

newbie question on scrolling through a table one record at a time

[Kerry Frater]

Hi all,
I don't think this is the right list for the question but I am hoping
someone in the list will be able to point me in the right direction.

I am testing the use of Delphi with MySQL (using Micoolap's DAC) to access
and manipulate a number of tables in a databse. Some of the more complex
structures I want to do are quite clear on how to implement them with many
papers and books published. My problem is the technique on implementing a
far more fundamental issue.

Let us say I have a couple of tables with a large number of rows (Master and
Detail) with a common (indexed for performance) column MasterRef. Getting
the rows from table Detail is straight forward by using a Query

select * from Detail where Master.MasterRef = Detail.MasterRef

The problem is more fundamental with the scrolling through the records/rows
of Master. Reading previously posted information, it comes to light that if
I open a table to scroll through using an application navigator then the app
creates and uses a local dataset. Not a big issue if the database is local,
on a high speed connection, or has a relatively small number of rows. But
what if Master has 1 million rows with 200 columns. It is not sensible to
allow 200 million pieces of data to be transferred to the Delphi PC to build
a local Dataset to scroll through.

I note there is the concept of LIMIT. This looks good until I try to see how
to implement its usage in the real world. The concept of creating an app
that only works on one (or a small number of rows) at a time is eluding me
at the moment. Getting the first row is easy

select * from Master order by MasterRef limit 1;

but getting the next record isn't as straight forward. Mainly because I have
no idea of what the next value of MasterRef is. All I know is that I want to
get the next row in sequence. None of the papers I have or have seen
addresses this issue. Either this concept is not required in SQL programming
or it is so obvious that it doesn't need explaining. Either way I can't see
the wood for the trees.

If MasterRef is a unique value column then the next record  would be:

select * from Master order by MasterRef limit 1 where MasterRef 
MyCurrentMasterRefValue;

This simply raises questions 1) how to get the previous row (presume you use
the DESCENDING keyword of the table, 2) how to test for Begining and End of
Table and 3) what to do if the column being ordered on is not unique.

Are there any known papers, documents, references, books etc that go through
these issues.

Or can someone tell me that the posts I have been reading are no longer
applicable and that when I program using Table components that it doesn't
download a complete large dataset and that I only get one row at a time
which takes away the concern, and the need to manage the data scroll
directly. JOIN is not an option because in my project one form can have up
to 9 DETAIL tables showing with the Master table.

I am at the point where I have done a lot of reading and now want to look at
the reality of implementation. Which means I have a little knowledge which
is a dangerous thing. I want to change that status.

Many thanks

Kerry

RE: newbie question on scrolling through a table one record at a time

[Tom Horstmann]

Hi Kerry,

 The problem is more fundamental with the scrolling through 
 the records/rows of Master. [..] It is not sensible to allow 
 200 million pieces of data to be transferred to the Delphi PC 
 to build a local Dataset to scroll through.
 
[..]
 Getting the first row is easy
 
 select * from Master order by MasterRef limit 1;

It is, but LIMIT mostly is executed after getting all records
meeting the WHERE-condition. That might be quite slow.


 but getting the next record isn't as straight forward. Mainly 
 because I have no idea of what the next value of MasterRef 
 is. All I know is that I want to get the next row in 
 sequence. None of the papers I have or have seen addresses 
 this issue. Either this concept is not required in SQL 
 programming or it is so obvious that it doesn't need 
 explaining. Either way I can't see the wood for the trees.
 
 If MasterRef is a unique value column then the next record  would be:
 
 select * from Master order by MasterRef limit 1 where 
 MasterRef  MyCurrentMasterRefValue;
 
 This simply raises questions 1) how to get the previous row 
 (presume you use the DESCENDING keyword of the table, 2) how 
 to test for Begining and End of Table and 3) what to do if 
 the column being ordered on is not unique.

I ran through the same problem the last days (still). This is
how i did it: At first you need a unique key (auto-increment).
I name it id. If you have records that are non-unique by the
column you want to sort them, try to add other columns to sort
on to get them as unique as you can. Having non-unique records
isn't a problem as long there are not many beeing equal.

How to move through records:
Starting with a value of '', always SELECT the record having a
higher value in that columns you sorted on than the last one.
As you said, that's all, if there all records are unique.

You will need LIMIT to get through equal records. It's quite
easy having an example:

record   key
1a
2a
3b
4b
5c

Get the first record (assuming your id starts with 0):
SELECT key FROM table WHERE key='' AND id-1
ORDER BY key,id LIMIT 0,1

Store these:
old_key = key
old_id  = id

Get the next record:
old_key = SELECT key FROM table WHERE key=old_key AND idold_id
ORDER BY key,id LIMIT 1,1

The LIMIT has to be 1 here to get the second record. As long as key
stays equal to old_key, increase the limit by one for each record
fetched. When getting a new value for key, set the limit to 1 again.

You need to find a good relation between the amount of equal records
and the columns you use to sort and move on. 500 records having the
same key will make things slow, but using a long WHERE- clause will
do, too.
I tested this for upto 3 records. If there is an index on all
columns used in the SELECTs, speed seems to be stable upto that size.

hth,

TomH

-- 
PROSOFT EDV-Loesungen GmbH  Co. KGphone: +49 941 / 78 88 7 - 121
Ladehofstrasse 28, D-93049 Regensburg cellphone: +49 174 / 41 94 97 0
Geschaeftsfuehrer: Axel-Wilhelm Wegmann  [EMAIL PROTECTED]
AG Regensburg HRA 6608 USt.183/68311http://www.proSoft-Edv.de
-- 



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

newbie question: subareas

[Alice]

Just been using mysql for a couple of days, so this is possibly old ground,
but i have searched and can't find any solution. i'm sure it must have come
up before.

The project is to allow resources to be shared amongst community groups
throughout uk. there are a number of attributes that can be searched on, but
the one i'm having difficulty with is geographical area.

areas can also be subareas of other areas. for instance there could be areas
england, wales, newtown, london, lambeth, southwark so if someone
searches england then intuitively the subareas should be searched as
well...

i thought of a possible method, but its very weird and i'd appreciate
comments on it or suggestions for different ways.


area_id varchar(6);

england - A
wales - B
newtown - BA
london - AA
lambeth - AAA
southwark - AAB

and then searches could be as precise as required ...

another option, from a friend more experienced than me is:
have extra columns for
country/county/borough/town/street/etc

your front end for querying would eitehr present the options/boxes for
searching within the country/town etc.  or you could just present one
search box, which takes the search criterion, and checks each of the
different columns in turn, starting with the largest geographical type,
the country.


have aggregated indexes (country+country+town) wont get you anything, so
dont bother with that.

so to summise

create table locations
(
area_id serial,
country char(16),
country varchar(64),
borough varchar(64),
town varchar(64)
);

the problem wit that table layout, is you have no way of checking if for
twwo entries with different spelling/case.  e.g. London/london.  the
data values arent strongly checked.


cheers in advance.

=
-- 
ae
I give mankind no more than a chance in a thousand. But I would not be human if I did 
not place my stake on this one chance. Albert Camus

http://www.sei.ukshells.co.uk/





___ALL-NEW Yahoo! Messenger - 
all new features - even more fun!  http://uk.messenger.yahoo.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]