SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Member
    Join Date
    Feb 2014
    Location
    netherlands
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    This table does not contain a unique column...

    Hello, I am not so well known with mysql, but here is the thing.

    In my database I created a table named: province_cities

    After some troubles I managed to import my list of province and city
    Structure is: id 1 = "Province", id 2 = "City". Both Type text.

    But as you can see on the topic title, I am not able to do anything but read it. lol. And maybe sometime the names will change.

    I need this table to perform postings by selecting a Province and making a choice of one belonging city.

    Any suggestions would help me much.

    ps. I am using Joomla 3.2 with SEBLOD.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    not sure what you're asking

    please do a SHOW CREATE TABLE for your table

    the unique key will likely be a composite key consisting of both province and city
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Feb 2014
    Location
    netherlands
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    see attached image

    If you mean Show Created Table, then I attach the image on this post. Hope it helps...

    table-example.jpg

  4. #4
    SitePoint Member
    Join Date
    Feb 2014
    Location
    netherlands
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just an update >> From a example I see on internet they had 2 tables. One is for US states (in my case Provincies), and the other was for cities with State Letters (in my case Gemeenten with Provincie Letters).

    And what I would like to establish is through Joomla have a selectbox with choices Provincies, and eventually make a choice out of Gemeenten inside that Provincie.
    I hope this makes sense. Can anyone tell me if this is the correct way?

    First table is called 'provincies':
    Code:
    SET FOREIGN_KEY_CHECKS=0;
    -- ----------------------------
    -- Table structure for provincies
    -- ----------------------------
    DROP TABLE IF EXISTS `provincies`;
    CREATE TABLE `provincies` (
      `provincie` varchar(22) NOT NULL,
      `provincie_code` char(2) NOT NULL,
      PRIMARY KEY (`provincie_code`)
    ) ENGINE=MyISAM;
    
    -- ----------------------------
    -- Records 
    -- ----------------------------
    INSERT INTO `provincies` VALUES ('Drente', 'DR');
    Second table is called 'gemeenten':
    Code:
    SET FOREIGN_KEY_CHECKS=0;
    -- ----------------------------
    -- Table structure for gemeenten
    -- ----------------------------
    DROP TABLE IF EXISTS `gemeenten`;
    CREATE TABLE `gemeenten` (
      `gemeente` varchar(50) NOT NULL,
      `provincie_code` char(2) NOT NULL,
      KEY `idx_provincie_code` (`provincie_code`)
    ) ENGINE=MyISAM;
    
    -- ----------------------------
    -- Records 
    -- ----------------------------
    INSERT INTO `gemeenten` VALUES ('Aa en Hunze', 'DR');

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    you are definitely on the right track, and those tables will do the job nicely

    to complete, you should declare that gemeente is a PRIMARY KEY

    also, provincie_code in gemeenten should be a FOREIGN KEY referencing provincie_code in provincies
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Member
    Join Date
    Feb 2014
    Location
    netherlands
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    to complete, you should declare that gemeente is a PRIMARY KEY

    also, provincie_code in gemeenten should be a FOREIGN KEY referencing provincie_code in provincies
    Hello, thanks for thinking with me.

    I do have a problem with that. I tried in table Gemeenten to set Gemeente as Primary key, and it says: duplicate entry "Bergen" for key "PRIMARY".
    I think the reason is that I have names which often have the same name inside: Bergen, Haaksbergen, Bergen op Zoom, etc.
    And, I don't have id's inside that table, should I have those?

    Maybe it is easier if I sned the tables?

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by bwakad View Post
    I do have a problem with that. I tried in table Gemeenten to set Gemeente as Primary key, and it says: duplicate entry "Bergen" for key "PRIMARY".
    this error message is pretty clear -- you have two rows with the exact same value

    take a look, you will find them


    Quote Originally Posted by bwakad View Post
    And, I don't have id's inside that table, should I have those?
    no!!


    Quote Originally Posted by bwakad View Post
    Maybe it is easier if I sned the tables?
    sorry, i have no idea what "sned" is
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Member
    Join Date
    Feb 2014
    Location
    netherlands
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi thanks again,
    ps. 'sned' was ment to be 'send'. lol

    I checked the entries and see inside some Provincie(s) it has a Gemeente with the exact same name... bummer.

    How can I accomplish this. Do I need to make it just one table?
    Users should enter : Gemeente and I need to display: Gemeente | Provincie
    After that they need to be able to click Provincie and display a list of ALL inside Provincie
    And they need to be able to click Gemeente and display a list of ALL inside Gemeente

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by bwakad View Post
    I checked the entries and see inside some Provincie(s) it has a Gemeente with the exact same name... bummer.
    not really a problem at all
    Code:
    CREATE TABLE gemeenten 
    ( gemeente VARCHAR(50) NOT NULL
    , provincie_code CHAR(2) NOT NULL
    , PRIMARY KEY ( gemeente , provincie_code )
    , FOREIGN KEY ( provincie_code )
             REFERENCES provincies ( provincie_code )
    );
    by the way, make your tables innodb instead of myisam, so that the foreign key checking is enabled
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Member
    Join Date
    Feb 2014
    Location
    netherlands
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    not really a problem at all
    Code:
    CREATE TABLE gemeenten 
    ( gemeente VARCHAR(50) NOT NULL
    , provincie_code CHAR(2) NOT NULL
    , PRIMARY KEY ( gemeente , provincie_code )
    , FOREIGN KEY ( provincie_code )
             REFERENCES provincies ( provincie_code )
    );
    by the way, make your tables innodb instead of myisam, so that the foreign key checking is enabled
    Very good, Thank you so much!

    ps do you have an example on how to test this, I mean, how I can call a Gemeente and also see the Provincie? I am really just starting with db.

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by bwakad View Post
    how I can call a Gemeente and also see the Provincie?
    Code:
    SELECT p.provincie
         , g.gemeente
      FROM provincies AS p
    INNER
      JOIN gemeenten AS g
        ON g.provincie_code = p.provincie_code 
     WHERE g.gemeente LIKE 'Bergen%'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Member
    Join Date
    Feb 2014
    Location
    netherlands
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, really cool.
    So in general language it would mean something as:
    select provincie and gemeente
    from table provincies use provincie
    from table gemeenten use gemeente
    connect those two by provincie_code
    return the provincie and gemeente only if gemeente has the word Bergen inside

    I do not know so much of querying a databse, do you have some websites i could go to learn. I really do not want to take all your time...

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by bwakad View Post
    I do not know so much of querying a databse, do you have some websites i could go to learn.
    here's one

    http://www.sitepoint.com/simply-sql-the-from-clause/

    disregard the section on leading commas -- the code display is badly broken there
    rudy.ca | @rudydotca
    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
  •