Welcome, guest! Please login or register.

    * Shoutbox

    RefreshHistory
    • puta loca: or what section can i ask this
      Today at 05:45:08 AM
    • puta loca: does someoen has platinum ps v2 files?
      Today at 05:44:59 AM
    • w azza 3: server down??
      Today at 05:07:47 AM
    • charmie: rippppppppppppppppppppppppppppppppppppppppppppppp
      May 20, 2018, 09:03:41 PM
    • Tesco Value: eco reset? :o
      May 20, 2018, 08:54:27 PM
    • Tesco Value: aw is server down? :P
      May 20, 2018, 08:54:03 PM
    • mandmgalaxy: is the game down?
      May 20, 2018, 08:05:07 PM
    • bliss death: i believe 95% of the community disliked this change heavily as it came out of nowhere. and the fact you clear ironmen banks as well. terrible change. disappointed.
      May 20, 2018, 06:08:36 PM
    • bliss death: just wondering when the server is gonna be fixed and reverted
      May 20, 2018, 06:07:36 PM
    • Saltyspade10: I'll be back ;)
      May 15, 2018, 04:43:53 PM
    • Nunubuffs:[link]
      May 15, 2018, 12:06:25 PM
    • Nunubuffs: .info god
      May 15, 2018, 12:05:40 PM
    • Nunubuffs: Www.scaperune
      May 15, 2018, 12:05:25 PM
    • Nunubuffs: Www.scaperune
      May 15, 2018, 12:05:18 PM
    • Wilkooo: Meep meep
      May 15, 2018, 04:49:58 AM
    • FoHammer: Checkout my thread here we're now live: [link]
      May 14, 2018, 08:26:59 PM
    • FoHammer: Beemonumerouno
      May 14, 2018, 08:26:25 PM
    • DeathsChaos9::|
      May 14, 2018, 07:46:09 PM
    • Service.Man: Hi guys. I've just made a new account and downloaded the server - Still can't log in. Any tips?
      May 14, 2018, 01:37:43 AM
    • beemonumerouno: where can i ask if someone has a good custom server for free?
      May 11, 2018, 04:00:25 PM

    Author Topic: MySQL question  (Read 533 times)

    0 Members and 1 Guest are viewing this topic.

    OfflineDavidi2

    • Member
    • ****
    • *
    • Posts: 23,272
    • Thanks: +0/-0
      • View Profile
    MySQL question
    « on: January 29, 2015, 02:52:43 PM »
    Hey, random MySQL question. Is there a way to design a table so that one of the columns is cross checked against another table when you insert values, and it rejects it if the value doesn't exist?

    For example:

    Manufacturers table
    Company, Plant, HQ, PID

    Boeing, 56, Seattle, 5571
    Boeing, 27, Houston, 1671
    Airbus, 12, Frankfurt, 1673
    Blahblah, 271, Toronto, 1234
    (company -> plant is a unique index)

    And now I want to create a table of planes that has a 'company' field that matches up to a company in the manufacturers table. The index is between Company & Plant in that table, but is there a way to just make sure that when inserting into the planes table that the company exists, not necessarily the exact company & plant? What sort of relationship would that be?
    RS2Ad banner

    OfflineZymus

    • Member
    • ****
    • *
    • Posts: 7,267
    • Thanks: +0/-0
      • View Profile
    Re: MySQL question
    « Reply #1 on: January 29, 2015, 03:01:48 PM »
    You're probably looking for a foreign key to the company. That way if you go to insert it, if the company doesn't exist, it will fail to insert.
    RS2Ad banner

    OfflineDavidi2

    • Member
    • ****
    • *
    • Posts: 23,272
    • Thanks: +0/-0
      • View Profile
    Re: MySQL question
    « Reply #2 on: January 29, 2015, 03:03:34 PM »
    You're probably looking for a foreign key to the company. That way if you go to insert it, if the company doesn't exist, it will fail to insert.
    How would I set that up if the company itself isn't a key because it's linked to the specific plant?
    RS2Ad banner

    Offlinesk8rdude461

    • MOPARSCAPE WAS HACKED
    • Member
    • ****
    • *
    • Posts: 12,471
    • Thanks: +0/-0
      • View Profile
    Re: MySQL question
    « Reply #3 on: January 29, 2015, 03:06:41 PM »
    Okay. Welp. I'm not web design guru nor am I a MySQL guru.
    But here's what I could think of (For your solution):

    Write a query to select all the tables from the manufacturers table, you could limit it down to just grabbing the Company column.
    Code: MySQL
    1. SELECT`Company`FROM`Manufacturers`

    Which would return an array of your results..
    You'd then store that array in.. Well whatever. Let's assume it's being stored into a ArrayList in Java.
    Code: Java
    1. while(results.next()){
    2.      companyList.add(results.getNextString("Company"));// Adds name to list..
    3. }

    And then you now can check any plane you're adding into the table against the ArrayList..

    Code: Java
    1. if(companyList.contains(whatever))
    2.      // Insert that b1tch.
    3. else
    4.      // Can't insert, doesn't exist.
    5.  

    Only way I could think of doing it. Using a secondary language to determine whether or not the String exists in the database..

    Again, someone probably knows a better way to do this.

    OfflineDavidi2

    • Member
    • ****
    • *
    • Posts: 23,272
    • Thanks: +0/-0
      • View Profile
    Re: MySQL question
    « Reply #4 on: January 29, 2015, 03:16:10 PM »
    I know plenty of ways to get around it, but I'm talking about a strictly MySQL design related approach sk8r :P Thanks tho

    OfflineRuneAgent

    • wololo
    • Member
    • ****
    • *
    • *
    • Posts: 7,515
    • Thanks: +0/-0
      • View Profile
      • MITB FORUMS
    Re: MySQL question
    « Reply #5 on: January 29, 2015, 03:56:47 PM »

    Offlinejustaguy

    • Member
    • ****
    • *
    • Posts: 706
    • Thanks: +0/-0
      • View Profile
    Re: MySQL question
    « Reply #6 on: January 29, 2015, 04:29:44 PM »
    Well I just put together a quick SQL fiddle that, from as far as I can understand from your question, solves this problem. Check out the fiddle here: http://sqlfiddle.com/#!2/9e52b/1.

    Basically what I'm doing is while I'm inserting into `planes` I'm running a SELECT query at the same time (basically an INSERT INTO SELECT query) so that I can query all the companies from the `manufacturers` table. Basically I only insert if I can find the specified company and I limit the result-set to 1 otherwise it would insert multiple rows into `planes`. I'm hard-coding the rest of the values to insert into `planes` in the SELECT so hopefully that isn't a problem when you're generating the query. If it can't find the company in `manufacturers`, basically it's a null result-set and nothing happens.

    Seems hackish but I think it should help.

    EDIT: Not sure how I missed silab's post but they look almost equivalent; mine only uses a single SELECT query as opposed to 2.
    « Last Edit: January 29, 2015, 04:32:44 PM by justaguy »
    RIP

    OfflineDavidi2

    • Member
    • ****
    • *
    • Posts: 23,272
    • Thanks: +0/-0
      • View Profile
    Re: MySQL question
    « Reply #7 on: January 29, 2015, 05:16:38 PM »
    https://www.techonthenet.com/mysql/exists.php should do it for you

    From that post:
    Code: SQL
    1. INSERTINTO contacts
    2. (contact_id, contact_name)
    3. SELECT supplier_id, supplier_name
    4. FROM suppliers
    5. WHEREEXISTS(SELECT*
    6.               FROM orders
    7.               WHERE suppliers.supplier_id = orders.supplier_id);

    That's the type of thing I mean, but I was wondering if it was possible to use some sort of relationship to integrate this directly into the table design without having to use a special query with each insert.

    For example, if I created another table that contained the company names as a primary key only, then I could create a relationship from that name, to the company plant name, then to the plane company name (or an id relating to that key)

    I think I am going to kind of do something like that instead.

    The exact thing this is being used for is NPC drop tables, I wanted to link NPC definitions to the drop tables there, but instead I think Im going to do something like create a table of

    table_id, npc_ids

    and then the drops tables are just linked to the table id
    « Last Edit: January 29, 2015, 05:21:14 PM by Davidi2 »

    OfflineZymus

    • Member
    • ****
    • *
    • Posts: 7,267
    • Thanks: +0/-0
      • View Profile
    Re: MySQL question
    « Reply #8 on: January 30, 2015, 02:47:27 PM »
    https://www.techonthenet.com/mysql/exists.php should do it for you

    From that post:
    Code: SQL
    1. INSERTINTO contacts
    2. (contact_id, contact_name)
    3. SELECT supplier_id, supplier_name
    4. FROM suppliers
    5. WHEREEXISTS(SELECT*
    6.               FROM orders
    7.               WHERE suppliers.supplier_id = orders.supplier_id);

    That's the type of thing I mean, but I was wondering if it was possible to use some sort of relationship to integrate this directly into the table design without having to use a special query with each insert.

    For example, if I created another table that contained the company names as a primary key only, then I could create a relationship from that name, to the company plant name, then to the plane company name (or an id relating to that key)

    I think I am going to kind of do something like that instead.

    The exact thing this is being used for is NPC drop tables, I wanted to link NPC definitions to the drop tables there, but instead I think Im going to do something like create a table of

    table_id, npc_ids

    and then the drops tables are just linked to the table id

    So let's say you had two tables, Npc and NpcDrops. What exactly do you want to be able to do between them? Get a list of drops based on the NpcId?

    OfflineDavidi2

    • Member
    • ****
    • *
    • Posts: 23,272
    • Thanks: +0/-0
      • View Profile
    Re: MySQL question
    « Reply #9 on: January 30, 2015, 03:35:53 PM »
    The reason it was an issue is simply because the way the tables are designed, you have 4 rows per drop table id (or table name), so it couldn't be a unique column in itself. It could be a unique index on two columns, but that isn't allowed in the relationship.

    I did a bit of a hack around by creating an extra table as a middleman sort of thing that has the drop table names and npc ids in it, and having that name be in a relationship with the drop tables.

    OfflineSi Force

    • The beekeeper
    • Member
    • ****
    • *
    • Posts: 5,328
    • Thanks: +0/-0
      • View Profile
    Re: MySQL question
    « Reply #10 on: January 30, 2015, 07:25:14 PM »
    I haven't really looked into it much, but couldn't you make use of a MySQL trigger using "BEFORE INSERT ON X TABLE" ?
    There is a house in New Orleans

    Check out the Official Moparscape Server! Being developed by Sinisoul and Davidi2

     

    Copyright © 2017 MoparScape. All rights reserved.
    Powered by SMFPacks SEO Pro Mod |
    SimplePortal 2.3.5 © 2008-2012, SimplePortal