Page 1 of 1

Dynamic Newsletter

Posted: Thu Feb 01, 2007 1:29 pm
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]

Posted: Thu Feb 01, 2007 2:05 pm
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:

Dynamic Newsletter

Posted: Thu Feb 01, 2007 3:17 pm
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

Posted: Fri Feb 02, 2007 10:07 am
by pickle
What's your DB schema? It looks possible for you to combine both those queries.

Schema

Posted: Fri Feb 02, 2007 2:24 pm
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

Posted: Fri Feb 02, 2007 3:36 pm
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).

Answers

Posted: Fri Feb 02, 2007 4:01 pm
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.")

Posted: Fri Feb 02, 2007 7:03 pm
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).

Thanks

Posted: Sat Feb 03, 2007 5:57 am
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

Posted: Sat Feb 03, 2007 9:11 am
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.

Thanks again

Posted: Sat Feb 03, 2007 9:33 am
by patch2112
Excellent,

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

Philip