MySQL Forums
Forum List  »  Newbie

Unresolved challenge FULLTEXT : email alerts 99% done HELP!
Posted by: Giacomo Biondi Morra
Date: March 02, 2006 07:56AM

Hello,

first of all, thanks to all of you who helped me with my last issue. It now works perfectly !

I now have the following tricky issue :

I have a site with classifieds (see at the bottom of that page : http://www.serveurcentral.com/help/tables.jpg).

I would like my visitors to be able to transform the searches they perform on the search engines on the site in 'alerts' according to their search parameters. That way I can then send the alerts by email to these visitors when new classifieds matching their parameters are posted.

This is an example of the process :

1) a visitor comes to the site and sees all the classifieds.

2) the visitor decides to create an "alert" to receive only the classifieds from code_pays=5 and code_region=10 and rub=3 and cat=99

this is what I insert in my database :

"INSERT INTO alertes (al_typ, al_dir ,al_code_pays, al_code_region, al_rub, al_cat, al_email) VALUES(1, 1, 5, 10, 3, 99, 'visitor@domain.com')"

So, my table "alertes" is filled with the visitor's preferences.


The first problem is quite simple :

I want to process all the alerts in my table "alertes" and send the corresponding classifieds (from my table "Annonces") by email to each visitor.

The big problem is the following:

- A visitor can record several alerts
- For each alert, if there many results (many classifieds), they should be regrouped in ONE email
- I my Alert Form, the visitor must be able not to fill in all the fields (Ex: He wants code_pays=1 but he does not want to specify code_region)
- I might want to specify a keyword as well (Fulltext, search in annonces.titre, annonces.text and annonces.lieu)

I've thought about something like this but it does not seem to work :

SELECT alertes.email as visitor_email, annonces.ann_id as num FROM alertes, annonces
WHERE
annonces.ann_typ = alertes.al_typ AND
annonces.ann_dir = alertes.xxxx AND
annonces.code_pays = alertes.al_code_pays AND
annonces.code_region = alertes.al_code_region AND
annonces.rub = alertes.al_rub AND
annonces.cat = alertes.al_cat AND
MATCH (annonces.titre, annonces.text, annonces.lieu) AGAINST (alertes.keyword IN BOOLEAN MODE)

GROUP BY alertes.email


...
...
...

while($row = mysql_fetch_array($result)) {

$email=row['visitor_email']; // email to be sent

// send the email with the list of classifieds
...
...
..




I want each visitor to get an email like that :

"
Hello

Our website have found 7 classifieds for your alert:

http://www.mydomaine.com/show_classified.php?num=15
http://www.mydomaine.com/show_classified.php?num=57
http://www.mydomaine.com/show_classified.php?num=154
http://www.mydomaine.com/show_classified.php?num=195
http://www.mydomaine.com/show_classified.php?num=211
http://www.mydomaine.com/show_classified.php?num=215
http://www.mydomaine.com/show_classified.php?num=554

"

Does anyone have any idea on these issues ? It would be very very cool if you can give me a working snippet.

Thank you very much in advance

Giac



Edited 9 time(s). Last edit at 03/06/2006 09:10AM by Giacomo Biondi Morra.

Options: ReplyQuote


Subject
Written By
Posted
Unresolved challenge FULLTEXT : email alerts 99% done HELP!
March 02, 2006 07:56AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.