Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

How to get OsCommerce to work with PHP5 and MySQL5


postma

Recommended Posts

Wow, this has really been a long night.

 

First of all thanks to others here for posting all of the suggestions and what not, I had to use some of them.

 

PHP 5 pretty much has only one problem. In the admin/include/classes/upload.php file you must change line 31 from

$this = null;

to

unset($this);

 

Here is the list of all of the problems caused by MySQL5:

oscommerce.sql script does not load products nor a few other things properly.

This is caused by MySQL 5 not allowing '' to be passed in for datetime values, you must explicitly pass in null.

oscommerce.sql:1083

was

INSERT INTO customers_info VALUES('1', '', '0', now(), '', '0');

now

INSERT INTO customers_info VALUES('1', null, '0', now(), null, '0');

oscommerce.sql:1137-1164:

was

INSERT INTO products VALUES (1,32,'MG200MMS','matrox/mg200mms.gif',299.99, now(),null,'',23.00,1,1,1,0);
INSERT INTO products VALUES (2,32,'MG400-32MB','matrox/mg400-32mb.gif',499.99, now(),null,'',23.00,1,1,1,0);
INSERT INTO products VALUES (3,2,'MSIMPRO','microsoft/msimpro.gif',49.99, now(),null,'',7.00,1,1,3,0);
INSERT INTO products VALUES (4,13,'DVD-RPMK','dvd/replacement_killers.gif',42.00, now(),null,'',23.00,1,1,2,0);
INSERT INTO products VALUES (5,17,'DVD-BLDRNDC','dvd/blade_runner.gif',35.99, now(),null,'',7.00,1,1,3,0);
INSERT INTO products VALUES (6,10,'DVD-MATR','dvd/the_matrix.gif',39.99, now(),null,'',7.00,1,1,3,0);
INSERT INTO products VALUES (7,10,'DVD-YGEM','dvd/youve_got_mail.gif',34.99, now(),null,'',7.00,1,1,3,0);
INSERT INTO products VALUES (8,10,'DVD-ABUG','dvd/a_bugs_life.gif',35.99, now(),null,'',7.00,1,1,3,0);
INSERT INTO products VALUES (9,10,'DVD-UNSG','dvd/under_siege.gif',29.99, now(),null,'',7.00,1,1,3,0);
INSERT INTO products VALUES (10,10,'DVD-UNSG2','dvd/under_siege2.gif',29.99, now(),null,'',7.00,1,1,3,0);
INSERT INTO products VALUES (11,10,'DVD-FDBL','dvd/fire_down_below.gif',29.99, now(),null,'',7.00,1,1,3,0);
INSERT INTO products VALUES (12,10,'DVD-DHWV','dvd/die_hard_3.gif',39.99, now(),null,'',7.00,1,1,4,0);
INSERT INTO products VALUES (13,10,'DVD-LTWP','dvd/lethal_weapon.gif',34.99, now(),null,'',7.00,1,1,3,0);
INSERT INTO products VALUES (14,10,'DVD-REDC','dvd/red_corner.gif',32.00, now(),null,'',7.00,1,1,3,0);
INSERT INTO products VALUES (15,10,'DVD-FRAN','dvd/frantic.gif',35.00, now(),null,'',7.00,1,1,3,0);
INSERT INTO products VALUES (16,10,'DVD-CUFI','dvd/courage_under_fire.gif',38.99, now(),null,'',7.00,1,1,4,0);
INSERT INTO products VALUES (17,10,'DVD-SPEED','dvd/speed.gif',39.99, now(),null,'',7.00,1,1,4,0);
INSERT INTO products VALUES (18,10,'DVD-SPEED2','dvd/speed_2.gif',42.00, now(),null,'',7.00,1,1,4,0);
INSERT INTO products VALUES (19,10,'DVD-TSAB','dvd/theres_something_about_mary.gif',49.99, now(),null,'',7.00,1,1,4,0);
INSERT INTO products VALUES (20,10,'DVD-BELOVED','dvd/beloved.gif',54.99, now(),null,'',7.00,1,1,3,0);
INSERT INTO products VALUES (21,16,'PC-SWAT3','sierra/swat_3.gif',79.99, now(),null,'',7.00,1,1,7,0);
INSERT INTO products VALUES (22,13,'PC-UNTM','gt_interactive/unreal_tournament.gif',89.99, now(),null,'',7.00,1,1,8,0);
INSERT INTO products VALUES (23,16,'PC-TWOF','gt_interactive/wheel_of_time.gif',99.99, now(),null,'',10.00,1,1,8,0);
INSERT INTO products VALUES (24,17,'PC-DISC','gt_interactive/disciples.gif',90.00, now(),null,'',8.00,1,1,8,0);
INSERT INTO products VALUES (25,16,'MSINTKB','microsoft/intkeyboardps2.gif',69.99, now(),null,'',8.00,1,1,2,0);
INSERT INTO products VALUES (26,10,'MSIMEXP','microsoft/imexplorer.gif',64.95, now(),null,'',8.00,1,1,2,0);
INSERT INTO products VALUES (27,8,'HPLJ1100XI','hewlett_packard/lj1100xi.gif',499.99, now(),null,'',45.00,1,1,9,0);

