Table record not updated using prepare and execute

[Claus Mygind]

I am using php 5.3 and MySQL 5.0

The php is installed using mysqli not pdo
So I cannot use pdo syntax


I have 3 prepared statements
1. one for look up
2. one for INSERT
3. one for UPDATE

The first two statements work fine and execute as they are suppose to.

I have tested the update statement on the MySQL workbench and it works just fine there. Also all the steps and results leading up to the UPDATE execute as expected.


The following code does not update the table record when executed. The $result is always equal to false. Any usable help will be appreciated.

/*
-------------------------
prepared update statement
-------------------------
*/

$updStmt = $db->prepare('update `time` set HOL=8.00,WEBUSER="'.$webUser.'",WEBDATE='.$webDate.',WEBADDEDIT ="Edit" where EMPNODATE = ?');

/* Bind our params */
$updStmt->bind_param('s',$empNoDate);

/*execute updStmt*/
$result = $updStmt->execute();
if ($result == false ) 
{
  $fEdit.='~'.$c["EMPNO"];
}else{
  $cEdit++;
}

[Claus Mygind]

I have now determined that a standard $mysqli->('...') query also does not update the table from the app. So perhaps there is something I do not understand in using multiple prepare statements.

Here is the entirety of my code in the apps. Perhaps I need a close statement somewhere. or something?

<?php
try  
{
	/*open connection to database*/
	include("c:\<path>\<tomyconnection>.php");

	/*
	------------------------------------------------
	submitted data is stored in $oCGI assoc array
	------------------------------------------------
	*/

	// setup variables
	$webUser  = ((isset($_SERVER["REDIRECT_REMOTE_USER"])) ?$_SERVER["REDIRECT_REMOTE_USER"] :"test");
	$webDate  = date ('Ymd');
	$cHoliday = date ("Ymd", strtotime($_REQUEST["Holiday"]));


	$cAdd    = 0;
	$cEdit   = 0;
	$fAdd    = '';
	$fEdit   = '';
	/*
	------------------------------------------------
	retrive all recs from table
	------------------------------------------------
	*/
	$sql = 'select * from <table> order by myKey';
	if ($result = $db->query($sql))
	{
		
		$emp = mysqli_fetch_all( $result , $resulttype = MYSQLI_ASSOC );
		$recCnt = $result->num_rows;

		/*
		----------------------
		prepared add statement
		----------------------
		*/
		$addStmt = $db->prepare('insert into `time` set	`keyCol`=?,`col1`=?,`DATE`=?,`LAST`=?,`FIRST`=?,`LOCATION`=?,`HOL`=8.00,`WEBUSER`="'.$webUser.'",`WEBDATE`='.$webDate.',`WEBADDEDIT` ="Add"');   
		
		/* Bind our params */
		$addStmt->bind_param('ssisss',$keyCol, $col1, $date, $last, $first, $location);

		/*
		-------------------------
		prepared update statement
		-------------------------
		*/
		$updStmt = $db->prepare('update `time` set HOL=8.00,WEBUSER="'.$webUser.'",WEBDATE='.$webDate.',WEBADDEDIT ="Edit" where keyCol = ?');

		/* Bind our params */
		$updStmt->bind_param('s',$keyCol);

		/*
		-------------------------
		prepared lookup statement
		-------------------------
		*/
		$lkuStmt = $db->prepare("select keyCol from `time` where keyCol = ?");

		/* Bind our params */
		$lkuStmt->bind_param('s',$keyCol);

		/*
		----------------------------
		loop through employee rowset
		----------------------------
		*/
		for ($n=0; $n < $recCnt; $n++) 
		{
			$c = $emp[$n];

			/* Set up params */
			$keyCol = $c["col1"].$cHoliday;
			$col1     = $c["col1"];
			$date      = $cHoliday;
			$last      = $c["LAST"];
			$first     = $c["FIRST"];
			$location  = $c["LOCATION"];

			/*
			-------------------------------
			check if this employee 
			has time entered for this date
			-------------------------------
			*/
			if ($lkuStmt->execute()) 
			{
				/* bind result variables */
				$lkuStmt->bind_result($wasFound);
				
				/* fetch value */
				$lkuStmt->fetch();
				
				if ($wasFound != $keyCol)
				{
					/*
					------------------------------
					add holiday for this employee
					------------------------------
					*/
					$resultY = $addStmt->execute();
					if ($resultY == false ) 
					{
						$fAdd.='~'.$c["col1"];
					}else{
						$cAdd++;
					}
				}else{
				/*
					------------------------------------------
					update time for this employee for this day
					------------------------------------------
					*/
					$sql = 'update time set HOL=8.00, WEBUSER="'.$webUser.'", WEBDATE='.$webDate.', WEBADDEDIT ="Edit"  where keyCol = "'.$keyCol.'"';

//					$resultX = $updStmt->execute();
//					if ($resultX == false ) 
					if($resultX = $db->query($sql))
					{
						$cEdit++;
					}else{
						$fEdit.='~'.$c["col1"];
					}
				} /* end if add or update */
			}else{
				throw new Exception("Lookup query for ".$c["col1"]." failed!");
			}/* end if lookup worked*/
		} /* end of employee file loop */

	}else{
		throw new Exception("query to open <myLookUpTable> file failed!");
	}

	$response = '{"added":'.$cAdd.', failedAdds'.$fAdd.',"edited":'.$cEdit.', failedEdits'.$fEdit.'}';

} /* end try */
catch (exception $e) 
{
//if you want to find what line threw exception uncomment next line
$firephp->log($e, 'e');
	$response = 'An error occured : ' .$e->getMessage();
}
echo json_encode ($response);
$db->close();

