Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Help needed for SQL failure


PupStar

Recommended Posts

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

Link to comment
Share on other sites

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.

Support Links:

For Hire: Contact me for anything you need help with for your shop: upgrading, hosting, repairs, code written, etc.

Get the latest versions of my addons

Recommended SEO Addons

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Support Links:

For Hire: Contact me for anything you need help with for your shop: upgrading, hosting, repairs, code written, etc.

Get the latest versions of my addons

Recommended SEO Addons

Link to comment
Share on other sites

products_options_values_id is empty and missing from the sql statements

KEEP CALM AND CARRY ON

I do not use the responsive bootstrap version since i coded my responsive version earlier, but i have bought every 28d of code package to support burts effort and keep this forum alive (albeit more like on life support).

So if you are still here ? What are you waiting for ?!

 

Find the most frequent unique errors to fix:

grep "PHP" php_error_log.txt | sed "s/^.* PHP/PHP/g" |grep "line" |sort | uniq -c | sort -r > counterrors.txt

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Archived

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

×
×
  • Create New...