SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 52

Hybrid View

  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2009
    Location
    Newark, Notts, UK
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Transfering Data from one Table to another,

    Hello everyone again,

    I have some idea on what to do but not sure how to put it all togther,

    I would like to copy some data from one table to another table with my PHP script, the reason for this is so that when my customer clicks on a link it will automatically copy data from one to another so that it produces a new ID from invoicing numbers. All I need is the code, so it it will move my selected field data from the main table to the invoicing table so that I can my code can get the information from it and produce a new ID for invoicing purposes.

    If any one can help it would be much appreciated..

    Thanks

  2. #2
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    We're not going to write the code for you, for that you'd want a contractor (no such thing as a free lunch).

    However, we will help if you come across problems in your code. So, what have you got so far?
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2009
    Location
    Newark, Notts, UK
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cool,

    Here I go,

    $sql = "INSERT INTO jos_reg_term_1 (id, familyid) SELECT id, familyid FROM jos_reg"

    Not sure if that is even close to being correct but it doesn't work esither way.

    Thanks Again

  4. #4
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    That should actually work, though you'd want to limit it:
    PHP Code:
    $sql "INSERT INTO jos_reg_term_1 (jos_reg_term_1.id, jos_reg_term_1.familyid) SELECT jos_reg.id, jos_reg.familyid FROM jos_reg WHERE jos_reg.id = ..."
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  5. #5
    SitePoint Enthusiast
    Join Date
    Jul 2009
    Location
    Newark, Notts, UK
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I should have the values of the id, familyid listed at the top shouldn't I so;

    $id=mysql_etc($_GET['id]);

    or is this not needed?.

    I will have another go in a mo to see if |I have no leaft anything out, but I do not need to limit it as Ineed all sending to the new table for the invoices to be printed..

    Thanks

  6. #6
    SitePoint Enthusiast
    Join Date
    Jul 2009
    Location
    Newark, Notts, UK
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation

    Hello Everyone again,

    This is the code that I have written to get the data fromone table to another and it still does not work, can anyone have a look to see what I have done to make it not work,

    Thanks

    PHP Code:
    include '../includes/db.inc.php';

    if (isset(
    $_POST['term_1']))
    {
        
        if (
    waiting == 0)
        {
        
    $sql 'INSERT INTO jos_reg_term_1 (
        jos_reg_term_1.id, 
        jos_reg_term_1.familyid, 
        jos_reg_term_1.childfirstname, 
        jos_reg_term_1.childlastname, 
        jos_reg_term_1.knownas, 
        jos_reg_term_1.fathername, 
        jos_reg_term_1.mothername, 
        jos_reg_term_1.sunday,
        jos_reg_term_1.monday, 
        jos_reg_term_1.tuesday, 
        jos_reg_term_1.wednesday, 
        jos_reg_term_1.thursday, 
        jos_reg_term_1.class ) 
        SELECT 
        jos_reg.id, 
        jos_reg.familyid, 
        jos_reg.childfirstname, 
        jos_reg.childlastname, 
        jos_reg.knownas, 
        jos_reg.fathername, 
        jos_reg.mothername, 
        jos_reg.sunday,
        jos_reg.monday, 
        jos_reg.tuesday, 
        jos_reg.wednesday, 
        jos_reg.thursday, 
        jos_reg.class 
        FROM jos_reg'
    ;

        }
        
        include 
    'autumnterm.php';


  7. #7
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    PHP Code:
       if (waiting == 0
    Thats not PHP? SB $waiting ?

    Why don't you get it working with one or two values first, then build up?

    Half of that is unnecessary typing, you've stipulated the table, no need to keep tell it the same table
    Code:
      $sql = 'INSERT INTO jos_reg_term_1 (
        id, 
        familyid,
    ....
    Unless you are getting if from different tables
    Code:
      $sql = 'INSERT INTO jos_reg_term_1 as j (
        j.id, 
        j.familyid,
    ....

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,217
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    i would question the whole need for two tables here
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Enthusiast
    Join Date
    Jul 2009
    Location
    Newark, Notts, UK
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello and thanks for your replies,

    The reason I am having to transfer the data to a new table is so that it porduces a new ID number for invoicing and also to have a record of the invoice for that particular term. The idea would be for the data to transfered from the main table (jos_reg) and then over to each individual term table (jos_reg_term_1) so that we are able to have a record of the invoices that have been created.

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,217
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    there are many individual term tables??

    now i ~really~ question your design
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Enthusiast
    Join Date
    Jul 2009
    Location
    Newark, Notts, UK
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    R937, I would like to thank you for the help that you given me, as I am new to SQL and PHP you comments are as much help to me as the information I already know.. I was under the impression that Forums were here to help individuals not to ridicule them?? Being the Staff Member of the year and a SQL consultant I would expect more of profesional answer, but hay what do I know..!!!

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,217
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    newark, that ~was~ a professional answer

    ridicule? you're being way too sensitive

    a database design with multiple tables for the same data is very poor

    i am sorry if you do not like hearing that
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Enthusiast
    Join Date
    Jul 2009
    Location
    Newark, Notts, UK
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello R937,

    Sorry for being a little short with you, this is the first time that I am using a MySQL DB in a major way and as you can see I am having a few difficulties, I am hoping that the book I have purchased from sitepoint will help with my DB design and planning and I will understand and learn a better way of doing this sort of thing, but at present I have to complete this small part so that I am able to get on with the rest of the site, if you would be able to point me in the right direction that would be great.

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,217
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    sure, i'd be happy to help you dig a deeper hole

    are the tables exactly the same, or are there any column differences, like, say, and extra invoice number?

    if so, you would have to show us the table layouts for each table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Enthusiast
    Join Date
    Jul 2009
    Location
    Newark, Notts, UK
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937, to be honest mate I don't think it is possible to dig a deeper hole it is pretty hot from where I am sitting

    The Main table comprises of information of the children and parents details, the term table has only the information that is the code including the extra invoice id that is AI to produce a different # for each invoice created.

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,217
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    so without showing us the table layouts, are you comfortable writing the INSERT query?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Enthusiast
    Join Date
    Jul 2009
    Location
    Newark, Notts, UK
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    HI,

    I have no problems writing the insert query it is the getting the info from one into the other I have troubles with,

    PHP Code:

    (DB Connection)

    $id=mysql_real_escape_string($_POST['id']);
    and 
    so on....

    $sql="INSERT INTO jos_reg_term_1 (id,etc.....) VALUES ('','" $id ','" etc....)"

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,217
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    okay, that looks fine, except for some piddly details concerning which columns and the values for them
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #19
    SitePoint Enthusiast
    Join Date
    Jul 2009
    Location
    Newark, Notts, UK
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Would like to expand on that?

  20. #20
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,217
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    sure, i can expand on that

    this statement --
    Code:
    INSERT INTO jos_reg_term_1 (id,etc.....) VALUES ('','" . $id . ','" etc....)";
    is fine, except for the stuff in between both sets of parentheses

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  21. #21
    SitePoint Enthusiast
    Join Date
    Jul 2009
    Location
    Newark, Notts, UK
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    so, not sure what you mean, are you taking the mikey and all the stuff between the () is wrong or there is not enough info there.??!!

  22. #22
    SitePoint Wizard silver trophybronze trophy Stormrider's Avatar
    Join Date
    Sep 2006
    Location
    Nottingham, UK
    Posts
    3,133
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    He means, paste the whole query, don't just put 'etc...' in there

  23. #23
    SitePoint Enthusiast
    Join Date
    Jul 2009
    Location
    Newark, Notts, UK
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:

    (DB COnnection)

    $id=mysql_real_escape_string($_POST['id']);
    $familyid=mysql_real_escape_string($_POST['familyid']);
    $childfirstname=mysql_real_escape_string($_POST['childfirstname']);
    $childlastname=mysql_real_escape_string($_POST['childlastname']);
    $knownas=mysql_real_escape_string($_POST['knownas']);
    $fathername=mysql_real_escape_string($_POST['fathername']);
    $mothername=mysql_real_escape_string($_POST['mothername']);
    $sunday=mysql_real_escape_string($_POST['sunday']);
    $monday=mysql_real_escape_string($_POST['monday']);
    $tuesday=mysql_real_escape_string($_POST['tuesday']);
    $wednesday=mysql_real_escape_string($_POST['wednesday']);
    $thursday=mysql_real_escape_string($_POST['thursday']);
    $class=mysql_real_escape_string($_POST['class']);


    $sql 'INSERT INTO jos_reg_term_1 (id, familyid, childfirstname, childlastname,  knownas, fathername,  mothername, sunday, monday, tuesday, wednesday, thursday, class ) VALUES ('','" . $id . ',' . $familyid . ',' . $childfirstname . ',' . $childlastname . ',' . $knownas . ',' . fathername . ',' . $ mothername . ',' . $mothername . ',' . $sunday . ',' . $monday . ',' . $tuesday . ',' . $wednesday . ',' . $thursday . ',' . $class . "')"; 
    I think that is about it, sorry for being a little slow.

  24. #24
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,217
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    actually, i don't need to see the INSERT statement fully fleshed out, because even if it were, it might still not work correctly

    what i wanted was to see the table layouts, but failing that, he's on his own about whether he has the right number of columns in the INSERT statement...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  25. #25
    SitePoint Wizard silver trophybronze trophy Stormrider's Avatar
    Join Date
    Sep 2006
    Location
    Nottingham, UK
    Posts
    3,133
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    You have a stray double quote in there right after ) VALUES ( for a start, and none of your values are quoted when they probably should be. You also have an extra '' in there before the id in the values bit, before the value for id, and you aren't escaping single quotes when you are using them so they end the PHP string there.


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
  •