PDO预处理语句：如果缺少值，则使用NULL

Question

I have a prepared statement to store in my db some data coming from an API:

$sql = "INSERT INTO `standings` (`league_id`, `season`, `position`, `team_id`, `played`, `playedathome`, `playedaway`, `won`, `draw`, `lost`, `numberofshots`, `yellowcards`, `redcards`, `goals_for`, `goals_against`, `points`)
VALUES (:league_id, :season, :position, :team_id, :played, :playedathome, :playedaway, :won, :draw, :lost, :numberofshots, :yellowcards, :redcards, :goals_for, :goals_against, :points)
ON DUPLICATE KEY UPDATE `league_id`=:league_id, `season`=:season, `position`=:position, `team_id`=:team_id, `played`=:played, `playedathome`=:playedathome, `playedaway`=:playedaway, `won`=:won, `draw`=:draw, `lost`=:lost, `numberofshots`=:numberofshots, `yellowcards`=:yellowcards, `redcards`=:redcards, `goals_for`=:goals_for, `goals_against`=:goals_against, `points`=:points";

$prep = $pdo->prepare($sql);

$prep->bindParam(':league_id', $league_id);
$prep->bindParam(':season', $season);
$prep->bindParam(':position', $position);
$prep->bindParam(':team_id', $team_id);
$prep->bindParam(':played', $played);
$prep->bindParam(':playedathome', $playedathome);
$prep->bindParam(':playedaway', $playedaway);
$prep->bindParam(':won', $won);
$prep->bindParam(':draw', $draw);
$prep->bindParam(':lost', $lost);
$prep->bindParam(':numberofshots', $numberofshots);
$prep->bindParam(':yellowcards', $yellowcards);
$prep->bindParam(':redcards', $redcards);
$prep->bindParam(':goals_for', $goals_for);
$prep->bindParam(':goals_against', $goals_against);
$prep->bindParam(':points', $points);


foreach($result->TeamLeagueStanding as $standing){

    $team_id = $standing->Team_Id;
    $played = $standing->Played;
    $playedathome = $standing->PlayedAtHome;
    $playedaway = $standing->PlayedAway;
    $won = $standing->Won;
    $draw = $standing->Draw;
    $lost = $standing->Lost;
    $numberofshots = $standing->NumberOfShots;
    [etc...]


    $prep->execute() or print("PDO execute error: ".$prep->errorInfo()[2]);

The problem is that some leagues don't provide the NumberOfShots value, so $standing->NumberOfShots is an empty string, causing the error:

PDO execute error: Incorrect integer value: '' for column 'numberofshots' at row 1

And that row doesn't get inserted.

How can I make something like

if($numberofshot == "")
    $numberofshot = NULL;

or tell PDO to use the default value instead of throwing an error, if the value is incorrect?

Answer 1

http://php.net/manual/en/pdo.setattribute.php

try:

setAttribute(PDO::ATTR_ORACLE_NULLS, PDO::NULL_EMPTY_STRING);