[Resolved] simple query dependant on Date

[ejwf]

I have a really simple query that I cannot get to work for love nor money!

the whole code is

$today = date('Y-m-d');
//formatted to match the date in the database

$query = "SELECT CRef FROM tblDate WHERE OpenEarly = $today";
$result = mysql_query($query)
	or die(mysql_error());
while ($row = mysql_fetch_array($result));
  $CRef[] = $row["CRef"];

  foreach ($CRef as $value)
  {
    $body1 = "Hello, blah blah blah
regards
'$CoName'
";

$query = "SELECT CoName FROM tblConces WHERE ConcesId = '$ConcesRef'";
$result1 = mysql_query($query)
	or die(mysql_error());
$row1 = mysql_fetch_array($result1);
  $CoName = $row1["CoName"];

$query = "SELECT CEmail FROM tblUser WHERE AuthorityLevel >= 3 AND ConcesRef = '$ConcesRef'";
$result2 = mysql_query($query)
	or die(mysql_error());
while($row2 =  mysql_fetch_array($result2))
  $CEmail[] = $row2["CEmail"];

foreach ($CEmail as $value)
{
mail ("$CEmail", "subject", "$body1");
}

I have tested quite a few of the variables and the start of the problem is at $today
I have echo'd out $today and it outputs what I would expect in the correct format, but when I try to match it to the date in the database I get nothing.

I have done print_r($CRef) and get Array ( [0] => ) but I know there is one record in the database that matches the criteria. So from here on in none of the code will work........

Is there something in the way dates work that I am missing, something that is causing this problem?

I am also getting the error message Invalid argument supplied for foreach() on the second foreach

foreach ($CEmail as $value)
{
mail ("$CEmail", "subject", "$body1");
}

again any ideas? can't you have a foreach within a foreach?

thanks in advance

[tsinka]

Hi,

I see one problem:

while ($row = mysql_fetch_array($result));

Remove the ; at the end of the line. This causes PHP just to walk through the result set without doing anything.

$CRef[] = $row["CRef"];

will be empty because $row points to nothing after that while loop.

Thomas

[ejwf]

thank you Thomas, now when I print_r($CRef) I get what I expect.

If you have time, can you help me out with the rest of this code, I am now struggling with the foreach constructs, moving on with the code I have

foreach ($CRef as $value) 
  { 
    $body1 = "Hello, blah blah blah 
regards 
'$CoName' 
"; 

$query = "SELECT CoName FROM tblConces WHERE ConcesId = '$ConcesRef'"; 
$result1 = mysql_query($query) 
    or die(mysql_error()); 
$row1 = mysql_fetch_array($result1); 
  $CoName = $row1["CoName"]; 

$query = "SELECT CEmail FROM tblUser WHERE AuthorityLevel >= 3 AND ConcesRef = '$ConcesRef'"; 
$result2 = mysql_query($query) 
    or die(mysql_error()); 
while($row2 =  mysql_fetch_array($result2)) 
  $CEmail[] = $row2["CEmail"]; 

foreach ($CEmail as $value) 
{ 
mail ("$CEmail", "subject", "$body1"); 
}

when I try to echo out the variable $CoName I get nothing
and when I do print_r(CEmail) again, nothing.....

Also, when the code is run I get the error message
Invalid argument supplied for foreach() for line 41 which is foreach ($CEmail as $value)

any help would be gratefully received
thanks

[tsinka]

Ok,

where does $ConcesRef come from ? From the $CRef array ?

I modified your code a little bit but it might meed some tweaking ...

foreach ($CRef as $ref) { 
  $query = "SELECT CoName FROM tblConces WHERE ConcesId = '".$ref."'"; 
  $result1 = mysql_query($query) or die(mysql_error()); 

  if ($row1 = mysql_fetch_assoc($result1)) { 
      $CoName = $row1["CoName"]; 

  $body1 = "Hello, blah blah blah 
regards 
'{$CoName}'
"; 

  $query = "SELECT CEmail FROM tblUser WHERE AuthorityLevel >= 3 AND ConcesRef = '".$ref."'"; 
  $result2 = mysql_query($query) or die(mysql_error()); 
  while ($row2 =  mysql_fetch_array($result2)) { 
      mail ($row2["CEmail"], "subject", $body1);
  }  
  }
}

Thomas

[ejwf]

thank you Thomas, that works up to a point, I get no error messages, but the code isn't picking up the EMail addresses from the database in the SELECT CEmail FROM tblUser part of the code.
I have echo'd out ther variables using the following code

$email = $row2["$CEmail"];
echo $email;
echo "<br>";
echo $CoName;
echo "<br>";
echo $ref;

$CoName and $ref output OK but I get nothing from the $email variable and nothing comes through on an e-mail as a result of the mail command .......

any ideas
thanks again

[tsinka]

Replace
$email = $row2["$CEmail"];
with
$email = $row2["CEmail"];

Thomas

[ejwf]

thank you so much
all working now