Email from DB query

[PatrikIden]

I have i DB a table named "jobadd". And in this table i have the columns "Kategori" and "Länk"
in "Kategori" i have different add categorys ex, "bygg" and in "Länk" i have links that will direct a user
to a page so that the user can see the details of this add.

And in the table USERS i have the columns "Kategori" and "email" in "Kategory" i have different categorys that a company
picks when registering on the site ex, "Bygg & Anläggning" and in "email" is the email to this company.

Now what i want is to send a mail to the companys that is in the "USERS" / "Bygg & Anläggning" categoty. After checking
in the "jobadd" table if there are some new (24 hrs) adds in the category "bygg" and if there are, then send a mail containing the
link(Länk) to this add.

I'v got this code but it does not work. i only get this in the mail. $subject = "Ny Förfrågan från Jobler.se"; $message = "Ny Förfrågan från Jobler.se"; $message .= "\r\nLänk: ";

This code does not work at all, but mayby it will giv a hint of what i need?


<?php
$databasename='*********'; // Name of the database
$mysqladd='************'; // Address to the MySQL Server
$mysqluser='*********'; // Your MySQL UserName
$mysqlpass='**********'; // Your MySQL Password


//CONNECT TO MYSQL
$link=mysql_connect($mysqladd, $mysqluser, $mysqlpass, true) or die('Could not connect to database: ' . mysql_error());

//CONNECT TO DATABASE
mysql_select_db($databasename, $link) or die('Could not connect to table: ' . mysql_error());

$query="SELECT *, DATE_FORMAT(Time_of_Submission, '%Y/%m/%d') FROM jobadd WHERE Kategori='bygg' AND Time_of_Submission BETWEEN SYSDATE() - INTERVAL 4 DAY AND SYSDATE() ORDER BY ID DESC";
$query2="SELECT * FROM USERS WHERE Kategori='Bygg & Anläggning'";

$row = mysql_fetch_array ($query);
$row2 = mysql_fetch_array ($query2);
$adlink = mysql_real_escape_string($Länk);
$to = $row2['email'];
mysql_close($link);

// From email address
$email = "myemail@mail.com";

// To email addresss
$epost = $to;

// The subject
$subject = "Ny Förfrågan från Jobler.se";

// The message
$message = "Ny Förfrågan från Jobler.se";
$message .= "\r\nLänk: ";
$message .= $adlink;

mail($email, $epost, $subject, $message);


echo "Mail skickat!.";


?>

[jironimo]

I don't know why you are doing an escape on

$adlink = mysql_real_escape_string($Länk);
$Länk is nothing and you are escaping it, also I don't think there is any purpose in escaping data after you take out out of the db, unless you want to reinsert it.
Ok so mysql_fetch_array, returns exactly what it says an array, well actually 2 an assoc one and a numbered one.
so your code should be


$row = mysql_fetch_array ($query);
$row2 = mysql_fetch_array ($query2);
$adlink = $row['Länk'];
$to = $row2['email'];
mysql_close($link);


Also, I am pretty sure you don't need 2 mysql queries, you can use a JOIN but I won't comment on that since I didn't understood your mysql tables layout.

[PatrikIden]

Thank you for your reply. i actuly got this code now, and it works sort of.
The problem is that it only sending one link, should be 3 links at least.

And this link is also send to only one email. Can you see wy?



//DB settings
....

//CONNECT TO MYSQL
$link=mysql_connect($mysqladd, $mysqluser, $mysqlpass, true) or die('Could not connect to database: ' . mysql_error());

//CONNECT TO DATABASE
mysql_select_db($databasename, $link) or die('Could not connect to table: ' . mysql_error());

$query="SELECT *, DATE_FORMAT(Time_of_Submission, '%Y/%m/%d') FROM jobadd WHERE Kategori='bygg' AND Time_of_Submission BETWEEN SYSDATE() - INTERVAL 4 DAY AND SYSDATE() ORDER BY ID DESC";
$query2="SELECT * FROM USERS WHERE Kategori='Bygg & Anläggning'";

