Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

MySQL question from a novice


dcmo

Recommended Posts

Hi,

 

I'm just reading through the files in preparation to install the separate pricing for customer groups contribution (I have my shop up and running and have installed other conributions before no problem e.g. easypopulate).

 

Trouble is, the readme file says this

 

"Run the spcc_v41_install.sql database installation file on your MySQL database."

 

Ummm.... :blush: what does that mean? What do I have to do?

 

I've tried seaching but haven't found the answer that way. :'(

 

dcmo

Link to comment
Share on other sites

They mean you need to get an app that talks directly to your database, like phpmyadmin. It's free and very handy to have if you're going to run any mysql database. Installation isn't hard. There's a place where you upload files that are sql queries; which I'll bet is what that is. It will insert tables and/or modify ones already there to complete the installation.

Link to comment
Share on other sites

So, to be a little clearer, install phpmyadmin in a directory on your server (at your host). Browse to it. www.myhost.com/phpmyadminfolder/index.php. find the space where you browse and upload a file from your computer. Do the upload of the sql file in the package. If you did it right and the file is good, you'll get a success message and your contrib should start working. (assuming all other things about the contrib are good).

Good Luck!

Link to comment
Share on other sites

Thanks for that... :D after reading your first reply I had a poke around and found my ISP already has phpmyadmin installed as part of the package. Looks pretty scary! I'll reread your second reply again tomorrow evening after I've finished reading all the stuff about the contribution and give it a go then

 

Cheers brushwood

 

dcmo

Link to comment
Share on other sites

Help!

 

So, to be a little clearer, install phpmyadmin in a directory on your server (at your host). Browse to it. www.myhost.com/phpmyadminfolder/index.php.

 

Ok to this part :thumbsup:

 

but...

 

find the space where you browse and upload a file from your computer.

 

:blush: :'(

 

So what I have is "...../phpMyAdmin/index.php" open and I see 2 panels.

 

The one on the left says phpMyAdmin and 5 icons for home, exit, mysql and 2 for documentation (neither of which help btw). Under that is a drop down box where I have selected my osc database.

 

The right hand panel has 6 tabs called Structure, SQL, Export, Search , query and operations. The SQL tab is the only one that has a browse for text file bit which says it is for running queries.

 

Is this the place you mean? I hope not 'cos I tried it and it gave loads of errors in bright red text....

 

:blink: The new install directions say

 

"Before beginning the manual install you need to; ..... Run the spcc_v41_install.sql database installation file on your MySQL database."

 

But am I supposed to upload my modified files first before running spcc_v41_install.sql or can I do this afterwards? I've made all the changes to a copy of the shop but have yet to upload any live changes.

 

Any help much appreciated.

 

Do the upload of the sql file in the package. If you did it right and the file is good, you'll get a success message and your contrib should start working. (assuming all other things about the contrib are good).

Good Luck!

Link to comment
Share on other sites

You need to use the little dropdown menu to select your database first. Then you can click on the SQL tab, and paste the SQL script in and press go! Shouldn't matter if you do the SQL first or last, as far as the db goes, it only matters when you try to run a file in your store!

 

Jeremy

Link to comment
Share on other sites

AAARRGGH!!!!

 

I did that and now my shop is broken :o

 

After I ran the SQL thingummy it said

 

#1091 - Can't DROP 'customers_group_id'. Check that column/key exists

 

