read - php xml




Import XML into MySQL 5.1 (6)

I happend to stumble across this PHP and AJAX example a little while back - it might get you pointed in the right direction, but with that much data, you might want to consider importing it into a database and use it's searching capabilities- that's what they're designed for, whereas performance will likely be an issue chugging through that much plain text of an XML file. Take a look at this answer for XML importing. Also found this SO answer about importing GCIDE XML.

Please excuse my lack of knowledge... I know there is a lot of documentation on the internet related to this but I still don't understand.

My situation is this:

I have an XML file that I need import and eventually replace daily with.

    <item>
        <model>AA311-Pink</model>
        <title>1122</title>
        <price>19.43</price>
        <category>cat</category>
        <loc>/AA311.html</loc>
        <image>/aa311.jpg</image>
        <description>Item Info</description>
        <weight>0.45</weight>
        <option_type>Color-Color</option_type>
        <option_value>Pink-Pink</option_value>
        <suggested_retail>51.50</suggested_retail>
        <special_handling/>
        <manufacturer>Tantus</manufacturer>
        <manufacturer_code>VB5074 and VB5067</manufacturer_code>
        <packaging>Retail Packaging</packaging>
        <in_stock>Yes</in_stock>
        <lastupdated>2008-11-05 16:35:56</lastupdated>

I need to change a handful of the column names automatically and import them into multiple tables in my database.

For instance,

    <item>
        <products_model>AA315</products_model>
        <products_name>name</products_name>
        <price>19.43</price>
        <category>cat</category>
        <loc>/AA315.html</loc>
        <products_image>aa315.jpg</products_image>
        <products_description>info</products_description>
        <products_weight>0.44</products_weight>
        <option_type/>
        <option_value/>
        <products_price>51.50</products_price>
        <special_handling/>
        <manufactures_name>Tantus</manufactures_name>
        <manufacturer_code>VA5104</manufacturer_code>
        <packaging>Retail Packaging</packaging>
        <products_status>Yes</products_status>
        <products_last_modified>2008-11-05 16:35:27</products_last_modified>

And then import into MySQL DB

Columns: products_weight, products_model, products_image, products_price, products_last_modified

import into table 'products'

Columns: products_description, products_name

import into table 'product_description

Also what about the product_id that is automatically created? I can send SQL output of table structure.

I really apprecaite the help... I am willing to pay some if they are willing to create a fully automated procedure to import this file into my database; I am using Zen Cart to host my shopping cart.


No paying required, Using XML in MySQL 5.1 and 6.0 will answer most of your questions. Also, go back to the top and read the entire page, you can do a lot with XML and MySQL.


Yes thanks gx, http://web.archive.org/web/20100105150533/http://dev.mysql.com/tech-resources/articles/xml-in-mysql5.1-6.0.html#xml-5.1-importing did it for me. I used the stored procedure mentioned there, it however has a small bug if you want to import into another table than 't1'. Just replace line

SET @ins_text = CONCAT('INSERT INTO t1 (', ins_list, ') VALUES (', val_list, ')');

with

SET @ins_text = CONCAT('INSERT INTO ', database_name, '.', table_name, ' (', ins_list, ') VALUES (', val_list, ')');

Execute the procedure via

call xmldump_load('<filename>', '<schema>', '<tablename>');

Before calling this procedure make sure the file to import is accessible, for instance in the data folder of mysql (/var/lib/mysql/) and execute it with a user with FILE grant.



How can i insert data from an XML into a database

For the PHP approach, you will find the following useful:

<?php
$string = <<<XML
  <PrintLetterBarcodeData 
    uid="725733706873" 
    name="RAVINDER KUMAR" 
    gender="M" 
    yob="1996" 
    co="S/O KAKA RAM" 
    house="460A" 
    street="WARD NO. 6" 
    lm="NA" 
    loc="NA" 
    vtc="Nanyola (292)" 
    po="Naneola" 
    dist="Ambala" 
    state="Haryana" 
    pc="134003"
  />
