SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    Join Date
    Mar 2004
    Location
    DK
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    mutliple sql inserts vs arrays

    Probabyl its a fairly easy question, Im new to PHP/Mysql and have problem with the following:
    How can I get all this into a single line of code, instead of using $date_from1, $date_from2 etc. Ive tried with some arrays but the SQL gets messed up and I cant get out of it. I know that I need to get my values defined by an array but the think I get problems with the SQL syntax??

    Thanks,


    /* Insert query into the Database*/
    $sql = mysql_query("INSERT INTO activities (ref_num, act_type_id, date_from, date_to, activity_place, activity_contact, insert_date)
    VALUES('$r_num','$act_type_id','$date_from1','$date_to1', '$activity_place', '$activity_contact', now())") or die (mysql_error());

    $sql = mysql_query("INSERT INTO activities (ref_num, act_type_id, date_from, date_to, activity_place, activity_contact, insert_date)
    VALUES('$r_num','$act_type_id','$date_from2','$date_to2', '$activity_place', '$activity_contact', now())") or die (mysql_error());

    $sql = mysql_query("INSERT INTO activities (ref_num, act_type_id, date_from, date_to, activity_place, activity_contact, insert_date)
    VALUES('$r_num','$act_type_id','$date_from3','$date_to3', '$activity_place', '$activity_contact', now())") or die (mysql_error());

  2. #2
    SitePoint Member
    Join Date
    Mar 2004
    Location
    Belgium
    Posts
    23
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I assume you want to enter three rows into your table, so I would stick with running the statement three times. I can imagine you can get a workaround to do it with one line of code but debugging it later would be much harder.

  3. #3
    SitePoint Member
    Join Date
    Mar 2004
    Location
    DK
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    in fact I want to do it 15 times depending on what comes from the passing page - so this is possible yes and works, but I dont like the method - and if you had - say 100 possible inserts....it would be messy

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    one of the nice things about mysql is that it allows this syntax --

    insert into mytable ( foo, bar ) values
    ( 1, 'curly' )
    ,( 2, 'larry' )
    ,( 3, 'moe' )

    one statement, multiple rows inserted
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Mar 2004
    Location
    DK
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok this is working if you know the number of rows what if you dont know the incomiing number of rows, how can you use that syntax then?
    thanks,

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    you would construct the query statement "on the fly"

    (not sure i know what you mean by "incoming")

    just keep appending , (values)

    when you have no more incoming stuff, then submit the query string to the database
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •