Dynamic Newsletter

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
patch2112
Forum Commoner
Posts: 86
Joined: Sun Oct 31, 2004 9:44 am
Location: London

Dynamic Newsletter

Post by patch2112 »

pickle | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Hello all,

I'm building a job listings website for a client.  I'm building a feature to send weekly emails to registered users of new jobs that meet their criteria.  It's a small niche market, but I would expect the list to be 1,000-5,000 within 6 months.

The problem is that I'm worried about crashing the script/server if I do a query to gather the listings for each name on the list, something like...

[syntax="php"]$sql = mysql_query ("select criteria, email from users");
while ($row = mysql_fetch_array($sql))
{
     $sql2 = mysql_query ("select listing from listings where details=critera");
     while ($row2 = mysql_fetch_array($sql2)
     {
          $email_msg .= "Listing Name: " . $row2['listing_name'] . "\n";
     }

     mail($row['email'],"Weekly Listings",$email_msg);
}
Does anyone know of a better way?

Should I just process them in smaller batches? If so, what seems to be a reasonable number of users to loop through at a time?

Thanks for any help!
Philip


pickle | Please use[/syntax]

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
User avatar
seodevhead
Forum Regular
Posts: 705
Joined: Sat Oct 08, 2005 8:18 pm
Location: Windermere, FL

Post by seodevhead »

That's a lot of data to be displayed on one single page. But not a lot for the database the gather at one time. I wouldn't sweat it one bit personally, but I'm not too big on how optimized sql queries are. As far as displaying the data to the browser, you definitely want to paginate that, the browser can't keep up with MySQL.

But I do know you have a bit of an error in your code there. You are using mysql_fetch_array() for both data sets but you are referring to the data as if it is of an associative array. In other words, you need to refer to $row and $row as $row[0], $row2[0], not $row2['listing_data']. It is a numerical array, not associative. But I know this isn't probably the code that is within your app, just in case, that's a prob. :lol:
patch2112
Forum Commoner
Posts: 86
Joined: Sun Oct 31, 2004 9:44 am
Location: London

Dynamic Newsletter

Post by patch2112 »

Thanks for your post seodevhead,

I'm really just wondering if running 1-5k queries in a single script is too much. The data will be emailed one user at a time, so nothing is displayed. I just short-handed the code too to get the idea across.

Any one know if I should break these up into smaller batches, and how many queries per batch is ok?

Thanks,
Philip
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

What's your DB schema? It looks possible for you to combine both those queries.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
patch2112
Forum Commoner
Posts: 86
Joined: Sun Oct 31, 2004 9:44 am
Location: London

Schema

Post by patch2112 »

Hello Pickle,

Maybe it can be one big query, but I wouldn't know how to write it. The tables in question are like...

Code: Select all

Table: listings
+---------------+------------------+------+-----+---------+----------------+
| Field         | Type             | Null | Key | Default | Extra          |
+---------------+------------------+------+-----+---------+----------------+
| listing_id    | int(11)          |      | PRI | NULL    | auto_increment |
| title         | varchar(100)     | YES  |     | NULL    |                |
| salaryMin     | int(2)           | YES  |     | NULL    |                |
| salaryMax     | int(2)           | YES  |     | NULL    |                |
| city_id       | int(11)          | YES  |     | NULL    |                |
| job_type_id   | int(11)          | YES  |     | NULL    |                |
| listingNumber | varchar(25)      | YES  |     | NULL    |                |
| description   | text             | YES  |     | NULL    |                |
| date_created  | datetime         | YES  |     | NULL    |                |
| featured      | enum('yes','no') | YES  |     | no      |                |
+---------------+------------------+------+-----+---------+----------------+

Table: users
+--------------------------+------------------+------+-----+---------+----------------+
| Field                    | Type             | Null | Key | Default | Extra          |
+--------------------------+------------------+------+-----+---------+----------------+
| user_id                  | int(11)          |      | PRI | NULL    | auto_increment |
| first_name               | varchar(50)      | YES  |     | NULL    |                |
| last_name                | varchar(50)      | YES  |     | NULL    |                |
| address1                 | varchar(150)     | YES  |     | NULL    |                |
| city                     | varchar(50)      | YES  |     | NULL    |                |
| state                    | varchar(25)      | YES  |     | NULL    |                |
| zipe                     | varchar(10)      | YES  |     | NULL    |                |
| home_phone               | varchar(10)      | YES  |     | NULL    |                |
| cell_phone               | varchar(10)      | YES  |     | NULL    |                |
| email                    | varchar(100)     | YES  |     | NULL    |                |
| notification             | enum('yes','no') | YES  |     | NULL    |                |
| notification_city_id     | int(11)          | YES  |     | NULL    |                |
| notification_job_type_id | int(11)          | YES  |     | NULL    |                |
| notification_min_salary  | int(11)          | YES  |     | NULL    |                |
| notification_keywords    | varchar(255)     | YES  |     | NULL    |                |
+--------------------------+------------------+------+-----+---------+----------------+
The idea is to find the users who have elected to get notified (users.notification), then taking their criteria (users.notification_city_id, users.notification_job_type_id, users.notification_min_salary, users.notification_keywords) and the gather the listings from the "listings" table and inject into the email that gets sent to them.

Clear as mud? lol. Sorry about the code wrappers too.

Thanks a lot for your help!
Philip
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

I think it would be possible with a join of some sort - though I'm not the best at those. Would you mind giving me the results of:

Code: Select all

SHOW CREATE TABLE listings; SHOW CREATE TABLE users;
so I can duplicate your db here & see if I can finagle something.

In case I can't figure it out & you do have to do a bunch of queries, what execution options do you have? Does this necessarily have to be run from a web page (ie you're on a 3rd party shared host)? Can you run this as a command line script or a cron job? If so, you could set the 'niceness' of the script (if the former), or break up the emailing & do 300 people every 10 minutes or so (if the latter).
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
patch2112
Forum Commoner
Posts: 86
Joined: Sun Oct 31, 2004 9:44 am
Location: London

