Jump to content

updating from one column to another based on duplicate item number


jakebur01

Recommended Posts

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

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 '

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

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

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

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?

Archived

This topic is now archived and is closed to further replies.

×
×
  • 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.