now

INSERT INTO products VALUES (1,32,'MG200MMS','matrox/mg200mms.gif',299.99, now(),null,null,23.00,1,1,1,0);
INSERT INTO products VALUES (2,32,'MG400-32MB','matrox/mg400-32mb.gif',499.99, now(),null,null,23.00,1,1,1,0);
INSERT INTO products VALUES (3,2,'MSIMPRO','microsoft/msimpro.gif',49.99, now(),null,null,7.00,1,1,3,0);
INSERT INTO products VALUES (4,13,'DVD-RPMK','dvd/replacement_killers.gif',42.00, now(),null,null,23.00,1,1,2,0);
INSERT INTO products VALUES (5,17,'DVD-BLDRNDC','dvd/blade_runner.gif',35.99, now(),null,null,7.00,1,1,3,0);
INSERT INTO products VALUES (6,10,'DVD-MATR','dvd/the_matrix.gif',39.99, now(),null,null,7.00,1,1,3,0);
INSERT INTO products VALUES (7,10,'DVD-YGEM','dvd/youve_got_mail.gif',34.99, now(),null,null,7.00,1,1,3,0);
INSERT INTO products VALUES (8,10,'DVD-ABUG','dvd/a_bugs_life.gif',35.99, now(),null,null,7.00,1,1,3,0);
INSERT INTO products VALUES (9,10,'DVD-UNSG','dvd/under_siege.gif',29.99, now(),null,null,7.00,1,1,3,0);
INSERT INTO products VALUES (10,10,'DVD-UNSG2','dvd/under_siege2.gif',29.99, now(),null,null,7.00,1,1,3,0);
INSERT INTO products VALUES (11,10,'DVD-FDBL','dvd/fire_down_below.gif',29.99, now(),null,null,7.00,1,1,3,0);
INSERT INTO products VALUES (12,10,'DVD-DHWV','dvd/die_hard_3.gif',39.99, now(),null,null,7.00,1,1,4,0);
INSERT INTO products VALUES (13,10,'DVD-LTWP','dvd/lethal_weapon.gif',34.99, now(),null,null,7.00,1,1,3,0);
INSERT INTO products VALUES (14,10,'DVD-REDC','dvd/red_corner.gif',32.00, now(),null,null,7.00,1,1,3,0);
INSERT INTO products VALUES (15,10,'DVD-FRAN','dvd/frantic.gif',35.00, now(),null,null,7.00,1,1,3,0);
INSERT INTO products VALUES (16,10,'DVD-CUFI','dvd/courage_under_fire.gif',38.99, now(),null,null,7.00,1,1,4,0);
INSERT INTO products VALUES (17,10,'DVD-SPEED','dvd/speed.gif',39.99, now(),null,null,7.00,1,1,4,0);
INSERT INTO products VALUES (18,10,'DVD-SPEED2','dvd/speed_2.gif',42.00, now(),null,null,7.00,1,1,4,0);
INSERT INTO products VALUES (19,10,'DVD-TSAB','dvd/theres_something_about_mary.gif',49.99, now(),null,null,7.00,1,1,4,0);
INSERT INTO products VALUES (20,10,'DVD-BELOVED','dvd/beloved.gif',54.99, now(),null,null,7.00,1,1,3,0);
INSERT INTO products VALUES (21,16,'PC-SWAT3','sierra/swat_3.gif',79.99, now(),null,null,7.00,1,1,7,0);
INSERT INTO products VALUES (22,13,'PC-UNTM','gt_interactive/unreal_tournament.gif',89.99, now(),null,null,7.00,1,1,8,0);
INSERT INTO products VALUES (23,16,'PC-TWOF','gt_interactive/wheel_of_time.gif',99.99, now(),null,null,10.00,1,1,8,0);
INSERT INTO products VALUES (24,17,'PC-DISC','gt_interactive/disciples.gif',90.00, now(),null,null,8.00,1,1,8,0);
INSERT INTO products VALUES (25,16,'MSINTKB','microsoft/intkeyboardps2.gif',69.99, now(),null,null,8.00,1,1,2,0);
INSERT INTO products VALUES (26,10,'MSIMEXP','microsoft/imexplorer.gif',64.95, now(),null,null,8.00,1,1,2,0);
INSERT INTO products VALUES (27,8,'HPLJ1100XI','hewlett_packard/lj1100xi.gif',499.99, now(),null,null,45.00,1,1,9,0);

