Create search agent (saved search)

rubino

I'm developing for a cars seller who wants to allow customers to create search agents: they will receive an e-mail from time to time containing models corresponding to their search agent.

I've thought of 2 ways round:

Every time a new car is added, it works as a search criteria on agents table. (good: less stress on db, bad: 1 mail for each offer instead than regular mails (monthly for example) with many cars available
Each Agent starts at night (loop of select on car models)
(good: that's how it should work! 😃 bad: considering 200 agents running each time, on about 600 cars, can I break MYSQL bones!? :xbones: )

Waiting for suggestions about best soution for customer, and form resources of MySQL Server. Also need some suggestion about scheduling php page!

Thank u so much!
Emiliano

jheitz

That is not a lot of stress for a database. I don't understand on the other hand why you need 200 agents running in parallel: why not run a nightly batch process that goes through all the agents one by one (using a cursor) and creates the mails and sends them out?

rubino

that's the idea jheitz but i'm not sure about my project cause it's 1st time I manage many datas and user (I just used to connect in usual ways to db and extract records, without minding about anything).

My doubt comes because I don't know when the stress of db ends...I mean (ok i'm confused!): I make the SELECT, make mysql_query, then mysql_fetch_assoc, and then my idea is to loop on results with search agents before writing mysql_free_result.

Questions:
1.in this model the db connection would stay open during the whole loop until mysql_free_result?
2. How can i schedule this process?
3. Suppose it's started manually: do I risk a page timeout?
4. And finally!...can u suggest some good way to send so many e-mails in short time, can I confide in usual php script?

Thank u so much

jheitz

Hi rubino,

My thoughts...

1) I guess so, but an open connection is consuming resources on the server, but stress on the database is only happening when you run a query. So if you leave the connection open for the length of the script there is no real problem (to my knowledge)

2, 3 & 4) You don't want to do any of that in PHP (I wouldn't anyways). From the web you control the users creating their agents and the new cars being entered, that seems ok. But then you need a JOB (MySQL does not have them but they are easy to "create", I'll explain later on) that runs scheduled overnight on the database, creates the emails and sends them.

How to do this depends on a variety of factors, most importantly if you are using UNIX/LINUX or WINDOWS. Both systems have schedulers that allow execution of tasks at certain times. You create a scheduled task (cron job in UNIX) and make it run a batch file (shell script in UNIX) that calls your Stored Procedure and sends the emails...

Hope all that makes sense.

rubino

it all makes a lot of sense jheitz! you are cleaning my doubts.

Point n.1: u said conenction itself doesn't stress db...so I should make only 1 SELECT of all records in cars db, and then simply loop through results for each Agent, & that's all?

Hosting is on Linux server but it's not mine, I will ask for cron job...I only heard about it.
So the cron job starts a shell script (I will learn how to create it!) and the script calls my .php file, right?

it all seems easier than I could expect...(at least I hope so!)

jheitz

No, don't use PHP for any of that. Your web page will insert records in a CAR and AGENT table, right?

Then create a CRON job in UNIX that runs at 00:00 every day for example and that calls a file called SendEmailsOut.sh

This SHELL SCRIPT file should open a connection to MySQL and call a STORED PROCEDURE that will loop through all the AGENTS and compose the relevant emails (this you can do using a DB cursor). About sending the emails as such you could use LINUX's mail if there is no way to do this using MySQL (you need to find out).

rubino

...mh...it sounds beyond my knowledges!!...especially how to create the shell script! (my problem of course!)

should learn it or find some way out using php 🙂

rubino

...well I'm collecting info on websites and it's not so beyond!!...

They are normal SQL commands actually to interact with MySQL...

rubino

Question: can I use PHP as language for shell scripting?

Somewhere I read I could write a script in PHP starting like that:

#!/usr/bin/php

saving it as myscript.php, and then calling it as a cron job to connect to MySQL, make queries and send emails to users.

Anything wrong on it?

If this works, can I test my php code through webserver and than use it as shell command, called by cron job?

Thanks again, im learning a lot 🙂

jheitz

No idea to be honest... I am new to PHP and have only used for web pages. Maybe you could... do a bit of research... Because I know a bit of unix shell scripting I would go for the "call a MySQL Stored Procedure" and let it do all the work... but maybe there are alternative approaches... can't help you there sorry

rubino

Thanks a lot anyway jheitz u showed me the right way to make it!

I will take a look around...of course if anybody here has ever tried something similar, I'd be grateful 🙂

Stepdad

rubino wrote:
I'm developing for a cars seller who wants to allow customers to create search agents: they will receive an e-mail from time to time containing models corresponding to their search agent.

I've thought of 2 ways round:

Every time a new car is added, it works as a search criteria on agents table. (good: less stress on db, bad: 1 mail for each offer instead than regular mails (monthly for example) with many cars available

Each Agent starts at night (loop of select on car models)
(good: that's how it should work! 😃 bad: considering 200 agents running each time, on about 600 cars, can I break MYSQL bones!? :xbones: )

Waiting for suggestions about best soution for customer, and form resources of MySQL Server. Also need some suggestion about scheduling php page!

Thank u so much!
Emiliano

Just a thought here, but why not break this into two sections. First each time a new car is added it checks your "agents" file for matching criteria. When it finds one it takes all of the info needed out of the database and outputs it to a text file or html file, one file for each customer that created an agent. When new cars are added, if they match the criteria they will be automatically added to the external files for that customer.

Next a secondary function that runs weekly or perhaps monthly (depending on your needs/desires). This function checks for the existence of these text/html files, reads them in and sends them via email, then deletes them.

If it were me I'd probably just include a basic description of the vehicle (one or two lines) and a hyperlink back to the webpage for more information and pictures of that particular vehicle. If you put it in html format it's just a simple matter of reading the file in and sending it out via email, then destroying the file. Keeps your database load to a minimum and it wouldn't be sending 100 emails per agent, just one email a week or one a month depending on your preferences. The text/html file will continue to accumulate vehicles until your secondary function sends that information via email, then it's destroyed and the next time a car is added that matches those criteria it starts over from scratch. Include a link in each email that allows the customer to deactivate their "agent" once they found the car their looking for and your all set.

Hope that helps.

rubino

that's brilliant stepdad!! absolutely smart solution!...especially the idea to split work in two...in this way I could also use a mySQL table instead than text/html: in the end it's just a matter of creating and sending e-mail, I don't need to ask each agent and then extract and then send all together!

I should only care about Agents updating: if a user changes some criteria during the month, I should delete from records cars not corresponding anymore.

damned, can't believe it could be so easy 🙂 I'll try to find obstacles, but sounds perfect!

Thanks a lot!

Stepdad

That, as they say, is why I make the big money.. ROTFL 🙂

Seriously though, happy to be of assistance.