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.