Results 1 to 14 of 14
  1. #1
    Join Date
    Jul 2011
    Posts
    13

    Unanswered: php, multi-insert problem

    Hi everyone,

    I am working with wamp 2.1 , including php 5.3 and mysql 5.1.53. I have a php script with which I am trying to dynamically build a mysql query to insert into a table.I have tried to test the query with phpmyadmin. Unfortunately I am getting an error that I can't figure out.

    #1136 - Column count doesn't match value count at row 1
    INSERT INTO milp( `<!--#echo var="auth_type" -->` , `<!--#echo var="content_length" -->` , `<!--#echo var="content_type" -->` , `<!--#echo var="date_local" -->` )
    VALUES (
    '(none)', '(none)'
    ), (
    '(none)', '(none)'
    ), (
    '(none)', '(none)'
    ), (
    'Tuesday, 19-Jul-2011 17:15:46 CDT', 'Tuesday, 19-Jul-2011 17:15:52 CDT'
    )

    the sql statement is

    Code:
    INSERT INTO milp (`<!--#echo var="auth_type" -->`, `<!--#echo var="content_length" -->`, `<!--#echo var="content_type" -->`, `<!--#echo var="date_local" -->`) VALUES ('(none)','(none)') , ('(none)','(none)') , ('(none)','(none)') , ('Tuesday, 19-Jul-2011 17:15:46 CDT','Tuesday, 19-Jul-2011 17:15:52 CDT')
    the show create table yields

    CREATE TABLE `milp` (
    `&lt;!--#echo var="auth_type" --&gt;` char(40) DEFAULT NULL,
    `&lt;!--#echo var="content_length" --&gt;` char(40) DEFAULT NULL,
    `&lt;!--#echo var="content_type" --&gt;` char(40) DEFAULT NULL,
    `&lt;!--#echo var="date_local" --&gt;` char(40) DEFAULT NULL,
    ....................

    Can anyone spot the problem?

    Thanks in advance,

    KC

  2. #2
    Join Date
    May 2011
    Posts
    24
    you specify 4 columns to insert to, but only provide 2 columns of data.

    and whats with the field name?

  3. #3
    Join Date
    Jul 2011
    Posts
    13
    I may be misunderstanding the entire insert process. Here is what I want my table to look like:

    "auth_type" "content_length" "content_type" "date_local"

    (none) (none) (none) Tuesday, 19-Jul-2011 17:15:46
    (none) (none) (none) Tuesday, 19-Jul-2011 17:15:52

    Sorry, I'm a noob,

    KC

  4. #4
    Join Date
    Jul 2011
    Posts
    10
    Im not sure what variable your field names are referencing but take a look at the below and try to mimic it.

    -----------------------------
    INSERT INTO `tablename`
    (
    field1,
    field2,
    field3,
    field4
    )
    VALUES
    (
    data1,
    data2,
    data3,
    data4
    );
    -----------------------------


    The only other issue I see is your lacking a parathesis in your value statement.

    Yours shows:
    -----------------------------
    VALUES (
    '(none)','(none)') ,
    ('(none)','(none)') ,
    ('(none)','(none)') ,
    ('Tuesday, 19-Jul-2011 17:15:46 CDT','Tuesday, 19-Jul-2011 17:15:52 CDT')
    -----------------------------

    It should be:
    -----------------------------
    VALUES (
    ('(none)','(none)') ,
    ('(none)','(none)') ,
    ('(none)','(none)') ,
    ('Tuesday, 19-Jul-2011 17:15:46 CDT','Tuesday, 19-Jul-2011 17:15:52 CDT')
    );
    -----------------------------




    good luck,

    Ryan
    Last edited by rpot; 07-20-11 at 12:53.

  5. #5
    Join Date
    Jul 2011
    Posts
    13
    Thanks Ryan,

    I should explain further that my goal is to do a single insert statement to insert multiple rows. (according to an article I found this should be doable) . Please see Multiple Insert in Single Query PHP/MySQL Blog Archive Amoeba Solution Kiosk.

    So , in line with this, I am trying to build the query to be like :

    Code:
    INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);
    Thanks for looking at it,

    KC

  6. #6
    Join Date
    Jul 2011
    Posts
    10
    That's fine, but if you open a paranthesis you must close it..

    VALUES(
    (VALUE1),(VALUE2),
    (VALUE3),(VALUE4)
    );

    So the statement should look like this:

    INSERT INTO table
    (field1, field2, field3, field4)
    VALUES
    (data1, 'data11', 'data12', 'data13'),
    (data2, 'data21', 'data22', 'data23'),
    (data3, 'data31', 'data32', 'data33'),
    (data4, 'data41', 'data42', 'data43');

  7. #7
    Join Date
    Jul 2011
    Posts
    13
    Hi Ryan,

    Sorry to be dense about this, but I've looked at it several times and I don't see the parentheses error. I am using

    Code:
    INSERT INTO milp (`<!--#echo var="auth_type" -->`, `<!--#echo var="content_length" -->`, `<!--#echo var="content_type" -->`, `<!--#echo var="date_local" -->`) VALUES('(none)','(none)') , ('(none)','(none)') , ('(none)','(none)') , ('Tuesday, 19-Jul-2011 17:15:46 CDT','Tuesday, 19-Jul-2011 17:15:52 CDT')
    which generates:

    #1136 - Column count doesn't match value count at row 1

    INSERT INTO milp( `<!--#echo var="auth_type" -->` , `<!--#echo var="content_length" -->` , `<!--#echo var="content_type" -->` , `<!--#echo var="date_local" -->` )
    VALUES (
    '(none)', '(none)'
    ), (
    '(none)', '(none)'
    ), (
    '(none)', '(none)'
    ), (
    'Tuesday, 19-Jul-2011 17:15:46 CDT', 'Tuesday, 19-Jul-2011 17:15:52 CDT'
    )

    I've also tried the values with an extra set of parentheses and with and without a semicolon at the end, within phpmyadmin.

    I'm clearly doing something wrong,

    Thanks again,

    KC

  8. #8
    Join Date
    Jul 2011
    Posts
    10
    Ok lets try this from PHPMYADMIN:


    INSERT INTO milp
    (
    auth_type,
    content_length,
    content_type,
    date_local
    )
    VALUES
    ('(none)','(none)','(none)','Tuesday, 19-Jul-2011 17:15:46'),
    ('(none)','(none)','(none)','Tuesday, 19-Jul-2011 17:15:52');


    If this works without an issue then what's occuring is your echo statements contain no values since its likely being processed through PHP. PHPMYADMIN needs a sql corrected statement and has no way of defining your variables. Hence the reason I put the field name directly into the statment.

    let me know,

    Ryan

  9. #9
    Join Date
    Jul 2011
    Posts
    13
    Thanks Ryan,

    I decided to simplify it even more, but I'm still having problems.

    Code:
    INSERT INTO milp( `<!--#echo var="auth_type" -->` , `<!--#echo var="content_length" -->` , `<!--#echo var="content_type" -->` , `<!--#echovar="date_local" -->` ) VALUES ('none', 'none', 'none', 'Tuesday, 19-Jul-2011 17:15:46')
    I am now getting the following error when I try the query in phpmyadmin:

    #1054 - Unknown column '&lt;!--#echo var=&quot;auth_type&quot; --&gt;' in 'field list'

    INSERT INTO milp( `<!--#echo var="auth_type" -->` , `<!--#echo var="content_length" -->` , `<!--#echo var="content_type" -->` , `<!--#echovar="date_local" -->` )
    VALUES (
    'none', 'none', 'none', 'Tuesday, 19-Jul-2011 17:15:46'
    )

    Any ideas?

    Thanks,

    KC

  10. #10
    Join Date
    Jul 2011
    Posts
    10
    Ok........ We will get this. First lets forget about PHP altogether and lets get the query operating properly. Once we get the query we can easily setup the PHP. So with that said lets drop all notions of trying to establish a variable in your query. What I want you to try is to simply cut and past the below query into your PHPMYADMIN MySQL Query Window and execute it. If we get an error we can work from there.

    --------------------------------
    INSERT INTO milp
    (
    auth_type,
    content_length,
    content_type,
    date_local
    )
    VALUES
    ('(none)','(none)','(none)','Tuesday, 19-Jul-2011 17:15:46'),
    ('(none)','(none)','(none)','Tuesday, 19-Jul-2011 17:15:52');

    --------------------------------

    If and when this works, as it should because I setup a test table and executed this perfectly fine, we can work with your variables.

    Your error is because you are attempting to create a field with this statement: `<!--#echo var="auth_type" -->`

    So essentially what your attempting to tell MySQL if it could interpret the statement is: echo the variable "Auth_Type". which technically wont work because we havent established the variable Auth_Type, nor can we from a pure SQL statment.

    Let me know how this works, within PHPMYADMIN, and then lets continue.

    Ryan

  11. #11
    Join Date
    Jul 2011
    Posts
    13
    Hi Ryan,

    Thanks for persisting on this. I'm sorry to take so long to get back to you, but because my fields differ from yours, I've rewritten some php, so that when a new table is generated, the fields will appear as you have indicated.

    It should be working now and I will try your query and get back to you shortly.

    Regards,

    KC

  12. #12
    Join Date
    Jul 2011
    Posts
    13
    Ok Ryan,

    So I have created a test table 'milp' as follows

    CREATE TABLE `milp` (
    `auth_type` char(40) DEFAULT NULL,
    `content_length` char(40) DEFAULT NULL,
    `content_type` char(40) DEFAULT NULL,
    `date_local` char(40) DEFAULT NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1

    and using phpmyadmin I have executed your sql ststement;

    Code:
    INSERT INTO milp
    (
    auth_type,
    content_length,
    content_type,
    date_local
    )
    VALUES
    ('(none)','(none)','(none)','Tuesday, 19-Jul-2011 17:15:46'),
    ('(none)','(none)','(none)','Tuesday, 19-Jul-2011 17:15:52');
    It does work, inserting the rows correctly.

  13. #13
    Join Date
    Jul 2011
    Posts
    10
    Ok great. So now let me make sure we clear any misunderstanding. You are wanting a script via php to insert variables which you need to establish, correct?

    Can you provide me with what exactly your trying to do and where these variables are coming from (IE a Form using GET, POST, SESSION) we can get the php working.

    Or if your good to go, then good luck, glad its all worked out..

    Ryan

  14. #14
    Join Date
    Jul 2011
    Posts
    13
    Hi Ryan,

    I could probably use some help here. Here is the function I am using to build the prior SQL query string. I'd appreciate your thoughts on how to proceed:

    Just a word on structure:

    I am saving my data in an associative array with multiple values 'glued' to gether with the ~ charachter

    The array looks like:

    [A]=>B~C~D~E
    [F]=>G~H~I~J
    [K]=>L~M~N~O








    PHP Code:

            <?php
        
    function build_sql_insert($table$data)
        {
           
           function 
    addQuotes($value) { 

               
    //sprintf('%s', $value );
               //echo $value;
                
    $output sprintf("'%s'"$value );
                echo 
    $output;
                return 
    $output;
            }

            function 
    addbrackets($value) { 

               
    //sprintf('%s', $value );
               //echo $value;
                
    $output sprintf('(%s)'$value );
                echo 
    $output;
                return 
    $output;
            }
            
            

          

               foreach(
    array_values($data) as $val) {
                
    $pieces=explode("~"$val);
                
    $data1array_map("addQuotes"$pieces);
               

              
    $values[] = implode(','$data1); // VALUES CONTAINS THE REBUILT QUOTED ARRAY VALUES
                
    print ' VAL ' ;
                
    var_dump($val);


               }
                           print 
    ' VALUES ' ;

                
    $data2array_map("addbrackets"$values);


               
    var_dump($data2);

             
    // $values = implode(',', $data1); // VALUES CONTAINS THE REBUILT QUOTED ARRAY VALUES


              // exit;

               
                  
    echo " ARRAYvalS "count((array_keys($data))).'<br>';
               echo 
    " ARRAYkeys "count((array_values($data))).'<br>';
               echo 
    " values "count($values).'<br>';

               
               echo 
    " data1`ARRAYvalS ".'<br>';   
               
    //var_dump($data1);
         
             
    print ' HELLO '.sprintf("INSERT INTO %s (`%s`) VALUES (%s) "$tableimplode('`, `'array_keys($data)), implode(") , (",  $values) );
            
    // $sql=sprintf("INSERT INTO %s (`%s`) VALUES (%s) ", $table, implode('`, `', array_keys($data)), implode(") , (",  $values) );
             
    $sql=sprintf("INSERT INTO `%s` (`%s`) VALUES %s"$tableimplode('`, `'array_keys($data)), implode(" , ",  $data2) );
                          
    //  print ' HELLO '.sprintf("INSERT INTO %s (`%s`) VALUES ('%s')", $table, implode('`, `', array_keys($data)), implode("') , ('",  array_values($data)) );
            
             
             
    echo $sql;

          
             
            return(
    $sql);
        }

        
        
    ?>

        }

        
        ?>
    Thanks,

    KC

Posting Permissions

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