mbk

Hi, Thanks for the response. Reason behind is that I get a feed from a supplier of my product data which is in the fulldata table. I then get feeds from my suppliers of goods they stock, however this isnt as in depth as the product data, so I am basically cross referencing the data from one table to the other, so I end up with a complete list of goods my suppliers will supply me and where applicable it will contain in depth product data. Is there an easier way to do it?

Thanks for the response OK, in my table fulldata, m_prod_id contains a part number, then the image field contains a link to the image of that relevant part. My Supplier table contains an m_prod_id field, but no link to the image in the image field. What I want is to use the m_prod_id in the supplier table to query the fulldata table, if it exists in the fulldata table then copy the image field from the fulldata table to the supplier table in the image field, if it doesnt exist then insert NULL and continue. Have I made any sense?

Can anyone help pls?

Hi, I have two tables, fulldata and suppliers, fulldata contains all data available from my vendor, suppliers contains the data that my suppliers stock. within the fulldata table is a field called image, which I want to include in the suppliers table, with the common field between the two being m_prod_id. I have the following code, but am stuck as to how I can assign the relevant m_prod_id the correct image value, also some m_prod_id's will not have an image value. select fulldata.image from fulldata, suppliers where fulldata.m_prod_id = suppliers.m_prod_id Can someone help pls?

Thanks Harris - all sorted now.

sorry... $sql="INSERT INTO $table (category, supp_pn, desc)VALUES('$category', '$supp_pn', '$desc')"; mysql_query($sql)or die (mysql_error()); MySQL version 5.0.75

Thanks for the quick reply - I made the amend and now get the following error.. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'desc)VALUES('', 'FUNDHD ', 'FUNDHD ')' at line 1

Hi, I am trying to parse a csv file and insert the contents into a mysql table. I could use the insert local file option, but I dont want all the columns importing. I have the following code, but this doesnt seem to work. <?php $table = 'suppliers'; $username = "user"; $pass = 'pass'; $db = mysql_connect("localhost", $username, $pass); mysql_select_db("testdb",$db); $empty_table = "TRUNCATE TABLE $table"; mysql_query($empty_table) or die (mysql_error()); $file_handle = fopen("file.csv", "r"); while (!feof($file_handle) ) { $tmp = fgetcsv($file_handle, 8192); $category = ($tmp[0]); $supp_pn = ($tmp[1]); $desc = ($tmp[3]); $help = ($tmp[5]); $sql = "INSERT INTO $table SET category='$category', supp_pn='$supp_pn', desc='$desc', $help='help'"; mysql_query($sql)or die (mysql_error()); } fclose($file_handle); ?> An example of the data in the csv is below - "","FUNDHD ","","FUNDHD ","",0.00,0.00,0 "[Pending Assignment]","ATS0057 "," ","AT-FS705LE-30 ","Hama UK Ltd ",17.31,29.80,0 "[Pending Assignment]","MARKETING "," ","MARKETING ","Hama UK Ltd ",20000.00,30000.00,0 Can someone help pls?

Schweppes - thanks for the info and for the link. I still havent managed to solve this though. I am looking to get a value from within an element that is not surrounded by the <> but is a string always placed between the same two points, the start point is an = and the close point is Club. I also need to remove the quotes surrounding the text to be selected. I think I will need a Regex statement to complete this, but am not sure.

I have looked through the information that you directed me through but there doesnt seem to be a way documented that will allow me to search within the element and extract the specific data, as they arent actual children within the element. I am thinking it may be some sort of regex based on the element that needs to be done??

Hi All, Another new issue I have now! I have the following XML code and am needing to extract certain parts of the element <contact> however there are various different fields, and the field I need is also repeated in another element in the same page. I need to extract the position, the club and nationality from the following, but dont really know where to begin. <Contact FirstName="Jim" Surname="Jones" Age="32" Position="Forward" Club="Bury" Weight="12st 3lb" Height="6ft 2in" Appearances="28" Sub="14" Goals="22" SquadNumber="16" Nationality="Jamaican"> <Related FirstName="Gary" Surname="Jones" Age="38" Position="Forward" Club="Tranmere Rovers" Weight="13st 1lb" Height="6ft 4in" Appearances="2" Sub="14" Goals="4" SquadNumber="12" Nationality="Welsh"> </Related> </Contact> All help appreciated.

