mysql_insert_id() and products_id and categores_id = 0 in products_to_categories table

[Dmacman]

Hi All,

 

I have searched everywhere and do not see anyone with this issue, I have seen some with the insert_id errors and some with the id=0 errors where they feel its related to the session key not set to an int value and not auto incrementing. But none like what I am getting.

 

When I try to insert a new product, after I preview, the URL has the correct values,

 

store/admin/categories.php?cPath=2&action=new_product_preview

 

Then I hit submit and I get...

 

Warning: mysql_insert_id(): supplied argument is not a valid MySQL-Link resource in path-removed\site\store\admin\includes\functions\database.php on line 94

 

Warning: Cannot modify header information - headers already sent by (output started at path-removed\store\admin\includes\functions\database.php:94) in D:\Program Files\Apache Group\Apache2\htdocs\izzit\store\admin\includes\functions\general.php on line 28

 

and in the products_to_categories table there is now a products_id=0 - categories_id=0 where this product_id should be 38 and the categories_id should be 2?

 

Somehow they both default to zero and then I get the error and those 2 lines are...

 

database.php on line 94 --->

return mysql_insert_id($link);

 

and

 

general.php on line 28 -->

header('Location: ' . $url);

 

I am stuck. I cannot seem to find anyone on this forum or on google or yahoo (even the foreign language posts) who had this one.

 

I appreciate the help in advance,

 

Don

[♥geoffreywalton]

Did you try google

 

oscommerce Warning: Cannot modify header information - headers already sent by

 

First link could solve your problem, hope so.

[Guest]

The knowledgebase may help.

[Dmacman]

Thanks for the replies, but I stripped out all extra returns, and whitespace when I put the site up months ago (this site was up and running for awhile).

 

As with most osC sites, I needed to mod the site and I am not sure what might have caused the change since I put several mod's in place since I last needed to add a new product.

 

Mods in place are.

 

Optional Related Products

How did you hear about us?

UPS XML shipping

Batch Print

Discount Coupons (not the orginal CCGV)

 

And I did search the knowledge base (no results matched my errors). I searched the forums, again, none similar to my products_to_categories errors.

 

I searched Google and Yahoo up to pages 10 (including the foreign language results that seemed might be similar errors, even though I dont speek russian).

 

I have seen the "Warning: Cannot modify header information" many times and I dont think that's the problem, I think it the zero id's in the products_to_categories table.

 

If I try to insert another product after the original error, I get...

 

Warning: mysql_insert_id(): supplied argument is not a valid MySQL-Link resource in D:\Program Files\Apache Group\Apache2\htdocs\izzit\store\admin\includes\functions\database.php on line 94

1062 - Duplicate entry '0-2' for key 1

 

insert into products_to_categories (products_id, categories_id) values ('0', '2')

 

[TEP STOP]

 

 

No luck.

 

I am still open to more suggestions, or advice.

 

Thanks,

 

Don

[Guest]

Thanks for the replies, but I stripped out all extra returns, and whitespace when I put the site up months ago (this site was up and running for awhile).

 

As with most osC sites, I needed to mod the site and I am not sure what might have caused the change since I put several mod's in place since I last needed to add a new product.

 

Mods in place are.

 

Optional Related Products

How did you hear about us?

UPS XML shipping

Batch Print

Discount Coupons (not the orginal CCGV)

 

And I did search the knowledge base (no results matched my errors). I searched the forums, again, none similar to my products_to_categories errors.

 

I searched Google and Yahoo up to pages 10 (including the foreign language results that seemed might be similar errors, even though I dont speek russian).

 

I have seen the "Warning: Cannot modify header information" many times and I dont think that's the problem, I think it the zero id's in the products_to_categories table.

 

If I try to insert another product after the original error, I get...

No luck.

 

I am still open to more suggestions, or advice.

 

Thanks,

 

Don

What is the removed directory for?

[Dmacman]

Just for security sake, albeit I missed one.

 

:)

[Jan Zonjee]

I am still open to more suggestions, or advice.

You must have made a mistake IMHO when adding code for one of those mods to admin/categories.php.

 

Look for the part in case 'insert_product': case 'update_product':

Around line 228 in a original categories.php:

 

		  if ($action == 'insert_product') {
		$insert_sql_data = array('products_date_added' => 'now()');

		$sql_data_array = array_merge($sql_data_array, $insert_sql_data);

		tep_db_perform(TABLE_PRODUCTS, $sql_data_array);
		$products_id = tep_db_insert_id();

		tep_db_query("insert into " . TABLE_PRODUCTS_TO_CATEGORIES . " (products_id, categories_id) values ('" . (int)$products_id . "', '" . (int)$current_category_id . "')");
	  } elseif ($action == 'update_product') {

As you can see first an insert in the table products is done (tep_db_perform(TABLE_PRODUCTS, $sql_data_array);) and the products_id is gotten with $products_id = tep_db_insert_id();. Then the table products_to_categories is updated. You surely have code in between the tep_db_perform and tep_db_query that messes with the $products_id and $categories_id.

[Dmacman]

I could not see anything in there that might be the issue except the "Optional Related Products" Mod, so I uninstalled it, and I still get that error.

 

Then I went back to the 'stock' categories.php file to ensure I had a clean version, and I still get that error.

 

Note: that I go thru my database and clean out the products tables, including the products_to_categories table and remove the junk records to ensure I don't have any stray records messing up anything.

 

The one thing I notice is that I consistently get a products_to_categories record products_id=0(this is incorrect, there never would be an id=0. it autoincrements and would be in the 40's) and categories_id=2(this is correct).

 

I appreciate the help and I hope this new information is leading you (us) to a fix.

 

Thanks,

Don

[Dmacman]

Hi Jan,

 

I now have....

 

 if ($action == 'insert_product') {
		$insert_sql_data = array('products_date_added' => 'now()');

		$sql_data_array = array_merge($sql_data_array, $insert_sql_data);

		tep_db_perform(TABLE_PRODUCTS, $sql_data_array);
		$products_id = tep_db_insert_id();

		tep_db_query("insert into " . TABLE_PRODUCTS_TO_CATEGORIES . " (products_id, categories_id) values ('" . (int)$products_id . "', '" . (int)$current_category_id . "')");
	  } elseif ($action == 'update_product') {
		$update_sql_data = array('products_last_modified' => 'now()');

		$sql_data_array = array_merge($sql_data_array, $update_sql_data);

		tep_db_perform(TABLE_PRODUCTS, $sql_data_array, 'update', "products_id = '" . (int)$products_id . "'");
	  }

 

in store/admin/categories.php

 

I still get the error and the products_id=0 and categories_id=2 in the products_to_categories table. (I added this reply after the previous post, but this code was part of the "I went back to the 'stock' categories.php file to ensure I had a clean version" quote. (Just wanted to clarify for the record).

 

Thanks,

Don

[Jan Zonjee]

I still get the error and the products_id=0 and categories_id=2 in the products_to_categories table.

But did the new product go into the database? The update to products_to_categories is after the new product has been added to the table products. If that hasn't happened, the error is earlier.

[Jan Zonjee]

If it did go in the database and only getting the insert id is a problem you could try coding around it:

// $products_id = tep_db_insert_id();

$last_products_id_query = tep_db_query("select MAX(products_id) from '" . TABLE_PRODUCTS . "');
$products_id = tep_db_fetch_array($last_products_id_query);

[Dmacman]

OK, it did insert the product into the products database ok (products_id=50) [question 1]

 

And for your fix in the second post...

 

$last_products_id_query = tep_db_query("select MAX(products_id) from " . TABLE_PRODUCTS . "");
		$products_id = tep_db_fetch_array($last_products_id_query);

// I added a second " near the end of the first line "");

 

it gives me....

 

1062 - Duplicate entry '1-2' for key 1

 

insert into products_to_categories (products_id, categories_id) values ('1', '2')

 

[TEP STOP]

 

and does not insert a record for the products_to_categories (since I have a products_id=1 and categories_id=2 <-- but it did not keep the products_id=50 like it should from your MAX code?)

 

I know you are close.

 

Thoughts?

 

Thanks again,

 

Don

[Jan Zonjee]

it gives me....

and does not insert a record for the products_to_categories (since I have a products_id=1 and categories_id=2 <-- but it did not keep the products_id=50 like it should from your MAX code?)

Oops, a bit too hasty.

 

Try this:

$last_products_id_query = tep_db_query("select MAX(products_id) as last_products_id from " . TABLE_PRODUCTS . "");
		$last_products_id_result = tep_db_fetch_array($last_products_id_query);
$products_id = $last_products_id_result['last_products_id'];

[Dmacman]

Whooooohoooooo!!!!!!!!!!!!!!!!!!!!!

 

That did it.

 

Finally..

 

You ROCK!

 

 

I don't know how it broke, but you fixed it.

 

I owe you big time!

 

Thanks for the great Christmas present.

 

Consider this RESOLVED.

 

Anything I can do for you, you got it.

 

Don.