SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2012
    Posts
    69
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    inserting a lot of info?

    Alright, so i have to insert a lot (20 rows) of information into the database. I tried putting all the post variables into an array and tried a foreach statement
    PHP Code:

    $insert_query 
    'insert into info values(';

    foreach(
    $post_variables as $field){
           
    $insert_query .= '"'.$value.'",';
           };
    $insert_query .= ')'
    this did not work..could someone please tell me why it didn't work? And give me a way to do this correctly? thanks!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    sorry, can't help you with the php, as this is the database forum

    what you want the php to generate is this --
    Code:
    INSERT
      INTO tablename
         ( list,of,columns )
    VALUES
      ( list,of,values ) 
    , ( list,of,values ) 
    , ( list,of,values ) 
    , ...
    if it's really just the php you want help with, click on the little red flag at the left under your avatar and stats, and ask a moderator to move this thread to the php forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2007
    Location
    San Sebastian, Spain
    Posts
    93
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    If you have an INSERT statement you should also define each field that the data should be added for. For example, INSERT INTO info(field1, field2, field3, ..., fieldn) VALUES (v1, v2, v3, ..., vn).

    Looking at your PHP code you are referencing $value when this does not appear to be set anywhere? Should you code be doing

    Code:
    foreach($post_variables as $field => $value) {
      $insert_query .= '"' . $value . '",';
    }
    Also you need to take care with the value of $value variable. If this contains a double quote then your code may end up doing something very different. I suggest you should escape any double quotes if you are creating your SQL in this way.

  4. #4
    SitePoint Enthusiast
    Join Date
    Jul 2012
    Posts
    69
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by rcashell View Post
    Also you need to take care with the value of $value variable. If this contains a double quote then your code may end up doing something very different. I suggest you should escape any double quotes if you are creating your SQL in this way.
    What do you mean by that? How could it have double quotes?

  5. #5
    SitePoint Enthusiast
    Join Date
    Jul 2012
    Posts
    69
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Inserting a lot of info into a database??

    Alright, so i have to insert a lot (20 rows) of information into the database. I tried putting all the post variables into an array and tried a foreach statement


    PHP Code:
    $insert_query 'insert into info values('

    foreach(
    $post_variables as $field => $value){ 
           
    $insert_query .= '"'.$value.'",'
           }; 
    $insert_query .= ')';
    echo 
    $insert_query
    this did not work..could someone please tell me why it didn't work? And give me a way to do this correctly? thanks!

  6. #6
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    A lot of rows? Or a lot of columns?
    The code you posted creates a query that will insert 1 row with a number of column values.

    And what does 'did not work' mean? Do you get an error? If so, what error?

    And if you look closely at the query that is displayed by the echo statement, you should notice there is one , too many...
    Hint: take a look at implode()

  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)
    If your database table has these fields:
    Code:
    mytable
    ======
    id (auto-incrementing integer)
    first_name 
    last_name
    To add say 2 new rows from an array, say:
    PHP Code:
    $people[0]['first'] = "Joe";
    $people[0]['last'] = "Bloggs";
    $people[1]['first'] = "John";
    $people[1]['last'] = "Doe"
    You have to build a string which, as r937 says looks like this:
    PHP Code:
    insert into mytable (first_namelast_name
    values 
    ('Joe''Bloggs'), 
    (
    'John','Doe'); 
    So that is what you have to get PHP to do for you.

    You should echo the resulting string out onto the screen as you go, to check it against what you think it should be AND to then paste it into your database to make sure it really is a valid sql statement.

    Bear in mind, if you have other columns in the target table without a default value, and you do not set one, this will cause an sql error.

    If your id (in the above example table) is not of the kind "auto-increment" then it will cause an sql error.

    Any other typo or syntax error on your part could cause an sql error.

    With an auto-incrementing field, this will also work:
    PHP Code:
    insert into mytable (idfirst_namelast_name
    values 
    (0'Joe''Bloggs'), 
    (
    0'John','Doe'); 


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
  •