confusion with data type

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
primate
Forum Commoner
Posts: 49
Joined: Fri Jun 18, 2004 4:38 am
Location: England, UK

confusion with data type

Post 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]
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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..
User avatar
Buddha443556
Forum Regular
Posts: 873
Joined: Fri Mar 19, 2004 1:51 pm

Post by Buddha443556 »

You can really get spoiled by MySQL database. Using integers and unix timestamps may not be a bad idea with MSSQL.
Post Reply