date:20120118

Re: [PHP-DB] Storing multiple items in one MySQL field?

2012-01-18 Thread Niel Archer

 Ok, so getting closer to the goal.. slight hiccup. I am experimenting  
 with some UNION and INNER JOIN and don't know if I am doing this  
 correctly or if this is the correct way to do this.
 
 PREFACE:
 I have now moved colors and sizes to their own tables and added a  
 product options table that has id combinations that relate to what  
 options a product has.
 Currently there is only colors and sizes, but this may expand  
 depending on needs.
 
 DALEMA:
 Now as I said above, I am trying to get the data out and using UNION  
 and INNER JOIN to mash everything up to a usable situation.
 
 STRUCTURE: (will attempt the fancy tables)
 
 COLORS TABLE:
 ++++
 |  colorid (P) |   pd_color   |   pd_color_desc   |
 ++++
 |  1 |Blk|Black|
 ++++
 |  2 |Wht   |White   |
 ++++
 |  5 |Pnk   | Pink|
 ++++
 
 SIZES TABLE:
 ++++
 |   sizeid (P)  |pd_size   |  pd_size_desc  |
 ++++
 |  4 | Md   |Medium   |
 ++++
 |  5 | Lg|Large   |
 ++++
 |  6 | XL|  Xtra Large|
 ++++
 
 PRODUCT OPTIONS TABLE: (Eg: mens shirt - pd_id == 1; womens - pd_id ==  
 2)
 +++++
 | optid (P) |pd_id|colorid|   sizeid|
 +++++
 |1   |1   |   1 | 
 4   |
 +++++
 |2   |1   |   1 | 
 5   |
 +++++
 |3   |1   |   1 | 
 6   |
 +++++
 |4   |1   |   2 | 
 4   |
 +++++
 |5   |1   |   2 | 
 5   |
 +++++
 |6   |1   |   2 | 
 6   |
 +++++
 |7   |2   |   1 | 
 4   |
 +++++
 |8   |2   |   1 | 
 5   |
 +++++
 etc.. etc..
 
 This is where I am stuck. I want to call the product options table
 with a product id, get the colors for that product, then get the sizes  
 for each color of said product.
 combine them with the product details and return everything to be  
 extracted.
 
 This is what I have currently and is causing the knot in my brain. If  
 anyone can help me at this point, I would greatly appreciate it.
 The first part of the $sql is the part that works. It grabs the main  
 product info out of the product table. That works fine.
 Everything after is what I was working on. From UNION down in the $sql  
 string.
 
 CODE:
 
 /* Get detail information of a product */
 function getProductDetail($pdId, $catId)
 {
   global $database;
   $_SESSION['shoppingReturnUrl'] = $_SERVER['REQUEST_URI'];
   
   // get the product information from database
   $sql = SELECT pd_name, pd_series, pd_description, pd_price,  
 pd_image, pd_qty
   FROM .TABLE_PRODUCTS.
   WHERE pd_id = .mysql_real_escape_string($pdId).
 
   //Start Me
   UNION
   
   SELECT colorid, sizeid, c.pd_color, c.pd_color_desc, 
 s.pd_size,  
 s.pd_size_desc
   FROM .TABLE_PROD_OPTIONS. po
   WHERE pd_id = .mysql_real_escape_string($pdId).
   INNER JOIN .TABLE_COLORS. c, .TABLE_SIZES. s
   ON po.colorid = c.colorid AND po.sizeid = s.sizeid;
   //End Me

I'm not sure what you are trying to achieve here, your clauses are in
the wrong order (WHERE comes after joins). See if this gets you what
you're after.

SELECT c.pd_color_desc, s.pd_size
FROM `product_options` INNER JOIN `colors` AS c USING (colorID) INNER
JOIN `sizes` AS s USING (sizeID)
WHERE productID = 1
ORDER BY c.pd_color_desc, s.pd_size

   $result = $database-query($sql);
   $row= mysql_fetch_assoc($result);
   extract($row);
   
   //this is where I am trying to extract and order the colors and sizes.
   if(mysql_num_rows($row['colorid']) 1) {
   foreach($row['colorid'] as $color) {
   ... //fetch each size for $color
   } else {
   ... //Get size for only color
   }
 
   $row['pd_description'] = nl2br($row['pd_description']);
   
   if ($row['pd_image']) {
   $row['pd_image'] = PRODUCT_IMAGE_DIR . $row['pd_image'];
   } else {
   $row['pd_image'] = WEB_ROOT .

Re: [PHP-DB] Storing multiple items in one MySQL field?

2012-01-18 Thread Karl DeSaulniers


Hi Niel,
Thanks for taking the time to respond. Comments below.

On Jan 18, 2012, at 11:14 AM, Niel Archer wrote:


Ok, so getting closer to the goal.. slight hiccup. I am experimenting
with some UNION and INNER JOIN and don't know if I am doing this
correctly or if this is the correct way to do this.

PREFACE:
I have now moved colors and sizes to their own tables and added a
product options table that has id combinations that relate to what
options a product has.
Currently there is only colors and sizes, but this may expand
depending on needs.

DALEMA:
Now as I said above, I am trying to get the data out and using UNION
and INNER JOIN to mash everything up to a usable situation.

STRUCTURE: (will attempt the fancy tables)

COLORS TABLE:
+—+—++
|  colorid (P) |   pd_color   |   pd_color_desc   |
+—+—++
|  1 |Blk| 
Black|

+—+—++
|  2 |Wht   | 
White   |

+—+—++
|  5 |Pnk   |  
Pink|

+—+—++

SIZES TABLE:
+—+—+———+
|   sizeid (P)  |pd_size   |  pd_size_desc  |
+—+—+———+
|  4 | Md   |Medium   |
+—+—+———+
|  5 | Lg|Large   |
+—+—+———+
|  6 | XL|  Xtra Large|
+—+—+———+

PRODUCT OPTIONS TABLE: (Eg: mens shirt - pd_id == 1; womens - pd_id  
==

2)
+++—++
| optid (P) |pd_id|colorid|   sizeid|
+++—++
|1   |1   |   1 |
4   |
+++—++
|2   |1   |   1 |
5   |
+++—++
|3   |1   |   1 |
6   |
+++—++
|4   |1   |   2 |
4   |
+++—++
|5   |1   |   2 |
5   |
+++—++
|6   |1   |   2 |
6   |
+++—++
|7   |2   |   1 |
4   |
+++—++
|8   |2   |   1 |
5   |
+++—++
etc.. etc..

This is where I am stuck. I want to call the product options table
with a product id, get the colors for that product, then get the  
sizes

for each color of said product.
combine them with the product details and return everything to be
extracted.

This is what I have currently and is causing the knot in my brain. If
anyone can help me at this point, I would greatly appreciate it.
The first part of the $sql is the part that works. It grabs the main
product info out of the product table. That works fine.
Everything after is what I was working on. From UNION down in the  
$sql

string.

CODE:

/* Get detail information of a product */
function getProductDetail($pdId, $catId)
{
global $database;
$_SESSION['shoppingReturnUrl'] = $_SERVER['REQUEST_URI'];

// get the product information from database
$sql = SELECT pd_name, pd_series, pd_description, pd_price,
pd_image, pd_qty
FROM .TABLE_PRODUCTS.
WHERE pd_id = .mysql_real_escape_string($pdId).

//Start Me
UNION

SELECT colorid, sizeid, c.pd_color, c.pd_color_desc, 
s.pd_size,
s.pd_size_desc
FROM .TABLE_PROD_OPTIONS. po
WHERE pd_id = .mysql_real_escape_string($pdId).
INNER JOIN .TABLE_COLORS. c, .TABLE_SIZES. s
ON po.colorid = c.colorid AND po.sizeid = s.sizeid;
//End Me


I'm not sure what you are trying to achieve here, your clauses are in
the wrong order (WHERE comes after joins). See if this gets you what
you're after.


:) Yeah figured that out too.. Oops



SELECT c.pd_color_desc, s.pd_size
FROM `product_options` INNER JOIN `colors` AS c USING (colorID) INNER
JOIN `sizes` AS s USING (sizeID)
WHERE productID = 1
ORDER BY c.pd_color_desc, s.pd_size


I will try this.. thank you!

What I am trying to do is fill some input type=select with the  
colors and depending on the color selected,
showing in another input type=select the sizes associated with  
that color of shirt.


So if the Black shirt has only mediums and larges and the white shirt  
has medium, large and extra large
only those show in the sizes dropdown. I have it to working... kind- 
of. I have since split it up into two functions.
Might be overkill. My problem was getting only the sizes for the  
selected color to show.


I kept getting..
Black
Black
Black
White
White
White

Then I got it to just show..

[PHP-DB] Multiple Access to Database - The Answer

2012-01-18 Thread Ethan Rosenberg


Dear List -

I have a database:

mysql show tables;
+-+
| Tables_in_hospital2 |
+-+
| Intake3 |
| Visit3  |
+-+


mysql describe Intake3;
++-+--+-+-+---+
| Field  | Type| Null | Key | Default | Extra |
++-+--+-+-+---+
| Site   | varchar(6)  | NO   | PRI | |   |
| MedRec | int(6)  | NO   | PRI | NULL|   |
| Fname  | varchar(15) | YES  | | NULL|   |
| Lname  | varchar(30) | YES  | | NULL|   |
| Phone  | varchar(30) | YES  | | NULL|   |
| Height | int(4)  | YES  | | NULL|   |
| Sex| char(7) | YES  | | NULL|   |
| Hx | text| YES  | | NULL|   |
++-+--+-+-+---+

mysql describe Visit3;
++--+--+-+-++
| Field  | Type | Null | Key | Default | Extra  |
++--+--+-+-++
| Indx   | int(4)   | NO   | PRI | NULL| auto_increment |
| Site   | varchar(6)   | YES  | | NULL||
| MedRec | int(6)   | YES  | | NULL||
| Notes  | text | YES  | | NULL||
| Weight | int(4)   | YES  | | NULL||
| BMI| decimal(3,1) | YES  | | NULL||
| Date   | date | YES  | | NULL||
++--+--+-+-++

I want Bob to have access to all tables and fields, with all privileges.
I want John to have read access to  Visit3: fields [Site, MedRec, 
Weight, BMI]


1] How do I do it?
2] In the case that I have two users with write access to a table, how 
do I lock the tables/fields so that the two users can not change the 
same varible at the same time?


Thanks.

Ethan

==

-- THE ANSWER ---
First - AS ROOT - Create the users and their privileges:

create user 'bob'@'localhost' identified by '1234';
grant all on hospital2.* to 'bob'@'localhost';

create user 'john'@'localhost' identified by '5678';
grant select on hospital2.Visit3 to 'john'@'localhost';
grant select (Site,MedRec,Weight,BMI,Date) on  hospital2.Visit3 to 
'john'@'localhost';




logout and login as bob

ethan@rosenberg:~/Desktop$ /usr/bin/mysql -u bob -p
Enter password:
Welcome to the MySQL monitor.

mysql show databases;
++
| Database   |
++
| information_schema |
| hospital2  |
++

use hospital2;

show tables;

+-+
| Tables_in_hospital2 |
+-+
| Intake3 |
| Visit3  |
+-+

mysql describe Visit3;
++--+--+-+-++
| Field  | Type | Null | Key | Default | Extra  |
++--+--+-+-++
| Indx   | int(4)   | NO   | PRI | NULL| auto_increment |
| Site   | varchar(6)   | YES  | | NULL||
| MedRec | int(6)   | YES  | | NULL||
| Notes  | text | YES  | | NULL||
| Weight | int(4)   | YES  | | NULL||
| BMI| decimal(3,1) | YES  | | NULL||
| Date   | date | YES  | | NULL||
++--+--+-+-++


logout and login as john

ethan@rosenberg:~/Desktop$ /usr/bin/mysql -u john -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.

use hospital2;

mysql show tables;
+-+
| Tables_in_hospital2 |
+-+
| Visit3  |
+-+

-- NOTE only access to one table --

mysql describe Visit3;
++--+--+-+-+---+
| Field  | Type | Null | Key | Default | Extra |
++--+--+-+-+---+
| Site   | varchar(6)   | YES  | | NULL|   |
| MedRec | int(6)   | YES  | | NULL|   |
| Weight | int(4)   | YES  | | NULL|   |
| BMI| decimal(3,1) | YES  | | NULL|   |
| Date   | date | YES  | | NULL|   |
++--+--+-+-+---+

-- Compare this to bob. See above --

mysql INSERT INTO Visit3 (Site) VALUES(15);
ERROR 1142 (42000): INSERT command denied to user 'john'@'localhost' for 
table 'Visit3'


-- NOTE: Readonly privileges --

The explanation of Lock Tables is long and complicated.  It can be found 
here:


http://dev.mysql.com/doc/refman/5.1/en/lock-tables.html

I hope this clarifies the issue.

PS:

I respectfully request that if a user asks a question, that an explicit 
answer be given, not just a

Re: [PHP-DB] Storing multiple items in one MySQL field?

Re: [PHP-DB] Storing multiple items in one MySQL field?

[PHP-DB] Multiple Access to Database - The Answer

3 matches

Site Navigation

Mail list logo

Footer information