Solved

PHP MSSQL > Updating DB entries

Posted on 2014-02-16
32
996 Views
Last Modified: 2014-02-17
Morning Experts,
I have the following script attempting to update fields in my MSSQL table.

<?php

$Shift_Start 	= $_POST['Shift_Start'];
$Shift_End 	= $_POST['Shift_End'];
$Lunch_Start 	= $_POST['Lunch_Start'];
$Lunch_End	= $_POST['Lunch_End']; 
$Agent_ID	= $_POST['Agent_ID'];
$Update		= array($Shift_Start,$Shift_End,&$Agent_ID);



$serverName = "---";
$connectionInfo = array( "Database"=>"---", "UID"=>"---", "PWD"=>"---");
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn === false) {
    die( print_r( sqlsrv_errors(), true));
}

$sql = "UPDATE dbo.Roster
        SET 		Shift_Start = ?,
			Shift_End = ?,
        WHERE    Agent_ID = ?";

$stmt = sqlsrv_prepare( $conn, $sql, $Update);
if( !$stmt ) {
    die( print_r( sqlsrv_errors(), true));
}

    if( sqlsrv_execute( $stmt ) === false ) {
          die( print_r( sqlsrv_errors(), true));
    }

?>	

Open in new window


This works fine when I give the array information to it directly (rather than using the $_POST) but when I try to use $_POST to deliver the information from the previous page it gives me the following error:

Array ( [0] => Array ( [0] => IMSSP [SQLSTATE] => IMSSP [1] => -15 'code' => -15 [2] => An invalid direction for parameter 1 was specified. SQLSRV_PARAM_IN, SQLSRV_PARAM_OUT, and SQLSRV_PARAM_INOUT are valid values. [message] => An invalid direction for parameter 1 was specified. SQLSRV_PARAM_IN, SQLSRV_PARAM_OUT, and SQLSRV_PARAM_INOUT are valid values. ) )


My form is giving time strings as per the following:
<td><input name='Shift_Start' type='text' id='Shift_Start' value='".($row['Shift_Start']->format('H:i:s'))."'></td>
<td><input name='Shift_End' type='text' id='Shift_End' value='".($row['Shift_End']->format('H:i:s'))."'></td>
<td><input name='Lunch_Start' type='text' id='Lunch_Start' value='".($row['Lunch_Start']->format('H:i:s'))."'></td>
<td><input name='Lunch_End' type='text' id='Lunch_End' value='".($row['Lunch_End']->format('H:i:s'))."'></td>

Open in new window


And my DB table is as follows:
Snapshot of DB Table
Hope I have provided enough information, but if not please feel free to ask!
Thanks in advance
Luke
0
Comment
Question by:getinked
  • 16
  • 9
  • 4
  • +1
32 Comments
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 39863417
The problem is with passing an array as the data for the sqlsrv_prepare function.  This page http://us1.php.net/manual/en/function.sqlsrv-prepare.php shows an array structure but frankly, I find it confusing when compared to their example.
0
 
LVL 13

Expert Comment

by:duncanb7
ID: 39863432
Dear getinked,

What is  format  that user input Shift_Start, Shift_End,Lunch_Start,Lunch_End, for example,
they will input 18:06:03 at input tag, Right ? Or in time stamp format ?

You better do javascript time format convention before submit the form to yourform.com,

If the input is in time stamp format , you need javascript function  of toHHMMSS to convert the input value to H:i:s format. I mean convert it using format() and then submit
the form to http://your form.com, for just example only and not tested as follows

and please read the link for onsubmit at http://www.w3schools.com/jsref/tryit.asp?filename=tryjsref_form_onsubmit

<form method="post" action="http://yourform.com" onsubmit="format();">......</form>


