Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

What is wrong With this SQL command?


Mort-lemur

Recommended Posts

Hi,

 

Im trying to install Osc Affiliate onto another of my 2.2 Rc2a stores - i have it running well already on one store.

 

However, when I try to run the SQL command through PHPmyadmin I get an error caused by the following line:

 

INSERT INTO configuration_group VALUES (6508, 'Affiliate Program', 'Options for the Affiliate Program', 50, 1);
INSERT INTO configuration VALUES ('','E-Mail Address', 'AFFILIATE_EMAIL_ADDRESS', '<affiliate@[member='localhost'].com>', 'The E Mail Address for the Affiliate Program', 6508, 1, NULL, now(), NULL, NULL);

 

Basically telling me the entry cannot be Null.

 

The original SQL used location 900 for the entry but this is in use in my database so i have changed it to the next available number ie 6508 throughout the sql command.

 

Thanks :)

Now running on a fully modded, Mobile Friendly 2.3.4 Store with the Excellent MTS installed - See my profile for the mods installed ..... So much thanks for all the help given along the way by forum members.

Link to comment
Share on other sites

Hi,

 

Im trying to install Osc Affiliate onto another of my 2.2 Rc2a stores - i have it running well already on one store.

 

However, when I try to run the SQL command through PHPmyadmin I get an error caused by the following line:

 

INSERT INTO configuration_group VALUES (6508, 'Affiliate Program', 'Options for the Affiliate Program', 50, 1);
INSERT INTO configuration VALUES ('','E-Mail Address', 'AFFILIATE_EMAIL_ADDRESS', '<affiliate@[member='localhost'].com>', 'The E Mail Address for the Affiliate Program', 6508, 1, NULL, now(), NULL, NULL);

 

Basically telling me the entry cannot be Null.

 

The original SQL used location 900 for the entry but this is in use in my database so i have changed it to the next available number ie 6508 throughout the sql command.

 

Thanks :)

as a dirty fix, you can try replacing the NULL values with an empty string

alternatively you have to look at the table structure to see which field is not null allowed and provide a value

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

Hi,

 

Im trying to install Osc Affiliate onto another of my 2.2 Rc2a stores - i have it running well already on one store.

 

However, when I try to run the SQL command through PHPmyadmin I get an error caused by the following line:

 

