dongtuo2373 2011-06-17 23:21
浏览 108

无法插入MySQL数据库

I have several xml files. I am trying to insert the data collected from those xml files into a database. The problem is that some got inserted and some other ones are not. here are two of the xml files. The first one is inserted without any pb, however, the second one is not inserted into the data base.

<?xml version="1.0" encoding="UTF-8"?>

<recipeml version="0.5">
  <recipe>
    <head>
      <title>10 Minute Meat Loaf</title>
      <categories>
        <cat>Beef</cat></categories>
      <yield>4</yield></head>
    <ingredients>
      <ing>
        <amt>
          <qty>1</qty>
          <unit>pound</unit></amt>
        <item>Ground beef</item></ing>
      <ing>
        <amt>
          <qty>1</qty>
          <unit/></amt>
        <item>Egg</item></ing>
      <ing>
        <amt>
          <qty>1/2</qty>
          <unit>cups</unit></amt>
        <item>Bread crumbs</item></ing>
      <ing>
        <amt>
          <qty>1/4</qty>
          <unit>cups</unit></amt>
        <item>Milk</item></ing>
      <ing>
        <amt>
          <qty>2</qty>
          <unit>tablespoons</unit></amt>
        <item>Onion soup mix</item></ing>
      <ing>
        <amt>
          <qty>2</qty>
          <unit>tablespoons</unit></amt>
        <item>Ketchup</item></ing>
      <ing>
        <amt>
          <qty>2</qty>
          <unit>tablespoons</unit></amt>
        <item>Soy sauce</item></ing>
      <ing>
        <amt>
          <qty>1/2</qty>
          <unit>cups</unit></amt>
        <item>Swiss cheese; shredded</item></ing></ingredients>
    <directions>
      <step>  Combine all ingredients and shape into a round or oval loaf. Place in
  microwave-safe dish, cover with waxed paper and microwave on high for 10
  minutes, turning dish after 5 minutes, of cooking, drain and cover with
  foil. Let stand 10 minutes before slicing. Makes 4-6 servings.

  busted by sooz

  Recipe by: Key Gourmet

  Posted to recipelu-digest by sooz &lt;kirkland@gj.net&gt; on Mar 18, 1998

</step></directions></recipe></recipeml>

****second XML File I have trouble to insert into the database***

<?xml version="1.0" encoding="UTF-8"?>

