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.
You should a read on this - load a XML into MySQL http://dev.mysql.com/doc/refman/5.5/en/load-xml.html
This allow you to do something like this:
mysql> LOAD XML LOCAL INFILE 'items.xml'
-> INTO TABLE item
-> ROWS IDENTIFIED BY '<item>';
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 .= ', <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;
}
?>