从sql数据库创建简单的xml文件

Question

i'm a newbee to sql code .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="false"></youtube>
<youtube media="http://www.youtube.com/watch?v=5wx0GfbC0BA" autoplay="false"></youtube>
<youtube media="http://www.youtube.com/watch?v=ERGrSQoY5fs" autoplay="false"></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"

   
----------  -----------
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.

 <?php

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);
 ?>

</div>

Answer 1

$query = mysql_query("SELECT `value` FROM `jr_jryoutube_item_key` WHERE `key` = "youtube_id"");

// $query is an array with your values

// sprintf link template, %s is where youtube_id goes
$sprintf_link = '<youtube media="http://www.youtube.com/watch?v=%s" autoplay="true">';

// iterate the query row-wise
foreach($query as $row_idx => $row)
{  
    // fetch value
    $youtube_id = $row['value']; 

    // build link
    $xml_txt .= sprintf($sprintf_link, $youtube_id);  
}

This doesn't answer the part, where you concatenate / add the CDATA parts. It's just additional data from the db, which you might concatenate to the string or insert with sprintf().

If the SQL query is true, then your DB table structure can be optimized. You a querying and replicating the string "youtube_id" over all rows. You might use a table "youtube_id" with "index" (auto_increment) and "key" (string length 11).