XML;


$xml = simplexml_load_string($string);
$attribs = $xml->attributes();
// convert the '$attribs' to an array
foreach($attribs as $key=>$val) {
    $arrayOfAttribs[(string)$key] = "'".(string)$val."'";
}
$namesOfColumns = implode(",", array_keys($arrayOfAttribs));
$valuesOfColumns = implode(",", array_values($arrayOfAttribs));

// your database stuff
$query = "INSERT INTO yourtable ($namesOfColumns) VALUES ($valuesOfColumns);";
?>

How to search inside GCIDE XML using PHP

Your project intrigued me, and thought I might find it useful sometime, so did some research, and found the below code on this page. I ran this php, and currently have a fully functional dictionary in my database!

Here's everything I did to get it up and running (I unzipped the XML files into a folder called XML within the folder that contains these files).

SQL for Table - gcide

CREATE TABLE `gcide` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `word` varchar(255) DEFAULT NULL,
  `definition` text,
  `pos` varchar(50) DEFAULT NULL,
  `fld` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `word` (`word`)
) ENGINE=MyISAM

PHP for gcide XML Import - import_gcide_xml.php

 <?php
    $connection = mysql_connect('localhost', 'root', '') or die('Could not connect to MySQL database. ' . mysql_error());
    $db = mysql_select_db('fiddle',$connection);

    mysql_query('TRUNCATE TABLE gcide') or die(mysql_error());

    $xml = array('xml/gcide_a.xml', 'xml/gcide_b.xml', 'xml/gcide_c.xml', 'xml/gcide_d.xml', 'xml/gcide_e.xml','xml/gcide_f.xml','xml/gcide_g.xml', 'xml/gcide_h.xml', 'xml/gcide_i.xml', 'xml/gcide_j.xml', 'xml/gcide_k.xml', 'xml/gcide_l.xml', 'xml/gcide_m.xml', 'xml/gcide_n.xml', 'xml/gcide_o.xml', 'xml/gcide_p.xml', 'xml/gcide_q.xml', 'xml/gcide_r.xml', 'xml/gcide_s.xml', 'xml/gcide_t.xml', 'xml/gcide_u.xml', 'xml/gcide_v.xml', 'xml/gcide_w.xml', 'xml/gcide_x.xml', 'xml/gcide_y.xml', 'xml/gcide_z.xml');
    $numberoffiles = count($xml);

    for ($i = 0; $i <= $numberoffiles-1; $i++) {
        $xmlfile = $xml[$i];
        // original file contents
        $original_file = @file_get_contents($xmlfile);
        // if file_get_contents fails to open the link do nothing
        if(!$original_file) {}
        else {
            // find words in original file contents
            preg_match_all("/<hw>(.*?)<\/hw>(.*?)<def>(.*?)<\/def>/", $original_file, $results);
            $blocks = $results[0];
            // traverse blocks array
            for ($j = 0; $j <= count($blocks)-1; $j++) {
                preg_match_all("/<hw>(.*?)<\/hw>/", $blocks[$j], $wordarray);
                $words = $wordarray[0];
                $word = addslashes(strip_tags($words[0]));
                $word = preg_replace('{-}', ' ', $word);
                $word = preg_replace("/[^a-zA-Z0-9\s]/", "", $word);
                preg_match_all("/<def>(.*?)<\/def>/", $blocks[$j], $definitionarray);
                $definitions = $definitionarray[0];
                $definition = addslashes(strip_tags($definitions[0]));
                $definition = preg_replace('{-}', ' ', $definition);
                $definition = preg_replace("/[^a-zA-Z0-9\s]/", "", $definition);
                preg_match_all("/<pos>(.*?)<\/pos>/", $blocks[$j], $posarray);
                $poss = $posarray[0];
                $pos = addslashes(strip_tags($poss[0]));
                $pos = preg_replace('{-}', ' ', $pos);
                $pos = preg_replace("/[^a-zA-Z0-9\s]/", "", $pos);
                preg_match_all("/<fld>(.*?)<\/fld>/", $blocks[$j], $fldarray);
                $flds = $fldarray[0];
                $fld = addslashes(strip_tags($flds[0]));
                $fld = preg_replace('{-}', ' ', $fld);
                $fld = preg_replace("/[^a-zA-Z0-9\s]/", "", $fld);

                $insertsql = "INSERT INTO gcide (word, definition, pos, fld) VALUES ('$word', '$definition', '$pos', '$fld')";
                $insertresult = mysql_query($insertsql) or die(mysql_error());

                echo $word. " " . $definition ."\n";
            }
        }
    }
    echo 'Done!';
