Help needed for SQL failure

[PupStar]

Happy Christmas guys, I hope you have all been on santas nice list o:)

 

I have gone back to an old script I have which worked great on an older version of oscommerce but I am still left with 1 error which I can not resolve.

 

These are the errors which I am having trouble resolving.

 

 

 INSERT INTO products_options_values(language_id, products_options_values_id, products_options_values_name) VALUES ('1', 'Blue')\r\nArray

[sql failure] REPLACE INTO products_options_values_to_products_options(products_options_id, products_options_values_id) VALUES (0, )

[sql failure] INSERT INTO products_attributes(products_id, options_id, options_values_id, options_values_price, price_prefix) VALUES (1, 0, , 0, '+')\r\nArray

[sql failure] INSERT INTO products_options_values(language_id, products_options_values_id, products_options_values_name) VALUES ('1', 'Purple')\r\nArray

[sql failure] REPLACE INTO products_options_values_to_products_options(products_options_id, products_options_values_id) VALUES (0, )

[sql failure] INSERT INTO products_attributes(products_id, options_id, options_values_id, options_values_price, price_prefix) VALUES (1, 0, , 0, '+')\r\nArray

 

below is the part of the script which handles product attributes.

	        $sql = "DELETE FROM products_attributes

                WHERE products_id = $product_id";

	        if (!$db->sql_query($sql)) {

	            $this->report('[SQL failure] ' . $sql);

	        } elseif (DEBUG) {

	            $this->report('[SQL ok] ' . $sql);

	        }

	        $sql = "SELECT products_id, options_id, options_values_id, options_values_price, price_prefix FROM puck_products_attributes WHERE products_id = $id ORDER BY products_id, options_id";

			if (!$puck_result = $db->sql_query($sql)) {

                 $this->report('[SQL failure] ' . $sql . '\r\n' . $db->sql_error($puck_result));

                 printr($puck_result);

            } elseif (DEBUG) {

                 $this->report('[SQL ok] ' . $sql);

            }

            $puck_options_id = 0;

            $options_id = 1;

	        while ($row = $db->sql_fetchrow($puck_result)) {

	        	if ($row['options_id'] != $puck_options_id) {

	        		$options_id = $this->ImportProductOption($row['options_id']);

	        		$puck_options_id = $row['options_id'];

	        	}

				$options_values_id = $this->ImportProductOptionsValue($row['options_values_id']);

				$sql = "REPLACE INTO products_options_values_to_products_options(products_options_id, products_options_values_id) VALUES ($options_id, $options_values_id)";

	            if (!$db->sql_query($sql)) {

	                $this->report('[SQL failure] ' . $sql);

	            } elseif (DEBUG) {

	                $this->report('[SQL ok] ' . $sql);

	            }

				$sql = "INSERT INTO products_attributes(products_id, options_id, options_values_id, options_values_price, price_prefix) VALUES ($product_id, $options_id, $options_values_id, 0, '+')";

				if (!$live_result = $db->sql_query($sql)) {

                    $this->report('[SQL failure] ' . $sql . '\r\n' . $db->sql_error($live_result));

                } elseif (DEBUG) {

                    $this->report('[SQL ok] ' . $sql);

                }

         	}

	   	}

	}

help would be appreciated as I do not understand why it is not working.

 

Thanks

 

Mark

[Jack_mcs]

What is the actual error?

[PupStar]

What is the actual error?

 

@@Jack_mcs

 

the actual error is that the product attribute options are not inserted into the database, if thats what you meant

[Jack_mcs]

So you are not getting an error - the code is just not working. Is that correct? In either case, the code you show is not from a standard oscommerce shop that I can see. It appears to be a php class and the function being used to write to the database is not shown. So this isn't something someone here can help you with without more information. But assuming this is from an addon, I suggest you ask in the support thread for that addon since you are more likely to get an answer there.

[PupStar]

So you are not getting an error - the code is just not working. Is that correct? In either case, the code you show is not from a standard oscommerce shop that I can see. It appears to be a php class and the function being used to write to the database is not shown. So this isn't something someone here can help you with without more information. But assuming this is from an addon, I suggest you ask in the support thread for that addon since you are more likely to get an answer there.

@@Jack_mcs

 

