Trying to automatically email users of missing infromation.

[matthewst]

I'm using MySQL - 5.0.24 and phpMyAdmin - 2.8.1.

I need to send my users an email every 15 days that tells them what information of theirs is missing from our database. I have absolutly no idea how to do that.

 

[guelphdad]

the email part would have nothing to do with mysql so your thread needs to be moved to the appropriate forum after you figure out the sql part.

what are you going to use for the mailing? PHP, PERL or something else? that is where the thread will go.

meanwhile how do you denote missing information? do you use empty strings? NULLs or what?

you would obviously use a join to get all the information from your tables and alert the folks what is missing. Do you have that part already? can you start with a join and let us work from there? you would obviously have to list all the columns and then let us know the defaults.

 

[feherke]

Hi

I just played around abit. In Linux the solution could be one line.

0 0 1,15 * * mysql -D [green][i]database[/i][/green] -u [green][i]username[/i][/green] -B -N -e "select [green][i]name[/i][/green],[green][i]mail[/i][/green],concat(if([green][i]data1[/i][/green] is null,'[green][i]Data 1[/i][/green], ',''),if([green][i]data2[/i][/green] is null,'[green][i]Data 2[/i][/green], ',''),if([green][i]data3[/i][/green] is null,'[green][i]Data 3[/i][/green], ','')) from [green][i]members[/i][/green] where [green][i]data1[/i][/green] is null or [green][i]data2[/i][/green] is null or [green][i]data3[/i][/green] is null;" | while IFS=$'\t' read name mail data; do echo "Dear $name, you still not filled $data" | mail -s "Your account data is incomplete" "$mail"; done

Feherke.

http://rootshell.be/~feherke/