Inserting an array into mysql

[tokick4]

For those that have a good understanding of PHP arrays, I have a form that takes in multiple inputs that can be added to the form by js, the data is added to the codeRef[] array and I have tried to do several different things to get the data into mysql. If it runs I dont get any error nor does it enter into the database. Thanks for any help anyone can give.

Jon

<form action="plan_review_comment_form.php?id_plan_review="<?php $_GET['id_plan_review'];?> method="post" id="frmMain">
<p>
<ol id="comment">
    <li>
        <label name="idPlanRev"> Plan Review No:</label>
        <input name"codeRef[]"id="planReviewId"  value="<?php  echo $id_plan_review;?>"/>
        <label name="emply"> Employee:</label>
        <input name"codeRef[]"id="emply" value="<?php echo $_SESSION['contact_id'];?>"/><!---->
        <label name="codeRef"> Code Reference:</label>
        <input name="codeRef[]" id="codeRef" type="text"  value=""/> 
        <label name="comment"> Comment:</label>
        <input name="codeRef[]" id="comments" type="text"  size="60" value=""/>
        <label name="date">Date:</label>         
        <input name"codeRef[]"id="date" type="date" value="<?php echo date('Y-m-d');?>"/><!---->
        <input type="button" id="add" value="Add Line"/>
    </li>        
</ol>
<input type="submit" id="save" value="Save "/>
</p>
</form>
<?php


?>

</div>
<!-- end content div -->
</div>
<!-- end wrapper div -->
<script type="text/javascript" src="/js/jquery.js"></script>

<!-- event handler function to add more fields boxes -->
<script type="text/javascript">
    $(document).ready(function() {
        $('#add').click(function()
        {
            var str ='<li>';
            str += '<label> Plan Review No</label><input name="codeRef[]" id="planReviewId" type="text" value="<?php  echo $id_plan_review;?>" />';
            str += '<label> Employee</label><input name="codeRef[]" id="emply" type="text" value="<?php echo $_SESSION['contact_id'];?>" />';
            str += '<label> Code Reference</label><input name="codeRef[]" type="text" value="" />';
            str += '<label> Comment</label><input name="codeRef[]" type="text" value="" size=60/>';
            str += '<label> Date</label><input name="codeRef[]" id="date" type="date" value="<?php echo date('Y-m-d');?>" />';
            str +='<input type="button" value="Remove" class="remove"/>';
            str += '</li>';
            $('#comment').append(str);
        });        
        $('.remove').live('click',function()
        {
            $(this).parent('li').remove();
        });
    });


</script>
<script type="text/javascript">
/*  var mutli_codeRef=document.frmMain.input["codeRef[]"];
    for(i=0;i<mutli_codeRef.length;i++)
    {
        alert(mutli_codeRef[i].value);
        }*/
</script>
<!-- event handler function for the forms submit event ------->
<script type="text/javascript">
    $(document).ready(function() 
    {
        $('#frmMain').submit(function()
        {
        var flag = true;
        $('#error').empty();
        $('.required').each(function() 
        {
         if(jQuery.trim($(this).val()) == '')
           {
               flag = false;
           }
        });
        if(!flag)
        {
            $('#error').html('Please fill all the fields');
            return false;
        }
        else
        {
            return true;        
        }
        });
    });
</script>
<?php 
//if(isset($_POST['submit'])){
$myArr ="";
$arr=$_POST['codeRef'];
$cnt=count($arr)-1;
for($i=0;$i<$cnt;$i++){
    $myArr.="'$arr[$i]',";  
}
$myArr.="'$arr[$cnt]'";
print_r($myArr);
'<br>';
//if($_POST)
{
    if(is_array($myArr)){
        $sql="INSERT INTO plan_review_comment (
        id_plan_review,
        employee_id,
        code_ref,
        comment,
        date
        )values ";
        $valuesArr = array();
        foreach($myArr as $row){
        $planReviewId = (int) $row['id_plan_review'];   
        $employeeId = $mysql_real_eascape_string($_SESSION['contact_id']);
        $code_ref = $mysqli_real_escape_string($row['code_ref']);
        $comment = $mysql_real_escape_string($_row['comment']);
        $date_submit = date('Y-m-d');
        $valuesArr[] = "('$planReviewId','$employId','$code_ref','$comment','$date_submit')";
        }
        $sql.= implode(',',$valuesArr);
        $mysql_query($sql) or exit(mysql_error());
    }
    ?><pre> 

}
    <?php
    //echo  print_r($_POST['codeRef']);
    }
