CODEWITHSUNDEEP
phpmysql
goldenboy48
goldenboy48

Reputation: 23

Mysql new rows don't have default value inserted

ALTER TABLE  `songs`
CHANGE  `artist_name`  `artist_name` VARCHAR( 255 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT  'N/A'

^ So that's my table and the default for artist_name is 'N/A'. I'm using a form to submit data. However, when I submit the data through a form, the row doesn't contain 'N/A' and the value is empty. Anyone know what is going on?

$video_id=$_POST['video_id'];
$song_name=$_POST['song_name'];
$artist_name=$_POST['artist_name'];
$movie_name=$_POST['movie_name'];
$language=$_POST['language'];
$oldornew=$_POST['oldornew'];

//inserting data order
$order = "INSERT INTO dhb_dhb.songs
        (video_id, song_name,artist_name, movie_name,language,oldornew)
        VALUES
        ('$video_id', '$song_name','$artist_name', '$movie_name', '$language', '$oldornew')";

Upvotes: 0

Views: 1147

Answers (2)

Highway of Life
Highway of Life

Reputation: 24301

Any user input can be checked to see if it's set, and if not, use a default value of NULL:

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

Now calling this in a query will set the default value if $_POST['variable'] is empty (or unset).

I must caution. Whenever you take user input and put directly into a Database Query as you've done in your code example, you're opening up a serious security vulnerability known as SQL Injection. Consider sanitizing your user input using mysql_real_escape_string

EDIT:

Your code could then be applied by using something like the following:

<?php

$data = array(
    'video_id',
    'song_name',
    'artist_name',
    'movie_name',
    'language',
    'oldornew',
);
$insert_ary = array();

foreach ($data as $val) {
    if (!empty($_POST[$val])) {
        $insert_ary[$val] = mysql_real_escape_string($_POST[$val]);
    }
}

$columns = implode(', ', array_keys($insert_ary));
$values = implode("', '", $insert_ary);
$sql = "INSERT INTO dhb_dhb.songs ($columns) VALUES('$values')";

Upvotes: 1

Joost
Joost

Reputation: 10413

You'd have to unset the specific $_POST key if it's empty, so NULL will be inserted which then automatically is assigned the default value. Now you're just inserting a blank string, which is not translated into the default column value, since it isn't NULL.

EDIT The easiest way to do it then is to use an associative array, containing only the keys/values that are not empty:

$data = array('video_id' => $video_id);
foreach(array('artist_name', 'song_name') as $key)
    if(!empty($_POST[$key])){
        $data[$key] = mysql_real_escape_string($key);
    }
}

$columns = "`". implode("`, `", array_keys($data)) ."`";
$values = "'". implode("', '", array_values($data)) ."'";
$query = "INSERT INTO table ({$columns}) VALUES ({$values})";

This hasn't been tested and values should be tested before inserting (e.g. is $song_id really an integer?) but this is the general idea.

Upvotes: 2

Related Questions