DATE -TIME Question

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

vamsinadella
Forum Newbie
Posts: 15
Joined: Fri Dec 22, 2006 6:05 pm

DATE -TIME Question

Post by vamsinadella »

feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Hi,

I have a datetime field as:

[syntax="html"]
<input type="datetime" name="targetgadate">
and i get the value of this filed (user input) to the variable as:[/syntax]

Code: Select all

$target_ga_date=$_POST['targetgadate'];
The problem is such date columns does not allow the user to leave the field blank. If the field is blank the it throws an error:

"Incorrect datetime value: 'null' for column 'TargetGADateShort' at row 1insert into datahub.targetdetails"

I have to allow nulls into this column.

The weird thing is the database (mysql) allows nulls in the column and there are already a lot of nulls.

Can anyone help me how to get this into shape.

Thanks,

- Vamsi


feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Can you post the query generation code?

I'm going to guess that you're always placing quotes around the value thereby making it a string. Even if you insert null for the value, it will be a string, that not conforming to the format the database expects.
vamsinadella
Forum Newbie
Posts: 15
Joined: Fri Dec 22, 2006 6:05 pm

Post by vamsinadella »

Hey thanks for the response.....andsorry for not using correct tags...

Code: Select all

$sql1 = "insert into datahub.targetdetails".
		"(lastupdate, servermodeltable_id1, codename,  targetesxrelease, targetgadateshort, actualgadateshort, serverformfactortable_id, cputable_id, maxnumbercpus, cpuspeed, stepping, l2cachesize, l3cachesize, frontsidebustable_id, serverchipsettable_id, serverbiostable_id,". 			
		"memoryconfiguration, maxmemorysize, pcislots, pcixslots, pcieslots, satacontroller, sciscontroller, raidcontroller, sascontroller, fchba, iscsihba, expecteddeliverydatetovmware, requiredreturndate, hide, NDA, lvl_1_ecx, lvl_1_edx, lvl_81_ecx, lvl_81_edx)".
		"values('$last_update', '$serv_id', '$serv_code', '$targ_esx_release', '$target_ga_date', '$actual_ga_date', '$form_fac_id', '$cpu_id', '$no_cpu', '$cpu_speed', '$stepping', '$l2_cache', '$l3_cache', '$fsb_id', '$chipset_id', '$bios_id', '$mem_config', '$mem_max', '$pci', ".
		"'$pcix', '$pcie', '$sata', '$sci', '$raid', '$sas', '$fc_hba', '$iscsi', '$to_vmw_delivery_date', '$return_to_partner_date', '2', '1', '$lvl1_ecx', '$lvl1_edx', '$lvl81_ecx', '$lvl81_edx')";
It is only when the user does not enter anything that i have to enter a null else, the user entered date goes as it is.... How do i check this? I mean, i know how(logic wise) but syntax...?

Thanks.

- Vamsi
vamsinadella
Forum Newbie
Posts: 15
Joined: Fri Dec 22, 2006 6:05 pm

Post by vamsinadella »

Hi Feyd,

I tried removing the quotes in the sql statement and also removed them while getting the values into the variables. Like:

Code: Select all

$target_ga_date=$_POST[targetgadate];
$actual_ga_date=$_POST[actualgadate];
instead of

Code: Select all

$target_ga_date=$_POST['targetgadate'];
$actual_ga_date=$_POST['actualgadate'];

But it still is not accepting the query and is giving me an error pointing at the location where i removed the quotes in the sql stmt.

- Vamsi
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

The latter of the above two snippets should always be used.

As for checking whether the user entered something, empty() works well for these occasions.

It would also appear that you are rather blindly accepting user input into your SQL query, so I'll also recommend that you use mysql_real_escape_string() on every single user input that you will be using.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Have you tried setting your vars to null if they are not set to something?

Code: Select all

<?php
$my_var = null;

if (isset($_POST['my_var']))
{
  $my_var = $_POST['my_var'];
}

