Keeping NULL default on blank field entries

[kingnutter]

Hi everyone,

 

I am trying to generate a NULL SQL entry on the following page (and a corresponding UPDATE form) if a field is left blank.

 

IF and ELSE or ELSEIF are not working for me, I have tried several permutations.

 

Am I going about this the right way, or should I be assigning values in another part of the code / section?

 

Any help welcome.

 

Cheers

KN

 

This is the query portion of the code following the HTML form and checks:

 

if (sizeof($errorList) == 0)
{
	// open database connection
	$connection = mysql_connect($host, $user, $pass)
or die ('Unable to connect to database: Line 131');

// select database
mysql_select_db($db)
or die ('Unable to connect');

$moj_id = $_POST[id];
$track_no = $_POST[track_no];
$track_title = $_POST[track_title];
$track_artist = $_POST[track_artist];
$track_asinuk = $_POST[track_asinuk];
$track_itune = $_POST[track_itune];
/*	$track_itune2 = $_POST[track_itune2]; */
$track_asinus = $_POST[track_asinus];


if (trim($track_asinuk) != '')
{
$query = "INSERT INTO tracks (track_asinuk) VALUE ('$track_asinuk')";
$result = mysql_query($query)
or die ("Error in Query: $query. " . mysql_error());
}

if (trim($track_asinus) != '')
{
$query = "INSERT INTO tracks (track_asinus) VALUE ('$track_asinus')";
$result = mysql_query($query)
or die ("Error in Query: $query. " . mysql_error());
}	

if (trim($track_itune) != '')
{
$query = "INSERT INTO tracks (track_itune) VALUE ('$track_itune')";
$result = mysql_query($query)
or die ("Error in Query: $query. " . mysql_error());
}

	// generate and execute query
$query = "INSERT INTO
tracks(moj_id, track_no, track_title, track_artist, track_timestamp)
VALUES ('$moj_id', '$track_no', '$track_title', '$track_artist', NOW())";



$result = mysql_query($query)
or die ("Error in Query: $query. " . mysql_error());

// print result
?>



<script language="JavaScript" type="text/JavaScript">

window.location.href = "tracks.php?id=<?php echo "$moj_id"."&happened=added"; ?>";

</script>

<?php





// close database connection
mysql_close($connection);

}
else
{
	// errors found
	// print as list
	echo '<font size=-1>The following errors were encountered:';
	echo '<br>';
	echo '<ul>';
	for ($x=0; $x<sizeof($errorList); $x++)
	{
		echo "<li>$errorList[$x]";
	}
	echo '</ul></font>';
}
}

?>

[ignace]

$id = empty($_POST['id']) ? 'NULL' : $_POST['id'];

[thebadbad]

Note that to insert a 'true' null value into a database field, you must use null without any surrounding quotes, in your query. You can prepare the variables with the below code, resetting them to 'null' (string) if they are empty, or running them through mysql_real_escape_string() and surrounding them with single quotes if they are not:

 

<?php
//connect to database first
foreach ($_POST as $name => $value) {
$_POST[$name] = (trim($value) == '') ? 'null' : "'" . mysql_real_escape_string($value) . "'";
}
?>

 

Then afterwards, you can just use the POST variables in your query like this (without any further checking):

 

<?php
$query = "INSERT INTO tracks (`track_asinus`) VALUE ({$_POST['track_asinus']})";
?>

 

If $_POST['track_asinus'] was empty, null will be inserted to the database.

[ignace]

<?php
//connect to database first
foreach ($_POST as $name => $value) {
$_POST[$name] = (trim($value) == '') ? 'null' : "'" . mysql_real_escape_string($value) . "'";
}
?>

 

connect to database first?

 

Edit: Nevermind, got it. I thought mysql_real_escape_string() could operate independently of a db connection.

[thebadbad]

<?php
//connect to database first
foreach ($_POST as $name => $value) {
$_POST[$name] = (trim($value) == '') ? 'null' : "'" . mysql_real_escape_string($value) . "'";
}
?>

 

connect to database first?

 

Yeah, before the code that follows 'Cause mysql_real_escape_string() requires an open connection.