need help

chippyb · Aug 18, 2011

Hi Guys

I need help with mysql really, i have 2 hotel sites up using a live url xml feed, its ok buts it slow and cant display all the data as it takes to long to load, so basicly can some explain to me either how to import my url xml feed into mysql and pull the data from mysql to my site or either import the csv file to my sql and pull the data down, i have tried and cant work it out at all, if someone could show me or teach me once that would be great for my future sites,
Thanks

grantw · Aug 18, 2011

If you post the code that isn't working someone may be able to tell you what's wrong with it. I doubt anyone is going to write the code out for you from scratch.

Grant

chippyb · Aug 18, 2011

thats fine, well this is what i have so far and it works using the url live feed, but its very slow.

<?php
// specify url of xml file
$url = "http://xmlfeed.laterooms.com/index.aspx?aid=*****&rtype=4&kword=milton+keynes";
// get xml file contents
$xml = simplexml_load_file($url);

// begin new paragraph

echo "<table width=\"100%\">";
echo "<tr><th>Hotel Name</th>";
echo "<th>Accommodation Type</th> ";
echo "<th>HotelStar</th>" ;
echo "<th>Prices From</th>";
echo "<th>View</th></tr>";
echo "</hr>";

// loop begins
$red = 1;
foreach($xml->hotel as $hotel)

{

echo "<tr class='" . ($red?'red':'blue') . "'><td>";
echo $hotel->hotel_name."</td><td>";
echo $hotel->star_accomodation_type."</td><td>";
echo $hotel->hotel_star."</td><td>";
echo "£".$hotel->prices_from."</td><td>";
echo "<a href=".$hotel->hotel_link.">View Hotel</a>"."</td></tr>";

$red = !$red;
// end paragraph

}
echo "</table>";

// loop ends

?>

aquanuke · Aug 18, 2011

Most efficient way to load into mysql is through mysql load data infile function

Needs to be in csv format so either get the feed in csv or convert the xml with php

http://dev.mysql.com/doc/refman/4.1/en/load-data.html

chippyb · Aug 18, 2011

Yea that what i dont know how to do or display it after this is what im after help for, is this something i need to offer money for?

aquanuke · Aug 19, 2011

mysql is dead easy to use lots of examples on the web. Once you have inserted the data in then just use mysql select

http://www.tizag.com/mysqlTutorial/mysqlselect.php

grantw · Aug 19, 2011

You need another php file based on the code you posted.

The new file needs amending so that the data is inserted into the database rather than displayed with echos. You then setup a cron job to run this file once a day or whatever to automatically update the database.

The existing file then needs amending to fetch the data from the database rather than the xml feed.

To be honest, you seem to have no idea what you're doing, I'd suggest getting someone to do it from a freelance site, the basic coding will probably cost you £20.

Grant

chippyb · Aug 19, 2011

I really dont want to use someone else product like that wordpress, im quite happy using my design and not someone else's, im just trying to learn so I can make this easier for the future sites

chippyb · Aug 19, 2011

Haha yea it is cheap but i dont have £50 lol, buying a new car tomorrow so im wiped out at the mo!
I am completely new to it, html css all fine but php and mysql no idea, everytime i search the net for it i only find things that are no good to me.

AssetDomains · Aug 19, 2011

Thank you for the recommendation Chris.
Chippy theirs plenty of code snippets out there to make your solution work google is your friend here.
Some pointers from what I've learned putting together my theme.
The live XML is not what you want to build your framework laterooms tell you as much. You either need the massive static XML or the csv file I'd recommend the later much more manageable.
As for getting the data back out If your coding your own solution again your going to have to use google again to get examples.
Have a look at http://www.w3schools.com/sql/default.asp maybe work through this section with there demo code.
think again about wordpress or another cms millions of sites built around such frameworks for a reason they provide a great solid base to start from and are hugely customizable.

chippyb · Aug 19, 2011

for some reason very time I put the import the data into mysql from a csv file it either all goes into one coloum or all the coloums are empty? i thought as i was editing the data first that was the prob so i just used it as i got from laterooms and its the same problem

AssetDomains · Aug 19, 2011

post your import code I'll have a look im far from expert in these matters I used a plugin to handel the import in my solution.

chippyb · Aug 19, 2011

im using the gui on my website host in myphpadmin

AssetDomains · Aug 19, 2011

I just done a test of one of my csv's using the import tool in the phpmyadmin and it worked like a charm.
The option csv selected automaticaly when i browsed to the file then I chose the option that said use header as colomn names.
This was on a blank database with no tables created which i think makes a difference.

chippyb · Aug 19, 2011

hmmm im starting to think mine is different than yours?! as mine doesnt let me seem to do that, does yours have an upload file size limit, im having to cut mine down as it limits me to 8mb and well the file is 40mb and wont upload at that size,
Im hosted by one.com, anyone else have that issue?

Thanks for your help

AssetDomains · Aug 19, 2011

Yes but mine is 105mb that shouldn't be an issue though you really don't need to upload the whole UK csv for most sites.
For example the London site in your sig would only have the 700 or so hotels. Use excel to filter the city column for london etc copy the results and save as a new csv london.csv to upload make sure keep the header rows however so they can be used as your column headers in your database.