Inserting multiple records into one table

[monaya]

Hi I'm using this code to insert multiple records. The code executes but nothing is entered into my database. This usually happens when there's a mismatch in data types.

 

How do I ensure that description goes in as text which in sql is wrapped in single quotes, but also make sure the other variables go in as numeric.

 // an array items to insert
    $array = array( 'theid'   => $theid,
            'descr'      =>   $descr,
            'costperunit'    => $costperunit,
            'quantity'       => $quantity,
'costperlot'       => $costperlot
            );


    // begin the sql statement
    $sql1 = "INSERT INTO  descriptions (jobid, description, costperunit, quantity, costperlot) VALUES ";

    $it = new ArrayIterator( $array );


    // a new caching iterator gives us access to hasNext()
    $cit = new CachingIterator( $it );


    // loop over the array
    foreach ( $cit as $value )
    {
        // add to query
        $sql1 .= "('".$cit->key()."','" .$cit->current()."')";
       

        if( $cit->hasNext() )
        {
            $sql1 .= ",";
        }
    }

Edited December 12, 2014 by monaya

[jcbones]

You are trying to add two values to the database, but you have 5 columns listed.  Yes, the database is going to puke on that one.  It doesn't know what columns you want to add to.  You will either have to shorten the columns, or add to the values.

You should be using prepared statements to interact with the database.  Couple that with error logging, you will see the problem pretty quick.

Edit: prepared statements will cure the data type problem as well.

Edited December 12, 2014 by jcbones

[hansford]

Before you execute the query, echo the query string to see what it actually looks like in string form.

We don't see any code showing the actual insert execution, so we can't see if there's potentially a problem there.