?>

[Dormilich]

what do the MySQLi error handlers say?

(e.g. http://php.net/manual/en/mysqli-stmt.error-list.php)

[Claus Mygind]

I'm not able to get the error handler to run. I have tried both the procedural and object oriented methods.

I get the following message
Undefined property: mysqli_stmt::$e rror_list

					$resultX = $updStmt->execute();
					if ($resultX == false ) 
					{
						$cEdit++;

					}else{
						$fEdit.='~'.$c["EMPNO"];
					}
$firephp->log($updStmt->error_list, '$error_list');

and likewise for mysqli_stmt_err or_list()
Call to undefined function mysqli_stmt_err or_list()

where I inserted it like this

					$resultX = $updStmt->execute();
					if ($resultX == false ) 
					{
						$cEdit++;

					}else{
						$fEdit.='~'.$c["EMPNO"];
					}
echo "Error:\n";
    print_r(mysqli_stmt_error_list($updStmt));

I will see if I can put together a demo app to recreate the problem with sample data which I can post.

[Claus Mygind]

Ok I see the problem with the error handler, I'm running version 5.3 of php and the error handler was first introduced in version 5.4.

Have to finish my conversion of old code to php before I can think about upgrading.

[Dormilich]

then it should work with $updStmt->error.

[Dormilich]

you could also try

$driver = new mysqli_driver();
$driver->report_mode = MYSQLI_REPORT_STRICT;

[Claus Mygind]

Here is a sample code and data to demonstrate my problem.

Oddly enough not only does the "UPDATE" not occur, the test on line 106/108 to see if a records exists also does not work.

Below the code is the result I got.

<?php
try  // Error trap entire applet.; 
{
	/*open connection to database*/
	//**********SET UP YOUR DATABASE CONNECTION**************************
	$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

	$mysqli->query("CREATE TABLE myTest1 (`empno` VARCHAR(4) not null default' ', PRIMARY KEY(`empno`) ) ENGINE=MyISAM");

	$mysqli->query("INSERT INTO myTest1 set empno='0139'");
	$mysqli->query("INSERT INTO myTest1 set empno='0173'");
	$mysqli->query("INSERT INTO myTest1 set empno='0189'");
	$mysqli->query("INSERT INTO myTest1 set empno='0194'");
	$mysqli->query("INSERT INTO myTest1 set empno='0237'");
	$mysqli->query("INSERT INTO myTest1 set empno='0362'");
	$mysqli->query("INSERT INTO myTest1 set empno='0447'");
	$mysqli->query("INSERT INTO myTest1 set empno='0521'");
	$mysqli->query("INSERT INTO myTest1 set empno='0523'");

	$mysqli->query("CREATE TABLE myTest2 (`empno` VARCHAR(4) not null default' ',`hol` DECIMAL(5,2) NOT NULL DEFAULT '0.00', `date` DATE NOT NULL DEFAULT '0000-00-00', PRIMARY KEY(`empno`)) ENGINE=MyISAM");

	$mysqli->query("INSERT INTO myTest2 set empno='0139', hol=0.00, date=20140917");
	$mysqli->query("INSERT INTO myTest2 set empno='0173', hol=0.00, date=20140917");
	$mysqli->query("INSERT INTO myTest2 set empno='0189', hol=0.00, date=20140917");
	$mysqli->query("INSERT INTO myTest2 set empno='0194', hol=0.00, date=20140917");
	$mysqli->query("INSERT INTO myTest2 set empno='0237', hol=0.00, date=20140917");
	$mysqli->query("INSERT INTO myTest2 set empno='0362', hol=0.00, date=20140917");
	$mysqli->query("INSERT INTO myTest2 set empno='0447', hol=0.00, date=20140917");
//	$mysqli->query("INSERT INTO myTest2 set empno='0521', hol=0.00, date=20140917");
//	$mysqli->query("INSERT INTO myTest2 set empno='0523', hol=0.00, date=20140917");

	$cHoliday = date ("Ymd", strtotime("2014-09-17"));


	$cAdd    = 0;
	$cEdit   = 0;
	$fAdd    = '';
	$fEdit   = '';
	/*
	------------------------------------------------
	retrive all employees from myTest1 table
	------------------------------------------------
	*/
	$sql = 'select * from myTest1 order by empno';
	if ($result = $mysqli->query($sql))
	{
		
		$emp = mysqli_fetch_all( $result , $resulttype = MYSQLI_ASSOC );
		$recCnt = $result->num_rows;

		/*
		----------------------
		prepared add statement
		----------------------
		*/
		$addStmt = $mysqli->prepare('insert into `myTest2` set `empno`=?,`hol`=8.00,`date`=?');
		
		/* Bind our params */
		$addStmt->bind_param('ss', $empNo, $cHoliday);

		/*
		-------------------------
		prepared update statement
		-------------------------
		*/
		$updStmt = $mysqli->prepare('update `myTest2` set hol=8.00 where empno = ?');

		/* Bind our params */
		$updStmt->bind_param('s',$empno);

		/*
		-------------------------
		prepared lookup statement
		-------------------------
		*/
		$lkuStmt = $mysqli->prepare("select empno from `myTest2` where empno = ?");

		/* Bind our params */
		$lkuStmt->bind_param('s',$empno);

		/*
		----------------------------
		loop through employee rowset
		----------------------------
		*/
		for ($n=0; $n < $recCnt; $n++) 
		{
			$c = $emp[$n];

			/* Set params */
			$empNo     = $c["empno"];
			$date      = $cHoliday;

			/*
			-------------------------------
			check if this employee 
			has time entered for this date
			-------------------------------
			*/
			if ($lkuStmt->execute()) 
			{
				/* bind result variables */
				$lkuStmt->bind_result($wasFound);
				
				/* fetch value */
				$lkuStmt->fetch();
				
				if ($wasFound != $empno)
				{
					/*
					------------------------------
					add holiday for this employee
					------------------------------
					*/
					$resultY = $addStmt->execute();
					if ($resultY == false ) 
					{
						/*inserting record failed*/
						$fAdd.='~'.$c["empno"];
					}else{
						$cAdd++;
					}
				}else{
				/*
					-----------------------------
					update time for this employee
					-----------------------------
					*/
					$resultX = $updStmt->execute();
					if ($resultX == false ) 
					{
						/*udating record failed*/
						$fEdit.='~'.$c["empno"];

					}else{
						$cEdit++;
					}
				} /* end if insert or update */
			}else{
				throw new Exception("Lookup query for ".$c["empno"]." failed!");
			}/* end if lookup worked*/
		} /* end of employee file loop */

	}else{
		throw new Exception("query to open myTest1 file failed!");
	}

	echo 'added='.$cAdd.', failedAdds ='.$fAdd.', edited='.$cEdit.', failedEdits='.$fEdit;

	$sql = 'select * from myTest2 order by empno';
	if ($result = $mysqli->query($sql))
	{
		while ($c = $result->fetch_assoc()) {
			echo '<br>'.$c['empno'].' holiday hours = '.$c['hol'];
		}
	}


} /* end try */
catch (exception $e) 
{
	echo 'An error occured : ' .$e->getMessage();
}
$mysqli->close();

?>

Resulting code should have added two records for employee 0521 and 0523, the other 7 records should have been updated such that the hol column is set to 8 hours per employee.

added=0, failedAdds =, edited=9, failedEdits=
0139 holiday hours = 0.00
0173 holiday hours = 0.00
0189 holiday hours = 0.00
0194 holiday hours = 0.00
0237 holiday hours = 0.00
0362 holiday hours = 0.00
0447 holiday hours = 0.00

The result should have looked like this
added=2, failedAdds =, edited=7, failedEdits=
0139 holiday hours = 8.00
0173 holiday hours = 8.00
0189 holiday hours = 8.00
0194 holiday hours = 8.00
0237 holiday hours = 8.00
0362 holiday hours = 8.00
0447 holiday hours = 8.00
0521 holiday hours = 8.00
0523 holiday hours = 8.00

[Claus Mygind]

Per your suggestion I inserted the code you suggested as shown below. I received no feed back when I ran the code I posted. The test on the result of the update always gives a true, so that part of it is not failing

try  // Error trap entire applet.; 
{


    $driver = new mysqli_driver();
    $driver->report_mode = MYSQLI_REPORT_STRICT;

......... other code see previous post ..........


					$resultX = $updStmt->execute();
echo $updStmt->error;
					if ($resultX == false ) 
					{
						/*udating record failed*/
						$fEdit.='~'.$c["empno"];

					}else{
						$cEdit++;
					}
........ more of the code above ......

[Claus Mygind]

By converting the prepared statement for the lookup to a basic mysqli->query($sql) statement, I was able to get it to work in my actual code, but not in the test code I posted here. Albeit, the code runs much slower than with the prepared statement.

So there must be something I don't understand about prepared statements. It would seem what I am doing should be a standard type of operation.
1) look up a value in a table
2) depending of the result:
A) add a new record to the table or
B) edit an existing record in the table.