function format(){
$("#Start_Start).val($("#Start_Start).val().toHHMMSS());
$("#Start_End).val($("#Start_End).val().toHHMMSS());
$("#Lunch_Start).val($("#Lunch_Start).val().toHHMMSS());
$("#Lunch_End).val($("#Lunch_End).val().toHHMMSS());

String.prototype.toHHMMSS = function () {
    var sec_num = parseInt(this, 10); // don't forget the second param
    var hours   = Math.floor(sec_num / 3600);
    var minutes = Math.floor((sec_num - (hours * 3600)) / 60);
    var seconds = sec_num - (hours * 3600) - (minutes * 60);

    if (hours   < 10) {hours   = "0"+hours;}
    if (minutes < 10) {minutes = "0"+minutes;}
    if (seconds < 10) {seconds = "0"+seconds;}
    var time    = hours+':'+minutes+':'+seconds;
    return time;
}
}

Or if what I said that is not correct above, it might be  whether it is related
to sqlsrv_prepare( $conn, $sql, $Update) ?

The number OR string type of  variable parameter in $sql is not matched to $update ?
and what is "&" in  &Agent_ID in array of $Update=array($Shift_Start,$Shift_End,&$Agent_ID); ?



Hope understand your question completely, if not please pt it out .
Give us more detail that will help on it

Duncan
0
 

Author Comment

by:getinked
ID: 39863475
Thanks Duncan, User is submitting as 18:06:03 and this works if i don't transmit the output with $_POST.

The reason for the &$Agent_ID is because this is the only way to pass the ID, not sure why, but in the array this is the only way it is accepted, when I remove the & i get the following error:
Variable parameter 3 not passed by reference (prefaced with an &). Variable parameters passed to sqlsrv_prepare or sqlsrv_query should be passed by reference, not by value.
0
 

Author Comment

by:getinked
ID: 39863479
Thanks Dave, I also fount that very confusing!
I had been over that recourse several times with no joy.

As I mentioned, I know that the array is fine, and the update is correct, For some reason though, When i try to $_POST the data for the update it falls over!
0
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 39863490
Try using it this way and let me know what happens.
$stmt = sqlsrv_prepare( $conn, $sql, array( &$Shift_Start, &$Shift_End, &$Agent_ID));

Open in new window

0
 
LVL 13

Expert Comment

by:duncanb7
ID: 39863494
Thanks for your reply.

What is Agent_ID's value in your form input tag that doesn't show in your post ?
Could you talk more information of Agent_ID ?

And at input tag
<input name='Shift_Start' type='text' id='Shift_Start' value='".($row['Shift_Start']->format('H:i:s'))."'>

($row['Shift_Start']->format('H:i:s')) from which how it works ? could you post your format()
function code to us ?

please check whether The number OR string type of  variable parameter in $sql is not matched to $update or not  ?

If send us the full html and php code(not just partial) , that will be easier for us


Duncan
0
 

Author Comment

by:getinked
ID: 39863504
Dave
Updated as per your post, Error as follows:
Warning: Variable parameter 1 not passed by reference (prefaced with an &). Variable parameters passed to sqlsrv_prepare or sqlsrv_query should be passed by reference, not by value. For more information, see sqlsrv_prepare or sqlsrv_query in the API Reference section of the product documentation. in C:\Inetpub\wwwroot\Development\roster_update_roster.php on line 23

Warning: Variable parameter 2 not passed by reference (prefaced with an &). Variable parameters passed to sqlsrv_prepare or sqlsrv_query should be passed by reference, not by value. For more information, see sqlsrv_prepare or sqlsrv_query in the API Reference section of the product documentation. in C:\Inetpub\wwwroot\Development\roster_update_roster.php on line 23
0
 

Author Comment

by:getinked
ID: 39863509
Duncan

This is the Form:
<?php 
set_time_limit(10);

//Start Table
echo "<p>&nbsp;</p>
<table id='update' align='center' width='950'>";

$server = "---";
$connectionInfo = array( "Database"=>"---", "UID"=>"---", "PWD"=>"---" );
$conn = sqlsrv_connect( $server, $connectionInfo );
$sql = "SELECT * FROM dbo.Roster";
$params = array();
$options =  array( "Scrollable" => SQLSRV_CURSOR_KEYSET );
$stmt = sqlsrv_query( $conn, $sql , $params, $options );


$row_count = sqlsrv_num_rows( $stmt );
while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC) ) {
echo"
<tr><form name='update' action='roster_update_roster.php' method='post'>
<td><input name='Agent_ID' type='text' id='Agent_ID' readonly value='".($row['Agent_ID'])."'></td>
<td>".$row['Agent_Name']."</td>
<td><input name='Shift_Start' type='text' id='Shift_Start' value='".($row['Shift_Start']->format('H:i:s'))."'></td>
<td><input name='Shift_End' type='text' id='Shift_End' value='".($row['Shift_End']->format('H:i:s'))."'></td>
<td><input name='Lunch_Start' type='text' id='Lunch_Start' value='".($row['Lunch_Start']->format('H:i:s'))."'></td>
<td><input name='Lunch_End' type='text' id='Lunch_End' value='".($row['Lunch_End']->format('H:i:s'))."'></td>
<td><input type='submit' name='Submit' value='Submit'></td></tr>";
}
echo "</tr></table>";

?>

Open in new window


This is the "roster_update_roster.php" processing file
<?php
$Shift_Start 	= $_POST['Shift_Start'];
$Shift_End 	= $_POST['Shift_End'];
$Lunch_Start 	= $_POST['Lunch_Start'];
$Lunch_End	= $_POST['Lunch_End']; 
$Agent_ID	= $_POST['Agent_ID'];

$Update		= array(&$Shift_Start,&$Shift_End,&$Agent_ID);

$serverName = "---";
$connectionInfo = array( "Database"=>"---", "UID"=>"---", "PWD"=>"---");
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn === false) {
    die( print_r( sqlsrv_errors(), true));
}

$sql = "UPDATE dbo.Roster
        SET 
			Shift_Start = ?,
			Shift_End = ?
        WHERE Agent_ID = ?";

$stmt = sqlsrv_prepare( $conn, $sql, array( $Shift_Start, $Shift_End, &$Agent_ID));
	if( !$stmt ) {
    die( print_r( sqlsrv_errors(), true));
}

    if( sqlsrv_execute( $stmt ) === false ) {
          die( print_r( sqlsrv_errors(), true));
    }

?>	

Open in new window

0
 
LVL 13

Expert Comment

by:duncanb7
ID: 39863517
Digesting it.sorry about that At the beginning I thought the form is from html code . now it
is shown on php code that is why  it works for  value='".($row['Agent_ID'])."' on php code
not html and not javascript code.


Duncan
0
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 39863523
Are you sure you copied my post?  Because it does not create an error on this machine.  Note the '&' in front of the variable names.  The '&' is necessary because this function 'binds' the SQL variables to the PHP variables.  That means that if the value of the PHP variables changes, those changes are 'automatically' picked up the SQL execute statement (for the duration of this script).
$stmt = sqlsrv_prepare( $conn, $sql, array(&$Shift_Start, &$Shift_End, &$Agent_ID));

Open in new window

0
 

Author Comment

by:getinked
ID: 39863540
Dave

Yep here is is, just tried it again to be sure:
$stmt = sqlsrv_prepare( $conn, $sql, array(&$Shift_Start, &$Shift_End, &$Agent_ID));
	if( !$stmt ) {
    die( print_r( sqlsrv_errors(), true));

Open in new window

Error:

Warning: Variable parameter 1 not passed by reference (prefaced with an &). Variable parameters passed to sqlsrv_prepare or sqlsrv_query should be passed by reference, not by value. For more information, see sqlsrv_prepare or sqlsrv_query in the API Reference section of the product documentation. in C:\Inetpub\wwwroot\Development\roster_update_roster.php on line 25

Warning: Variable parameter 2 not passed by reference (prefaced with an &). Variable parameters passed to sqlsrv_prepare or sqlsrv_query should be passed by reference, not by value. For more information, see sqlsrv_prepare or sqlsrv_query in the API Reference section of the product documentation. in C:\Inetpub\wwwroot\Development\roster_update_roster.php on line 25
Array ( [0] => Array ( [0] => 07002 [SQLSTATE] => 07002 [1] => 0 "code" => 0 [2] => [Microsoft][SQL Server Native Client 10.0]COUNT field incorrect or syntax error [message] => [Microsoft][SQL Server Native Client 10.0]COUNT field incorrect or syntax error ) ) PHP Warning: Variable parameter 1 not passed by reference (prefaced with an &). Variable parameters passed to sqlsrv_prepare or sqlsrv_query should be passed by reference, not by value. For more information, see sqlsrv_prepare or sqlsrv_query in the API Reference section of the product documentation. in C:\Inetpub\wwwroot\Development\roster_update_roster.php on line 25 PHP Warning: Variable parameter 2 not passed by reference (prefaced with an &). Variable parameters passed to sqlsrv_prepare or sqlsrv_query should be passed by reference, not by value. For more information, see sqlsrv_prepare or sqlsrv_query in the API Reference section of the product documentation. in C:\Inetpub\wwwroot\Development\roster_update_roster.php on line 25
0
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 39863547
What Windows version?  What PHP version?  SQLSRV2 or SQLSRV3?  I am using SQL Server Native Client 10.0 also.
0
 

Author Comment

by:getinked
ID: 39863557
Dave:
PHP Version 5.4.9

Windows NT --- 5.2 build 3790
(Windows Server 2003 R2 Standard Edition Service Pack 2) i586

SQLServerVersion: 10.00.1600

Does that help?
0
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 39863650
That tells me you're using SQL Server 2008 (32-bit)  and the SQL Native client that came with it.  Nothing else yet.
0
 

Author Comment

by:getinked
ID: 39863655
Do you need something more from me?
0
 

Author Comment

by:getinked
ID: 39863678
Hi guys, I think I have found the issue (or part there of...)
I added "Var_Dump" to my roster_update_roster.php and found that I am getting mutliple results from the form page...

Any ideas?
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 39863686
Show me the dump.  If, for example, $_POST['Shift_Start'] was actually an array itself, then I can see where it would cause a problem.
0
 

Author Comment

by:getinked
ID: 39863700
array(5) { [0]=> string(8) "09:00:00" [1]=> string(8) "08:30:00" [2]=> string(8) "08:30:00" [3]=> string(8) "08:30:00" [4]=> string(8) "08:30:00" } array(5) { [0]=> string(8) "17:00:00" [1]=> string(8) "17:00:00" [2]=> string(8) "17:00:00" [3]=> string(8) "17:00:00" [4]=> string(8) "17:00:00" } array(5) { [0]=> string(8) "12:30:00" [1]=> string(8) "12:30:00" [2]=> string(8) "12:30:00" [3]=> string(8) "12:30:00" [4]=> string(8) "12:30:00" } array(5) { [0]=> string(8) "13:29:00" [1]=> string(8) "13:29:00" [2]=> string(8) "13:29:00" [3]=> string(8) "13:29:00" [4]=> string(8) "13:29:00" } array(5) { [0]=> string(1) "1" [1]=> string(1) "2" [2]=> string(1) "3" [3]=> string(1) "4" [4]=> string(1) "5" }
0
 

Author Comment

by:getinked
ID: 39863724
This may make more sense...
When I click on Submit, I expect only the 1 set of results to flow to the next page
ie; if i Select the first row, only the changes for "Luke Chilcott" should come through but as you can see from the var_dump I am getting all fields from all agents.
*note* It would be AWESOME if I could have a single submit to change all fields for all agent, in case that is easier.

Here is the Screen shot I have put together to show the issue in more detail...
The Issue
0
 
LVL 82

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 100 total points
ID: 39863825
You're getting all of the data in part because every form has the same name.  And since each item is an array, you're getting an error message.  If you used 'foreach' to separate them into 'sets' of variables, you could do a loop that would update each row that way.

If you used a counter ($formcnt) and added it to the name of the form, then you should get only the row with that submit button instead of all of them.
$formcnt++;
echo"
<tr><form name='update$formcnt' action='roster_update_roster.php' method='post'>

Open in new window

There are other things you can do to separate the rows.
0
 

Author Comment

by:getinked
ID: 39863845
Thanks Dave, I do want all of the rows to appear, not sure this is going to resolve my issue.
0
 

Author Comment

by:getinked
ID: 39863866
Hi Guys,
So it looks like I have worked it out almost.

the following is working for the first record in the array:
<?php
$Shift_Start 	=($_POST['Shift_Start']);
$Shift_End 		=($_POST['Shift_End']);
$Lunch_Start 	=($_POST['Lunch_Start']);
$Lunch_End		=($_POST['Lunch_End']); 
$Agent_ID		=($_POST['Agent_ID']);

$Update			= array($Shift_Start[0], $Shift_End[0], $Lunch_Start[0], $Lunch_End[0]);

$serverName = "---";
$connectionInfo = array( "Database"=>"---", "UID"=>"---", "PWD"=>"---");
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn === false) {
    die( print_r( sqlsrv_errors(), true));
}

$sql = "UPDATE dbo.Roster
        SET 
			Shift_Start = ( ?),
			Shift_End = ( ?),
			Lunch_Start = ( ?),
			Lunch_End = ( ?)";

$stmt = sqlsrv_prepare( $conn, $sql, array(&$Update[0],&$Update[1],&$Update[2],&$Update[3]));
	if( !$stmt ) {
    die( print_r( sqlsrv_errors(), true));
}
( sqlsrv_execute( $stmt ));
    if( sqlsrv_execute( $stmt ) === false ) {
          die( print_r( sqlsrv_errors(), true));
    }




?>	

Open in new window



What I need to do now is get the update to loop through the available results.
I think i need to get the following to increment by one for every result (or row counted), what are your thoughts?

$Update = array($Shift_Start[0], $Shift_End[0], $Lunch_Start[0], $Lunch_End[0]);

Open in new window

0
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 39863949
Your 'sqlsrv_prepare' statement needs to be inside the loop but the $sql doesn't.  That's because the 'sqlsrv_prepare' function tries to 'bind' the variable to the SQL statement variables.  That not what you want here because the variables for the different rows are not in the 'same place' that the '&' pass-by-reference wants them to be.
0
 
LVL 42

Expert Comment

by:Chris Stanyon
ID: 39864595
When you're submitting rows of form field like this, you can make your life a whole lot easier by naming your form fields correctly. This means using an array for the names, and the easiest way to loop through your records in a PHP script is to name then something like this:

details[agentID][Shift_Start]
details[agentID][Shift_End]
details[agentID][Lunch_Start]
details[agentID][Lunch_End]

In your form building script, use something like this:

<tr>
<td><input name='details[" . $row['Agent_ID'] . "][Shift_Start]' type='text' value='" . $row['Shift_Start']->format('H:i:s') . "'></td>
<td><input name='details[" . $row['Agent_ID'] . "][Shift_End]' type='text' id='Shift_End' value='" . $row['Shift_End']->format('H:i:s') . "'></td>
<td><input name='details[" . $row['Agent_ID'] . "][Lunch_Start]' type='text' id='Lunch_Start' value='" . $row['Lunch_Start']->format('H:i:s') . "'></td>
<td><input name='details[" . $row['Agent_ID'] . "][Lunch_End]' type='text' id='Lunch_End' value='" . $row['Lunch_End']->format('H:i:s') . "'></td>
</tr>

Open in new window

Then when the whole form is posted, you have a POST key called 'details' that you can loop through:

foreach ($_POST['details'] as $agentID => $info):
   echo $agentID;
   echo $info['Shift_Start'];
   echo $info['Shift_End'];
   echo $info['Lunch_Start'];
   echo $info['Lunch_End'];
   //execute your Update in here :)
endforeach;

Open in new window

0
 

Author Comment

by:getinked
ID: 39866096
Thanks Chris, It worked... Twice, Then it died...
I am getting this error:

Shift_Start
Warning: Illegal string offset 'Shift_Start' in C:\Inetpub\wwwroot\Development\roster_update_roster.php on line 22
5
Warning: Illegal string offset 'Shift_End' in C:\Inetpub\wwwroot\Development\roster_update_roster.php on line 23
5
Warning: Illegal string offset 'Lunch_Start' in C:\Inetpub\wwwroot\Development\roster_update_roster.php on line 24
5
Warning: Illegal string offset 'Lunch_End' in C:\Inetpub\wwwroot\Development\roster_update_roster.php on line 25
5
Warning: Illegal string offset 'Shift_Start' in C:\Inetpub\wwwroot\Development\roster_update_roster.php on line 27

Fatal error: Cannot create references to/from string offsets in C:\Inetpub\wwwroot\Development\roster_update_roster.php on line 27
PHP Warning: Illegal string offset 'Shift_Start' in C:\Inetpub\wwwroot\Development\roster_update_roster.php on line 22 PHP Warning: Illegal string offset 'Shift_End' in C:\Inetpub\wwwroot\Development\roster_update_roster.php on line 23 PHP Warning: Illegal string offset 'Lunch_Start' in C:\Inetpub\wwwroot\Development\roster_update_roster.php on line 24 PHP Warning: Illegal string offset 'Lunch_End' in C:\Inetpub\wwwroot\Development\roster_update_roster.php on line 25 PHP Warning: Illegal string offset 'Shift_Start' in C:\Inetpub\wwwroot\Development\roster_update_roster.php on line 27 PHP Fatal error: Cannot create references to/from string offsets in C:\Inetpub\wwwroot\Development\roster_update_roster.php on line 27

We are so close!
0
 
LVL 42

Expert Comment

by:Chris Stanyon
ID: 39866123
Generally in programming, something either works or it doesn't. It doesn't work twice and then fail, unless changes were made to your code after the second run :)