?>

CSS For Search Page - gcide.css

body{ font-family:Arial, Helvetica, sans-serif; }
#search_box { padding:4px; border:solid 1px #666666; margin-bottom:15px; width:300px; height:30px; font-size:18px;-moz-border-radius: 6px;-webkit-border-radius: 6px; }
#search_results { display:none;}
.word { font-weight:bold; }
.found { font-weight: bold; }
dl {    font-family:serif;}
dt {    font-weight:bold;}
dd {    font-weight:normal;}
.pos {    font-weight: normal;}
.fld {    margin-right:10px;}

HTML for Search Page - index.html

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
        <title>PHP, jQuery search of GCIDE</title>
        <link href="gcide.css" rel="stylesheet" type="text/css"/>
        <link href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8/themes/ui-lightness/jquery-ui.css" rel="stylesheet" type="text/css"/>
        <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.7.1/jquery.min.js"></script>
        <script src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8/jquery-ui.min.js"></script>
        <script type="text/javascript">
            $(function() {
                $("#search_box").keyup(function() {
                    // getting the value that user typed
                    var searchString    = $("#search_box").val();
                    // forming the queryString
                    var data            = 'search='+ searchString;
                    // if searchString is not empty
                    if(searchString) {
                        // ajax call
                        $.ajax({
                            type: "POST",
                            url: "gcide_search.php",
                            data: data,
                            beforeSend: function(html) { // this happens before actual call
                                $("#results").html('');
                                $("#search_results").show();
                                $(".word").html(searchString);
                            },
                            success: function(html){ // this happens after we get results
                                $("#results").show();
                                $("#results").append(html);
                            }
                        });
                    }
                    return false;
                });
            });
        </script>
    </head>
    <body>
        <div class="ui-widget-content" style="padding:10px;">
            <input id="search_box" class='search_box' type="text" />
            <div id="search_results">Search results for <span class="word"></span></div>
            <dl id="results"></dl>
        </div>
    </body>
</html>

PHP for jQuery Search - gcide_search.php

<?php
    if (isset($_POST['search'])) {
        $db = new pdo("mysql:host=localhost;dbname=fiddle", "root", "");
        // never trust what user wrote! We must ALWAYS sanitize user input
        $word = mysql_real_escape_string($_POST['search']);
        $query = "SELECT * FROM gcide WHERE word LIKE '" . $word . "%' ORDER BY word LIMIT 10";
        $result = $db->query($query);
        $end_result = '';
        if ($result) {
            while ( $r = $result->fetch(PDO::FETCH_ASSOC) ) {
                $end_result                 .= '<dt>' . $r['word'];
                if($r['pos'])   $end_result .= ',&nbsp;<span class="pos">'.$r['pos'].'</span>';
                $end_result                 .= '</dt>';
                $end_result                 .= '<dd>';
                if($r['fld'])   $end_result .= '<span class="fld">('.$r['fld'].')</span>';
                $end_result                 .= $r['definition'];
                $end_result                 .= '</dd>';
            }
        }
        if(!$end_result) {
            $end_result = '<dt><div class="ui-state-highlight ui-corner-all" style="margin-top: 20px; padding: 0 .7em;">
            <p><span class="ui-icon ui-icon-info" style="float: left; margin-right: .3em;"></span>
            No results found.</p>
            </div></dt>';
        }
        echo $end_result;
    }
?>




xml