Member Avatar for daver40

Hi! I'm a bit of a beginner at PHP, and I'm having trouble comparing two strings in a conditional statement. Both of the variables are strings, but one of them them is assigned from a MSSQL database. In this database, it of the unicode varchar data type. It doesn't seem like this should make a difference, but for some reason, whenever I try to compare the two dates, I get a false return value, even when the dates are equivalent.

Here's my code:

//Set database access variables

	$host ='mssql.library.uiuc.edu';
	$user = 'BEL_webuser';
	$pass ='NeUn72L';
	$db =  'BEL';
	
	//Open connection
	
	$connection = mssql_connect($host, $user, $pass) or die("Unable to connect");
	
	//Select database
	
	mssql_select_db($db) or die("Unable to select database");
	
	// create query
	
    //$query = "SELECT computer, time, netid FROM bloomberg WHERE(date=N'$date') AND (computer=N'$comp')";
	
	$query = "SELECT * FROM bloomberg";
	
	// execute query
    $result = mssql_query($query) or die ("Error in query");
	$date1 = '11-05-2007';
	
		for($i = 0; $i < mssql_num_rows($result); $i++){
			
			$row = mssql_fetch_array($result);
			$date2 = $row['date'];
			if($date1 == $date2){
				$dateflag = 1;
			}
			else { 
				$dateflag = 0;
			} 
		echo $dateflag;
		}

Thanks,

David

  • 3 Contributors
  • 3 Replies
  • 137 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by daver40

Dani AI

Generated

Good pointers from and — the issue is rarely PHP string comparison itself and more often hidden differences in the value coming from the database. later noted the column is NVARCHAR, which raises three likely causes: invisible bytes or a BOM from UTF-16, extra whitespace or control characters, or a different date format (and possibly an embedded time).

Quick checks to reveal the problem:

var_dump($row['date']);
echo 'len=' . strlen($row['date']) . "\n";
for ($i = 0; $i < strlen($row['date']); $i++) { echo ord($row['date'][$i]) . ' '; }

If you see zero bytes, a 0xFE/0xFF BOM, or unexpected byte values, that points to encoding issues (NVARCHAR → UTF-16). Trailing spaces or CR/LF will show up in the length and ord output.

Normalization and a robust compare:

  • Strip control bytes and trim.
  • Convert encoding if the string is UTF-16.
  • Parse to a DateTime and compare canonical formats.

Example approach:

$date2 = @iconv('UTF-16LE', 'UTF-8//IGNORE', $row['date']); // if needed
$date2 = preg_replace('/[\x00-\x1F\x7F]/', '', $date2);
$date2 = trim($date2);

$d1 = DateTime::createFromFormat('m-d-Y', $date1);
$d2 = DateTime::createFromFormat('m-d-Y', $date2);
if ($d1 && $d2 && $d1->format('Y-m-d') === $d2->format('Y-m-d')) { /* match */ }

See PHP docs for DateTime::createFromFormat and iconv for details: DateTime::createFromFormat, iconv.

If possible, fix it at the source: store dates in a DATE/DATETIME column or have SQL return a canonical text form (for example, use CONVERT with an unambiguous style like yyyy-mm-dd). The SQL CONVERT/Cast documentation shows available styles: CAST and CONVERT (Transact-SQL).

Recommended Answers

All 3 Replies

Member Avatar for JeniF

Hi! I'm a bit of a beginner at PHP, and I'm having trouble comparing two strings in a conditional statement. Both of the variables are strings, but one of them them is assigned from a MSSQL database. In this database, it of the unicode varchar data type. It doesn't seem like this should make a difference, but for some reason, whenever I try to compare the two dates, I get a false return value, even when the dates are equivalent.

Here's my code:

//Set database access variables
 
    $host ='mssql.library.uiuc.edu';
    $user = 'BEL_webuser';
    $pass ='NeUn72L';
    $db =  'BEL';
 
    //Open connection
 
    $connection = mssql_connect($host, $user, $pass) or die("Unable to connect");
 
    //Select database
 
    mssql_select_db($db) or die("Unable to select database");
 
    // create query
 
    //$query = "SELECT computer, time, netid FROM bloomberg WHERE(date=N'$date') AND (computer=N'$comp')";
 
    $query = "SELECT * FROM bloomberg";
 
    // execute query
    $result = mssql_query($query) or die ("Error in query");
    $date1 = '11-05-2007';
 
        for($i = 0; $i < mssql_num_rows($result); $i++){
 
            $row = mssql_fetch_array($result);
            $date2 = $row['date'];
            if($date1 == $date2){
                $dateflag = 1;
            }
            else { 
                $dateflag = 0;
            } 
        echo $dateflag;
        }

Thanks,

David

SQL Server stores the datetime in a certain manner. The variable $date1 that you have set looks to be in the incorrect format.
Take a look at this to get a bit more information.

Member Avatar for Member #210412

if u wanna compare two dates in that way you will always get "0", coz sql server stores the dates like
dd/mm/yyyy hh:mm:ss ms etc..
u should change your query as between $date1 and $date2 or reduce $date1 n $date2 to dd/mm/yyyy hh/mm then u can compare them..

Member Avatar for daver40

SQL Server stores the datetime in a certain manner. The variable $date1 that you have set looks to be in the incorrect format.
Take a look at this to get a bit more information.

if u wanna compare two dates in that way you will always get "0", coz sql server stores the dates like
dd/mm/yyyy hh:mm:ss ms etc..
u should change your query as between $date1 and $date2 or reduce $date1 n $date2 to dd/mm/yyyy hh/mm then u can compare them..

Thank you both for the help. I've stored my date as a string, though, using the nvarchar data type.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.