oscommerce.sql:1354:

was

INSERT INTO reviews VALUES (1,19,1,'John doe',5, now(),'',0);

now

INSERT INTO reviews VALUES (1,19,1,'John doe',5, now(),null,0);

oscommerce.sql:1358-1361:

was

INSERT INTO specials VALUES (1,3, 39.99, now(), '', '', '', '1');
INSERT INTO specials VALUES (2,5, 30.00, now(), '', '', '', '1');
INSERT INTO specials VALUES (3,6, 30.00, now(), '', '', '', '1');
INSERT INTO specials VALUES (4,16, 29.99, now(), '', '', '', '1');

now

INSERT INTO specials VALUES (1,3, 39.99, now(), null, null, null, '1');
INSERT INTO specials VALUES (2,5, 30.00, now(), null, null, null, '1');
INSERT INTO specials VALUES (3,6, 30.00, now(), null, null, null, '1');
INSERT INTO specials VALUES (4,16, 29.99, now(), null, null, null, '1');

 

 

There are quite a few errors in various select statements in index.php that cause 1054 errors, like unknown column 'p.products_id'. This is because those statements should read p2c.products_id not p.products_id

index.php:175:

was

		$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "'";

now

		$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_SPECIALS . " s on p2c.products_id = s.products_id where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "'";

index.php:184

was

		$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'";

now

		$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_SPECIALS . " s on p2c.products_id = s.products_id where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'";

index.php:187:

was

		$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'";

now

		$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_SPECIALS . " s on p2c.products_id = s.products_id where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'";

 

 

There are a few places where a 1064 error is caused because a negative value is passed in a limit statement. You will need to modify the code to pick the max between $offset and 0, like $max(offset,0)

includes/classes/split_page_results.php:67:

was

	  $this->sql_query .= " limit " . $offset . ", " . $this->number_of_rows_per_page;

now

	  $this->sql_query .= " limit " . max($offset,0) . ", " . $this->number_of_rows_per_page;

admin/includes/classes/split_page_results.php:38:

was

	  $sql_query .= " limit " . $offset . ", " . $max_rows_per_page;

now

	  $sql_query .= " limit " . max($offset,0) . ", " . $max_rows_per_page;

 

After making these changes it pretty much worked, I haven't found any other problems yet, but someone may add to this if they do or if I forgot something.

Enjoy, my labor should hopefully help many others... And while I'm at it I should plug our store at http://www.mediapitstop.com or at http://store.mediapitstop.net

After going to the .com you will see why we need a better faster solution, our site is slow...

