Re: [PHP-DB] Storing multiple items in one MySQL field?
On 08/01/12 23:35, Karl DeSaulniers wrote: On Jan 8, 2012, at 10:36 AM, Bastien wrote: On 2012-01-08, at 7:27 AM, Niel Archer n...@chance.now wrote: -- Niel Archer niel.archer (at) blueyonder.co.uk Hello phpers and sqlheads, If you have a moment, I have a question. INTRO: I am trying to set up categories for a web site. Each item can belong to more than one category. IE: Mens, T-Shirts, Long Sleeve Shirts, etc.. etc.. (Sorry no fancy box drawing) QUESTION: My question is what would the best way be to store this in one MySQL field and how would I read and write with PHP to that field? I have thought of enum() but not on the forefront of what that actually does and what it is best used for. I just know its a type of field that can have multiple items in it. Not sure if its what I need. REASON: I just want to be able to query the database with multiple category ID's and it check this field and report back if that category is present or if there are multiple present. Maybe return as a list or an array? I would like to stay away from creating multiple fields in my table for this. Have you considered separate tables? Store the categories in one table and use a third to store the item and category combination, one row per item,category combo. This is a common pattern to manage such situations. NOTE: The categories are retrieved as a number FYI. Any help/code would be greatly appreciated. But a link does just fine for me. Best Regards, Karl DeSaulniers Design Drumm http://designdrumm.com Hope your all enjoying your 2012! -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Neil's solution is the best. Storing a comma separated list will involve using a LIKE search to find your categories. This will result in a full table scan and will be slow when your tables get bigger. Storing them in a join table as Neil suggested removes the need for a like search an will be faster Bastien -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Thanks guys for the responses. So.. what your saying if I understand correctly. Have the categories in one table all in separate fields. Than have a the products table. Than have a third table that stores say a product id and all the individual categories for that product in that table as separate fields associated with that product id? Am I close? Sounds like a good situation, but I didn't want to really create a new table. One product will probably have no more than 3 combinations of categories. So not sure it this is necessary. EG: Tshirts = 1 Jackets = 2 etc.. Mens = 12 Womens = 13 So lets say I want to find all the Mens Tshirts.. I was wanting one field to hold the 1, 12 hope that clarifies Karl DeSaulniers Design Drumm http://designdrumm.com Hi Karl, if you don't want to do with the third-table-solution, how about an assembler-style bit-wise OR of all categories ? constant TSHIRTS = 1 ; // 2 to the 0th power constant JACKETS = 2 ; // 2 to the 1st power constant MENS= 8 ; // 2 to the 3rd power constant WOMENS = 16 ; // 2 to the girl power :-) INSERT INTO TABLE t_myTable ( ID, categoryField) VALUES ( myNewId, TSHIRTS | MENS ) ; SELECT ID FROM t_myTable WHERE ( categoryField ( TSHIRTS | MENS )) 0 ; This assumes that your number of categories is not that big of course, as you're limited to 64 bits/categories on a modern machine. Bert -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Receiving Error- Error: user account already exists..
I keep receiving the following error..( Error: user account already exists..
) although I don't have any data in my table. Any help would be appreciated.
The code follows:
?php
session_start();
include ('dbc.php');
if (isset($_POST['Submit']) =='Register')
{
if (strlen($_POST['email']) 5)
{
die (Incorrect email. Please enter valid email address..);
}
if (strcmp(isset($_POST['pass1']),isset($_POST['pass2'])) ||
empty($_POST['pass1']) )
{
//die (Password does not match);
die(ERROR: Password does not match or empty..);
}
if (strcmp(md5($_POST['user_code']),$_SESSION['ckey']))
{
die(Invalid code entered.
Please enter the correct code as shown in the Image);
}
$rs_duplicates = mysql_query(select id from users where
user_email='$_POST[email]');
$duplicates = mysql_num_rows($rs_duplicates);
if ($duplicates 0);
{
//die (ERROR: User account already exists.);
header(Location: register.php?msg=ERROR: User account
already exists..);
exit();
}
$md5pass = md5($_POST['pass2']);
$activ_code = rand(1000,);
$host = $_SERVER['HTTP_HOST'];
$uri = rtrim(dirname($_SERVER['PHP_SELF']), '/\\');
mysql_query(INSERT INTO users
(`user_email`,`user_pwd`,`country`,`joined`,`activation_code`,`full_name`)
VALUES
('$_POST[email]','$md5pass','$_POST[country]',now(),'$activ_code','$_POST[fu
ll_name]')) or die(mysql_error());
$message =
Thank you for registering an account with $server. Here are the login
details...\n\n
User Email: $_POST[email] \n
Password: $_POST[pass2] \n
Activation Code: $activ_code \n
*** ACTIVATION LINK * \n
Activation Link:
http://$host$uri/activate.php?usr=$_POST[email]code=$activ_code \n\n
_
Thank you. This is an automated response. PLEASE DO NOT REPLY.
;
mail($_POST['email'] , Login Activation, $message,
From: \Auto-Response\ notifications@$host\r\n .
X-Mailer: PHP/ . phpversion());
unset($_SESSION['ckey']);
echo (Registration Successful! An activation code has been
sent to your email address with an activation link...);exit;
}
?
[PHP-DB] Re: Receiving Error- Error: user account already exists..
if (isset($_POST['Submit']) =='Register') I don't understand why you are using isset on your post vars. Since isset returns a boolean value, how can you compare that to a string? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Receiving Error- Error: user account already exists..
I keep receiving the following error..( Error: user account already exists..
) although I don't have any data in my table. Any help would be appreciated.
The code follows:
?php
session_start();
include ('dbc.php');
if (isset($_POST['Submit']) =='Register')
As Jim already pointed out, if $_POST['Submit'] is set to ANY value this
check will be true. I think you want something like :
if (isset($_POST['Submit']) $_POST['Submit'] =='Register')
{
if (strlen($_POST['email']) 5)
{
die (Incorrect email. Please enter valid email address..);
}
if (strcmp(isset($_POST['pass1']),isset($_POST['pass2'])) ||
empty($_POST['pass1']) )
{
//die (Password does not match);
die(ERROR: Password does not match or empty..);
}
if (strcmp(md5($_POST['user_code']),$_SESSION['ckey']))
{
die(Invalid code entered.
Please enter the correct code as shown in the Image);
}
$rs_duplicates = mysql_query(select id from users where
user_email='$_POST[email]');
You should test $rs_duplicates !== false to be sure you have a valid
resource.
Have you tried retrieving the supposed row(s) and var_dump-ing them to
see what it thinks they are? My guess is, you're getting an error
message, which is why there is a row to be counted. You can't use an
array inside a double-quoted string like that, change it to be wrapped
in braces like:
$rs_duplicates = mysql_query(select id from users where user_email='
{$_POST[email]}');
--
Niel Archer
niel.archer (at) blueyonder.co.uk
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Storing multiple items in one MySQL field?
On Jan 10, 2012, at 9:30 AM, B. Aerts wrote:
On 08/01/12 23:35, Karl DeSaulniers wrote:
On Jan 8, 2012, at 10:36 AM, Bastien wrote:
On 2012-01-08, at 7:27 AM, Niel Archer n...@chance.now wrote:
--
Niel Archer
niel.archer (at) blueyonder.co.uk
Hello phpers and sqlheads,
If you have a moment, I have a question.
INTRO:
I am trying to set up categories for a web site.
Each item can belong to more than one category.
IE: Mens, T-Shirts, Long Sleeve Shirts, etc.. etc..
(Sorry no fancy box drawing)
QUESTION:
My question is what would the best way be to store this in one
MySQL
field and how would I read and write with PHP to that field?
I have thought of enum() but not on the forefront of what that
actually does and what it is best used for.
I just know its a type of field that can have multiple items in
it.
Not sure if its what I need.
REASON:
I just want to be able to query the database with multiple
category
ID's and it check this field and report back if that category is
present or if there are multiple present.
Maybe return as a list or an array? I would like to stay away from
creating multiple fields in my table for this.
Have you considered separate tables? Store the categories in one
table
and use a third to store the item and category combination, one
row per
item,category combo. This is a common pattern to manage such
situations.
NOTE:
The categories are retrieved as a number FYI.
Any help/code would be greatly appreciated.
But a link does just fine for me.
Best Regards,
Karl DeSaulniers
Design Drumm
http://designdrumm.com
Hope your all enjoying your 2012!
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Neil's solution is the best. Storing a comma separated list will
involve using a LIKE search to find your categories. This will
result
in a full table scan and will be slow when your tables get bigger.
Storing them in a join table as Neil suggested removes the need
for a
like search an will be faster
Bastien
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Thanks guys for the responses. So.. what your saying if I understand
correctly.
Have the categories in one table all in separate fields.
Than have a the products table. Than have a third table that stores
say
a product id
and all the individual categories for that product in that table as
separate fields associated with that product id?
Am I close? Sounds like a good situation, but I didn't want to really
create a new table.
One product will probably have no more than 3 combinations of
categories. So not sure it this is necessary.
EG:
Tshirts = 1
Jackets = 2
etc..
Mens = 12
Womens = 13
So lets say I want to find all the Mens Tshirts.. I was wanting one
field to hold the 1, 12
hope that clarifies
Karl DeSaulniers
Design Drumm
http://designdrumm.com
Hi Karl,
if you don't want to do with the third-table-solution, how about an
assembler-style bit-wise OR of all categories ?
constant TSHIRTS = 1 ; // 2 to the 0th power
constant JACKETS = 2 ; // 2 to the 1st power
constant MENS= 8 ; // 2 to the 3rd power
constant WOMENS = 16 ; // 2 to the girl power :-)
INSERT INTO TABLE t_myTable ( ID, categoryField)
VALUES ( myNewId, TSHIRTS | MENS ) ;
SELECT ID
FROM t_myTable
WHERE ( categoryField ( TSHIRTS | MENS )) 0 ;
This assumes that your number of categories is not that big of
course, as you're limited to 64 bits/categories on a modern machine.
Bert
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Hi Bert,
Thanks for the response. I did consider that, but there may be more
than 64 categories.
So I am thinking that may not be best for my situation. I am actually
at the same point again, but
this time with the colors. I have multiple colors for each tshirt.
I dont want to put all the separate colors as their own fields and
there is an image associated with those colors too.
I'd also like to not put those all in separate fields if I can.
What's the best way to store multiple values that may change from time
to time?
What kind of field?
IE:
('red.png', 'green.png', 'blue.png')
SET()
enum()
blob()
varchar()
???
TIA
Karl DeSaulniers
Design Drumm
http://designdrumm.com
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Storing multiple items in one MySQL field?
On Jan 10, 2012, at 10:49 PM, Karl DeSaulniers wrote:
On Jan 10, 2012, at 9:30 AM, B. Aerts wrote:
On 08/01/12 23:35, Karl DeSaulniers wrote:
On Jan 8, 2012, at 10:36 AM, Bastien wrote:
On 2012-01-08, at 7:27 AM, Niel Archer n...@chance.now wrote:
--
Niel Archer
niel.archer (at) blueyonder.co.uk
Hello phpers and sqlheads,
If you have a moment, I have a question.
INTRO:
I am trying to set up categories for a web site.
Each item can belong to more than one category.
IE: Mens, T-Shirts, Long Sleeve Shirts, etc.. etc..
(Sorry no fancy box drawing)
QUESTION:
My question is what would the best way be to store this in one
MySQL
field and how would I read and write with PHP to that field?
I have thought of enum() but not on the forefront of what that
actually does and what it is best used for.
I just know its a type of field that can have multiple items in
it.
Not sure if its what I need.
REASON:
I just want to be able to query the database with multiple
category
ID's and it check this field and report back if that category is
present or if there are multiple present.
Maybe return as a list or an array? I would like to stay away
from
creating multiple fields in my table for this.
Have you considered separate tables? Store the categories in one
table
and use a third to store the item and category combination, one
row per
item,category combo. This is a common pattern to manage such
situations.
NOTE:
The categories are retrieved as a number FYI.
Any help/code would be greatly appreciated.
But a link does just fine for me.
Best Regards,
Karl DeSaulniers
Design Drumm
http://designdrumm.com
Hope your all enjoying your 2012!
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Neil's solution is the best. Storing a comma separated list will
involve using a LIKE search to find your categories. This will
result
in a full table scan and will be slow when your tables get bigger.
Storing them in a join table as Neil suggested removes the need
for a
like search an will be faster
Bastien
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Thanks guys for the responses. So.. what your saying if I understand
correctly.
Have the categories in one table all in separate fields.
Than have a the products table. Than have a third table that
stores say
a product id
and all the individual categories for that product in that table as
separate fields associated with that product id?
Am I close? Sounds like a good situation, but I didn't want to
really
create a new table.
One product will probably have no more than 3 combinations of
categories. So not sure it this is necessary.
EG:
Tshirts = 1
Jackets = 2
etc..
Mens = 12
Womens = 13
So lets say I want to find all the Mens Tshirts.. I was wanting one
field to hold the 1, 12
hope that clarifies
Karl DeSaulniers
Design Drumm
http://designdrumm.com
Hi Karl,
if you don't want to do with the third-table-solution, how about an
assembler-style bit-wise OR of all categories ?
constant TSHIRTS = 1 ; // 2 to the 0th power
constant JACKETS = 2 ; // 2 to the 1st power
constant MENS= 8 ; // 2 to the 3rd power
constant WOMENS = 16 ; // 2 to the girl power :-)
INSERT INTO TABLE t_myTable ( ID, categoryField)
VALUES ( myNewId, TSHIRTS | MENS ) ;
SELECT ID
FROM t_myTable
WHERE ( categoryField ( TSHIRTS | MENS )) 0 ;
This assumes that your number of categories is not that big of
course, as you're limited to 64 bits/categories on a modern machine.
Bert
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Hi Bert,
Thanks for the response. I did consider that, but there may be more
than 64 categories.
So I am thinking that may not be best for my situation. I am
actually at the same point again, but
this time with the colors. I have multiple colors for each tshirt.
I dont want to put all the separate colors as their own fields and
there is an image associated with those colors too.
I'd also like to not put those all in separate fields if I can.
What's the best way to store multiple values that may change from
time to time?
What kind of field?
IE:
('red.png', 'green.png', 'blue.png')
SET()
enum()
blob()
varchar()
???
TIA
Karl DeSaulniers
Design Drumm
http://designdrumm.com
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
I am thinking of limiting the colors to 10 for now (after all there
are only so many ways to die a shirt. =)
and using a comma delimited list of abbreviated color names as a
varchar string.
Then read out that string, explode on the commas and put in an array.
`pd_color` varchar(39) CHARACTER SET utf8 NOT NULL DEFAULT
'Blk,Wht,Gry,Tan,Nvy,Red,Grn,Yel,Org,Trq'
With this I can dynamically call each different color file dependent
on what's in the $colors[]
Re: [PHP-DB] Storing multiple items in one MySQL field?
On Jan 11, 2012 7:13 AM, Karl DeSaulniers k...@designdrumm.com wrote:
On Jan 10, 2012, at 10:49 PM, Karl DeSaulniers wrote:
On Jan 10, 2012, at 9:30 AM, B. Aerts wrote:
On 08/01/12 23:35, Karl DeSaulniers wrote:
On Jan 8, 2012, at 10:36 AM, Bastien wrote:
On 2012-01-08, at 7:27 AM, Niel Archer n...@chance.now wrote:
--
Niel Archer
niel.archer (at) blueyonder.co.uk
Hello phpers and sqlheads,
If you have a moment, I have a question.
INTRO:
I am trying to set up categories for a web site.
Each item can belong to more than one category.
IE: Mens, T-Shirts, Long Sleeve Shirts, etc.. etc..
(Sorry no fancy box drawing)
QUESTION:
My question is what would the best way be to store this in one MySQL
field and how would I read and write with PHP to that field?
I have thought of enum() but not on the forefront of what that
actually does and what it is best used for.
I just know its a type of field that can have multiple items in it.
Not sure if its what I need.
REASON:
I just want to be able to query the database with multiple category
ID's and it check this field and report back if that category is
present or if there are multiple present.
Maybe return as a list or an array? I would like to stay away from
creating multiple fields in my table for this.
Have you considered separate tables? Store the categories in one
table
and use a third to store the item and category combination, one row
per
item,category combo. This is a common pattern to manage such
situations.
NOTE:
The categories are retrieved as a number FYI.
Any help/code would be greatly appreciated.
But a link does just fine for me.
Best Regards,
Karl DeSaulniers
Design Drumm
http://designdrumm.com
Hope your all enjoying your 2012!
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Neil's solution is the best. Storing a comma separated list will
involve using a LIKE search to find your categories. This will result
in a full table scan and will be slow when your tables get bigger.
Storing them in a join table as Neil suggested removes the need for a
like search an will be faster
Bastien
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Thanks guys for the responses. So.. what your saying if I understand
correctly.
Have the categories in one table all in separate fields.
Than have a the products table. Than have a third table that stores say
a product id
and all the individual categories for that product in that table as
separate fields associated with that product id?
Am I close? Sounds like a good situation, but I didn't want to really
create a new table.
One product will probably have no more than 3 combinations of
categories. So not sure it this is necessary.
EG:
Tshirts = 1
Jackets = 2
etc..
Mens = 12
Womens = 13
So lets say I want to find all the Mens Tshirts.. I was wanting one
field to hold the 1, 12
hope that clarifies
Karl DeSaulniers
Design Drumm
http://designdrumm.com
Hi Karl,
if you don't want to do with the third-table-solution, how about an
assembler-style bit-wise OR of all categories ?
constant TSHIRTS = 1 ; // 2 to the 0th power
constant JACKETS = 2 ; // 2 to the 1st power
constant MENS= 8 ; // 2 to the 3rd power
constant WOMENS = 16 ; // 2 to the girl power :-)
INSERT INTO TABLE t_myTable ( ID, categoryField)
VALUES ( myNewId, TSHIRTS | MENS ) ;
SELECT ID
FROM t_myTable
WHERE ( categoryField ( TSHIRTS | MENS )) 0 ;
This assumes that your number of categories is not that big of course,
as you're limited to 64 bits/categories on a modern machine.
Bert
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Hi Bert,
Thanks for the response. I did consider that, but there may be more than
64 categories.
So I am thinking that may not be best for my situation. I am actually at
the same point again, but
this time with the colors. I have multiple colors for each tshirt.
I dont want to put all the separate colors as their own fields and there
is an image associated with those colors too.
I'd also like to not put those all in separate fields if I can.
What's the best way to store multiple values that may change from time
to time?
What kind of field?
IE:
('red.png', 'green.png', 'blue.png')
SET()
enum()
blob()
varchar()
???
TIA
Karl DeSaulniers
Design Drumm
http://designdrumm.com
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
I am thinking of limiting the colors to 10 for now (after all there are
only so many ways to die a shirt. =)
and using a comma delimited list of abbreviated color names as a varchar
string.
Then read out that string, explode on the commas and put in an array.
`pd_color` varchar(39) CHARACTER SET utf8 NOT NULL DEFAULT
