Help with a PHP/MSQL call

Hi all, need a bit more help now with some php/mysql suff.

I am trying to adapt a script that i have and i need to alter a search that is done on my db.

What i need to do is add in another condition that comes from a different table. So what i have atm is a table holding Job seekers information ( job_seeker_info) one with their profile(job_resume) and one which says whether they have signed up or not to receive emails (job_email_signup).

atm when a new job is added the script looks for all jobseekers with a matching preffered category from(job_resume) and then gets the associated email from (job_seeker_info) and sends them out. What i need is to add in an extra bit where by it does all that and then checks (job_email_signup) to make sure the user has elected to receive emails.

The current working code is below. I think i will just need to add an IF statement somewhere but im not sure exactly where to put it or what to put.

$sch = array();
$sch[] = "category like '%$JobStr%'";

$q8 = "select * from job_resume ".(($sch)?"where ".join(" and ", $sch):"");
$r8 = mysql_query($q8) or die(mysql_error());

while($a8 = mysql_fetch_array($r8))
{
$qs = "select * from job_seeker_info where uname = \"$a8[uname]\" ";
$rs = mysql_query($qs) or die(mysql_error());
$as = mysql_fetch_array($rs);

$to = $as[job_seeker_email];
$subject = "An employer has posted a vacancy that matches your requirements";
$message = "xxx";
$from = "From: <$contactemail>";
mail($to, $subject, $message, $from);
}

Code (markup):

i need to add the condition from another table for the same user selected above - ie

select * from job_email_signup where Email ="1"

thanks again all

Mike

 

Do job_seeker_info, job_resume, job_email_signup have unique IDs by which you can relate them together? it looks like you work with strings, you should really start using IDs. Makes for faster searching and prevents all sorts of string based hassle.

Add (auto_increment) IDs to all tables if you can.

So when yuo eneter a new profile it's entered with auto_increment. Use last_insert_id() to fetch that ID.

Then insert that ID with the rest in the email preferences table etc.

Without IDs to link up tables, you will find it very hard (and error-prone) to combine tables.

 

hello again TOPS30.

Thanks for the suggestions again! Each of those tables contains the username which is unique on all tables. this is uname above

 

OK well it will work but it's not really ideal. This should work (mind the brackets )

$sch = array();
$sch[] = "category like '%$JobStr%'";

//My added constraint should/could go here but it's a bit messy
$q8 = "select * from job_resume ".(($sch)?"where ".join(" and ", $sch):"");
$r8 = mysql_query($q8) or die(mysql_error());

while($a8 = mysql_fetch_array($r8))
{
//So I put it in this query
$qs = "SELECT * FROM job_seeker_info jsi LEFT JOIN job_email_signup jes on jsi.uname = jes.uname WHERE uname = \"$a8[uname]\" AND jes.opted_in = 1"; //opted_in = what you called that field
$rs = mysql_query($qs) or die(mysql_error());
$as = mysql_fetch_array($rs);

$to = $as[job_seeker_email];
$subject = "An employer has posted a vacancy that matches your requirements";
$message = "xxx";
$from = "From: <$contactemail>";
mail($to, $subject, $message, $from);
}

PHP:

Or if you want some reporting:

$sch = array();
$sch[] = "category like '%$JobStr%'";

//My added constraint should/could go here but it's a bit messy
$q8 = "select * from job_resume ".(($sch)?"where ".join(" and ", $sch):"");
$r8 = mysql_query($q8) or die(mysql_error());

while($a8 = mysql_fetch_array($r8))
{
//So I put it in this query
$qs = "SELECT * FROM job_seeker_info jsi LEFT JOIN job_email_signup jes on jsi.uname = jes.uname WHERE uname = \"$a8[uname]\"";
$rs = mysql_query($qs) or die(mysql_error());
$as = mysql_fetch_array($rs);
$emailed = 0;
$not_emailed = 0;
if($ad['opted_in'] == 1) //opted_in = what you called that field
{
$to = $as[job_seeker_email];
$subject = "An employer has posted a vacancy that matches your requirements";
$message = "xxx";
$from = "From: <$contactemail>";
mail($to, $subject, $message, $from);
$emailed++;
}
else
{
$not_emailed++;
}
}

echo "<p>We sent " . $emailed . " updates. " . $not_emailed . " missed out since they didn't want any emails</p>";

PHP:

 

i tried the first and it gives me the following error

which i googled to no avail

but i dont understand it

 

This should fix it. It's ambiguous because SELECT * gets it twice.

$sch = array();
$sch[] = "category like '%$JobStr%'";

//My added constraint should/could go here but it's a bit messy
$q8 = "select * from job_resume ".(($sch)?"where ".join(" and ", $sch):"");
$r8 = mysql_query($q8) or die(mysql_error());

while($a8 = mysql_fetch_array($r8))
{
//So I put it in this query
$qs = "SELECT jsi.job_seeker_email FROM job_seeker_info jsi LEFT JOIN job_email_signup jes on jsi.uname = jes.uname WHERE jsi.uname = \"$a8[uname]\""; //Instead of SELECT *, just get what you need
$rs = mysql_query($qs) or die(mysql_error());
$as = mysql_fetch_array($rs);
$emailed = 0;
$not_emailed = 0;
if($ad['opted_in'] == 1) //opted_in = what you called that field
{
$to = $as[job_seeker_email];
$subject = "An employer has posted a vacancy that matches your requirements";
$message = "xxx";
$from = "From: <$contactemail>";
mail($to, $subject, $message, $from);
$emailed++;
}
else
{
$not_emailed++;
}
}

echo "<p>We sent " . $emailed . " updates. " . $not_emailed . " missed out since they didn't want any emails</p>";

PHP:

 

sorry about this but im still getting the same

 

Sorry, my bad. the WHERE clause is updated now.

It said WHERE uname = 'whatever' but there's jsi.uname as well as jes.uname so it didn't know which one to pick. I now gave it the table prefix.