Try changing this
INSERT INTO configuration VALUES ('','E-Mail Ad

to

INSERT INTO configuration VALUES (NULL,'E-Mail Ad

Support Links:

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

All of My Addons

Get the latest versions of my addons

Recommended SEO Addons

Link to comment
Share on other sites

INSERT INTO configuration_group VALUES (6508, 'Affiliate Program', 'Options for the Affiliate Program', 50, 1);
INSERT INTO configuration VALUES ('','E-Mail Address', 'AFFILIATE_EMAIL_ADDRESS', '<affiliate@[member='localhost'].com>', 'The E Mail Address for the Affiliate Program', 6508, 1, NULL, now(), NULL, NULL);

This looks like a problem:

'<affiliate@[member='localhost'].com>'

You might try escaping the inner quotes:

'<affiliate@[member=\'localhost\'].com>'

 

It's also always better to explicitly list the field names in () so that you are sure of matching up fields and values. It's very common to modify the database schema and have a difficult to diagnose mismatch between the number of fields and the number of values.

 

Basically telling me the entry cannot be Null.

 

The original SQL used location 900 for the entry but this is in use in my database so i have changed it to the next available number ie 6508 throughout the sql command.

Wrong approach. If this "entry" is an autoincrement field, it should be omitted from the VALUES list (you need to give the list of field names). If you have to give an explicit value for an autoincrement field, NULL should work. Never try to give a specific value -- you don't know how that's going to interact with the autoincrement counter in MySQL.

 

Now, if this is not an autoincrement field, you would have to give a value if NULL is not allowed. From its position at the beginning of the list, I'm assuming this is an autoincrement *_ID field.

 

Once MySQL has picked the next ID value for the first statement (configuration_group), you will have to query the ID's value and use that in the second statement. There's no way to do both together.

Link to comment
Share on other sites

Thanks for the replies - I may shelve this one for a while - mainly because I dont understand any of the answers :- - thing I cant understand is why I didnt have this problem when I installed on my other site 12 months ago - as it is an identical site as far as PHP files and database structure is concerned.

Now running on a fully modded, Mobile Friendly 2.3.4 Store with the Excellent MTS installed - See my profile for the mods installed ..... So much thanks for all the help given along the way by forum members.

Link to comment
Share on other sites

1. Never give a value other than NULL to an "autoincrement" field (exception: reloading a database from a backup). Always let MySQL pick the next ID value to be used. '' is not a NULL, and is invalid (wrong type) anyway. MySQL keeps an internal counter of the next ID to use.

 

Let MySQL pick the ID values for both tables. SELECT and read the value picked for configuration_group::configuration_group_id and use that in the INSERT for configuration::configuration_group_id.

 

2. To avoid having a mismatch of number of fields in a table and number of VALUES, it's best to explicitly list the fields that you are entering values for. Plus, this lets you omit some fields, such as, oh, an autoincremented ID field.

 

3. ' within '-delimited string, or " within "-delimited string, need to be escaped with \.

 

4. Why did it work last time? Who knows. You must have done something differently.

Link to comment
Share on other sites

So I should remove the 6508 field numbers from the sql commands to allow mysql to pick its own values?

Now running on a fully modded, Mobile Friendly 2.3.4 Store with the Excellent MTS installed - See my profile for the mods installed ..... So much thanks for all the help given along the way by forum members.

Link to comment
Share on other sites

Remove it from the hard-coded configuration_group table INSERT. Let MySQL pick the new configuration_group_id. Remember the ID it picks, and use it in the configuration table INSERT, AND use NULL for the first field (not ''). That is, let MySQL pick the configuration_id value in the second INSERT.

Link to comment
Share on other sites

@@Mort-lemur I took look at the actual sql statement in the version of that addon I have here and it is different from yours. You have

 

INSERT INTO configuration VALUES ('','E-Mail Address', 'AFFILIATE_EMAIL_ADDRESS', '<affiliate@@localhost.com>', 'The E Mail Address for the Affiliate Program', 6508, 1,NULL, now(), NULL, NULL);

but it should be

 

INSERT INTO configuration VALUES (NULL,'E-Mail Address', 'AFFILIATE_EMAIL_ADDRESS', '<affiliate@@localhost.com>', 'The E Mail Address for the Affiliate Program', 900, 1, NULL, now(), NULL, NULL);

Maybe the version you are using is a non-working one. You know how some addon updates are.

Support Links:

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

All of My Addons

Get the latest versions of my addons

Recommended SEO Addons

Link to comment
Share on other sites

The '900' value is still hard coded. It needs to match whatever ID was produced when the configuration_group record was INSERTed. And I did tell her already that '' needs to be replaced by NULL.

Link to comment
Share on other sites

@@Jack_mcs Im using the latest version by Fimble (2.8.1) but going back through all the other updates - they all seem to have the " rather than the NULL - do you know what version the sql you are using came from?

 

Thanks

Now running on a fully modded, Mobile Friendly 2.3.4 Store with the Excellent MTS installed - See my profile for the mods installed ..... So much thanks for all the help given along the way by forum members.

Link to comment
Share on other sites

It's possible that you might get away with using an empty string '' instead of a NULL on some systems, but I doubt that it's considered proper SQL everywhere. I have seen NULL and 0 both offered as legitimate field values to force an autoincrementing. Or, explicitly list your field names and just omit the name and value for the autoincremented ID.

Link to comment
Share on other sites

@@Mort-lemur It's 2.8.1. I changed the "" to NULL because "" is no longer an acceptable choice. I don't recall which version of mysql if was changed in, probably 5.0, but the "" used to be OK. Using NULL is fine. It is the id of that item, which has to be a unique number. Using NULL tells mysql to assign it. If you want to use some other number, look at the id's in the configuration table and use one that is not already in use. It will work either way.

Support Links:

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

All of My Addons

Get the latest versions of my addons

Recommended SEO Addons

Link to comment
Share on other sites

Archived

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

×
×
  • Create New...