Welcome, guest! Please login or register.

    * Shoutbox

    RefreshHistory
    • King Bosnia: Any recommendations to private servers?
      October 18, 2018, 04:47:35 PM
    • FightMexoxo: oi i have the files if anyone whats em
      October 18, 2018, 12:44:42 AM
    • The Wanderer: Whoops
      October 15, 2018, 12:56:18 AM
    • The Wanderer: Making own operating system
      October 15, 2018, 12:56:11 AM
    • Travas: no
      October 07, 2018, 04:03:36 PM
    • Saltyspade10: Server still up and running?
      October 05, 2018, 07:13:41 PM
    • Wesam: Can someone upload moparscape.jar?
      October 05, 2018, 11:43:43 AM
    • Ligoe: yo
      September 30, 2018, 12:48:15 PM
    • Ligoe: how come i can't login the game
      September 30, 2018, 12:48:03 PM
    • fuzzout: Anybody here with experience in using Luna source? I'm looking to develop something, but can't seem to get any of my clients to work with it...
      September 26, 2018, 10:47:27 AM
    • `Discardedx2`: ?
      September 21, 2018, 12:24:09 PM
    • Pookey71: ARE YOU HERE TO RECEIVE MY LIMP PENIS!?
      September 20, 2018, 11:55:24 PM
    • Pookey71: THIS IS DEMOCRACY MANIFESTO
      September 20, 2018, 11:55:07 PM
    • Vasi28: Гранд
      September 03, 2018, 07:06:22 PM
    • Rash: test
      August 30, 2018, 09:09:18 PM
    • Guruu:[link]
      August 30, 2018, 12:04:45 AM
    • Guruu: am i allowed to post links shoutbox?
      August 30, 2018, 12:03:46 AM
    • drubrkletern:[link] feedback wanted
      August 28, 2018, 03:55:17 PM
    • bugz000: Anyone got a link for the old ass silab client and cache.zip? from mopar 3.2 era :) 317
      August 25, 2018, 12:48:15 AM
    • cholland2015: Does anyone know where I can download the old moparscape client/server?
      August 24, 2018, 02:30:51 PM

    Author Topic: MySQL question  (Read 577 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?
    Runescape Gambling

    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.
    Runescape Gambling

    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?
    Runescape Gambling

    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