// Now handle the insert
?>
vamsinadella
Forum Newbie
Posts: 15
Joined: Fri Dec 22, 2006 6:05 pm

Post by vamsinadella »

I have implemented both the suggestions above by Feyd and Everah. Changes - i used this to fetch the variables....

Code: Select all

$target_ga_date=$_POST['targetgadate'];
$actual_ga_date=$_POST['actualgadate'];
Then i set them to null and initiated them with user values when the form is posted....

Code: Select all

$target_ga_date = null;
$actual_ga_date = null;
if ($_SERVER['REQUEST_METHOD'] == "POST")
	{//<<<...code....>>
if(isset($_POST['targetgadate'])){$target_ga_date = $_POST['targetgadate'];}
		if(isset($_POST['actualgadate'])){$actual_ga_date = $_POST['actualgadate'];}

echo "Dates: $target_ga_date \n $actual_ga_date";

}
Also I removed the quotes in the sql insert statement:

Code: Select all

$sql1 = "insert into datahub.targetdetails".
                "(lastupdate, servermodeltable_id1, codename,  targetesxrelease, targetgadateshort, actualgadateshort, serverformfactortable_id, cputable_id, maxnumbercpus, cpuspeed, stepping, l2cachesize, l3cachesize, frontsidebustable_id, serverchipsettable_id, serverbiostable_id,".      
                "memoryconfiguration, maxmemorysize, pcislots, pcixslots, pcieslots, satacontroller, sciscontroller, raidcontroller, sascontroller, fchba, iscsihba, expecteddeliverydatetovmware, requiredreturndate, hide, NDA, lvl_1_ecx, lvl_1_edx, lvl_81_ecx, lvl_81_edx)".
                "values('$last_update', '$serv_id', '$serv_code', '$targ_esx_release', $target_ga_date, $actual_ga_date, '$form_fac_id', '$cpu_id', '$no_cpu', '$cpu_speed', '$stepping', '$l2_cache', '$l3_cache', '$fsb_id', '$chipset_id', '$bios_id', '$mem_config', '$mem_max', '$pci', ".
                "'$pcix', '$pcie', '$sata', '$sci', '$raid', '$sas', '$fc_hba', '$iscsi', $to_vmw_delivery_date, $return_to_partner_date, '2', '1', '$lvl1_ecx', '$lvl1_edx', '$lvl81_ecx', '$lvl81_edx')";
Then i submit the form with NULL values in the date columns.......this error pops up:
Dates: This line should print the dates entered. Since they are null, there is nothing to print
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' , '2', '110', '', '', '', '', '', '19', '83', '49', '', '', '', '', '', '2', '2' at line 1insert into datahub.targetdetails(lastupdate, servermodeltable_id1, codename, targetesxrelease, targetgadateshort, actualgadateshort, serverformfactortable_id, cputable_id, maxnumbercpus, cpuspeed, stepping, l2cachesize, l3cachesize, frontsidebustable_id, serverchipsettable_id, serverbiostable_id,memoryconfiguration, maxmemorysize, pcislots, pcixslots, pcieslots, satacontroller, sciscontroller, raidcontroller, sascontroller, fchba, iscsihba, expecteddeliverydatetovmware, requiredreturndate, hide, NDA, lvl_1_ecx, lvl_1_edx, lvl_81_ecx, lvl_81_edx)values('2007-01-04 11:01:50', '614', '', '1', , , '2', '110', '', '', '', '', '', '19', '83', '49', '', '', '', '', '', '2', '2', '2', '2', '2', '2', , , '2', '1', '', '', '', '')
So the i decide to enter some dates and i enter the values for dates and when i submit the form THIS error pops up:
Dates: 2008-01-01 00:00:00 2008-01-01 00:00:00 2007-01-01 00:00:00 2007-05-01 0:00:00 Now it prints the dates correctly. But....."This error
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '00:00:00, 2008-01-01 00:00:00, '2', '110', '', '', '', '', '', '19', '83', '49',' at line 1insert into datahub.targetdetails(lastupdate, servermodeltable_id1, codename, targetesxrelease, targetgadateshort, actualgadateshort, serverformfactortable_id, cputable_id, maxnumbercpus, cpuspeed, stepping, l2cachesize, l3cachesize, frontsidebustable_id, serverchipsettable_id, serverbiostable_id,memoryconfiguration, maxmemorysize, pcislots, pcixslots, pcieslots, satacontroller, sciscontroller, raidcontroller, sascontroller, fchba, iscsihba, expecteddeliverydatetovmware, requiredreturndate, hide, NDA, lvl_1_ecx, lvl_1_edx, lvl_81_ecx, lvl_81_edx)values('2007-01-04 11:01:55', '614', '', '1', 2008-01-01 00:00:00, 2008-01-01 00:00:00, '2', '110', '', '', '', '', '', '19', '83', '49', '', '', '', '', '', '2', '2', '2', '2', '2', '2', 2007-01-01 00:00:00, 2007-05-01 0:00:00, '2', '1', '', '', '', '')
Is this error because of the space i have between date and time - 2007-01-01 00:00:00? or something else? Actually i'm stuck both ways. Any help?????

