API tools faq
paste
Advertisement
DECEiFER

OpenCart Auto Category/Product/Info Page Names to SEO URLs

DECEiFER
Aug 11th, 2014
304
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PHP 9.58 KB | None | 0 0
raw download clone embed print report
  1. <?php
  2. ## OpenCart Automatic Category/Product/Info Page Names to SEO-Friendly Keywords
  3. ## Version 1.6
  4. ## Written by David O'Neill on August 11th, 2014
  5. ## Updated on August 20th, 2014
  6.  
  7. ## Changelog ##
  8. ## ========= ##
  9. ## 1.6:
  10. ## * Tidied the code and cut the amount of repetition by implementing multidimensional arrays and loops
  11. ##
  12. ## 1.5:
  13. ## * Prepared statements are now being used
  14. ## * Better error handling
  15. ## * Ordered categories, products, and information pages by ID ascending
  16. ## * Clearer result report shown at the end of the execution
  17. ## * Minor, non-crucial bug fixes
  18.  
  19. # Put this file in your OpenCart root directory and run it in your browser.
  20. # Always backup your database before running this or any other database-changing script - it's just best practice.
  21. # This script uses the existing Category, Product, and Information page names to assign each SEO-friendly keywords.
  22. # Fully-automatic and safe, and if your database tables are InnoDB, it's even safer as autocommit is set to off in the script.
  23. # You may run this script and any SEO-friendly keywords that are already based on the respective names will not be affected.
  24. # Any SEO-friendly keywords that are not based exactly on the respective names will be changed, so bear that in mind.
  25. # This has been tested on my own live installations of OpenCart 1.5.6.1 and 1.5.6.4 with 100% success in all scenarios.
  26.  
  27. // Include the OpenCart configuration file to get the database constants
  28. include('config.php');
  29.  
  30. // Connect to the database
  31. $mysqli = new MySQLi(DB_HOSTNAME, DB_USERNAME, DB_PASSWORD, DB_DATABASE);
  32. if ($mysqli->connect_errno)
  33.     die('Error: Could not make a database link (' . $mysqli->connect_errno . ')');
  34.  
  35. // Turn off transation autocommit
  36. // Only applies to OpenCart installations whose database tables have been converted to InnoDB
  37. // This is safe to leave alone even if using MyISAM - it just won't have any effect (commits will remain automatic)
  38. $mysqli->autocommit(false);
  39.  
  40. // Query the category names and IDs
  41. $stmt = prepareStatement("SELECT `category_id`, `name` FROM `oc_category_description` ORDER BY `category_id` ASC");
  42. executeStatement($stmt);
  43. $stmt->bind_result($id, $name);
  44.  
  45. while ($stmt->fetch()){
  46.     $seoinfo['categories']['ids'][] = $id;
  47.     $seoinfo['categories']['names'][] = html_entity_decode($name);
  48.     $seoinfo['categories']['raw_names'][] = html_entity_decode($name);
  49. }
  50.  
  51. $stmt->free_result();
  52. $stmt->close();
  53.  
  54. namesToSEOKeywords($seoinfo['categories']['names']);
  55.  
  56. // Query the product names and IDs
  57. $stmt = prepareStatement("SELECT `product_id`, `name` FROM `oc_product_description` ORDER BY `product_id` ASC");
  58. executeStatement($stmt);
  59. $stmt->bind_result($id, $name);
  60.  
  61. while ($stmt->fetch()){
  62.     $seoinfo['products']['ids'][] = $id;
  63.     $seoinfo['products']['names'][] = html_entity_decode($name);
  64.     $seoinfo['products']['raw_names'][] = html_entity_decode($name);
  65. }
  66.  
  67. $stmt->free_result();
  68. $stmt->close();
  69.  
  70. namesToSEOKeywords($seoinfo['products']['names']);
  71.  
  72. // Query the information page names and IDs
  73. $stmt = prepareStatement("SELECT `information_id`, `title` FROM `oc_information_description` ORDER BY `information_id` ASC");
  74. executeStatement($stmt);
  75. $stmt->bind_result($id, $title);
  76.  
  77. while ($stmt->fetch()){
  78.     $seoinfo['infopages']['ids'][] = $id;
  79.     $seoinfo['infopages']['names'][] = html_entity_decode($title);
  80.     $seoinfo['infopages']['raw_names'][] = html_entity_decode($title);
  81. }
  82.  
  83. $stmt->free_result();
  84. $stmt->close();
  85.  
  86. namesToSEOKeywords($seoinfo['infopages']['names']);
  87.  
  88. // Query the existing SEO keywords
  89. $stmt = prepareStatement("SELECT `query` FROM `oc_url_alias`");
  90. executeStatement($stmt);
  91. $stmt->bind_result($query);
  92.  
  93. while ($stmt->fetch()){
  94.     $urlalias['queries'][] = $query;
  95. }
  96.  
  97. $stmt->free_result();
  98. $stmt->close();
  99.  
  100. $html = "<!DOCTYPE html>\n";
  101. $html .= "<html lang='en-US'>\n";
  102. $html .= "<head>\n";
  103. $html .= "<title>OpenCart Automatic Category/Product/Info Page Names to SEO-Fiendly Keywords</title>\n";
  104. $html .= "<meta charset='utf-8'>\n";
  105. $html .= "</head>\n";
  106. $html .= "<body>\n";
  107. $html .= "<div style='font-family:Arial,Helvetica,sans-serif;'>\n";
  108. $html .= "Done!\n<br><br><br>\n";
  109.  
  110. // INSERT or UPDATE the SEO values
  111. foreach ($seoinfo as $key => $value){
  112.     if ($key == "categories"){
  113.         $querytype = "category_id=";
  114.         $seotype = "Category";
  115.     } elseif ($key == "products"){
  116.         $querytype = "product_id=";
  117.         $seotype = "Product";
  118.     } else{
  119.         $querytype = "information_id=";
  120.         $seotype = "Information page";
  121.     }
  122.    
  123.     foreach ($seoinfo[$key]['ids'] as $key2 => $value2){
  124.         $query = $querytype . $value2;
  125.        
  126.         if (isset($urlalias['queries']) && array_search($query, $urlalias['queries']) !== false){
  127.             $stmt = prepareStatement("SELECT `keyword` FROM `oc_url_alias` WHERE `query` = ? LIMIT 1");
  128.             bindParams('s', $query);
  129.             executeStatement($stmt);
  130.             $stmt->bind_result($keyword);
  131.             $stmt->fetch();
  132.             $stmt->free_result();
  133.             $stmt->close();
  134.            
  135.             if ($keyword == $seoinfo[$key]['names'][$key2]){
  136.                 $seoinfo[$key]['old_names'][] = "none";
  137.                 $seoinfo[$key]['task'][] = "none";
  138.             } else{
  139.                 $stmt = prepareStatement("UPDATE `oc_url_alias` SET `keyword` = ? WHERE `query` = ? LIMIT 1");
  140.                 bindParams('ss', $seoinfo[$key]['names'][$key2], $query);
  141.                 executeStatement($stmt);
  142.                 $stmt->close();
  143.                 $seoinfo[$key]['old_names'][] = $keyword;
  144.                 $seoinfo[$key]['task'][] = 'update';
  145.             }
  146.         } else{
  147.             $stmt = prepareStatement("INSERT INTO `oc_url_alias` (`query`, `keyword`) VALUES (?, ?)");
  148.             bindParams('ss', $query, $seoinfo[$key]['names'][$key2]);
  149.             executeStatement($stmt);
  150.             $stmt->close();
  151.             $seoinfo[$key]['old_names'][] = "none";
  152.             $seoinfo[$key]['task'][] = "insert";
  153.         }
  154.     }
  155.    
  156.     // Note the SEO values added and changed
  157.     $html .= "<table style='min-width:50%;border:0;'>\n";
  158.     $html .= "<tr>\n<th colspan='2' style='text-align:left;'><b>" . $seotype . " SEO keywords added ("
  159.         . $sizeof = sizeof(array_keys($seoinfo[$key]['task'], 'insert')) .
  160.         "):</b></th>\n</tr>\n";
  161.    
  162.     if ($sizeof > 0){
  163.         foreach ($seoinfo[$key]['names'] as $key2 => $value2)
  164.             if ($seoinfo[$key]['task'][$key2] == 'insert')
  165.                 $html .= "<tr>\n<td style='padding-right:10px;'>"
  166.                     . $seoinfo[$key]['raw_names'][$key2] .
  167.                     "</td>\n<td>= "
  168.                     . $value2 .
  169.                     "</td>\n</tr>\n";
  170.     } else{
  171.         $html .= "<tr>\n<td>None</td>\n<td></td>\n</tr>\n";
  172.     }
  173.    
  174.     $html .= "</table>\n<br>\n";
  175.    
  176.     $html .= "<table style='min-width:50%;border:0;'>\n";
  177.     $html .= "<tr>\n<th colspan='2' style='text-align:left;'><b>" . $seotype . " SEO keywords changed ("
  178.         . $sizeof = sizeof(array_keys($seoinfo[$key]['task'], 'update')) .
  179.         "):</b></th>\n</tr>\n";
  180.        
  181.     if ($sizeof > 0){
  182.         foreach ($seoinfo[$key]['names'] as $key2 => $value2)
  183.             if ($seoinfo[$key]['task'][$key2] == 'update')
  184.                 $html .= "<tr>\n<td style='padding-right:10px;'>"
  185.                     . $seoinfo[$key]['raw_names'][$key2] .
  186.                     "</td>\n<td>= "
  187.                     . $seoinfo[$key]['old_names'][$key2] . " <b>-></b> " . $value2 .
  188.                     "</td>\n</tr>\n";
  189.     } else{
  190.         $html .= "<tr>\n<td>None</td>\n<td></td>\n</tr>\n";
  191.     }
  192.    
  193.     $html .= "</table>\n<br>\n";
  194. }
  195.  
  196. $html .= "</div>\n";
  197. $html .= "</body>\n";
  198. $html .= "</html>";
  199.  
  200. // Commit the changes
  201. // Only applies to OpenCart installations whose database tables have been converted to InnoDB
  202. // This is safe to leave alone even if using MyISAM - it just won't have any effect (commits will remain automatic)
  203. if (!$mysqli->commit())
  204.     die('Transaction failed - no changes have been committed. Please try again.');
  205.  
  206. // Close the database connection
  207. $mysqli->close();
  208.  
  209. // All good? You're done!
  210. // Let's echo out the changes
  211. echo $html;
  212.  
  213.  
  214. // Convert the names to lowercase, strip symbols, and put hyphens in the place of spaces
  215. function namesToSEOKeywords(&$names){
  216.     foreach ($names as &$value){
  217.         $value = strtolower($value);
  218.         $value = preg_replace('/&/', 'and', $value);
  219.         $value = trim(preg_replace('/[^A-Za-z0-9\']/', ' ', $value));
  220.         $value = preg_replace('/\'/', '', $value);
  221.         $value = preg_replace('/\s+/', '-', $value);
  222.     }
  223. }
  224.  
  225. // Prepare the MySQLi statement, return the result, and die on error
  226. function prepareStatement($query){
  227.     global $mysqli;
  228.    
  229.     if (!$stmt = $mysqli->prepare($query)){
  230.         // Die and display the error
  231.         // OpenCart installations whose database tables have been converted to InnoDB
  232.         // will take advantage of not having changes committed at this point
  233.         $error = $mysqli->error;
  234.         $mysqli->close();
  235.        
  236.         die('mysqli::prepare(): ' . $error);
  237.     }
  238.    
  239.     return $stmt;
  240. }
  241.  
  242. // Bind MySQLi paramters and die on error
  243. // Variadic function whose arguments should be in the order of datatypes, param1, param2, etc. - the same as mysqli_stmt::bind_param()
  244. function bindParams(){
  245.     global $mysqli, $stmt;
  246.    
  247.     $args = func_get_args();
  248.    
  249.     // Convert the arguments to references from values to avoid an error with mysqli_stmt::bind_param()
  250.     foreach ($args as $key => $value)
  251.         $args[$key] = &$args[$key];
  252.    
  253.     if (!call_user_func_array(array($stmt, 'bind_param'), $args)){
  254.         // Die and display the error
  255.         // OpenCart installations whose database tables have been converted to InnoDB
  256.         // will take advantage of not having changes committed at this point
  257.         $stmt->close();
  258.         $mysqli->close();
  259.        
  260.         die('mysqli_stmt::bind_param(): failed');
  261.     }
  262. }
  263.  
  264. // Execute the MySQLi statement and die on error
  265. function executeStatement(&$stmt){
  266.     global $mysqli;
  267.    
  268.     if (!$stmt->execute()){
  269.         // Die and display the error
  270.         // OpenCart installations whose database tables have been converted to InnoDB
  271.         // will take advantage of not having changes committed at this point
  272.         $error = $stmt->error;
  273.         $stmt->close();
  274.         $mysqli->close();
  275.        
  276.         die('mysqli_stmt::execute(): ' . $error);
  277.     }
  278. }
  279. ?>
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!