Member Avatar for anish.anick

Hi,
I am a beginner in php mysql... I want to pass null values to integer from PHP file to mysql stored procedure.

eg:
$name = "John";
$age = $_POST; // Which is a null value..

and i called the procedure like CALL InsertStudent('$name',$age);

my procedure is not executing ... But it works fine if the $age has some values...

  • 2 Contributors
  • 1 Reply
  • 115 Views
  • 2 Months Discussion Span
  • Latest Post Latest Post by petr.pavel

Dani AI

Generated

— the most common root cause here is that the SQL you send becomes syntactically invalid when the PHP variable is empty (for example you end up with CALL InsertStudent('John',)). already pointed out that you must send the SQL literal NULL when you mean SQL NULL. Two safe approaches are shown below.

Use prepared statements (recommended). Bind NULL explicitly so the driver and server treat it correctly:

$stmt = $pdo->prepare('CALL InsertStudent(:name, :age)');
$stmt->bindValue(':name', $name, PDO::PARAM_STR);
if ($age === null) {
    $stmt->bindValue(':age', null, PDO::PARAM_NULL);
} else {
    $stmt->bindValue(':age', (int)$age, PDO::PARAM_INT);
}
$stmt->execute();

If you must build the CALL string manually, insert the unquoted SQL word NULL for missing integers and quote the string parameter safely:

$age_sql = is_null($age) ? 'NULL' : (int)$age;
$sql = "CALL InsertStudent(" . $pdo->quote($name) . ", $age_sql)";
$pdo->exec($sql);

Troubleshooting tips: echo or log the final SQL to see if it’s valid, and catch exceptions to read the database error message. Prefer PDO or mysqli over the old mysql extension to avoid security and compatibility problems. Relevant docs: PDO prepared statements and .

Member Avatar for petr.pavel

Hi Anish, I don't quite understand your description. What do you mean by "pass null values to integer"?

PHP NULL values won't be passed to MySQL as NULL you have to explicitly type NULL.
Example:

if ($value === NULL)
  $sqlValue = "NULL";
else
  $sqlValue = "'".mysql_real_escape_string($value)."'";

mysql_query("INSERT INTO mytable (myattribute) VALUES ($sqlValue)");
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.