Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New to the CF scene
    Join Date
    Apr 2014
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Complicated (for a noob ie. me) php mysql query

    Hi,

    I want to start off my saying hello! AND I am a TOTAL NOOB with php and mySQL.

    I wish I wasn't even doing this but I have been dumped in the deep end by people that have let me down. It is 1 day away from "completion" and I am left to pick up and mend the pieces of this system I have which doesn't fully function!

    I have tried to explain this as logically and clearly as possible. If you need clarification please let me know and I will do my best!

    Thanks in advance for any help as I am now desperate to get this thing done!

    I have 3 issues!

    **ISSUE1**


    Matching Keys
    (x)



    TABLE1

    | C_ID | C_Eth_O (x) |
    +--------------------+-------------------------+
    | 234 | 8 |
    | 341 | 11 |
    | 440 | 2 |


    TABLE2

    | Eth_ID (x) | C1_Eth_O |
    +-----------------------+------------------------+
    | 2 | Label2 |
    | 8 | Label8 |
    | 9 | Label9 |
    | 11 | Label11 |


    I need to list all the "C1_Eth_O" values in a multi select list. The user will make multiple selections from that list. When submitted I need to get the "Eth_ID" value(s) and return all the value(s) from TABLE1 where any of the selected options "C_Eth_O" = "Eth_ID". Each "C_ID" can only have one "C_Eth_O".


    **ISSUE2**


    Matching Keys
    (x)
    (o)


    TABLE1

    | C_ID (x) |
    +---------------------+
    | 234 |
    | 341 |
    | 440 |


    TABLE3

    | Ail_ID (o) | Ali_Label |
    +-------------------------+-------------------------+
    | 1 | Label1 |
    | 2 | Label2 |
    | 3 | Label3 |
    | 4 | Label4 |
    | 5 | Label5 |
    | 6 | Label6 |


    TABLE4

    | CA_ID | C1_ID (x) | Ail1_ID (o) |
    +---------------------+------------------------+------------------------+
    | 1 | 234 | 1 |
    | 2 | 341 | 4 |
    | 3 | 341 | 6 |
    | 4 | 440 | 2 |



    I need to list all the "Ali_Label" values from TABLE3 in a multi select list. The user will make multiple selections from that list. When submitted I need to get the "Ail_ID" value(s) from TABLE3 and return all the value(s) from TABLE1 where any of the selected match in TABLE4 "Ali1_ID" = "Ail_ID" & "C1_ID" = "C_ID". Each "C_ID" can have multiple "Ail_ID" values



    **ISSUE3**


    Matching Keys
    (x)
    (o)
    (-)


    TABLE1

    | C_ID (x) |
    +---------------------+
    | 234 |
    | 341 |
    | 440 |


    TABLE3

    | Ail_ID (o) | Ali_Label |
    +------------------------+-------------------------+
    | 1 | Label1 |
    | 2 | Label2 |
    | 3 | Label3 |
    | 4 | Label4 |
    | 5 | Label5 |
    | 6 | Label6 |



    TABLE5

    | R_ID | C1_ID (x) | Cf1_ID (-) |
    +-----------------------+-----------------------+-----------------------+
    | 1 | 234 | 768 |
    | 2 | 234 | 854 |
    | 3 | 234 | 768 |
    | 4 | 440 | 854 |



    TABLE6


    | CA_ID | Cf_ID (-) | Ail1_ID (o) |
    +---------------------+------------------------+------------------------+
    | 1 | 768 | 1 |
    | 2 | 854 | 4 |
    | 3 | 768 | 6 |
    | 4 | 880 | 2 |


    I need to list all the "Ali_Label" values from TABLE3 in a multi select list. The user will make multiple selections from that list. When submitted I need to get the "Ail_ID" value(s) from TABLE3 and return all the value(s) from TABLE1 where any of the selected "Ali_ID" = "Ail1_ID" & "C1_ID" = "C_ID" & "Cf1_ID" = "Cf_ID". Each "C_ID" can have multiple "Cf1_ID" values AND each "Cf_ID" can have multiple "Ail1_ID" values.

    I have the system working returning simple queries such as

    SELECT * FROM table1 WHERE C_ID = 234

    but nothing with multiple tables and multiple results per C_ID!

    Thanks in advance for any help!

  • #2
    New to the CF scene
    Join Date
    Apr 2014
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have the code all setup pulling and displaying simple queries where the ID and the required result are in the same table but not multiples. Some of the code is below.


    1 part
    ====================

    $town = trim($_GET['town']);
    $town = strip_tags($town); // remove any html/javascript.
    $townDB = mysql_real_escape_string($town); // prevent sql injection.


    2 part
    ====================

    $types[] = isset($_GET['town'])?"`Carers_Town` LIKE '%{$townDB}%'":'';



    3 part
    ====================
    <div class="control-group">
    <label class="control-label" for="input">Town</label>
    <div class="controls">
    <input id="input" class="input-medium" type="text" name="town" value="<?php echo isset($town)?htmlspecialchars($town):''; ?>">
    </div>
    </div>

  • #3
    Regular Coder Linux_Sage's Avatar
    Join Date
    Mar 2014
    Location
    Sterling,VA
    Posts
    105
    Thanks
    0
    Thanked 10 Times in 10 Posts
    Wow, those table/column names are extremely user unfriendly.
    This I guess would help your first issue. You just need to join when those two colums are equal. This stuff is hard to read.

    Code:
    SELECT t.C_Eth_O FROM TABLE1 t
    INNER JOIN TABLE2 t2 ON t.C_Eth_O = t2.Eth_ID
    Basically you need to select the values you're looking for and join extra tables based on values those other tables possess. Lookup more info on JOINS or other ways of combining data across several tables. I'm not sure I want to endure any further pain of sifting through those poorly worded table names.


  •  

    Tags for this Thread

    Posting Permissions

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