Take care (:

Kory Postma

MS Computer Science (summa cum laude), WUSTL

BS Physics (summa cum laude), WUSTL

Link to comment
Share on other sites

I feel your pain, I spent a day getting it to work on mysql 5 also. One thing though, are you getting the correct searches to come up with only changing p.products_id to p2c.products_id ??

 

I made a post in this thread:

 

http://www.oscommerce.com/forums/index.php?showtopic=178805

 

I had to completely change the SQL to get it to work for SQL:2003 compliance(mysql5 is using it now). You basically have to nest the join statements. The searches now work correctly for me. I still am going to try to trim down the searching cause I have 600,000 products in my DB and it can take 5-7 sec. for a search to complete.

Link to comment
Share on other sites

I feel your pain, I spent a day getting it to work on mysql 5 also. One thing though, are you getting the correct searches to come up with only changing p.products_id to p2c.products_id ??

 

I made a post in this thread:

 

http://www.oscommerce.com/forums/index.php?showtopic=178805

 

I had to completely change the SQL to get it to work for SQL:2003 compliance(mysql5 is using it now). You basically have to nest the join statements. The searches now work correctly for me. I still am going to try to trim down the searching cause I have 600,000 products in my DB and it can take 5-7 sec. for a search to complete.

 

Only 5-7 seconds? Damn, go on over and see a SQL Server 2000 version with only 400,000 items loaded at www.mediapitstop.com. It takes nearly 1-3 minutes per page. It runs horribly slow. I have not tried the searches and it may cause problems, thanks for mentioning this. I saw someone else did this fix and it worked for me, but I didn't actually try searching.

 

Funny, I just tried it now and I didn't modify this particular SQL statment, I will have to try it after fixing this. I will add it to my large list of things todo.

Thanks,

Kory

Link to comment
Share on other sites

I have made a contribution that contains all of these files changes here, http://www.oscommerce.com/community/contributions,3727.

Enjoy,

Kory

 

Kory:

 

Thanks a million for the contribution. I was just about to give up as I had spent several days and several hours trying to get this to work.

 

However, I am still having problems with the customer registration and the manufacturers script.

 

Any ideas??

 

Thanks again for sharing the information.

:thumbsup:

Link to comment
Share on other sites

Kory:

 

Thanks a million for the contribution. I was just about to give up as I had spent several days and several hours trying to get this to work.

 

However, I am still having problems with the customer registration and the manufacturers script.

 

Any ideas??

 

Thanks again for sharing the information.

:thumbsup:

 

This is the error I am getting when a customer attempts to register.

 

1364 - Field 'customers_default_address_id' doesn't have a default value

 

insert into customers (customers_firstname, customers_lastname, customers_email_address, customers_telephone, customers_fax, customers_newsletter, customers_password, customers_gender, customers_dob) values ('marie', 'marie', '[email protected]', '12312312345', '', '', '6e25fcfae98ee1022c76357cf5e0a1b8:3b', 'f', '20050207')

 

[TEP STOP]

 

Thanks

Link to comment
Share on other sites

To fix the searching one must make the following change. This too was a simple change.

 

In the file advanced_search_results.php on line 213:

was

  $from_str = "from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m using(manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c";

now

  $from_str = "from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m using(manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd left join " . TABLE_SPECIALS . " s on pd.products_id = s.products_id, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c";

 

If anyone notices anything else please let me know. I will look into the customer registration and manufacturer issues now.

Thanks,

Kory

Link to comment
Share on other sites

To fix the customer registration issue requires a change to the .sql file used to do the install.

In install/oscommerce.sql on line 169:

was

   customers_default_address_id int NOT NULL,

now

   customers_default_address_id int(1) NOT NULL,

 

If you have already installed your store login to your MySQL console or admin program and type the following (change the {DATABASE_NAME_HERE} to the name of your database), this is basically adding the default value of 1 to that field:

ALTER TABLE `{DATABASE_NAME_HERE}`.`customers` MODIFY COLUMN `customers_default_address_id` INTEGER NOT NULL DEFAULT 1;

 

If you then run into the problem of not being able to send mail as I did and you are on a

Windows machine, like I am. You must set OsCommerce to use SMTP instead of sendmail, this is under Email options in the admin configuration screen.

 

If you get an error saying it can't relay or something like that and you are running the store locally on a test machine then follow these steps:

"SMTP server response: 550 5.7.1 Unable to relay.."

 

To solve the problem here is what I did to enable SMTP Relaying on my test machine:

 

1) Go to: start > settings > control panel > Administrative Tools > Internet Information Services

