smoked1

That was awesome! It worked perfect for me. Thanks a bunch man!

I am trying to get some data out of a MSSQL database and INSERT it in to a MySQL database and so far that has worked out ok. The problem is that I want to be able to run it more than once and not have to worry about creating duplicate rows. How can I tell the INSERT queries to inly INSERT if data from a certain field "DOES NOT" exist? I'm sure that there are quite a few other thing that I could have done better as well but I don't do this very often. And for the code. <?php // Define Functions function formatPhone($phone = '', $convert = false, $trim = true) { // If we have not entered a phone number just return empty if (strlen($phone) < 7) { return ''; } // Strip out any extra characters that we do not need only keep letters and numbers $phone = preg_replace("/[^0-9A-Za-z]/", "", $phone); // Do we want to convert phone numbers with letters to their number equivalent? // Samples are: 1-800-TERMINIX, 1-800-FLOWERS, 1-800-Petmeds if ($convert == true) { $replace = array('2'=>array('a','b','c'), '3'=>array('d','e','f'), '4'=>array('g','h','i'), '5'=>array('j','k','l'), '6'=>array('m','n','o'), '7'=>array('p','q','r','s'), '8'=>array('t','u','v'), '9'=>array('w','x','y','z')); // Replace each letter with a number // Notice this is case insensitive with the str_ireplace instead of str_replace foreach($replace as $digit=>$letters) { $phone = str_ireplace($letters, $digit, $phone); } } // If we have a number longer than 11 digits cut the string down to only 11 // This is also only ran if we want to limit only to 11 characters if ($trim == true && strlen($phone)>11) { $phone = substr($phone, 0, 11); } // Perform phone number formatting here if (strlen($phone) == 7) { return preg_replace("/([0-9a-zA-Z]{3})([0-9a-zA-Z]{4})/", "$1-$2", $phone); } elseif (strlen($phone) == 10) { return preg_replace("/([0-9a-zA-Z]{3})([0-9a-zA-Z]{3})([0-9a-zA-Z]{4})/", "($1) $2-$3", $phone); } elseif (strlen($phone) == 11) { return preg_replace("/([0-9a-zA-Z]{1})([0-9a-zA-Z]{3})([0-9a-zA-Z]{3})([0-9a-zA-Z]{4})/", "$1($2) $3-$4", $phone); } // Return original phone if not 7, 10 or 11 digits long return $phone; } // Create DB variables for source db $host = "THE HOST"; $user = "THE USER"; $pass = "THE PASSWORD"; $dbname = "UPC"; // Connect to MSSQL $dbconnect = mssql_connect($host, $user, $pass) or die("Couldn't connect to SQL Server on $host"); // Select the database $dbselect = mssql_select_db($dbname); // Testing Connection echo "You are connected to $dbname <br />"; // Testing MSSQL Functions $select = mssql_query("SELECT tblCustomer.NameFirst AS firstname, tblCustomer.NameLast AS lastname, tblCustomer.Password AS password, tblCustomer.CompanyName AS company, tblCustomer.AddressLine1 AS address1, tblCustomer.AddressLine2 AS address2, tblCustomer.CityName AS city, tblState.StateName AS state, tblState.AbbrZipCode AS stateabr, tblCustomer.ZipCode AS zip, tblCountry.CountryAbbr AS country, tblCustomer.PhoneNumber AS phone, tblCustomer.FaxNumber AS fax, tblCustomer.EmailAddress AS email FROM tblCustomer, tblState, tblCountry WHERE CustomerID = CustomerID AND tblCustomer.State_ID = tblState.StateID AND tblCountry.CountryID = tblState.Country_ID"); // If the query fails let us know if (!$select) { die('<p>Error performing query: ' . mssql_get_last_message() . '</p>'); } // The while loop while ( $row = mssql_fetch_array($select) ) { // Assign the needed variables $firstname = addslashes($row['firstname']); $lastname = addslashes($row['lastname']); $password = md5($row['password']); $company = addslashes($row['company']); $address1 = addslashes($row['address1']); $address2 = addslashes($row['address2']); $city = addslashes($row['city']); $state = addslashes($row['state']); $stateabr = addslashes($row['stateabr']); $zip = addslashes($row['zip']); $country = addslashes($row['country']); $phone = addslashes(formatPhone($row['phone'])); $fax = addslashes(formatPhone($row['fax'])); $email = addslashes($row['email']); $email = strtolower($email); $time = time(); // Connect to the new database // Create DB variables $host2 = "THE HOST"; $user2 = "THE USER"; $pass2 = "THE PASSWORD"; $dbname2 = "usedphones_main"; // Connect to MySQL $dbconnect2 = mysql_connect($host2, $user2, $pass2) or die("Couldn't connect to SQL Server on $host2"); // Select the database $dbselect2 = mysql_select_db($dbname2); // Adding the new data to cscart_users $update2 = mysql_query("INSERT INTO cscart_users (user_type, user_login, timestamp, password, firstname, lastname, company, email, phone, fax) VALUES ('C', '$email', '$time', '$password', '$firstname', '$lastname', '$company', '$email', '$phone', '$fax') SELECT user_login FROM cscart_users WHERE user_login = '$email'"); // If the query fails let us know if (!$update2) { die('<p>Error performing update2 query: ' . mysql_error( ) . '</p>'); } // Adding the new data to cscart_user_profiles $update3 = mysql_query("INSERT INTO cscart_user_profiles (user_id, b_firstname, b_lastname, b_address, b_address_2, b_city, b_state, b_country, b_zipcode, s_firstname, s_lastname, s_address, s_address_2, s_city, s_state, s_country, s_zipcode, profile_name) VALUES (last_insert_id(), '$firstname', '$lastname', '$address1', '$address2', '$city', '$stateabr', '$country', '$zip', '$firstname', '$lastname', '$address1', '$address2', '$city', '$stateabr', '$country', '$zip', 'Main')"); // If the query fails let us know if (!$update3) { die('<p>Error performing update3 query: ' . mysql_error( ) . '</p>'); } } // Close DB connection mssql_close($dbconnect); mysql_close($dbconnect2); ?>