$result=mysql_query($query);

$result2=mysql_query($query2);

$mailmessage = mysql_fetch_array($result);

$mailmessage2 = mysql_fetch_array($result2);

$num=mysql_numrows($result);

$num=mysql_numrows($result2);

if (!mysql_query($query,$link))
{
die('Error: ' . mysql_error());
}

mysql_close($link)
?>
<?php

$email=mysql_result($result2,$i,"Epost");

// Your email address


$fromemail = "patrik@fcab.se";


// The subject

$subject = "Warning!";



// The message


$message = $mailmessage['where'].", ".$mailmessage['Länk']."\n";


mail($email, $subject, $message, "From: $fromemail");



echo "The email has been sent.";



?>
<?php

$i=0;
while ($i < $num) {

$where=mysql_result($result,$i,"where");
$submitted=mysql_result($result,$i,"Länk");

$submitted2=mysql_result($result2,$i,"email");

echo "<b><br>$bwhere</b><br>$submitted<hr><br>";
echo "<b><br>$submitted2</b><hr><br>";

$i++;
}

?>

[PatrikIden]

I'v got this code now. It works for sending one link(Länk) to all the emails in DB table. But how can i get this to send all the links(Länk) in DB and not to send anything if there is no new link(Länk) to send (that is a link(Länk) that is older then 24 hrs. As it is now it sends a emty email if the newest link(Länk) in DB is older that 24 hrs.



//CONNECT TO MYSQL
$link=mysql_connect($mysqladd, $mysqluser, $mysqlpass, true) or die('Could not connect to database: ' . mysql_error());

//CONNECT TO DATABASE
mysql_select_db($databasename, $link) or die('Could not connect to table: ' . mysql_error());

$query="SELECT *, DATE_FORMAT(Time_of_Submission, '%Y/%m/%d') FROM jobadd WHERE Kategori='bygg' AND Time_of_Submission BETWEEN SYSDATE() - INTERVAL 1 DAY AND SYSDATE() ORDER BY ID DESC";
$query2="SELECT * FROM USERS WHERE Kategori='Bygg & Anläggning'";

$result=mysql_query($query);

$result2=mysql_query($query2);

$mailmessage = mysql_fetch_array($result);

$mailmessage2 = mysql_fetch_array($result2);

$num=mysql_numrows($result);

$num=mysql_numrows($result2);

if (!mysql_query($query,$link))
{
die('Error: ' . mysql_error());
}

mysql_close($link)
?>
<?php
$i=0;
while ($i < $num) {
$where=mysql_result($result,$i,"where");
$submitted=mysql_result($result,$i,"Länk");

$email=mysql_result($result2,$i,"email");


// Your email address


$fromemail = "no_reply@jobler.se";


// The subject

$subject = "Det har kommit in en ny Tjänstförfrågan på Jobler.se som passar er profil (klicka på länken nedan)";



// The message


$message = $mailmessage['where']." ".$mailmessage['Länk']."\n";


mail($email, $subject, $message, "From: $fromemail");


$i++;
}
echo "Förfrågan skickad";

?>
<?php

$i=0;
while ($i < $num) {
$where=mysql_result($result,$i,"where");
$submitted=mysql_result($result,$i,"Länk");

$submitted2=mysql_result($result2,$i,"email");

echo "<b><br>$where</b><br>$submitted<hr><br>";
echo "<b><br>$submitted2</b><hr><br>";


$i++;
}

?>

[jironimo]

This would be much easier if you would tell me the structure of your database, but anyway, here is an adaptation of your code, I hope it works.

ATTETION: THIS CODE IS NOT TEST AND SHOULD NOT BE USED IN A PRODUCTION ENVIROMENT


<?php

//CONNECT TO MYSQL
$link = mysql_connect( $mysqladd, $mysqluser, $mysqlpass, true ) or die( 'Could not connect to database: ' . mysql_error() );

