php - 带有空值的 xml dom 到 mysql

Question

这是我将 XML 文件导入 MySQL 表的 PHP 代码:

<?php

sleep(3);
$output = '';
$fileXML = 'archivi/clienti.xml';

if (isset($fileXML) && $fileXML != '')
    {
    $valid_extension = array(
        'xml'
    );
    $file_data = explode('.', $fileXML);
    $file_extension = end($file_data);
    if (in_array($file_extension, $valid_extension))
        {
        $xmlDoc = new DOMDocument();
        $xmlDoc->load($fileXML);
        $connect = new PDO('mysql:host=localhost;dbname=otc', 'root', 'root');
        $xmlObject = $xmlDoc->getElementsByTagName('cliente');
        $itemCount = $xmlObject->length;
        print_r($xmlObject);
        for ($i = 0; $i < $itemCount; $i++)
            {
            $codice = $xmlObject->item($i)->getElementsByTagName('codice')->item(0)->childNodes->item(0)->nodeValue;
            $ragione_sociale = $xmlObject->item($i)->getElementsByTagName('ragione_sociale')->item(0)->childNodes->item(0)->nodeValue;
            $indirizzo = $xmlObject->item($i)->getElementsByTagName('indirizzo')->item(0)->childNodes->item(0)->nodeValue;
            $cap = $xmlObject->item($i)->getElementsByTagName('cap')->item(0)->childNodes->item(0)->nodeValue;
            $citta = $xmlObject->item($i)->getElementsByTagName('citta')->item(0)->childNodes->item(0)->nodeValue;
            $prov = $xmlObject->item($i)->getElementsByTagName('prov')->item(0)->childNodes->item(0)->nodeValue;
            $piva = $xmlObject->item($i)->getElementsByTagName('piva')->item(0)->childNodes->item(0)->nodeValue;
            if (!empty($cfisc = $xmlObject->item($i)->getElementsByTagName('cfisc')->item(0)->childNodes->item(0)->nodeValue))
                {
                echo 'not empty';
                }
              else
                {
                echo 'is not set or empty';
                $cfisc = ' ';
                }

            $sql = $connect->prepare("INSERT INTO clienti (codice, ragione_sociale,indirizzo,cap,citta,prov,piva,cfisc ) VALUES (?, ?, ?, ?, ?, ?, ?)");
            $sql->execute(array(
                $codice,
                $ragione_sociale,
                $indirizzo,
                $cap,
                $citta,
                $prov,
                $piva,
                $cfisc
            ));
            print "Finished Item $title n<br/>";
            }
        }
      else
        {
        $output = '<div class="alert alert-warning">Invalid File</div>';
        }
    }
  else
    {
    $output = '<div class="alert alert-warning">Please Select XML File</div>';
    }

echo $output;

?>

XML

<?xml version="1.0"?>
<elenco_clienti>
    <cliente>
        <codice>00001</codice>
        <ragione_sociale>xxxxxxxxxxxxx</ragione_sociale>
        <indirizzo>yyyyyyyyyyy</indirizzo>
        <cap>000000</cap>
        <citta>xxxxxxxx</citta>
        <prov>xx</prov>
        <piva>000000000</piva>
        <cfisc/>
        <luogo_nasc/>
        <data_nasc>01011900</data_nasc>
        <sesso>A</sesso>
        <tele>00000000</tele>
        <cell/>
        <mail>xxxxxxxxxxxxxx</mail>
        <cod_card/>
        <cod_card1/>
        <punti_card>0</punti_card>
    </cliente>
</elenco_clienti>

问题是Notice: Trying to get property of non-object in line 31因为该值为空。

如何获取空值并放入我的数据库？

我也尝试使用 PHP simplexml，但在尝试获取类似 <cod_card></cod_card> 的值时我得到了相同的结果.我收到上述错误。

Answer 1

关于预准备语句 的关键点之一是您只需准备一次语句对象，然后只需更改分配给绑定(bind)参数的值即可根据需要执行多次。为此，在循环外准备语句，如果成功创建语句，则将占位符绑定(bind)到变量。此时的变量不需要存在 - 它们稍后在循环内创建，之后可以执行语句。

子节点值的访问可以通过使用一个简单的函数来简化——未经测试但看起来应该可以正常工作。我更新了函数 getchildvalue 来测试子节点，如果不存在则返回一个空字符串，这应该可以防止 Trying to get property of non-object

的错误警告