I added that because the price is in one table that does not have the model number in it and the other table is the one that has the model number.

Based on that I made some changes for my database and now it tells me that a table does not exist when it does. As far as I can tell the query looks fine. Part TH8BG-YF is being updated from 665.0000 to Unknown table 'cscart_products' in where clause <?php // From DB // OSC DB Info $hostF = "192.168.6.2"; $userF = "user"; $passF = "pass"; $dbnameF = "shop0"; //To DB // CSCART DB Info $hostT = "localhost"; $userT = "user"; $passT = "pass"; $dbnameT = "cscart0"; // Connect to osc $handleFrom = mysql_connect($hostF, $userF, $passF); // Connect to cscart $handleTo = mysql_connect($hostT, $userT, $passT); // Select the osc database mysql_select_db($dbnameF,$handleFrom) or die("Couldn't connect to DB"); // Select the cscart database mysql_select_db($dbnameT,$handleTo) or die("Couldn't connect to DB"); // Query OSC $SqlFrom = "SELECT products_model, products_price FROM products "; // OSC Results $resultFrom = mysql_query($SqlFrom, $handleFrom); if (!$resultFrom) { die('<p>Error performing query: ' . mysql_error() . '</p>'); } while ( $FromRow = mysql_fetch_array($resultFrom) ) { $FromID = $FromRow['product_id']; $FromPart = $FromRow['products_model']; $FromPrice = $FromRow['products_price']; $SqlTo = "SELECT product_code, price FROM cscart_products, cscart_product_prices WHERE product_code = '$FromPart'"; $resultTo = mysql_query($SqlTo,$handleTo) or die(mysql_error()); $ToRow = mysql_fetch_array($resultTo); $ToPart = $ToRow['products_model']; $ToPrice = $ToRow['products_price']; echo "<p>Part $FromPart is being updated from $FromPrice to $ToPrice" . "<br />"; $SqlUpdate = "UPDATE cscart_product_prices SET price='$ToPrice' WHERE cscart_products.product_id = cscart_product_prices.product_id AND cscart_products.product_code = '$FromPart'"; $result = mysql_query($SqlUpdate, $handleTo) or die(mysql_error()); } ?>