Thanks...

- Vamsi
Last edited by vamsinadella on Thu Jan 04, 2007 3:22 pm, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

You're not putting quotes around them when they are dates.

Are you using mysql_real_escape_string() on all the user submitted values?
vamsinadella
Forum Newbie
Posts: 15
Joined: Fri Dec 22, 2006 6:05 pm

Post by vamsinadella »

Sorry feyd but i did not understand what you implied. I should not put quotes where? In the sql statement or in the $_POST statement? Or should i put quotes??? Sorry but i'm confused.

As for mysql_real_escape_string(), i'm implementing it on all the variables.

and can i say:

Code: Select all

mysql_real_escape_string($target_ga_date) = $_POST['targetgadate'];
Thanks,

- Vamsi
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

wrong:

Code: Select all

mysql_real_escape_string($target_ga_date) = $_POST['targetgadate'];
right:

Code: Select all

$target_ga_date = mysql_real_escape_string($_POST['targetgadate']);
And make sure you default your date values to null, then change them after verifying that they are set to something.
vamsinadella
Forum Newbie
Posts: 15
Joined: Fri Dec 22, 2006 6:05 pm

Post by vamsinadella »

Still not able to get it to work.....It is just throwing the same errors....

I strongly feel that there is something wrong with the quotes and the way i'm handling the 'datetime' field, but am not able to figure out what it is exactly.

Any more suggestions?

Thanks,

- vamsi
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

Let's assume each field in your xform/html form has the same name as the field in your db table.
$_POST['lastupdate'] => `lastupdate`, $_POST['sciscontroller'] => `sciscontroller`, ....
no dazzling name mangling like $_POST['targetgadate'] => $target_ga_date => `targetgadateshort` :?:

try

Code: Select all

$fields = array(
		'lastupdate', 'servermodeltable_id1', 'codename', 'targetesxrelease', 'targetgadateshort', 'actualgadateshort', 
		'serverformfactortable_id', 'cputable_id', 'maxnumbercpus', 'cpuspeed', 'stepping', 'l2cachesize', 
		'l3cachesize', 'frontsidebustable_id', 'serverchipsettable_id', 'serverbiostable_id', 'memoryconfiguration', 'maxmemorysize', 
		'pcislots', 'pcixslots', 'pcieslots', 'satacontroller', 'sciscontroller', 'raidcontroller', 
		'sascontroller', 'fchba', 'iscsihba', 'expecteddeliverydatetovmware', 'requiredreturndate', 'hide', 
		'NDA', 'lvl_1_ecx', 'lvl_1_edx', 'lvl_81_ecx', 'lvl_81_edx'
	);
	

$mysql = mysql_connect(...) or die(mysql_error());
mysql_select_db(..., $mysql)  or die(mysql_error());

