updating from one column to another based on duplicate item number

[jakebur01]

Here's my problem. I have a database with about 50,000 items. Three hundred of these items have identical part numbers because I am using the manufacturers part number. The column next to the manufacturers part number is my number. I cannot just up and revert to using my unique part number because Google has already indexed a lot of my pages.

 

What I am wanting to do is to select all the items that are being repeated and update my unique part number from the isbn2 column into the manufacturers part number column (isbn).

 

Heres what I am trying, but not working:

 $sSQL = "SELECT DISTINCT isbn FROM books";  //  Select Unique values

  $recUnique = MySQL_Query( $sSQL ) or
    Die( "Error executing query: " . MySQL_Error() );

  while ( $row = MySQL_Fetch_array( $recUnique ) )
  {
  $isbn=$row["isbn"];
    $sSQL = "SELECT * FROM books WHERE `isbn`='$isbn'";

    $recDuplicate = MySQL_Query( $sSQL ) or
      Die( "Error executing query: " . MySQL_Error() );

    while ( $duplicates = MySQL_Fetch_Array( $recDuplicate ) )
    {
$updatedescription=$duplicates["description"];
$updateisbn=$duplicates["isbn"];
    //  print( "Duplicate found: " . $duplicates["isbn"] . "<BR>\n" );

$query = "UPDATE `books` SET `isbn` = isbn2 where `description` = '$updatedescription' and `isbn` = '$updateisbn'"; 
//print($query);
mysql_query($query) or die(mysql_error());
    }

    MySQL_Free_Result( $recDuplicate );
  }

  MySQL_Free_Result( $recUnique );
  MySQL_Close( $dbConn ); 
  

 

I am getting 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 'HANDLE' and `isbn` = '06100 '' at line 1

[jakebur01]

bump.

[jakebur01]

bump.

[BlueSkyIS]

mysql_query($query) or die(mysql_error() . " for query $query");

 

 

then you'll probably see a need for mysql_real_escape_string();

[jakebur01]

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 'HANDLE' and `isbn` = '06100 '' at line 1 for query UPDATE `books` SET `isbn` = isbn2 where `description` = 'CANT HOOK/2-1/4"X2' HANDLE' and `isbn` = '06100 '

[BlueSkyIS]

then you'll probably see a need for mysql_real_escape_string();

[jakebur01]

now i'm getting this error:

Error executing query: 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 '06'' at line 1

 

  $sSQL = "SELECT DISTINCT isbn FROM books";  //  Select Unique values

  $recUnique = MySQL_Query( $sSQL ) or
    Die( "Error executing query: " . MySQL_Error() );

  while ( $row = MySQL_Fetch_array( $recUnique ) )
  {
  $isbn=$row["isbn"];
    $sSQL = "SELECT * FROM books WHERE `isbn`='$isbn'";

    $recDuplicate = MySQL_Query( $sSQL ) or
      Die( "Error executing query: " . MySQL_Error() );

    while ( $duplicates = MySQL_Fetch_Array( $recDuplicate ) )
    {
$updatedescription=$duplicates["description"];
$updatedescription=mysql_real_escape_string($updatedescription);
$updateisbn=$duplicates["isbn"];
$updateisbn=mysql_real_escape_string($updateisbn);

    //  print( "Duplicate found: " . $duplicates["isbn"] . "<BR>\n" );

$query = "UPDATE `books` SET `isbn` = `isbn2` where `description` = '$updatedescription' and `isbn` = '$updateisbn'"; 
//print($query);
mysql_query($query) or die(mysql_error() . " for query $query");
    }

    MySQL_Free_Result( $recDuplicate );
  }

  MySQL_Free_Result( $recUnique );
  MySQL_Close( $dbConn ); 
  

 

`Jake

[BlueSkyIS]

for query... ???

[jakebur01]

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 '\'PIONEER CONDENSER\' and `isbn` = 0060700C \'' at line 1 for query UPDATE `books` SET `isbn` = `isbn2` where `description` = \'PIONEER CONDENSER\' and `isbn` = 0060700C \'

 

  $sSQL = "SELECT DISTINCT isbn FROM books";  //  Select Unique values

  $recUnique = MySQL_Query( $sSQL ) or
    Die( "Error executing query: " . MySQL_Error() );

  while ( $row = MySQL_Fetch_array( $recUnique ) )
  {
  $isbn=$row["isbn"];
    $sSQL = "SELECT * FROM books WHERE `isbn`='$isbn'";

    $recDuplicate = MySQL_Query( $sSQL ) or
      Die( "Error executing query: " . MySQL_Error() );

    while ( $duplicates = MySQL_Fetch_Array( $recDuplicate ) )
    {
$updatedescription=$duplicates["description"];
//$updatedescription=mysql_real_escape_string($updatedescription);
$updateisbn=$duplicates["isbn"];
//$updateisbn=mysql_real_escape_string($updateisbn);

    //  print( "Duplicate found: " . $duplicates["isbn"] . "<BR>\n" );

$query = "UPDATE `books` SET `isbn` = `isbn2` where `description` = '$updatedescription' and `isbn` = $updateisbn'"; 
$query = mysql_real_escape_string($query);
//print($query);
mysql_query($query) or die(mysql_error() . " for query $query");
    }

    MySQL_Free_Result( $recDuplicate );
  }

  MySQL_Free_Result( $recUnique );
  MySQL_Close( $dbConn ); 
  

[BlueSkyIS]

1. you're missing the left single quote before $updateisbn

2. don't use mysql_real_escape_string on the whole query, use it on query values.

 

$updatedescription=mysql_real_escape_string($duplicates["description");
$updateisbn=mysql_real_escape_string($duplicates["isbn"]);

$query = "UPDATE books SET isbn = isbn2 where description = '$updatedescription' and isbn = '$updateisbn'";
mysql_query($query) or die(mysql_error() . " for query $query");

[jakebur01]

I didn't get any errors but the page tried to load forever and it returned this:

CGI Timeout

The specified CGI application exceeded the allowed time for processing. The server has deleted the process.

 

Do I have what I am trying to do setup correctly? Where I am trying to update info from one column into another where my items repeat?

[jakebur01]

What am I doing wrong??

[jakebur01]

Does anyone know how to do this?

[jakebur01]

bump

[jakebur01]

bump