I am not sure what else you need to see but the complete function is this

	function ImportProduct ($id) {

		global $db, $numberOfProductsInactive;

		//Retrieves all the products data from puck table

		$sql = "SELECT products_model, products_id, products_quantity, products_image,

				products_price, products_date_added, products_weight,

				products_status, manufacturers_id FROM puck_products where products_id = $id";

		$result = $db->sql_query($sql);

	   	if ($row = $db->sql_fetchrow($result)) {

   		 	$pid = $row['products_model'];

		   	$sql = "SELECT products_id, products_status FROM products WHERE products_model = '$pid'";

	        if (!$live_result = $db->sql_query($sql)) {

	            $this->report('[SQL failure] ' . $sql . '\r\n' . $db->sql_error($live_result));

	        } elseif (DEBUG) {

	            $this->report('[SQL ok] ' . $sql);

	        }

	        $price = $this->get_net_price($this->GetProductIncreasedPrice($id, $row['products_price']));

	        $stock = intval($row['products_quantity']);

	        $image = $row['products_image'];

	        $weight = ''; //TODO:

	        $manufacturer_id = $row['manufacturers_id'];

	        $products_id = $row['products_id'];

	   		if ($stock < $this->minimum_number_of_products_active) {

	        	$status = 0;

	        	$numberOfProductsInactive++;

	        } else {

	        	$status = 1;

	        }

	        if (!$row = $db->sql_fetchrow($live_result)) {

	            //Insert into live products table

	            $sql = "INSERT INTO products

	                    (products_model, products_quantity, products_image, products_price, products_date_added, products_weight, products_status, manufacturers_id)

	                    VALUES ('$pid',  $stock, '$image', $price, NOW(), '$weight', $status, '$manufacturer_id')";

	            if (!$live_result = $db->sql_query($sql)) {

	                 $this->report('[SQL failure] ' . $sql . '\r\n' . $db->sql_error($live_result));

                } elseif (DEBUG) {

                    $this->report('[SQL ok] ' . $sql);

                }

	            if (!$product_id = $db->sql_nextid()) {

	                $this->report("[SQL failure] No product id returned for $sql, although the query was successful.");

	            }

		   	} else {

	            //Updates products

	            $product_id = $row['products_id'];

	            $sql = "UPDATE products

	                    SET products_quantity = $stock, products_image = '$image',

	                        products_price = $price, products_last_modified = NOW(),

	                        products_weight = '$weight',

	                        manufacturers_id = '$manufacturer_id',

	                        products_status = $status

	                    WHERE products_id = $product_id;

	                   ";

	            if (!$live_result = $db->sql_query($sql)) {

	                 $this->report('[SQL failure] ' . $sql . '\r\n' . $db->sql_error($live_result));

	            } elseif (DEBUG) {

	                 $this->report('[SQL ok] ' . $sql);

	            }

	        }

         	//Import data in products_description table

	        $sql = "SELECT products_id, language_id, products_name, products_description, products_delivery, products_dimensions

	        		FROM puck_products_description

	                WHERE products_id = $products_id AND language_id = " . LANGUAGE_ID;

	        if (!$puck_result = $db->sql_query($sql)) {

	            $this->report('[SQL failure] ' . $sql . '\r\n' . $db->sql_error($puck_result));

	        } elseif (DEBUG) {

	            $this->report('[SQL ok] ' . $sql);

	        }

	        $row = $db->sql_fetchrow($puck_result);

	        $lang = $row['language_id'];

	        $name = $db->sql_escape($row['products_name']);

	        $products_delivery = $db->sql_escape($row['products_delivery']);

	        $products_dimensions = $db->sql_escape($row['products_dimensions']);

	        $description = $db->sql_escape($row['products_description']);

	        $sql = "SELECT products_id FROM products_description

                WHERE products_id = $product_id AND language_id = " . LANGUAGE_ID;

	        if (!$live_result = $db->sql_query($sql)) {

            	$this->report('[SQL failure] ' . $sql . '\r\n' . $db->sql_error($live_result));

	        } elseif (DEBUG) {

	            $this->report('[SQL ok] ' . $sql);

	        }

	        if (!$row = $db->sql_fetchrow($live_result)) {

	            //New product

	            $sql = "INSERT INTO products_description

	                    (products_id, language_id, products_name, products_description, products_delivery, products_dimensions)

	                    VALUES ($product_id,  $lang, '$name', '$description', '$products_delivery', '$products_dimensions')";

	            if (!$live_result = $db->sql_query($sql)) {

	                 $this->report('[SQL failure] ' . $sql . '\r\n' . $db->sql_error($live_result));

                } elseif (DEBUG) {

                    $this->report('[SQL ok] ' . $sql);

                } else {

                	$this->report("[$name] - Updated");

				}


	            if (!$products_id = $db->sql_nextid()) {

	                $this->report("[SQL failure] No product id returned for $sql, although the query was successful.");

	                return;

	            }

	        } else {

	            //Updates products description

	            $sql = "UPDATE products_description

	                    SET products_name = '$name',

	                        products_description = '$description',

	                        products_delivery = '$products_delivery',

	                        products_dimensions = '$products_dimensions'

	                    WHERE products_id = $product_id AND language_id = $lang";

	            if (!$live_result = $db->sql_query($sql)) {

	                 $this->report('[SQL failure] ' . $sql . '\r\n' . $db->sql_error($live_result));

                } elseif (DEBUG) {

                    $this->report('[SQL ok] ' . $sql);

                } else {

                	$this->report("[$name] - Product Updated") . '<br>';
				}

	        }



	        $sql = "DELETE FROM products_attributes

                WHERE products_id = $product_id";

	        if (!$db->sql_query($sql)) {

	            $this->report('[SQL failure] ' . $sql);

	        } elseif (DEBUG) {

	            $this->report('[SQL ok] ' . $sql);

	        }

	        $sql = "SELECT products_id, options_id, options_values_id, options_values_price, price_prefix FROM puck_products_attributes WHERE products_id = $id ORDER BY products_id, options_id";

			if (!$puck_result = $db->sql_query($sql)) {

                 $this->report('[SQL failure] ' . $sql . '\r\n' . $db->sql_error($puck_result));

                 printr($puck_result);

            } elseif (DEBUG) {

                 $this->report('[SQL ok] ' . $sql);

            }

            $puck_options_id = 0;

            $options_id = 1;

	        while ($row = $db->sql_fetchrow($puck_result)) {

	        	if ($row['options_id'] != $puck_options_id) {

	        		$options_id = $this->ImportProductOption($row['options_id']);

	        		$puck_options_id = $row['options_id'];

	        	}

				$options_values_id = $this->ImportProductOptionsValue($row['options_values_id']);

				$sql = "REPLACE INTO products_options_values_to_products_options(products_options_id, products_options_values_id) VALUES ($options_id, $options_values_id)";

	            if (!$db->sql_query($sql)) {

	                $this->report('[SQL failure] ' . $sql);

	            } elseif (DEBUG) {

	                $this->report('[SQL ok] ' . $sql);

	            }

				$sql = "INSERT INTO products_attributes(products_id, options_id, options_values_id, options_values_price, price_prefix) VALUES ($product_id, $options_id, $options_values_id, 0, '+')";

				if (!$live_result = $db->sql_query($sql)) {

                    $this->report('[SQL failure] ' . $sql . '\r\n' . $db->sql_error($live_result));

                } elseif (DEBUG) {

                    $this->report('[SQL ok] ' . $sql);

                }

         	}

	   	}

	}