I have no idea what lines 22-27 of your code are!! Can you post your roster_update_roster.php script, along with the part of your HTML code that generates the form fields so we can make sure they tie up.

At least we're making progress :)
0
 

Author Comment

by:getinked
ID: 39866162

The Form

<html>
<head>


<style>
body {
font-family:Verdana;
font-size:10px;
}
table {
border-collapse:collapse;
}
th {
font-family:Verdana;
font-size:10px;
text-align:right;
padding: 3px;
background-color:#3396FF;
color:#ffffff;
border:1px solid #3396FF;
}
td {
font-family:Verdana;
font-size:10px;
text-align:right;
border-width:thin;
border:1px solid #B3B3B3;
padding:2px 8px 2px 2px;
}
</style>

</head>
<body >


<!--<div id="loading" style="position:absolute; width:100%; text-align:center; top:50px;">
<img src="http://ndssl.vbi.vt.edu/gis/dbv/v2.0/images/loading_circle_fully_working.gif" width"50" border="0">
</div>
-->
<?php 
set_time_limit(10);

//Start Table
echo "<p>&nbsp;</p>
<table id='update' align='center' width='950'>";

$server = "---";
$connectionInfo = array( "Database"=>"---", "UID"=>"---", "PWD"=>"---" );
$conn = sqlsrv_connect( $server, $connectionInfo );
$sql = "SELECT * FROM dbo.Roster";
$params = array();
$options =  array( "Scrollable" => SQLSRV_CURSOR_KEYSET );
$stmt = sqlsrv_query( $conn, $sql , $params, $options );

