Hi, I have been working on a site which uses a large XML database (70meg) and have decided against using SimpleXML to work with this and am going to convert the XML database to SQL so it is easier to work with. The database however with be changed once a month as it contains properties. So the database will be completely changed. How would i go about converting the database to SQL? Cheers, Adam
I already asked in your other threads for a better example of the XML file. It's hard to help you without it.
Write a script that uses SimpleXML to load the XML-file into SQL? That way you won't have to use SimpleXML more than once a months. The rest of the time you get the data from MySQL.
Here is an example of the XML: - <Property> <ID>14209</ID> <Partner_Id>15</Partner_Id> - <Name> - <![CDATA[ ]]> </Name> - <Reference> - <![CDATA[ NOBR15 ]]> </Reference> <Country>Portugal</Country> - <Region> - <![CDATA[ Madeira ]]> </Region> - <Location1> - <![CDATA[ Funchal ]]> </Location1> - <Location2> - <![CDATA[ ]]> </Location2> <Type>Villa</Type> <New_Development>0</New_Development> <Leaseback>0</Leaseback> <Investment>N</Investment> <Price>404750.00</Price> <Currency>GBP</Currency> <Bedrooms>5</Bedrooms> <Delivery_Date /> - <Short_Description> - <![CDATA[ ]]> </Short_Description> - <Long_Description> - <![CDATA[ A TRUE RURAL RETREAT IN A TRANQUIL SETTING AMONGST LUSH VEGETATION ! Split level Quinta /Vila - on the Funchal Border - only 12km away from Funchal and 600m above sea level with breathtaking sea and mountainviews ! Only 4 years old ! Large 7070 m2 grounds Building rights for at least another dwelling to be built on the land - eg. granny cottage or another residential home. Type: Residential Style: 2 Story Split "Modern Upmarket Vila Design" Bedrooms: 4 "WITH BUILT IN CUPBOARDS" Bathrooms: 3 "MAIN EN-SUITE" Garage: Triple "LOADS OF EXTRA PARKING" Basement: No Size: 495 m² Lot Type: Rectangular Lot Size: 7070 m² n/a "WITH PERMISSION FOR BUILDING ANOTHER HOME ON STAND" Has Suite: Yes Year Built: 2000 "AS NEW" Taxes: €0.00 EUR Condo Fees: €0.00 EUR ]]> </Long_Description> - <Small_Image_URL> - <![CDATA[ http://www.nobregarealty.com/Shared/Cache/Listing/114108/Photo/7-Gallery.img?_Version=632208272672200000 ]]> </Small_Image_URL> - <Large_Image_URL> - <![CDATA[ http://www.nobregarealty.com/Shared/Cache/Listing/114108/Photo/7-Gallery.img?_Version=632208272672200000 ]]> </Large_Image_URL> <Extra_Images /> - <Currencies> - <Currency> <Name>EUR</Name> <Price>599097.10</Price> </Currency> - <Currency> <Name>GBP</Name> <Price>404750.00</Price> </Currency> - <Currency> <Name>USD</Name> <Price>754848.94</Price> </Currency> </Currencies> </Property> Code (markup):
Create a table with all the fields you have in this piece of XML. And then do simply: $xml = simplexml_load_file('database.xml'); foreach ($xml->xpath('/properties/Property') AS $property) { $property = array_map('trim', (array)$property); extract($property); mysql_query(" INSERT INTO table_name (id, partner_id, name, ....) VALUES ('" . $id . "', '" . $Partner_id . "', '" . $Name . "', ....) ") OR die(mysql_error()); } PHP: This is just a raw example since I'm way too lazy to type all the field names and such, and since you're not able to provide a better example. So this will require a bit of tweaking but you might get the idea.
Hi, I have just realised that i have got a problem here. After looking at the XML date it seems to have extra details which i don't know how to split up. <Property> <ID>319441</ID> <Partner_Id>346</Partner_Id> <Name><![CDATA[]]></Name> <Reference><![CDATA[CerrNov-WNCV1051]]></Reference> <Country>Portugal</Country> <Region><![CDATA[CentralAlgarve]]></Region> <Location1><![CDATA[Paderne]]></Location1> <Location2><![CDATA[]]></Location2> <Type>Villa</Type> <Investment>N</Investment> <Price>250000.00</Price> <Max_Price>0.00</Max_Price> <Currency>EUR</Currency> <Bedrooms>3</Bedrooms> <Short_Description><![CDATA[Beautiful new 3 bedroom linked villa of an excellent design and high quality finishings. Fully equipped kitchen with Bosch electrical equipment (washing machine, dishwasher, cooker hob, oven, extractor, micro-wave and fridge/freezer) and many more extras.]]></Short_Description> <Long_Description><![CDATA[Beautiful new 3 bedroom linked villa of an excellent design and high quality finishings. Fully equipped kitchen with Bosch electrical equipment (washing machine, dishwasher, cooker hob, oven, extractor, micro-wave and fridge/freezer) and many more extras.]]></Long_Description> <Small_Image_URL><![CDATA[http://www.fisksinternational.co.uk/international/uploaded_images/47062_thumb.jpg]]></Small_Image_URL> <Large_Image_URL><![CDATA[http://www.fisksinternational.co.uk/international/uploaded_images/47062.jpg]]></Large_Image_URL> <Extra_Images> <Extra_Image_URL><![CDATA[http://www.fisksinternational.co.uk/international/uploaded_images/47063.jpg]]></Extra_Image_URL> <Extra_Image_URL><![CDATA[http://www.fisksinternational.co.uk/international/uploaded_images/47064.jpg]]></Extra_Image_URL><Extra_Image_URL><![CDATA[http://www.fisksinternational.co.uk/international/uploaded_images/47065.jpg]]></Extra_Image_URL></Extra_Images> <Currencies> <Currency> <Name>EUR</Name> <Price>250000.00</Price> </Currency> <Currency> <Name>GBP</Name> <Price>168900.00</Price> </Currency> <Currency> <Name>USD</Name> <Price>314994.41</Price> </Currency> </Currencies> </Property> Code (markup): Under <Extra_Images> there is 2 <Extra_Image_URL> and also under <Currencies> there are 3 different currency's. How would i go about adding these as they have the same field names and also i think that there could be only 1 extra image sometimes and also maybe more than 3 currency's.
www.php.net/simplexml Have a look at this site. It's explained there. I just converted the object into an array because I thought it was easier to work with, for you. $property->Extra_Images->Extra_Image_URL[0]; // And $property->Extra_Images->Extra_Image_URL[1]; PHP: Should work.
Right this is working fine now apart from one section which is for the extra images. The code is as follows: <?php include ("config/db.config.php"); $xml = simplexml_load_file('database/xml.xml'); foreach ($xml->xpath('/Properties/Property') AS $property) { $property = array_map('trim', (array)$property); extract($property); mysql_query("INSERT INTO property (id, partner_id, name, reference, country, region, location1, location2, type, new_development, leaseback, investment, price, currency, bedrooms, delivery_date, short_description, long_description, small_image_url, large_image_url, extra_images1, extra_images2) VALUES ('" . $id . "', '" . $Partner_Id . "', '" . $Name . "', '" . $Reference . "', '" . $Country . "', '" . $Region . "', '" . $Location1 . "', '" . $Location2 . "', '" . $Type . "', '" . $New_Development . "', '" . $Leaseback . "', '" . $Investment . "', '" . $Price . "', '" . $Currency . "', '" . $Bedrooms . "', '" . $delivery_date . "', '" . $Short_Description . "', '" . $Long_Description . "', '" . $Small_Image_URL . "', '" . $Large_Image_URL . "', '" . $property->Extra_Images->Extra_Image_URL[0] . "', '" . $property->Extra_Images->Extra_Image_URL[1] . "') ") OR die(mysql_error()); } ?> Code (markup): Any ideas why the extra images are not working? Cheers, Adam
I realize it's working now but what I found about year ago and can't go without anymore is a program that will convert many sorts of files into a mysql db. It does cost money but there is a free 30 day trial www.navicat.com It is a gui for mysql and importing a xml file is a matter of a few clicks. Try it...you will get hooked
I still seem to be having a problem with this one that i hope someone can help with. This is the code: $xml = simplexml_load_file('../database/PropertyInvestment.xml'); foreach ($xml->xpath('/Investments/Investment') AS $property) { $property = array_map('trim', (array)$property); extract($property); mysql_query("INSERT INTO investments (property_id, risk, location1, location2, type1, type2, type3) VALUES ('" . $Property_ID . "', '" . $Risk . "', '" . $property->Locations->Location[0] . "', '" . $property->Locations->Location[1] . "', '" . $property->Types->Type[0] . "', '" . $property->Types->Type[1] . "', '" . $property->Types->Type[2] . "') ") OR die(mysql_error()); } Code (markup): It won't add the values for $property->Types->Type[0], $property->Types->Type[1] etc and just adds a blank entry. Here is an example of the XML <?xml version="1.0" encoding="ISO-8859-2" ?><Investments><Investment><Property_ID>49758</Property_ID><Risk>Medium</Risk><Locations><Location>Coastal</Location></Locations><Types><Type>Buy to let - Tourism</Type><Type>Buy to turn</Type><Type>Low deposit</Type></Types></Investment><Investment><Property_ID>63389</Property_ID><Risk>Medium</Risk><Locations><Location>Rural</Location></Locations><Types><Type>Buy to let - Residential</Type></Types></Investment><Investment><Property_ID>63390</Property_ID><Risk>Low</Risk><Locations><Location>Rural</Location></Locations><Types><Type>High rental yield</Type><Type>Holiday usage</Type><Type>Low cost investment</Type></Types></Investment><Investment><Property_ID>63548</Property_ID><Risk>Medium</Risk><Locations><Location>Mountain</Location></Locations><Types><Type>Holiday usage</Type></Types></Investment><Investment><Property_ID>63589</Property_ID><Risk>Medium</Risk><Locations><Location>Coastal</Location></Locations><Types><Type>Holiday usage</Type><Type>Low cost investment</Type></Types></Investment><Investment><Property_ID>64221</Property_ID><Risk>Low</Risk><Locations><Location>Mountain</Location><Location>Rural</Location></Locations><Types><Type>Buy to turn</Type><Type>Capital appreciation</Type><Type>Holiday usage</Type><Type>Low cost investment</Type></Types></Investment><Investment><Property_ID>64238</Property_ID><Risk>Low</Risk><Locations><Location>Rural</Location></Locations><Types><Type>Buy to turn</Type><Type>Capital appreciation</Type><Type>Holiday usage</Type><Type>Low cost investment</Type></Types></Investment><Investment><Property_ID>68956</Property_ID><Risk>Medium</Risk><Locations><Location>Rural</Location></Locations><Types><Type>High rental yield</Type><Type>Holiday usage</Type><Type>Low cost investment</Type></Types></Investment><Investment> Code (markup): Anyone know why this is not working? Cheers in advanced for your help.