Insert date into database

[nabilabolo]

Hi,
I tried to insert date into database.
The date is based on the status. If the status is “COMPLETE” then it will insert today’s date while if the status is other than complete, the date is null.

The problem is the date become default date instead of null and current date.

$today = date(“Y-m-d”);
for($row = 1; $row <= $highestRow; ++$row) {

	if($row != 1)
	{
		
		$order= $sheet->getCell('A'.$row)->getValue();
		$name= $sheet->getCell('D'.$row)->getValue();
		$age= $sheet->getCell('E'.$row)->getValue(); 
		$status= $sheet->getCell('S'.$row)->getValue();
		
		
		 
		if($status== "Completed")
		{
			$date = $today;
		}
		else
		{
			$date = null;
		}
		
		$checksql = "SELECT * FROM Customer where order_no = ? ";
		$checkparam = array($st);
		$chk = sqlsrv_query( $conn, $checksql, $checkparam);
		$chkrow = sqlsrv_fetch_array($chk);
				
			
			
			if($chkrow == null)
			{
				$sql = "INSERT INTO Customer (order_no, name, age, status, CompleteDate) VALUES (?,,?,?,?,'$date')";
				$params = array($order, $name, $age, $status);
				$stmt = sqlsrv_query( $conn, $sql, $params);
				if( $stmt === false ) {
					echo '<pre>';die( print_r( sqlsrv_errors(), true));echo '</pre>';
				} 
				echo "Record inserted - ".$order."<br>";
			}
			else
			{
				$sql2 = "UPDATE Customer SET name = ?, status= ? where order = ?";
				$params_val = array($name, $status,$order);
					$stmt_val = sqlsrv_query( $conn, $sql2, $params_val);
					if( $stmt_val === false ) {
						echo '<pre>';die( print_r( sqlsrv_errors(), true));echo '</pre>';
					}
					echo "Record updated - ".$order."<br>";
			}
	}
	else
	{
		$row++;	
	}	 			

}

[frankbeen]

• Are you sure that the column CompleteDate may be empty (or NULL)?
• The problem are the single quotes around $date. NULL is something different then ‘NULL’

		if($status== "Completed")
		{
			$sql = "INSERT INTO Customer (order_no, name, age, status, CompleteDate) VALUES (?,?,?,?,'$date')";
		}
		else
		{
			$sql = "INSERT INTO Customer (order_no, name, age, status, CompleteDate) VALUES (?,?,?,?, NULL)";
		}

But you can also choose to leave the whole CompleteDate column out of the query when it must be NULL:

INSERT INTO Customer (order_no, name, age, status) VALUES (?,?,?,?)

[frankbeen]

Another point is that you are not escaping the variables before you deliver them to the database. It makes SQL Injection easy and your database could be screwed up because of this security risk. At least use the mysqli_real_escape_string() function to escape those variables or even better use PDO with prepared statements. This will solve the issue with the quotes too!