Thanks for the reply, now have everything working fine. Just as a note, I did try the load data method and this also worked fine, as well as being very fast. Your help was much appreciated.

thanks for the replies. OK - I have continued with the original script and have successfully imported 120,000 of the 400,000 records Then I get this error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '4 INCH DIGI', quality='ICECAT'' at line 1 Full code and DB structure is below <?php // Set Mysql Variables //$host = 'host'; //$user = 'user'; //$pass = 'pass'; //$db = 'testdb'; $table = 'on_market_dump'; $username = "user"; $pass = 'pass'; $db = mysql_connect("host", $username, $pass); mysql_select_db("testdb",$db); //mysql_connect($host,$user,$pass) or die(mysql_error()); $empty_table = "TRUNCATE TABLE $table"; mysql_query($empty_table) or die (mysql_error()); $file = "on_market.export_urls_rich.txt"; $fp = fopen($file, "r"); $data = fread($fp, filesize($file)); fclose($fp); $output = str_replace("\t|\t", "|", $data); $output = explode("\n", $output); foreach($output as $var) { $tmp = explode("\t", $var); $productid = $tmp[0]; $prodid = $tmp[1]; $quality = $tmp[2]; $url_spec = $tmp[3]; $supplier_id = $tmp[4]; $highres = $tmp[5]; $lowres = $tmp[6]; $thumbnail = $tmp[7]; $uncatid = $tmp[8]; $catid = $tmp[9]; $manu_pn = $tmp[10]; $ean_upcs = $tmp[11]; $modelname = $tmp[12]; $onmarket = $tmp[15]; $countries = $tmp[16]; $updated = $tmp[17]; $sql = "INSERT INTO $table SET productid='$productid', prodid='$prodid', quality='$quality'"; mysql_query($sql)or die (mysql_error()); } echo "Done!"; //The MySQL looks like this: //CREATE TABLE `testdb`.`on_market_dump` ( //`productid` VARCHAR( 255 ) NOT NULL , //`prodid` VARCHAR( 255 ) NOT NULL , //`quality` VARCHAR( 255 ) NOT NULL //) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci; ?>

Catfish - thanks for the response. I have partially got it working now, although strangely needed to include the line $output = str_replace("\t|\t", "|", $data); which was initially commented out. I am now wondering if there is an easier way to simply import the whole file into mysql, but maintain the relevant fields. Instead of creating an array and picking out certain points, simply just drop the whole file in. Is there a simpler way? Thanks for your help.

Thanks for the reply. When I open the file, there is just white space between each field and then the same again between each record. If I open it in Open Office Spreadsheet and set it to tab delimited then it automatically formats the fields and records correctly which leads me to believe there is some sort of end of record. I have pasted the first two records - the header and first proper record below. product_id prod_id Quality URL supplier_id High_res_img Low_res_img Thumbnail_img UNCATID Category_ID m_prod_id ean_upcs model_name original_supplier_id product_view on_market country_market_set Updated 10657 PA-T3+= ICECAT http://prf.icecat.biz/index.cgi?product_id=10657;mi=start;smi=product; 11 http://images.icecat.biz/img/norm/high/10657-3366.jpg http://images.icecat.biz/img/norm/low/10657-3366.jpg http://images.icecat.biz/thumbs/10657.jpg 43201409 182 PA-T3+= 746320213880;0746320213880 1-Port Adapter T3 11 1 NL;BE;FR;UK;DE;DK;PL;HU;FI;NO;SE;CH;IT;US;AU 20100215225607

