5

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>&nbsp;</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.

Improve this question
4
  • So u have Default Null, will you explain why you want to insert null again Commented Nov 22, 2013 at 9:22
  • using default null is slow, just my 2 cents Commented Nov 22, 2013 at 9:24
  • Yes, because of "DEFAULT NULL" mysql_query() should store NULL when I send empty $phpVariable ('' or ""). But it is not storing NULL in MySQL row. It is storing 0 (Zero). That is the main problem. Commented Nov 22, 2013 at 9:33
  • Can anyone help me now? Commented Nov 22, 2013 at 15:58

4 Answers 4

Reset to default
3

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)
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Sorry! This is NOT the ANSWER of my QUESTION actually. I want to store NULL in MySQL from PHP Variable (the Variable will be NULL). Therefore NULL will be inserted from PHP Variable to MySQL.
In your code, check your variable is empty, if they are, use NULL instead of the variable
Actually from Drop down, when User selects Value "0" I check it with IF-ELSE Statement, then when It is "0" I put $variable = NULL. Then I store $variable inside MySQL. And, in different cases, artist/genre/album any one of those may be empty. So I must have to use $phpVariable inside mysql_query(). So, I can't check it outside using any PHP-Case. If it would be an issue of only one Column, then I could do that. Hope you have understood.
When I am not selecting "album_id" (therefore the song is not of any album) it should store NULL. But "album_id" is being saved as "0" when I am adding a new Song.
2

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

Improve this answer

Comments

1

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; }
Improve this answer

2 Comments

Sorry! I tried, NULL, null, 'NULL', "NULL" everything. But nothing is storing NULL in MySQL. It is storing 0.
By the way, it is InnoDB Engine. Not MyISAM. But I don't think that this will be an issue...
0

EDIT For the current question:

Lets assume $album_id=='NULL', $genre_id=='NULL', $artist_id==1 and $song_name=='deeper'

This query (the your one):

"INSERT INTO `songs` (`song_name`, `artist_id`, `album_id`, `genre_id`) VALUES ('".$song_name."', '".$artist_id."', '".$album_id."', '".$genre_id."')"

Will get you:

"INSERT INTO `songs` (`song_name`, `artist_id`, `album_id`, `genre_id`) VALUES ('deeper', '1', 'NULL', 'NULL')"

Which is not what you want. Get rid of the slashes arround integers and nulls. This query:

"INSERT INTO `songs` (`song_name`, `artist_id`, `album_id`, `genre_id`) VALUES ('$song_name', $artist_id, $album_id, $genre_id)"

Will get you:

"INSERT INTO `songs` (`song_name`, `artist_id`, `album_id`, `genre_id`) VALUES ('deeper', 1, NULL, NULL)"

Which is what you want.

To avoid sql injection make another little change:

// do not use addslashes when your server uses magic_quotes (probably it does not) $song_name = addslashes(trim($_POST['song_name'])); // if there is number, it will stay unchanged // otherwise it is changed to 0, which is safe $artist_id = $_POST['artist_id'] + 0; $album_id = $_POST['album_id'] + 0; if($album_id == 0) { $album_id = 'NULL'; } $genre_id = $_POST['genre_id'] + 0; if($genre_id == 0) { $genre_id = 'NULL'; }

When you want to ensure your script will work properly independently on magic_quotes, define this function:

function gpc_addslashes($str) { return (get_magic_quotes_gpc() ? $str : addslashes($str)); }

and use it instead of addslashes() for the song name.

Improve this answer

6 Comments

I hope it makes sense to you, and you will get it work. I've put quite a lot information, because there are many weird things in your code. :-)
Thanks @Lukas Z., can you kindly check this Post again? I have re-submitted the Question properly (I believe). I need a fast solution urgent in fact. Trying for a long time but getting no solution.
Get rid of the apostrophes in your query (but keep $album_id = 'NULL'), because then in your query is 'NULL' instead of NULL. Instead of ...'".$artist_id."'... use just ...".$artist_id."... You have provided $query2 and $query1, and I said to use the query by $query1, in your edited post you have used the one with $query2 in your original POST. I'll edit the answer to match your current version.
OK, no you have it quite straight-forward, if i did not do any mistake, you just copy-paste. PS: some of those irrelevant topics are actually rather critical ;-)
I didn't point you in fact. Someone was saying to put NULL inside mysql_query(). I was saying it to him/her. Sorry if I hurt you. However, I have got an alternative solution... ... $query2 = mysql_query(" UPDATE songs SET album_id = NULL, genre_id = NULL, film_id = NULL WHERE song_name = '".$song_name."' "); ... ... I am running this query after my $query, and then it is inputting NULL.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.