Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Few problems with database due new addon


sinopia

Recommended Posts

Hi, I got a oscommerce 2.3.4 website and I'm installing this addon: Imprint Text Options -> https://apps.oscommerce.com/wLYWg&imprint-text-options

This is a old addon I found but is exactly what I need. It works fine but there's a problem if I choice two products it gets the same attributes for both.

Also ...

session_id = '" . $osCsid . "'");

As not working had to change it to: tep_session_id() so it could insert the session_id into database.

Then I tried instead of normally product_id using the products_id with the variables like this -> 270{10}86{1}15{8}64{7}70{5}49{2}26{6}57{4}42{9}85 so even if the product if the same at least if any attributes changes it would show a different one.

But this to work correctly I need to change in the database the column products_id which is int(11) and primary key. In the database others products_id are tinytext. But always I try to change I get something about blob so I can't change it to allow those special characters.  Here is the sql of the installation:

DROP TABLE IF EXISTS customers_basket_text_attributes;
CREATE TABLE customers_basket_text_attributes (
  session_id varchar(255) NOT NULL default '',
  products_id int(11) NOT NULL default '0',
  products_text_attributes_id int(11) NOT NULL default '0',
  products_text_attributes_text text NOT NULL,
  PRIMARY KEY  (session_id,products_id,products_text_attributes_id)
) ENGINE=MyISAM;

DROP TABLE IF EXISTS orders_text_attributes;
CREATE TABLE orders_text_attributes (
  orders_id int(11) NOT NULL default '0',
  products_id int(11) NOT NULL default '0',
  products_text_attributes_id int(11) NOT NULL default '0',
  products_text_attributes_text text NOT NULL,
  PRIMARY KEY  (orders_id,products_id,products_text_attributes_id)
) ENGINE=MyISAM;

DROP TABLE IF EXISTS products_text_attributes;
CREATE TABLE products_text_attributes (
  products_text_attributes_id int(11) NOT NULL auto_increment,
  products_text_attributes_name varchar(255) NOT NULL default '',
  PRIMARY KEY  (products_text_attributes_id)
) ENGINE=MyISAM;

INSERT INTO products_text_attributes (products_text_attributes_id, products_text_attributes_name) VALUES (1, 'Mes');
INSERT INTO products_text_attributes (products_text_attributes_id, products_text_attributes_name) VALUES (2, 'Ano');
INSERT INTO products_text_attributes (products_text_attributes_id, products_text_attributes_name) VALUES (3, 'Geracao');

DROP TABLE IF EXISTS products_text_attributes_enabled;
CREATE TABLE products_text_attributes_enabled (
  products_id int(11) NOT NULL default '0',
  products_text_attributes_id int(11) NOT NULL default '0',
  PRIMARY KEY  (products_id,products_text_attributes_id)
) ENGINE=MyISAM;

Any suggestion or help would be really great, would appreciate it a lot. Best regards.

Link to comment
Share on other sites

Also... Had to change this because it was forcing to write only the product id without the attributes:

     $b = strpos($products[$i]['id'], '{');
     if ($b === false) {
       $pid = $products[$i]['id'];
     } else {
       $pid = substr($products[$i]['id'], 0, $b);
     }

 

Link to comment
Share on other sites

Quote

Hi, I got a oscommerce 2.3.4 website

Before you spend much time on this, I want to remind you that osC 2.3.4 (the official current version) is totally obsolete and should not be used. The only supported and current (up to date) version is osC 2.3.4BS "Edge", which you have to get from GitHub.

Quote

Then I tried instead of normally product_id using the products_id with the variables like this -> 270{10}86{1}15{8}64{7}70{5}49{2}26{6}57{4}42{9}85


 

You may be getting problems because many servers now remove or otherwise mess with variables and URL Query Strings containing { and }. There has been much discussion on this. If you are going to use product options or attributes, you would need to update the code to use other characters to mark start and end of sub-values.

Link to comment
Share on other sites

Hello @MrPhil

I know.. I might update it. I could setting it working changed the SQL and some PHP variables.

CREATE TABLE customers_basket_text_attributes (
  text_attributes_id int(11) NOT NULL AUTO_INCREMENT,
  session_id varchar(255) NOT NULL default '',
  products_id varchar(60) NOT NULL default '0',
  products_text_attributes_id int(11) NOT NULL default '0',
  products_text_attributes_text text NOT NULL,
  PRIMARY KEY  (text_attributes_id)
) ENGINE=MyISAM; 

CREATE TABLE orders_text_attributes (
  text_attributes_id int(11) NOT NULL AUTO_INCREMENT,
  orders_id int(11) NOT NULL default '0',
  products_id varchar(60) NOT NULL default '0',
  products_text_attributes_id int(11) NOT NULL default '0',
  products_text_attributes_text text NOT NULL,
  PRIMARY KEY  (text_attributes_id)
) ENGINE=MyISAM;

The only situation is when I add a new product with the same id and attributes but different products_text_attributes_text it shows the texts I choiced but in the same product (2 quantities), but I think that's fine.

Link to comment
Share on other sites

Just one question.. now in admin/orders and account_history_info.php (as the order is created) I cannot get the id's like 2255{18}138{17}130{19}155, only the 2255.

If a order has two same products but different attributes it display the same text twice for each product... Is there any function or query to get the id's complete as 2255{18}138{17}130{19}155?

 

Tried tep_get_uprid but it isn't working.

Link to comment
Share on other sites