I was still getting an error and I noticed a few things so I made some changes. Here is the location: http://coastalmicrosupply.com/migrate_new.php I am still getting the same original issue. Here are the changes. <?php // CSCart DB Info $hostcs = "localhost"; $usercs = "user"; $passcs = "pass"; $dbnamecs = "cscart0"; // OSC DB Info $hostosc = "192.168.6.2"; $userosc = "user"; $passosc = "pass"; $dbnameosc = "shop0"; // Connect to cscart $handlecscart = mysql_connect($hostcs, $usercs, $passcs); // Connect to osc $handleosc = mysql_connect($hostosc, $userosc, $passosc); // Select the cscart database mysql_select_db($dbnamecs,$handlecscart) or die("Couldn't connect to DB"); // Select the osc database mysql_select_db($dbnameosc,$handleosc) or die("Couldn't connect to DB"); // Query OSC $selectosc = "SELECT products_model, products_price FROM products "; if (!$selectosc) { die('<p>Error performing query: ' . mysql_error() . '</p>'); } // OSC Results $resultosc = mysql_query($selectosc,$handleosc); if (!$resultosc) { die('<p>Error performing query: ' . mysql_error() . '</p>'); } //*******ONLY LOOP ONE //while ( $rowcs = mysql_fetch_array($resultcs) AND $rowosc = mysql_fetch_array($resultosc) ) while ( $rowosc = mysql_fetch_array($resultosc) ) { // $oscpart = $rowosc['product_code'] ; $oscpart = $rowosc['products_model']; // $oscprice = $rowosc['price']; $oscprice = $rowosc['products_price']; // $oscPID = $rowosc['product_id'] ; // Updates should be based on the product model //Find the New Product price etc $selectosc = "SELECT products_model, products_price FROM products WHERE products_model = '$oscpart'"; $resultcs = mysql_query($selectosc,$handleosc); $rowcs = mysql_fetch_array($resultcs); $cspart = $rowcs['products_model']; $csprice = $rowcs['products_price']; echo "<p>Part $cspart is being updated to $csprice" . "<br />"; // Made some changes so that new price is updated based on $oscpart $result = mysql_query("UPDATE cscart_product_prices SET price='$csprice' WHERE cscart_products.product_id = cscart_products_price.product_id AND cscart_products.product_code = '$oscpart'",$handlecscart) or die(mysql_error()); } ?>

Thanks man. There was one ";" missing and I added that in but now I am getting: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /var/www/vhosts/coastalmicrosupply.com/httpdocs/migrate_new.php on line 62 I went ahead and added the code with the additional ";" here for reference. <?php // CSCart DB Info $hostcs = "localhost"; $usercs = "user"; $passcs = "pass"; $dbnamecs = "cscart0"; // OSC DB Info $hostosc = "192.168.6.2"; $userosc = "user"; $passosc = "pass"; $dbnameosc = "shop0"; // Connect to cscart $handlecscart = mysql_connect($hostcs, $usercs, $passcs); // Connect to osc $handleosc = mysql_connect($hostosc, $userosc, $passosc); // Select the cscart database mysql_select_db($dbnamecs,$handlecscart) or die("Couldn't connect to DB"); // Select the osc database mysql_select_db($dbnameosc,$handleosc) or die("Couldn't connect to DB"); // Query OSC $selectosc = "SELECT products_model, products_price FROM products "; if (!$selectosc) { die('<p>Error performing query: ' . mysql_error() . '</p>'); } //****DON'T NEED TO PULL DATE FROM HERE YET***** // Query CSCART //$selectcs = "SELECT product_code, price, product_id FROM cscart_products, cscart_product_prices"; // if (!$selectcs) { // die('<p>Error performing query: ' . mysql_error() . '</p>'); // } // CSCART results //$resultcs = mysql_query($selectcs,$handlecscart); // if (!$resultcs) { // die('<p>Error performing query: ' . mysql_error() . '</p>'); // } //******************************** // OSC Results $resultosc = mysql_query($selectosc,$handleosc); if (!$resultosc) { die('<p>Error performing query: ' . mysql_error() . '</p>'); } //*******ONLY LOOP ONE //while ( $rowcs = mysql_fetch_array($resultcs) AND $rowosc = mysql_fetch_array($resultosc) ) while ( $rowcs = mysql_fetch_array($resultcs) ) { $cspart = $rowcs['product_code'] ; $csprice = $rowcs['price']; $csPID = $rowcs['product_id'] ; //Find the New Product price etc $selectosc = "SELECT products_model, products_price FROM products WHERE products_id = $csPID"; $resultcs = mysql_query($selectcs,$handlecscart); $rowosc = mysql_fetch_array($resultosc); $oscpart = $rowosc['products_model']; $oscprice = $rowosc['products_price']; echo "<p>Part $cspart is being updated to $oscprice" . "<br />"; $result = mysql_query("UPDATE cscart_product_prices SET price='$oscprice' WHERE cscart_products.product_id = $csPID",$handlecscart) or die(mysql_error()); } ?>

