SPPC 4.2 can't add prices to customer group "Dealer"

[wendyv]

Hello,

 

I've just recently installed this contribution.

Since then my already existing product attributes are empty within the shop (not in the admin).

So i thought it would be caused by the fact that there are no prices for the dealers (my new customer group).

 

So i went to the admin and opened "group prices":

Attribute prices for groups

 

ID Product Name Option Name Option Value

 

110 Bonfire Kleur Zwart

 

 

Group Name Prefix Value Price Delete Insert Hidden

 

Retail +

Dealer +

 

My retail prices have mostly all value price: + 0.00 and an empty checkbox under delete and hidden. This is ok, the color (black or brown) does not change my prices.

So now i ad 0.000 to the value price box on the dealer, and click the checkbox for insert.

But when i hit the save button i get the next message:

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 'where products_attributes_id = '110'' at line 1

 

update products_attributes se where products_attributes_id = '110'

 

[TEP STOP

Does anybody know what's wrong? I cant get the error out.

 

When i look up product id 110 within the DB, i see this:

products_attributes_id value: 110

products_id value: 28

options_id value: 1

options_values_id value: 1

options_values_price value: 0.0000

price_prefix value: +

attributes_hide_from_groups value:@

 

Has it anything to do with attributes_hide_from_groups value=@ ?

 

Kind regards,

 

Wendy

[Jan Zonjee]

So now i ad 0.000 to the value price box on the dealer, and click the checkbox for insert.

But when i hit the save button i get the next message:

This is an insane sql error. The first sql when you update something in that popup window is the one in products_attributes (line 31-33 in attributes_groups.php:

		// update of table products_attributes with retail price and hide_from information
	$sql_data_array = array_merge($sql_data_array, $insert_sql_data);
	tep_db_perform(TABLE_PRODUCTS_ATTRIBUTES, $sql_data_array, 'update', "products_attributes_id = '" . (int)$_POST['attribute_id'] . "'");

The code relies on admin/includes/functions/database.php to update the table row. When I echo the query it becomes something like:

update products_attributes set attributes_hide_from_groups = '@', options_values_price = '0.0000', price_prefix = '+' where products_attributes_id = '1'

So with you some piece is missing in between? I can only assume you mangled that file database.php in some way (line 77-93):

    } elseif ($action == 'update') {
     $query = 'update ' . $table . ' set ';
     while (list($columns, $value) = each($data)) {
       switch ((string)$value) {
         case 'now()':
           $query .= $columns . ' = now(), ';
           break;
         case 'null':
           $query .= $columns .= ' = null, ';
           break;
         default:
           $query .= $columns . ' = \'' . tep_db_input($value) . '\', ';
           break;
       }
     }
     $query = substr($query, 0, -2) . ' where ' . $parameters;
   }

[wendyv]

This is an insane sql error. The first sql when you update something in that popup window is the one in products_attributes (line 31-33 in attributes_groups.php:

		// update of table products_attributes with retail price and hide_from information
	$sql_data_array = array_merge($sql_data_array, $insert_sql_data);
	tep_db_perform(TABLE_PRODUCTS_ATTRIBUTES, $sql_data_array, 'update', "products_attributes_id = '" . (int)$_POST['attribute_id'] . "'");

The code relies on admin/includes/functions/database.php to update the table row. When I echo the query it becomes something like:

update products_attributes set attributes_hide_from_groups = '@', options_values_price = '0.0000', price_prefix = '+' where products_attributes_id = '1'

So with you some piece is missing in between? I can only assume you mangled that file database.php in some way (line 77-93):

    } elseif ($action == 'update') {
     $query = 'update ' . $table . ' set ';
     while (list($columns, $value) = each($data)) {
       switch ((string)$value) {
         case 'now()':
           $query .= $columns . ' = now(), ';
           break;
         case 'null':
           $query .= $columns .= ' = null, ';
           break;
         default:
           $query .= $columns . ' = \'' . tep_db_input($value) . '\', ';
           break;
       }
     }
     $query = substr($query, 0, -2) . ' where ' . $parameters;
   }

 

Thanks Jan for your reaction.

It's insane indead, and i get a bit crazy by it ;-).

I've copied the complete function in my database file:

 

function tep_db_perform($table, $data, $action = 'insert', $parameters = '', $link = 'db_link') {
   reset($data);
   if ($action == 'insert') {
     $query = 'insert into ' . $table . ' (';
     while (list($columns, ) = each($data)) {
       $query .= $columns . ', ';
     }
     $query = substr($query, 0, -2) . ') values (';
     reset($data);
     while (list(, $value) = each($data)) {
       switch ((string)$value) {
         case 'now()':
           $query .= 'now(), ';
           break;
         case 'null':
           $query .= 'null, ';
           break;
         default:
           $query .= '\'' . tep_db_input($value) . '\', ';
           break;
       }
     }
     $query = substr($query, 0, -2) . ')';
   } elseif ($action == 'update') {
     $query = 'update ' . $table . ' set ';
     while (list($columns, $value) = each($data)) {
       switch ((string)$value) {
         case 'now()':
           $query .= $columns . ' = now(), ';
           break;
         case 'null':
           $query .= $columns .= ' = null, ';
           break;
         default:
           $query .= $columns . ' = \'' . tep_db_input($value) . '\', ';
           break;
       }
     }
     $query = substr($query, 0, -2) . ' where ' . $parameters;
   }

   return tep_db_query($query, $link);
 }

[wendyv]

I've checked everything 10 times today, but can't get it right somehow....

 

Is it possible that it has anything to do with the double quotes around the db_database:

"show table status from `" . DB_DATABASE . "`"

?

 

Hope you can help me.

Edited October 27, 2009 by wendyv

[Jan Zonjee]

I've checked everything 10 times today, but can't get it right somehow....

 

Is it possible that it has anything to do with the double quotes around the db_database:?

It looks fine but from your post at the Dutch forum it looks like you are using a text editor that either saves your files as utf-8 instead of iso-8859-1 (ASCII for PHP files is fine too) or adds a BOM-marker at the beginning of a file. PHP code does not work well with UTF-8 files and will do strange things when it encounters UTF-8 spaces for example. And spaces are pretty "invisible" of course.

That might be the issue here.

[wendyv]

It looks fine but from your post at the Dutch forum it looks like you are using a text editor that either saves your files as utf-8 instead of iso-8859-1 (ASCII for PHP files is fine too) or adds a BOM-marker at the beginning of a file. PHP code does not work well with UTF-8 files and will do strange things when it encounters UTF-8 spaces for example. And spaces are pretty "invisible" of course.

That might be the issue here.

 

 

Hi Jan,

 

Thanks again, but i use ultra edit. I think it's caused by using another action during the posting.

I just searched through my db and found this:

MySQL gaf een lege resultaat set terug (0 rijen). ( Query duurde 0.0013 sec )

SELECT *

FROM `products_attributes_groups`

LIMIT 0 , 30

 

This table is empty, seems strange to me, but i don't know much about MYSQL ;-).

 

Greetz,

Wendy

[Jan Zonjee]

Thanks again, but i use ultra edit. I think it's caused by using another action during the posting.

Oh, OK, so you say UltraEdit does not do that because...?

[wendyv]

Oh, OK, so you say UltraEdit does not do that because...?

 

I would not say that it doesn't happen, but i never experienced it...so that was an assumption...sorry!

I just got my database.php file from my first install, which was never changed, and opened it with UniRed, by your posting in the dutch forum, and saved it as a iso-8859-1 file.

Uploaded it again, but it doesn't help...,is there another way to try or fix this?

[Jan Zonjee]

Uploaded it again, but it doesn't help...,is there another way to try or fix this?

The website I linked to there also said that if you are on Windows you could open the file in Notepad and delete the offending characters.

 

I'm using a Mac and in TextWrangler I can save a file as ASCII (no problem for PHP files) or set it to show invisble characters. UTF-8 spaces show up as black circles then.

I saw it happen when I copied and pasted code from a website for example.

So I have no experience how to deal with this on Windows but I'm not unfamiliar with the issue at hand...

[wendyv]

The website I linked to there also said that if you are on Windows you could open the file in Notepad and delete the offending characters.

 

I'm using a Mac and in TextWrangler I can save a file as ASCII (no problem for PHP files) or set it to show invisble characters. UTF-8 spaces show up as black circles then.

I saw it happen when I copied and pasted code from a website for example.

So I have no experience how to deal with this on Windows but I'm not unfamiliar with the issue at hand...

 

Thanks Jan for all your input.

I will try to find a nice windows solution tomorow, i got realy tired by this problem ;-).

Other wise i through my computer out the Window(S)...problem also solved, but not in the right way ;-).

 

Thanks again and have a good evening!

 

Kind regards,

Wendy

[wendyv]

Thanks Jan for all your input.

I will try to find a nice windows solution tomorow, i got realy tired by this problem ;-).

Other wise i through my computer out the Window(S)...problem also solved, but not in the right way ;-).

 

Thanks again and have a good evening!

 

Kind regards,

Wendy

 

I was wondering if it can't be caused by another file, cause i've tried everything.

database.php has no syntax error, i found one but it didn't not solve my problem......

 

Thanks!

 

Greetings,

Wendy