//foreach($_POST['codeRef'] && $_POST['codeRef2'] as $key)print_r($_POST['codeRef2']);
//echo ($key .'<br>');
?><pre>

[urtrivedi]

I do not agree with your html layout or I am not able to understand what you trying to achieve.

You are using same name coderef[] for different kind of of datatype, Usually html php array is used for same kind (datatype) of column for handling multiple values.

So I would suggest you to first use unique names of columns, changed handler accordingly

<li>
        <label name="idPlanRev"> Plan Review No:</label>
        <input name"planReviewId"id="planReviewId"  value="<?php  echo $id_plan_review;?>"/>
        <label name="emply"> Employee:</label>
        <input name"emply"id="emply" value="<?php echo $_SESSION['contact_id'];?>"/><!---->
        <label name="codeRef"> Code Reference:</label>
        <input name="codeRef" id="codeRef" type="text"  value=""/> 
        <label name="comment"> Comment:</label>
        <input name="comments" id="comments" type="text"  size="60" value=""/>
        <label name="date">Date:</label>         
        <input name"date"id="date" type="date" value="<?php echo date('Y-m-d');?>"/><!---->
        <input type="button" id="add" value="Add Line"/>
    </li>        

[Alberto Bucur]

Hi,
Your sql is wrong

INSERT INTO plan_review_comment (
    id_plan_review,
    employee_id,
    code_ref,
    comment,
    date
        ) 
   values ( list of value separated by comma )";

The strings must be puted between ' too.
What urtrived said is correct too, would be good to keep in mind.

[tokick4]

What I am trying to accomplish is that the form dynamicly adds a new line to the form and the data added to it is placed into an array and then the array is saved to the database. The data gathered is to go through each row and insert each one into the database. I hope that make since. If there is an easyer way let me know. I am always learning.
Jon

[diafol]

You can try a variation on this snippet that I knocked up:

<?php
    if($_POST)
    {
        function reDim($globalArray, $firstField)
        {
            $output = array();
            $altered = array();
            foreach($globalArray[$firstField] as $num=>$p)
            {
                foreach($globalArray as $key=>$array)
                {
                    $altered[$num][$key] = $globalArray[$key][$num];    
                }
                if(trim(implode($altered[$num])) != '') $output[] = $altered[$num];
            }
            return $output;
        }

        function createValuesSQL($data, $keyArray)
        {
            $output = array();
            foreach($data as $record)
            {
                $rec = array_map("mysql_real_escape_string",$record);
                $values = array();
                foreach($keyArray as $key)
                {
                    $values[] = $rec[$key];
                }
                $output[] = "('" . implode("','", $values) ."')";
            }
            return implode(',',$output);
        }

        $fields = array('t1','t2','t3');

        $post = reDim($_POST, $fields[0]);
        $values = createValuesSQL($post, $fields);      

        $sql = "INSERT INTO `table` (`" . implode('`,`',$fields) . "`) VALUES " . $values;

        echo $sql;
    }
?>
<!DOCTYPE HTML>
<html>
<head>
<meta charset="utf-8">
<title>Untitled Document</title>
</head>
<body>
<form method="POST">
    <input name="t1[]" />
    <input name="t2[]" />
    <textarea name="t3[]"></textarea>
<br />
    <input name="t1[]" />
    <input name="t2[]" />
    <textarea name="t3[]"></textarea>
    <input type="submit" value="Send" />
