SitePoint Sponsor

User Tag List

Page 2 of 3 FirstFirst 123 LastLast
Results 26 to 50 of 52
  1. #26
    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)
    Your query, formatted a bit better:

    Code:
    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 . "'
     )";
    You are mixing quote styles (what are the double quotes for?), not including single quotes around your text values where you need to, and have that extra empty string in there before id. There is no $ before fathername, mothername is in there twice, once with an extra space which will cause it to error.

    A simple check of the column names against the values you are inserting will have picked these up, php should have reported some of those errors anyway, and echoing out your generated SQL string ($sql) should reveal the problems with the query itself.

  2. #27
    SitePoint Enthusiast
    Join Date
    Jul 2009
    Location
    Newark, Notts, UK
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    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 . ',
      
    ' . $sunday . ',
      
    ' . $monday . ',
      
    ' . $tuesday . ',
      
    ' . $wednesday . ',
      
    ' . $thursday . ',
      
    ' . $class . '
     
    )"; 
    I have not got this code place in my code yet as I would like to find out how I can get one table info into another, I typed this out as I thought it was asked of me..

  3. #28
    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)
    Yeh, I think you were asked for your code so far so people can help you with specific problems you are having with it. Have you tried it? You still have that extra empty string before the id, you aren't quoting text values, and a stray double quote at the end (although partly my fault - I didn't put the assignment of the php variable in my quote of your code, but it uses single quotes)

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

    Thanks for that, the issue I am having is how I am able to get the data from the main table and place into the term_1 table? I will test the code that I have above but how would I get the data from the main jos_reg table?..

    So just to confirm that I should place single quotes around;

    INSERT INTO jos_reg_term_1 ('id', 'familyid', etc..)

    Thanks

  5. #30
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by newarkweb View Post
    I would like to find out how I can get one table info into another
    instead of INSERT...VALUES, you need INSERT...SELECT
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #31
    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)
    Quote Originally Posted by newarkweb View Post
    Hi Stormrider,

    Thanks for that, the issue I am having is how I am able to get the data from the main table and place into the term_1 table? I will test the code that I have above but how would I get the data from the main jos_reg table?..

    So just to confirm that I should place single quotes around;

    INSERT INTO jos_reg_term_1 ('id', 'familyid', etc..)

    Thanks
    No, you place quotes around the VALUES, not the field names. Field names should/can be enclosed with backticks (``), but Rudy is the expert here, I don't know for sure...

  7. #32
    SitePoint Enthusiast
    Join Date
    Jul 2009
    Location
    Newark, Notts, UK
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So would it have to be

    PHP Code:
    SELECT FROM jos_reg (,' . $id . ',   ' . $familyid . 'etc
    Thanks

  8. #33
    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)
    Quote Originally Posted by newarkweb View Post
    So would it have to be

    PHP Code:
    SELECT FROM jos_reg (,' . $id . ',   ' . $familyid . 'etc
    Thanks
    No, you use field names in a select, unless your field names are stored in those variables (and you have have an extraneous comma in there as well).

    Can you do "INSERT INTO table2 (SELECT * FROM table1);" if the fields are identical in each? Or is that not standard?

  9. #34
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Stormrider View Post
    Can you do "INSERT INTO table2 (SELECT * FROM table1);" if the fields are identical in each? Or is that not standard?
    yes, you can, if the columns (please, not "fields") are identical, and provided you remove the parentheses around the SELECT statement

    however, the tables are not identical, one of them has an extra invoice number

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

  10. #35
    SitePoint Enthusiast
    Join Date
    Jul 2009
    Location
    Newark, Notts, UK
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Stormrider View Post
    No, you use field names in a select, unless your field names are stored in those variables (and you have have an extraneous comma in there as well).

    Can you do "INSERT INTO table2 (SELECT * FROM table1);" if the fields are identical in each? Or is that not standard?
    I could but I was under the assumption that you would have to have the same amount of fields in the second table as you had in the main which is not the case. I have only got a few fields from the main one in the second one. Is it still possible as that would save me a little time writing it all out..

    Thanks

  11. #36
    SitePoint Enthusiast
    Join Date
    Jul 2009
    Location
    Newark, Notts, UK
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    INSERT INTO jos_reg_term_1 (   id,   familyid,   childfirstname,   childlastname,   knownas,   fathername,   mothername,   sunday,   monday,   tuesday,   wednesday,   thursday,   class, ) SELECT idfamilychildfirstnamechildlastnameknowasfathernamemothernamesundaymondaytuesdaywednesdaythursday, class) FROM jos_reg
    Please tell me I am getting close?

  12. #37
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by newarkweb View Post
    I have only got a few fields from the main one in the second one. Is it still possible
    no

    you have to write them out, at least in the SELECT portion, and it is strongly advised to write them out in the INSERT portion as well
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #38
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by newarkweb View Post
    Please tell me I am getting close?
    where's the extra invoice number?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #39
    SitePoint Enthusiast
    Join Date
    Jul 2009
    Location
    Newark, Notts, UK
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The invoice number is auto incremental in MySQL, Should I place that at the beginning of the INSERT?

  15. #40
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by newarkweb View Post
    The invoice number is auto incremental in MySQL, Should I place that at the beginning of the INSERT?
    no, leave it out altogether, in both the list of columns after the INSERT, and the list of columns in the SELECT

    i wish i could help you further, but you still haven't shared the table layouts with us

    are there any columns that you are worried about revealing to us?

    if not, could you please do a SHOW CREATE TABLE for each table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #41
    SitePoint Enthusiast
    Join Date
    Jul 2009
    Location
    Newark, Notts, UK
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937, I will do that when I get home, if that is OK, nothing that I am worried to show you (if only, I would feel a little special ).

    The Invoice ID is only in the second table not the first so I would be able to select it, I will get the tables pasted into here. What is the best way of doing it?

  17. #42
    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)
    Just do "SHOW CREATE TABLE <tablename>" for each of your tables and paste the results here

  18. #43
    SitePoint Enthusiast
    Join Date
    Jul 2009
    Location
    Newark, Notts, UK
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is the jos_reg_term_1 table

    invoice int(255) No None auto_increment
    id int(255) No None
    familyid int(255) No None
    childfirstname varchar(255) utf8_general_ci No None
    childlastname varchar(255) utf8_general_ci No None
    knownas varchar(255) utf8_general_ci No None
    fathername varchar(255) utf8_general_ci No None
    mothername varchar(255) utf8_general_ci No None
    sunday varchar(255) utf8_general_ci No None
    monday varchar(255) utf8_general_ci No None
    tuesday varchar(255) utf8_general_ci No None
    wednesday varchar(255) utf8_general_ci No None
    thursday varchar(255) utf8_general_ci No None
    friday varchar(255) utf8_general_ci No None
    class varchar(255) utf8_general_ci No None

    SHould I place the other one in the same way??

  19. #44
    SitePoint Enthusiast
    Join Date
    Jul 2009
    Location
    Newark, Notts, UK
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is that at the SQL command line? if so I do not have access to the SQL server, I have to request it and wait 5 days or something like that..

  20. #45
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by newarkweb View Post
    This is the jos_reg_term_1 table
    how did you produce this?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  21. #46
    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 can run the query with PHP and get the output that way

  22. #47
    SitePoint Enthusiast
    Join Date
    Jul 2009
    Location
    Newark, Notts, UK
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    how did you produce this?
    I copied it from PHPMyAdmin

  23. #48
    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)
    Well, if you have access to PHPMyAdmin, you can run the SHOW CREATE TABLE queries there too

  24. #49
    SitePoint Enthusiast
    Join Date
    Jul 2009
    Location
    Newark, Notts, UK
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Not sure how to do it in PHPMyAdmin, but this is jos_reg;

    id int(11) No None auto_increment
    familyid int(11) No None
    childfirstname varchar(255) utf8_general_ci No None
    childlastname varchar(255) utf8_general_ci No None
    knownas varchar(255) utf8_general_ci No None
    dob date No None
    male varchar(6) utf8_general_ci No None
    female varchar(6) utf8_general_ci No None
    nationality varchar(255) utf8_general_ci No None
    firstlang varchar(255) utf8_general_ci No None
    pobox int(10) No None
    hometel int(10) No None
    childrp int(15) No None
    fathername varchar(255) utf8_general_ci No None
    fatheremployer varchar(255) utf8_general_ci No None
    fathermobile int(10) No None
    fatheroffice int(10) No None
    fatheremail varchar(255) utf8_general_ci No None
    fatherrp int(15) No None
    mothername varchar(255) utf8_general_ci No None
    motheremployer varchar(255) utf8_general_ci No None
    mothermobile int(10) No None
    motheroffice int(10) No None
    motheremail varchar(255) utf8_general_ci No None
    allergies varchar(3) utf8_general_ci No None
    allergiesnotes varchar(250) utf8_general_ci No None
    sunday varchar(10) utf8_general_ci No None
    monday varchar(10) utf8_general_ci No None
    tuesday varchar(10) utf8_general_ci No None
    wednesday varchar(10) utf8_general_ci No None
    thursday varchar(10) utf8_general_ci No None
    month varchar(255) utf8_general_ci No None
    class varchar(20) utf8_general_ci No None
    reg_date timestamp on update CURRENT_TIMESTAMP No CURRENT_TIMESTAMP
    startdate date No None
    enddate date No None
    waiting int(11) No None
    finish int(11) No None

  25. #50
    SitePoint Enthusiast
    Join Date
    Jul 2009
    Location
    Newark, Notts, UK
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry yes I do know how to do it in PHPMyAdmin, my mind went blank for a little while infact for a long while, would you like me to run the query or is the copy and paste OK?.


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
  •