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