Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Location
    United Kingdom
    Posts
    132
    Plugin Contributions
    0

    Default SQL Patch command / query for changing description.

    Dear Group,

    I was wondering if there was an SQL Patch / Query command, which can be issued from zencart to do the following ;

    Within "Product Description" change all instances of "01202 123456" to "01258 654321" (In other words, change our telephone number, which is within some of the product descriptions).

    Thanks in advance for your help !

    Kind Regards,

    Steve

  2. #2
    Join Date
    Sep 2004
    Location
    Western Massachusetts
    Posts
    2,945
    Plugin Contributions
    5

    Default Re: SQL Patch command / query for changing description.

    You won't be able to do this with a simple sql patch, as mySQL doesn't have a capability to replace substrings of data fields.

    You'll need to build a php script to do it - the easiest way is to build a new page for admin that does the job.

    Take the following code, paste it into a blank text editor file, edit the two numbers in line 11 (first is the old number, second is the new number) and save the file as admin/update_phone.php

    **** I highly recommend making a backup of your database before this next step, because I haven't tested this code

    Then from any page in admin, just change the url in your browser to www.yoursite.com/admin_path/update_phone.php

    When it loads, the page should show xxx product descriptions updated where xxx is equal to your number of products multiplied by the number of languages you are using in your store.

    Code:
    <?php
      require('includes/application_top.php');
      
      $query = "select products_id, language_id, products_description from " . TABLE_PRODUCTS_DESCRIPTION;
      $result = $db->Execute($query);
      $records = 0;
      while (!$result->EOF) {
        $new_description = str_replace('01202 123456', '01258 654321', $result->fields['products_description']);
    	
    	$db->Execute("UPDATE " . TABLE_PRODUCTS_DESCRIPTION . " SET products_description = " . $new_description . " 
    	              WHERE products_id = '" . (int)$result->fields['products_id'] . "'
    				  AND language_id = '" . (int)$result->fields['language_id'] . "'");
      
        
    	$records += 1;
    	$result->MoveNext();
      }
    
    ?>
    <!doctype html public "-//W3C//DTD HTML 4.01 Transitional//EN">
    <html <?php echo HTML_PARAMS; ?>>
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=<?php echo CHARSET; ?>">
    <title><?php echo TITLE; ?></title>
    <link rel="stylesheet" type="text/css" href="includes/stylesheet.css">
    <script language="javascript" src="includes/menu.js"></script>
    </head>
    <body marginwidth="0" marginheight="0" topmargin="0" bottommargin="0" leftmargin="0" rightmargin="0" bgcolor="#FFFFFF">
    
    <!-- body_text //-->
    <table border="0" width="100%" cellspacing="0" cellpadding="2">
      <tr>
        <td>
        
        
        <?php echo $records; ?> product descriptions updated
        
        
        </td>
          </tr>
    </table>
    <!-- body_text_eof //-->
    
    <br>
    </body>
    </html>
    <?php require(DIR_WS_INCLUDES . 'application_bottom.php'); ?>
    Last edited by bunyip; 15 Mar 2009 at 12:31 AM.
    Neville
    An assumption is what you arrive at when you get tired of thinking...

  3. #3
    Join Date
    Mar 2004
    Location
    United Kingdom
    Posts
    132
    Plugin Contributions
    0

    Default Re: SQL Patch command / query for changing description.

    wow, thanks, thats amazing, i will make a backup and test the code.

    Kind Regards,

    Steve

  4. #4
    Join Date
    Mar 2004
    Location
    United Kingdom
    Posts
    132
    Plugin Contributions
    0

    Default Re: SQL Patch command / query for changing description.

    oops, that didn't work, i get ;

    1064 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 ' ' at line 1
    in:
    [UPDATE products_description SET products_description = WHERE products_id = '176' AND language_id = '1']
    If you were entering information, press the BACK button in your browser and re-check the information you had entered to be sure you left no blank fields.

 

 

Similar Threads

  1. v154 Help with a SQL Query for Query Builder
    By lindasdd in forum Managing Customers and Orders
    Replies: 2
    Last Post: 24 Mar 2016, 01:18 PM
  2. Replies: 3
    Last Post: 11 May 2012, 12:35 PM
  3. v139h DB/SQL Error when using SQL Patch for Add Pages More Info Sidebox addon
    By maperr55 in forum All Other Contributions/Addons
    Replies: 6
    Last Post: 5 Mar 2012, 09:32 PM
  4. Replies: 13
    Last Post: 8 Dec 2009, 03:30 AM
  5. SQL query in description field
    By johnny125 in forum General Questions
    Replies: 0
    Last Post: 15 Sep 2009, 09:14 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
disjunctive-egg
Zen-Cart, Internet Selling Services, Klamath Falls, OR