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 5 of 5
  1. #1
    New to the CF scene
    Join Date
    Sep 2013
    Posts
    9
    Thanks
    4
    Thanked 0 Times in 0 Posts

    fetching data from table2 while inserting records into table1

    I've got two tables, named "Table1" and "Table2", both have a common field named P_id.

    Now I need to add records to "Table1" by looping through an array, and with each record I need to retrieve a value from Table2's "coding" field.'

    I haven't got a clue how to add the value from that table-field though.
    Basicly the structure I'm having now is this:

    PHP Code:
    $a = array(12317);

    foreach (
    $a as $v) {

    mysqli_query($con,"INSERT INTO Persons (arrayvalue, table2value)
    VALUES ('"
    .$v."', 'Griffin')");


    I need to insert the value from Table2's "coding" field into the "table2value" field from Table1 (where value 'Griffin' is right now).

    I hope this makes sense, I think it's something with inner join, but I can't figure it out myself.

  • #2
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,982
    Thanks
    120
    Thanked 76 Times in 76 Posts
    Assuming (1, 2, 3, 17) are values that corensponds your tables P_id

    Code:
    mysqli_query($con,"INSERT INTO Persons (arrayvalue, table2value)
    VALUES ('".$v."', 'Griffin')");
    instead of 'griffin' part you could do subselect, like

    Code:
     (sellect coding from table 2 where P_id  = arrayvalue)
    Doh this is all veird, cant guarantee my answer is what you want.

    I need to insert the value from Table2's "coding" field into the "table2value" field from Table1 (where value 'Griffin' is right now).
    Basicaly that is wrong, if you have data in one table and you are copying it to another that is surely very bad.
    If data are already there you should be able to get them out from original table when searching, using join, provided, you have smart table design.
    In order to have smart table design, you have to fully understand what join does first, which I think, you don't yet.
    Last edited by BubikolRamios; 09-27-2013 at 06:20 PM.
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #3
    New to the CF scene
    Join Date
    Sep 2013
    Posts
    9
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Well basicly, the values in table two (field coding) are values that can be changed by a user. To look back in history I need to store those values into an other table, table1.

    So it ain't exactly copying from one table to another.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,588
    Thanks
    80
    Thanked 4,497 Times in 4,461 Posts
    Code:
    $a = array(1, 2, 3, 17); 
    
    foreach ($a as $v) 
    { 
        $sql = "INSERT INTO persons (P_ID, coding ) "
             . " SELECT P_ID, coding FROM othertable WHERE P_ID = " . $v;
        echo "DEBUG SQL: " . $sql . "<hr/>\n";
        mysqli_query($con, $sql );
    }
    Do *NOT* put apostrophes around NUMERIC (your P_ID) values unless the field being inserted into/updated/test is a NON-numeric field.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    Goliath (09-30-2013)

  • #5
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    INSERT. . . SELECT should be able to handle multiples correct?
    PHP Code:
    $a = array(12317);
    $sql "INSERT INTO persons (P_ID, coding) "
         
    ." SELECT P_ID, coding FROM othertable WHERE P_ID IN (" implode(', '$a) . ")";
    mysqli_query($con$sql); 
    Slightly less mess. Otherwise, a loop with a prepared statement would probably be alright.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • Users who have thanked Fou-Lu for this post:

    Goliath (09-30-2013)


  •  

    Posting Permissions

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