If anyone has a working example or can tell me why my sample test code above will not execute with 3 prepared statements, I would truly appreciate it.

[B]//inserted standard query request like this[/B]
$lsql = 'select empno from `myTest2` where empno = '.$empno;
	if ($result = $db->query($lsql))
	{
//	if ($lkuStmt->execute()) 
//	{
//		/* bind result variables */
//		$lkuStmt->bind_result($wasFound);
//				
//		/* fetch value */
//		$lkuStmt->fetch();
//[B]made a standard test to see if a record was found[/B]
		if ($result->num_rows == 0)
		{

//		if ($wasFound != $empNoDate)
//		{

[Dormilich]

So there must be something I don't understand about prepared statements. It would seem what I am doing should be a standard type of operation.
1) look up a value in a table
2) depending of the result:
A) add a new record to the table or
B) edit an existing record in the table.

erm, INSERT … ON DUPLICATE KEY UPDATE

[Claus Mygind]

Thanks for the input, but that really is not the problem here. There are no duplicate records in the data set. Also the problem I'm trying to resolve is why the "UPDATE" is not occurring. Note that when the sample code is run the result is 0 not 8 hours (compare the output to the expected results I posted above).

I posted a complete sample code in hopes that someone would copy the code, run it and confirm that indeed I have a problem with the update.

