Easy (probably) MySQL question.
Moderator: General Moderators
Easy (probably) MySQL question.
Is there some way to select all but 1 column (the primary key) with mysql_query?
Thanks,
Philip
Thanks,
Philip
Code: Select all
"SELECT column1, column2, colum3 FROM tableSolution
I thought I would post my code for anyone else who may be learning PHP/MySQL and comes accross this.
Code: Select all
//execute query to select all options in size id from sizes
$rs_size_2 = mysql_query("select * from sizes where Size_id = "$size_band"")
or die ("err re_size_2");
//fetch array from rs
$value_size_2 = mysql_fetch_array($rs_size_2, MYSQL_ASSOC);
//loop through rs to get elements
foreach ($value_size_2 as $name=>$value)
{
//if statement to gather elements != N
if ($value!="N")
{
//if statement to eliminate primary key and size_id
if (($name != "pri_id") and ($name !="Size_id"))
{
//generate the html
$size .= "<option value="$value">$value</option>";
}
}
}Thanks for the input timvw, but I think it's necessary in this case. I didn't really explain before, because it's such a complex project, but I'll give you a little idea of it. Please feel free to let me know if you have suggestions, as I'm simultaneously learning PHP/MySQL/Javascript will developing a (seemly) complex project.
I'm developing a solution for a Embroider that allows her customers to order online. The problem/headache is that there are a massive amount of possibilities for each order. For example. An order's price is based on:
Quantity (1+, 25+, 100+) per item
Price Bands (up to six for each product based on size/color)
Placement options (printed or embroidered and where (can be any combo of several locations)
In order to correctly capture and manipulate all of these possibilities for each item, I had to create some tables that weren't so hot design wise, I think. For instance, with the colors (Over 200 that are randomly applied to each item) I created a color table that has each possible color listed as a colum name. The for each item there are 6 records(one for each price band ie ItemB1, ItemB2...) and then a Y or N for each of the color columns. That way I can grab them based on which item and band the person is operating in.
Same with sizes, I've got an identifier in my main products table that matches a record in my sizes table, which will give me the results based on the column names (the entry is the same name as the column name).
Does it make a little more sense now? Any help is appreciated.
Philip
I'm developing a solution for a Embroider that allows her customers to order online. The problem/headache is that there are a massive amount of possibilities for each order. For example. An order's price is based on:
Quantity (1+, 25+, 100+) per item
Price Bands (up to six for each product based on size/color)
Placement options (printed or embroidered and where (can be any combo of several locations)
In order to correctly capture and manipulate all of these possibilities for each item, I had to create some tables that weren't so hot design wise, I think. For instance, with the colors (Over 200 that are randomly applied to each item) I created a color table that has each possible color listed as a colum name. The for each item there are 6 records(one for each price band ie ItemB1, ItemB2...) and then a Y or N for each of the color columns. That way I can grab them based on which item and band the person is operating in.
Same with sizes, I've got an identifier in my main products table that matches a record in my sizes table, which will give me the results based on the column names (the entry is the same name as the column name).
Does it make a little more sense now? Any help is appreciated.
Philip
ok, if i understand a little, you are trying to store n-m relations ... as you have experienced it is impossible to do that directly.
[color] n ---- m [band]
meaning: each color can be used multiple bands and multiple bands can use a color.
to get round this problem it's common practice to introduce a help entitie, let's call it [bandcolor] which gives you [color] 1 -- n [bandcolor] n -- 1 [band]
meaning: each color can be used by multiple bandcolors. and each band can have multiple bandcolors.
[color] n ---- m [band]
meaning: each color can be used multiple bands and multiple bands can use a color.
to get round this problem it's common practice to introduce a help entitie, let's call it [bandcolor] which gives you [color] 1 -- n [bandcolor] n -- 1 [band]
meaning: each color can be used by multiple bandcolors. and each band can have multiple bandcolors.
I see. I've got 6 though.
[color] -- [band] -- [size] -- [quan discount] -- [price] -- [placement upcharges]
Which is why I went with the getting 2 pieces of info out of each record route. It acts more like a table with values along the side as well as the top. I only have 80 items, so I think it should be okay to do it this way.
I think you would faint if you saw some of my PHP though! lol. I'll go back through and optimize as best as I can later. Just got to get it up for now.
Thanks Tim,
Philip
[color] -- [band] -- [size] -- [quan discount] -- [price] -- [placement upcharges]
Which is why I went with the getting 2 pieces of info out of each record route. It acts more like a table with values along the side as well as the top. I only have 80 items, so I think it should be okay to do it this way.
I think you would faint if you saw some of my PHP though! lol. I'll go back through and optimize as best as I can later. Just got to get it up for now.
Thanks Tim,
Philip
[color] -- [band] -- [size] -- [quan discount] -- [price] -- [placement upcharges]
it is hard to tell because i don't know the situation, but i assume there are relations like this:
band - color
band - size
band - price
band - placement
now if i wanted all the bands with a blue color, and had a linktable to normalize the n-m relation between band and color my sql would look like
SELECT *
FROM band
INNER JOIN bandcolor ON band.band_ID=bandcolor.band_ID
INNER JOIN color ON bandcolor.color_ID=color.color_ID
..
it is hard to tell because i don't know the situation, but i assume there are relations like this:
band - color
band - size
band - price
band - placement
now if i wanted all the bands with a blue color, and had a linktable to normalize the n-m relation between band and color my sql would look like
SELECT *
FROM band
INNER JOIN bandcolor ON band.band_ID=bandcolor.band_ID
INNER JOIN color ON bandcolor.color_ID=color.color_ID
..