I have a Song Uploading Form, where I will not directly input NULL value in MySQL like: mysql_query("INSERT INTO songs
(album_id
) VALUES (NULL)". I will insert NULL from PHP Variable to MySQL, and surely being safe from SQL Injection.
My SQL Table is:
CREATE TABLE IF NOT EXISTS `songs` (
`song_id` int(4) NOT NULL,
`song_name` varchar(64) NOT NULL,
`artist_id` int(4) NOT NULL,
`album_id` int(4) DEFAULT NULL,
`genre_id` int(4) DEFAULT NULL
PRIMARY KEY (`song_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
My FORM and FORM HANDLER Code is (PHP, HTML) like below:
<?php
if(isset($_REQUEST['SongForm']))
{
$song_name = trim($_POST['song_name']);
$artist_id = trim($_POST['artist_id']);
$album_id = $_POST['album_id']; if($album_id == 0) { $album_id = 'NULL'; } // I even tried using NULL instead of 'NULL'
$genre_id = $_POST['genre_id']; if($genre_id == 0) { $genre_id = 'NULL'; }
$query = mysql_query("
INSERT INTO `songs` (`song_name`, `artist_id`, `album_id`, `genre_id`)
VALUES ('".$song_name."', '".$artist_id."', '".$album_id."', '".$genre_id."')
");
}
?>
<form method="post" action="<?php echo $_SERVER['PHP_SELF']?>" name="SongForm" id="SongForm">
<table style="width: 100%">
<tr>
<td><b>Song Name</b></td>
<td><input name="song_name" value="" type="text" required /></td>
</tr>
<tr>
<td><b>Artist Name</b></td>
<td>
<select id="artist_id" name="artist_id">
<option value ="0">No Artist</option>
<option value ="1">Jennifer Lopez</option>
</select>
</td>
</tr>
<tr>
<td><b>Album Name</b></td>
<td>
<select id="album_id" name="album_id">
<option value ="0">No Album</option>
<option value ="1">Rebirth</option>
</select>
</td>
</tr>
<tr>
<td><b>Genre Name</b></td>
<td>
<select id="genre_id" name="genre_id">
<option value ="0">No Genre</option>
<option value ="1">Epic Records</option>
</select>
</td>
</tr>
<tr>
<td><b> </td></b>
<td><input name="SongForm" type="submit" value="Upload Song" /></td>
</tr>
</table>
</form>
But after this I get the Result in MySQL like:
Serial ID: 1 Song Name Name: I, Love Artist ID: 1 Album ID: 1 Genre ID: 1
Now, when I am NOT SELECTING "Album ID" and "Genre ID" for any song, it should Input "NULL" inside MySQL. But it is inputting "0".
Therefore the result is coming like:
Serial ID: 1 Song Name Name: I, Love Artist ID: 1 Album ID: 0 Genre ID: 0
Please give me a solution so that I can input NULL when I choose "No Album" and "No Genre".
Please don't make me confused explaining irrelevant topics.
Thanks to the friends who supported explaining answers, though any of the answers didn't give me proper solution yet.
You can insert NULL value into an int column with a condition i.e. the column must not have NOT NULL constraints. The syntax is as follows. INSERT INTO yourTableName(yourColumnName) values(NULL); To understand the above syntax, let us first create a table.
To prevent SQL Injection vulnerabilities in PHP, use PHP Data Objects (PDO) to create parametrized queries (prepared statements).
In your code, check your variable is empty, if they are empty, use NULL
instead of the variable.
To pass a NULL
to MySQL
, try
INSERT INTO table (field,field2) VALUES (NULL,3)
I tried the same thing that you did- set the variable to NULL, null, 'NULL', "NULL", even wrote null as a constant in the SQL string- nothing. The field in the database was always set to 0. Then I tried updating the table after inserting the row and that worked.
"UPDATE songs SET album_id = NULL WHERE etc."
The INSERT statement will insert a 0 if the column is specified at all. You could modify the SQL statement to not include the column that you want to be NULL, for example:
$query = mysql_query("
INSERT INTO `songs` (`song_name`,`artist_id`) VALUES ('".$song_name."','".$artist_id."')
");
and that would leave the remaining columns NULL.
http://www.w3schools.com/sql/sql_insert.asp
skip to Insert Data Only in Specified Columns
use if($album_id == 0) { $album_id = NULL; }
instead of if($album_id == 0) { $album_id == "NULL"; }
.
"NULL" is a string.
Remove the quotes and change ==(Checking equality) to =(Assigning a value to variable) in $album_id == "NULL";
$album_id = trim($_POST['album_id']); if($album_id == 0) { $album_id = NULL; }
$genre_id = trim($_POST['genre_id']); if($genre_id == 0) { $genre_id = NULL; }
$film_id = trim($_POST['film_id']); if($film_id == 0) { $film_id = NULL; }
$category_id = trim($_POST['category_id']); if($category_id == 0) { $category_id = NULL; }
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With