So far the only thing I have been able to determine is if I replace the prepared statement for the look up with a standard ->query() request, I can get it to work.

Please copy my code and run it.

[Dormilich]

There are no duplicate records in the data set.

And that’s exactly why I recommend to use INSERT … ON DUPLICATE KEY UPDATE. it does an insert if the employee does not exist and an update if he does. this way you don’t need to use two different queries with an outside (of SQL) comparison.

in code

INSERT INTO
    `myTest2`
SET
    empno = ?,
    hol   = 8.00,
    date  = ?
ON DUPLICATE KEY UPDATE
    hol   = 8.00
;

[Claus Mygind]

Ok, thanks for taking the blinders off my eyes. I'm going to give that a shot.

[Claus Mygind]

Thanks Dormilich for your post. With your suggestion I was able to eliminate 2 of the 3 prepare statements and cut my processing time by 75%.

The only thing I have not been able to do is count the number of INSERTS vs. UPDATES. I can get the total using the code below. I have attempted to use mysqli_info($va r);, which should return that information but I get a parameter error. Any additional suggestion to solve this problem would be appreciated (see lines 79 - 87 in code below).

Here is the corrected code for anyone who may need similar help.

<?php
try  // Error trap entire applet.; 
{
    /*open connection to database*/
    //**********SET UP YOUR DATABASE CONNECTION**************************
    $mysqli = new mysqli("localhost", "my_user", "my_password", "world");
 
    $mysqli->query("CREATE TABLE myTest1 (`empno` VARCHAR(4) not null default' ', PRIMARY KEY(`empno`) ) ENGINE=MyISAM");
 
    $mysqli->query("INSERT INTO myTest1 set empno='0139'");
    $mysqli->query("INSERT INTO myTest1 set empno='0173'");
    $mysqli->query("INSERT INTO myTest1 set empno='0189'");
    $mysqli->query("INSERT INTO myTest1 set empno='0194'");
    $mysqli->query("INSERT INTO myTest1 set empno='0237'");
    $mysqli->query("INSERT INTO myTest1 set empno='0362'");
    $mysqli->query("INSERT INTO myTest1 set empno='0447'");
    $mysqli->query("INSERT INTO myTest1 set empno='0521'");
    $mysqli->query("INSERT INTO myTest1 set empno='0523'");
 
    $mysqli->query("CREATE TABLE myTest2 (`empno` VARCHAR(4) not null default' ',`hol` DECIMAL(5,2) NOT NULL DEFAULT '0.00', `date` DATE NOT NULL DEFAULT '0000-00-00', PRIMARY KEY(`empno`)) ENGINE=MyISAM");
 
    $mysqli->query("INSERT INTO myTest2 set empno='0139', hol=0.00, date=20140917");
    $mysqli->query("INSERT INTO myTest2 set empno='0173', hol=0.00, date=20140917");
    $mysqli->query("INSERT INTO myTest2 set empno='0189', hol=0.00, date=20140917");
    $mysqli->query("INSERT INTO myTest2 set empno='0194', hol=0.00, date=20140917");
    $mysqli->query("INSERT INTO myTest2 set empno='0237', hol=0.00, date=20140917");
    $mysqli->query("INSERT INTO myTest2 set empno='0362', hol=0.00, date=20140917");
    $mysqli->query("INSERT INTO myTest2 set empno='0447', hol=0.00, date=20140917");
//    $mysqli->query("INSERT INTO myTest2 set empno='0521', hol=0.00, date=20140917");
//    $mysqli->query("INSERT INTO myTest2 set empno='0523', hol=0.00, date=20140917");
 
    $cHoliday = date ("Ymd", strtotime("2014-09-17"));
 
 
    $cAdd    = 0;
    $cEdit   = 0;
    $fAdd    = '';
    $fEdit   = '';
    /*
    ------------------------------------------------
    retrive all employees from myTest1 table
    ------------------------------------------------
    */
    $sql = 'select * from myTest1 order by empno';
    if ($result = $mysqli->query($sql))
    {
 
        $emp = mysqli_fetch_all( $result , $resulttype = MYSQLI_ASSOC );
        $recCnt = $result->num_rows;
 
        /*
        ----------------------
        prepared add statement
        ----------------------
        */
		$addStmt = $mysqli->prepare('INSERT INTO `myTest2` SET empno = ?, hol   = 8.00, date  = ? ON DUPLICATE KEY UPDATE hol   = 8.00;');		
 
        /* Bind our params */
        $addStmt->bind_param('ss', $empNo, $cHoliday);
 
        /*
        ----------------------------
        loop through employee rowset
        ----------------------------
        */
        for ($n=0; $n < $recCnt; $n++) 
        {
            $c = $emp[$n];
 
            /* Set params */
            $empNo     = $c["empno"];
            $date      = $cHoliday;
 
            /*
            ------------------------------
            add holiday for this employee
            ------------------------------
            */
            [B]$resultY = $addStmt->execute();[/B]
            [B]if ($resultY == false ) [/B]
            [B]{[/B]
                /*inserting record failed*/
                [B]$fAdd.='~'.$c["empno"];[/B]
            [B]}else{[/B]
                [B]$cAdd++;[/B]
           [B] }[/B]
        } /* end of employee file loop */
 
    }else{
        throw new Exception("query to open myTest1 file failed!");
    }
 
    echo 'added='.$cAdd.', failedAdds ='.$fAdd.', edited='.$cEdit.', failedEdits='.$fEdit;
 
    $sql = 'select * from myTest2 order by empno';
    if ($result = $mysqli->query($sql))
    {
        while ($c = $result->fetch_assoc()) {
            echo '<br>'.$c['empno'].' holiday hours = '.$c['hol'];
        }
    }
} /* end try */
catch (exception $e) 
{
    echo 'An error occured : ' .$e->getMessage();
}
$mysqli->close();
 
?>