Forum Moderators: coopster
My problem is that I have 2 tables containing email addresses for the mailing list. One is for existing customers who have signed up for the newsletter, the other is for surfers to add themselves to the newsletter list.
As the importing of the addresses is via a single select statement I am having to manually import the data from one of the tables.
Im wondering whether it is possible to import the email data from 2 tables using a single select statement resulting in one list of addresses. From my limited knowledge and experimentation, I suspect this is not possible and would welcome any suggestions as to how I could circumvent this problem.
My thanks in advance,
Ian
I have a browser based php script which sends out our newsletter. It imports the email address data to a text box list...
So I assume that you are allowed to edit the list from this page.
My problem is that I have 2 tables containing email addresses for the mailing list. One is for existing customers who have signed up for the newsletter, the other is for surfers to add themselves to the newsletter list.
Why not use one table? Is there some particular reason for keeping them seperated?
Im wondering whether it is possible to import the email data from 2 tables using a single select statement resulting in one list of addresses.
Providing the fields match it should be a piece of cake. In theory you could use:
SELECT table1.*, table2.*
FROM table1,table2
If the field names don't match you can always put them into the array with a different name in the select statement.
SELECT table1.id, table1.name, table2.id, CONCAT(table2.fname,' ',table2.lname) as table2.name
which simply concatenates (combines) the fields and a string (' ') to create a field called name.
So I assume that you are allowed to edit the list from this page.
Correct
Why not use one table? Is there some particular reason for keeping them seperated?
The customer table has 15 fields, the maillist table has 2 fields. As the cust table is used for other reports, these would look strange if the only entries for some customers were name and mail addy
Providing the fields match it should be a piece of cake. In theory you could use:SELECT table1.*, table2.*
FROM table1,table2
The fields have the same name in both tables 'customers' and 'maillist': customers_email_address and customers_newsletter (1 or 0)
I tried:
select customers.customers_email_address, maillist.customers_email_address from customers, maillist where customers.customers_newsletter and maillist.customers_newsletter = 1;but this brought up 2 columns of results
(SELECTThe SUBSTRING_INDEX is a MySQL function. Here it is being used to show an optional way to sort the list nicely by anything in front of the '@' sign in the email address.
customers_email_address
FROM customers
WHERE customers_newsletter = 1)
UNION
(SELECT
customers_email_address
FROM maillist
WHERE customers_newsletter = 1)
ORDER BY SUBSTRING_INDEX(customers_email_address, '@', 1)
;
Stuck with murky old 2.5.5 I'm afraid, so it doesn't recognise the UNION syntax
Going back to an earlier thought that lorax had regarding a single table -- why not? And just include a column of "customer type" or something like that? Then you could easily query the table based on that column, "paying customer" versus "non-paying", or any other "customer type" that you want to establish. It would also be a lot easier to convert a "non-paying" to a "paying" quite easily, too.
That sorted it. Added a new field and changed the refs in the php script on signup. Now all data on 1 table and select pulls all the addy's in one hit.
Thanks to all for their input.
Ian