//CONNECT TO DATABASE
mysql_select_db( $databasename, $link ) or die( 'Could not connect to table: ' .
mysql_error() );

$query = "SELECT *, DATE_FORMAT(Time_of_Submission, '%Y/%m/%d') FROM jobadd WHERE Kategori='bygg' AND Time_of_Submission BETWEEN SYSDATE() - INTERVAL 1 DAY AND SYSDATE() ORDER BY ID DESC";
$query2 = "SELECT * FROM USERS WHERE Kategori='Bygg & Anläggning'";

$result = mysql_query( $query );
$result2 = mysql_query( $query2 );

//the email we are sending from
$fromemail = "no_reply@jobler.se";

//number of rows returned from our 2 queries
$link_count = mysql_num_rows( $result );
$users_count = mysql_num_rows( $result2 );

//check if there is at least 1 link to be sent and at least 1 user to get the email
if ( $link_count > 0 && $users_count > 0 )
{

//we should prepare the message in advance since all the users get the same email
while ( $mailmessage = mysql_fetch_assoc( $result ) )
{
$where = $mailmessage['where'];
$submitted = $mailmessage["Länk"];

// The subject
$subject = "Det har kommit in en ny Tjänstförfrågan på Jobler.se som passar er profil (klicka på länken nedan)";


// The message
$message .= $mailmessage['where'] . " " . $mailmessage['Länk'] . "\n";
}

//now we will send the message to all the users
while ( $users = mysql_fetch_assoc( $result2 ) ){


$email = $users['email'];

if(mail($email, $subject, $message, "From: $fromemail"))
echo "<b><br>Sent to $email</b><br>Message: $message<hr><br>";

}
}
else
{
echo "We are sorry it looks like we have no links to send or no one to send them to!";
}

[PatrikIden]

Thank you for your reply. Now iget this error meg: Error: Query was empty

[jironimo]

Sorry Patrik, like I said I copied your php and modified it a bit and some bits of the old code didn't fit the new ones.

I think it's okay now, I've edited the above text with the corrections needed.

If the code above doesn't work, just leave me a message here and I will do a mock-up with a database and give you a complete tested code.

Cheers!

[PatrikIden]

Sorry Patrik, like I said I copied your php and modified it a bit and some bits of the old code didn't fit the new ones.

I think it's okay now, I've edited the above text with the corrections needed.

If the code above doesn't work, just leave me a message here and I will do a mock-up with a database and give you a complete tested code.

Cheers!

Thank's it's working now. How can i modify this code so that a text is echoed if there are no links(Länk) to mail, Like " echo No links to send":? And Do you know a way to check if a link(Länk) is sent once and if so dont send that link(Länk) againe?

[jironimo]

<?php

//CONNECT TO MYSQL
$link = mysql_connect( $mysqladd, $mysqluser, $mysqlpass, true ) or die( 'Could not connect to database: ' . mysql_error() );

//CONNECT TO DATABASE
mysql_select_db( $databasename, $link ) or die( 'Could not connect to table: ' .
mysql_error() );

$query = "SELECT *, DATE_FORMAT(Time_of_Submission, '%Y/%m/%d') FROM jobadd WHERE Kategori='bygg' AND Time_of_Submission BETWEEN SYSDATE() - INTERVAL 1 DAY AND SYSDATE() ORDER BY ID DESC";
$query2 = "SELECT * FROM USERS WHERE Kategori='Bygg & Anläggning'";

$result = mysql_query( $query );
$result2 = mysql_query( $query2 );

//the email we are sending from
$fromemail = "no_reply@jobler.se";

//number of rows returned from our 2 queries
$link_count = mysql_num_rows( $result );
$users_count = mysql_num_rows( $result2 );