I am trying to take the data from the price field in one data base and put it in the other database for the same product.

I added echo "Done"; but it does show Done in the browser.

Sorry about that. It stops right at the update query. It prints the first echo and then stops. It does not give me any errors and I have my php.ini file set to show errors. Here is the URL: http://72.36.197.204/migrate.php

I am trying to update the prices on one shopping cart using the prices from another shopping carts database on another server. I am still a newbie at PHP and have been working on this all day. If anyone could help me out I would really appreciate it. <?php // CSCart DB Info $hostcs = "localhost"; $usercs = "user"; $passcs = "pass"; $dbnamecs = "cscart0"; // OSC DB Info $hostosc = "192.168.6.2"; $userosc = "user"; $passosc = "pass"; $dbnameosc = "shop0"; // Connect to cscart $handlecscart = mysql_connect($hostcs, $usercs, $passcs); // Connect to osc $handleosc = mysql_connect($hostosc, $userosc, $passosc); // Select the cscart database mysql_select_db($dbnamecs,$handlecscart) or die("Couldn't connect to DB"); // Select the osc database mysql_select_db($dbnameosc,$handleosc) or die("Couldn't connect to DB"); // Query CSCART $selectcs = "SELECT product_code, price FROM cscart_products, cscart_product_prices WHERE cscart_products.product_id = cscart_product_prices.product_id"; if (!$selectcs) { die('<p>Error performing query: ' . mysql_error() . '</p>'); } // Query OSC $selectosc = "SELECT products_model, products_price FROM products WHERE products_id = products_id"; if (!$selectosc) { die('<p>Error performing query: ' . mysql_error() . '</p>'); } // CSCART results $resultcs = mysql_query($selectcs,$handlecscart); if (!$resultcs) { die('<p>Error performing query: ' . mysql_error() . '</p>'); } // OSC Results $resultosc = mysql_query($selectosc,$handleosc); if (!$resultosc) { die('<p>Error performing query: ' . mysql_error() . '</p>'); } while ( $rowcs = mysql_fetch_array($resultcs) AND $rowosc = mysql_fetch_array($resultosc) ) { $cspart = $rowcs['product_code'] ; $csprice = $rowcs['price']; $oscpart = $rowosc['products_model']; $oscprice = $rowosc['products_price']; echo "<p>Part $cspart is being updated to $oscprice" . "<br />"; $result = mysql_query("UPDATE cscart_product_prices SET price='12.00' WHERE cscart_products.product_code = $cspart AND cscart_products.product_id = cscart_product_prices.product_id",$handlecscart) or die(mysql_error()); } ?>

Yeah it is an option provided for changing our prices on pricewatch. They say just submit a url to change the prices

This is the part of the code that I am having issues with: // Update prices now. $ch = curl_init("http://domain.com/dl/updateprice.asp?d=STATIC_USER&x=STATIC_PASS&pt=".$row[0]."&pr=".$row[3]); curl_setopt($ch, CURLOPT_NOBODY, 1); curl_exec($ch); curl_close ($ch); I don't even want to open the page. All that this does is change settings on a server using the variables.

