CODEWITHSUNDEEP
phpmysql
user547794
user547794

Reputation: 14511

PHP MYSQL - empty variable fails on insert

I have an insert statement that inserts variables collected from a form POST on the previous page. If the variables from the form are not filled in it fails on insert (presumably because it is inserting an empty string...) I have the dataype set to allow NULL values - how do I insert null values if the field was left empty from the form POST? 

$query = "
INSERT INTO songs (
    userid,
    wavURL,
    mp3URL,
    genre,
    songTitle,
    BPM
) VALUES (
    '$userid',
    '$wavFile',
    '$mp3File',
    '$genre',
    '$songTitle',
    '$BPM'
)
";

$result = mysql_query($query);

Upvotes: 0

Views: 1417

Answers (3)

nageeb
nageeb

Reputation: 2042

Here's how I do it. I don't like sending anything to an SQL query right from POST (always sanitize!) in the following cas you just run through the POST vars one by one and assign them to a secondary array while checking for 0 length strings and setting them to NULL.

foreach ($_POST as $key => $value) {
   strlen($value)=0 ? $vars[$key] = NULL : $vars[$key] = $value
 }

Then you can build your SQL query from the newly created $vars[] array.

As Jon states above, this would be the place to also escape strings, strip code and basically do all your server side validation prior to data being inserted into the db.

Upvotes: 0

derp
derp

Reputation: 3030

For what I understand when something is not filled the post variable is not set as an empty value but rather not set at all so in php you'd do for example:

$genre = isset($_POST['genre']) ? $_POST['genre'] : NULL;

Upvotes: 0

Jon
Jon

Reputation: 437376

The exact manner depends on if you are writing the query or binding parameters to a prepared statement.

If writing your own, it would look something like this:

$value = empty($_POST['bar']) ? null : $_POST['bar'];
$sql = sprintf('INSERT INTO foo (bar) VALUES (%s)',
             $value === null ? 'NULL', "'".mysql_real_escape_string($value)."'");

$result = mysql_query($sql);

The main point is that you need to pass in the string NULL (without quotes) if the value should be null and the string 'val' if the value should be "val". Note that since we are writing string literals in PHP, in both cases there is one more pair of quotes in the source code (this makes one pair in the first case, two pairs in the second).

Warning: When inserting to the database directly from request variables, it is very easy to be wide open to SQL injection attacks. Do not be another victim; read about how to protect yourself and implement one of the universally accepted solutions.

Upvotes: 1

Related Questions