2) Expand the " (local computer)" node

3) Right click on your SMTP server > go to "Properties"

4) Click "Access" tab

5) Under Relay Restrictions, click the "Relay" button

6) Click "Add"

7) Select "Single Computer" and enter IP address 127.0.0.1 (which points to localhost)

8) Hit OK, OK (until the properties dialog is closed)

 

At this point it should work, but just to be extra sure, create a php page called SMTPtest.php and drop in the following code:

<?
$to = "[email protected]";
$subject = "test from localhost";
$msg = "I completely understand SMTP servers now!";
$headers = "From: [email protected]\nReply-To: [email protected]";
$config = "[email protected]";
mail("$to", "$subject", "$msg", "$headers", "$config");
echo "finished!";
?>

 

I will now look into the manufacturer issue.

 

Enjoy,

Kory

Link to comment
Share on other sites

To fix the manufacturer issue of the products not showing up from the drop down menu the following must be changed: in index.php on line 178:

was

		$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where p.products_status = '1' and pd.products_id = p.products_id and pd.language_id = '" . (int)$languages_id . "' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "'";

now

		$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from (" . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m) left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where p.products_status = '1' and pd.products_id = p.products_id and pd.language_id = '" . (int)$languages_id . "' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "'";

 

I noticed this has no problems if it is filtered by categories, so this should resolve the issue completely.

 

I will try to make changes to the contribution so that these updates are reflected in there as well. If anyone else notices anything please post it and I'll try to fix it. This is really helping me to learn OsCommerce.

Kory

Link to comment
Share on other sites

To fix the manufacturer issue of the products not showing up from the drop down menu the following must be changed: in index.php on line 178:

was

		$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where p.products_status = '1' and pd.products_id = p.products_id and pd.language_id = '" . (int)$languages_id . "' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "'";

now

		$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from (" . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m) left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where p.products_status = '1' and pd.products_id = p.products_id and pd.language_id = '" . (int)$languages_id . "' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "'";

 

I noticed this has no problems if it is filtered by categories, so this should resolve the issue completely.

 

I will try to make changes to the contribution so that these updates are reflected in there as well. If anyone else notices anything please post it and I'll try to fix it. This is really helping me to learn OsCommerce.

Kory

 

Kory, Thanks so much. I am going to test the recent contributions. I think I was having a few more issues and yes one was a problem with the e-mail. However, not the problem that you described above. I am going to test this and I will post any new issues.

 

Marie

:D

Link to comment
Share on other sites

Kory, Thanks so much. I am going to test the recent contributions. I think I was having a few more issues and yes one was a problem with the e-mail. However, not the problem that you described above. I am going to test this and I will post any new issues.

 

Marie

:D

 

Sure let me know if you happen to find anything else. I have noticed a few issues but this has more to do with OsCommerce than the upgrades. Basically if you enter 9999999999999 for the quantity for a HP printer then you try to check out it will say you don't have enough stock. Then you just click continue and it complains about the final_price not having a default value. I have tested it with 100 copies of Unreal Tournament and it seems to work fine so it seems to be an issue with OsCommerce accepting these values. It would be best for them to fix this issue in the future. Anyhow, I am open to anymore suggestions or problems.

I will check this from time to time...

Kory

Link to comment
Share on other sites

Kory, Thanks so much. I am going to test the recent contributions. I think I was having a few more issues and yes one was a problem with the e-mail. However, not the problem that you described above. I am going to test this and I will post any new issues.

 

Marie

:D

 

 

Kory:

 

I am still getting an error message when I use the drop down box to list the manufacturer products in the body of the page. When I use the drop down manufacturer box, I now see that the "whats new" item changes to correspond to the manufacturer.

Here is the error I am still receiving.

1054 - Unknown column 'p.products_id' in 'on clause'

 

select count(p.products_id) as total from products p, products_description pd, manufacturers m left join specials s on p.products_id = s.products_id where p.products_status = '1' and pd.products_id = p.products_id and pd.language_id = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '6'

[TEP STOP]

 

Still getting the same error message when attempting to register a customer.

 

Here is one more for you.

When I try to add to the basket I get

 

1364 - Field 'final_price' doesn't have a default value

 

insert into customers_basket (customers_id, products_id, customers_basket_quantity, customers_basket_date_added) values ('1', '3', '1', '20051106')

 

[TEP STOP]

 

If I refresh the browser the product adds to the basket.

 

Does any of this happen with MySQL earlier versions??

Thanks,

Marie

Link to comment
Share on other sites

Kory:

 

I am still getting an error message when I use the drop down box to list the manufacturer products in the body of the page. When I use the drop down manufacturer box, I now see that the "whats new" item changes to correspond to the manufacturer.

Here is the error I am still receiving.

Still getting the same error message when attempting to register a customer.

 

Here is one more for you.

When I try to add to the basket I get

If I refresh the browser the product adds to the basket.

 

Does any of this happen with MySQL earlier versions??

Thanks,

Marie

 

Did you make all of the above changes? It seems that you didn't. Also what file are these errors in? It seems that the fix for the manufacturers was not put in because I don't see the change made to the SQL statement, possibly the same for the customer registration change.

 

Regarding the adding of a product to the basket if you read my previous message, it states that I noticed the same issue but it happened in a strange case. I just tried to break it anyway possible and I can't, it is now working fine for me. Which product did you click on? What exactly did you do to produce it?

 

If you still cannot get it to work I would recommend clearing out the database and using the contribution file linked above to overwrite all of the files that need to be changed. Then try it out and see if it is still giving you problems. I don't seem to notice any other issues at the moment but I haven't tried everything on the store yet.

Kory

Link to comment
Share on other sites

Kory:

I installed your newest contribution. The manufacturer drop down is now working.

 

However still having the same issue with adding to basket (refresh works) does this on all items.

 

Last issue:

After the order has been placed and confirmed and you see the man on the surf board, when you click continue you get:

1264 - Out of range value adjusted for column 'products_id' at row 1

 

insert into products_notifications (products_id, customers_id, date_added) values ('', '1', now())

 

[TEP STOP]

 

Thanks

Link to comment
Share on other sites

Kory:

I installed your newest contribution. The manufacturer drop down is now working.

 

However still having the same issue with adding to basket (refresh works) does this on all items.

 

Last issue:

After the order has been placed and confirmed and you see the man on the surf board, when you click continue you get:

Thanks

 

OK, this could be a problem, I will look into it.

Kory

Link to comment
Share on other sites

Here are a few more changes to the files to fix other weird problems.

 

To fix the "final_price" default value error, we need to give it a default value in the install/oscommerce.sql file on line 183:

was

  final_price decimal(15,4) NOT NULL,

now

  final_price decimal(15,4) DEFAULT 0 NOT NULL,

On line 311:

was

  final_price decimal(15,4) NOT NULL,

now

  final_price decimal(15,4) DEFAULT 0 NOT NULL,

 

To fix a previous problem I said to change install/oscommerce.sql line 169:

From

   customers_default_address_id int NOT NULL,

To

   customers_default_address_id int(1) NOT NULL,

But the real solution is to change it to

   customers_default_address_id int DEFAULT 1 NOT NULL,

 

To fix errors associated with Product Notifications you must change the includes/application_top.php file starting on line 383:

was

for ($i=0, $n=sizeof($notify); $i<$n; $i++) {
 $check_query = tep_db_query("select count(*) as count from " . TABLE_PRODUCTS_NOTIFICATIONS . " where products_id = '" . $notify[$i] . "' and customers_id = '" . $customer_id . "'");
 $check = tep_db_fetch_array($check_query);
 if ($check['count'] < 1) {
tep_db_query("insert into " . TABLE_PRODUCTS_NOTIFICATIONS . " (products_id, customers_id, date_added) values ('" . $notify[$i] . "', '" . $customer_id . "', now())");
 }
}

now

for ($i=0, $n=sizeof($notify); $i<$n; $i++) {
 if (strlen($notify[$i])>0) {
$check_query = tep_db_query("select count(*) as count from " . TABLE_PRODUCTS_NOTIFICATIONS . " where products_id = '" . $notify[$i] . "' and customers_id = '" . $customer_id . "'");
$check = tep_db_fetch_array($check_query);
if ($check['count'] < 1) {
  tep_db_query("insert into " . TABLE_PRODUCTS_NOTIFICATIONS . " (products_id, customers_id, date_added) values ('" . $notify[$i] . "', '" . $customer_id . "', now())");
}
 }
}

 

This should resolve those other errors. Let me know what anyone else finds and then I will update the contributions to reflect these changes.

Kory

Link to comment
Share on other sites

In case you do not want to reinstall your DATABASE with the install/oscommerce.sql script you can just use the following (replace osc with your database name):

ALTER TABLE `osc`.`customers_basket` MODIFY COLUMN `final_price` DECIMAL(15,4) NOT NULL DEFAULT 0;
ALTER TABLE `osc`.`orders_products` MODIFY COLUMN `final_price` DECIMAL(15,4) NOT NULL DEFAULT 0;

 

Kory

Link to comment
Share on other sites

In case you do not want to reinstall your DATABASE with the install/oscommerce.sql script you can just use the following (replace osc with your database name):

ALTER TABLE `osc`.`customers_basket` MODIFY COLUMN `final_price` DECIMAL(15,4) NOT NULL DEFAULT 0;
ALTER TABLE `osc`.`orders_products` MODIFY COLUMN `final_price` DECIMAL(15,4) NOT NULL DEFAULT 0;

 

Kory

 

 

INCREDIBLE

 

I think you have solved all of the issues. It was a real pleasure to have met you.

Much luck to you! If you get a chance, I will leave my test site running while I go for a walk. Try it, I think it works perfect now. I will let you know if I run into any more issues when I return from my walk.

 

Thanks again!

Marie

Link to comment
Share on other sites

This problem was found when taking one product and copying it and it did not have a date_available defined. The previous version of MySQL took '' to mean null and now it is an error. This just checks if it is '' and then explicitly sets it to null.

The change is in admin/categories.php on line 292:

replace this line

tep_db_query("insert into " . TABLE_PRODUCTS . " (products_quantity, products_model,products_image, products_price, products_date_added, products_date_available, products_weight, products_status, products_tax_class_id, manufacturers_id) values ('" . tep_db_input($product['products_quantity']) . "', '" . tep_db_input($product['products_model']) . "', '" . tep_db_input($product['products_image']) . "', '" . tep_db_input($product['products_price']) . "',  now(), '" . tep_db_input($product['products_date_available']) . "', '" . tep_db_input($product['products_weight']) . "', '0', '" . (int)$product['products_tax_class_id'] . "', '" . (int)$product['manufacturers_id'] . "')");

with all of this

if ($product['products_date_available'] == '') {
		  tep_db_query("insert into " . TABLE_PRODUCTS . " (products_quantity, products_model,products_image, products_price, products_date_added, products_date_available, products_weight, products_status, products_tax_class_id, manufacturers_id) values ('" . tep_db_input($product['products_quantity']) . "', '" . tep_db_input($product['products_model']) . "', '" . tep_db_input($product['products_image']) . "', '" . tep_db_input($product['products_price']) . "',  now(), null, '" . tep_db_input($product['products_weight']) . "', '0', '" . (int)$product['products_tax_class_id'] . "', '" . (int)$product['manufacturers_id'] . "')");
		} else {
		  tep_db_query("insert into " . TABLE_PRODUCTS . " (products_quantity, products_model,products_image, products_price, products_date_added, products_date_available, products_weight, products_status, products_tax_class_id, manufacturers_id) values ('" . tep_db_input($product['products_quantity']) . "', '" . tep_db_input($product['products_model']) . "', '" . tep_db_input($product['products_image']) . "', '" . tep_db_input($product['products_price']) . "',  now(), '" . tep_db_input($product['products_date_available']) . "', '" . tep_db_input($product['products_weight']) . "', '0', '" . (int)$product['products_tax_class_id'] . "', '" . (int)$product['manufacturers_id'] . "')");
		}

 

I will await anymore PMs, e-mails, or messages here to fix other problems.

Kory

Link to comment
Share on other sites

  • 2 weeks later...

Archived

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

×
×
  • Create New...