Your server is cutting off the Query String at {, leaving you only with 2255. You have two choices: add code to URLencode() the Query String as it's produced ({ } to %7B %7D), or change the PHP code to use something other than { }. Neither Gary (definitely) nor Harald (likely) have any plans to fix this, so you're on your own.

Link to comment
Share on other sites

21 hours ago, MrPhil said:

Your server is cutting off the Query String at {, leaving you only with 2255. You have two choices: add code to URLencode() the Query String as it's produced ({ } to %7B %7D), or change the PHP code to use something other than { }. Neither Gary (definitely) nor Harald (likely) have any plans to fix this, so you're on your own.

This is not the same problem as is seen elsewhere.  

In osCommerce, when a product is bought that has options/attributes...the long (eg) 33{2}4{5}6 string that signifies a product with two options is not stored like that in the database....

In the database, it is stored as the products_id in the orders_products table, and as separate entries in the orders_product_attributes table using the products_id and the options_id(s).   (note I going from memory so table names may be slightly different, but you get the general idea).

Then using these tables, when the order is needed to be displayed (eg) at admin/orders.php ... the order is rewritten using the orders class to piece together the relevant details.

I have *long* asked for the full and true ID to also be stored in the orders_products table in an extra column.  So products_id is stored as is (eg) 33 and the actual and true id if what has been purchased is *also* stored as (eg) 33{2}4{5}6

If osCommerce did that...we would be more easily able to do all sorts of things.  It doesn't so we can't.

Link to comment
Share on other sites

I solved the issue in a similar way by creating two new columns on the orers_products_attributes table: products_options_id and products_options_values_id. Those values are stored separately so you don't have to decode the brackets into attributes and values. I also added those values to the orders class so I can work easily with those values, for example on a report or a stock/sales table.

Link to comment
Share on other sites

Hello thank you all for you answers,

It works good it imports to database the  2255{18}138{17}130{19}155 but then order is being processed it importes to tables orders and it just keeps the order number (2255) the rest of attributes are in anothers tables. I saw a function  tep_get_uprid, I tried with the query of attributes to see if I could get the attributes because the server is fine, only way is also to import the products_id{variables} to orders when it are done.

Link to comment
Share on other sites

Hi,

On 1.10.2017 at 2:26 PM, burt said:

I have *long* asked for the full and true ID to also be stored in the orders_products table in an extra column.  So products_id is stored as is (eg) 33 and the actual and true id if what has been purchased is *also* stored as (eg) 33{2}4{5}6

If osCommerce did that...we would be more easily able to do all sorts of things.  It doesn't so we can't.

this is done by QTPro inside a column added to orders_products (I think for restocking of cancelled orders) in its own format looking like 1-444,2-9 (option-option_value, option-option_value).

 QTPro or any stock function for attributes should become core anyway for obvious reasons.

Best regards

Christoph

Link to comment
Share on other sites

Hi @beerbee,

I've been looking if I could use some part of code from any addon but nothing seems to work and I can't figure it out.

Even tried some sql query to see if I could get the string from the product_id like 2255{18}138{17}130{19}155 but without success.

Any help would be awesome guys.

Link to comment
Share on other sites

Hi,

was just looking through the old support thread and also through the comments that I found for updated versions.

On 29.9.2017 at 4:30 PM, sinopia said:

If a order has two same products but different attributes it display the same text twice for each product

This seems to have been a problem from beginning with this addon. But it's not clear to me if it has ever been solved. This looks like it might have:

Imprint 1.3.2 (bugfix from 1.3.1) but on the other hand the last entry to the thread is

"Has anyone fixed the single item error? ie adding another product with another text? which overwrites the first ?"

Best regards

Christoph

Link to comment
Share on other sites

Hi @beerbee,

I think in two solution, when order is created add also the string (products + variables) to database (like: 2255{18}138{17}130{19}155). I think that isn't good idea, just will save unnecessary records.

Other is to make some function that runs some sql query that can convert the attributes in that order to id's (like tep_get_uprid in functions/general.php) but it's missing me something.

// Return a product ID with attributes
  function tep_get_uprid($prid, $params) {
    if (is_numeric($prid)) {
      $uprid = (int)$prid;

      if (is_array($params) && (sizeof($params) > 0)) {
        $attributes_check = true;
        $attributes_ids = '';

        reset($params);
        while (list($option, $value) = each($params)) {
          if (is_numeric($option) && is_numeric($value)) {
            $attributes_ids .= '{' . (int)$option . '}' . (int)$value;
          } else {
            $attributes_check = false;
            break;
          }
        }

        if ($attributes_check == true) {
          $uprid .= $attributes_ids;
        }
      }
    } else {
      $uprid = tep_get_prid($prid);

      if (is_numeric($uprid)) {
        if (strpos($prid, '{') !== false) {
          $attributes_check = true;
          $attributes_ids = '';

// strpos()+1 to remove up to and including the first { which would create an empty array element in explode()
          $attributes = explode('{', substr($prid, strpos($prid, '{')+1));

          for ($i=0, $n=sizeof($attributes); $i<$n; $i++) {
            $pair = explode('}', $attributes[$i]);

            if (is_numeric($pair[0]) && is_numeric($pair[1])) {
              $attributes_ids .= '{' . (int)$pair[0] . '}' . (int)$pair[1];
            } else {
              $attributes_check = false;
              break;
            }
          }

          if ($attributes_check == true) {
            $uprid .= $attributes_ids;
          }
        }
      } else {
        return false;
      }
    }

    return $uprid;
  }
Link to comment
Share on other sites

Archived

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

×
×
  • Create New...