<recipeml version="0.5">
  <recipe>
    <head>
      <title>10 Can Chicken Casserole</title>
      <categories>
        <cat>Casserole</cat></categories>
      <yield>8</yield></head>
    <ingredients>
      <ing>
        <amt>
          <qty>1</qty>
          <unit>large</unit></amt>
        <item>Fryer; boiled with onion and celery; deboned, cut up</item></ing>
      <ing>
        <amt>
          <qty>1</qty>
          <unit>can</unit></amt>
        <item>Cheddar cheese soup</item></ing>
      <ing>
        <amt>
          <qty>1</qty>
          <unit>can</unit></amt>
        <item>Cream of mushroom soup</item></ing>
      <ing>
        <amt>
          <qty>1</qty>
          <unit>can</unit></amt>
        <item>(small) evaporated milk</item></ing>
      <ing>
        <amt>
          <qty>1</qty>
          <unit>can</unit></amt>
        <item>(#202) asparagus tips; drained</item></ing>
      <ing>
        <amt>
          <qty>1</qty>
          <unit>can</unit></amt>
        <item>French style green beans; drained</item></ing>
      <ing>
        <amt>
          <qty>1</qty>
          <unit>can</unit></amt>
        <item>Water chestnuts; drained, sliced</item></ing>
      <ing>
        <amt>
          <qty>1</qty>
          <unit>can</unit></amt>
        <item>(4-oz) mushroom stems &amp; pieces with liquid</item></ing>
      <ing>
        <amt>
          <qty>1</qty>
          <unit>can</unit></amt>
        <item>(small) pimento; diced, with liquid</item></ing>
      <ing>
        <amt>
          <qty>1</qty>
          <unit>can</unit></amt>
        <item>Chow mein noodles</item></ing>
      <ing>
        <amt>
          <qty>1</qty>
          <unit>can</unit></amt>
        <item>French fried onion rings</item></ing>
      <ing>
        <amt>
          <qty/>
          <unit/></amt>
        <item>Red pepper to taste</item></ing></ingredients>
    <directions>
      <step>   Mix all ingredients, except the onion rings, together. Put into a 3 quart
  casserole; top with onion rings. Bake at 350 degrees until bubbly,
  approximately 30 minutes.

  From &lt;A Taste of Louisiana&gt;.Downloaded from Glen's MM Recipe Archive,
  http://www.erols.com/hosey.

</step></directions></recipe></recipeml>

now, this is my code in php5:

<?php

$link = mysql_connect('localhost', 'root', 'recipe');
                        if (!$link) 
                        {
                            die('Could not connect: ' . mysql_error());
                        }

                                    mysql_select_db('php_database');

                                $sql = 'DROP TABLE IF EXISTS `recipe`';
                                    mysql_query( $sql, $link );

                                    $sql = 'CREATE TABLE `recipe` (
                                            `idRecipe` TINYINT( 12) UNSIGNED NOT NULL AUTO_INCREMENT ,
                                            `RecipeTitle` VARCHAR( 75 ) NOT NULL,
                                            `Yield` TINYINT( 5) UNSIGNED NOT NULL ,
                                            `Steps` Text( 10000 ) NOT NULL,

                                            PRIMARY KEY ( `idRecipe` )
                                            )';
                                    echo 'Creating table: \'recipe\'....';
                                    mysql_query( $sql, $link );



        if ($handle = opendir('C:\wamp\www\PhP\Folder xml'))
        {   


            while (false !== ($file = readdir($handle)))
            {   //$i++;

                    if ($file != '.' && $file != '..')
                    {



                        if (file_exists($file))
                        {   

                            $use_errors = libxml_use_internal_errors(true);
                            $xml = simplexml_load_file($file); 
                            if (!$xml)
                            {
                                echo ("Cannot open the XML file $file <br />");

                            }
                            else
                            {

                                echo "<br /> <br />";

                                $title=$xml->recipe->head->title;
                                $yield=$xml->recipe->head->yield;
                                $step=$xml->recipe->directions->step;

                                echo "value of Recipe Title= $title, yield= $yield and step= $step<b />";
                                mysql_query("INSERT INTO recipe (RecipeTitle,Yield,Steps) values ('$title', '$yield','$step')");
                                 printf("Last inserted record has id %d
", mysql_insert_id());
                                mysql_close($link);
                                echo "<br />";

                                $xmla= "$file";
                                $xmlDoc = new DOMDocument();
                                $xmlDoc->load($xmla);
                                $cat = $xmlDoc->getElementsByTagName("cat");
                                $numOfcat = $cat->length;
                                echo "@@ here is the number of cat  :$numOfcat  @@<br />";

                                $qty = $xmlDoc->getElementsByTagName("qty");
                                $numOfqty = $qty->length;
                                echo "@@ here is the number of qty  :$numOfqty  @@<br />";


                                echo $xml->getName() . "<br />"; // displays recipeml

                                foreach($xml->children() as $child) 
                                {
                                    echo $child->getName() . ": " . $child . "<br />"; //displays recipe:
                                    //echo "INSERT " . $child . " INTO....";

                                    echo ("title=  ");
                                    echo $xml->recipe->head->title .  " ,
" ; //displays the title
                                    echo ("category= ");
                                    echo $xml->recipe->head->categories->cat . ",
" ;// displays the category
                                    echo ("yield= ");
                                    echo $xml->recipe->head->yield . ",
" ; //displays yield number
                                    echo ("quantity= ");
                                    echo $xml->recipe->ingredients->ing->amt->qty . ",
" ; //displays quantity
                                    echo ("unit= ");
                                    echo $xml->recipe->ingredients->ing->amt->unit . ",
" ; ////displays unit
                                    echo ("Item= ");
                                    echo $xml->recipe->ingredients->ing->item . ",
" ; //displays item

                                        for($i=1; $i<$numOfqty; $i=$i+1)
                                        {
                                            echo ("quantity= ");
                                            echo $xml->recipe->ingredients->ing[$i]->amt->qty . ",
" ;
                                            echo ("unit= ");
                                            echo $xml->recipe->ingredients->ing[$i]->amt->unit . ",
" ;
                                            echo ("Item= ");
                                            echo $xml->recipe->ingredients->ing[$i]->item . ",
" ; 
                                        } 
                                    echo ("Step to cook=********************** ");  
                                    echo $xml->recipe->directions->step . ",
" ; //displays item 
                                }
                            }
                        }   
                    }    
            }  
        }

        closedir($handle);
?> 

Any suggestions? many of those files are not well formed, But I used Altova to check if those two xml files are well formed and altova confirmed that they are both well formed. I will appreciate any suggestions.

Thanks,

T3000.

  • 写回答

2条回答 默认 最新

  • doushen9863 2011-06-17 23:28
    关注

    You have a ' in your second directions->step, which will make the query fail, as the rest of the text after the ' will be seen as regular SQL - which it is not.

    You should use mysql_real_escape_string on the strings before inserting them to the query.

    Or switch to MySQLi or a DB abstraction layer that utilizes prepared statements.

    评论

报告相同问题?

悬赏问题

  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题