Help needed with database join and email script

matt_php · July 31, 2011, 11:32am

Hi everyone,

This is my first post on the site here. I hope I can learn from you.

I have a database with 2 tables:

// Jobs table example data

//±—±----------------------------------------------±------------±-------------±-------±---------
//| ID | description | posted_date | closing_date | filled | job_type
//±—±----------------------------------------------±------------±-------------±-------±---------
//| 41 | risus varius orci, in consequat | 2012-02-29 | 2011-09-01 | 0 | temporary
//| 42 | felis eget varius ultrices, mauris | 2011-10-10 | 2011-09-01 | 1 | temporary
//| 43 | Aenean gravida nunc sed pede. | 2012-02-01 | 2011-09-01 | 1 | permanent
//| 44 | eu erat semper rutrum. Fusce | 2012-04-15 | 2011-09-01 | 1 | temporary
//| 45 | lectus convallis est, vitae sodales | 2012-03-07 | 2011-09-01 | 0 | temporary
//| 46 | a neque. Nullam ut nisi | 2012-05-08 | 2011-09-01 | 0 | temporary
//| 47 | dignissim. Maecenas ornare egestas ligula. | 2012-01-07 | 2011-09-01 | 0 | permanent
//| 48 | eget, venenatis a, magna. Lorem | 2011-08-03 | 2011-09-01 | 0 | permanent
//| 49 | Mauris molestie pharetra nibh. Aliquam | 2011-11-02 | 2011-09-01 | 0 | permanent

// Users table example data

//±—±------------------±------------------------------------±----------±------±-------+
//| ID | name | email | job_type | level | active |
//±—±------------------±------------------------------------±----------±------±-------+
//| 61 | Dante Watts | [email protected] | temporary | 5 | 0 |
//| 59 | Jared Howard | [email protected] | permanent | 3 | 0 |
//| 60 | Herrod Roth | [email protected] | temporary | 1 | 1 |
//| 58 | Gavin Garner | [email protected] | permanent | 2 | 0 |
//| 57 | Grant Allen | [email protected] | temporary | 1 | 1 |
//| 56 | Carl Erickson | [email protected] | temporary | 4 | 0 |

It is for a jobs website. Each day I want to send a mass email to all the active users with the jobs posted that day which are still available (not filled) and match their criteria (here it is just ‘temporary’ or ‘permanent’ but this will become more detailed later (e.g. temporary jobs in the south of england).

[php]<?php

// Create an array to hold DB details
$db = array(
‘DBhost’ => ‘xxx’,
‘DBuser’ => ‘xxx’,
‘DBpass’ => ‘xxx’,
‘DBname’ => ‘xxx’
);

// Extract variables from the array
extract($db);

// Open connection to database
$connection = mysql_connect($DBhost, $DBuser, $DBpass);
if (!$connection) {
die('Could not connect ’ . mysql_error());
}

// Select the database
if (!mysql_select_db($DBname)) {
die('Could not connect ’ . mysql_error());
}

// Get today’s date in mySQL DATE format
$today = date(‘Y-m-d’);

// Get all the users who are active
$activeUsers = mysql_query(“SELECT name, email
FROM users
WHERE active = 1”);

// Get all the jobs which are still open and haven’t been filled
$newQuery = mysql_query(“SELECT name, email, description, jobs.job_type
FROM users
LEFT JOIN jobs
ON users.job_type=jobs.job_type”);

while ($row = mysql_fetch_assoc($newQuery)) {
// Something goes here I think
}

// send them an email with the job details

function getJobs($user){
// mail each user the jobs which match their requirements
}

// Close connection to database
mysql_close($connection);

?>[/php]

The above join gives me the following which is kind of what I want but now I just want each person to have an array containing their job descriptions so I can echo them out for each user in a loop:

Does this make any sense? Can you offer any advice?

P.S tables have been truncated to fit

codeguru · July 31, 2011, 1:01pm

Hi matt,

I’ve spent few minutes for your problem and here’s what I’ve got (this may not be the optimal solution but hope this will help):

[php]
// Get all the jobs which are still open and haven’t been filled
$newQuery = mysql_query(“SELECT name, email, description, jobs.job_type
FROM users
LEFT JOIN jobs
ON users.job_type=jobs.job_type”);

$c = 0; 
$final_result = array();
while ($row = mysql_fetch_assoc($newQuery)) {
    if($temp_name=""){
		//create first array of name
		$temp_name = $row['name']; 				// $temp_name='Dante Watts' 
		$temp_name[$c] = $row['description'];	// Dante Watts[0] = 'risus varius orci, in consequat'
		$c = $c + 1;
	}
	else{
		if($temp_name==$row['name']){			//if next name is equal to the first name, insert another key and value to the array
			$temp_name[$c] = $row['description']; // Dante Watts[1] = 'felis eget varius ultrices, mauris'
			$c = $c + 1;
		}
		else{
			//you can optionally append the previous set of values to our $finaly_result before creating another one. 
			//For example, you can append our array "Dante Watts []" as first value of $final_array. It's up to you.
			
			//Now, create another array of succeding name
			//but we need to reset temp_name and our counter c first
			$temp_name='';
			$c=0;
			$temp_name = $row['name']; 				// $temp_name='Jared Howard' 
			$temp_name[$c] = $row['description'];	// Jared Howard[0] = 'Aenean gravida nunc sed pede. '
			$c = $c + 1;
		}
	}
}
/*
So basically, you'll have an array output like:
Dante Watts [0] = "risus varius orci, in consequat";
Dante Watts [1] = "felis eget varius ultrices, mauris";
.
.
.
.
.
.
Dante Watts [8] = " Integer vulputate, risus a ultricies";

Jared Howard[0]  = "Aenean gravida nunc sed pede. ";
.
.
Jared Howard[3]  = "Mauris molestie pharetra nibh. Aliquam";	
*/

[/php]

matt_php · August 1, 2011, 10:10am

Hey Codeguru,

Thanks very much for taking the time to reply. I tried out your solution but I found it wasn’t getting the result I was after. Strange things seemed to be happening to peoples name, like it was shifting the first character of peoples name rather than making arrays.

After much playing around, I found the following worked for me:

[php]
// Get all the active users
$activeUsers = mysql_query(“SELECT name, email
FROM users
WHERE active = 1”);

// Get an associative array for each active user
while ($row = mysql_fetch_assoc($activeUsers)) {
$name = $row[‘name’];
$email = $row[‘email’];
// A new query to find all suitable jobs for each user
$personsJobs = mysql_query(“SELECT jobs.description, jobs.id, name
FROM users
LEFT JOIN jobs
ON users.job_type = jobs.job_type
WHERE name = ‘$name’”);

// Get an associative array for each active users' suitable jobs
$i = 0;
$jobs = array();
while ($theJobs = mysql_fetch_assoc($personsJobs)) {
    $jobs[$i] = $theJobs['description'];
    $i++;
}

// Pass the jobs array and the user details to the mail function for each user sequentially
mailJobs($email, $name, $jobs);

}

// Define the mail function and its appropriate arguments
function mailJobs($email, $name, $jobs){
// This is where the PHP mailer would work its stuff, a string is outputted here for testing purposes
echo (“Now I would send the following email to $name at $email

“);
echo (“Hello there $name, here are your jobs for today:

“);
$yourJobs = implode(”
”, $jobs);
echo $yourJobs;
echo(”

”);
}[/php]

Thanks very much for your help

Matt