$rowC = sqlsrv_num_rows($stmt);
echo $rowC;
while( $row = sqlsrv_fetch_array( $stmt) ) {
echo"
<tr><form name='details' action='roster_update_roster.php' method='post'>
<td>".$row['Agent_Name']."</td>
<td><input name='details[Shift_Start]". $row['Agent_ID']. "' type='text' value='" . $row['Shift_Start']. "'></td>
<td><input name='details[Shift_End]". $row['Agent_ID']. "' type='text' id='Shift_End' value='" . $row['Shift_End']. "'></td>
<td><input name='details[Lunch_Start]". $row['Agent_ID']. "' type='text' id='Lunch_Start' value='" . $row['Lunch_Start']. "'></td>
<td><input name='details[Lunch_End]". $row['Agent_ID']. "' type='text' id='Lunch_End' value='" . $row['Lunch_End']. "'></td>
</tr>";
}
echo "<tr><td colspan='5'><input type='submit' name='Submit' value='Submit'></td></tr></table>";

?>

Open in new window


The Process

<?php
set_time_limit(15);
$serverName = "---";
$connectionInfo = array( "Database"=>"---", "UID"=>"---", "PWD"=>"---");
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn === false) {
    die( print_r( sqlsrv_errors(), true));
}

//var_dump($_POST['details']);