and my shop is broken (http://www.phrazzle.co.uk/PCLshop/)

 

I have backups but if anyone is on right now who can help I'd rather try and fix it.

 

dcmo (in a panic)

Link to comment
Share on other sites

the contribution uses customers_group_id wheras you will probably not have this in your database table

 

did you have another contribution installed prior to this one and if so was the other contribution for group discounts

No longer giving free advice. Please place deposit in meter slot provided.  Individual: [=] SME: [==] Corporation: [===]
If deposit does not fit one of the slots provided then you are asking too much! :P

Is your Osc dated try Phoenix  raising oscommerce from the ashes.

Link to comment
Share on other sites

After I ran the SQL thingummy it said

 

#1091 - Can't DROP 'customers_group_id'. Check that column/key exists

Why on earth do you run the "uninstall" sql when you want to install?

Run the sppc_v41_install.sql. Your shop is broken because you first installed one or more files from SPPC and haven't done the sql thing. It doesn't work like that.

 

Read the manual:

Before beginning the manual install you need to;

 

Upload two included image files to your site. They are located in; catalog\admin\includes\languages\english\images\buttons\

 

Upload two new .php files;

catalog\admin\includes\languages\english\customers_groups.php  &

catalog\admin\customers_groups.php

 

Run the spcc_v41_install.sql database installation file on your MySQL database.

Link to comment
Share on other sites

copy and paste this into the sql window in phpmyadmin

DROP TABLE IF EXISTS products_groups;
CREATE TABLE products_groups (
 customers_group_id smallint UNSIGNED NOT NULL default '0',
 customers_group_price decimal(15,4) NOT NULL default '0.0000',
 products_id int(11) NOT NULL default '0',
 PRIMARY KEY  (customers_group_id, products_id)
);

No longer giving free advice. Please place deposit in meter slot provided.  Individual: [=] SME: [==] Corporation: [===]
If deposit does not fit one of the slots provided then you are asking too much! :P

Is your Osc dated try Phoenix  raising oscommerce from the ashes.

Link to comment
Share on other sites

This is the file I ran

 

This bit ran ok

 

ALTER TABLE customers

ADD customers_group_ra enum('0','1') NOT NULL,

ADD customers_payment_allowed varchar(255) NOT NULL default '',

ADD customers_shipment_allowed varchar(255) NOT NULL default '';

 

This bit was ok the first time

 

CREATE TABLE customers_groups (

customers_group_id smallint UNSIGNED NOT NULL,

customers_group_name varchar(32) NOT NULL default '',

customers_group_show_tax enum('1','0') NOT NULL,

customers_group_tax_exempt enum('0','1') NOT NULL,

group_payment_allowed varchar(255) NOT NULL default '',

group_shipment_allowed varchar(255) NOT NULL default '',

PRIMARY KEY (customers_group_id)

);

 

But this line gives the error "#1054 - Unknown column 'c.customers_group_id' in 'field list' "

 

INSERT INTO customers_groups SELECT DISTINCT c.customers_group_id, c.customers_group_name, '1', '0' , '', '' FROM customers c GROUP BY customers_group_id;

 

These are ok

 

ALTER TABLE address_book

ADD entry_company_tax_id VARCHAR(32) DEFAULT NULL AFTER entry_company;

 

ALTER TABLE specials

ADD customers_group_id smallint UNSIGNED NOT NULL default '0';

 

Link to comment
Share on other sites

this is the entire sql

ALTER TABLE customers
ADD customers_group_id smallint UNSIGNED NOT NULL default '0',
ADD customers_group_ra enum('0','1') NOT NULL,
ADD customers_payment_allowed varchar(255) NOT NULL default '',
ADD customers_shipment_allowed varchar(255) NOT NULL default '';

DROP TABLE IF EXISTS products_groups;
CREATE TABLE products_groups (
 customers_group_id smallint UNSIGNED NOT NULL default '0',
 customers_group_price decimal(15,4) NOT NULL default '0.0000',
 products_id int(11) NOT NULL default '0',
 PRIMARY KEY  (customers_group_id, products_id)
);

ALTER TABLE specials
ADD customers_group_id smallint UNSIGNED NOT NULL default '0';

DROP TABLE IF EXISTS customers_groups;
CREATE TABLE customers_groups (
customers_group_id smallint UNSIGNED NOT NULL,
customers_group_name varchar(32) NOT NULL default '',
customers_group_show_tax enum('1','0') NOT NULL,
customers_group_tax_exempt enum('0','1') NOT NULL,
group_payment_allowed varchar(255) NOT NULL default '',
group_shipment_allowed varchar(255) NOT NULL default '',
PRIMARY KEY (customers_group_id)
);

INSERT INTO customers_groups VALUES('0','Retail','1','0','','');

ALTER TABLE address_book 
ADD entry_company_tax_id VARCHAR(32) DEFAULT NULL AFTER entry_company;

 

try running this part of the sql again

DROP TABLE IF EXISTS customers_groups;
CREATE TABLE customers_groups (
customers_group_id smallint UNSIGNED NOT NULL,
customers_group_name varchar(32) NOT NULL default '',
customers_group_show_tax enum('1','0') NOT NULL,
customers_group_tax_exempt enum('0','1') NOT NULL,
group_payment_allowed varchar(255) NOT NULL default '',
group_shipment_allowed varchar(255) NOT NULL default '',
PRIMARY KEY (customers_group_id)
);

No longer giving free advice. Please place deposit in meter slot provided.  Individual: [=] SME: [==] Corporation: [===]
If deposit does not fit one of the slots provided then you are asking too much! :P

Is your Osc dated try Phoenix  raising oscommerce from the ashes.

Link to comment
Share on other sites

OK we were all typing at once there.

 

I think what happened was I ran the INSTALL which gave an error, then when I went to open it to look at the code again I accidentally opened the wrong ie uninstall file ( :blush: ) . (note to self calm down and think before acting next time).

 

241 I ran your code and it said it was successful but no change in the shop. Also I can't see my customer page in admin

 

Parse error: parse error, unexpected $ in /home/phrazzle/public_html/PCLshop/admin/customers.php on line 1244

 

so Im off to check the customer.php again very carefully.

 

 

thanks all

Link to comment
Share on other sites

Thanks everyone, I restored my database then reran the SQL line by line and the shop is back! :rolleyes: . I've tested the login and it seems to be upgraded ok (ie it asks for the tax number etc) .... but I still can't see my customer page in admin. I'm going to go back to the original and reapply the SPPC changes and hope that fixes the problem.

 

I'm glad you are out there and respond so fast !

 

dcmo

Link to comment
Share on other sites

but I still can't see my customer page in admin. I'm going to go back to the original and reapply the SPPC changes and hope that fixes the problem.
You might be better of starting with the SPPC one in the package and then add the changes for other contributions you are using on that page. There are quite some changes on that page because of SPPC...
Link to comment
Share on other sites

Archived

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

×
×
  • Create New...