Inserting NULL

vertmonkee

I have this sql but when it runs certain values should be coming in as NULL but instead there is just a blank area in the database. So later when i run a if isset statement it gets everything. How can i have those values where nothing was entered on the form coming in as null.

$obstacle_insert = "INSERT INTO obstacles (spot_id , rails , steps , ledges, gaps , drops, manuals) VALUES ($spot_id, '$rails', '$steps', '$ledges', '$gaps', '$drops', '$manuals')";

$obstacle_result = mysql_query($obstacle_insert) or die (mysql_error());

Thanks

Roger_Ramjet

If you want a column to store NULL when there is no data then you set it to allow null and the default to null. Then on insert or update you just omit the column form the query and the db stores null.

What this means is that you have to validate the form input and only include the field in the query if the post var is present.

$base = "INSERT INTO `obstacles` (";
$cols = "";
$vals = "";
// now build the column and value lists
foreach ($_POST as $key=>$val) {
   $cols .= $key . ",";
   $vals .= "'" . $val . "',";
}
// now tidy up the cols and vals
$cols = rtrim($cols, ',');
$vals = rtrim($vals, ',');
// now build the query
$sql = $base . $cols . ') VALUES (' . $vals . ')';

Now you have a query that only inserts data into columns where there is user input. Of course, in production one would be validating the user input, stripping slashes, escaping quotes, etc. I always have an array of required field names that I compare to the POST array before I do anything else, if a required field is missing then output an error message and rebuild the form with the data that was present, etc.

vertmonkee

Thanks for that, the only thing is that sql is part of a larger piece of code and i'm not sure it will work in there.

This is the code.

<?php
session_start();

include "includes\connect.php";

//Information
$spot_name = $_POST['spot_name'];
$address = $_POST['address'];
$suburb = $_POST['suburb'];
$town = $_POST['town'];
$county = $_POST['county'];
$country = $_POST['country'];
$username = $_SESSION['user_logged'];
$comments = $_POST['comments'];

//Obstacles
$rails = $_POST['rails'];
$steps = $_POST['steps'];
$ledges = $_POST['ledges'];
$gaps = $_POST['gap'];
$drops = $_POST['drop'];
$manuals = $_POST['manual'];

//Images
//$image1 = $_POST[''];

$information_insert = "INSERT INTO `spot_information` ( `name` , `address` , `suburb` , `town` , `county` , `country` , `username` , `comments`) 
                       VALUES (
                       '$spot_name', '$address', '$suburb', '$town', '$county', '$country', '$username', '$comments'
                       )";

$information_result = mysql_query($information_insert) or die (mysql_error());

$spot_id = mysql_insert_id();

$obstacle_insert = "INSERT INTO `obstacles` (`spot_id` , `rails` , `steps` , `ledges`, `gaps` , `drops`, `manuals`) VALUES ($spot_id, '$rails', '$steps', '$ledges', '$gaps', '$drops', '$manuals')";

$obstacle_result = mysql_query($obstacle_insert) or die (mysql_error());

?>

<html>

<head>
<title>Thanks for adding <?php echo $spot_name; ?> to the list.</title>
<link REL=stylesheet HREF="stylesheet.css" TYPE="text/css">
</head>

<body>
<div id="pagewidth" > <!--begin pagewidth div -->

<div id="header" > <!-- begin header div -->

<?php include "includes\header.php"; ?>

</div> <!-- end header width -->

<div id="wrapper" class="clearfix" > <!-- begin wrapper div-->

<div id="maincol" > <!-- begin maincol div -->

Thanks for adding <?php echo $spot_name; ?> to the list.
<br>
<a href='index.php'>Click here to go back to the home page</a>


</div> <!-- end maincol div -->

<div id="leftcol" > <!-- begin leftcol div -->

<?php include "menu.php"; ?>

</div> <!-- end leftcol div -->
</div> <!-- end wrapper div -->

<div id="footer" > <!-- begin footer div -->

<?php include "includes\footer.php"; ?>

</div> <!-- end footer div -->
</div> <!-- end pagewidth div -->
</body>
</html>

The $_POST array contains all the input from the form, but i need to insert the information part to get the id for the spot. Then do the obstacles. Hope that makes sense.

Roger_Ramjet

No validation of user input. No protection against sql injection attacks. And only limited error handling if the query fails. All in all, the whole script needs revision.

My code example would replace all that at the top where the POST vars are passed into local vars. Before that you would need to validate the user has entered all the required data and have a routine to go back and collect any missing fields when they have not.