php 使用外键将简单的 xml 插入到 mysql 数据库表中

Question

使用 php 将 xml 元素插入 mysql 数据库的逻辑存在真正的问题。我发现所有示例仅使用 1 个表(简单)。我需要一个用于属性元素的表格，然后是一个用于保存 xml 文件中嵌套在另一层的许多图片的表格。

图片需要有一个与单个 property_id 相关的外键。我设法通过使用 insert_last_id 来做到这一点，但它只插入了 1 张图片..而不是元素下附加的许多图片。 (这是一个 url，而不是 jpeg)，所以只是一个 varchar 列。 )

如有帮助，不胜感激。 (将使用准备好的语句，我刚开始是这样的)

//加载xml
$xml = simplexml_load_file('propertieshort.xml') 或 die("找不到它");

//循环遍历每个属性并插入选定的$变量。

foreach($xml->branches->branch->properties->property as $row){


$price_text = (string)$row->numeric_price;
$bedrooms = (int)$row->bedrooms;
$priority =(string)$row->priority;
$advert_heading = (string)$row->advert_heading;
$brochure = (string)$row->brochure;


$sql = "INSERT INTO properties( price_text, bedrooms, priority,  
advert_heading, brochure) VALUES ('". $price_text ."','". $bedrooms ."','". 
$priority ."','". $advert_heading ."','". $brochure ."')";

//尝试了一下，但没有成功将 property_id 链接到许多图片 $prop_id = mysqli_insert_id($conn);

//尝试循环与每个属性关联的许多图片 foreach ($xml->分支->分支->属性->属性->图片->图片为 $行)

$pictures = (string)$rows->filename;  

$sql2 = "INSERT INTO pictures(property_id,  filename) VALUES ('". $prop_id 
."' , '". $pictures ."')";

//i just print to html for testing not insert into db

print_r($sql);
echo"<br><br>", print_r($sql2);echo"<br><br>";







 <?xml version="1.0" encoding="ISO-8859-1" ?>
<agency>
  <branches>
    <branch name=" Baxters Bath">
      <properties>
        <property reference="BB00000285">
          <availableFrom>02/12/2011 00:00:00</availableFrom>
          <department>Agricultural</department>
          <propertyofweek>NO</propertyofweek>
          <price_text>OIRO £749,950</price_text>
          <numeric_price>749950.0000</numeric_price>
          <bedrooms>4</bedrooms>
          <receptions>2</receptions>
          <bathrooms>0</bathrooms>
          <priority>Exchanged</priority>
          <tenure>Leasehold</tenure>
          <advert_heading>A superbly presented four bedroom two bathroom semi detached family house.</advert_heading>
          <main_advert>A superbly presented four bedroom two bathroom semi detached family house. The property  has been extended to offer large ground floor accomodation and also benifits from a workshop/ office at the rear of the garden.
&lt;a href="http://www.google.co.uk" target="_blank"&gt;this is a link&lt;/a&gt;</main_advert>
          <advert2>A superbly presented four bedroom two bathroom semi detached family house. The property  has been extended to offer large ground floor accomodation and also benifits from a workshop/ office at the rear of the garden.
&lt;a href="http://www.google.co.uk" target="_blank"&gt;this is a link&lt;/a&gt;</advert2>
          <advert3 />
          <advert4 />
          <advert5 />
          <advert6 />
          <house_number>11</house_number>
          <street>Murhill</street>
          <district>Limpley Stoke</district>
          <town>Bath</town>
          <county />
          <postcode>BA2 7FG</postcode>
          <country>UK        </country>
          <area>Bath City Centre</area>
          <bullet1 />
          <bullet2 />
          <bullet3 />
          <bullet4 />
          <bullet5 />
          <bullet6 />
          <bullet7 />
          <bullet8 />
          <bullet9 />
          <bullet10 />
          <bullet11 />
          <bullet12 />
          <bullet13 />
          <bullet14 />
          <bullet15 />
          <bullet16 />
          <bullet17 />
          <bullet18 />
          <bullet19 />
          <bullet20 />
          <brochure />
          <epc />
          <hips />
          <filename3 />
          <filename4 />
          <filename5 />
          <filename6 />
          <virtual_tour_url />
          <property_type>House</property_type>
          <commercial_type> Dairy Unit</commercial_type>
          <property_style>Semi Detached</property_style>
          <property_reference>367268</property_reference>
          <newHome>NO</newHome>
          <noChain>NO</noChain>
          <furnished>Unknown</furnished>
          <rooms />
          <pictures>
            <picture name="Front">
              <filename>http://www.expertagent.co.uk/in4glestates/{76C20EAB-0589-4AEB-A7BE-CE3182F07285}/{3CCBEC59-BBE0-4DFE-864C-582BDAB5C577}/7A.jpg</filename>
            </picture>
            <picture name="Dining Room">
              <filename>http://www.expertagent.co.uk/in4glestates/{76C20EAB-0589-4AEB-A7BE-CE3182F07285}/{3CCBEC59-BBE0-4DFE-864C-582BDAB5C577}/7B.jpg</filename>
            </picture>
            <picture name="Garden">
              <filename>http://www.expertagent.co.uk/in4glestates/{76C20EAB-0589-4AEB-A7BE-CE3182F07285}/{3CCBEC59-BBE0-4DFE-864C-582BDAB5C577}/7D.jpg</filename>
            </picture>
            <picture name="Photo 5">
              <filename>http://www.expertagent.co.uk/in4glestates/{76C20EAB-0589-4AEB-A7BE-CE3182F07285}/{3CCBEC59-BBE0-4DFE-864C-582BDAB5C577}/HH002070.JPG</filename>
            </picture>
          </pictures>
          <floorplans />
        </property>
       </properties>
      </branch>
     </branches>
     </agency>

Answer 1

试试这个:

foreach($xml->branches->branch->properties->property as $row){

echo 'Inside Property Loop'; 

$price_text = (string)$row->numeric_price;
$bedrooms = (int)$row->bedrooms;
$priority =(string)$row->priority;
$advert_heading = (string)$row->advert_heading;
$brochure = (string)$row->brochure;


$allPictures = (array)$row->pictures;
foreach($allPictures['picture'] as $singlePic){
     echo '<br/> >>> Inside Picture loop.<br/>';

    $name = (string)$singlePic['name'];
    $url = (string)$singlePic->filename;
}
}