For some reason my prev thread is not approved here. no idea why.
Let me do it again.
I have 4 different XML files with the same format. I want to parse this files and enter them into the MySQL DB on regular basis (once a month).
My site will have a search box on index.html, so any user searches for 'BON JOVI' the system will pick all the listings with the keyword and display it on to the page.
Bottomline: I have no idea how and where do I start. I need to start this asap. Pls help me!! a starting point for me would be of gr8 help.
Sample XML code is here
<?xml version="1.0" encoding="utf-8"?>
<listings>
<listing>
<category>Rock and Pop</category>
<id>311709</id>
<performer_id>4290</performer_id>
<title>2000 Trees Festival in Cheltenham, United Kingdom</title>
<url>http://www.abc.com/tickets/2000-trees-festival-tickets/cheltenham-64314.html</url>
<address />
<city>Cheltenham</city>
<country>United Kingdom</country>
<state />
<zip_code />
<description>2000 Trees Festival tickets for 11/07/2008</description>
<event_date>2008-07-11</event_date>
<event_time>19:00:00</event_time>
<event_name>2000 Trees Festival</event_name>
<event_venue>Upcote Farm</event_venue>
<event_row>-</event_row>
<event_section>Adult Weekend Ticket</event_section>
<image_url>http://www.abc.com/images/category_2.jpg</image_url>
<number_available>6</number_available>
<price>86.90</price>
</listing>
</listings>Help really appreciated.
Thanks
dave
Hi David!
I got some reference from the this post here http://www.magicparser.com/node/253
and i have come up with this code to insert xml into mysql. do you find any errors or something which i would have done wrong?
<?php
require("MagicParser.php");
$filename = "all.xml";
function myRecordHandler($product)
{
$sql = "
INSERT INTO music SET
listings = '".mysql_real_escape_string( $product["LISTINGS"] )."',
listing = '".mysql_real_escape_string( $product["LISTING"] )."',
category = '".mysql_real_escape_string( $product["CATEGORY"] )."',
id = '".mysql_real_escape_string( $product["ID"] )."',
performer_id = '".mysql_real_escape_string( $product["PERFORMER_ID"] )."',
title = '".mysql_real_escape_string( $product["TITLE"] )."',
url = '".mysql_real_escape_string( $product["URL"] )."',
address = '".mysql_real_escape_string( $product["ADDRESS"] )."',
city = '".mysql_real_escape_string( $product["CITY"] )."',
country = '".mysql_real_escape_string( $product["COUNTRY"] )."',
state = '".mysql_real_escape_string( $product["STATE"] )."',
zip_code = '".mysql_real_escape_string( $product["ZIP_CODE"] )."',
description = '".mysql_real_escape_string( $product["DESCRIPTION"] )."',
event_date = '".mysql_real_escape_string( $product["EVENT_DAVE"] )."',
event_time = '".mysql_real_escape_string( $product["EVENT_TIME"] )."',
event_name = '".mysql_real_escape_string( $product["EVENT_NAME"] )."',
event_venue = '".mysql_real_escape_string( $product["EVENT_VENUE"] )."',
event_row = '".mysql_real_escape_string( $product["EVENT_ROW"] )."',
event_section = '".mysql_real_escape_string( $product["EVENT_SECTION"] )."',
image_url = '".mysql_real_escape_string( $product["IMAGE_URL"] )."',
number_available = '".mysql_real_escape_string( $product["NUMBER_AVAILABLE"] )."',
price = '".mysql_real_escape_string( $product["PRICE"] )."',
";
mysql_query($sql) or die(mysql_error());
echo "Data Inserted!";
}
MagicParser_parse($filename,"myRecordHandler","xml|listing/listings/");
?>Thanks David. Help appreciated. along with this i have created a table called 'music' with fields in there.
dave
Hello Dave,
You're certainly along the right lines, but what your script is missing is the MySQL connection code where you establish a connection to the database with an appropriate username and password.
You would normally need something like the following at the top of your script (at least before you start to parse the file anyway...)
$link = mysql_connect("localhost","username","password");
mysql_select_db("mydatabase",$link);The first parameter in the call to mysql_connect is the database server, which is normally "localhost" (i.e. the same computer as your web server is running on); however sometimes it is different and requires a full host name. If this is the case, you should have some information provided by your host as to what you need to use. Likewise with the username and password.
Hope this helps!
Cheers,
David.
Hello Dave,
There is quite a lot to what you want to do.
To begin with, have you ever written any PHP > MySQL code at all? The first thing to do is to design your database table based on the fields you want to insert, and to start with I would try writing a basic script to insert a test row before moving onto the parsing and inserting.
The best place to start is the PHP manual, which has good examples for MySQL coding - have a look at:
http://uk.php.net/manual/en/function.mysql-query.php
Cheers,
David.