jakebur01 Posted March 25, 2008 Share Posted March 25, 2008 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 Quote Link to comment https://forums.phpfreaks.com/topic/97742-updating-from-one-column-to-another-based-on-duplicate-item-number/ Share on other sites More sharing options...
jakebur01 Posted March 25, 2008 Author Share Posted March 25, 2008 bump. Quote Link to comment https://forums.phpfreaks.com/topic/97742-updating-from-one-column-to-another-based-on-duplicate-item-number/#findComment-500244 Share on other sites More sharing options...
jakebur01 Posted March 25, 2008 Author Share Posted March 25, 2008 bump. Quote Link to comment https://forums.phpfreaks.com/topic/97742-updating-from-one-column-to-another-based-on-duplicate-item-number/#findComment-500394 Share on other sites More sharing options...
BlueSkyIS Posted March 25, 2008 Share Posted March 25, 2008 mysql_query($query) or die(mysql_error() . " for query $query"); then you'll probably see a need for mysql_real_escape_string(); Quote Link to comment https://forums.phpfreaks.com/topic/97742-updating-from-one-column-to-another-based-on-duplicate-item-number/#findComment-500396 Share on other sites More sharing options...
jakebur01 Posted March 25, 2008 Author Share Posted March 25, 2008 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 ' Quote Link to comment https://forums.phpfreaks.com/topic/97742-updating-from-one-column-to-another-based-on-duplicate-item-number/#findComment-500404 Share on other sites More sharing options...
BlueSkyIS Posted March 25, 2008 Share Posted March 25, 2008 then you'll probably see a need for mysql_real_escape_string(); Quote Link to comment https://forums.phpfreaks.com/topic/97742-updating-from-one-column-to-another-based-on-duplicate-item-number/#findComment-500406 Share on other sites More sharing options...
jakebur01 Posted March 25, 2008 Author Share Posted March 25, 2008 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 Quote Link to comment https://forums.phpfreaks.com/topic/97742-updating-from-one-column-to-another-based-on-duplicate-item-number/#findComment-500425 Share on other sites More sharing options...
BlueSkyIS Posted March 25, 2008 Share Posted March 25, 2008 for query... ??? Quote Link to comment https://forums.phpfreaks.com/topic/97742-updating-from-one-column-to-another-based-on-duplicate-item-number/#findComment-500436 Share on other sites More sharing options...
jakebur01 Posted March 25, 2008 Author Share Posted March 25, 2008 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 ); Quote Link to comment https://forums.phpfreaks.com/topic/97742-updating-from-one-column-to-another-based-on-duplicate-item-number/#findComment-500444 Share on other sites More sharing options...
BlueSkyIS Posted March 25, 2008 Share Posted March 25, 2008 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"); Quote Link to comment https://forums.phpfreaks.com/topic/97742-updating-from-one-column-to-another-based-on-duplicate-item-number/#findComment-500451 Share on other sites More sharing options...
jakebur01 Posted March 25, 2008 Author Share Posted March 25, 2008 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? Quote Link to comment https://forums.phpfreaks.com/topic/97742-updating-from-one-column-to-another-based-on-duplicate-item-number/#findComment-500465 Share on other sites More sharing options...
jakebur01 Posted March 25, 2008 Author Share Posted March 25, 2008 What am I doing wrong?? Quote Link to comment https://forums.phpfreaks.com/topic/97742-updating-from-one-column-to-another-based-on-duplicate-item-number/#findComment-500480 Share on other sites More sharing options...
jakebur01 Posted March 25, 2008 Author Share Posted March 25, 2008 Does anyone know how to do this? Quote Link to comment https://forums.phpfreaks.com/topic/97742-updating-from-one-column-to-another-based-on-duplicate-item-number/#findComment-500542 Share on other sites More sharing options...
jakebur01 Posted March 25, 2008 Author Share Posted March 25, 2008 bump Quote Link to comment https://forums.phpfreaks.com/topic/97742-updating-from-one-column-to-another-based-on-duplicate-item-number/#findComment-500657 Share on other sites More sharing options...
jakebur01 Posted March 25, 2008 Author Share Posted March 25, 2008 bump Quote Link to comment https://forums.phpfreaks.com/topic/97742-updating-from-one-column-to-another-based-on-duplicate-item-number/#findComment-500723 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.