</form>
</body>
</html>

[tokick4]

diafol, thanks for the snip I will try it and work through it.

[tokick4]

Ok I have been away from this for a while and was able to get it to work with a single line from the from to enter the data into mysql database, it does not work when I use javascript to add dynamic fields to the form it will add the field each time the add violation button is clicked, I get the following error when submitting the form to the database.
10 is the count of entrys and
the following are the entered into the form
1,11,1,r302,2014-05-12,1,11,R400,R400,2014-05-12
Then I get this error.
Column count doesn't match value count at row 1

        <div id="content"> <h1>Plan Review Comment Form</h1> 
        <form method="post" id="frmMain">
            <ol id="comment">
            <input type="button" id="add" value="Add Violation"/>
            </ol>
            <input type="submit" name="submit" id="save" value="Save "/>
        </form>
        </div><!-- end content div -->
        </div><!-- end wrapper div -->
        <!-- event handler function to add more fields boxes -->
        <script type="text/javascript">
            $(document).ready(function() {
                $('#add').click(function()
                {
                    var str ='<li>';
                    str += '<label> Plan Review No</label><input name="codeRef[]" id="planReviewId" type="text" value="<?php  echo $id_plan_review;?>" />';
                    str += '<label> Employee</label><input name="codeRef[]" id="emply" type="text" value="<?php echo $_SESSION['contact_id'];?>" />';
                    str += '<label> Code Reference</label><input name="codeRef[]" type="text" value="" />';
                    str += '<label> Comment</label><input name="codeRef[]" type="text" value="" size=60/>';
                    str += '<label> Date</label><input name="codeRef[]" id="date" type="date" value="<?php echo date('Y-m-d');?>" />';
                    str +='<input type="button" value="Remove" class="remove"/>';
                    str += '</li>';
                    $('#comment').append(str);
                });        
                $('.remove').live('click',function()
                {
                    $(this).parent('li').remove();
                });
            });
        </script>
        <?php 
        if(isset($_POST['submit']))
        {
        $vio =$_POST['codeRef'];
        $count = count($vio);
        echo $count;
        echo implode (",", $vio)."<br>";
        mysql_query("INSERT INTO `plan_review_comments`(`id_plan_review`, `employee_id`, `code_ref`,`comment`,`date`)
                    VALUES('".implode("','",$vio)."')") or die(mysql_error());
        }

Any help will be appreicated
Jon

[diafol]

Coulumn count refers to too many or too little few values in your sql query. This may be because you aren't creating the correct query:

1,11,1,r302,2014-05-12,1,11,R400,R400,2014-05-12

Realtes to 2 rows to be inserted, but it seems you have them in all in one. It should look like this...

... VALUES (1,11,'1','r302','2014-05-12'),(1,11,'R400','R400','2014-05-12')

However, you have 4 fields:

`id_plan_review`, `employee_id`, `code_ref`,`comment`,`date`

But you supply 5 values for each row.

//EDIT = forgive my counting it's 5 and 5 - my bad :(

[tokick4]

The first column is an auto increment. My question is how do I get the array to split after the date on each line and enter into the database as a new entry.

[diafol]

The first column is an auto increment.

In which case, use a value of NULL if you place the autoincrement value in the field list. Easiest if you don't include the autoinc field in the list - so you can ignore it.

From your code, it seems that you're calling each form field 'coderef' - that's awkward since you'll get a long list of items in a single array for multiple records as opposed to a structured array that you can loop over.

However, you can use something like this...

$post = $_POST['coderef']; //this should also be sanitized e.g. with array map/mysql_real_escape_string if you use deprecated mysql functions!
$loopReady = array_chunk($post, 4);
$bits = array();
foreach($loopReady as $r)
{
    $bits[] = "('" . implode("',",$r); . "')";
}
$values = implode(',',$bits);

$sql = "SELECT ... VALUES $values";

Untested

[tokick4]

diafol, Thanks for the help with some tweeking it worked.