Trying to set a column equal to the value of another column in same row
My MySQL query:
UPDATE `form_select_options` WHERE `value` IS NULL SET `value` = `option`
What I'm trying to do is, everywhere in the table that `value` is null, I want it to be changed to match the value of `option`. So, like, if there is a row where `option` is "Idaho", and `value` is null, I want `value` to be changed to "Idaho".
The better I get at programming, the more I appreciate arrays. Handy dandy things they are.
Also I'm wondering how I can tell MySQL to order results starting with ones that have "USA" for the value of `optgroup` and then order the rest of the results by the other values in`optgroup`. I'm thinking it would look something like this:
SELECT somefieldshere FROM atable WHERE meetssomecondition ORDER BY `optgroup` = 'USA' THEN `optgroup`
The table this query will be going to has a list of states and provinces. The optgroup specifies what country the sate/province comes from. These results are going to be echoed out. I want the USA states to be displayed first.
The better I get at programming, the more I appreciate arrays. Handy dandy things they are.
What I'm trying to do is, everywhere in the table that `value` is null, I want it to be changed to match the value of `option`. So, like, if there is a row where `option` is "Idaho", and `value` is null, I want `value` to be changed to "Idaho".
Pretty close:
Code:
UPDATE `form_select_options` SET `value` = `option` WHERE `value` IS NULL
"Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
~ Terry Pratchett in Nation
Also I'm wondering how I can tell MySQL to order results starting with ones that have "USA" for the value of `optgroup` and then order the rest of the results by the other values in`optgroup`. I'm thinking it would look something like this:
The table this query will be going to has a list of states and provinces. The optgroup specifies what country the sate/province comes from. These results are going to be echoed out. I want the USA states to be displayed first.
Code:
SELECT
col1,
col2,
col3,
IF(col3='USA', 1, 2) AS sort_by
FROM . . .
ORDER BY sort_by ASC, col3 ASC
"Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
~ Terry Pratchett in Nation
Okay, I now have the following, and what it is doing is just pulling up states for the USA and they're in reverse ABC order (Wyoming is first, then Oklahoma, then so on.) I want an alphabetical list of US states pulled up first and then the others after that. I would prefer if the Australian states to be next but I guess I could live without this.
Anyway, my query at the moment:
SELECT
`option`,
`value`,
`optgroup`,
IF(`optgroup`='USA', 1, 2) AS sort_by
FROM `form_select_options`
WHERE `field_name` = 'state_or_province'
ORDER BY sort_by ASC, `optgroup` ASC
Info about this table: It has the columns "row_id", "field_name", "option", "value" and "optgroup". "row_id" is never pulled from the database but the others end up creating the options for <select></select> fields in forms.
The general idea:
<select name='nameofthefield'>
<optgroup label='theoptgroupname'>
<option value='thevalue'>theoption</option>
</optgroup>
</select>
If you need it, here is the actual HTML output for that field (output generated with my original query, not the one above):
If you are going to do that, it might be simpler in the long run to add some sort of sort_precedence column to the table, maybe have it default to zero, then assign higher numbers for those you want to sort to the top:
USA : 10
Australia: 5
<others>: 0
Then your sort clause would be something like:
Code:
SORT BY sort_precendence DESC, state_or_province ASC
PS: The more I think about it, the more I think that the opt-group might need to be in a separate table to make this cleaner, so the opt-group table would include the precedence column, and each state/province would have a foreign key to the opt-group table (which might need to include a "default" row for those that do not really belong to any group.
Last edited by NogDog; 08-30-2012 at 08:13 PM.
"Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
~ Terry Pratchett in Nation
PS: The more I think about it, the more I think that the opt-group might need to be in a separate table to make this cleaner, so the opt-group table would include the precedence column, and each state/province would have a foreign key to the opt-group table (which might need to include a "default" row for those that do not really belong to any group.
I always use MyISAM tables and the MySQL manual says to use foreign keys you need InnoDB tables. Is it okay to have a database with more than one kind of table? Sorry if that question sounds dumb. It is only recently I am realizing just how much there is to know about SQL, and how little of that is stuff I actually know 0_0
The better I get at programming, the more I appreciate arrays. Handy dandy things they are.
I was (confusingly) using "foreign key" more in a conceptual than literal sense here, simply(?) meaning that the state/province/whatever table would include a column that would hold the value of the primary key in the new group table.
Code:
geo_group:
=========
key | name | precendence
1 | USA | 1
2 | Australia | 3
3 | Canada | 2
4 | Other | 99
state_province_whatever
================
key | name | group
1 | New Jersey | 1
2 | Idaho | 1
3 | New South Wales | 2
4 | Quebec | 3
5 | Panama | 4
Query might then be something like:
Code:
SELECT s.key, s.name AS name, g.name AS group. g.precedence
FROM state_province_whatever AS s
INNER JOIN geo_group AS g ON s.group = g.key
ORDER BY g.precedence, s.name
"Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
~ Terry Pratchett in Nation
Reply With Quote
Originally Posted by evenstar7139
Bookmarks