473,378 Members | 1,388 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

home > topics > php > questions > table record not updated using prepare and execute

Join Bytes to post your question to a community of 473,378 software developers and data experts.

Table record not updated using prepare and execute

Claus Mygind
571 512MB
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.

Expand|Select|Wrap|Line Numbers
  1. /*
  2. -------------------------
  3. prepared update statement
  4. -------------------------
  5. */
  6.  
  7. $updStmt = $db->prepare('update `time` set HOL=8.00,WEBUSER="'.$webUser.'",WEBDATE='.$webDate.',WEBADDEDIT ="Edit" where EMPNODATE = ?');
  8.  
  9. /* Bind our params */
  10. $updStmt->bind_param('s',$empNoDate);
  11.  
  12. /*execute updStmt*/
  13. $result = $updStmt->execute();
  14. if ($result == false ) 
  15. {
  16.   $fEdit.='~'.$c["EMPNO"];
  17. }else{
  18.   $cEdit++;
  19. }
  20.  
Sep 19 '14 #1

✓ answered by Claus Mygind

Below is the sample code that with the help of Dormilich has one prepared statement which
1) checks if a record exists
2) INSERTS if not found or
3) UPDATES if found
4) counts the number of INSERTS, UPDATES and errors.
While I could not find out how to use mysqli_info(); I did find I could use $mysqli->affected_rows; to determine if a record was INSERTED, UPDATED or not processed at all (see lines (74 - 86 in code below). Oddly enough affected_rows returns on INSERT... ON DUPLICATE KEY... a 1 for INSERTS, a 2 for UPDATES and I would assume a 0 for not processed. The affected_rows can be executed right after the prepared statement, to capture the result of the transaction (see switch statement below)



Expand|Select|Wrap|Line Numbers
  1. <?php
  2. try  // Error trap entire applet.; 
  3. {
  4.     /*open connection to database*/
  5.     //**********SET UP YOUR DATABASE CONNECTION**************************
  6.     $mysqli = new mysqli("localhost", "my_user", "my_password", "world");
  7.  
  8.     $mysqli->query("CREATE TABLE myTest1 (`empno` VARCHAR(4) not null default' ', PRIMARY KEY(`empno`) ) ENGINE=MyISAM");
  9.  
  10.     $mysqli->query("INSERT INTO myTest1 set empno='0139'");
  11.     $mysqli->query("INSERT INTO myTest1 set empno='0173'");
  12.     $mysqli->query("INSERT INTO myTest1 set empno='0189'");
  13.     $mysqli->query("INSERT INTO myTest1 set empno='0194'");
  14.     $mysqli->query("INSERT INTO myTest1 set empno='0237'");
  15.     $mysqli->query("INSERT INTO myTest1 set empno='0362'");
  16.     $mysqli->query("INSERT INTO myTest1 set empno='0447'");
  17.     $mysqli->query("INSERT INTO myTest1 set empno='0521'");
  18.     $mysqli->query("INSERT INTO myTest1 set empno='0523'");
  19.  
  20.     $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");
  21.  
  22.     $mysqli->query("INSERT INTO myTest2 set empno='0139', hol=0.00, date=20140917");
  23.     $mysqli->query("INSERT INTO myTest2 set empno='0173', hol=0.00, date=20140917");
  24.     $mysqli->query("INSERT INTO myTest2 set empno='0189', hol=0.00, date=20140917");
  25.     $mysqli->query("INSERT INTO myTest2 set empno='0194', hol=0.00, date=20140917");
  26.     $mysqli->query("INSERT INTO myTest2 set empno='0237', hol=0.00, date=20140917");
  27.     $mysqli->query("INSERT INTO myTest2 set empno='0362', hol=0.00, date=20140917");
  28.     $mysqli->query("INSERT INTO myTest2 set empno='0447', hol=0.00, date=20140917");
  29. //    $mysqli->query("INSERT INTO myTest2 set empno='0521', hol=0.00, date=20140917");
  30. //    $mysqli->query("INSERT INTO myTest2 set empno='0523', hol=0.00, date=20140917");
  31.  
  32.     $cHoliday = date ("Ymd", strtotime("2014-09-17"));
  33.  
  34.     $cAdd  = 0;
  35.     $cEdit = 0;
  36.     $cErr  = 0;
  37.     $fErr  = '';
  38.     /*
  39.     ------------------------------------------------
  40.     retrive all employees from myTest1 table
  41.     ------------------------------------------------
  42.     */
  43.     $sql = 'select * from myTest1 order by empno';
  44.     if ($result = $mysqli->query($sql))
  45.     {
  46.         /*
  47.         ------------------
  48.         prepared statement
  49.         ------------------
  50.         */
  51.         $stmt = $mysqli->prepare('INSERT INTO `myTest2` SET empno = ?, hol   = 8.00, date  = ? ON DUPLICATE KEY UPDATE hol   = 8.00;');        
  52.  
  53.         /* Bind our params */
  54.         $stmt->bind_param('ss', $empNo, $cHoliday);
  55.  
  56.         /*
  57.         ----------------------------
  58.         loop through employee rowset
  59.         ----------------------------
  60.         */
  61.         while ($c = $result->fetch_assoc()) {
  62.  
  63.             /* Set params */
  64.             $empNo     = $c["empno"];
  65.             $date      = $cHoliday;
  66.  
  67.             /*
  68.             ------------------------------
  69.             add holiday for this employee
  70.             ------------------------------
  71.             */
  72.             $stmt->execute();
  73.  
  74.              $transType = $mysqli->affected_rows;
  75.             switch ($transType)
  76.             {
  77.                 case 1:
  78.                     $cAdd++;
  79.                     break;
  80.                 case 2:
  81.                     $cEdit++;
  82.                     break;
  83.                 default:
  84.                     $cErr++;
  85.                     $fErr.=' '.$c["EMPNO"];
  86.             }
  87.  
  88.         } /* end of employee file loop */
  89.  
  90.     }else{
  91.         throw new Exception("query to open myTest1 file failed!");
  92.     }
  93.  
  94.  
  95.     echo 'Records Processed<br>added:'.$cAdd.',<br>edited:'.$cEdit.',<br>errors:'.$cErr.',<br>employees not processed: '.(($fErr=='')?' none':$fErr);
  96.  
  97.     $sql = 'select * from myTest2 order by empno';
  98.     if ($result = $mysqli->query($sql))
  99.     {
  100.         while ($c = $result->fetch_assoc()) {
  101.             echo '<br>'.$c['empno'].' holiday hours = '.$c['hol'];
  102.         }
  103.     }
  104. } /* end try */
  105. catch (exception $e) 
  106. {
  107.     echo 'An error occured : ' .$e->getMessage();
  108. }
  109. $mysqli->close();
  110.  
  111. ?>

15 2539
Claus Mygind
571 512MB
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?
Expand|Select|Wrap|Line Numbers
  1. <?php
  2. try  
  3. {
  4.     /*open connection to database*/
  5.     include("c:\<path>\<tomyconnection>.php");
  6.  
  7.     /*
  8.     ------------------------------------------------
  9.     submitted data is stored in $oCGI assoc array
  10.     ------------------------------------------------
  11.     */
  12.  
  13.     // setup variables
  14.     $webUser  = ((isset($_SERVER["REDIRECT_REMOTE_USER"])) ?$_SERVER["REDIRECT_REMOTE_USER"] :"test");
  15.     $webDate  = date ('Ymd');
  16.     $cHoliday = date ("Ymd", strtotime($_REQUEST["Holiday"]));
  17.  
  18.  
  19.     $cAdd    = 0;
  20.     $cEdit   = 0;
  21.     $fAdd    = '';
  22.     $fEdit   = '';
  23.     /*
  24.     ------------------------------------------------
  25.     retrive all recs from table
  26.     ------------------------------------------------
  27.     */
  28.     $sql = 'select * from <table> order by myKey';
  29.     if ($result = $db->query($sql))
  30.     {
  31.  
  32.         $emp = mysqli_fetch_all( $result , $resulttype = MYSQLI_ASSOC );
  33.         $recCnt = $result->num_rows;
  34.  
  35.         /*
  36.         ----------------------
  37.         prepared add statement
  38.         ----------------------
  39.         */
  40.         $addStmt = $db->prepare('insert into `time` set    `keyCol`=?,`col1`=?,`DATE`=?,`LAST`=?,`FIRST`=?,`LOCATION`=?,`HOL`=8.00,`WEBUSER`="'.$webUser.'",`WEBDATE`='.$webDate.',`WEBADDEDIT` ="Add"');   
  41.  
  42.         /* Bind our params */
  43.         $addStmt->bind_param('ssisss',$keyCol, $col1, $date, $last, $first, $location);
  44.  
  45.         /*
  46.         -------------------------
  47.         prepared update statement
  48.         -------------------------
  49.         */
  50.         $updStmt = $db->prepare('update `time` set HOL=8.00,WEBUSER="'.$webUser.'",WEBDATE='.$webDate.',WEBADDEDIT ="Edit" where keyCol = ?');
  51.  
  52.         /* Bind our params */
  53.         $updStmt->bind_param('s',$keyCol);
  54.  
  55.         /*
  56.         -------------------------
  57.         prepared lookup statement
  58.         -------------------------
  59.         */
  60.         $lkuStmt = $db->prepare("select keyCol from `time` where keyCol = ?");
  61.  
  62.         /* Bind our params */
  63.         $lkuStmt->bind_param('s',$keyCol);
  64.  
  65.         /*
  66.         ----------------------------
  67.         loop through employee rowset
  68.         ----------------------------
  69.         */
  70.         for ($n=0; $n < $recCnt; $n++) 
  71.         {
  72.             $c = $emp[$n];
  73.  
  74.             /* Set up params */
  75.             $keyCol = $c["col1"].$cHoliday;
  76.             $col1     = $c["col1"];
  77.             $date      = $cHoliday;
  78.             $last      = $c["LAST"];
  79.             $first     = $c["FIRST"];
  80.             $location  = $c["LOCATION"];
  81.  
  82.             /*
  83.             -------------------------------
  84.             check if this employee 
  85.             has time entered for this date
  86.             -------------------------------
  87.             */
  88.             if ($lkuStmt->execute()) 
  89.             {
  90.                 /* bind result variables */
  91.                 $lkuStmt->bind_result($wasFound);
  92.  
  93.                 /* fetch value */
  94.                 $lkuStmt->fetch();
  95.  
  96.                 if ($wasFound != $keyCol)
  97.                 {
  98.                     /*
  99.                     ------------------------------
  100.                     add holiday for this employee
  101.                     ------------------------------
  102.                     */
  103.                     $resultY = $addStmt->execute();
  104.                     if ($resultY == false ) 
  105.                     {
  106.                         $fAdd.='~'.$c["col1"];
  107.                     }else{
  108.                         $cAdd++;
  109.                     }
  110.                 }else{
  111.                 /*
  112.                     ------------------------------------------
  113.                     update time for this employee for this day
  114.                     ------------------------------------------
  115.                     */
  116.                     $sql = 'update time set HOL=8.00, WEBUSER="'.$webUser.'", WEBDATE='.$webDate.', WEBADDEDIT ="Edit"  where keyCol = "'.$keyCol.'"';
  117.  
  118. //                    $resultX = $updStmt->execute();
  119. //                    if ($resultX == false ) 
  120.                     if($resultX = $db->query($sql))
  121.                     {
  122.                         $cEdit++;
  123.                     }else{
  124.                         $fEdit.='~'.$c["col1"];
  125.                     }
  126.                 } /* end if add or update */
  127.             }else{
  128.                 throw new Exception("Lookup query for ".$c["col1"]." failed!");
  129.             }/* end if lookup worked*/
  130.         } /* end of employee file loop */
  131.  
  132.     }else{
  133.         throw new Exception("query to open <myLookUpTable> file failed!");
  134.     }
  135.  
  136.     $response = '{"added":'.$cAdd.', failedAdds'.$fAdd.',"edited":'.$cEdit.', failedEdits'.$fEdit.'}';
  137.  
  138. } /* end try */
  139. catch (exception $e) 
  140. {
  141. //if you want to find what line threw exception uncomment next line
  142. $firephp->log($e, 'e');
  143.     $response = 'An error occured : ' .$e->getMessage();
  144. }
  145. echo json_encode ($response);
  146. $db->close();
  147.  
  148. ?>
Sep 19 '14 #2
Dormilich
8,658 Expert Mod 8TB
what do the MySQLi error handlers say?

(e.g. http://php.net/manual/en/mysqli-stmt.error-list.php)
Sep 20 '14 #3
Claus Mygind
571 512MB
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::$error_list

Expand|Select|Wrap|Line Numbers
  1.                     $resultX = $updStmt->execute();
  2.                     if ($resultX == false ) 
  3.                     {
  4.                         $cEdit++;
  5.  
  6.                     }else{
  7.                         $fEdit.='~'.$c["EMPNO"];
  8.                     }
  9. $firephp->log($updStmt->error_list, '$error_list');
  10.  
and likewise for mysqli_stmt_error_list()
Call to undefined function mysqli_stmt_error_list()

where I inserted it like this
Expand|Select|Wrap|Line Numbers
  1.                     $resultX = $updStmt->execute();
  2.                     if ($resultX == false ) 
  3.                     {
  4.                         $cEdit++;
  5.  
  6.                     }else{
  7.                         $fEdit.='~'.$c["EMPNO"];
  8.                     }
  9. echo "Error:\n";
  10.     print_r(mysqli_stmt_error_list($updStmt));
  11.  
I will see if I can put together a demo app to recreate the problem with sample data which I can post.
Sep 22 '14 #4
Claus Mygind
571 512MB
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.
Sep 22 '14 #5
Dormilich
8,658 Expert Mod 8TB
then it should work with $updStmt->error.
Sep 22 '14 #6
Dormilich
8,658 Expert Mod 8TB
you could also try
Expand|Select|Wrap|Line Numbers
  1. $driver = new mysqli_driver();
  2. $driver->report_mode = MYSQLI_REPORT_STRICT;
Sep 22 '14 #7
Claus Mygind
571 512MB
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.

Expand|Select|Wrap|Line Numbers
  1. <?php
  2. try  // Error trap entire applet.; 
  3. {
  4.     /*open connection to database*/
  5.     //**********SET UP YOUR DATABASE CONNECTION**************************
  6.     $mysqli = new mysqli("localhost", "my_user", "my_password", "world");
  7.  
  8.     $mysqli->query("CREATE TABLE myTest1 (`empno` VARCHAR(4) not null default' ', PRIMARY KEY(`empno`) ) ENGINE=MyISAM");
  9.  
  10.     $mysqli->query("INSERT INTO myTest1 set empno='0139'");
  11.     $mysqli->query("INSERT INTO myTest1 set empno='0173'");
  12.     $mysqli->query("INSERT INTO myTest1 set empno='0189'");
  13.     $mysqli->query("INSERT INTO myTest1 set empno='0194'");
  14.     $mysqli->query("INSERT INTO myTest1 set empno='0237'");
  15.     $mysqli->query("INSERT INTO myTest1 set empno='0362'");
  16.     $mysqli->query("INSERT INTO myTest1 set empno='0447'");
  17.     $mysqli->query("INSERT INTO myTest1 set empno='0521'");
  18.     $mysqli->query("INSERT INTO myTest1 set empno='0523'");
  19.  
  20.     $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");
  21.  
  22.     $mysqli->query("INSERT INTO myTest2 set empno='0139', hol=0.00, date=20140917");
  23.     $mysqli->query("INSERT INTO myTest2 set empno='0173', hol=0.00, date=20140917");
  24.     $mysqli->query("INSERT INTO myTest2 set empno='0189', hol=0.00, date=20140917");
  25.     $mysqli->query("INSERT INTO myTest2 set empno='0194', hol=0.00, date=20140917");
  26.     $mysqli->query("INSERT INTO myTest2 set empno='0237', hol=0.00, date=20140917");
  27.     $mysqli->query("INSERT INTO myTest2 set empno='0362', hol=0.00, date=20140917");
  28.     $mysqli->query("INSERT INTO myTest2 set empno='0447', hol=0.00, date=20140917");
  29. //    $mysqli->query("INSERT INTO myTest2 set empno='0521', hol=0.00, date=20140917");
  30. //    $mysqli->query("INSERT INTO myTest2 set empno='0523', hol=0.00, date=20140917");
  31.  
  32.     $cHoliday = date ("Ymd", strtotime("2014-09-17"));
  33.  
  34.  
  35.     $cAdd    = 0;
  36.     $cEdit   = 0;
  37.     $fAdd    = '';
  38.     $fEdit   = '';
  39.     /*
  40.     ------------------------------------------------
  41.     retrive all employees from myTest1 table
  42.     ------------------------------------------------
  43.     */
  44.     $sql = 'select * from myTest1 order by empno';
  45.     if ($result = $mysqli->query($sql))
  46.     {
  47.  
  48.         $emp = mysqli_fetch_all( $result , $resulttype = MYSQLI_ASSOC );
  49.         $recCnt = $result->num_rows;
  50.  
  51.         /*
  52.         ----------------------
  53.         prepared add statement
  54.         ----------------------
  55.         */
  56.         $addStmt = $mysqli->prepare('insert into `myTest2` set `empno`=?,`hol`=8.00,`date`=?');
  57.  
  58.         /* Bind our params */
  59.         $addStmt->bind_param('ss', $empNo, $cHoliday);
  60.  
  61.         /*
  62.         -------------------------
  63.         prepared update statement
  64.         -------------------------
  65.         */
  66.         $updStmt = $mysqli->prepare('update `myTest2` set hol=8.00 where empno = ?');
  67.  
  68.         /* Bind our params */
  69.         $updStmt->bind_param('s',$empno);
  70.  
  71.         /*
  72.         -------------------------
  73.         prepared lookup statement
  74.         -------------------------
  75.         */
  76.         $lkuStmt = $mysqli->prepare("select empno from `myTest2` where empno = ?");
  77.  
  78.         /* Bind our params */
  79.         $lkuStmt->bind_param('s',$empno);
  80.  
  81.         /*
  82.         ----------------------------
  83.         loop through employee rowset
  84.         ----------------------------
  85.         */
  86.         for ($n=0; $n < $recCnt; $n++) 
  87.         {
  88.             $c = $emp[$n];
  89.  
  90.             /* Set params */
  91.             $empNo     = $c["empno"];
  92.             $date      = $cHoliday;
  93.  
  94.             /*
  95.             -------------------------------
  96.             check if this employee 
  97.             has time entered for this date
  98.             -------------------------------
  99.             */
  100.             if ($lkuStmt->execute()) 
  101.             {
  102.                 /* bind result variables */
  103.                 $lkuStmt->bind_result($wasFound);
  104.  
  105.                 /* fetch value */
  106.                 $lkuStmt->fetch();
  107.  
  108.                 if ($wasFound != $empno)
  109.                 {
  110.                     /*
  111.                     ------------------------------
  112.                     add holiday for this employee
  113.                     ------------------------------
  114.                     */
  115.                     $resultY = $addStmt->execute();
  116.                     if ($resultY == false ) 
  117.                     {
  118.                         /*inserting record failed*/
  119.                         $fAdd.='~'.$c["empno"];
  120.                     }else{
  121.                         $cAdd++;
  122.                     }
  123.                 }else{
  124.                 /*
  125.                     -----------------------------
  126.                     update time for this employee
  127.                     -----------------------------
  128.                     */
  129.                     $resultX = $updStmt->execute();
  130.                     if ($resultX == false ) 
  131.                     {
  132.                         /*udating record failed*/
  133.                         $fEdit.='~'.$c["empno"];
  134.  
  135.                     }else{
  136.                         $cEdit++;
  137.                     }
  138.                 } /* end if insert or update */
  139.             }else{
  140.                 throw new Exception("Lookup query for ".$c["empno"]." failed!");
  141.             }/* end if lookup worked*/
  142.         } /* end of employee file loop */
  143.  
  144.     }else{
  145.         throw new Exception("query to open myTest1 file failed!");
  146.     }
  147.  
  148.     echo 'added='.$cAdd.', failedAdds ='.$fAdd.', edited='.$cEdit.', failedEdits='.$fEdit;
  149.  
  150.     $sql = 'select * from myTest2 order by empno';
  151.     if ($result = $mysqli->query($sql))
  152.     {
  153.         while ($c = $result->fetch_assoc()) {
  154.             echo '<br>'.$c['empno'].' holiday hours = '.$c['hol'];
  155.         }
  156.     }
  157.  
  158.  
  159. } /* end try */
  160. catch (exception $e) 
  161. {
  162.     echo 'An error occured : ' .$e->getMessage();
  163. }
  164. $mysqli->close();
  165.  
  166. ?>
  167.  
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
Sep 22 '14 #8
Claus Mygind
571 512MB
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
Expand|Select|Wrap|Line Numbers
  1. try  // Error trap entire applet.; 
  2. {
  3.  
  4.  
  5.     $driver = new mysqli_driver();
  6.     $driver->report_mode = MYSQLI_REPORT_STRICT;
  7.  
  8. ......... other code see previous post ..........
  9.  
  10.  
  11.                     $resultX = $updStmt->execute();
  12. echo $updStmt->error;
  13.                     if ($resultX == false ) 
  14.                     {
  15.                         /*udating record failed*/
  16.                         $fEdit.='~'.$c["empno"];
  17.  
  18.                     }else{
  19.                         $cEdit++;
  20.                     }
  21. ........ more of the code above ......
  22.  
Sep 22 '14 #9
Claus Mygind
571 512MB
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.

Expand|Select|Wrap|Line Numbers
  1. //inserted standard query request like this
  2. $lsql = 'select empno from `myTest2` where empno = '.$empno;
  3.     if ($result = $db->query($lsql))
  4.     {
  5. //    if ($lkuStmt->execute()) 
  6. //    {
  7. //        /* bind result variables */
  8. //        $lkuStmt->bind_result($wasFound);
  9. //                
  10. //        /* fetch value */
  11. //        $lkuStmt->fetch();
  12. //made a standard test to see if a record was found
  13.         if ($result->num_rows == 0)
  14.         {
  15.  
  16. //        if ($wasFound != $empNoDate)
  17. //        {
  18.  
  19.  
Sep 22 '14 #10
Dormilich
8,658 Expert Mod 8TB
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
Sep 23 '14 #11
Claus Mygind
571 512MB
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.
Sep 24 '14 #12
Dormilich
8,658 Expert Mod 8TB
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
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO
  2.     `myTest2`
  3. SET
  4.     empno = ?,
  5.     hol   = 8.00,
  6.     date  = ?
  7. ON DUPLICATE KEY UPDATE
  8.     hol   = 8.00
  9. ;
  10.  
Sep 24 '14 #13
Claus Mygind
571 512MB
Ok, thanks for taking the blinders off my eyes. I'm going to give that a shot.
Sep 25 '14 #14
Claus Mygind
571 512MB
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($var);, 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.
Expand|Select|Wrap|Line Numbers
  1. <?php
  2. try  // Error trap entire applet.; 
  3. {
  4.     /*open connection to database*/
  5.     //**********SET UP YOUR DATABASE CONNECTION**************************
  6.     $mysqli = new mysqli("localhost", "my_user", "my_password", "world");
  7.  
  8.     $mysqli->query("CREATE TABLE myTest1 (`empno` VARCHAR(4) not null default' ', PRIMARY KEY(`empno`) ) ENGINE=MyISAM");
  9.  
  10.     $mysqli->query("INSERT INTO myTest1 set empno='0139'");
  11.     $mysqli->query("INSERT INTO myTest1 set empno='0173'");
  12.     $mysqli->query("INSERT INTO myTest1 set empno='0189'");
  13.     $mysqli->query("INSERT INTO myTest1 set empno='0194'");
  14.     $mysqli->query("INSERT INTO myTest1 set empno='0237'");
  15.     $mysqli->query("INSERT INTO myTest1 set empno='0362'");
  16.     $mysqli->query("INSERT INTO myTest1 set empno='0447'");
  17.     $mysqli->query("INSERT INTO myTest1 set empno='0521'");
  18.     $mysqli->query("INSERT INTO myTest1 set empno='0523'");
  19.  
  20.     $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");
  21.  
  22.     $mysqli->query("INSERT INTO myTest2 set empno='0139', hol=0.00, date=20140917");
  23.     $mysqli->query("INSERT INTO myTest2 set empno='0173', hol=0.00, date=20140917");
  24.     $mysqli->query("INSERT INTO myTest2 set empno='0189', hol=0.00, date=20140917");
  25.     $mysqli->query("INSERT INTO myTest2 set empno='0194', hol=0.00, date=20140917");
  26.     $mysqli->query("INSERT INTO myTest2 set empno='0237', hol=0.00, date=20140917");
  27.     $mysqli->query("INSERT INTO myTest2 set empno='0362', hol=0.00, date=20140917");
  28.     $mysqli->query("INSERT INTO myTest2 set empno='0447', hol=0.00, date=20140917");
  29. //    $mysqli->query("INSERT INTO myTest2 set empno='0521', hol=0.00, date=20140917");
  30. //    $mysqli->query("INSERT INTO myTest2 set empno='0523', hol=0.00, date=20140917");
  31.  
  32.     $cHoliday = date ("Ymd", strtotime("2014-09-17"));
  33.  
  34.  
  35.     $cAdd    = 0;
  36.     $cEdit   = 0;
  37.     $fAdd    = '';
  38.     $fEdit   = '';
  39.     /*
  40.     ------------------------------------------------
  41.     retrive all employees from myTest1 table
  42.     ------------------------------------------------
  43.     */
  44.     $sql = 'select * from myTest1 order by empno';
  45.     if ($result = $mysqli->query($sql))
  46.     {
  47.  
  48.         $emp = mysqli_fetch_all( $result , $resulttype = MYSQLI_ASSOC );
  49.         $recCnt = $result->num_rows;
  50.  
  51.         /*
  52.         ----------------------
  53.         prepared add statement
  54.         ----------------------
  55.         */
  56.         $addStmt = $mysqli->prepare('INSERT INTO `myTest2` SET empno = ?, hol   = 8.00, date  = ? ON DUPLICATE KEY UPDATE hol   = 8.00;');        
  57.  
  58.         /* Bind our params */
  59.         $addStmt->bind_param('ss', $empNo, $cHoliday);
  60.  
  61.         /*
  62.         ----------------------------
  63.         loop through employee rowset
  64.         ----------------------------
  65.         */
  66.         for ($n=0; $n < $recCnt; $n++) 
  67.         {
  68.             $c = $emp[$n];
  69.  
  70.             /* Set params */
  71.             $empNo     = $c["empno"];
  72.             $date      = $cHoliday;
  73.  
  74.             /*
  75.             ------------------------------
  76.             add holiday for this employee
  77.             ------------------------------
  78.             */
  79.             $resultY = $addStmt->execute();
  80.             if ($resultY == false ) 
  81.             {
  82.                 /*inserting record failed*/
  83.                 $fAdd.='~'.$c["empno"];
  84.             }else{
  85.                 $cAdd++;
  86.             }
  87.         } /* end of employee file loop */
  88.  
  89.     }else{
  90.         throw new Exception("query to open myTest1 file failed!");
  91.     }
  92.  
  93.     echo 'added='.$cAdd.', failedAdds ='.$fAdd.', edited='.$cEdit.', failedEdits='.$fEdit;
  94.  
  95.     $sql = 'select * from myTest2 order by empno';
  96.     if ($result = $mysqli->query($sql))
  97.     {
  98.         while ($c = $result->fetch_assoc()) {
  99.             echo '<br>'.$c['empno'].' holiday hours = '.$c['hol'];
  100.         }
  101.     }
  102. } /* end try */
  103. catch (exception $e) 
  104. {
  105.     echo 'An error occured : ' .$e->getMessage();
  106. }
  107. $mysqli->close();
  108.  
  109. ?>
  110.  
  111.  
Sep 26 '14 #15
Claus Mygind
571 512MB
Below is the sample code that with the help of Dormilich has one prepared statement which
1) checks if a record exists
2) INSERTS if not found or
3) UPDATES if found
4) counts the number of INSERTS, UPDATES and errors.
While I could not find out how to use mysqli_info(); I did find I could use $mysqli->affected_rows; to determine if a record was INSERTED, UPDATED or not processed at all (see lines (74 - 86 in code below). Oddly enough affected_rows returns on INSERT... ON DUPLICATE KEY... a 1 for INSERTS, a 2 for UPDATES and I would assume a 0 for not processed. The affected_rows can be executed right after the prepared statement, to capture the result of the transaction (see switch statement below)



Expand|Select|Wrap|Line Numbers
  1. <?php
  2. try  // Error trap entire applet.; 
  3. {
  4.     /*open connection to database*/
  5.     //**********SET UP YOUR DATABASE CONNECTION**************************
  6.     $mysqli = new mysqli("localhost", "my_user", "my_password", "world");
  7.  
  8.     $mysqli->query("CREATE TABLE myTest1 (`empno` VARCHAR(4) not null default' ', PRIMARY KEY(`empno`) ) ENGINE=MyISAM");
  9.  
  10.     $mysqli->query("INSERT INTO myTest1 set empno='0139'");
  11.     $mysqli->query("INSERT INTO myTest1 set empno='0173'");
  12.     $mysqli->query("INSERT INTO myTest1 set empno='0189'");
  13.     $mysqli->query("INSERT INTO myTest1 set empno='0194'");
  14.     $mysqli->query("INSERT INTO myTest1 set empno='0237'");
  15.     $mysqli->query("INSERT INTO myTest1 set empno='0362'");
  16.     $mysqli->query("INSERT INTO myTest1 set empno='0447'");
  17.     $mysqli->query("INSERT INTO myTest1 set empno='0521'");
  18.     $mysqli->query("INSERT INTO myTest1 set empno='0523'");
  19.  
  20.     $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");
  21.  
  22.     $mysqli->query("INSERT INTO myTest2 set empno='0139', hol=0.00, date=20140917");
  23.     $mysqli->query("INSERT INTO myTest2 set empno='0173', hol=0.00, date=20140917");
  24.     $mysqli->query("INSERT INTO myTest2 set empno='0189', hol=0.00, date=20140917");
  25.     $mysqli->query("INSERT INTO myTest2 set empno='0194', hol=0.00, date=20140917");
  26.     $mysqli->query("INSERT INTO myTest2 set empno='0237', hol=0.00, date=20140917");
  27.     $mysqli->query("INSERT INTO myTest2 set empno='0362', hol=0.00, date=20140917");
  28.     $mysqli->query("INSERT INTO myTest2 set empno='0447', hol=0.00, date=20140917");
  29. //    $mysqli->query("INSERT INTO myTest2 set empno='0521', hol=0.00, date=20140917");
  30. //    $mysqli->query("INSERT INTO myTest2 set empno='0523', hol=0.00, date=20140917");
  31.  
  32.     $cHoliday = date ("Ymd", strtotime("2014-09-17"));
  33.  
  34.     $cAdd  = 0;
  35.     $cEdit = 0;
  36.     $cErr  = 0;
  37.     $fErr  = '';
  38.     /*
  39.     ------------------------------------------------
  40.     retrive all employees from myTest1 table
  41.     ------------------------------------------------
  42.     */
  43.     $sql = 'select * from myTest1 order by empno';
  44.     if ($result = $mysqli->query($sql))
  45.     {
  46.         /*
  47.         ------------------
  48.         prepared statement
  49.         ------------------
  50.         */
  51.         $stmt = $mysqli->prepare('INSERT INTO `myTest2` SET empno = ?, hol   = 8.00, date  = ? ON DUPLICATE KEY UPDATE hol   = 8.00;');        
  52.  
  53.         /* Bind our params */
  54.         $stmt->bind_param('ss', $empNo, $cHoliday);
  55.  
  56.         /*
  57.         ----------------------------
  58.         loop through employee rowset
  59.         ----------------------------
  60.         */
  61.         while ($c = $result->fetch_assoc()) {
  62.  
  63.             /* Set params */
  64.             $empNo     = $c["empno"];
  65.             $date      = $cHoliday;
  66.  
  67.             /*
  68.             ------------------------------
  69.             add holiday for this employee
  70.             ------------------------------
  71.             */
  72.             $stmt->execute();
  73.  
  74.              $transType = $mysqli->affected_rows;
  75.             switch ($transType)
  76.             {
  77.                 case 1:
  78.                     $cAdd++;
  79.                     break;
  80.                 case 2:
  81.                     $cEdit++;
  82.                     break;
  83.                 default:
  84.                     $cErr++;
  85.                     $fErr.=' '.$c["EMPNO"];
  86.             }
  87.  
  88.         } /* end of employee file loop */
  89.  
  90.     }else{
  91.         throw new Exception("query to open myTest1 file failed!");
  92.     }
  93.  
  94.  
  95.     echo 'Records Processed<br>added:'.$cAdd.',<br>edited:'.$cEdit.',<br>errors:'.$cErr.',<br>employees not processed: '.(($fErr=='')?' none':$fErr);
  96.  
  97.     $sql = 'select * from myTest2 order by empno';
  98.     if ($result = $mysqli->query($sql))
  99.     {
  100.         while ($c = $result->fetch_assoc()) {
  101.             echo '<br>'.$c['empno'].' holiday hours = '.$c['hol'];
  102.         }
  103.     }
  104. } /* end try */
  105. catch (exception $e) 
  106. {
  107.     echo 'An error occured : ' .$e->getMessage();
  108. }
  109. $mysqli->close();
  110.  
  111. ?>
Sep 26 '14 #16

Sign in to post your reply or Sign up for a free account.

Similar topics

3
table not updated
by: xarrisx | last post by:
I am using an Insert command in my asp code that Inserts a rows in my database. After the Insert command i create a recordset and ask for all the rows in the table that i aplied the insert command...
ASP / Active Server Pages
28
Emulating a table layout purely using CSS
by: Anthony Williams | last post by:
Good morning, I'm currently designing a site, using CSS, and wish to create a variable width two-column layout, with header and footer, and one fixed-width column on the left. Previously, I...
HTML / CSS
3
score object property values against a decision rule created from from database table record values?
by: hazz | last post by:
if i have a table record called Rule 1 with 1. table name 2. table column name 3. operator (>,< =) 4. value (0 to n) 5. score how could I create code to create a rule automatically so...
Visual Basic .NET
3
Dataset update error (record requires parent table record)
by: PAUL | last post by:
Hello, I have 2 datasets I am trying to update. The parent table seems to update fine but when I go update the chiled table I get an error message that says I need a related record in the parent...
Visual Basic .NET
0
Record DVD using VB.NET
by: Renato Tasso | last post by:
Please, everyone can help me ??? I wanna record DVD using visual basic.net language. How can i do ??? Thanks -- Renato Tasso
Visual Basic .NET
2
find no. of rows in data table inside dataset using c#
by: gurusshetty | last post by:
Hi Please help me in finding out no. of rows in data table inside dataset using c# thanks guru
.NET Framework
5
harshadd
Update a foxpro table (.dbf file) using client side coding in DHTML?
by: harshadd | last post by:
can some one explain me, how to update foxpro table (.dbf file) using clien side coading in DHTML or ay client side code like java?
Javascript
5
Creating Table in Mysqldatabase Using PHP coding
by: Shalini Bhalla | last post by:
I am trying to create a table in mysql using php , but the problem is that no. of fields and their datatype is taken from user in input form in am array .so , i want to know that how should i create...
PHP
1
Cloud Servers without Credit Card and Email Registration: A Simpler Way to Get on the Cloud
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
General
0
Wordpress or something else?
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
Content Management Systems
0
One-click Importing Excel Data into a*Database
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
Microsoft Excel
0
Easy Steps to Fix "Canon Printer Won't Connect to WiFi Network"
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
General
0
How to turn on java script in a villaon keypad mobile phone
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
Java
0
Basic Javascript concepts
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
Javascript
0
Merging data from multiple Excel files
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
Data Management
0
Migrating Website to Cloud - Emmanuel Katto
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
General
1
Looking to do Android software development, any suggestions? Is flutter better?
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
General

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.

BYTES.COM © 2024
About Bytes
Terms Of Use
Privacy Policy
Sitemap
Advertise on Bytes:
Post a Job
Sponsored Posts
Platinum & Gold Sponsors
Hire Now!