Hallo,
wir sind gerade dabei unseren oscommerce Shop nach Shopware umzuziehen und mussten deshalb die alten Produkt URLS auf die neuen Shopware URLs umleiten.
Das geht bekanntlich mit der Tabelle „s_core_rewrite_urls“. Ich habe ein php Skript geschrieben, welches aus einer CSV Datei mit den alten URLs und Produkt IDs automatisch, die Shopware rewrite Tabelle ändert, welches ich hier gerne teile. Vielleicht hilft es ja jemandem.
Hier noch ein paar Punkte:
-
Skript bitte nur verwenden, wenn Ihr Euch einigermassen auskennt.
-
die CSV Tabelle sollte in der ersten Spalte die alten Produkt IDs haben (in Shopware „ordernumber“) und in der zweiten Spalte die alte Produkt URL (ohne Base) mit Semikolon getrennt:
Beispiel:
328;lieber-osterhase-kommst-bald-buch-p-328.html
812;grosses-fotoalbum-meine-taufe-p-812.html
883;spielkoffer-weissen-punkten-p-883.html
884;spielkoffer-dunkelblau-rakete-p-884.html
914;coppenrath-warten-aufs-christkind-p-914.html
Der Name der CSV Datei kann hier eingetragen werden:
$baf_filename = „articles_redirect_baf.csv“;
ausstellen falls das Skript zu lange braucht.
- Die URLs des alten und neuen Shops können hier eingetragen werden:
$old_store_url = „http://www.oldshop_url.de/“;
$sw_store_url =„http://www.shopware-shop.de/“;
-
Falls ein Eintrag schon besteht, wird das entsprechende Produkt einfach übersprungen
-
im Browser wird ein Log angezeigt mit allen URLs und der Shopware internen URL
DB_ADDED => Eintrag in die Tabelle eingetragen
DB_EXISTS => Eintrag besteht schon
URLS_OK => alte URL, Shopware URL existieren
-
WICHTIG: VORHER UNBEDINGT DIE DATENBANK SICHERN!!!
Hier ist der php code:
connect_errno) {
echo "Failed to connect to MySQL: (" . $swdb->connect_errno . ") " . $swdb->connect_error;
}
//////////////////////////
// read csv file
//////////////////////////
$baf_filename = "articles_redirect_baf.csv";
$fp = fopen($baf_filename,"r");
///////////////////////
// function to check if a page exists
///////////////////////
function page_exists($tmp_url)
{
$tmp_check=get_headers($tmp_url);
if(strpos($tmp_check[0],'200')===false)
return FALSE;
else
return TRUE;
}
/////////////////////////////////
//go over all lines of csv file
/////////////////////////////////
while (($csv_line=fgetcsv($fp,0,";")) != FALSE) {
$old_pid = $csv_line[0];
$old_url = $csv_line[1];
//get corresponding shopware product id
$sw_query=$swdb->query("SELECT articleID FROM s_articles_details WHERE ordernumber = " . $old_pid . " ");
$sw_row=$sw_query->fetch_assoc();
$sw_pid=$sw_row['articleID'];
//shopware org path
$sw_org_path="sViewport=detail&sArticle=" . $sw_pid;
//get shopware url for product
$sw_query=$swdb->query("SELECT path FROM s_core_rewrite_urls WHERE org_path = '". $sw_org_path . "' ");
if ($sw_query->num_rows > 0) {
$sw_row = $sw_query->fetch_assoc();
$sw_url = $sw_row['path'];
echo "-- " . $old_url . " -> " . $sw_url . " -> " . $sw_org_path;
}
else
{
echo "-- ERROR: no shopware url found for old product id: " . $sw_pid . " and old url" . $old_url . "";
continue;
}
////////////////////////
// update redirect table
////////////////////////
//check if the entry already exists in the table
$sw_query = $swdb->query("SELECT path FROM s_core_rewrite_urls WHERE path = '" . $old_url . "' ");
if ($sw_query->num_rows > 0) {
//entry exists just update
echo " DB_EXISTS";
} else {
//no entry exists insert new row
if ($check_urls) {
//check if all pages exist
$urls_ok = TRUE;
//check old url
if (page_exists($old_store_url . $old_url) == FALSE) {
$urls_ok = FALSE;
echo "ERROR: old url does not exist" . $old_store_url . $old_url . "";
continue;
}
//check new shopware SEO url
if (page_exists($sw_store_url . $sw_url) == FALSE) {
$urls_ok = FALSE;
echo "ERROR: shopware SEO url does not exist" . $sw_store_url . $sw_url . "";
continue;
}
//check shopware org path
if (page_exists($sw_org_url . $sw_org_path) == FALSE) {
$urls_ok = FALSE;
echo "ERROR: shopware org url does not exist" . $sw_org_url . $sw_org_path . "";
continue;
}
if ($urls_ok)
echo " URLS_OK ";
}
//no entry yet: insert
if ($table_update) {
$insert_query = "INSERT INTO s_core_rewrite_urls (org_path, path, main, subshopID) VALUES ('" . $sw_org_path . "', '" . $old_url . "',0,1)";
$sw_query = $swdb->query($insert_query);
if ($sw_query === TRUE) {
echo " DB_ADDED";
} else {
echo " DB_INSERT_ERROR";
}
}
}
echo "";
}
fclose($fp);
?>
Und Ihr braucht noch die Datei „sw_db_config.php“ im gleichen Ordner, für Eure Datenbankeinstellungen:
Viel Spass. Ich hoffe, es hilft jemandem.
Gruss,
Jens
Hallo Jens,
feine Sache dass du es hier einstellst! Das kommt mir auch sehr gelegen. Ein alter XT muss noch portiert werden. Hatte mir gedacht per nginx rewrite das zu erledigen. Sind aber über 1000 Zeilen.
In der DB ist das gut aufgehoben :) 
Hi,
ich arbeite gerade an dem Skript für die Kategorien und die Hersteller. Das kann ich auch einstellen, falls Interesse besteht.
Gruss,
Jens
Kannst du gern machen. In meinem Fall brauche ich nur die Produkte umgeleitet.
Von oscommerce auf Shopware ist ja ein Mega Sprung 
> Von oscommerce auf Shopware ist ja ein Mega Sprung 
Kann man wohl sagen. Das ist wie von einem Eselsritt auf einen Raumschiffsflug zu wechseln.
Ich lerne gerade viel dazu … 
Hier ist noch das Skript zur Generierung von Kategorie Rewrites aus einer CSV Datei:
connect_errno) {
echo "Failed to connect to MySQL: (" . $swdb->connect_errno . ") " . $swdb->connect_error;
}
//////////////////////////
// read csv file
//////////////////////////
$baf_filename = "categories_redirect_baf.csv";
$fp = fopen($baf_filename,"r");
///////////////////////
// function to check if a page exists
///////////////////////
function page_exists($tmp_url)
{
$tmp_check=get_headers($tmp_url);
if(strpos($tmp_check[0],'200')===false)
return FALSE;
else
return TRUE;
}
/////////////////////////////////
//go over all lines of csv file
/////////////////////////////////
while (($csv_line=fgetcsv($fp,0,";")) != FALSE) {
$old_pid = $csv_line[0];
$old_url = $csv_line[1];
$sw_pid = $old_pid;
//shopware org path
$sw_org_path="sViewport=cat&sCategory=" . $sw_pid;
//get shopware url for product
$sw_query=$swdb->query("SELECT path FROM s_core_rewrite_urls WHERE org_path = '". $sw_org_path . "' ");
if ($sw_query->num_rows > 0)
{
$sw_row = $sw_query->fetch_assoc();
$sw_url = $sw_row['path'];
echo "-- " . $old_url . " -> " . $sw_url . " -> " . $sw_org_path;
}
else
{
echo "-- ERROR: no shopware url found for old category id: " . $sw_pid . " and old url" . $old_url . "";
if (!$ignore_errors)
continue;
}
////////////////////////
// update redirect table
////////////////////////
//check if the entry already exists in the table
$sw_query = $swdb->query("SELECT path FROM s_core_rewrite_urls WHERE path = '" . $old_url . "' ");
if ($sw_query->num_rows > 0) {
//entry exists just update
echo " DB_EXISTS";
} else {
//no entry exists insert new row
if ($check_urls) {
//check if all pages exist
$urls_ok = TRUE;
//check old url
if (page_exists($old_store_url . $old_url) == FALSE) {
$urls_ok = FALSE;
echo "ERROR: old url does not exist" . $old_store_url . $old_url . "";
continue;
}
//check new shopware SEO url
if (page_exists($sw_store_url . $sw_url) == FALSE) {
$urls_ok = FALSE;
echo "ERROR: shopware SEO url does not exist" . $sw_store_url . $sw_url . "";
if (!$ignore_errors)
continue;
}
//check shopware org path
if (page_exists($sw_org_url . $sw_org_path) == FALSE) {
$urls_ok = FALSE;
echo "ERROR: shopware org url does not exist" . $sw_org_url . $sw_org_path . "";
if (!$ignore_errors)
continue;
}
if ($urls_ok)
echo " URLS_OK ";
}
//no entry yet: insert
if ($table_update) {
//add entry even if category is not in shopware s_core_rewrite table
if (($ignore_errors) and !$urls_ok)
echo "-> nevertheless adding entry to s_core_rewrite_urls: ". $old_url . " -> " . $sw_org_path;
$insert_query = "INSERT INTO s_core_rewrite_urls (org_path, path, main, subshopID) VALUES ('" . $sw_org_path . "', '" . $old_url . "',0,1)";
$sw_query = $swdb->query($insert_query);
if ($sw_query === TRUE) {
echo " DB_ADDED";
} else {
echo " DB_INSERT_ERROR";
}
}
}
echo "";
}
fclose($fp);
?>
…und noch Supplier Rewrites aus einer CSV Datei:
connect_errno) {
echo "Failed to connect to MySQL: (" . $swdb->connect_errno . ") " . $swdb->connect_error;
}
//////////////////////////
// read csv file
//////////////////////////
$baf_filename = "manufacturers_redirect_baf.csv";
$fp = fopen($baf_filename,"r");
///////////////////////
// function to check if a page exists
///////////////////////
function page_exists($tmp_url)
{
$tmp_check=get_headers($tmp_url);
if(strpos($tmp_check[0],'200')===false)
return FALSE;
else
return TRUE;
}
/////////////////////////////////
//go over all lines of csv file
/////////////////////////////////
while (($csv_line=fgetcsv($fp,0,";")) != FALSE) {
$old_pid = $csv_line[0];
$old_url = $csv_line[1];
$sw_pid = $old_pid;
//shopware org path
$sw_org_path="sViewport=listing&sAction=manufacturer&sSupplier=" . $sw_pid;
//get shopware url for product
$sw_query=$swdb->query("SELECT path FROM s_core_rewrite_urls WHERE org_path = '". $sw_org_path . "' ");
if ($sw_query->num_rows > 0)
{
$sw_row = $sw_query->fetch_assoc();
$sw_url = $sw_row['path'];
echo "-- " . $old_url . " -> " . $sw_url . " -> " . $sw_org_path;
}
else
{
echo "-- ERROR: no shopware url found for old category id: " . $sw_pid . " and old url" . $old_url . "";
if (!$ignore_errors)
continue;
}
////////////////////////
// update redirect table
////////////////////////
//check if the entry already exists in the table
$sw_query = $swdb->query("SELECT path FROM s_core_rewrite_urls WHERE path = '" . $old_url . "' ");
if ($sw_query->num_rows > 0) {
//entry exists just update
echo " DB_EXISTS";
} else {
//no entry exists insert new row
if ($check_urls) {
//check if all pages exist
$urls_ok = TRUE;
//check old url
if (page_exists($old_store_url . $old_url) == FALSE) {
$urls_ok = FALSE;
echo "ERROR: old url does not exist" . $old_store_url . $old_url . "";
continue;
}
//check new shopware SEO url
if (page_exists($sw_store_url . $sw_url) == FALSE) {
$urls_ok = FALSE;
echo "ERROR: shopware SEO url does not exist" . $sw_store_url . $sw_url . "";
if (!$ignore_errors)
continue;
}
//check shopware org path
if (page_exists($sw_org_url . $sw_org_path) == FALSE) {
$urls_ok = FALSE;
echo "ERROR: shopware org url does not exist" . $sw_org_url . $sw_org_path . "";
if (!$ignore_errors)
continue;
}
if ($urls_ok)
echo " URLS_OK ";
}
//no entry yet: insert
if ($table_update) {
//add entry even if category is not in shopware s_core_rewrite table
if (($ignore_errors) and !$urls_ok)
echo "-> nevertheless adding entry to s_core_rewrite_urls: ". $old_url . " -> " . $sw_org_path;
$insert_query = "INSERT INTO s_core_rewrite_urls (org_path, path, main, subshopID) VALUES ('" . $sw_org_path . "', '" . $old_url . "',0,1)";
$sw_query = $swdb->query($insert_query);
if ($sw_query === TRUE) {
echo " DB_ADDED";
} else {
echo " DB_INSERT_ERROR";
}
}
}
echo "";
}
fclose($fp);
?>
Viel Spass,
Jens