How To Add Email Addresses To The Database?

[ChrisBroadhurst]

Hi All

 

OK when we upgraded our website we began to use OSCommerce, before this we used a different cart, I have a list of around 400 email addresses that I need to somehow add to our OSCommerce database so that when I goto:

 

tools/sendmail

 

all the addresses show up so I can send newsletters to everyone in one go nice and easy.

 

Can this be done? Im presumming they will need adding to the database can soehow give me a bit of instruction to do this?

 

As usual any help appreciated

 

thanks in advance

 

Chris

[burtonsnow8]

Do you have PHPMyAdmin?

[ChrisBroadhurst]

Hi thanks for the reply, Yes I do have PHP Myadmin

[burtonsnow8]

Hi thanks for the reply, Yes I do have PHP Myadmin

 

Well, unfortunately the structure is a little different than I thought. I would suggest installing the following contribution:

 

http://www.oscommerce.com/community/contributions,535

 

 

After installing the contribution you are going to have to upload those customer e-mail address into your database via phpmyadmin. If you DO NOT have knowledge working with mysql database come back here for some help.

 

 

 

If you wanted to forgo installing that contribution you will have to add your current customers info into the customers table in phpmyadmin. You will then have to link their info into the address_book and customers_info table, which means creating another 800 entries (400 for each). At the end of the day it will probably be less work to install the contribution.

[burtonsnow8]

I've done a quick tutorial for phpmyadmin...let me know if it helps at all:

 

 

Download your current database in its entirety. It can take minutes to hours depending on how big your site is, so give it time.

 

1) Load phpmyadmin

2) Click on your database name

3) click on the export tab at the top

4) Make sure all your tables are selected and check the "save to file" option

5) click go

 

Once your database is downloaded, search for the table that you wish to edit. In the example below i've searched for the "customers" table and am going to add a user. Look for the following which signifies the start of a table:

 

--
-- Table structure for table `customers`
--

 

Next we are going to look what fields the customers table holds. They are in the following format:

 

INSERT INTO `customers` (`customers_id`, `customers_gender`, `customers_firstname`, `customers_lastname`, `customers_dob`, `customers_email_address`, `customers_default_address_id`, `customers_telephone`, `customers_fax`, `customers_password`, `customers_newsletter`) VALUES

 

 

Now we need to find the end of the current table. Also note that the fields will match the ones above. when a table ends it is always finished using a ";" (semicolon):

 

(280, 'm', 'Chris1', 'Sperling2', '0000-00-00 00:00:00', '[email protected]', 280, '818 XXX 4625', '', 'removed for security', '1');

-- --------------------------------------------------------

 

If we wanted to insert any users we would use the following format. note that at the end is a customers_newsletter, 0 means not subscribed and 1 means subscribed, so this is the most important field for these customers. Also note that the customers_id and customers_default_address_id are unique values.

 

INSERT INTO `customers` (`customers_id`, `customers_gender`, `customers_firstname`, `customers_lastname`, `customers_dob`, `customers_email_address`, `customers_default_address_id`, `customers_telephone`, `customers_fax`, `customers_password`, `customers_newsletter`) VALUES
(281, 'm', 'New', 'Customer', '0000-00-00 00:00:00', '[email protected]', 281, '818 XXX XXXX', 'NULL', 'NULL', '1'),
(282, 'f', 'New1' , 'Customer1', '0000-00-00 00:00:00', '[email protected]', 282, '818 XXX XXXX', 'NULL', 'NULL', '1');

 

If you have more entries after use a comma, on your last entry use ";" (semicolon). To add entries to our database we do the following:

 

1) open phpmyadmin

2) click on the sql tab at the top

3) paste into the text box the code we wish to add

4) click go

5) wait for phpmyadmin results

 

 

Here is how I would add a user to all the necessary database:

 

 

INSERT INTO `customers` (`customers_id`, `customers_gender`, `customers_firstname`, `customers_lastname`, `customers_dob`, `customers_email_address`, `customers_default_address_id`, `customers_telephone`, `customers_fax`, `customers_password`, `customers_newsletter`) VALUES
(281, 'm', 'New', 'Customer', '0000-00-00 00:00:00', '[email protected]', 281, '818 XXX XXXX', 'NULL', '', '1'),
(282, 'f', 'New1' , 'Customer1', '0000-00-00 00:00:00', '[email protected]', 282, '818 XXX XXXX', 'NULL', '', '1');

INSERT INTO `address_book` (`address_book_id`, `customers_id`, `entry_gender`, `entry_company`, `entry_firstname`, `entry_lastname`, `entry_street_address`, `entry_suburb`, `entry_postcode`, `entry_city`, `entry_state`, `entry_country_id`, `entry_zone_id`) VALUES
(281, 281, 'm', '', 'New', 'Customer', '17 Ridge Cir', NULL, '91341', 'That place', '', 223, 12),
(282, 282, 'm', '', 'New1', 'Customer1', '18 Ridge Cir', NULL, '91341', 'That Place2', '', 223, 12);

INSERT INTO `customers_info` (`customers_info_id`, `customers_info_date_of_last_logon`, `customers_info_number_of_logons`, `customers_info_date_account_created`, `customers_info_date_account_last_modified`, `global_product_notifications`) VALUES
(281, '0000-00-00 00:00:00', 0, '0000-00-00 00:00:00', NULL, 0),
(282, '0000-00-00 00:00:00', 0, '0000-00-00 00:00:00', NULL, 0);