mysql_real_escape_string() problem

[robert_gsfame]

I just realise that single quotes '' and NULL will completely give me different result

 

and now i have another problem, how can i insert NULL value into database but still using mysql_real_escape_string() ??

 

yet if i have '' and Null , and i use if(empty())  will all records with both NULL and '' still can be retrieved

 

thanks in advance

[de.monkeyz]

If you want to provide the null keyword, you can check if the data entered is blank, and define it as NULL if it is. Like so:

 

$data = empty($data) ? 'NULL', "'".mysql_real_escape_string($data)."'"; //The '' around the data are already included in this. When the data is empty it will give NULL

$query = "INSERT INTO `table` SET `field` = $data"; //We already added the single quotes in the above line.

[robert_gsfame]

NULL still has to be put between quotes??

[de.monkeyz]

NULL isn't put in quotes. If you check my code, it only puts quotes around values which get put though mysql_real_escape_string.

 

Example:

$data is My Title
$data becomes 'My Title'

$data is 
$data becomes NULL

[robert_gsfame]

$test="NULL"

 

Is this still correct???

 

mysql_query(sprintf("INSERT INTO table1(column1)VALUES('%s')",

mysql_real_escape_string($test)));

 

 

[de.monkeyz]

No it is not. You're surrounding the value in quotes in your query.

 

Here

INSERT INTO table1 (column1) VALUES( 'NULL' ) //What your code does - incorrect

INSERT INTO table1 (column1) VALUES(NULL) //What it should do

[robert_gsfame]

oops confused..... :confused:

[robert_gsfame]

so if i write like what u did, then i cannot use sprintf()

[de.monkeyz]

NULL is a keyword in mysql. Like SELECT is, or like function is in php. If you wrap it in quotes like your code did, it will just read it as the string NULL. My code that I posted will take a variable called $data and either make it into a string including the quotes for the query, or NULL, WITHOUT quotes. I can even turn it into a function

 

function nullify($data) {
   return empty($data) ? 'NULL', "'".mysql_real_escape_string($data)."'"; //The '' around the data are already included in this. When the data is empty it will give NULL
}

//Then I can use it your query:
$test = '' //Set it to an empty string if you want NULL

mysql_query(sprintf("INSERT INTO table1(column1)VALUES(%s)",
nullify($test)));

[robert_gsfame]

Yess!!! i got it now!! thank you so much for your help!!