Page 1 of 1

confusion with data type

Posted: Fri Jun 18, 2004 4:38 am
by primate
Hi, I new to php and working with SQL in general and I'm trying to make use of what I've been slowly learning over the past few weeks, so feel free to criticise my inept scripting below.

To put what I am trying to do in context I have several PIX firewalls sending Syslog messages to Kiwi Syslog which I am dropping into an MS SQL 2000 database.

According to SQL server Enterprise manager the data type in all the columns in the database that Kiwi Syslog creates are "varchar".

However when I run a query against the database, I receive the following error - "Syntax error converting the varchar value '2004-04-22' to a column of data type int" so this would indicate that SQL thinks the data is numerical rather than text correct?

Initially I am attempting to pull out data by date values to then work with in php as this seemed the most efficient way of doing it.

Code: Select all

<?php

//error handling.

ini_set ('display_errors', 1);
error_reporting (E_ALL & E_NOTICE);

//pretend that dates submitted by form

$day1 = 22;
$month1 = 4;
$year1 = 2004;
$day2 = 24;
$month2 = 4;
$year2 = 2004;

//convert the date to format generated by Kiwi Syslog

$date1 = "$year1-$month1-$day1";
$date2 = "$year2-$month2-$day2";

print $date1;
print $date2;

if ($sql = @mssql_connect("172.31.100.7","xxx", "xxx")) 
		{

			print '<p>Successfully connected to MSSQL.</p>';
			
		if ($sql=@mssql_select_db('syslog'))
		
			{
				print '<p>Succesfully selected syslog database.</p>';
				
			} else {
						
					print '<p>Failed to select syslog database:' . mssql_get_last_message . '</p>';
						
					}
		
			
		} else {
				
				die ('<p>Could not connect to MSSQL:' . mssql_get_last_message() .'</p>');

}


$query = "SELECT MsgText FROM syslog.PIXStats WHERE (MsgDate = $date1)";

if ($r = mssql_query ($query)) 
{    //check that the query will work and run it

	while ($row=mssql_fetch_array ($r)) 
	
		{
		
		print "{$row['MsgDate']}{$row['MsgText']}<br />";
	
		}
	
	} else { //query didn't run
	
			die ('<p>Could not retrieve the data because: <b>' . mssql_get_last_message() . '</p>');
			
}


mssql_close();
		
?>
feyd|use

Code: Select all

tags please.[/color]

Posted: Fri Jun 18, 2004 4:44 am
by feyd
I'm not going to pretend I know the stored procedures of mssql, so I'll suggest using [php_man]strftime[/php_man]() or [php_man]mktime[/php_man]() to create timestamps (integers) of your dates..

Posted: Fri Jun 18, 2004 8:41 am
by Buddha443556
You can really get spoiled by MySQL database. Using integers and unix timestamps may not be a bad idea with MSSQL.