Please help with query

[smoked1]

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

[.josh]

in a loop, run a query to select where data=$data if mysql_num_rows > 0 then it exists, skip insert. If not, run a query to insert.

[MadTechie]

change this block of code

// 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>');
	}

 

 

$count1 = mysql_query("SELECT user_login FROM cscart_users WHERE user_login = '$email' ");
$num1 = mysql_num_rows($count1);
if($num1 == 0)
{
//****ADDED above
// 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')"); //****UPDATED
// If the query fails let us know
if (!$update2) {
  die('<p>Error performing update2 query: ' . mysql_error( ) . '</p>');
	}
//****ADDED BELOW
}
$count2 = mysql_query("SELECT cscart_user_profiles FROM cscart_users WHERE user_login = '$email' ");
$num2 = mysql_num_rows($count2);
if($num2 == 0)
{
//****ADDED ABOVE
// 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>');
	}
}//****ADDED

 

this is untested but i think your get the idea

[smoked1]

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