I have a Tab delimited txt file that I need to import into a mysql database, however I am having an issue in that I dont think there is an end of line character within the file. The code below is something i have picked up via google and made a couple of amends to, can someone please look at this and see if they can see anything obviously wrong with it? The txt file itself is in the same directory at the mintue as the php file and when opened with either open office word or spreadsheet doesnt contain any | between fields, just tabs. I would include a link to the txt file however it is 75MB. Thanks <?php // Set Mysql Variables //$host = 'localhost'; //$user = 'root'; //$pass = 'ASsC9XlI'; //$db = 'testdb'; $table = 'on_market_dump'; $username = "user"; $pass = 'mypass'; $db = mysql_connect("localhost", $username, $pass); mysql_select_db("testdb",$db); //mysql_connect($host,$user,$pass) or die(mysql_error()); $empty_table = "TRUNCATE TABLE '$table'"; mysql_query($empty_table) or die (mysql_error()); $file = "on_market.export_urls_rich.txt"; $fp = fopen($file, "r"); $data = fread($fp, filesize($file)); fclose($fp); //$output = str_replace("\t|\t", "|", $data); $output = explode("\n", $output); //mysql_connect($host,$user,$pass) or die(mysql_error()); foreach($output as $var) { $tmp = explode("\t", $var); $productid = $tmp[0]; $prodid = $tmp[1]; $quality = $tmp[2]; $url_spec = $tmp[3]; $supplier_id = $tmp[4]; $highres = $tmp[5]; $lowres = $tmp[6]; $thumbnail = $tmp[7]; $uncatid = $tmp[8]; $catid = $tmp[9]; $manu_pn = $tmp[10]; $ean_upcs = $tmp[11]; $modelname = $tmp[12]; $onmarket = $tmp[15]; $countries = $tmp[16]; $updated = $tmp[17]; $sql = "INSERT INTO $table SET productid='$productid', prodid='$prodid', quality='$quality'"; mysql_query($sql)or die (mysql_error()); } echo "Done!"; ?>

Hi all, I need to create a curl loop that increments and uses the increment to access pages. URL of www.blah.com/index.asp?p=1 The number changes for each post on the site and some posts dont exist, ie p=4 doesnt exist it just brings a message up saying Post you are looking for doesnt exist. So what I need to do is have a script that takes the base URL upto = and then increment the counter each time but also if the page comes back post doesnt exist then skip this... Can anyone help pls? thanks

OK - but what I am initially looking for is a way to retrieve the URLs I wish to visit. Which I guess is the sitemap isnt it... Think I need to look into the sitemap more. Thanks for info re scraping too, its OK though.

Thanks Neil, Just want to clarify that by using cUrl I will be able to in effect search for the prdno from my original post and action them if found? sorry if this is a simple question just want to make sure I am reading the correct info. At present the directories beyond the domain are all virtual listings and thus inacessible, so I need to find a way of generating the prdno and trying to access the page. cUrl is the way to go? Thanks

sKunKbad - thanks for the quick reply. I tried using the crawler you suggested to see if it worked on the site in question so I could then look at the source, however it didnt work, it merely returned the root url. Any other suggestions pls?

I am trying to create a crawler and have worked out how to extract the data I need from the site using regex, however I havent yet worked out how to access the site! I know, wrong way round, but still... The pages I need to crawl are all of the following makeup:- http://www.blah.co.uk/applications/blah/details.asp?prdno=xyz Issue is that I dont know how many different prdnos there are, but am looking for a way to crawl through starting from 0 and if it exists then access data if not then move on to the next. Can someone help me please?

Thanks for that cags.

Rajiv, Cags thanks for your help, Have now got:- <?php $str = $input preg_match_all("#rvo_model='(.*?)';#", $str, $matches); print_r($matches[1]); ?> Only thing was, using preg_match_all and print_r I get Array ( [0] => MFC5860CNU1 ) I only want the MFC5860CNU1. The only way I have found round this is to change to a preg_match and use echo instead of print_r Whats the main difference between preg_match and preg_match_all? Thanks guys for the help.

Thanks Rajiv, However, it is the code being assigned to $str that can change all the time. So one time it may be <script type="text/javascript"> rvo_retailer_ref='msc'; rvo_manufacturer='Brother'; rvo_model='MFC5860CNU1'; rvo_format='image_horizontal310x70'; </script> and another time it may be <script type="text/javascript"> rvo_retailer_ref='msc'; rvo_manufacturer='blahblah'; rvo_model='blahblah'; rvo_format='image_horizontal310x70'; </script> I just need to work out how to correctly assign to $str without hard coding it.

Rajiv, Thanks for the quick response. The definitions of rvo_manufactuer and rvo_model change all the time, so I need to find a way of being able to recognise this has changed, but you have given me a starting place Thanks very much for your help.