SitePoint Sponsor

User Tag List

Results 1 to 19 of 19
  1. #1
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    command line query and insert

    Hi,

    familiarising myself with the phpMyAdmin command line and I need to do a query and insert. Is that possible?

    firstly I can get the data I need for the insert.

    Code MySQL:
    SELECT business_id
         , address_id 
     from business_details

    but how do I then insert ignore into business_addresses with that data? do I need to build a script to process it?

    Code MySQL:
    insert ignore into business_addresses (business_id, address_id)
         values (business_id, address_id);

    bazz
    Last edited by IBazz; Jan 11, 2010 at 07:44. Reason: clarification of command line

  2. #2
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    8,897
    Mentioned
    138 Post(s)
    Tagged
    2 Thread(s)
    You can combine the two queries:

    Code SQL:
    INSERT IGNORE INTO
      business_addresses (business_id, address_id)
    SELECT
      business_id, address_id
    FROM business_details

    PS. This query gives me the feeling your database is not in 1NF. Please look into that
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by IBazz View Post
    familiarising myself with the phpMyAdmin command line
    huh? are you referring to the SQL tab? the big textarea form field where you can enter SQL statements?

    i'd hardly call that a "command line" -- more descriptive would be "SQL window" as other software applications also use this term
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks ScallioXTX,

    Thank you for the pointer about 1NF but; which part of the query suggests I've not achieved it? Maybe I have missed something in my effort to achive 3NF.

    I did have 1 address per business record, hence the two values in business_details (PK and FK).

    Now I am making a m:m table so each business can have one or more address depending on type, eg billing, postal, location, admin, etc. So I have started to fill a table with FKs to business_details and FKs to addresses where there is a table (addresses) and a table (business_details).

    The plan is that once I have built and populated the m:m table, I shall remove the 1:1 relationship for addresses from the business_details table

    Trying to keep it all normalised here

    Please let me know if I still seem to be violating 1NF.

    bazz
    Last edited by IBazz; Jan 11, 2010 at 08:43. Reason: correcting 1:m to m:m

  5. #5
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    huh? are you referring to the SQL tab? the big textarea form field where you can enter SQL statements?

    Yup rudy, you got it. OK, sorry for the error in terminology.

    bazz

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    regarding your m:m business:address structure...

    at what time to you insert a new address? do you pre-populate this table with all addresses, and then wait for businesses to link to them? or do you create an address only if at least one business needs it? and how do you detect duplicate addresses? will you delete an address if it no longer has any businesses linked to it? how do you detect this?

    there's a heck of a lot of extra coding required to maintain an address table, you see

    what i'm driving at is that many people declare an address table when in fact they really don't need one at all!!!

    the "acid test" is... are you interested in addresses regardless of whether they have any business?

    i'll bet the answer is no
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks rudy,

    when a business is subscribing, they enter the addresses for billing, administrator, location etc as appropriate. (one business - many addresses)

    So the addresses are entered to the table, addresses.
    The business is entered to business_details

    so as not to have duplicate addresses in table addresses, I thought I should have a third table to associate business, addresses and address type.

    Code MySQL:
    CREATE TABLE business_addresses
    ( address_id int(11) NOT NULL
    , business_id int(11) NOT NULL
    , address_type varchar(32) NOT NULL default 'Location'
    , PRIMARY KEY  (address_id,business_id,address_type)
    , KEY reverse_ix (business_id,address_id,address_type)
    , constraint business_addresses_business_fk
        foreign key (business_id)
          references business_details (business_id) on delete cascade
    , constraint business_addresses_address_fk
        foreign key (address_id)
          references addresses(id) on delete cascade
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 collate utf8_unicode_ci;

    The other possibility I thought of was not having the 'joining table'.

    Instead, I could have each address listed and in its record, store what the purpose of the address is to be, eg admin, postal etc. But I moved away from that thinking because a business which has one address for all types (billing, admin etc), would have that same address listed numerous times in the address table where the only difference between one if its records and another, would be the field `address_type`.

    which is more correct? having the joining table or, making the address table like this (where one address may be repeated for each address type):-


    Code MySQL:
    CREATE TABLE address
    ( business_id int(11) NOT NULL
    , address_type varchar(32) NOT NULL
    , house_name varchar(64) NOT NULL
    , house_number varchar(12) NOT NULL
    , address_1 varchar(64) NOT NULL
    , address_2 varchar(64) NOT NULL
    , address_3 varchar(64) NOT NULL
    , town varchar(168) NOT NULL
    , townland varchar(64) NOT NULL
    , county varchar(32) NOT NULL
    , city varchar(60) NOT NULL
    , post_code varchar(11) NOT NULL
    , country_name varchar(90) NOT NULL
    , grid varchar(12) NOT NULL
    , latitude varchar(12) NOT NULL
    , longitude varchar(12) NOT NULL
    , northings varchar(6) NOT NULL
    , eastings varchar(6) NOT NULL
    , PRIMARY KEY  (business_id,address_type)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 collate utf8_unicode_ci;

    bazz

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    in my opinion -- and that's the key part of this sentence -- you should not have an address table at all

    you need:

    - a businesses table, sans addresses
    - a business addresses table, with one row per business address

    so you have a 1:m relationship, but you do ~not~ have the m:1 relationship to an addresses table

    in essence, the business_addresses table is ~like~ the joining table, but instead of joining to the addresses table (which you don't need), it simply carries the address

    please let me know if you don't understand this before we move on
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That makes sense. It also seems to seem very similar to this table set up.


    Code MySQL:
     
    CREATE TABLE address
    ( business_id INT(11) NOT NULL
    , address_type VARCHAR(32) NOT NULL
    , house_name VARCHAR(64) NOT NULL
    , house_number VARCHAR(12) NOT NULL
    , address_1 VARCHAR(64) NOT NULL
    , address_2 VARCHAR(64) NOT NULL
    , address_3 VARCHAR(64) NOT NULL
    , town VARCHAR(168) NOT NULL
    , townland VARCHAR(64) NOT NULL
    , county VARCHAR(32) NOT NULL
    , city VARCHAR(60) NOT NULL
    , post_code VARCHAR(11) NOT NULL
    , country_name VARCHAR(90) NOT NULL
    , grid VARCHAR(12) NOT NULL
    , latitude VARCHAR(12) NOT NULL
    , longitude VARCHAR(12) NOT NULL
    , northings VARCHAR(6) NOT NULL
    , eastings VARCHAR(6) NOT NULL
    , PRIMARY KEY  (business_id,address_type)
    , constraint business_address_business_fk
        foreign key (business_id)
          references business_details (business_id) on delete cascade
    ) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE utf8_unicode_ci;


    or does that statement mean I don't actually understand you?

    bazz

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    actually, yes, you do understand

    there remains then only the issue of how to indicate that a single address fulfills multiple roles (e.g. billing, admin etc.)
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    heh! thats why I thought the m:m table would be suitable.

    Mmmm, maybe we need a m:m between addresses and address_types? spur of the moment thought I'll try to think it through.

    bazz

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    well, as this is mysql, you could use the SET datatype

    normally i would not recommend it (its evilness approaches that of the ENUM)

    you want as few rows in this table as necessary (i.e. only distinct addresses per business) without the added complexity of an additional table

    naturally, SET introduces additional complexities, but you would only need it when returning all rows for a given business
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Reading up on SET and I don't like the part which shows the db will no longer be normalised.

    So, if I did make a joining table to relate addresses to each address_type, do you know of any inefficiencies or complications that could/would arise from that?

    Already, I can 'see' how querying for a business postal address would be quite a complex query for such a simple output of 1 result because it would necessitate joining three tables business_details, addresses and address types.

    And then there is the issue of setting a PK for addresses, if address_type were to be removed. I guess auto_increment is the only realistic way.


    bazz

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    if you don't like SET, just split that off

    it would ~not~ be a joining table

    business to business_addresses is one-to-many

    business_addresses to business_address_types is also one-to-many

    ABC inc
    -- 123 Main Street
    ---- biling
    ---- admin

    XYZ corp
    -- 456 Oak Street
    ---- billing
    -- 789 Elm Street
    ---- admin
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Rudy.

    Clearly I have become confused/forgetful, since the break for the festive season and manflu. I know they are 1:many relationships and quite where m:m came from I have no idea.

    I'll push myself for a day or two until I get back into all again.

    Thanks again.

    Bazz

  16. #16
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    8,897
    Mentioned
    138 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by r937 View Post
    normally i would not recommend it (its evilness approaches that of the ENUM)
    completely offtopic, but why is enum evil?
    I was under the impression I could save some serious data storage here by using an enum like 'Mr.', 'Ms.', 'Miss.'.

    Normally these would take 3 bytes ('Mr.' and 'Ms.') or 5 bytes ('Miss.') (assuming a 1 byte per character charset like ISO-8859-1, for UTF-16 you need to double the number of bytes).

    Using the enum, they use 1 bit (for Mr. and Ms.) and 2 bits for Miss. ('Mr.' = 0, can be stored in 1 bit, 'Ms.' = 1, can be stored in 1 bit, 'Miss.' = 2, can be stored in 2 bits).


    So, why is it evil?
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    do a search for "evil enum" and you'll see

    then holler if it doesn't make sense

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #18
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    8,897
    Mentioned
    138 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by r937 View Post
    do a search for "evil enum" and you'll see

    then holler if it doesn't make sense

    That is mostly about using switch() statements in conjunction with C or C#
    How I use it is just to save some data in the database and make sure only a valid value can be selected.
    Sounds like a whole different thing to me ...
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  19. #19
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    my apologies, your search term should have been "evil enum +mysql"

    have a look at this -- http://www.sitepoint.com/forums/showthread.php?t=430646

    here's another one -- http://www.dbforums.com/mysql/121388...s-vs-enum.html
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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