douji8017 2015-01-04 20:04
浏览 43
已采纳

通过php [关闭]从sql数据库输出xml文件

I need help to generate a simple xml file of youtube id's from my sql database via php.

DESIRED OUTPUT

<videos>
<youtube media="http://www.youtube.com/watch?v=_hTiRnqnvDs" autoplay="true"></youtube>
<youtube media="http://www.youtube.com/watch?v=5wx0GfbC0BA" autoplay="true"></youtube>
<youtube media="http://www.youtube.com/watch?v=ERGrSQoY5fs" autoplay="true"></youtube>
</videos>

MY OUTPUT

<videos>
    <youtube media="http://www.youtube.com/watch?v=![CDATA[$value]]" autoplay="true"></youtube>
    <youtube media="http://www.youtube.com/watch?v=![CDATA[$value]]" autoplay="true"></youtube>
    <youtube media="http://www.youtube.com/watch?v=![CDATA[$value]]" autoplay="true"></youtube>
</videos>

This is the output I get from a database query

SELECT `key`,`value` FROM `jr_jryoutube_item_key` WHERE `key` = "youtube_id"

Result:

----------  -----------
key            value
----------  -----------
youtube_id  3VVAzFlmeWc
youtube_id  Rr9SfJwctRg
youtube_id  ocOZLHyOSZw
youtube_id  n-rQDYNOCyA
youtube_id  VaQlSnII-Hc

Below is my attempt.It's generating an xml file but it's not reading the youtube id's.

error_reporting(E_ALL);
     //database configuration
$_conf['jrCore_db_host'] = 'localhost';
$_conf['jrCore_db_port'] = '3306';
$_conf['jrCore_db_name'] = 'xxxxxx';
$_conf['jrCore_db_user'] = 'xxxxxx';
$_conf['jrCore_db_pass'] = 'xxxxxx';

       //connect to host
$con = mysqli_connect($_conf['jrCore_db_host'],$_conf['jrCore_db_user'],$_conf['jrCore_db_pass']);

      // Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
   }

      // select database

mysqli_select_db($con,$_conf['jrCore_db_name']);

echo "Connected successfully";

$myFile = "videos.xml";
$fh = fopen($myFile, 'wb') or die("can't open file");
$xml_txt .= '<videos>';

    // $query is an array with your values
$query = "SELECT `key`,`value` FROM `jr_jryoutube_item_key` WHERE `key` = 'youtube_id'";

$result = mysqli_query($con,$query);

if (!$result) {
    die('Invalid query: ' . mysqli_error($con));
}
if(mysqli_num_rows($result)>0)
{
    while($result_array = mysqli_fetch_assoc($result))

   {

      //loop through each key,value pair in row
      foreach($result_array as $key => $value)
        {          
         //embed the SQL data in a CDATA element to avoid XML entity issues
         $xml_txt .= '<youtube media="http://www.youtube.com/watch?v=![CDATA[$value]]" autoplay="true">';
         $xml_txt .= '</youtube>';  

         }

     }
}

$xml_txt .= '</videos>';
fwrite($fh, $xml_txt);
fclose($fh);
  • 写回答

1条回答 默认 最新

  • dongshao4207 2015-01-04 21:52
    关注

    You shouldn't use fwrite for xml files, there are more convenient methods available. For example, you can use simple_xml:

    $videoxml = new SimpleXMLElement("<videos></videos>");
    

    you are running a foreach loop in a while loop, which is wrong. Simply run the while loop and pick the value from the resultset:

    $url   = 'http://www.youtube.com/watch?v=' . $result_array['value'];
    $video = $videoxml->addChild('youtube');
    $video->{0} = $url;
    $video->addAttribute('autoplay', 'true');
    

    I hope I've interpreted your code right, but as far as I see there is no reason to pick "key" from DB as you don't use it.

    The code with the loops should look something like that:

    $xml = new SimpleXMLElement("<videos></videos>");
    
    foreach ($result as $row)
    {
        $url   = 'http://www.youtube.com/watch?v=' . $row['value'];
        $video = $xml->addChild('youtube');
        $video->{0} = $url;
        $video->addAttribute('autoplay', 'true');
    }
    
    $xml->saveXML('/path/to/file.xml')
    

    Please notice that I have given the value to the tag directly, not using two attributes and no content for the tag.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥20 做一个web书店,带答辩
  • ¥15 #求带 python,使用pycharm爬取智联招聘数据资源并保存为Excel文件,同时以图表形式呈现
  • ¥15 学校优化算法怎么样,国内用的多吗?
  • ¥15 微信购物商城购物车的所有功能以及购物车,和首页的搜索功能
  • ¥40 servlet的web程序部署出错
  • ¥50 activiti 新建流程系列问题
  • ¥50 为什么我版本升级之后运行速度变慢5倍??
  • ¥15 如何在gazebo中加载机械臂和机械手
  • ¥15 纯运放实现隔离采样方案设计
  • ¥20 easyconnect无法连接后缀带.com的网站,一直显示找不到服务器ip地址,但是带有数字的网站又是可以连接上的,如何解决