values = array();
foreach($fields as $f) {
	if ( !isset($_POST[$f]) ) {
		die("_POST[$f] is missing");
	}
	
	$values[] = "'" . mysql_real_escape_string($_POST[$f], $mysql) . "'";
}

$sql_fields = join(',', $fields);
$sql_values = join(',', $values);

$query = "INSERT INTO
		datahub.targetdetails
		($sql_fields)
	VALUES
		($sql_values)";
mysql_query($query, $mysql) or die(mysql_error().': '.$query);
vamsinadella
Forum Newbie
Posts: 15
Joined: Fri Dec 22, 2006 6:05 pm

Post by vamsinadella »

Thanks Volka. That was fantastic. It looks so organized. I like it and will definitely implement it. But, my problem is not with data insert, but with inserting null values (and that too, null values for datetime and integer datatypes).

Your code below checks for data entry and forces the user to enter data. I do not want to do that. They can leave certain fields blank. For those fields, i have to insert nulls. The problem is:

For string variables - No concerns, it is happily inserting a null or ''.
For datetime - it is throwing me errors saying "incorrect datetime value for <<col name>>.
For numerics/integers - it sis very interesting..

In the sql statement ($sql - given above in my questions), if i put a '' around the integer variable, it says - Out of Range value adjusted for the column.

If i do not put a '' then it says - you have an error in the sql syntax and points to the spot where the integer variable is.

So i know very well that it is something i'm doing wrong with the NULLS, QUOTES or $_POST (fetching values). But i'm not able to figure out what it is....

Let me know if you need anything more to help me out. Thanks for the help. Any further suggestions are greatly appreciated.

- Vamsi
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

Even if the user does not typeanything into a input/text the browser will send the (empty) value. Therefore isset($_POST[$f]) would not return false if the user doesn't enter a value but if the form does not contain an element with the name stored in $f. Empty fields would be inserted as '', i.e. empty string. But if you want null try

Code: Select all

<?php
$fields = array(
		'lastupdate','servermodeltable_id1','codename','targetesxrelease','targetgadateshort',
		'actualgadateshort','serverformfactortable_id','cputable_id','maxnumbercpus','cpuspeed',
		'stepping','l2cachesize','l3cachesize','frontsidebustable_id','serverchipsettable_id',
		'serverbiostable_id','memoryconfiguration','maxmemorysize','pcislots','pcixslots',
		'pcieslots','satacontroller','sciscontroller','raidcontroller','sascontroller',
		'fchba','iscsihba','expecteddeliverydatetovmware','requiredreturndate','hide',
		'NDA','lvl_1_ecx','lvl_1_edx','lvl_81_ecx','lvl_81_edx'
	);
$mysql = mysql_connect(...) or die(mysql_error());
mysql_select_db(..., $mysql)  or die(mysql_error());

values = array();
foreach($fields as $f) {
	if ( !isset($_POST[$f]) || 0==strlen($v=trim($_POST[$f]) ) {
		$values = 'null';
	}
	else {
		$values[] = "'" . mysql_real_escape_string($v, $mysql) . "'";
	}
}

$sql_fields = join(',', $fields);
$sql_values = join(',', $values);

$query = "INSERT INTO
		datahub.targetdetails
		($sql_fields)
	VALUES
		($sql_values)";
mysql_query($query, $mysql) or die(mysql_error().': '.$query);
Last edited by volka on Thu Jan 04, 2007 5:56 pm, edited 1 time in total.
vamsinadella
Forum Newbie
Posts: 15
Joined: Fri Dec 22, 2006 6:05 pm

Post by vamsinadella »

DONE......It's working...i guess the way i was inserting nulls was wrong. i played around with the quotes for some time and i got it. Thanks you guys for all the help. This si the first time i'm coding php and you guyz have been great. As they say the reward for good work is more work....So I'll bug you more.... :P

Oh volka, I did not see your final response. Actually that is what i did.

Thanks..

- Vamsi
Last edited by vamsinadella on Thu Jan 04, 2007 5:59 pm, edited 1 time in total.
Post Reply