<?php

    $output = array();
    $file = 'archivi/clienti.xml';

    if( !empty( $file ) ) {

        $valid_extension = array('xml');
        $ext=pathinfo( $file, PATHINFO_EXTENSION ); # much simpler method to get file extension


        function getchildvalue( $node, $tag ){
            return $node->getElementsByTagName( $tag )->item(0)->childNodes->length> 0 ? $node->getElementsByTagName( $tag )->item(0)->childNodes->item(0)->nodeValue : '';
        }


        if( in_array( $ext, $valid_extension ) ) {

            $dom = new DOMDocument();
            $dom->load( $file );

            /* connect & prepare sql */
            $connect = new PDO('mysql:host=localhost;dbname=otc', 'root', 'root');
            $sql='insert into `clienti` (`codice`,`ragione_sociale`,`indirizzo`,`cap`,`citta`,`prov`,`piva`,`cfisc` ) values (?, ?, ?, ?, ?, ?, ?, ?)';
            $stmt=$connect->prepare( $sql );

            if( $stmt ){
                /* statement OK - bind parameters to variables */

                /* UPDATE: incremented each by +1 ~ these are not zero based as initially thought */

                $stmt->bindParam(1,$codice);
                $stmt->bindParam(2,$ragione_sociale);
                $stmt->bindParam(3,$indirizzo);
                $stmt->bindParam(4,$cap);
                $stmt->bindParam(5,$citta);
                $stmt->bindParam(6,$prov);
                $stmt->bindParam(7,$piva);
                $stmt->bindParam(8,$cfisc);

                /* get DOM nodes */
                $col = $dom->getElementsByTagName('cliente');
                foreach( $col as $node ){
                    try{
                        /* get values of child nodes */
                        $codice = getchildvalue( $node, 'codice');
                        $ragione_sociale = getchildvalue( $node, 'ragione_sociale');
                        $indirizzo = getchildvalue( $node, 'indirizzo');
                        $cap = getchildvalue( $node, 'cap');
                        $citta = getchildvalue( $node, 'citta');
                        $prov = getchildvalue( $node, 'prov');
                        $piva = getchildvalue( $node, 'piva');
                        $cfisc = getchildvalue( $node, 'cfisc');

                        if( empty( $cfisc ) ) $output[]='cfisc is empty';

                        /* execute sql statement */
                        $stmt->execute();


                        $output[]="Finished Item $title";   //  ?where is $title defined?
                    }catch( Exception $e ){
                        $output[]=$e->getMessage();
                    }
                }

                $stmt->closeCursor();

            } else {
                $output[]='sql failed preparation';
            }
        } else {
            $output[]='<div class="alert alert-warning">Invalid File</div>';
        }
    } else {
        $output[]='<div class="alert alert-warning">Please Select XML File</div>';
    }        
    echo $output;
?>

读取xml文件的例子

<?php


    function getchildvalue( $node, $tag, $rv=false ){
        /*
            $rv = return value
            specify default value to return if node does
            not contain any content
        */
        return $node->getElementsByTagName( $tag )->item(0)->childNodes->length> 0 ? $node->getElementsByTagName( $tag )->item(0)->childNodes->item(0)->nodeValue : ( $rv ? $rv : '' );
    }


    $xml='<?xml version="1.0"?>
    <elenco_clienti>
        <cliente>
            <codice>00001</codice>
            <ragione_sociale>xxxxxxxxxxxxx</ragione_sociale>
            <indirizzo>yyyyyyyyyyy</indirizzo>
            <cap>000000</cap>
            <citta>xxxxxxxx</citta>
            <prov>xx</prov>
            <piva>000000000</piva>
            <cfisc/>
            <luogo_nasc/>
            <data_nasc>01011900</data_nasc>
            <sesso>A</sesso>
            <tele>00000000</tele>
            <cell/>
            <mail>xxxxxxxxxxxxxx</mail>
            <cod_card/>
            <cod_card1/>
            <punti_card>0</punti_card>
        </cliente>
    </elenco_clienti>';

    $dom = new DOMDocument();
    $dom->loadXML( $xml );

    $col = $dom->getElementsByTagName('cliente');
    foreach( $col as $node ){

        $codice = getchildvalue( $node, 'codice', 'codice is empty');
        $ragione_sociale = getchildvalue( $node, 'ragione_sociale', 'ragione_sociale is empty');
        $indirizzo = getchildvalue( $node, 'indirizzo');
        $cap = getchildvalue( $node, 'cap');
        $citta = getchildvalue( $node, 'citta');
        $prov = getchildvalue( $node, 'prov');
        $piva = getchildvalue( $node, 'piva');
        $cfisc = getchildvalue( $node, 'cfisc', 'big yellow banana' );/* <------ set alernate return value */


        echo $codice.' '.$ragione_sociale.' '.$indirizzo .' ' .$cap.' '.$citta.' '.$prov.' '.$piva.' '.$cfisc;
    }
?>

上面的输出

00001 xxxxxxxxxxxxx yyyyyyyyyyy 000000 xxxxxxxx xx 000000000 big yellow banana