//check if there is at least 1 link to be sent and at least 1 user to get the email
if ( $link_count > 0 && $users_count > 0 )
{

//we should prepare the message in advance since all the users get the same email
while ( $mailmessage = mysql_fetch_assoc( $result ) )
{
$where = $mailmessage['where'];
$submitted = $mailmessage["Länk"];

// The subject
$subject = "Det har kommit in en ny Tjänstförfrågan på Jobler.se som passar er profil (klicka på länken nedan)";


// The message
$message .= $mailmessage['where'] . " " . $mailmessage['Länk'] . "\n";
}

//now we will send the message to all the users
while ( $users = mysql_fetch_assoc( $result2 ) ){


$email = $users['email'];

if(mail($email, $subject, $message, "From: $fromemail"))
echo "<b><br>Sent to $email</b><br>Message: $message<hr><br>";

}
}
else
{
echo "We are sorry it looks like we have no links to send or no one to send them to!";
}


Since both queries need to return a result check them in the same if, so I just made an else which will echo a message if either if the results are empty.

As for your 2nd question:
You can modify the date to make it older then 24 hours so that the next execution of the script the link won't be selected anymore.
Or you could add an other field in your jobadd which will be 0 or 1. The value 0 will be the default and it means that the link wasn't sent yet, and after you send it you will update that value to 1 which means you already sent that link. Also you will need to modify your first query to select all results that have 0 in the newly added field.

I hope this helps.

[PatrikIden]

Thank you so much for your help. Could i just ask of you, how would the code look like if modyfied to work with the 0-1 function. That is if i add the field 0 and 1 in DB jobadd. I think this solution is what i need.

Thank you.

/Patrik.

[jironimo]

<?php

//CONNECT TO MYSQL
$link = mysql_connect( $mysqladd, $mysqluser, $mysqlpass, true ) or die( 'Could not connect to database: ' . mysql_error() );

//CONNECT TO DATABASE
mysql_select_db( $databasename, $link ) or die( 'Could not connect to table: ' .
mysql_error() );

$query = "SELECT *, DATE_FORMAT(Time_of_Submission, '%Y/%m/%d') FROM jobadd WHERE Kategori='bygg' AND Time_of_Submission BETWEEN SYSDATE() - INTERVAL 1 DAY AND SYSDATE() AND sent = 0 ORDER BY ID DESC";
$query2 = "SELECT * FROM USERS WHERE Kategori='Bygg & Anläggning'";

$result = mysql_query( $query );
$result2 = mysql_query( $query2 );

//the email we are sending from
$fromemail = "no_reply@jobler.se";

//number of rows returned from our 2 queries
$link_count = mysql_num_rows( $result );
$users_count = mysql_num_rows( $result2 );

//check if there is at least 1 link to be sent and at least 1 user to get the email
if ( $link_count > 0 && $users_count > 0 )
{

//we should prepare the message in advance since all the users get the same email
while ( $mailmessage = mysql_fetch_assoc( $result ) )
{
$where = $mailmessage['where'];
$submitted = $mailmessage["Länk"];

// The subject
$subject = "Det har kommit in en ny Tjänstförfrågan på Jobler.se som passar er profil (klicka på länken nedan)";


// The message
$message .= $mailmessage['where'] . " " . $mailmessage['Länk'] . "\n";

//mark the link as sent
mysql_query("UPDATE jobadd SET sent = '1' WHERE id = '".$mailmessage['id']."'");
}

//now we will send the message to all the users
while ( $users = mysql_fetch_assoc( $result2 ) ){


$email = $users['email'];

if(mail($email, $subject, $message, "From: $fromemail"))
echo "<b><br>Sent to $email</b><br>Message: $message<hr><br>";

}
}
else
{
echo "We are sorry it looks like we have no links to send or no one to send them to!";
}

This assumes the newly added row is called sent and the you have a unique key in your jobadd table called id

[PatrikIden]

That worked perfectly. Thank you so much for your help. I cant tell you how much it means to me, now i got a fully automatic Job ad system thank's to you'r help. So again i thank you.