www.webdeveloper.com
Results 1 to 8 of 8

Thread: Trying to set a column equal to the value of another column in same row

  1. #1
    Join Date
    Jun 2006
    Location
    Under your bed
    Posts
    357

    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".

  2. #2
    Join Date
    Jun 2006
    Location
    Under your bed
    Posts
    357
    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.

  3. #3
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,611
    Quote Originally Posted by evenstar7139 View Post
    My MySQL query:



    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

    eBookworm.us

  4. #4
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,611
    Quote Originally Posted by evenstar7139 View Post
    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

    eBookworm.us

  5. #5
    Join Date
    Jun 2006
    Location
    Under your bed
    Posts
    357
    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):

    PHP Code:
    <select name='state_or_province' required>
       <
    option></option> <!-- I always have a blank first option in select fields -->
        <
    optgroup label="Australia">
    <
    option value="Australian Capi">Australian Capital Territory</option>
    <
    option value="New South Wales">New South Wales</option>
    <
    option value="Western Austral">Western Australia</option>
    <
    option value="Victoria">Victoria</option>
    <
    option value="Tasmania">Tasmania</option>
    <
    option value="South Australia">South Australia</option>
    <
    option value="Queensland">Queensland</option>
    <
    option value="Northern Territ">Northern Territory</option>
    </
    optgroup>
    <
    optgroup label="Canada">
    <
    option value="New Brunswick">New Brunswick</option>
    <
    option value="Manitoba">Manitoba</option>
    <
    option value="British Columbi">British Columbia</option>
    <
    option value="Alberta">Alberta</option>
    <
    option value="Ontario">Ontario</option>
    <
    option value="Newfoundland an">Newfoundland and Labrador</option>
    <
    option value="Northwest Terri">Northwest Territories</option>
    <
    option value="Nova Scotia">Nova Scotia</option>
    <
    option value="Prince Edward I">Prince Edward Island</option>
    <
    option value="Québec">Qu&#233;bec</option>
    <option value="Saskatchewan">Saskatchewan</option>
    <
    option value="Yukon">Yukon</option>
    </
    optgroup>
    <
    optgroup label="Germany">
    <
    option value="Rheinland-Pfalz">Rheinland-Pfalz</option>
    <
    option value="Saarland">Saarland</option>
    <
    option value="Sachsen">Sachsen</option>
    <
    option value="Sachsen-Anhalt">Sachsen-Anhalt</option>
    <
    option value="Schleswig-Holst">Schleswig-Holstein</option>
    <
    option value="Nordrhein-Westf">Nordrhein-Westfalen</option>
    <
    option value="Niedersachsen">Niedersachsen</option>
    <
    option value="Hessen">Hessen</option>
    <
    option value="Hamburg">Hamburg</option>
    <
    option value="Bremen">Bremen</option>
    <
    option value="Brandenburg">Brandenburg</option>
    <
    option value="Berlin">Berlin</option>
    <
    option value="Bavaria">Bavaria</option>
    <
    option value="Thüringen">Th&#252;ringen</option>
    <option value="Baden-Wuerttemb">Baden-Wuerttemberg</option>
    <
    option value="Mecklenburg-Vor">Mecklenburg-Vorpommern</option>
    </
    optgroup>
    <
    optgroup label="India">
    <
    option value="Andaman and Nic">Andaman and Nicobar Islands</option>
    <
    option value="Orissa">Orissa</option>
    <
    option value="Nagaland">Nagaland</option>
    <
    option value="Mizoram">Mizoram</option>
    <
    option value="Meghalaya">Meghalaya</option>
    <
    option value="Manipur">Manipur</option>
    <
    option value="Maharashtra">Maharashtra</option>
    <
    option value="Punjab">Punjab</option>
    <
    option value="Pondicherry">Pondicherry</option>
    <
    option value="Rajasthan">Rajasthan</option>
    <
    option value="Sikkim">Sikkim</option>
    <
    option value="Tamil Nadu">Tamil Nadu</option>
    <
    option value="Tripura">Tripura</option>
    <
    option value="Uttar Pradesh">Uttar Pradesh</option>
    <
    option value="Uttaranchal">Uttaranchal</option>
    <
    option value="West Bengal">West Bengal</option>
    <
    option value="Madhya Pradesh">Madhya Pradesh</option>
    <
    option value="Lakshadweep Isl">Lakshadweep Islands</option>
    <
    option value="Andhra Pradesh">Andhra Pradesh</option>
    <
    option value="Arunachal Prade">Arunachal Pradesh</option>
    <
    option value="Assam">Assam</option>
    <
    option value="Bihar">Bihar</option>
    <
    option value="Chandigarh">Chandigarh</option>
    <
    option value="Chhattisgarh">Chhattisgarh</option>
    <
    option value="Daman and Diu">Daman and Diu</option>
    <
    option value="Dadra and Nagar">Dadra and Nagar Haveli</option>
    <
    option value="Delhi">Delhi</option>
    <
    option value="Goa">Goa</option>
    <
    option value="Gujarat">Gujarat</option>
    <
    option value="Haryana">Haryana</option>
    <
    option value="Himachal Prades">Himachal Pradesh</option>
    <
    option value="Jammu and Kashm">Jammu and Kashmir</option>
    <
    option value="Karnataka">Karnataka</option>
    <
    option value="Kerala">Kerala</option>
    <
    option value="Jharkhand">Jharkhand</option>
    </
    optgroup>
    <
    optgroup label="Mexico">
    <
    option value="Aguascalientes">Aguascalientes</option>
    <
    option value="Tabasco">Tabasco</option>
    <
    option value="Tamaulipas">Tamaulipas</option>
    <
    option value="Tlaxcala">Tlaxcala</option>
    <
    option value="Veracruz-Llave">Veracruz-Llave</option>
    <
    option value="Yucatan">Yucatan</option>
    <
    option value="Zacatecas">Zacatecas</option>
    <
    option value="Colima">Colima</option>
    <
    option value="Mexico">Mexico</option>
    <
    option value="Coahuila">Coahuila</option>
    <
    option value="Chihuahua">Chihuahua</option>
    <
    option value="Chiapas">Chiapas</option>
    <
    option value="Campeche">Campeche</option>
    <
    option value="Baja California">Baja California Sur</option>
    <
    option value="Baja California">Baja California Norte</option>
    <
    option value="Sonora">Sonora</option>
    <
    option value="Sinaloa">Sinaloa</option>
    <
    option value="Durango">Durango</option>
    <
    option value="Guanajuato">Guanajuato</option>
    <
    option value="Guerrero">Guerrero</option>
    <
    option value="Hidalgo">Hidalgo</option>
    <
    option value="Jalisco">Jalisco</option>
    <
    option value="Distrito Federa">Distrito Federal</option>
    <
    option value="Michoacan de Oc">Michoacan de Ocampo</option>
    <
    option value="Morelos">Morelos</option>
    <
    option value="Nayarit">Nayarit</option>
    <
    option value="San Luis Potosí">San Luis Potos&#237;</option>
    <option value="Quintana Roo">Quintana Roo</option>
    <
    option value="Querétaro">Quer&#233;taro</option>
    <option value="Puebla">Puebla</option>
    <
    option value="Oaxaca">Oaxaca</option>
    <
    option value="Nuevo Leon">Nuevo Leon</option>
    </
    optgroup>
    <
    optgroup label="USA">
    <
    option value="Iowa">Iowa</option>
    <
    option value="Kansas">Kansas</option>
    <
    option value="Kentucky">Kentucky</option>
    <
    option value="Louisiana">Louisiana</option>
    <
    option value="Maine">Maine</option>
    <
    option value="Maryland">Maryland</option>
    <
    option value="Massachusetts">Massachusetts</option>
    <
    option value="Michigan">Michigan</option>
    <
    option value="Minnesota">Minnesota</option>
    <
    option value="Mississippi">Mississippi</option>
    <
    option value="Missouri">Missouri</option>
    <
    option value="Indiana">Indiana</option>
    <
    option value="Illinois">Illinois</option>
    <
    option value="Idaho">Idaho</option>
    <
    option value="Alaska">Alaska</option>
    <
    option value="Arizona">Arizona</option>
    <
    option value="Arkansas">Arkansas</option>
    <
    option value="California">California</option>
    <
    option value="Colorado">Colorado</option>
    <
    option value="Connecticut">Connecticut</option>
    <
    option value="Delaware">Delaware</option>
    <
    option value="Florida">Florida</option>
    <
    option value="Georgia">Georgia</option>
    <
    option value="Hawaii">Hawaii</option>
    <
    option value="Alabama">Alabama</option>
    <
    option value="Montana">Montana</option>
    <
    option value="South Carolina">South Carolina</option>
    <
    option value="South Dakota">South Dakota</option>
    <
    option value="Tennessee">Tennessee</option>
    <
    option value="Texas">Texas</option>
    <
    option value="Utah">Utah</option>
    <
    option value="Vermont">Vermont</option>
    <
    option value="Virginia">Virginia</option>
    <
    option value="Washington">Washington</option>
    <
    option value="West Virginia">West Virginia</option>
    <
    option value="Wisconsin">Wisconsin</option>
    <
    option value="Rhode Island">Rhode Island</option>
    <
    option value="Pennsylvania">Pennsylvania</option>
    <
    option value="Oregon">Oregon</option>
    <
    option value="Nebraska">Nebraska</option>
    <
    option value="Nevada">Nevada</option>
    <
    option value="New Hampshire">New Hampshire</option>
    <
    option value="New Jersey">New Jersey</option>
    <
    option value="New Mexico">New Mexico</option>
    <
    option value="New York">New York</option>
    <
    option value="North Carolina">North Carolina</option>
    <
    option value="North Dakota">North Dakota</option>
    <
    option value="Ohio">Ohio</option>
    <
    option value="Oklahoma">Oklahoma</option>
    <
    option value="Wyoming">Wyoming</option>
    </
    optgroup>    
       </
    select

  6. #6
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,611
    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 09: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

    eBookworm.us

  7. #7
    Join Date
    Jun 2006
    Location
    Under your bed
    Posts
    357
    Quote Originally Posted by NogDog View Post
    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

  8. #8
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,611
    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

    eBookworm.us

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles