0

I have the following code which performs a sql select with a where condition involving an array.

I want subsequent queries (within the same code) to be performed using each unique value of the array

Here are the specifics:

This initial query begins the process by creating an array from which the following queries gets their conditional values.

$SQL = "SELECT clientID, servicearea FROM servicesrendered where servicearea like ' oil change%' group by clientID";
$r = mysql_query($SQL);
while ($row = mysql_fetch_array($r, MYSQL_ASSOC)) {
$clientID=$row['clientID'];
$clients[] = "$clientID";
//$array=array($client,"");
}
foreach($clients as  $client) {
$clientID = $client[0];

//$array = array("$client", "");
$list = "'". implode("','", $clients) ."'";
echo "$list";

$get_swork = "SELECT servicearea, date, customerid, clientID FROM servicesrendered WHERE servicearea=' Oil Change ' and clientID IN ($list)  order by id ASC limit 1";//limit 1 means the last entered

 $get_swork_res = mysql_query($get_swork); 

  if (mysql_num_rows($get_swork_res) > 0) 
  { 
     while ( $swork_info = mysql_fetch_array($get_swork_res)) 
    { 
      $servicearea = $swork_info['servicearea']; 
      $date = $swork_info['date']; 
      $customerid = $swork_info['customerid']; 
      $clientID = $swork_info['clientID'];

}
	}

In the $get_swork statement, the where condition produces the following:

WHERE servicearea=' Oil Change ' and clientID IN ('0927HD','3232','342DF','4FCDS','5437DC','543GD1','5455FV','54FD43','6736HD','87JFJ','DG9642','GDS34','RFFD43','TF42DC')

Now I have two subsequent queries that relies on variables from the preceding query, here are those queries

Denoted as Q1

$get_swork3 = "select * from ajax_client where customerid='$customerid'";//here we are pulling cell and email information from ajax client using the customer id from get_swork2. clientid is not recorded in ajax_client, so this statement is search by customer id
 $get_swork3_res = mysql_query($get_swork3); 

  if (mysql_num_rows($get_swork3_res) > 0) 
  { 
     while ( $swork3_info = mysql_fetch_array($get_swork3_res)) 
    { 
     $email =$swork3_info['email']; 
          }
}

Denoted as Q2

$get_swork2 = "select * from additional_cars where clientID='$clientID'";// order by id desc limit 1";
 $get_swork2_res = mysql_query($get_swork2); 

  if (mysql_num_rows($get_swork2_res) > 0) 
  { 
     while ( $swork2_info = mysql_fetch_array($get_swork2_res)) 
    { 
      $firstname = $swork2_info['firstname']; 
      $lastname = $swork2_info['lastname']; 
      $customerid = $swork2_info['customerid'];
      $dhtmlgoodies_category = $swork2_info['dhtmlgoodies_category']; 
      $dhtmlgoodies_subcategory = $swork2_info['dhtmlgoodies_subcategory']; 
      $caryear = $swork2_info['caryear']; 
      
 } // close foreach

My current task is to perform Q1 and Q2 with each unique array value in the initial query written at the top

Currently, Q1 performs this

select * from ajax_client where customerid='Bs6444'

and stops. I would like to select using the where condition of "customerid" of all associated ids generated in the array.

i.e: the values of array is ('0927HD','3232','342DF','4FCDS','5437DC' ect..), my initial query makes available a variable called $customerid='Bs6444' which is associated with $clientID='0927HD' (the same should happen with all values of the array). And thus,the same for both Q1 and Q2 above.

Is this doable? I know this may be way out of the stratosphere, but I hope there are some thoughts on this.

Any thoughts is very appreciated!
Mossa

Edited by mbarandao: n/a

3
Contributors
14
Replies
17
Views
6 Years
Discussion Span
Last Post by mbarandao
0

I think you are making it little complicated, This could be achevied using joining the table. So please post following

1) your table structure and
2) input to php page
3) output format expected

Also tell me what is the difference between your customerid and clientid

Edited by urtrivedi: n/a

0

I think you are making it little complicated, This could be achevied using joining the table. So please post following

1) your table structure and
2) input to php page
3) output format expected

Also tell me what is the difference between your customerid and clientid

Thank you very much for the reply;

To your request:

1: three tables
tbl: servicesrendered
id, servicearea, clientID(is actually a license plate#), customerid(account number), date

tbl 2: ajax_client (many columns, but only need the following)
email

tbl3: additional_cars (many columns, but only need the following)
firstname,lastname,car_make,car_model,caryear

2.input to php page, on this, I'm not following. could you please clarify?

3. there is no output format, the code will be ran using cron job. In the code, an email is to be sent once all the variables have been selected, passed and crossed check. Then, a mail function is written into the code to perform that task. this part works fine. I have ran some test with manually coding in some values.

if you like for me to post the entire code --about 315, I can PM it to you.

Again, thank you
Mossa

Edited by mbarandao: n/a

0

2.input to php page, on this, I'm not following. could you please clarify?

I mean any thing you pass like 'oil change' or anything else

3. there is no output format, the code will be ran using cron job. In the code, an email is to be sent once all the variables have been selected, passed and crossed check. Then, a mail function is written into the code to perform that task. this part works fine. I have ran some test with manually coding in some values.

If you have tested that what is the problem? please tell to whom you want to send mails. also tell me where is clientid/customernumber in ajax_client and additional_cars

0

I mean any thing you pass like 'oil change' or anything else


If you have tested that what is the problem? please tell to whom you want to send mails. also tell me where is clientid/customernumber in ajax_client and additional_cars

Thanks for the reply and for the clarification.

Actually, nothing is passed to the php page. the code is self contained and ran as cron job; whereas, I'm selecting all clientIDs (license plate numbers) from tbl servicesrendered where column servicearea= ' Oil Change ', and making them into an array, and then imploding them to create the variable $list. This variable is then used in the second query as follows:

$get_swork = "SELECT servicearea, date, customerid, clientID FROM servicesrendered WHERE servicearea=' Oil Change ' and clientID IN ($list)  order by id ASC limit 1";

. This query allows me to pull the other variable (ie: $date, $customerid)

You ask: "If you have tested that what is the problem?" by testing manually, I mean that if in the initial query, I replaced the array $list with a single clientID ('76dhff'), the complete script works. To get the mail to work, I have the following:

$timestamp = strtotime($date);// variable date is passed from the process above
$startDate=$timestamp; 
$limit=90;
$z=floor((time() - $startDate)/86400);
$days=$limit-abs($z);

//if remaining days equal to 0 perform the send the following email
if ($days == 0) 
{
//mail begins
         $fromAddr = 'noreply@autotechpro.net';
	 $today_date = date("M-d-Y"); 
	 $today_is =date('l, F j, Y');
	 $email2 ='admin@autotechpro.net';
	 $subjectStr = 'Courtsey Automatic Oil Change reminder';
	
$mailBodyText = <<< HHHHHHHHHHHHHH
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html;charset=utf-8">
<title>mail</title>
<style type="text/css">
            h1, h2, td, p {
                font-family: helvetica, arial, sans-serif;
            }
        </style>
</head>
  <body>
Dear $firstname $lastname:<p>
This is an automatic courtsey Oil Change reminder. Our records indicate that as of today, $today_is, your $dhtmlgoodies_category $dhtmlgoodies_subcategory is now due for an oil change!!!</p>
<table cellpadding='3'>
<tr><td colspan='4' align='left'><b>Vehicle Details</b></td></tr>
<tr>
<td bgcolor='#333333'><font color='#FFFFFF'>Car year</td>
<td bgcolor='#333333'><font color='#FFFFFF'>Car Make</td>
<td bgcolor='#333333'><font color='#FFFFFF'>Car Make</td>
<td bgcolor='#333333'><font color='#FFFFFF'>Lic. plate #</td>
<td bgcolor='#333333'><font color='#FFFFFF'>Last Oil Change Date</td>
</tr>
<tr>
<td style='color:#FF0000;'>$caryear</td>
<td style='color:#FF0000;'>$dhtmlgoodies_category</td>
<td style='color:#FF0000;'>$dhtmlgoodies_subcategory</td>
<td style='color:#FF0000;'>$clientID</td>
<td style='color:#FF0000;'>$date</td>
</tr>
</table>
<p>
Please contact the shop to schedule a visit, or simply stop by.<p>
Thank you,
</body>
</html>
HHHHHHHHHHHHHH;

$headers= <<< TTTTTTTTTTTT
Bcc:$email2
From: $fromAddr
MIME-Version: 1.0
Content-Type: text/html;
TTTTTTTTTTTT;

return mail( $recipientAddr , $subjectStr , $mailBodyText, $headers);
        
    }

clientID and customerid are in the both ajax_client and additional_cars tables. I hope this provides more clarity.

Again, thank you for your time on this.

Mossa

Edited by mbarandao: n/a

0

Now I understand your problem as following. I hope I am right.

You want to send reminder mail to those customers who have changed vehicle oil 3 months back.

So I would follow given steps to achieve this.

1) select records which are getting 3 months old. today. (one single select query with proper join will extract all information required to send mail).

2) loop them to send mail (you already have mail logic, just you need to keep under loop).

Here is complete code, nothing else required, Here my first line is sql query, first lets run it in phpmyadmin, if it gives proper result than you may go for mail part. query may have some syntax error as I have no database here.

$get_swork2 = "select 
 ad.fistname, ad.lastname, ad.customerid, ad.dhtmlgoodies_category, ad.dhtmlgoodies_subcategory, ad.caryear, aj.email, sr.servicearea, sr.clientID, sr.customerid, max(sr.date)  last_date
from servicesrendered sr inner join ajax_client aj on sr.clientId=aj.clientid and sr.customerid=aj.customerid
inner join additinal_cars ad on
sr.clientId=ad.clientid and sr.customerid=ad.customerid
where
sr.servicearea like '%Oil Change%' and trim(aj.email) <>''
having date_add(max(sr.date), interval 3 month)=current_date
group by
 ad.fistname, ad.lastname, ad.customerid, ad.dhtmlgoodies_category, ad.dhtmlgoodies_subcategory, ad.caryear, aj.email, sr.servicearea, sr.clientID, sr.customerid";

 $get_swork2_res = mysql_query($get_swork2); 

  if (mysql_num_rows($get_swork2_res) > 0) 
  { 
     while ( $swork2_info = mysql_fetch_array($get_swork2_res)) 
    { 

/////fetch data
      $firstname = $swork2_info['firstname']; 
      $lastname = $swork2_info['lastname']; 
      $customerid = $swork2_info['customerid'];
      $dhtmlgoodies_category = $swork2_info['dhtmlgoodies_category']; 
      $dhtmlgoodies_subcategory = $swork2_info['dhtmlgoodies_subcategory']; 
      $caryear = $swork2_info['caryear']; 
      $recipientAddr=$swork2_info['email']; 

///////send mail
//mail begins
         $fromAddr = 'noreply@autotechpro.net';
	 $today_date = date("M-d-Y"); 
	 $today_is =date('l, F j, Y');
	 $email2 ='admin@autotechpro.net';
	 $subjectStr = 'Courtsey Automatic Oil Change reminder';
	
$mailBodyText = <<< HHHHHHHHHHHHHH
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html;charset=utf-8">
<title>mail</title>
<style type="text/css">
            h1, h2, td, p {
                font-family: helvetica, arial, sans-serif;
            }
        </style>
</head>
  <body>
Dear $firstname $lastname:<p>
This is an automatic courtsey Oil Change reminder. Our records indicate that as of today, $today_is, your $dhtmlgoodies_category $dhtmlgoodies_subcategory is now due for an oil change!!!</p>
<table cellpadding='3'>
<tr><td colspan='4' align='left'><b>Vehicle Details</b></td></tr>
<tr>
<td bgcolor='#333333'><font color='#FFFFFF'>Car year</td>
<td bgcolor='#333333'><font color='#FFFFFF'>Car Make</td>
<td bgcolor='#333333'><font color='#FFFFFF'>Car Make</td>
<td bgcolor='#333333'><font color='#FFFFFF'>Lic. plate #</td>
<td bgcolor='#333333'><font color='#FFFFFF'>Last Oil Change Date</td>
</tr>
<tr>
<td style='color:#FF0000;'>$caryear</td>
<td style='color:#FF0000;'>$dhtmlgoodies_category</td>
<td style='color:#FF0000;'>$dhtmlgoodies_subcategory</td>
<td style='color:#FF0000;'>$clientID</td>
<td style='color:#FF0000;'>$date</td>
</tr>
</table>
<p>
Please contact the shop to schedule a visit, or simply stop by.<p>
Thank you,
</body>
</html>
HHHHHHHHHHHHHH;

$headers= <<< TTTTTTTTTTTT
Bcc:$email2
From: $fromAddr
MIME-Version: 1.0
Content-Type: text/html;
TTTTTTTTTTTT;

 mail( $recipientAddr , $subjectStr , $mailBodyText, $headers);







      
 } // close foreach
0

urtrivedi, I really appreciate your follow up and your rewrite of the code. Your understanding is dead-on!

One additional caveat, my intention is to send emails at certain date interval -in particularly 3 reminders. My thoughts were: using the value of each record's date value and apply the following code:

$timestamp = strtotime($date);
$startDate=$timestamp; 
$limit=90;//the number of days counting down from
$z=floor((time() - $startDate)/86400);
$days=$limit-abs($z);

with the above, I'm able to determine the number of days that are either left before the three months are up or have past for each client.

and then where true, construct the following if statements and send email:

if ($day == 0)
{//send Oil Change Now due email)
}
if ($days == 5)
{//send Oil Change due in five days email)
}
//and
if ($days == -15)//if past 15 days
{//send Oil Change is now  15 days overdue email)
}

Theoretically, this is how I see it, but not sure how this would be incorporated in your code rewrite.

I appreciate any further thoughts on this.

PS:
Concerning the sql,I run it through phpmyadmin and it returns the following error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '$get_swork2 = "select ad.fistname, ad.lastname, ad.customerid, ad.dhtmlgoodies_c' at line 1

I'm not very familiar with that strategy of join sql construct, so I'm not sure what's wrong?

Again, thank you very much for the superb help!
Mossa

Edited by mbarandao: n/a

0

Karthik_pranas, thanks for your post and concern! Actually, it did not. My theoretical idea seemed a bit more complicated than I imagined.

Can you deduce anything from what I have written and the suggestion of urtrivedi in this current post?

I appreciate any further thoughts on this.

Best,
Mossa

0

Got the sql error initial reported resolved, but now the query returns zero results.
the modified sql:

$get_swork2 = "select ad.firstname, ad.lastname, ad.customerid, ad.dhtmlgoodies_category, ad.dhtmlgoodies_subcategory, ad.caryear, aj.email, sr.servicearea, sr.clientID, sr.customerid, max(sr.date)  last_date from servicesrendered sr inner join ajax_client aj on sr.clientID=aj.clientID and sr.customerid=aj.customerid  inner join additional_cars ad on sr.clientID=ad.clientID and sr.customerid=ad.customerid where sr.servicearea=' Oil Change ' and trim(aj.email) <>'' having date_add(max(sr.date), interval 1 month) =current_date order by ad.firstname, ad.lastname, ad.customerid, ad.dhtmlgoodies_category, ad.dhtmlgoodies_subcategory, ad.caryear, aj.email, sr.servicearea, sr.clientID, sr.customerid";

There are records with the necessary criteria. any thoughts!

Edited by mbarandao: n/a

0

If your data is not confidential, then go to phpmyadmin, export all four tables used in sql format, and attach that file here. I will run this query here and let you know the solution.

We will also manage 3 reminders in same code

Edited by urtrivedi: n/a

0

If your data is not confidential, then go to phpmyadmin, export all four tables used in sql format, and attach that file here. I will run this query here and let you know the solution.

We will also manage 3 reminders in same code

Excellent, the sql dump is attached!

Thanks,
Mossa

Edited by mbarandao: n/a

Attachments
-- phpMyAdmin SQL Dump
-- version 3.3.8.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jul 19, 2011 at 01:25 AM
-- Server version: 5.1.58
-- PHP Version: 5.2.9

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `autotec6_ab_shop`
--

-- --------------------------------------------------------

--
-- Table structure for table `additional_cars`
--

CREATE TABLE IF NOT EXISTS `additional_cars` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company` varchar(30) NOT NULL,
  `firstname` varchar(25) NOT NULL DEFAULT '',
  `lastname` varchar(50) NOT NULL DEFAULT '',
  `contactperson` varchar(45) NOT NULL DEFAULT '',
  `address` varchar(100) NOT NULL DEFAULT '',
  `city` varchar(25) NOT NULL DEFAULT '',
  `state` varchar(2) NOT NULL DEFAULT '',
  `zipcode` varchar(5) NOT NULL DEFAULT '',
  `currentmileage` varchar(20) NOT NULL DEFAULT '',
  `drivenmileage` varchar(20) NOT NULL DEFAULT '',
  `dhtmlgoodies_category` varchar(20) NOT NULL DEFAULT '',
  `dhtmlgoodies_subcategory` varchar(50) NOT NULL DEFAULT '',
  `caryear` varchar(4) NOT NULL DEFAULT '',
  `clientID` varchar(10) NOT NULL DEFAULT '',
  `customerid` varchar(8) NOT NULL DEFAULT '',
  `registeredstate` varchar(3) NOT NULL DEFAULT '',
  `servicearea` varchar(250) NOT NULL DEFAULT '',
  `service` text NOT NULL,
  `servicedesc` text,
  `servicedescription` text NOT NULL,
  `todays_date` datetime NOT NULL,
  `invoicenum` varchar(24) NOT NULL,
  `firstname_initial` varchar(3) NOT NULL,
  `time` time NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=62 ;

--
-- Dumping data for table `additional_cars`
--

INSERT INTO `additional_cars` (`id`, `company`, `firstname`, `lastname`, `contactperson`, `address`, `city`, `state`, `zipcode`, `currentmileage`, `drivenmileage`, `dhtmlgoodies_category`, `dhtmlgoodies_subcategory`, `caryear`, `clientID`, `customerid`, `registeredstate`, `servicearea`, `service`, `servicedesc`, `servicedescription`, `todays_date`, `invoicenum`, `firstname_initial`, `time`) VALUES
(1, 'Self Account', 'Mossa', 'Barandao', '', '6572 Park Fifth Ave', 'New York', 'NY', '22384', '4334', '', 'JAGUAR', 'X-TYPE', '1988', 'DG9642', 'Mb3344', 'DC', ' Exhaust  on June 30, 2011, 9:55 am<br/>', '', '<br/>On June 30, 2011, 9:55 am<br/>We performed the following:<br/>Serviced Area:  Exhaust <br/>Service Details:<br/>\nexshaust<br/><br/>Mileage @ Service: 4334<br/><br/>----End of Service Report----<br/><br/>On June 24, 2011, 2:48 pm<br/>We performed the following:<br/>Serviced Area:  Driveshaft & Axle <br/>Service Details:<br/>\ntest<br/><br/>Mileage @ Service: 5634<br/><br/>----End of Service Report----<br/><br/>On June 22, 2011, 4:39 pm<br/>We performed the following:<br/>Serviced Area:  Clutch , Driveshaft & Axle , Suspension <br/>Service Details:<br/>\ntest<br/><br/>Mileage @ Service: 6377<br/><br/>----End of Service Report----<br/><br/>On June 21, 2011, 12:31 pm<br/>We performed the following:<br/>Serviced Area:  Air Intake , Brake , Oil Change , Engine Mechanical , Steering , Transmission <br/>Service Details:<br/>\ntest<br/><br/>Mileage @ Service: 4343<br/><br/>----End of Service Report----<br/><br/>On June 20, 2011, 11:05 am<br/>We performed the following:<br/>Serviced Area:  Clutch , Engine Mechanical , Steering <br/>Service Details:<br/>\ntest<br/><br/>Mileage @ Service: 4343<br/><br/>----End of Service Report----<br/><br/>On April 18, 2011, 9:43 am<br/>We performed the following:<br/>Serviced Area:  Driveshaft & Axle , Engine Mechanical <br/>Service Details:<br/>\ntest<br/><br/>Mileage @ Service: 54<br/><br/>----End of Service Report----<br/><br/>On April 16, 2011, 7:08 pm<br/>We performed the following:<br/>Serviced Area:  Air Intake , Brake , Driveshaft & Axle , Engine Mechanical <br/>Service Details:<br/>\nTest<br/><br/>Mileage @ Service: 657<br/><br/>----End of Service Report----<br/><br/>On April 10, 2011, 6:04 pm<br/>We performed the following:<br/>Serviced Area:  Exhaust <br/>Service Details:<br/>\ntest<br/><br/>Mileage @ Service: 5433<br/><br/>----End of Service Report----<br/><br/>On April 10, 2011, 5:00 pm<br/>We performed the following:<br/>Serviced Area:  Cooling System , Engine Mechanical <br/>Service Details:<br/>\ntest<br/><br/>Mileage @ Service: 54545<br/><br/>----End of Service Report----<br/><br/>On April 10, 2011, 4:59 pm<br/>We performed the following:<br/>Serviced Area:  Cooling System , Engine Mechanical <br/>Service Details:<br/>\ntest<br/><br/>Mileage @ Service: 54545<br/><br/>----End of Service Report----<br/><br/>On April 10, 2011, 4:51 pm<br/>We performed the following:<br/>\nAdded Vehicle Profile to this account<br/><br/>----End of Service Report----<br/>', '', '0000-00-00 00:00:00', '', 'M.', '00:00:00'),
(2, 'Self Account', 'Bob', 'Smith', '', '647 5th street ne', 'Washington', 'DC', '20019', '6578', '', 'KIA MOTORS', 'SEDONA', '2004', '87JFJ', 'Bs6444', 'DC', ' Suspension , Transmission  on July 5, 2011, 10:50 pm<br/>', ' Suspension , Transmission ', '<br/>On July 5, 2011, 10:50 pm<br/>We performed the following:<br/>Serviced Area:  Suspension , Transmission <br/>Service Details:<br/>\nNew Transmission rebuilt<br/>installed<br/><br/>Mileage @ Service: 6578<br/><br/>----End of Service Report----<br/><br/>On July 5, 2011, 6:07 pm<br/>We performed the following:<br/>Serviced Area:  Transmission <br/>Service Details:<br/>\ntest<br/><br/>Mileage @ Service: 8847<br/><br/>----End of Service Report----<br/><br/>On June 30, 2011, 12:27 pm<br/>We performed the following:<br/>Serviced Area:  Brake , Engine Mechanical , Suspension <br/>Service Details:<br/>\nmultiple services<br/><br/>Mileage @ Service: 54323<br/><br/>----End of Service Report----<br/><br/>On June 2, 2011, 11:40 am<br/>We performed the following:<br/>Serviced Area:  Engine Mechanical <br/>Service Details:<br/>\ntest service<br/><br/>Mileage @ Service: 45454<br/><br/>----End of Service Report----<br/><br/>On May 17, 2011, 6:49 am<br/>We performed the following:<br/>Serviced Area:  Oil Change <br/>Service Details:<br/>\noil change<br/><br/>Mileage @ Service: 5453<br/><br/>----End of Service Report----<br/><br/>On April 25, 2011, 11:39 pm<br/>We performed the following:<br/>Serviced Area:  Oil Change <br/>Service Details:<br/>\noil change<br/><br/>Mileage @ Service: 4333<br/><br/>----End of Service Report----<br/><br/>On April 10, 2011, 6:28 pm<br/>We performed the following:<br/>Serviced Area:  Engine Mechanical <br/>Service Details:<br/>\ntest<br/><br/>Mileage @ Service: 543<br/><br/>----End of Service Report----<br/><br/>On April 10, 2011, 5:13 pm<br/>We performed the following:<br/>Serviced Area:  Oil Change , Engine Mechanical <br/>Service Details:<br/>\ntest<br/><br/>Mileage @ Service: 5433<br/><br/>----End of Service Report----<br/><br/>On April 10, 2011, 5:11 pm<br/>We performed the following:<br/>\nAdded Vehicle Profile to this account<br/><br/>----End of Service Report----<br/>', 'New Transmission rebuilt<br/>installed', '2011-07-05 10:50:49', '424409', 'B.', '12:50:49'),
(3, 'Self Account', 'Jose', 'Smith', '', '633 pike Center drive', 'Vienna', 'VA', '22328', '6544', '', 'FORD', 'ESCAPE 4WD', '2003', '43DFS', 'JS6773', 'DC', ' Oil Change  on May 17, 2011, 6:54 am<br/>', '', '<br/>On May 17, 2011, 6:54 am<br/>We performed the following:<br/>Serviced Area:  Oil Change <br/>Service Details:<br/>\noil Change<br/><br/>Mileage @ Service: 6544<br/><br/>----End of Service Report----<br/><br/>On April 30, 2011, 7:40 pm<br/>We performed the following:<br/>Serviced Area:  Oil Change <br/>Service Details:<br/>\noil change<br/><br/>Mileage @ Service: 4343<br/><br/>----End of Service Report----<br/><br/>On April 11, 2011, 10:25 am<br/>We performed the following:<br/>Serviced Area:  Oil Change <br/>Service Details:<br/>\noil change<br/><br/>Mileage @ Service: 5433<br/><br/>----End of Service Report----<br/><br/>On April 10, 2011, 6:45 pm<br/>We performed the following:<br/>\nAdded Vehicle Profile to this account<br/><br/>----End of Service Report----<br/>', '', '0000-00-00 00:00:00', '', 'J.', '00:00:00'),
(4, 'Self Account', 'Jose', 'Smith', '', '633 pike Center drive', 'Vienna', 'VA', '22328', '6473', '', 'FORD', 'B4000 4WD', '2005', '2345TF', 'JS6773', 'FL', ' Air Intake  on July 5, 2011, 10:47 pm<br/>', ' Air Intake ', '<br/>On July 5, 2011, 10:47 pm<br/>We performed the following:<br/>Serviced Area:  Air Intake <br/>Service Details:<br/>\nnew parts installed<br/><br/>Mileage @ Service: 6473<br/><br/>----End of Service Report----<br/><br/>On July 4, 2011, 8:38 pm<br/>We performed the following:<br/>Serviced Area:  Driveshaft & Axle <br/>Service Details:<br/>\ntest<br/><br/>Mileage @ Service: 68889<br/><br/>----End of Service Report----<br/><br/>On July 4, 2011, 8:35 pm<br/>We performed the following:<br/>Serviced Area:  Suspension <br/>Service Details:<br/>\nnew system<br/><br/>Mileage @ Service: 546778<br/><br/>----End of Service Report----<br/><br/>On July 4, 2011, 8:31 pm<br/>We performed the following:<br/>Serviced Area:  Air Intake <br/>Service Details:<br/>\ntesting<br/><br/>Mileage @ Service: 4554<br/><br/>----End of Service Report----<br/><br/>On July 4, 2011, 6:50 pm<br/>We performed the following:<br/>Serviced Area: Other <br/>Service Details:<br/>\ndetails<br/><br/>Mileage @ Service: 7689<br/><br/>----End of Service Report----<br/><br/>On July 3, 2011, 12:50 pm<br/>We performed the following:<br/>Serviced Area:  Air Intake <br/>Service Details:<br/>\ntest<br/><br/>Mileage @ Service: 4345<br/><br/>----End of Service Report----<br/><br/>On April 10, 2011, 7:35 pm<br/>We performed the following:<br/>\nAdditional Vehicle added to this account<br/><br/>----End of Service Report----<br/>', 'new parts installed', '2011-07-05 10:47:56', '288378', 'J.', '12:47:56'),
(5, 'Self Account', 'Mossa', 'Barandao', '', '654', 'Washington', 'DC', '20393', '43434', '', 'PORSCHE', 'CAYENNE TURBO', '1996', '5437DC', 'Mb3344', 'DC', ' Steering  on June 30, 2011, 11:38 am<br/>', '', '<br/>On June 30, 2011, 11:38 am<br/>We perfo
0

I think this should work now, check query first.

$get_swork2 = "select ad.firstname, ad.lastname, ad.customerid, ad.dhtmlgoodies_category, ad.dhtmlgoodies_subcategory, ad.caryear, aj.email, sr.servicearea, sr.clientID, sr.customerid, datediff(current_date,max(sr.date))  days_passed ,max(sr.date) last_date
from servicesrendered sr 
inner join additional_cars ad on sr.customerid=ad.customerid and sr.clientid=ad.clientid
inner join ajax_client aj on sr.customerid=aj.customerid 
where sr.servicearea=' Oil Change ' and trim(aj.email) <>''  group by ad.firstname, ad.lastname, ad.customerid, ad.dhtmlgoodies_category, ad.dhtmlgoodies_subcategory, ad.caryear, aj.email, sr.servicearea, sr.clientID, sr.customerid";

 $get_swork2_res = mysql_query($get_swork2); 

  if (mysql_num_rows($get_swork2_res) > 0) 
  { 
     while ( $swork2_info = mysql_fetch_array($get_swork2_res)) 
    { 

/////fetch data
      $firstname = $swork2_info['firstname']; 
      $lastname = $swork2_info['lastname']; 
      $customerid = $swork2_info['customerid'];
      $dhtmlgoodies_category = $swork2_info['dhtmlgoodies_category']; 
      $dhtmlgoodies_subcategory = $swork2_info['dhtmlgoodies_subcategory']; 
      $caryear = $swork2_info['caryear']; 
      $recipientAddr=$swork2_info['email']; 
      $days_passed=$swork2_info['days_passed'];
      $date=$swork2_info['last_date'];
 
    /*  
     //following php code not requied now so commented
     $timestamp = strtotime($date);
      $startDate=$timestamp; 
      $limit=90;//the number of days counting down from
      $z=floor((time() - $startDate)/86400);
      $days_left=$limit-abs($z);*/

      if ($days_passed == 90)
      {
        $subject=" no days";

      }
      else if ($days_passed == 85)
      {

         $subject="5 days"  ;
      }

      else if ($days_passed == 105)//if past 15 days
      {
	 $subject="15 days";
         
      }
      else
      {
          continue;//we do not want to send mail if its not 0, 5 or -15, go to next record
      }




///////send mail
//mail begins
         $fromAddr = 'noreply@autotechpro.net';
	 $today_date = date("M-d-Y"); 
	 $today_is =date('l, F j, Y');
	 $email2 ='admin@autotechpro.net';
	 $subjectStr = 'Courtsey Automatic Oil Change reminder';
	
$mailBodyText = <<< HHHHHHHHHHHHHH
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html;charset=utf-8">
<title>mail</title>
<style type="text/css">
            h1, h2, td, p {
                font-family: helvetica, arial, sans-serif;
            }
        </style>
</head>
  <body>
Dear $firstname $lastname:<p>
This is an automatic courtsey Oil Change reminder. Our records indicate that as of today, $today_is, your $dhtmlgoodies_category $dhtmlgoodies_subcategory is now due for an oil change!!!</p>
<table cellpadding='3'>
<tr><td colspan='4' align='left'><b>Vehicle Details</b></td></tr>
<tr>
<td bgcolor='#333333'><font color='#FFFFFF'>Car year</td>
<td bgcolor='#333333'><font color='#FFFFFF'>Car Make</td>
<td bgcolor='#333333'><font color='#FFFFFF'>Car Make</td>
<td bgcolor='#333333'><font color='#FFFFFF'>Lic. plate #</td>
<td bgcolor='#333333'><font color='#FFFFFF'>Last Oil Change Date</td>
</tr>
<tr>
<td style='color:#FF0000;'>$caryear</td>
<td style='color:#FF0000;'>$dhtmlgoodies_category</td>
<td style='color:#FF0000;'>$dhtmlgoodies_subcategory</td>
<td style='color:#FF0000;'>$clientID</td>
<td style='color:#FF0000;'>$date</td>
</tr>
</table>
<p>
Please contact the shop to schedule a visit, or simply stop by.<p>
Thank you,
</body>
</html>
HHHHHHHHHHHHHH;

$headers= <<< TTTTTTTTTTTT
Bcc:$email2
From: $fromAddr
MIME-Version: 1.0
Content-Type: text/html;
TTTTTTTTTTTT;

 mail( $recipientAddr , $subjectStr , $mailBodyText, $headers);







      
 } // close foreach
0

I think this should work now, check query first.

$get_swork2 = "select ad.firstname, ad.lastname, ad.customerid, ad.dhtmlgoodies_category, ad.dhtmlgoodies_subcategory, ad.caryear, aj.email, sr.servicearea, sr.clientID, sr.customerid, datediff(current_date,max(sr.date))  days_passed ,max(sr.date) last_date
from servicesrendered sr 
inner join additional_cars ad on sr.customerid=ad.customerid and sr.clientid=ad.clientid
inner join ajax_client aj on sr.customerid=aj.customerid 
where sr.servicearea=' Oil Change ' and trim(aj.email) <>''  group by ad.firstname, ad.lastname, ad.customerid, ad.dhtmlgoodies_category, ad.dhtmlgoodies_subcategory, ad.caryear, aj.email, sr.servicearea, sr.clientID, sr.customerid";

 $get_swork2_res = mysql_query($get_swork2); 

  if (mysql_num_rows($get_swork2_res) > 0) 
  { 
     while ( $swork2_info = mysql_fetch_array($get_swork2_res)) 
    { 

/////fetch data
      $firstname = $swork2_info['firstname']; 
      $lastname = $swork2_info['lastname']; 
      $customerid = $swork2_info['customerid'];
      $dhtmlgoodies_category = $swork2_info['dhtmlgoodies_category']; 
      $dhtmlgoodies_subcategory = $swork2_info['dhtmlgoodies_subcategory']; 
      $caryear = $swork2_info['caryear']; 
      $recipientAddr=$swork2_info['email']; 
      $days_passed=$swork2_info['days_passed'];
      $date=$swork2_info['last_date'];
 
    /*  
     //following php code not requied now so commented
     $timestamp = strtotime($date);
      $startDate=$timestamp; 
      $limit=90;//the number of days counting down from
      $z=floor((time() - $startDate)/86400);
      $days_left=$limit-abs($z);*/

      if ($days_passed == 90)
      {
        $subject=" no days";

      }
      else if ($days_passed == 85)
      {

         $subject="5 days"  ;
      }

      else if ($days_passed == 105)//if past 15 days
      {
	 $subject="15 days";
         
      }
      else
      {
          continue;//we do not want to send mail if its not 0, 5 or -15, go to next record
      }




///////send mail
//mail begins
         $fromAddr = 'noreply@autotechpro.net';
	 $today_date = date("M-d-Y"); 
	 $today_is =date('l, F j, Y');
	 $email2 ='admin@autotechpro.net';
	 $subjectStr = 'Courtsey Automatic Oil Change reminder';
	
$mailBodyText = <<< HHHHHHHHHHHHHH
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html;charset=utf-8">
<title>mail</title>
<style type="text/css">
            h1, h2, td, p {
                font-family: helvetica, arial, sans-serif;
            }
        </style>
</head>
  <body>
Dear $firstname $lastname:<p>
This is an automatic courtsey Oil Change reminder. Our records indicate that as of today, $today_is, your $dhtmlgoodies_category $dhtmlgoodies_subcategory is now due for an oil change!!!</p>
<table cellpadding='3'>
<tr><td colspan='4' align='left'><b>Vehicle Details</b></td></tr>
<tr>
<td bgcolor='#333333'><font color='#FFFFFF'>Car year</td>
<td bgcolor='#333333'><font color='#FFFFFF'>Car Make</td>
<td bgcolor='#333333'><font color='#FFFFFF'>Car Make</td>
<td bgcolor='#333333'><font color='#FFFFFF'>Lic. plate #</td>
<td bgcolor='#333333'><font color='#FFFFFF'>Last Oil Change Date</td>
</tr>
<tr>
<td style='color:#FF0000;'>$caryear</td>
<td style='color:#FF0000;'>$dhtmlgoodies_category</td>
<td style='color:#FF0000;'>$dhtmlgoodies_subcategory</td>
<td style='color:#FF0000;'>$clientID</td>
<td style='color:#FF0000;'>$date</td>
</tr>
</table>
<p>
Please contact the shop to schedule a visit, or simply stop by.<p>
Thank you,
</body>
</html>
HHHHHHHHHHHHHH;

$headers= <<< TTTTTTTTTTTT
Bcc:$email2
From: $fromAddr
MIME-Version: 1.0
Content-Type: text/html;
TTTTTTTTTTTT;

 mail( $recipientAddr , $subjectStr , $mailBodyText, $headers);







      
 } // close foreach

Urtrivedi:

Right on, my friend! You have helped me navigate through this task. The code as modified, is now working as envisioned. can't thank you enough...

The very best,
Mossa

Edited by mbarandao: n/a

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.