Answers

Post by patch2112 »

Thanks man, I appreciate the help.

I have a VPS, so I can do the Cron Job if necessary. Below is the create syntax minus some lines that aren't necessary just to get the thereory and a couple insert commands you can use to populate some sample data.

Code: Select all

CREATE TABLE `users` (
  `user_id` int(11) NOT NULL auto_increment,
  `first_name` varchar(50) default NULL,
  `email` varchar(100) default NULL,
  `notification` enum('yes','no') default NULL,
  `notification_city_id` int(11) default NULL,
  `notification_job_type_id` int(11) default NULL,
  `notification_min_salary` int(11) default NULL,
  `notification_keywords` varchar(255) default NULL,
  PRIMARY KEY  (`user_id`) )

CREATE TABLE `listings` (
  `listing_id` int(11) NOT NULL auto_increment,
  `salaryMin` int(2) default NULL,
  `salaryMax` int(2) default NULL,
  `city_id` int(11) default NULL,
  `job_type_id` int(11) default NULL,
  `description` text,
  PRIMARY KEY  (`listing_id`) ) 

insert into users values ("auto","jim","jim@email.com","yes","1","1","30","manager")
insert into users values ("auto","bill","bill@email.com","yes","1","2","40","telemarketer")
insert into users values ("auto","jack","jack@email.com","yes","2","2","50","salesman")
insert into users values ("auto","bob","bob@email.com","no","1","1","50","manager")

insert into listings values ("auto","30","35","1","1","Looking to hire a manager.")
insert into listings values ("auto","40","45","1","2","Looking to hire a telemarketer.")
insert into listings values ("auto","50","55","1","3","Looking to hire a salesman.")
insert into listings values ("auto","30","35","2","1","Looking to hire a manager in area 2.")
insert into listings values ("auto","40","45","2","2","Looking to hire a telemarketer in area 2.")
insert into listings values ("auto","50","55","2","3","Looking to hire a salesman in area 2.")
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

So you want to grab all listings that are in the users city, are of the type the user wants, in the same keyword family and of the right salary and send them to those users that have elected to receive emails?

Sounds logical. For the emailing part of it, I would suggest Swiftmailer. As for the query, I would say take the question I just asked you and turn it in to SQL. If you need help with that part, post back. The key to your query is going to be using the correct logic in JOINING on the four fields that match the listing to the user (city, type, salary and keywords).
patch2112
Forum Commoner
Posts: 86
Joined: Sun Oct 31, 2004 9:44 am
Location: London

Thanks

Post by patch2112 »

Thanks Everah,

Swiftmailer looks like just the ticket, I'll certainly look into that.

As for the content, I can (eventually) figure out the correct sql, but I'm not really sure how to be process it once I get the results, as each person would get more than one match...

Code: Select all

$row["Jim","Jim@email.com","Boston","Job #123"];
$row["Jim","Jim@email.com","Boston","Job #124"];
$row["Jim","Jim@email.com","Boston","Job #125"];
$row["bill","bill@email.com","Boston","Job #124"];
$row["bill","bill@email.com","Boston","Job #126"];
I would need to only send one email to Jim with his three results, but I'm not sure how I would do this. Is reading the query results into multi-dimensional arrays the right direction? Something like...

Code: Select all

$email_listings = array(
  'Jim@email.com'=>array(
    'Job #123',
    'Job #124',
    'Job #125'
  ),
  'Bill@email.com'=>array(
    'Job #124',
    'Job #126',
  ),
);
Thanks again,
Philip
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Yes, you got the logic. What I might do is set up the array of arrays when the data comes out. Something along the lines of:

Code: Select all

<?php
$mailing_list = array(
  [0] = array(
    ['user_email'] => 'joe@shmoe.com', // This would be the user email field from the database
    ['user_listings'] = array(
      // This would be the listing data information
    ),
  )
);
?>
Then loop this data and implement the swift mailing with loop.

EDIT | You know, if I remember correctly, I think you can actually pass Swift an array of emails to send to. You might want to look into that. It might be more useful and less overhead.
patch2112
Forum Commoner
Posts: 86
Joined: Sun Oct 31, 2004 9:44 am
Location: London

Thanks again

Post by patch2112 »

Excellent,

Thanks to all. I'll let you know how it goes...

Philip
Post Reply