not sure what else you need to see.

[Jack_mcs]

The commands to write to the database are using an external function shown in what you posted. I can't tell you where that function is since this is not standard code for oscommerce.

[PupStar]

@@Jack_mcs

 

if you tell me which bit you are talking about I will have a look

 

Mark

[♥bruyndoncx]

products_options_values_id is empty and missing from the sql statements

[Jack_mcs]

@@PupStar If Carine's answer doesn't fix it, I am referring to the $db variable.

[PupStar]

products_options_values_id is empty and missing from the sql statements

@bruyndoncx

 

sorry Carine but I have stared so long at this code now I just can not see where from

 

Maybe I should open the bottle of honey jack I got from santa lol

[MrPhil]

The error messages are telling you everything you need to get started:

 INSERT INTO products_options_values(language_id, products_options_values_id, products_options_values_name) VALUES ('1', 'Blue')\r\nArray

There are three fields listed, but only two values given. It appears that products_options_values_id is missing from the VALUES list.
 

[sql failure] REPLACE INTO products_options_values_to_products_options(products_options_id, products_options_values_id) VALUES (0, )

There are two fields listed, but the second (products_options_values_id) is missing from the VALUES list.
 

[sql failure] INSERT INTO products_attributes(products_id, options_id, options_values_id, options_values_price, price_prefix) VALUES (1, 0, , 0, '+')\r\nArray

There are five fields listed, but the third (options_values_id) is missing its value.

 

You're going to have to hunt down why these two fields' values are missing. Did your host just upgrade your PHP level? Perhaps the old code was counting on some behavior which has changed.

 

If the code is very old, it might even be relying on register global variables to pass around the data! Is the file it's in being invoked with a URL Query String something like products_options_values_id=xxxx? If so, you may need to add code to explicitly define that variable (from $HTTP_GET_VARS). As the code you gave does not appear to be showing the affected statements (that create the SQL), I can't tell you exactly what variable is missing its value.