Easy (probably) MySQL question.

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
patch2112
Forum Commoner
Posts: 86
Joined: Sun Oct 31, 2004 9:44 am
Location: London

Easy (probably) MySQL question.

Post by patch2112 »

Is there some way to select all but 1 column (the primary key) with mysql_query?

Thanks,
Philip
User avatar
potsed
Forum Commoner
Posts: 50
Joined: Sat Oct 09, 2004 12:00 pm
Location: - South Africa

Post by potsed »

Code: Select all

"SELECT column1, column2, colum3 FROM table
only type in the columns u want seperated by a comma
patch2112
Forum Commoner
Posts: 86
Joined: Sun Oct 31, 2004 9:44 am
Location: London

Post by patch2112 »

Thanks posted, but that won't work here. I've got a huge list of product colors that I would have to type in each one. That might be okay, except they change around. Any other suggestions?
User avatar
potsed
Forum Commoner
Posts: 50
Joined: Sat Oct 09, 2004 12:00 pm
Location: - South Africa

Post by potsed »

write the query in a loop, and exclude the id column
patch2112
Forum Commoner
Posts: 86
Joined: Sun Oct 31, 2004 9:44 am
Location: London

Post by patch2112 »

Thanks Potsed, that should do the trick. I was just hoping there was some kind of exclude command or something, but I can't find anything, so I guess not.

Thanks again, I appreciate it.
Philip
User avatar
potsed
Forum Commoner
Posts: 50
Joined: Sat Oct 09, 2004 12:00 pm
Location: - South Africa

Post by potsed »

No problem... thats why the forums here ;)
patch2112
Forum Commoner
Posts: 86
Joined: Sun Oct 31, 2004 9:44 am
Location: London

Solution

Post by patch2112 »

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>";
&#125;
&#125;
&#125;
patch2112
Forum Commoner
Posts: 86
Joined: Sun Oct 31, 2004 9:44 am
Location: London

Post by patch2112 »

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
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

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.
patch2112
Forum Commoner
Posts: 86
Joined: Sun Oct 31, 2004 9:44 am
Location: London

Post by patch2112 »

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
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

[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

..
Post Reply