$sql = "UPDATE dbo.Roster
        SET 
			Shift_Start = ( ?),
			Shift_End = ( ?),
			Lunch_Start = ( ?),
			Lunch_End = ( ?)
		WHERE Agent_ID = ( ?)";

foreach ($_POST['details'] as $agentID => $info):
   echo $agentID;
   echo $info['Shift_Start'];
   echo $info['Shift_End'];
   echo $info['Lunch_Start'];
   echo $info['Lunch_End'];

$stmt = sqlsrv_prepare( $conn, $sql, array(&$info['Shift_Start'],&$info['Shift_End'],&$info['Lunch_Start'],&$info['Lunch_End'],&$agentID));
	if( !$stmt ) {
    die( print_r( sqlsrv_errors(), true));
}
( sqlsrv_execute( $stmt ));
    if( sqlsrv_execute( $stmt ) === false ) {
          die( print_r( sqlsrv_errors(), true));
    }
endforeach;

?>	

Open in new window

0
 
LVL 42

Accepted Solution

by:
Chris Stanyon earned 400 total points
ID: 39866179
Your HTML is naming the fields incorrectly. What we need is: details[agentID][fieldName]

What you have is details[fieldName]agentID

Change your code to this:

<td><input name='details[". $row['Agent_ID']. "][Shift_Start]' type='text' value='" . $row['Shift_Start']. "'></td>
<td><input name='details[". $row['Agent_ID']. "][Shift_End]' type='text' id='Shift_End' value='" . $row['Shift_End']. "'></td>
<td><input name='details[". $row['Agent_ID']. "][Lunch_Start]' type='text' id='Lunch_Start' value='" . $row['Lunch_Start']. "'></td>
<td><input name='details[". $row['Agent_ID']. "][Lunch_End]' type='text' id='Lunch_End' value='" . $row['Lunch_End']. "'></td>

Open in new window

You're also including a form on every row of the table - if you want to handle all rows at the same time, then you only need one form - put the opening form tag before your table and the closing form tag after the table.

I'm not overly familiar with the MS SQL library, but normally (at least when using mySQL/PDO), you only need to prepare the SQL statement once. You then execute that prepared statement inside your loop as many times as you need. While your script won't 'break' it is preparing the query over and over again, probably unnecessarily.

Make those changes and give it another whirl
0
 

Author Comment

by:getinked
ID: 39866213

YEEEEEHAAAAA

Awesome! Thank you to both Chris and Dave!
It is working!
0
 

Author Closing Comment

by:getinked
ID: 39866216
Thank you to everyone that assisted in this, Especially Dave for your tremendous assistance and Chris for the Solution!
0
 
LVL 42

Expert Comment

by:Chris Stanyon
ID: 39866222
Now that's one happy coder :)
0
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 39866279
Yes it is!!  Glad to help!
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
The viewer will learn how to count occurrences of each item in an array.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now