I am working on a script that will update a companies prices for the items that they list at pricewatch.com. I am pretty new to PHP but it's been easy so far. Basically the way that it works is that we can type this in to a browser: "http://mate.pricewatch.com/dl/updateprice.asp?d=MY_ID&x=MY_PASSWORD&pt=amdxp1500&pr=999" and the price will be changed to 999.00. I need to submit about 3000 of these from a script inside of a while loop using data from a database. It also needs to create a CSV file but I think I have that working just fine. Here is the code. If anyone could help me to get these URLs going like they should I would really appreciate it. <?php require('includes/application_top.php'); // Create DB variables $host = "localhost"; $user = "user"; $pass = "password"; $dbname = "dbname"; // Connect to mysql mysql_pconnect($host, $user, $pass) or die("Couldn't connect to SQL Server on $host"); // Select the database mysql_select_db($dbname); // Query the needed info $select = " SELECT products.products_model, products.products_model, products_description.products_name, products.products_price, products.products_id, products.products_image_med, manufacturers.manufacturers_name, categories_description.categories_name, products.products_model FROM products INNER JOIN products_description ON (products.products_id=products_description.products_id) INNER JOIN products_to_categories ON (products_description.products_id=products_to_categories.products_id) INNER JOIN categories_description ON (products_to_categories.categories_id=categories_description.categories_id) INNER JOIN manufacturers ON (products.manufacturers_id=manufacturers.manufacturers_id)"; // Create the variables $export = mysql_query($select); $fields = mysql_num_fields($export); // These are NULL because it is needed on WINBLOZE $header = ""; $data = ""; // Get the field names //for ($i = 0; $i < $fields; $i++) { // $header .= mysql_field_name($export, $i) . ","; //} // Replace the lame field names $header .= "PartNo" . ","; $header .= "Model" . ","; $header .= "Description" . ","; $header .= "Price" . ","; $header .= "URL" . ","; $header .= "ImageLink" . ","; $header .= "Brand" . ","; $header .= "Category" . ","; $header .= "ManPart" . ","; $header .= "Ship1" . ","; $header .= "Ship2" . ","; $header .= "ShipMess" . ","; // The while loop while($row = mysql_fetch_row($export)) { $line = ''; // Some static data $row[0] = $row[0]; $row[1] = $row[0]; $row[2] = $row[2]; $row[3] = $row[3]; $row[4] = 'http://domain.com/product_info.php?products_id='.$row[4]; $row[5] = 'http://domain.com/images/'.$row[5]; $row[6] = $row[6]; $row[7] = $row[7]; $row[8] = $row[8]; $row[9] = '0'; $row[10] = '0'; $row[11] = 'FREE FedEx Ground'; foreach($row as $value) { // If the data is NULL toss a tab in there if ((!isset($value)) OR ($value == "")) { $value = ","; } else { // If the data is not NULL delimit the shit $value = str_replace('"', '""', $value); $value = '"' . $value . '"' . ","; } // Update prices now. $ch = curl_init("http://domain.com/dl/updateprice.asp?d=STATIC_USER&x=STATIC_PASS&pt=".$row[0]."&pr=".$row[3]); curl_setopt($ch, CURLOPT_NOBODY, 1); curl_exec($ch); curl_close ($ch); // Line equals the value dickwad $line .= $value; } $data .= trim($line)."\n"; } $data = str_replace("\r","",$data); // If there is not the user should probably know about it if ($data == "") { $data = "\n(0) Records Found!\n"; } // Modify the header $fp = fopen("../pricewatch/export.csv","w"); fwrite($fp, "$header\n$data"); fclose($fp); echo "Pricewatch file has been updated!"; ?>

I just installed Apache2, PHP, and MySQL. Everything seems to be working fine but I can\'t kill the Apache sevice. I try it from the service monitor and nothing happens. When I try from the command line it just tels me that the service could not be stopped. How do I fix this?

I emailed it over to you. Thanks a bunch!