Jump to content

smoked1

Members
 View Profile  See their activity

  • Posts

    18

  • Joined

  • Last visited

    Never

 Content Type 

Posts posted by smoked1

    Please help with query

    in PHP Coding Help

    Posted

    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);
?>

    Newbie needs help please

    in PHP Coding Help

    Posted

    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());
}
?>

    Newbie needs help please

    in PHP Coding Help

    Posted

    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());
}
?>

    Newbie needs help please

    in PHP Coding Help

    Posted

    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());
}
?>

    Newbie needs help please

    in PHP Coding Help

    Posted

    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());


}
?>

    Need help with HTTP requests

    in PHP Coding Help

    Posted

    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.

    Need help with HTTP requests

    in PHP Coding Help

    Posted

    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!";
?>

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.