Re: [PHP-DB] Re: enum !!
Your first statement will not work. It is the same thing as saying select * from test where cat = 'c'. You need to use the % in order to find all rows which may contain 'c'. -Original Message- From: Miles Thompson <[EMAIL PROTECTED]> To: McShen <[EMAIL PROTECTED]>; [EMAIL PROTECTED] <[EMAIL PROTECTED]> Date: Monday, July 16, 2001 3:40 PM Subject: Re: [PHP-DB] Re: enum !! > >select * from test where cat like 'c' > >or, a little broader > >select *from text where cat like '%c%' > >Miles Thompson > >At 02:56 PM 7/16/01 -0400, McShen wrote: >>ok. I use set to make a table. here is my table 'test' >>+---+--+ >>| name | cat | >>+---+--+ >>| shen | a,c | >>| shen1 | b,c | >>+---+--+ >> >>How can I find all records that contain 'c'? >>i tried >>select * from test where cat='c'; >> >>it returned "empty set"; >> >>please help >> >> >>"Tom Hodder" <[EMAIL PROTECTED]> wrote in message >>[EMAIL PROTECTED]">news:[EMAIL PROTECTED]... >> > >> > First normal form (1NF), A table in which the intersection of every column >> > and record contains one, and only one, value. >> > >> > >> > >> > -Original Message- >> > From: Hugh Bothwell [mailto:[EMAIL PROTECTED]] >> > Sent: 16 July 2001 19:21 >> > To: [EMAIL PROTECTED] >> > Subject: [PHP-DB] Re: enum !! >> > >> > >> > >> > "McShen" <[EMAIL PROTECTED]> wrote in message >> > [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... >> > > how do i store multiple data in one field? >> > > i have a table like this >> > > >> > > +---+---+--+-+-+---+ >> > > | Field | Type | Null | Key | Default | Extra | >> > > +---+---+--+-+-+---+ >> > > | test | enum('a','b','c') | YES | | NULL| | >> > > +---+---+--+-+-+---+ >> > > >> > > insert into test values('a','b','c'); doens't work. please help >> > >> > Your query is mixed up; 'test' is the field name, not the table name, and >>I >> > think you want to put the values into three separate records, not one... >> > >> > Try >> > INSERT INTO tablename ( test ) VALUES ( 'a' ), ( 'b'), ('c'), (''); >> > >> > This should get you four records, with 'test' values of a, b, c, and null. >> > >> > If you want to store more than one value in the same record, you should be >> > looking at sets, not enums. >> > >> > >> > >> > -- >> > PHP Database Mailing List (http://www.php.net/) >> > To unsubscribe, e-mail: [EMAIL PROTECTED] >> > For additional commands, e-mail: [EMAIL PROTECTED] >> > To contact the list administrators, e-mail: [EMAIL PROTECTED] >> > >> > >> >> >> >>-- >>PHP Database Mailing List (http://www.php.net/) >>To unsubscribe, e-mail: [EMAIL PROTECTED] >>For additional commands, e-mail: [EMAIL PROTECTED] >>To contact the list administrators, e-mail: [EMAIL PROTECTED] > > >-- >PHP Database Mailing List (http://www.php.net/) >To unsubscribe, e-mail: [EMAIL PROTECTED] >For additional commands, e-mail: [EMAIL PROTECTED] >To contact the list administrators, e-mail: [EMAIL PROTECTED] > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Re: enum !!
thx alot! "Doug Semig" <[EMAIL PROTECTED]> wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > I applaud your efforts to learn SQL. > > I wonder, though, if the PHP-DB list the best place to learn SQL. I would > think that a book or a web-based tutorial on SQL would be more conducive to > your efforts. > > In the example you have given below, you would probably want to use LIKE. > For example, "SELECT * FROM test WHERE cat LIKE '%c%';" or similar ought to > do it. Of course, I hope that the example is merely an example because if > you really designed a production table like that then you wouldn't be > taking advantage of the "relational" part of your relational database > management system. > > So please keep learning SQL...it's a fantastic skill to have. If you have > a specific SQL problem every once in a while there are those on this list > who would undoubtedly enjoy helping out. Several beginner-level questions > back-to-back, though, is way beyond the charter of this mailing list. The > overall purpose of this particular mailing list is for discussions about > PHP's ability to interface with databases. There may possibly be an SQL > beginners mailing list on egroups or someplace similar...you might find an > environment like that better than the PHP-DB list to help you with this stuff. > > Doug > > At 02:56 PM 7/16/01 -0400, McShen wrote: > >ok. I use set to make a table. here is my table 'test' > >+---+--+ > >| name | cat | > >+---+--+ > >| shen | a,c | > >| shen1 | b,c | > >+---+--+ > > > >How can I find all records that contain 'c'? > >i tried > >select * from test where cat='c'; > ...snip... > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Re: enum !!
I applaud your efforts to learn SQL. I wonder, though, if the PHP-DB list the best place to learn SQL. I would think that a book or a web-based tutorial on SQL would be more conducive to your efforts. In the example you have given below, you would probably want to use LIKE. For example, "SELECT * FROM test WHERE cat LIKE '%c%';" or similar ought to do it. Of course, I hope that the example is merely an example because if you really designed a production table like that then you wouldn't be taking advantage of the "relational" part of your relational database management system. So please keep learning SQL...it's a fantastic skill to have. If you have a specific SQL problem every once in a while there are those on this list who would undoubtedly enjoy helping out. Several beginner-level questions back-to-back, though, is way beyond the charter of this mailing list. The overall purpose of this particular mailing list is for discussions about PHP's ability to interface with databases. There may possibly be an SQL beginners mailing list on egroups or someplace similar...you might find an environment like that better than the PHP-DB list to help you with this stuff. Doug At 02:56 PM 7/16/01 -0400, McShen wrote: >ok. I use set to make a table. here is my table 'test' >+---+--+ >| name | cat | >+---+--+ >| shen | a,c | >| shen1 | b,c | >+---+--+ > >How can I find all records that contain 'c'? >i tried >select * from test where cat='c'; ...snip... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Re: enum !!
Yes, that syntax is OK if you EXACTLY match the structure of the table. If you redesign the table and insert a column between name and cat, your data will go into the new field. For that reason I prefer insert into test set name = "shen", cat = "a,c" Yes, one insert statement per line. To do multiple inserts you could store the values in an array and then set up a loop to process the array, one insertion per row. You could extend that to open and read a text file, http://www.php.net/manual/en/function.fopen.php http://www.php.net/manual/en/function.fgets.php parsing each line into an array http://www.php.net/manual/en/function.split.php or http://www.php.net/manual/en/function.explode.php and using your array processing code to do the rest. Have fun - Miles Thompson At 03:00 PM 7/16/01 -0400, McShen wrote: >BTW, did i insert those data correctly? >here was what i type > >insert into test values('shen','a,c'); >insert into test values('shen1','a,c'); > >I think they were wrong. > >How do i insert 2 values ? > > >"McShen" <[EMAIL PROTECTED]> wrote in message >[EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > > ok. I use set to make a table. here is my table 'test' > > +---+--+ > > | name | cat | > > +---+--+ > > | shen | a,c | > > | shen1 | b,c | > > +---+--+ > > > > How can I find all records that contain 'c'? > > i tried > > select * from test where cat='c'; > > > > it returned "empty set"; > > > > please help > > > > > > "Tom Hodder" <[EMAIL PROTECTED]> wrote in message > > [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > > > > > > First normal form (1NF), A table in which the intersection of every >column > > > and record contains one, and only one, value. > > > > > > > > > > > > -Original Message- > > > From: Hugh Bothwell [mailto:[EMAIL PROTECTED]] > > > Sent: 16 July 2001 19:21 > > > To: [EMAIL PROTECTED] > > > Subject: [PHP-DB] Re: enum !! > > > > > > > > > > > > "McShen" <[EMAIL PROTECTED]> wrote in message > > > [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > > > > how do i store multiple data in one field? > > > > i have a table like this > > > > > > > > +---+---+--+-+-+---+ > > > > | Field | Type | Null | Key | Default | Extra | > > > > +---+---+--+-+-+---+ > > > > | test | enum('a','b','c') | YES | | NULL| | > > > > +---+---+--+-+-+---+ > > > > > > > > insert into test values('a','b','c'); doens't work. please help > > > > > > Your query is mixed up; 'test' is the field name, not the table name, >and > > I > > > think you want to put the values into three separate records, not one... > > > > > > Try > > > INSERT INTO tablename ( test ) VALUES ( 'a' ), ( 'b'), ('c'), (''); > > > > > > This should get you four records, with 'test' values of a, b, c, and >null. > > > > > > If you want to store more than one value in the same record, you should >be > > > looking at sets, not enums. > > > > > > > > > > > > -- > > > PHP Database Mailing List (http://www.php.net/) > > > To unsubscribe, e-mail: [EMAIL PROTECTED] > > > For additional commands, e-mail: [EMAIL PROTECTED] > > > To contact the list administrators, e-mail: [EMAIL PROTECTED] > > > > > > > > > > > > > >-- >PHP Database Mailing List (http://www.php.net/) >To unsubscribe, e-mail: [EMAIL PROTECTED] >For additional commands, e-mail: [EMAIL PROTECTED] >To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Re: enum !!
select * from test where cat like 'c' or, a little broader select *from text where cat like '%c%' Miles Thompson At 02:56 PM 7/16/01 -0400, McShen wrote: >ok. I use set to make a table. here is my table 'test' >+---+--+ >| name | cat | >+---+--+ >| shen | a,c | >| shen1 | b,c | >+---+--+ > >How can I find all records that contain 'c'? >i tried >select * from test where cat='c'; > >it returned "empty set"; > >please help > > >"Tom Hodder" <[EMAIL PROTECTED]> wrote in message >[EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > > > > First normal form (1NF), A table in which the intersection of every column > > and record contains one, and only one, value. > > > > > > > > -Original Message- > > From: Hugh Bothwell [mailto:[EMAIL PROTECTED]] > > Sent: 16 July 2001 19:21 > > To: [EMAIL PROTECTED] > > Subject: [PHP-DB] Re: enum !! > > > > > > > > "McShen" <[EMAIL PROTECTED]> wrote in message > > [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > > > how do i store multiple data in one field? > > > i have a table like this > > > > > > +---+---+--+-+-+---+ > > > | Field | Type | Null | Key | Default | Extra | > > > +---+---+--+-+-+---+ > > > | test | enum('a','b','c') | YES | | NULL| | > > > +---+---+--+-+-+---+ > > > > > > insert into test values('a','b','c'); doens't work. please help > > > > Your query is mixed up; 'test' is the field name, not the table name, and >I > > think you want to put the values into three separate records, not one... > > > > Try > > INSERT INTO tablename ( test ) VALUES ( 'a' ), ( 'b'), ('c'), (''); > > > > This should get you four records, with 'test' values of a, b, c, and null. > > > > If you want to store more than one value in the same record, you should be > > looking at sets, not enums. > > > > > > > > -- > > PHP Database Mailing List (http://www.php.net/) > > To unsubscribe, e-mail: [EMAIL PROTECTED] > > For additional commands, e-mail: [EMAIL PROTECTED] > > To contact the list administrators, e-mail: [EMAIL PROTECTED] > > > > > > > >-- >PHP Database Mailing List (http://www.php.net/) >To unsubscribe, e-mail: [EMAIL PROTECTED] >For additional commands, e-mail: [EMAIL PROTECTED] >To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP-DB] Re: enum !!
> -Original Message- > From: McShen [mailto:[EMAIL PROTECTED]] > Sent: Monday, July 16, 2001 1:56 PM > To: [EMAIL PROTECTED] > Subject: Re: [PHP-DB] Re: enum !! > > > ok. I use set to make a table. here is my table 'test' > +---+--+ > | name | cat | > +---+--+ > | shen | a,c | > | shen1 | b,c | > +---+--+ > > How can I find all records that contain 'c'? > i tried > select * from test where cat='c'; > > it returned "empty set"; Quoting from http://www.mysql.com/doc/S/E/SET.html Normally, you perform a SELECT on a SET column using the LIKE operator or the FIND_IN_SET() function: mysql> SELECT * FROM tbl_name WHERE set_col LIKE '%value%'; mysql> SELECT * FROM tbl_name WHERE FIND_IN_SET('value',set_col)>0; But the following will also work: mysql> SELECT * FROM tbl_name WHERE set_col = 'val1,val2'; mysql> SELECT * FROM tbl_name WHERE set_col & 1; The first of these statements looks for an exact match. The second looks for values containing the first set member. --- Mark Roedel | "The most overlooked advantage to owning a Systems Programmer| computer is that if they foul up there's no LeTourneau University | law against whacking them around a little." Longview, Texas, USA | -- Owen Porterfield -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Re: enum !!
BTW, did i insert those data correctly? here was what i type insert into test values('shen','a,c'); insert into test values('shen1','a,c'); I think they were wrong. How do i insert 2 values ? "McShen" <[EMAIL PROTECTED]> wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > ok. I use set to make a table. here is my table 'test' > +---+--+ > | name | cat | > +---+--+ > | shen | a,c | > | shen1 | b,c | > +---+--+ > > How can I find all records that contain 'c'? > i tried > select * from test where cat='c'; > > it returned "empty set"; > > please help > > > "Tom Hodder" <[EMAIL PROTECTED]> wrote in message > [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > > > > First normal form (1NF), A table in which the intersection of every column > > and record contains one, and only one, value. > > > > > > > > -Original Message- > > From: Hugh Bothwell [mailto:[EMAIL PROTECTED]] > > Sent: 16 July 2001 19:21 > > To: [EMAIL PROTECTED] > > Subject: [PHP-DB] Re: enum !! > > > > > > > > "McShen" <[EMAIL PROTECTED]> wrote in message > > [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > > > how do i store multiple data in one field? > > > i have a table like this > > > > > > +---+---+--+-+-+---+ > > > | Field | Type | Null | Key | Default | Extra | > > > +---+---+--+-+-+---+ > > > | test | enum('a','b','c') | YES | | NULL| | > > > +---+---+--+-+-+---+ > > > > > > insert into test values('a','b','c'); doens't work. please help > > > > Your query is mixed up; 'test' is the field name, not the table name, and > I > > think you want to put the values into three separate records, not one... > > > > Try > > INSERT INTO tablename ( test ) VALUES ( 'a' ), ( 'b'), ('c'), (''); > > > > This should get you four records, with 'test' values of a, b, c, and null. > > > > If you want to store more than one value in the same record, you should be > > looking at sets, not enums. > > > > > > > > -- > > PHP Database Mailing List (http://www.php.net/) > > To unsubscribe, e-mail: [EMAIL PROTECTED] > > For additional commands, e-mail: [EMAIL PROTECTED] > > To contact the list administrators, e-mail: [EMAIL PROTECTED] > > > > > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Re: enum !!
ok. I use set to make a table. here is my table 'test' +---+--+ | name | cat | +---+--+ | shen | a,c | | shen1 | b,c | +---+--+ How can I find all records that contain 'c'? i tried select * from test where cat='c'; it returned "empty set"; please help "Tom Hodder" <[EMAIL PROTECTED]> wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > > First normal form (1NF), A table in which the intersection of every column > and record contains one, and only one, value. > > > > -Original Message- > From: Hugh Bothwell [mailto:[EMAIL PROTECTED]] > Sent: 16 July 2001 19:21 > To: [EMAIL PROTECTED] > Subject: [PHP-DB] Re: enum !! > > > > "McShen" <[EMAIL PROTECTED]> wrote in message > [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > > how do i store multiple data in one field? > > i have a table like this > > > > +---+---+--+-+-+---+ > > | Field | Type | Null | Key | Default | Extra | > > +---+---+--+-+-+---+ > > | test | enum('a','b','c') | YES | | NULL| | > > +---+---+--+-+-+---+ > > > > insert into test values('a','b','c'); doens't work. please help > > Your query is mixed up; 'test' is the field name, not the table name, and I > think you want to put the values into three separate records, not one... > > Try > INSERT INTO tablename ( test ) VALUES ( 'a' ), ( 'b'), ('c'), (''); > > This should get you four records, with 'test' values of a, b, c, and null. > > If you want to store more than one value in the same record, you should be > looking at sets, not enums. > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > To contact the list administrators, e-mail: [EMAIL PROTECTED] > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP-DB] Re: enum !!
First normal form (1NF), A table in which the intersection of every column and record contains one, and only one, value. -Original Message- From: Hugh Bothwell [mailto:[EMAIL PROTECTED]] Sent: 16 July 2001 19:21 To: [EMAIL PROTECTED] Subject: [PHP-DB] Re: enum !! "McShen" <[EMAIL PROTECTED]> wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > how do i store multiple data in one field? > i have a table like this > > +---+---+--+-+-+---+ > | Field | Type | Null | Key | Default | Extra | > +---+---+--+-+-+---+ > | test | enum('a','b','c') | YES | | NULL| | > +---+---+--+-+-+---+ > > insert into test values('a','b','c'); doens't work. please help Your query is mixed up; 'test' is the field name, not the table name, and I think you want to put the values into three separate records, not one... Try INSERT INTO tablename ( test ) VALUES ( 'a' ), ( 'b'), ('c'), (''); This should get you four records, with 'test' values of a, b, c, and null. If you want to store more than one value in the same record, you should be looking at sets, not enums. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]