0

I am using the function query to insert data to a mysql table from php. The function, call and database structure are show below.

I have two questions: 1) why does the error message say invalid datetime format. The column customCrew is a tinyint.
2) I am able to insert 0 or 1 but how come I can't insert 'FALSE' or FALSE for this column?

function query(/* $sql [, ... ] */){    
   // SQL statement    
   $sql = func_get_arg(0);

   // parameters, if any    
   $parameters = array_slice(func_get_args(), 1);   

   // try to connect to database    
   static $handle;    
   if (!isset($handle)){    
      try{    
         // connect to database    
         $handle = new PDO("mysql:dbname=" . DATABASE . ";host=" . SERVER, USERNAME, PASSWORD);

         // ensure that PDO::prepare returns false when passed invalid SQL    
         $handle->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);    
      }catch (Exception $e){    
         echo 'help';   

         // trigger (big, orange) error    
         trigger_error($e->getMessage(), E_USER_ERROR);    
         exit;    
      }    
   }   

   // prepare SQL statement    
   $statement = $handle->prepare($sql);    
   if ($statement === false){

      // trigger (big, orange) error    
      trigger_error($handle->errorInfo()[2], E_USER_ERROR);      
      exit;    
   }    

   // execute SQL statement    
   $results = $statement->execute($parameters);   

   // return result set's rows, if any    
   if ($results !== false){    
      return $statement->fetchAll(PDO::FETCH_ASSOC);    
   }else{    
      return false;    
   }    
}

here is php statement to use above function:

query("INSERT INTO sails ( type,
                           date,
                           skipperId,
                           boat,
                           class,
                           resStart,
                           resEnd,
                           resTS,
                           spots,
                           customCrew)
                           VALUES(?,?,?,?,?,?,?,?,?,?)",
                           'RES',
                           $_POST['date'],
                           $_POST['skipperId'],
                           $_POST['boat'],
                           "50",
                           date('H:i', strtotime($_POST['start'])),
                           date('H:i', strtotime($_POST['end'])),
                           $response['now'],
                           $_POST['spots'],
                           'FALSE');

structure of table sails:

1   type                varchar(4)  utf8mb4_general_ci      No  None            
2   date Primary        date                                No  None            
3   skipperId Primary   int(11)                             No  None            
4   boat                varchar(20) utf8mb4_general_ci      No  None            
5   class               varchar(4)  utf8mb4_general_ci      No  None            
6   resStart            time                                No  None            
7   resEnd              time                                No  None            
8   resTS               datetime                            No  None            
9   sailStart           time                                Yes NULL            
10  sailEnd             time                                Yes NULL            
11  sailTS              datetime                            Yes NULL            
12  spots               int(11)                             No  None            
13  customCrew          tinyint(1)                          No  None            
14  xclTS               datetime                            Yes NULL

and here's the error message from the console:

<b>Fatal error</b>:  Uncaught PDOException: SQLSTATE[22007]: Invalid datetime format: 1366 
Incorrect integer value: 'FALSE' for column `sandbox`.`sails`.`customCrew` at row 1 in 
/opt/lampp/htdocs/lagin/includes/functions.php:177
Stack trace:
#0 /opt/lampp/htdocs/lagin/includes/functions.php(177): PDOStatement-&gt;execute(Array)
#1 /opt/lampp/htdocs/lagin/public/skipper.php(64): query('INSERT INTO sai...', 'RES', '2021-07-29', 
'1', 'Irlbach', '50', '09:00', '20:30', '2021-07-28 17:4...', '3', 'FALSE')
DCR
  • 14,737
  • 12
  • 52
  • 115
  • 2
    `customCrew` is of type `tinyint` and you try to enter a string. That's clearly wrong. Try to enter a number like 0 or 1. You can also use MySQL constants `TRUE` or `FALSE` which are 0 or 1 respectively, but that can't be done via binding – Dharman Jul 28 '21 at 22:15
  • did you read the question? 'FALSE' and FALSE both don't work. mysql is suppose to convert to 0 or 1 – DCR Jul 28 '21 at 22:17
  • Change `'FALSE'` to `0` and it will work – Dharman Jul 28 '21 at 22:18
  • use BOOLEAN bit the result is also 0 https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=7ce1677e00684e0466c8906e8b5603d5 – nbk Jul 28 '21 at 22:21
  • Also, please enable PDO exception mode instead of this messy error checking. It will save you a lot of trouble. – Dharman Jul 28 '21 at 22:22

0 Answers0